MySQL的介绍和SQL语句和DOS命令

目录

1）.MySQL的三层结构图
2）.数据库的操作
3）.数据库中表的操作
4）.数据库中表的约束
5）.数据库中的索引

1）.MySQL的三层结构：

2）.数据库的操作

1.创建数据库SQL语句
2.显示当前数据库服务器中的所有数据库的SQL语句
3.查看创建数据库的语句的SQL语句
4.删除数据库的语句
5.备份数据库命令（在DOS执行！）
6.恢复数据库命令（进入了mysql中再执行）
7.备份数据库中表的命令（在DOS执行）
8.指定数据库进行操作的命令
9.恢复数据库中表的步骤

1. 创建数据库：

创建数据库SQL语句格式：CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET utf8][ COLLATE utf8_bin]sd;，
[]包裹的可要可不要！
[IF NOT EXISTS] :不写的话如果有该数据库就会报错！
[CHARACTER SET utf8]:指定数据库采用的字符集，如果不指定字符集，默认utf8
[COLLATE]:指定数据库字符集的校对规则,在进行该数据库的表查询数据的时候会根据指定的校对规则查询，数据库里面表如果没有指定表字符集的校对规则，默认用数据库的校对规则（utf8_bin[区分字母大小写]，utf8_general_ci[不区分大小写]（默认utf8_general_ci））**

• utf8_bin[区分字母大小写]
  
• utf8_general_ci[不区分大小写]
  

2. 显示当前数据库服务器中的所有数据库的语句

SQL语句格式：SHOW DATABASES;

3. 查看创建数据库的语句的SQL语句：

SQL语句格式：SHOW CREATE DATABASE 数据库名;**

`db1`两边的反单引号是数据库名为了防止与数据库的关键字冲突，起规避作用，这样可以用关键字起名，
`位置在键盘中数字键“1”的左边，其上档符号是“~ ”
/*!40100*/ 是指代表在4以上数据库版本才执行这里面默认的默认字符集等语句

4. 删除数据库的语句(慎用！) ：

SQL语句格式：DROP DATABASE [IF EXISTS] 数据库名;

5 备份数据库命令（在DOS执行！）：

SQL语句格式：mysqldump -u root -p密码 -B 数据库1 数据库2 数据库n > 绝对路径+文件名.sql;**
**语句解析：-p后面输密码不用带空格，-B后面可以接多个数据库，> 导出符号 ，文件名.sql这是一个保存SQL语句的SQL文件，备份到这个文件，其实文件里面就是一个创建数据库和一些表的一些SQL语句。

6.恢复数据库命令（进入了mysql中再执行）：

SQL语句格式：Source 绝对路径+文件名.sql;
还有一种方法，其实可以直接把这个sql文件里面的内容执行**

7.备份数据库中表的命令（在DOS执行）：

SQL语句格式：mysqldump -u root -p密码 数据库名 表1 表2 表n > 绝对路径+文件名.sql;

8.指定数据库进行操作的命令：`

SQL语句格式：use 数据库名;

9.恢复数据库中表的步骤：

SQL语句格式：1. 先指定要恢复表的数据库use 数据库名;，
2.然后用恢复命令 Source 绝对路径+文件名.sql;

3）.数据库中表的操作

0.列类型的介绍
1.创建和删除表的SQL语句
2.修改表结构的SQL语句
3.操作表数据的SQL语句(增删改查)
4.查询中的函数SQL语句
5.加强查询中的函数SQL语句
6.分页查询
7.分组查询增强
8.多语句查询SQL语句格式
9.多表内连接查询
10.自连接查询
11.单行子查询和多行子查询
12.子查询临时表!
13.all(全部都) 和 any（一些，指最低）
14.多列表子查询（针对于多列单行数据）
15.表数据复制
16.创建表并复制其它表结构
17.表去重步骤

0.列类型介绍：

每个列类型可以在创建表的时候设置非空和默认值
SQL语句格式：CREATE TABLE 表名 (列名1 TINYINT UNSIGNED NOT NULL DEFAULT 默认值,列名2 列类型2 NOT NULL DEFAULT 默认值,列名n 列类型n NOT NULL DEFAULT 默认值)[character set 字符集] [collate 校对规则] [engine 存储引擎];

1. 数值型

• 数值型（整数）：使用规范：在满足需求的情况下，尽量选择占用空间内存小的
  默认是带符号的整数，如果列类型需要存储的是无符号的数值（全是正数），需要在创建表的时候在列类型关键字后面加一个unsigned关键字！
  SQL语句格式：CREATE TABLE 表名 (列名1 TINYINT UNSIGNED,列名2 列类型2,列名n 列类型n )[character set 字符集] [collate 校对规则] [engine 存储引擎];

常用类型	字节大小	带符号的范围(默认)	不带符号的范围（unsigned）
TINYINT	1字节	-128~127	0-255
SMALLINT	2字节
MEDIUMINT	3字节
INT(常用)	4字节
BIGINT	8字节

• 数值型（bit(M)位数）
  SQL语句格式：CREATE TABLE 表名 (列名1 BIT(M),列名2 列类型2,列名n 列类型n )[character set 字符集] [collate 校对规则] [engine 存储引擎];
  细节：1. 显示的时候，按照位的方式(二进制)显示
  2. M表示位数，默认值为1，范围1-64（0-8字节）
  3. 如果一个值只有0和1，可以使用bit(1).节省空间

