MySQL数据库：（1）SQL语法

一、SQL分类

（1）DDL（Data Definition Language）数据定义语言：
　　　　用来定义数据库对象
（2）DML（Data Manipulation Language）数据操作语言：
　　　　用来对数据库中表的数据进行增删改查
（3）DQL（Data Query Language）数据查询语言：
　　　　用来查询数据库中表的记录（数据）
（4）DCL（Data Control Language）数据控制语言：
　　　　用来定义数据库的访问权限和安全级别，及创建用户

 

二、DDL：操作数据库、表

1. 操作数据库：CRUD

（1）C（Creat）：创建

• 创建数据库：

   CREATE DATABASE 数据库名称；

• 创建数据库,判断不存在，再创建：

   CREATE DATABASE IF NOT EXISTS 数据库名称；

• 创建数据库，并指定字符集：

   CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称；

（2）R（Retrieve）：查询

• 查询所有数据库的名称：

	SHOW DATABASES;

• 查询某个数据库的字符集：

	SHOW CREATE DATABASE 数据库名称;

（3）U（Update）：修改

• 修改数据库的字符集：

	ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;

（4）D（Delete）：删除

• 删除数据库：

	DROP DATABASE 数据库名称;

• 判断数据库是否存在，存在再删除数据库：

	DROP DATABASE IF EXISTS 数据库名称;

（5）使用数据库

• 查询当前正在使用的数据库：

	SELECT DATABASE();

• 使用数据库：

	USE 数据库名称;

2.操作表

（1）C（Creat）：创建

• 创建表：

	CREATE TABLE 表名(
		列名1 数据类型1,
		列名2 数据类型2,
		...
		列名n 数据类型n
	);

• 复制表：

	CREATE TABLE 表名 LIKE 被复制的表名;

注：

数据类型	关键字
整数类型	int
小数类型	double
日期（只包含年月日）	date
日期（包含时分秒）	datetime
时间戳类型	timestamp
字符串	varchar

（2）R（Retrieve）：查询

• 查询某个数据库中所有表的名称：

	SHOW TABLES;

• 查询表结构：

	DESC 表名;

（3）U（Update）：修改

• 修改表名：

	ALTER TABLE 表名 RENAME TO 新的表名;

• 修改表的字符集：

	ALTER TABLE 表名 ADD 列名 数据类型;

• 添加一列：

	ALTER TABLE 表名 CHARACTER SET 字符集名称;

• 修改列名称、列的数据类型：

	ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
	--或者
	ALTER TABLE 表名 MODIFY 列名 新数据类型;

• 删除列：

	ALTER TABLE 表名 DROP 列名;

（4）D（Delete）：删除

• 删除表：

	DROP TABLE 表名;

• 判断表是否存在，存在再删除表：

	DROP TABLE IF EXISTS 表名;

 

三、DML：增删改表中的数据

1.添加数据

		INSERT INTO 
			表名
			(列名1,列名2,...,列名n)
		VALUES
			(值1,值2,...,值n);

注：

• 列名和值要一一对应；
• 如果表名后，不定义列名，则默认给所有列添加值；
• 除了数字类型，其他类型需要使用引号引起来；

2.删除数据

（1）删除满足条件的数据：

		DELETE FROM 
			表名 
		WHERE 
			条件;

（2）删除所有数据（先删除表，然后再创建一张一样的表）：

		TRUNCATE TABLE 表名;

3.修改数据

		UPDATE 
			表名
		SET
			列名1 = 值1,
			列名2 = 值2,
			...
			列名n = 值n
		WHERE
			条件;

 

四、DQL：查询表中的记录

1.查询语法

		SELECT 
			字段列表
		FROM
			表名列表
		WHERE
			条件列表
		GROUP BY
			分组字段
		HAVING
			分组之后的条件
		ORDER BY
			排序
		LIMIT
			分页限定;

2.基础查询

（1）多个字段的查询：

		SELECT
			字段1,
			字段2,
			...
		FROM
			表名;

注：如果查询所有字段，则可以使用*来代替字段列表。

		SELECT * FROM 表名;

（2）去除重复：

		DISTINCT

（3）计算列：
一般可以使用四则运算计算一些列的值

		/**
			表达式1：哪些字段需要判断是否是null
			表达式2：如果该字段为null后的替换值
		*/
		IFNULL(表达式1,表达式2):

（4）起别名：

		AS

3.条件查询

（1）where子句后跟条件
（2）运算符：
　　　　> 、< 、<= 、>= 、= 、<>
　　　　BETWEEN ... AND
　　　　IN(集合)
　　　　LIKE:模糊查询。占位符："_"表示单个任意字符；"%"表示任意多个字符;
　　　　IS NULL
　　　　AND 或 &&
　　　　OR 或 ||
　　　　NOT 或 !

4.排序查询

（1）语法：

		ORDER BY 
			排序字段1 排序方式1,
			排序字段2 排序方式2,
			...

（2）排序方式：

• ASC:升序，默认的
• DESC:降序
  注：若有多个排序条件，则当前面的条件值一样时，才会判断第二条件。

5.聚合函数

（1）定义：　
　　将一列数据作为一个整体，进行纵向的计算
（2）内容：

