SQL学习笔记

一、SQL语句中的四种分类

1） DDL（Data Definition Language）

	用来定义数据库中的所有对象：如数据库，表，列。

1.操作数据库：CRUD

		* C(CREATE)创建

需求	命令
创建数据库 【判断是否已经存在】【指定字符集】	CREATE DATABASE 【IF NOT EXISTS】 数据库名 【CHARACTER SET GBK;】;

		* R(RETRIEVE)查询

需求	命令
查询所有数据库的名称	SHOW DATABASES;
查看数据库的字符集	SHOW CREATE DATABASE 数据库名;
查看正在使用那个数据库	SELECT DATABASE();

		* U(UPDATE)修改

需求	命令
修改数据库的字符集	ALTER DATABASE 数据库名 CHARACTER SET utf8;

		* D(DELETE)删除

需求	命令
删除数据库【判断是否存在】	DROP DATABASE 【if exists】 数据库名;

		*使用数据库

需求	命令
使用数据库	USE 数据库名;

2.操作表：CRUD

		* C(CREATE)创建

需求	命令
创建表	CREATE TABLE 表名(列名1 数据类型 【约束条件】 , 列名2 数据类型 【约束条件】 , … 列名n 数据类型 【约束条件】);
复制表	CREATE TABLE 新表明 LIKE 要复制的表名;
创建表时添加外键约束	create table 表名( 列名 数据类型 [约束条件]，列名 数据类型 [约束条件]。。。。外键列 数据类型 ,）CONSTRAINT 外键名称 FOREIGN KEY (列名) REFERENCES 管联的数据表(表中的列名));；
创建表时添加复合主键	create table 表名( 列名 数据类型 [约束条件]，列名 数据类型 [约束条件]。。。。外键列 数据类型 ,）PRIMARY KEY(列名,列名)

		* R(RETRIEVE)查询

需求	命令
查询所使用的数据库中所有的表名称	SHOW TABLES;
查看某个表结构	DESC 表名;

		* U(UPDATE)修改

需求	命令
修改表名	ALTER TABLE 旧表名 RENAME TO 新表名;
修改表的字符集	ALTER TABLE 表名 RENAME CHARACTER SET gbk;
在表中添加一列	ALTER TABLE 表名 ADD 列名 数据类型 【约束条件】;
修改列名称和类型【约束】	ALTER TABLE 表名 CHANGE 列名 新列名 数据类型【约束条件】;
修改列类型	ALTER TABLE 表名 MODIFY 列名 数据类型【约束条件】;
创建表后添加外键约束	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名) REFERENCES 管联的数据表(表中的列名));
添加外键约束并设置级联更新	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名) REFERENCES 管联的数据表(表中的列名)) ON UPDATE CASCADE;
添加外键约束并设置级联删除(危险)	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名) REFERENCES 管联的数据表(表中的列名)) ON DELETE CASCADE;

		* D(DELETE)删除

需求	命令
删除数据库【判断是否存在】	DROP TABLE【if exists】 数据库名;
删除表中的唯一约束	ALTER TABLE 表名 DROP INDEX 字段名;
删除表的主键约束	ALTER TABLE 表名 DROP PRIMARY KEY;
删除外键约束条件	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

2） DML（Data Manipulation Language）

	对表中数据的插入、删除和修改。

1.添加数据

需求	命令
向表中添加数据	INSERT INTO 表名 (字段名1,字段2,…字段n) VALUES(字段对应的值);

2.删除数据

需求	命令
删除表中所有数据(不推荐)	DELETE FROM 表名;
删除表，并创建一个一模一样的空表(推荐)	TRUNCATE TABLE 表名;
按条件删除数据	DELETE FROM 表名 WHERE 条件;

3.修改数据

需求	命令
按条件修改表中字段的数据	UPDATE orders SET 列名 = 值 , 列名 = 值 WHERE id = 168 条件;

3） DQL（Data Query Language）

查询表中的数据。

1.查询数据