• 数值型（小数）
  SQL语句格式：CREATE TABLE 表名 (列名1 DOUBLE UNSIGNED,列名2 列类型2,列名n 列类型n )[character set 字符集] [collate 校对规则] [engine 存储引擎];

常用类型	字节大小	带符号的范围(默认) 不带符号的范围（unsigned）的小数点后小数位数
FLOAT	4字节	整数位加小数位一起6位（跟Java一起为8位有点不一样）
DOUBLE（常用）	8字节	小数位为16位
DECIMAL[M,D] （常用）	M代表整数位加小数位总位数，默认10位，最大65位	D代表小数位,默认0位，最大30位，当没达到指定的D位数时，会在后面补0

2. 文本字符串型

常用类型	size含义	范围	缺点	优点	选择
CHAR(size)	指定输入的字符(英文或汉字)个数	0~255个字符	因为是固定长度的，当输入字符数少于指定字符数，多出来的会浪费空间	查询速度比VARCHAR快	数据定长，如身份证号，邮箱号等，选这个！
VARCHAR(size)	指定输入的字符(英文或汉字)个数	0~65535（2^16）个字节	本身需要占用1-3个字节来记录存放内容长度，当字符集指定是utf8，最大只能指定字符数size为(65535-3）/3=21844个	由于是可变长度的，按照实际占用空间+1~3个字节去存储，比CHAR节省空间	一般用这个
TEXT		0~65535（2^16）个字节		没有多占用1~3个字节,也是按照实际占用空间大小存储	想简单点，也可以选这个
MEDIUMTEXT		0~2^24字节			当需要存储更多的字符时，选择这个
LONGTEXT		0~2^32字节			当需要存储更更多的字符时，选择这个

3. 日期类型
   SQL语句格式：CREATE TABLE 表名 ( 列名 DATA ,列名2 DATATIME, 列名3 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

常用类型	介绍
DATA	表示年月日
DATATIME	表示年月日 时分秒
TIMESTAMP	表示记录当前时间的时间戳（需要加格式中TIMESTAMP后面的语句，表示当没有往里面填数据时，会自动填入当前的时间）

4. 二进制类型
   SQL语句格式：CREATE TABLE 表名(列名 BLOB,列名2 LONGBOLB);

二进制类型	范围大小
BLOB	0~2^16-1个字节
LONGBLOB	0~2^32-1个字节

1. 创建和删除表的语句

前提：必须先指定创建表的数据库，用指定数据库进行操作的命令
创建表SQL语句格式：CREATE TABLE 表名 (列名1 列类型1,列名2 列类型2,列名n 列类型n )[character set 字符集] [collate 校对规则] [engine 存储引擎];
列类型：后面有细节！
[character set 字符集]：如果不指定，就默认所在的数据库的字符集；
[collate 校对规则]：如果不指定，就默认所在的数据库的校对规则；
[engine 存储引擎]：引擎：后面有细节！

删除表SQL语句格式：DROP TABLE 表名;

2. 修改表结构的SQL语句

• 查看表的列信息结构的SQL语句：desc 表名;
• 修改表名的SQL语句：Rename table 表名 to 新表名;
• 查看创建表的SQL语句的SQL语句：show create table 表名;
• 修改表字符集的SQL语句: ALTER TABLE 表名 character set 字符集;
• 添加列的SQL语句： ALTER TABLE 表名 ADD (添加的列名1 列类型1,添加的列名n 列类型n) [NOT NULL DEFAULT ' '] [AFTER 在哪个列之后的列名]; 只添加一列时不需要加()!
• 修改列的属性SQL语句:ALTER TABLE 表名 MODIFY 需要修改的列名 列类型; ！
• 修改列名：ALTER TABLE 表名 CHANGE 旧列名 新列名 新列类型;
• 删除列的SQL语句:ALTER TABLE 表名 DROP 需要删除的列名;

3. 操作表数据的SQL语句（增删改查CRUD）

判断是否为NULL用IS!

• 增加数据（insert）
  添加数据的SQL语句：INSERT INTO 表名 (列名1，列名n) VALUES (数据1，数据n);
  细节： 1. 插入的数据的类型应与列名的列类型相同；
  2. 字符和日期的列类型数据用单引号包含，数值类型不用
  3. 如果列类型在创建表的时候没有设置为NOT NULL，那么可以插入空值
  4. value后面可以多几个括号，可以一次添加多条记录
  5. 当列没有添加数据，就会存储默认数值，默认数值可以在创建表的时候指定列的DEFAULT 默认值；

• 删除数据（delete）
  删除一条数据的SQL语句：DELETE FROM 表名 [WHERE 指定要修改的列名=对应标记这一条数据一个列数据]
  细节：1. 如果无WHERE条件筛选语句，就是删除表中所有数据，
  2.不能单单删除某一列的某个值，只能用修改SQL语句去变成默认值。

• 修改数据（update）
  修改数据的SQL语句：UPDATE 表名 SET 列名1=修改的值1,[列名n=修改的值n] [WHERE 指定要修改的列名=对应标记这个要修改的这一段数据的一个列数据]
  细节:
  1.如果没有指定WHERE这个条件筛选语句，就是改这个列名的所有数据为修改的值
  2.set后面是哪些列要修改数据的，用where后面的条件筛选语句去改这一列的某个数据，

• 查询数据（select）
  单表基本用法
  1.在一个单表中查询某一列的所有数据的基本SQL语句：SELECT [DISTINCT] */需要查询的列名1，需要查询的列名n FROM 表名;
  细节：1.*代表查询所有列，其实查询就是select后面的列，并将其显示出来！
  2. [DISTINCT]，指当这几条数据，每列的数据都相同的时候，就留下一条，去重化。
  2.在一个单表中查询使用表达式对列与列之间进行运算的SQL语句：SELECT 列名运算表达式 FROM 表名;
  3.在一个单表中在SELECT语句中使用as设置列别名的SQL语句:SELECT 列名 as 列别名 FROM 表名;
  4.在一个单表中查询某一个对象(不单指人)的所有数据的SQL语句： SELECT * FROM 表名 WHERE 条件筛选语句;
  5.在一个单表中查询并排序显示出来结果的SQL语句：SELECT 列名1，列名n FROM 表名 ORDER BY 需要排序的列名 asc/desc;
  细节：1.ORDER BY后指定需要排序的列，
  2.ASC 升序（默认），DESC降序
  3.ORDER BY 位于结尾；

• WHERE 条件筛选语句中的运算符（第4种查询语句后的WHERE)

运算符类别	符号	含义
比较运算符	>,<,<=,>=,=,!=/<>	大于小于啥的
比较运算符	BETWEEN 数值 AND 数值	显示某一区间的值
比较运算符	in (值1,值2,值n)	显示在in列表的值的对象
比较运算符	LIKE ‘胡%‘ / NOT LIKE’ ’	模糊查询 ,以胡开头的
比较运算符	IS NULL	判断是否为空
逻辑运算符	and	多个条件同时成立
逻辑运算符	or	多个条件任一个成立
逻辑运算符	not	不成立

4. 函数（在增删改查SQL 语句中可使用）**

1.统计函数(针对数值类型的列)
2.字符串函数(针对字符串类型的列)
3.数学函数（针对单一的数字）
4.日期函数(对日期类型的列进行处理)
5.加密和获取用户名称和地址和当前数据库名称的函数(对密码进行处理)
6.流程控制语句)

