mysql基本操作汇总

1.数据库操作

（1）创建数据库

CREATE DATABASE <数据库名>;

例子：

CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

（2）删除数据库

drop database <数据库名>;

（3）选择数据库

use	<数据库名>;

2.数据类型

数据类型有三种：数值、日期/时间和字符串(字符)类型。

（1）数值类型
两种：严格数值类型和近似数值数据类型。
严格数值类型：INTEGER（INT）、SMALLINT、DECIMAL和NUMERIC。
近似数值数据类型：FLOAT、REAL和DOUBLE PRECISION。
（2）日期/时间类型
五种：DATETIME（YYYY-MM-DD HH:MM:SS）、DATE（YYYY-MM-DD）、TIMESTAMP（YYYYMMDD HHMMSS）、TIME（HH:MM:SS）和YEAR（YYYY）。
（3）字符串类型
常用的有CHAR、VARCHAR、TEXT等
char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
注意： 经常变化的字段用 varchar； 知道固定长度的用 char； 尽量用 varchar； 超过 255 字符的只能用 varchar 或者 text； 能用 varchar 的地方不用 text

　

3.表操作

（1）创建表

CREATE TABLE table_name (column_name_1 column_type_1, column_name_2 column_type_2.....);

例子：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
    `runoob_id` INT UNSIGNED AUTO_INCREMENT,
    `runoob_title` VARCHAR(100) NOT NULL,
    `runoob_author` VARCHAR(40) NOT NULL,
    `submission_date` DATE,
    PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：

