数据库操作

创建数据库

1	CREATE DATABASE database_name;

删除数据库

1	DROP DATABASE database_name;

选择数据库

1	USE database_name;

数据表操作

创建数据表

CREATE TABLE table_name (column_name, column_type);

-- example
CREATE TABLE IF EXISTS `t_example`(
  `id` INT AUTO_INCREMENT,
  `title` VARCHAR(100) NOT NULL,
  `author` VARCHAR(40) NOT NULL.
  `date` DATE,
  PRIMARY KEY(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除数据表

DROP TABLE table_name;

-- example
DROP TABLE t_example;

修改数据表名

1	ALTER TABLE old_name RENAME TO new_name;

删除数据表字段

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

1 2	ALTER TABLE table_name DROP col;

添加数据表字段

1 2	ALTER TABLE table_name ADD col_name data_type;

修改数据表字段数据类型

在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型

-- MODIFY子句实现
ALTER TABLE table_name MODIFY col data_type; 

-- CHANGE子句实现
ALTER TABLE table_name CHANGEK old_col new_col data_type;

ALTER TABLE testalter_tbl MODIFY c CHAR(10);

ALTER TABLE testalter_tbl CHANGE i j BIGINT;
ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 对 Null 值和默认值的影响

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

1 2	ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;

修改数据字段默认值

-- 使用 ALTER 来修改字段的默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
-- 使用 ALTER 命令及 DROP子句来删除字段的默认值
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

数据操作

插入数据

1 2	INSERT INTO table_name (col1,col2,...,coln) VALUES(val1,val2,...,valn);

查询数据

SELECT col_name,...
FROM table_name
[WHERE CLAUSE]
[LIMIT KN][OFFSET M]

关键字	说明
LIMIT	限制返回记录数
OFFSET	限制开始查询的数据偏移量，默认为0

更新数据

1
2
3

UPDATE table_name
SET col1 = new_val1,...
[WHERE CLAUSE]

删除数据

1
2
3

DELETE 
FROM table_name 
[WHERE CLAUSE]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

WHERE子句

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

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

LIKE子句

1
2
3

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

在 WHERE 子句中指定任何条件
在 WHERE 子句中使用LIKE子句
使用LIKE子句代替等号 =
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
可以使用 AND 或者 OR 指定一个或多个条件。
可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

UNION

MySQL 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 操作符已经删除了重复数据
ALL	回所有结果集，包含重复数据

ORDER BY

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

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;

JOIN

连接类型	说明
INNER JOIN（内连接,或等值连接）	获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）	获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）	获取右表所有记录，即使左表没有对应匹配的记录。

NULL值处理

处理NULL，MySQL提供的运算符

运算符	说明
IS NULL	当列的值是 NULL,此运算符返回 true。
IS NOT NULL	当列的值不为 NULL, 运算符返回 true。
<=>	比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n’ 或 ‘\r’ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n’ 或 ‘\r’ 之前的位置。
.	匹配除 “\n” 之外的任何单个字符。要匹配包括 ‘\n’ 在内的任何字符，请使用像 ‘[.\n]’ 的模式。
[…]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]’ 可以匹配 “plain” 中的 ‘a’。
^...	负值字符集合。匹配未包含的任意字符。例如， ‘^abc‘ 可以匹配 “plain” 中的’p’。
p1\	p2\	p3	匹配 p1 或 p2 或 p3。例如，’z\	food’ 能匹配 “z” 或 “food”。’(z\	f)ood’ 则匹配 “zood” 或 “food”。
*	匹配前面的子表达式零次或多次。例如，zo 能匹配 “z” 以及 “zoo”。等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，’zo+’ 能匹配 “zo” 以及 “zoo”，但不能匹配 “z”。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，’o{2}’ 不能匹配 “Bob” 中的 ‘o’，但是能匹配 “food” 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

查找name字段中以’st’为开头的所有数据：

1	SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以’ok’为结尾的所有数据：

1	SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含’mar’字符串的所有数据：

1	SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以’ok’字符串结尾的所有数据：

1	SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]\|ok$';

事务

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

ACID特性

ACID特性	说明
原子性	一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性	在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性	数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性	事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。

因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句

语句	说明
BEGIN 或 START TRANSACTION	显式地开启一个事务
COMMIT 也可以使用 COMMIT WORK	COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK	回滚会结束用户的事务，并撤销正在进行的所有未提交的修改
RELEASE SAVEPOINT identifier	删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier	把事务回滚到标记点；
SET TRANSACTION	用来设置事务的隔离级别。

InnoDB 存储引擎提供事务的隔离级别

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE。

事务处理主要有两种方法

用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交

索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

1	CREATE INDEX indexName ON table_name (col_name);

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

添加索引

1	ALTER TABLE tableName ADD INDEX indexName(col_name);

创建表直接指定索引

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

删除索引

1	DROP INDEX [indexName] ON mytable;

唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建索引

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

添加索引

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

创建表直接指定索引

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

使用ALTER 命令添加和删除索引

-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

-- 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)

-- 该语句指定了索引为 FULLTEXT ，用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

显示索引信息

可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

1	SHOW INDEX FROM table_name\G

复制表

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。如果仅仅使用CREATE TABLE … SELECT 命令，是无法实现的。

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完全的复制数据表结构。
如果你想复制表的内容，你就可以使用 INSERT INTO … SELECT 语句来实现。

元数据

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