1. 查询语句中的统计数值函数的SQL语句

函数含义	函数SQL语句	统计函数名	列举的SQL语句
统计列行数函数	COUNT(*)	根据条件统计对应全部行的总数，如果没有条件，就显示所有行数	SELECT COUNT(*) FROM 表名 [WHERE 条件];
统计列行数函数	COUNT(列名)	统计这一列全部行的总数（除开NULL值所在行）	SELECT COUNT(列名) FROM 表名 [WHERE 条件];
计算和的函数	SUM()	统计某一列的值的总和	SELECT SUM(列名1),[SUM(列名n)] FROM 表名 [WHERE 条件]
计算和的函数	SUM()	统计多列的值的总和	SELECT SUM(列名1+列名n) FROM 表名 [WHERE 条件]
计算平均数的函数	AVG()	统计某一列的值的平均数	SELECT AVG(列名1),[AVG(列名n)] FROM 表名 [WHERE 条件]
计算平均数的函数	AVG()	统计多列的值的总平均数	SELECT AVG(列名1+列名n) FROM 表名 [WHERE 条件]
比较最大值/最小值的函数	MAX/MIN(列名1)	统计列的最大值或最小值	SELECT MAX/MIN(列名1),[MAX/MIN(列名n)] FROM 表名 [WHERE 条件]

1.1.查询语句中的统计分组函数SQL语句：

函数含义	函数SQL语句	分组	SQL语句	细节
进行分组	GROUP BY 需要分组的列名	对列进行分组	SELECT 列名1，列名n，FROM 表名 GROUP BY 需要分组的列名;	因为分组是分成一组，所以只有一行数据，所以此时查询的列名下查询后的数据值只能存在一个，不然报错！
进行分组并过滤条件	GROUP BY 需要分组的列名 HAVING	对分组后的结果进行过滤	SELECT 列名1，列名n，FROM 表名 GROUP BY 需要分组的列名 HAVING 条件;	建议先简后繁，先写好对列分组，再写条件

2. 字符串函数(针对字符串类型的列)

