1.表的创建与删除
1.1 建表
CREATE关键字
CREATE TABLE IF NOT EXISTS `test` (
`id` INT (5) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`age` INT (3) COMMENT '年龄',
`sex` VARCHAR (1) COMMENT '性别',
-- 列名 数据类型（大小） 各种属性值 备注
PRIMARY KEY (`id`) -- 设置主键
) ENGINE = INNODB DEFAULT CHARSET = utf8 -- 设置引擎和默认字符集
1.2删表
DROP 关键字
DROP TABLE IF EXISTS `test01`
2.增删改查
2.1增
INSERT 关键字 ： 插入一行或多行数据（字段名可省略不写，但后面的值需与列一一对应）
INSERT INTO `test` (`name`,`age`,`sex`) VALUES ('张三',18,'男')
格式：INSERT INTO 表名 (字段名,字段名···),(···),(····)··· VALUES(对应值，对应值···),(···),(····)···
2.2改
UPDATE关键字 ：搭配WHERE更改符合条件位置的数据
UPDATE `test` SET `name`='李四',`sex`='女' WHERE `name`='李四' AND `id`=1
格式：UPDATE 表名 SET 字段名=对应值,字段名=对应值··· WHERE 条件(可用and或or链接多个条件)
2.3删
DELETE关键字 ：搭配WHERE删除符合条件的行
DELETE FROM `test` WHERE `id`=1
格式：DELETE FROM 表名 WHERE 条件
- 注意：不加where 则删除全表数据
TRUNCATE关键字：删除全表数据
TRUNCATE `test`
格式：TRUNCATE 表名
DELETE与TRUNCATE清空全表时的不同
- TRUNCATE 清空全表时时会重置自增列的计数器。
- TRUNCATE不会影响事务。
2.4查（DQL）
SELECT关建字
-- 查询指定表中所有数据
SELECT * FROM test
-- 查询指定表中的指定字段
SELECT `name`,`age` FROM test
-- 查询时可为字段名或表名取别名，查询结果会显示其别名
SELECT `name` AS `姓名`,`age` AS `年龄` FROM test AS `测试`
-- 函数CONCAT(a,b) 拼接字符串展示在查询结果中
SELECT CONCAT('姓名：',`name`) AS `姓名` FROM test AS `测试`
-- DISTINCT关键字去重
SELECT DISTINCT `name` AS `姓名` FROM test AS `测试`
-- 查询系统版本（函数）
SELECT VERSION()
-- 查询计算结果（表达式）
SELECT 100*10-1
-- 查询自增的步长（属性）
SELECT @@auto_increment_increment
-- 按条件查询
SELECT `name`,`age` FROM test WHERE age<20&&age>15
SELECT `name`,`age` FROM test WHERE age<20 AND age>15
SELECT `name`,`age` FROM test WHERE age BETWEEN 15 AND 20
格式：SELECT 字段名 FROM 表名 or SELECT 表达式 FROM 表名
2.4.1 WHERE子句模糊查询
模糊查询：比较运算符
| 运算符 | 语法格式 | 描述 |
|---|---|---|
| IS NULL | x IS NULL | 如果x为NULL，返回真 |
| IS NOT NULL | x IS NOT NULL | 如果x不为NULL，返回真 |
| BETWEEN | x BETWEEN a AND b | 若x在a,b之间，返回真 |
| LIKE | x LIKE y | 如果x匹配y，返回真 |
| IN | x IN (x1,x2,x3,···) | x是x1,x2,···中的某个值，返回真 |
-- %表示零个或多个任意字符
-- _表示一个任意字符
-- 查询姓刘的同学
SELECT `name` FROM test WHERE name LIKE '刘%'
-- 查询姓刘且刘后只有一个字的同学
SELECT `name` FROM test WHERE name LIKE '刘_'
-- 查询名字带江的同学
SELECT `name` FROM test WHERE name LIKE '%江%'
-- 查询id在1,2,3之中的同学
SELECT `name` FROM test WHERE id IN (1,2,3)
2.4.2 联表查询
jion关键字
- INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
- LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
- RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记
使用到的数据：
SELECT * from runoob_tbl
| runoob_id | runoob_title | runoob_author | submission_date |
| 1 | 学习 PHP | 菜鸟教程 | 2017-04-12 |
| 2 | 学习 MySQL | 菜鸟教程 | 2017-04-12 |
| 3 | 学习 Java | RUNOOB.COM | 2015-05-01 |
| 4 | 学习 Python | RUNOOB.COM | 2016-03-06 |
| 5 | 学习 C | FK | 2017-04-05 |
SELECT * FROM tcount_tbl
| runoob_author | runoob_count |
| 菜鸟教程 | 10 |
| RUNOOB.COM | 20 |
| 22 |
2.4.2.1 INNER JOIN
使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl AS a INNER JOIN tcount_tbl AS b ON a.runoob_author = b.runoob_author;
-- AS均可省略
查询结果如下：
| runoob_id | runoob_author | runoob_count |
| 1 | 菜鸟教程 | 10 |
| 2 | 菜鸟教程 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
2.4.2.2 LEFT JOIN
MySQL 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;
查询结果如下：
| runoob_id | runoob_author | runoob_count |
| 1 | 菜鸟教程 | 10 |
| 2 | 菜鸟教程 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
| 5 | FK | (NULL) |
2.4.2.2 RIGHT JOIN
MySQL 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;
查询结果如下：
| runoob_id | runoob_author | runoob_count |
| 2 | 菜鸟教程 | 10 |
| 1 | 菜鸟教程 | 10 |
| 4 | RUNOOB.COM | 20 |
| 3 | RUNOOB.COM | 20 |
| (NULL) | (NULL) | 22 |
2.4.2.3 更多连接方式
2.4.3 SELECT完整语法格式
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[LIMIT]
-- 用中括号([])括起来的部分表示是可选的，用大括号({})括起来的部分是表示必须从中选择其中的一个。
- FROM子句
FROM子句指定了SELECT语句中字段的来源。FROM子句后面是包含一个或多个的表达式(由逗号分开)，其中的表达式可为单一表名称、已保存的查询或由 INNER JOIN、LEFT JOIN 或 RIGHT JOIN 得到的复合结果。如果表或查询存储在外部数据库，在IN 子句之后指明其完整路径。 - ALL、DISTINCT、DISTINCTROW、TOP谓词
(1) ALL 返回满足SQL语句条件的所有记录。如果没有指明这个谓词，默认为ALL。
例：SELECT ALL FirstName,LastName
FROM Employees
(2) DISTINCT 如果有多个记录的选择字段的数据相同，只返回一个。
(3) DISTINCTROW 如果有重复的记录，只返回一个
(4) TOP显示查询头尾若干记录。也可返回记录的百分比，这是要用 TOP N PERCENT子句（其中N 表示百分比） - 分组和总结查询结果
在SQL的语法里，GROUP BY和HAVING子句用来对数据进行汇总。GROUP BY子句指明了按照哪几个字段来分组，而将记录分组后，用HAVING子句过滤这些记录。 - 用ORDER BY子句排序结果
ORDER子句按一个或多个（最多16个）字段排序查询结果，可以是升序（ASC）也可以是降序（DESC），缺省是升序。ORDER子句通常放在SQL语句的最后。
ORDER子句中定义了多个字段，则按照字段的先后顺序排序。 - LIMIT指定行数查询LIMIT 起始行数 offset 需要的行数
3.MySQL函数
3.1聚集函数
- COUNT(expr)：返回expr的行数（不包括NULL值）。
- SUM(expr)：返回expr的总和。
- AVG(expr)：返回expr的平均值。
- MAX(expr)：返回expr的最大值。
- MIN(expr)：返回expr的最小值。
- GROUP_CONCAT(expr)：将expr的值连接为一个字符串。
- STD(expr)：返回expr的标准差。
- VARIANCE(expr)：返回expr的方差。
3.2用于处理字符串的函数
- CONCAT(str1, str2, …)：将多个字符串拼接成一个字符串。
- SUBSTRING(str, start, length)：返回字符串str从第start个字符开始，长度为length的子串。
- LEFT(str, length)：返回字符串str左侧的length个字符。
- RIGHT(str, length)：返回字符串str右侧的length个字符。
- LENGTH(str)：返回字符串str的长度。
- REPLACE(str, old_str, new_str)：将字符串str中的所有old_str替换为new_str。
- UPPER(str)：将字符串str中的所有字符转换为大写。
- LOWER(str)：将字符串str中的所有字符转换为小写。
- TRIM(str)：去掉字符串str两端的空格。
- LTRIM(str)：去掉字符串str左侧的空格。
- RTRIM(str)：去掉字符串str右侧的空格。
- REVERSE(str)：将字符串str反转。
- LOCATE(substr, str, pos)：返回字符串str中substr第一次出现的位置，位置从pos开始搜索。
- ASCII(str)：返回字符串str的第一个字符的ASCII码值。
3.3用于处理数值的函数
- ABS (x)：返回 x 的绝对值。
- CEILING (x)：返回不小于 x 的最小整数值。
- FLOOR (x)：返回不大于 x 的最大整数值。
- ROUND (x,y)：对 x 进行四舍五入操作，并将结果保留为 y 个小数位。
- TRUNCATE (x,y)：截取 x 的小数部分，并将结果保留为 y 个小数位。
- MOD (x,y)：返回 x 除以 y 的余数。
- SQRT (x)：返回 x 的平方根。
- POW (x,y)：返回 x 的 y 次方。
- EXP (x)：返回 e 的 x 次幂。
- LOG (x)：返回 x 的自然对数。
- LOG10 (x)：返回 x 的以 10 为底的对数。
- SIGN (x)：返回 x 的符号，正数返回 1，负数返回 – 1，0 返回 0。
- RAND ()：返回一个 0 到 1 之间的随机数。
- PI ()：返回圆周率 π 的近似值 3.141592653589793。
3.4用于处理时间日期的函数
- NOW()：返回当前日期和时间。
- DATE_FORMAT(date, format)：将日期格式化为指定的字符串格式。
- YEAR(date)：返回日期的年份部分。
- MONTH(date)：返回日期的月份部分。
- DAY(date)：返回日期的日份部分。
- HOUR(time)：返回时间的小时部分。
- MINUTE(time)：返回时间的分钟部分。
- SECOND(time)：返回时间的秒钟部分。
- TIMESTAMPDIFF(unit, start_time, end_time)：返回从start_time到end_time之间的时间差，单位为unit。
- DATE_ADD(date, INTERVAL expr unit)：将日期date加上一个时间间隔，单位为unit。
- DATE_SUB(date, INTERVAL expr unit)：将日期date减去一个时间间隔，单位为unit。
- STR_TO_DATE(str, format)：将字符串转换为日期时间格式。
- UNIX_TIMESTAMP()：返回当前日期和时间的UNIX时间戳。
- FROM_UNIXTIME(unix_timestamp, format)：将UNIX时间戳转换为日期时间格式。
4.事务
4.1．什么是事务
事务（Transaction）是并发控制单位，是用户定义的一个操作序列，这些操作要么都做，要么都不做，是一个不可分割的工作单位。
事务通常以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。
事务通常以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。
4.2．事务的四大特性（ACID）
事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。
- 原子性原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性
数据库总是从一个一致性的状态转移到另一个一致性的状态。一致性确保了即使在执行第三、第四条语句之间时系统崩溃，前面执行的第一、第二条语句也不会生效，因为事务最终没有提交，所有事务中所作的修改也不会保存到数据库中。 - 隔离性
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。 - 持续性
指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。
事务中的所有操作要么全部执行，要么都不执行； 如果事务没有原子性的保证，那么在发生系统 故障的情况下，数据库就有可能处于不一致状态。 因而，事务的原子性与一致性是密切相关的。
5.索引
1.普通索引
(1) 最基本的索引类型，没有唯一性之类的限制；
(2) 作用只是为了加快系统对数据的访问速度；
(3) 允许空值、重复值。
2.唯一索引
(1) 唯一索引是不允许其中任何两行具有相同索引值的索引；
(2) 唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复；
(3) 允许有空值。
3.组合索引
(1) 由多个字段组成的索引叫组合索引，也称为复合索引或联合索引；
(2) 一个组合索引实质上为表的查询提供了多个索引
如：创建组合索引 (c1,c2,c3)，相当于创建了 (c1)，(c1, c2)，(c1, c2, c3) 三个索引。
注意如果不符合最左前缀原则，且也不符合覆盖索引，会导致索引失效
4.主键索引
(1) 主键索引是唯一索引的特定类型，该索引要求主键中的每个值都唯一；
(2) 不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。
5.全文索引
针对比较大的数据，如果要解决 like 查询效率低的问题，可以创建全文索引。
(1) 全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。
(2) 全文索引允许在索引列中插入重复值和空值。
注意对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
-- 创建索引
-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1, col_name_2);
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 创建全文索引
CREATE FULLTEXT INDEX index_name ON table_name(col_name);
-- 修改表
ALTER TABLE table_name ADD INDEX index_name(col_name); // 添加索引
ALTER TABLE table_name DROP INDEX index_name; // 删除索引
-- 创建表的时候指定索引
CREATE TABLE table_name ( […], INDEX [index_name] (col_name) );
-- 删除索引
DROP INDEX [index_name] ON table_name;
-- 查看索引
SHOW INDEX FROM table_name;
-- 其他命令
DESC table_name; -- 查看表结构
SHOW CREATE TABLE table_name; -- 查看生成表的SQL