表名和字段名的符号是反引号`。
PRIMARY KEY关键字用于定义列为主键。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

（2）删除数据表

DROP TABLE table_name ;

删除了表全部数据和表结构，立刻释放磁盘空间
补充：sql里的几种删除：

（i）drop table table_name : 删除表全部数据和表结构，立刻释放磁盘空间
（ii）truncate table table_name : 删除表全部数据，保留表结构，立刻释放磁盘空间
（iii）delete from table_name : 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
（vi）delete from table_name where xxx : 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;
delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间.

注：

当你不再需要该表时， 用 drop; 当你仍要保留该表，但要删除所有记录时， 用 truncate; 当你要删除部分记录时， 用 delete。
delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。
执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

　　

4.记录操作

（1）插入数据

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );

注：
如果数据是字符型，必须使用单引号或者双引号，如："value"。
如果value是按顺序全的话，前面的field可省略。
第一列设计了主键自增（PRINARY KEY AUTO_INCREMENT），第一列在增加数据的时候，可以写为0或者nul。
（省略field的话就要写主键的值，写了field就不需写主键字段，它会自己自增的。）

　　

（2）查询数据

SELECT column_name,column_name FROM table_name [WHERE Clause] [LIMIT N] [ OFFSET M]

注：

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。使用星号（*）来代替，返回表的所有字段数据
使用 WHERE 语句来包含任何条件。
使用 LIMIT 属性来设定返回的记录数。
通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。

（i）limit用法
limit M : 返回 M 条记录
limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录
例子：

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： 
SELECT * FROM table LIMIT 5; //检索前 5 个记录行 
//LIMIT n 等价于 LIMIT 0,n

（ii）where子句

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

注：

WHERE 子句类似于程序语言中的 if 条件。可以使用 AND 或者 OR 指定一个或多个条件。
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
如：SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';	//只有runoob.com才能查询出来，RUNOOB.COM就不行。不加BINARY就行。

（3）更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

例子：
将所有人的年龄增加 1：

update students set age=age+1;

将id为3的title改为学习C++：

UPDATE tbl SET title='学习 C++' WHERE id=3;

UPDATE替换某个字段中的某个字符：

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') [WHERE Clause]

如：更新 id 为 3 的title 字段值的 "C++" 替换为 "Python"：

UPDATE tbl SET title = REPLACE(title, 'C++', 'Python') where id = 3;

（4）删除数据

DELETE FROM table_name [WHERE Clause]

注：如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。

5.关键字

（1）like子句

SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

注：

可以在 WHERE 子句中使用LIKE子句。
可以使用LIKE子句代替等号 =。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。

例子：
在runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录：

SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM'；

其他：

like 匹配/模糊匹配，会与 % 和 _ 结合使用。
'%a' //以a结尾的数据
'a%' //以a开头的数据
'%a%' //含有a的数据
'_a_' //三位且中间字母是a的
'_a' //两位且结尾字母是a的
'a_' //两位且开头字母是a的

（2）union操作符
UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]
UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];

注：

DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

例子：

"Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：
SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;
使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：
SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country;

总结：

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

（3）ORDER BY排序

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

例子：

SELECT * from runoob_tbl ORDER BY submission_date ASC;
SELECT * from runoob_tbl ORDER BY submission_date DESC;

补充：拼音排序
如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：

SELECT * 
FROM runoob_tbl
ORDER BY runoob_title;

如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：

SELECT * 
FROM runoob_tbl
ORDER BY CONVERT(runoob_title using gbk);

（4）GROUP BY分组语句
GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

例子：
数据表按名字进行分组，并统计每个人有多少条记录：

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
例子：
将数据表按名字进行分组，再统计每个人登录的次数：

SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;

（5）连接
JOIN 来联合多表查询

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

例子：

SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;

INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。

SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

（6）NULL值处理
使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;

不能直接用=

select * , columnName1+ifnull(columnName2,0) from tableName;

columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。

（6）alter命令
修改数据表名或者修改数据表字段时用alter命令。
删除、添加、修改表字段

ALTER TABLE tbl DROP i;	//删除tb1表的i字段

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

ALTER TABLE tbl ADD i INT;	//在表 tbl 中添加 i 字段，并定义数据类型:
ALTER TABLE tbl MODIFY c CHAR(10);	//把字段 c 的类型改为 CHAR(10)
ALTER TABLE tbl CHANGE i j BIGINT;	//把字段i名称改为j，类型为BIGINT

修改表名：

ALTER TABLE tbl RENAME TO tb2;

（7）事务
事务主要用于处理操作量大，复杂度高的数据。
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert,update,delete 语句

事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成.事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。
因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，
或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；

注：

savepoint 是在数据库事务处理中实现“子事务”（subtransaction），也称为嵌套事务的方法。事务可以回滚到 savepoint 而不影响 savepoint 创建前的变化, 不需要放弃整个事务。
ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT，执行多条操作时，回滚到想要的那条语句之前。
使用 SAVEPOINT
SAVEPOINT savepoint_name; // 声明一个 savepoint
ROLLBACK TO savepoint_name; // 回滚到savepoint
删除 SAVEPOINT
保留点在事务处理完成（执行一条 ROLLBACK 或 COMMIT）后自动释放。

（8）索引
索引就像字典的目录页，快速查找到需要的字。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
索引本质是张表，该表保存了主键与索引字段，并指向实体表的记录。
索引优缺点：

提高检索速度
降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

（i）普通索引
创建索引：

CREATE INDEX indexName ON mytable(username(length)); 

修改表结构(添加索引)：

ALTER table tableName ADD INDEX indexName(columnName)；

创建表的时候直接指定：

CREATE TABLE mytable( 
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
INDEX [indexName] (username(length)) 
); 

删除索引：

DROP INDEX [indexName] ON mytable; 

（ii）唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建索引：

CREATE UNIQUE INDEX indexName ON mytable(username(length)); 

修改表结构(添加索引)：

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定：

CREATE TABLE mytable( 
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
UNIQUE [indexName] (username(length)) 
); 

删除索引：

DROP INDEX [indexName] ON mytable; 

显示索引信息：

HOW INDEX FROM table_name;

（9）复制表
完全的复制MySQL的数据表，包括表的结构，索引，默认值等.
步骤:
使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

（10）获取服务器元数据

SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

　　

（11）AUTO_INCREMENT
实现自动增长。

CREATE TABLE insect(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id),
    name VARCHAR(30) NOT NULL,
    date DATE NOT NULL, 
    origin VARCHAR(30) NOT NULL 
);

插入的时候,id的值为0或者 NULL , 实现自增
自增默认的开始值都是1,如果要指定从100开始:

CREATE TABLE insect(
　　id INT UNSIGNED NOT NULL AUTO_INCREMENT,
　　PRIMARY KEY (id),
　　name VARCHAR(30) NOT NULL, 
　　date DATE NOT NULL,
　　origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

或者在表创建成功后:

ALTER TABLE t AUTO_INCREMENT = 100;

　

（12）重复数据
防止表中出现重复数据:
指定字段为主键PRIMARY_KEY

CREATE TABLE person_tbl(
　　first_name CHAR(20) NOT NULL,
　　last_name CHAR(20) NOT NULL,
　　sex CHAR(10),
　　PRIMARY KEY (last_name, first_name)
);

设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。
另一种设置数据的唯一性方法是添加一个 UNIQUE 索引

CREATE TABLE person_tbl(
　　first_name CHAR(20) NOT NULL,
　　last_name CHAR(20) NOT NULL,
　　sex CHAR(10),
　　UNIQUE (last_name, first_name)
);　

统计重复数据:

SELECT COUNT(*) as repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;

注:

确定哪一列包含的值可能会重复。
在列选择列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句设置重复数大于1。
HAVING过滤

补充:

where：数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。
group by:对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。
having：用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。
执行顺序
select –>where –> group by–> having–>order by

过滤重复数据
在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

SELECT DISTINCT last_name, first_name FROM person_tbl;

使用 GROUP BY 来读取数据表中不重复的数据：

SELECT last_name, first_name FROM person_tbl GROUP BY (last_name, first_name);

删除重复数据

CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); //创建临时表,是过滤掉重复数据的
DROP TABLE person_tbl;	//删除原表
ALTER TABLE tmp RENAME TO person_tbl;	//将临时表名称改为原表

或者:
在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。

ALTER IGNORE TABLE person_tbl ADD PRIMARY KEY (last_name, first_name);