函数含义	函数SQL语句	含义	SQL语句	细节
函数字符集	CHARSET(列名)	查询这一列的字符串的字符串集	SELECT CHARSET(列名) FROM 表名;	这里列举查询语句
连接字符串	CONCAT(列名,[‘字符串’],列名)	查询并将列与列之间的字符串连接起来	SELECT CONCAT(列名,['字符串'],列名) FROM 表名;
得到子字符串在字符串中位置	INSTR(‘字符串’,‘子字符串’)	查询子字符串在字符串中的位置	SELECT INSTR('字符串','子字符串') FROM DUAL;	DUAL是mysql默认的表,下标从1开始
将英文字符全部变成大写	UCASE(列名)	查询并将该列的英文字符串全部转成大写，中文不变	SELECT UCASE(列名) FROM 表名;
将英文字符全部变成大写	LCASE(列名)	查询并将该列的英文字符串全部转成小写，中文不变	SELECT LCASE(列名) FROM 表名;
字符串中取字符	LEFT/RIGHT(‘列名’,Length)	查询并从该列的左/右边字符串起取Length个字符	SELECT LEFT/RIGHT('列名',Length) FROM 表名;
得出字符串长度	LENGTH(列名)	查询该列每行字符串的长度（以字节为单位）	SELECT LENGTH(列名) FROM 表名;
替换字符串	REPLACE(列名,‘指定更换的字符串’,‘新字符串’)	查询并在列中新字符串替换指定要换的字符串	SELECT REPLACE(列名,'指定更换的字符串','新字符串') FROM 表名;
比较字符串大写	STRCMP(‘字符串1’,‘字符串2’)	查询并逐字符比较两字符串大小	SELECT STRCMP('字符串1','字符串2') FROM DUAL;	字符串1的字符的ASCII码大于字符串2字符的ASCII码返回1，等于返回0，小于返回-1
自定义取字符	SUBSTRING(列名,position,[length])	查询并从列的字符串position开始（从1开始），取length个字符	SELECT SUBSTRING(列名,position,[length]) FROM 表名;
去除左边/右边空格	LTRIM/RTRIM(列名)	查询并除去左端/右端空格	SELECT LTRIM/RTRIM(列名) FROM 表名;
同时去除左右两边空格	TRIM(列名)	查询并除去左右端的空格	SELECT TRIM(列名) FROM 表名;

3.数学函数（对数字）

函数含义	函数SQL语句	含义	SQL语句	细节
数字绝对值	ABS(数字)	查询其数字绝对值	SELECT ABS(数字) FROM 表名	这里列举查询语句
十进制变成二进制	BIN(十进制数字)	查询其数字十进制转二进制	SELECT BIN(十进制数字) FROM 表名
数字向上取整	CEILING(数字)	查询其数字向上取整	SELECT CEILING(数字) FROM 表名
数字进行进制转换	CONV(数字,从什么进制数,变成什么进制数)	查询其数字进制转换	SELECT CONV(数字,从什么进制数,变成什么进制数) FROM 表名
向下取整	FLOOR(数字)	查询其数字向下取整	SELECT FLOOR(数字) FROM 表名
设置小数保留的位数	FORMAT(数字,保留的小数位数)	查询其数字并设置显示的小数位数	SELECT FORMAT(数字,保留的小数位数) FROM 表名
数字转1十六进制	HEX(数字)	查询其数字转十六进制	SELECT HEX(数字) FROM 表名
求几个数的最小值	LEAST(数字1,数字2,数字n)	查询其数字最小值	SELECT LEAST(数字1,数字2,数字n) FROM 表名
求余数	MOD(被除数,除数)	查询其数字求余数	SELECT MOD(被除数,除数) FROM 表名
产生一个随机数	RAND()	查询其随机数	SELECT RAND() FROM 表名	查询一次变一次，且在0<=大小<=1内
产生一个随机数，之后不再改变	RAND(seed)	查询其随机数	SELECT RAND(seed) FROM 表名	指定seed后,多次查询数值不变，且在0<=大小<=1内

4.日期函数(对日期类型的列进行处理)

函数含义	函数SQL语句	操作	列举操作的SQL语句	细节
当前日期	CURRENT_DATE()	向列中增加当前日期	INSERT INTO 表名(日期类型的列名) VALUES(CURRENT_DATE());
当前时间	CURRENT_TIME()	向列中增加当前时间	INSERT INTO 表名(日期类型的列名) VALUES(CURRENT_TIME());	如果日期类型为datetime,日期也会
当前时间戳	CURRENT_TIMESTAMP()	向列中增加当前时间戳	INSERT INTO 表名(日期类型的列名) VALUES(CURRENT_TIMESTAMP());
返回日期类型的部分信息	日期部分名(日期类型的列名)	查询列中日期部分	SELECT DATE(日期类型的列名) FROM 表名;	日期部分名可以是Year/Month/Day/Hour/Minute/Second/date/time
在日期上加日期或者时间	DATE_ADD(日期类型的列名,INTERVAL 增加的日期数或时间数或日期时间数 对应的单位)	查询并向日期加多长日期或者时间	SELECT DATE_ADD(日期类型的列名,INTERVAL 增加的日期数或时间数或日期时间数 对应的单位) FROM 表名;	单位有：Year/Month/Day/Hour/Minute/Second
在日期上减日期或时间	DATE_SUB(日期类型的列名,INTERVAL 增加的日期数或时间数或日期时间数 对应的单位)	查询并向日期减多长日期或者时间	SELECT DATE_SUB(日期类型的列名,INTERVAL 增加的日期数或时间数或日期时间数 对应的单位) FROM 表名;	单位有：Year/Month/Day/Hour/Minute/Second
两个日期差(结果是天数)	DATEDIFF(日期1,日期2)	查询两个时期差	SELECT DATEDIFF(日期1,日期2) FROM 表名;	结果是天数,日期类型用单引号
两个时间差（结果是多少小时多少分钟多少秒）	TIMEDIFF(日期1,日期2)	查询两个时间差	SELECT TIMEDIFF(日期1,日期2) FROM 表名;	日期类型用单引号
当前时间	NOW()	查询当前时间	SELECT NOW() FROM 表名;	日期和时间都能显示
返回1970-1-1到现在的秒数	UNIX_TIMESTAMP()	查询1970-1-1到现在的秒数	SELECT UNIX_TIMESTAMP() FROM 表名;
将UNIX_TIMESTAMP()返回的秒数进行格式化日期操作	FROM_UNIXTIME(秒数,输出的日期格式)	查询并将1970-1-1到现在的秒数进行日期格式化	SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d %H:%i:%s');	%Y-%m-%d %H:%i:%s为日期格式 ，在实际开发中，经常使用int来保存一个unix的时间戳，然后用FROM_UNIXTIME进行转化为日期