需求	命令
查询表中所有数据	select * from 表名;
查询表中几个字段	select 列名1,列名2,…列n from 表名;
查询表中去除重复后的数据	SELECT DISTINCT 列名n FROM 表名;
查询表中数据并计算【起别名】	SELECT 列名,列名,IFNULL(列名,0)+IFNULL(列名,0) 【AS 别名】 FROM 表名;
按条件查询数据	select * from 表名 WHERE 条件;
查询并排序 【排序规则】	select * from 表名 WHERE 条件 ORDER BY 排序列名 【ASC 升序 默认】【DESC 降序】;
查询列计算个数（排除null）	SELECT COUNT(列名【*】) FROM 表名
查询列最大值	SELECT max(列名【*】) FROM 表名
查询列最小值	SELECT MIN(列名【*】) FROM 表名
查询列和	SELECT sum(列名【*】) FROM 表名
查询列平均值	SELECT avg(列名【*】) FROM 表名
查询并分组	SELECT 分组字段，聚合函数 FROM 【where 条件】 GROUP BY 分组字段 【HAVING 聚合函数结果判断】
查询并分页（MYSQL特有）	SELECT * FROM 表名 LIMIT 开始的索引（从第几个开始展示）,一页显示几条。

2.多表查询数据

需求	命令
查询两个表的笛卡尔积并消除无用数据（隐式内连接）	SELECT * FROM 表名1，表名二 WHERE 条件;
查询两个表的笛卡尔积并消除无用数据（显示内连接）	SELECT * FROM 表名1 【INNER】 JOIN 表名二 ON 条件;
外连接查询	SELECT * FROM 表名1 left/right【OUTER】 JOIN 表名二 ON 条件;
子查询	略;

4） DCL(Data Control Language)

数据库控制语言。是用来设置或更改数据库用户或角色权限。

1.增加用户

需求	命令
增加一个用户	CREATE USER ‘用户名’@‘主机名’;

2.删除用户

需求	命令
删除一个用户	DROP USER ‘用户名’@‘主机名’;

3.修改用户

需求	命令
修改一个用户	UPDATE USER SET password = password(密码) where user = 用户名;

4.权限

需求	命令
查询用户权限	show grants for ‘用户名’@‘主机名’;
修改用户权限	grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
撤销用户权限	REVOKE 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;

5） 条件与约束

1.条件

条件	命令
并且	&&,AND
或者	OR,||
查询id字段在20-30之间	SELECT * FROM 表名 WHERE id BETWEEN 20 AND 30;
查询id字段时20或30或25的	SELECT * FROM 表名 WHERE id IN(20.30.25);
查询id字段是null的	SELECT * FROM 表名 WHERE id IS NULL;
查询id字段不是null的	SELECT * FROM 表名 WHERE id IS NOT NULL;
查询id字段不是null的	SELECT * FROM 表名 WHERE id IS NOT NULL;
模糊查询	SELECT * FROM 表名 WHERE id LIKE ‘%_’;
%	表示字数不限，内容不限。如：‘张%’，表示以张开始的所有数据
_	表示占位，内容不限，字数为一。如：‘张_’,表示张某。

2.约束

约束	命令
非空	NOT NULL;
唯一约束	UNIQUE;(null表示不确定，所以null可以重复)
主键约束	PRIMARY KEY;(每个表中只能有一个)
自动增长	AUTO_INCREMENT;
外键约束	foreign key

二、事务

1） 基本事务

多条sql语句组成的业务，如转账。mysql中是自动提交的，但是如果开启事务后需要手动提交。

需求	命令
查看事务默认提交方式	SELECT @@autocommit; --1（自定提交），0（手动提交）
设置事务提交方式	set @@autocommit = 1/0;

1 实现事务的步骤

开启事务 START TRANSACTION;
众多sql语句 …;
事务有问题回滚事务 ROLLBACK;
事务没有问题提交事务 COMMIT;

2 事务的四大特征

务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

3 事务的隔离级别

四种隔离级别

1.读未提交（Read uncommitted）：

这种事务隔离级别下，select语句不加锁。

此时，可能读取到不一致的数据，即“读脏 ”。这是并发最高，一致性最差的隔离级别。

2.读已提交（Read committed）：

可避免 脏读 的发生。

在互联网大数据量，高并发量的场景下，几乎 不会使用 上述两种隔离级别。

3.可重复读（Repeatable read）：

MySql默认隔离级别。

可避免 脏读 、不可重复读 的发生。

4.串行化（Serializable ）：

可避免 脏读、不可重复读、幻读 的发生。

5.查询和设置数据库的隔离级别：

select @@tx_isolation;

set tx_isolation=‘隔离级别’;