• count（）：计算个数（一般选择非空的列——主键）
• max（）：计算最大值
• min（）：计算最小值
• sum（）：计算和
• avg（）：计算平均值

注：聚合函数的计算，排除null值。

6.分组查询

（1）语法：

	 group by 分组字段;

（2）分组后查询的字段：
　　分组字段、聚合函数
（3）where和havin的区别：

• where在分组之前进行限定，若不满足条件，则不参与分组；having在分组之后限定，若不满足结果，这不会被查询出来。
• where后不可以跟聚合函数，having可以进行聚合函数的判断。

7.分页查询

（1）语法：

	limit 开始的索引,每页查询的条数;

（2）公式：
　　　　开始的索引 = （当前页码 - 1）* 每页显示的条数

8.约束

• 概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

（1） 主键约束：primary key

• 创建表时添加约束

	CREATE TABLE 表名(
			列名 数据类型 PRIMARY KEY
	);

• 创建完表后添加主键

	ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;

• 主键的自动增长：如果某列为数值类型，使用auto_increment可以用来完成值的自动增长

	--在创建表时，添加主键约束，并完成主键自增长
	CREATE TABLE 表名(
		列名 数据类型 AUTO_INCREMENT
	);

	--删除自增长
	ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;

• 删除主键

	ALTER TABLE 表名 DROP PRIMARY KEY;

注：主键就是表中记录的唯一标识。主键非空且唯一，一张表只能有一个字段为主键。
（2）非空约束：not null

• 创建表时添加约束

	CREATE TABLE 表名(
		列名 数据类型 NOT NULL
	);

• 创建完表后添加约束

	ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;

• 删除非空约束

	ALTER TABLE 表名 MODIFY 列名 数据类型;

（3）唯一约束：unique

• 创建表时添加约束

	CREATE TABLE 表名(
		列名 数据类型 UNIQUE
	);

注：MySQL中，唯一约束限定的列的值可以有多个null。

• 创建完表后添加约束

	ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;

• 删除约束

	ALTER TABLE 表名 DROP INDEX 列名;

（4）外键约束：foreign key
外键约束让表和表产生关系，从而保证数据的正确性。

• 创建表时添加外键

	CREATE TABLE 表名(
		CONSTRAINT 外键名称 FOREIGN KEY REFERENCES 主表名称
	);

• 创建完表后添加外键

	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY REFERENCES 主表名称;

• 删除外键

	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

• 级联操作
  级联更新：ON UPDATE CASCADE
  级联删除：ON DELETE CASCADE

	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY REFERENCES 主表名称 ON UPDATE CASCADE ON DELETE CASCADE;

 

五、多表查询

1.查询语法

	SELECT
		列名列表
	FROM
		表名列表
	WHERE
		...

2.多表查询的分类

（1）内连接查询

• 隐式内连接：使用where条件消除无用数据

	SELECT
		t1.name,
		t1.gender,
		t2.name
	FROM
		emp t1,
		dept t2
	WHERE
		t1.'dep_id' = t2.'id';

• 显示内连接

	SELECT
		字段列表
	FROM
		表名1
	JOIN
		表名2
	ON
		条件;

（2）外连接查询

• 左外连接：查询的是左表所有数据及其交集部分。

	SELECT
		字段列表
	FROM
		表名1
	LEFT JOIN
		表名2
	ON
		条件;

• 右外连接：查询的是右表所有数据及其交集部分。

	SELECT
		字段列表
	FROM
		表名1
	RIGHT JOIN
		表名2
	ON
		条件;

（3）子查询： 查询中嵌套查询，称嵌套查询为子查询。
 

六、事务

1.事务的基本介绍

（1）概念：如果一个包含多个步骤的业务操作被事务管理，那么这些操作要么同时成功，要么同时失败。
（2）操作：

• 开启事务

	START TRANSACTION;

• 回滚

	ROLLBACK;

• 开启事务

	COMMIT;

（3）事务的提交：

• 自动提交：
  　　MySQL就是自动提交的；一条DML（增删改）语句会自动提交一次事务。
• 手动提交：
  　　需要先开启事务，再提交。
• 修改事务的默认提交方式：

	--查看事物的默认提交方式
	SELECT @@AUTOCOMMIT; -- 1 代表自动提交  0 代表手动提交

	--修改事务的默认提交方式
	SET @@AUTOCOMMIT = 0;

2.事务的四大特征

（1）原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
（2）持久性：当事务提交或回滚后，数据库会持久化的保存数据。
（3）隔离性：多个事务之间，相互独立。
（4）一致性：事务操作前后，数据总量不变。

3.事务的隔离

（1）事务隔离产生的问题：

• 脏读： 一个事务读取到另一个事务中没有提交的数据。
• 虚读（不可重复读）： 在同一个事务中，两次读取到的数据不一样。
• 幻读： 一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

（2）隔离级别：

• 读未提交

	--产生的问题：脏读、虚读、幻读
	READ UNCOMMITTED;

• 读已提交

	--产生的问题：虚读、幻读
	READ COMMITTED;

• 可重复读

	--产生的问题：幻读
	REPEATABLE READ;

• 串行化

	--可以解决所有问题
	SERIALIZABLE;

（3）隔离级别的查询和设置

• 查询隔离级别

	SELECT @@TX_ISOLATION;

• 设置隔离级别

	SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别字符串;