5.加密函数

函数含义	函数SQL语句	列举函数操作	列举函数操作的SQL语句
得到用户的用户名和ip地址	USER()	增加用户的用户名和ip	INSERT INTO 表名 VALUES(USER());
得到当前数据库名称的函数	DATABASE()	查询当前使用的数据库名称	SELECT DATABASE();
为字符串算出一个MD5 32位的字符串，用于用户密码的加密	MD5(字符串)	添加一个经过MD5加密的密码进行保存	INSERT INTO 表名 VALUES(MD5(字符串));
为字符串算出一个加密字符串	PASSWORD()	添加一个经过加密的密码进行保存	INSERT INTO 表名(列名) VALUES(PASSWORD(字符串));

6.流程控制语句

函数含义	函数SQL语句	列举操作	列举操作的语句	细节
类似三元运算符	IF (条件语句,条件真执行的语句,条件假执行的语句)	如果列中的值是null,就显示0.0的语句	SELECT IF(列名 IS NULL,0.0,列名) FROM b1;	是用is，不是用is not
类似 if else if else if else控制语句	SELECT CASE WHEN 条件1 THRN 条件1为真执行的语句 WHEN 条件2 THEN 条件2为真执行的语句 ELSE 否则执行的语句 END;	如果列中的值大于80，就显示优秀，如果大于等于60，就显示合格，否则显示不及格	SELECT CASE WHEN 条件1>80 THEN '优秀' WHEN 条件2>60 THEN '合格' ELSE '不合格' END FROM b1;

5.查询加强和模糊查询SQL语句

操作符含义	SQL语句	列举操作	列举操作的语句	细节
在某个日期之后	日期类型列名>‘某个日期’	查询某个日期后入职的人	SELECT * FROM 表名 WHERE 日期类型列名>'某个日期';
模糊查询多个符%:表示0-到多个任意字符都可以	LIKE ‘陈%';	查询姓陈的所有人	SELECT * FROM 表名 WHERE name LIKE '陈%';
模糊查询单个符_:表示单个任意字符都可以	LIKE '_文%	查询第二个字为文的所有人	SELECT * FROM 表名 WHERE name LIKE '_文%';
多层排序	ORDER BY 列名1 ASC, 列名n DESC;	查询按照人员部门号升序而工资降序排列	SELECT * FROM 表名 ORDER BY 列名1 ASC,列名n DESC;	ASC 升序排序 DESC 降序排序

6. 分页查询

SQL语句：SELECT * FROM 表名 LIMIT 每页显示的行数 * (第几页-1),每页显示的行数;

7.分组增强

SQL语句：SELECT COUNT(*) FROM 表名 GROUP BY 列名;

8.多语句查询SQL语句格式

SQL语句：SELECT 列名1，列名2 FROM 表名 GROUP BY列名 HAVING 条件 ORDER BY 列名 排序方式 LIMIT start,行数;

9.多表内连接查询

当查询的需求需要用到两张表及以上时，就进行多表查询，多表查询的重点就是条件，因为如果单纯的是两张表查询并没有筛选条件时，就会变成一张笛卡尔积表，笛卡尔表达不到查询的要求，所以必须注明条件WHERE，根据关联条件，只显示所有匹配的记录，匹配不上的，不会显示！条件的最低个数=表的个数-1；
SQL语句为：SELECT 表名.列名1, 表名.列名n FROM 表名1,表名n WHERE 使表和表之间不形成笛卡尔表的条件;
两个表中满足条件的部分，只要有一个表中没有为null，就不显示那一行！
内连接另外一种形式SQL语句为：SELECT 表名.列名1, 表名.列名n FROM 表名1 inner join 表名n on 条件;

10.自连接查询

自连接：是指在同一张表上的连接查询（将一张表看作两张表）
细节：1.把同一张表当作两张表使用
2.表名使用时必须取表别名，这里取表别名不需要用AS关键字
3.列名不明确时，也可以指定列别名，需要用AS关键字
SQL语句：SELECT 列名1,列名n FROM 表名 表别名1, 表名 表别名2 WHERE 条件;

11.单行子查询和多行子查询

子查询是指嵌入在其它sql语句的select语句，也叫嵌套查询

子查询种类	SQL语句	细节
单行子查询	SELECT 列名1,列名n FROM 表名 WHERE 条件(条件中的SELECT语句,这个语句结果只有1个)
多行子查询	SELECT 列名1,列名n FROM 表名 WHERE 条件in(条件中的SELECT语句,这个语句结果有n个)	只要满足in中随便一个结果就可以,不代表全部！

12.子查询临时表!

子查询临时表:就是将查询出来的很多行数据当作一个临时表使用，内嵌在一个SELECT语句中使用
SQL语句:SELECT 列名1,列名n FROM (SELECT 列名1,列名n FROM 表名);

13.all(全部都) 和 any（一些，指最低）

SQL语句:SELECT 列名1,列名n FROM 表名 WHERE 条件all/any(条件中的SELECT语句,这个语句结果有n个)

14.多列表子查询（针对于多列单行数据）

SQL语句：SELECT 列名1,列名n FROM 表名 WHERE (列名1,列名n)=(SELECT 出来的对应多列单行数据);

15.表数据复制

SQL语句：INSERT INTO 复制后表名 SELECT * FROM 被复制表名;

16.创建表并复制其它表结构

SQL语句：CREATE TABLE 复制结构后表名 LIKE 被复制结构表名;

17.表去重步骤

1.创建一个缓存表temp，结构和需要去重的表结构一样 SQL语句：CREATE TABLE temp LIKE 原表;
2.将查询去重后的临时表数据复制到缓冲表中 SQL语句：INSERT INTO temp SELECT DISTINCT * FROM 原表;
3.删除需要去重的表所有数据 SQL语句：DELETE FROM 原表;
4.将缓冲表全部数据复制到需要去重的表 SQL语句：INSERT INTO 原表 SELECT * FROM temp;
5.删除缓冲表。 SQL语句：DROP TABLE temp;

18.合并查询

含义	SQL语句	细节
在一张表合并SELECT出来的多条数据，不会去除相同的数据	SELECT语句1 UNION ALL SELECT语句2	其两条SELECT语句搜出来的列名都必须相同
在一张表合并SELECT出来的多条数据，会去除相同的数据	SELECT语句1 UNION SELECT语句2	其两条SELECT语句搜出来的列名都必须相同

19.多表外连接查询

含义：：当两张表连接时，当需要查询出匹配条件之外的其中一张表全部记录时，就需要用到外连接！

含义	SQL语句	细节
左外连接：将左边的表的表信息全部显示出来	SELECT 列名1，列名n FROM 左边表 LEFT JOIN 右边表 ON 条件;
右外连接：将右边的表的表信息全部显示出来	SELECT 列名1，列名n FROM 左边表 RIGHT JOIN 右边表 ON 条件;

4）数据库中表的约束

1.主键约束(primary key)

一张表中只能有一个主键！
含义： 列在创建表的时候被主键约束了后，就表示这一列的每一个数据都是唯一标识每一行的数据！

类型和不同指定方式	SQL语句	细节
唯一主键指定的第一种方式,在列类型后指定	CREATE TABLE 表名 (列名1 列类型1 PRIMARY KEY);	被主键约束后的那一列不能有重复数据，且不能为null
唯一主键指定的第二种方式，在表定义最后指定	CREATE TABLE 表名 (列名1 列类型1,列名n 列类型n,PRIMARY KEY(列名));
复合主键指定的方式	CREATE TABLE 表名 (列名1 列类型1,列名n 列类型n,PRIMARY KEY(列名1,列名n));	被主键约束后的那两列不能同时有第二个重复数据，且不能为null

删除主键约束： ALTER TABLE 表名 DROP PRIMARY KEY;

2.非空约束(NOT NULL)

含义： 表示这一列的数据都不能为空
SQL语句：CREATE TABLE 表名 (列名1 列类型1 NOT NULL);

3.唯一约束(UNIQUE)

含义： 表示这一列的数据都不能重复
SQL语句：CREATE TABLE 表名 (列名1 列类型1 UNIQUE,列名n 列类型n UNIQUE);
细节： 1.如果创建表的时候没有指定not null，那么unique那一列可以有多个null
2.一张表可以有多个unique约束的列
删除唯一约束：ALTER TABLE 表名 DROP INDEX 列名;

4.外键约束(FOREIGN KEY)

含义: 用于定义主表和从表的关系，外键约束在从表的列上，主表对应的列必须有主键约束或者unique约束，当从表的列定义外键约束后，这个列上的数据必须在主表的对应的列上存在或者可以为null！
细节： 1.删除主表上被从表上外键约束的列时，必须先保证从表上没有一个数据在主表上存在，不然删除不了，必须先在从表上删除掉主表上需要删除的那个数据，主表才能顺利删除！
2.需要先有主表且那一列必须为主键或者unique，从表上才能进行外键约束！
SQL语句:CREATE TABLE 表名 (列名1 列类型1,列名n 列类型n,FOREIGN KEY (需要外键约束的列名) REFERENCES 主表名(被外键指定的列名));

删除外键约束：ALTER TABLE 表名 DROP FOREIGN KEY (列名);

5.check约束（限制列数据的条件）

含义：对约束的列数据进行检查（必须要在条件的范围内）才能保存，mysqk5.7暂不支持这个SQL语句！
SQL语句：CREATE TABLE 表名 (列名1 列类型1,列名n 列类型n CHECK(限制列数据的条件);

6.自增长

含义：添加一行数据，自动在定义了自增长的列进行+1填入
SQL语句：CREATE TABLE 表名 (列名1 列类型1 primary key auto_increment,列名n 列类型n UNIQUE auto_increment);
细节：
1.增加自增长的一行数据的方式
1.1.insert into 表名(列名1(是加了自增长的),列名2) values(null,'值2');
1.2.insert into 表名(列名2) values('值2');
1.3.insert into 表名 values(null,'值2');
使用细节：
2.一般来说自增长是和primary key 配合使用
3.自增长也可以单独使用（需要配一个unique）
4.自增长修饰的列为整数型的（小数也可以，但是很少用）
5.自增长默认从1开始，SQL语句为：alter table 表名 auto_increment = 新的开始值;
6.如果你添加数据时，给自增长列指定值了，后面增加1以定的值为准.

5）.数据库中的索引

1.索引介绍：提高数据库查询性能，不用增加内存，不用改程序，不用调sql，使得查询速度大大提高！

2.索引的原理：

2.1.当没有建立索引时，查询某条记录，需要对全表进行扫描，查询速度慢.
2.2.使用索引后（机制）：形成一个索引数据结构，使得查询速度大大提高!例如：二叉树数据结构
2.3.索引的缺点:占用磁盘空间；对dml（update delete insert）SQL语句的效率产生影响，因为dml语句会重新使得二叉树结构发生变化，需要一定的时间处理维护，对速度产生影响！但是实际开发中，dml语句使用频率远远低于查询语句，所以是利大于弊！

3.创建索引的SQL语句

索引的类型	SQL语句	细节
增加主键索引	ALTER TABLE 表名 ADD PRIMARY KEY(列名);	可以在创建表的时候就指定好主键，这时候就不用创建主键索引
增加唯一索引	CREATE UNIQUE INDEX 索引名 ON 表名(列名);	如果这一列的数据每个不能重复时，就用唯一索引
增加普通索引	CREATE INDEX 索引名 ON 表名(列名); ALTER TABLE 表名 ADD INDEX 索引名(列名);	如果这一列的数据每个可以允许重复时，就用普通索引
删除索引	DROP INDEX 索引名 ON 索引所在的表名;
删除主键索引	ALTER TABLE 表名 DROP PRIMARY KEYl;
查询表中是否有索引	SHOW INDEX FROM 表名;

4.创建索引的选择

1.较频繁的作为查询条件列名的应该创建索引
2.唯一性太差的列名不适合单独创建索引，如性别
3.更新非常频繁的字段不适合创建索引，因为创建索引后，会经常改变索引数据结构
4.不会出现在WHERE条件中不应该创建索引！

6）事务

ACID原则:1.原子性；2.一致性；3.隔离性（隔离机制）；4.持久性（一旦提交，结果不会再被影响）。

1.事务的含义：用于保证数据的一致性，由一组dml语句组成，这组dml语句要么全部成功，要么全部失败：如转账。

2.细节：

2.1当执行事务操作时，mysql会在表上加锁，防止其它用户改表的数据！
2.2如果不开始事务，默认情况下，dml操作自动提交，不能回滚；
2.3可以创建多个保存点;
2.4mysql的事务机制需要innodb的存储引擎才可以使用，myisam不好用。

3.几个重要操作

含义	命令
开始一个事务，执行dml操作	start transaction;
保存一个点名，可以用于取消部分事务	savepoint 点名;
回退到一个点，取消部分事务	rollback to 点名;
回退全部事务	rollback;
提交事务，删除所有保存点，释放锁，数据生效，其它客户端将可以看到新生效的数据	commit;

7)隔离级别

1.三种异常情况

异常情况名字	异常情况含义解读
脏读(Dirty read)	当一个客户端的事务开始并执行dml语句修改表数据，但没有提交时，另一个客户端的事务开始后却能查询到他这个没有提交的dml语句修改后的表的数据！
不可重复读(Non repeatable read)	当其它客户端的事务开始并执行dml语句修改表数据，提交后，另一个客户端的事务开始后由于其它客户端的事务提交的修改(update)或删除(delete)而导致自己事务返回不同的表数据 !
幻读(Phantom read)	当其它客户端的事务开始并执行dml语句修改表数据，提交后，另一个客户端的事务开始后由于其它客户端的事务提交的增加(insert into)而导致自己事务返回不同的表数据 !

2.事务隔离级别

隔离级别	脏读	不可重复读	幻读	加锁读	细节
读未提交(Read uncommitted)	可能出现	可能出现	可能出现	不加锁
读已提交(Read committed)	不可能出现！	可能出现	可能出现	不加锁
可重复读(Repeatable read) （默认,一般不更改，满足大部分需求）	不可能出现！	不可能出现！	不可能出现！	不加锁	只展示在我事务开始前的表数据，其它客户端事务在我事务开始后所提交的新表数据全部都不予在我事务的表数据展示！
可串行化(Serializable)	不可能出现！	不可能出现！	不可能出现！	加锁	一个事务如果再处理这个表，那么会在这个表上加了锁，每次只允许一个事务进行操作表数据，只有等一个事务提交完后，才能进行下一个事务对这个表的操作

2.1:查看当前会话(session)mysql的隔离级别命令：SELECT @@tx_isolation;
2.2:设置当前会话(session)mysql的隔离级别(先设置隔离级别，再进行事务开始)：SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;
2.3:查看系统当前隔离级别命令：SELECT @@global.tx_isolation;
2.4:设置系统当前隔离级别(先设置隔离级别，再进行事务开始)：SET global TRANSACTION ISOLATION LEVEL 隔离级别;

8).表的存储引擎

1.常见的存储引擎

存储引擎名	特点1	特点2	特点3	特点4	选择
Myisam	批量插入速度快	不支持事务和外键！	没有存储限制	表锁	不需要处理事务，只是单纯的CRUD，就选择这个，速度快
InnoDB(默认)	批量插入速度慢	支持事务和外键！所以会占用更多的存储空间保存索引和数据	64TB的存储限制	行锁	如果需要支持事务，就选择这个
Memory	批量插入速度快，默认使用HASH索引，所以访问速度也很快	不支持事务和外键	有存储限制，因为是存储在内存中，当mysql服务关闭时，表的所有数据消失，但是会保存表结构	表锁	数据存储在内存中，没有io等待时间，所有的数据在服务器重启后就消失，当类似“用户在线状态的频繁数据”存储时，选择这个

修改表存储引擎命令：ALTER TABLE 表名 ENGINE = 引擎名;

9)视图

1.视图含义： 当只希望表中某些列的数据被用户看到时，这时候就需要创建一个视图！

2.视图原理： 视图是一个虚拟表，其内容由查询出来的列来定义，同真实表一样，其数据还是来自原来的真实表（一个或多个基表）

3.细节：

1.可以通过视图来修改基表中的数据，同时基表的改变，也会影响视图的数据！
2.对应视图只有一个视图结构文件（视图名.frm），但是里面没有数据。在data里面对应数据库能找到！
3.视图可以再建视图！意思就是视图还可以作为基表创建其它视图！

视图的基本语句	SQL语句	细节
创建真实表的视图	CREATE VIEW 视图名 AS SELECT 语句;	SELECT 语句搜出来是一些列的数据，然后根据这些列数据映射创建出一个表
改变视图的内容	ALTER VIEW 视图名 AS SELECT语句;
查看创建视图的命令	SHOW CREATE VIEW 视图名;
删除视图	DROP VIEW 视图1,视图n;

4.视图应用实践的例子

1.安全： 一些数据表有着重要的信息，有些字段需要保密，不能让用户直接看到，所以就使用视图保留能让用户看到的列数据！
2.性能： 关系型数据库常常用分表存储对应的数据，使用外键建立这些表的之间关系，这时候数据库通常用JOIN连接2个及以上的表，这样麻烦且效率低，如果建立一个对应列的视图，将需要用到列组合到一个视图中，这样使得效率提高！
3.灵活： 如果系统中有旧表，由于设计的问题，即将被废弃，但是很多应用基于这张表，不易修改，这时候建立一张视图，视图中的数据直接映射到新建的表，达到升级数据表的目的！

10)MySQL用户管理

1.出现原因：根据不同的开发人员，赋予相应数据库的的Mysql权限，以保证重要数据库的存储！只有赋予相应数据库的的Mysql权限才能看见相应数据库！

2.存储用户位置:存储在系统数据库mysql中的user表中

3.user表中重要列名有

2.1:host允许用户登录的位置，localhost表示该用户只允许本机登录，也可以指定ip地址
2.2.user：用户名
2.3authentication_string:过了password加密后的用户密码

4.其中的一些SQL命令

1.在root管理员下创建新用户的命令：CREATE USER '新用户名'@'登录的ip地址'IDENTIFIED BY '用户密码';
细节：1.1.如果没有指定登录的ip地址，CREATE USER '新用户名';,这样默认为%，表示所有ip都可以登这个用户
1.2.也可以指定CREATE USER '新用户名'@'%'IDENTIFIED BY '用户密码';表示所有ip都可以登这个用户，
1.3.也可以这样指定CREATE USER '新用户名'@'192.168.1.%'IDENTIFIED BY '用户密码';,表示允许在这个网段都可以登这个用户，但是在删除的时候，如果登录的ip地址不是%，需要明确指定‘用户’@‘登录的ip地址’。
2.删除用户的命令：DROP USER '新用户名'@'登录的ip地址' ;
3.用户界面下修改自己的密码：SET PASSWORD = '密码';
4.在修改他人的密码（需要修改他人密码权限，一般管理员root可以修改）：SET PASSWORD FOR '用户名'@'登录的ip地址'='密码';

常见的权限含义	权限名
设置除开grant option(授予权限)之外的所有简单权限	ALL
修改表权限	ALTER
创建表权限	CREATE
管理用户权限：如创建新用户，删除用户，重命名用户，撤销用户权限	CREATE USER
创建视图权限	CREATE VIEW
删除表数据权限	DELETE
删除表权限	DROP
执行权限	EXECUTE
建立索引和删除索引权限	INDEX
添加表数据权限	INSERT
查询表数据权限	SELECT
显示所有数据库的权限	SHOW DATABASES
允许显示和创建视图权限	SHOW VIEW
允许修改表数据的权限	UPDATE
无权限同名词	USAGE
允许授予权限的权限	GRANT OPTION

拥有授予用户权限的人员给其它人员授予相应权限命令：
grant 权限名1,权限名n on 库名.对象名(表，视图，存储过程等名字)/*.*(所有数据库所有对象) to '用户名'@'登录的ip地址' [identified by '密码']
细节：identified by ‘密码’，如果用户存在，就是修改用户的密码，如果该用户不存在，以上命令就是创建新用户和密码和赋予相应权限！

回收用户授权的权限的命令：
revoke 权限名1,权限名n on 库名.对象名(表，视图，存储过程等名字)/*.*(所有数据库所有对象) from '用户名'@'登录的ip地址'
权限生效指令:
如果权限没有生效，可以执行下面命令（一般mysql5.7之前需要）
FLUSH PRIVILEGES;