MySQL数据库—SQL汇总

- 一、准备
- 二、SQL常用数据类型
  - 1、数值型
    - 1）整型
    - 2）小数
  - 2、字符型
  - 3、日期型
- 三、SQL字段约束
  - 1、六大约束
  - 2、标识列（自增长列）
- 四、DQL（Data Query Language）数据查询语言
  - 1、语句顺序
  - 2、基础查询（SELECT）
    - (1)查询常量
    - (2)查询表达式
    - (3)查询字段
      - a.表中字段查询
      - b.使用别名（AS）
      - c.去重（DISTINCT）
    - (3)查询函数
      - a.单行函数
        
        ·字符函数
        
        ·数学函数
        
        ·日期函数
        
        ·流程控制函数
      - b.分组函数
  - 3、条件查询（WHERE）
    - （1）运算符做条件
    - （2）逻辑表达式做条件
    - （3）模糊查询
  - 4、排序查询（ORDER BY）
    - （1）基本排序
    - （2）按函数排序
    - （3）按多个字段排序
  - 5、分组查询（GROUP BY）
    - （1）按单字段分组
    - （2）按多字段分组
    - （3）按函数分组
  - 6、连接查询（JOIN）
    - （1）sql92标准
      - a.内连接
        
        等值连接
        
        非等值连接
        
        自连接
    - （2）sql99标准
      - a.内连接（INNER JOIN）
        
        等值连接
        
        非等值连接
        
        自连接
      - b.外连接（OUTER JOIN）
        
        左外连接（LEFT JOIN ON）
        
        右外连接（RIGHT JOIN ON）
        
        全外连接（FULL JOIN ON）
      - c.交叉连接（CROSS JOIN）
  - 7、子查询
    - （1）子查询在SELECT后面
    - （2）子查询在FROM后面
    - （3）子查询在WHERE/HAVING后面
    - （4）子查询在EXISTS后面（相关子查询）
  - 8、分页查询（LIMIT）
  - 9、联合查询（UNION）
- 五、DML（Data Manipulation Language）数据操作语言
  - 1、增（INSERT）
    - 1）方式一
    - 2）方式二
  - 2、删（DELETE）
    - 1）方式一
      - a.删除单表记录
      - b.删除多表记录（连接删除）
        
        sql92语法
        
        sql99语法
    - 2）方式二
    - 3）比较
  - 3、改（UPDATE）
    - 1）方式一
      - a.修改单表记录
      - b.修改多表记录（连接修改）
        
        sql92语法
        
        sql99语法
- 六、DDL（Data Define Language）数据定义语言
  - 1、库的管理
    - 1）库的创建（CREATE）
    - 2）库的修改（ALTER）
    - 3）库的删除（DROP）
  - 2、表的管理
    - 1）表的创建（CREATE）
      - a.普通创建
      - b.复制创建
      - c.带约束创建
      - d.带标识列创建
    - 2）表的修改（ALTER）
    - 3）表的删除（DROP）
- 七、TCL（Transaction Control Language）事务控制语言
- 八、DCL（Data Control Language）数据控制语言
  - 1、补充用户基本操作
  - 2、创建用户
  - 3、给用户授权
  - 4、撤销权限
  - 5、查看权限
  - 6、删除用户
- 九、视图
  - 1、视图创建
  - 2、视图修改
  - 3、视图删除

一、准备

下文整理常见SQL语句的用法，使用MySQL5.7测试，参考了尚硅谷MySQL教程及用例。用例数据：
链接: https://pan.baidu.com/s/1J9CTxYOK0Uv3AAUQINZM7A 密码: vbhf

二、SQL常用数据类型

1、数值型

1）整型

类型	所占字节数	范围(有符号/无符号)
TINYINT	1	-128-127 0-255
SMALLINT	2	-32768-32767 0-65535
MEDIUMINT	3	-8388608-8388607 0-16777215
INT(INTEGER)	4	-2147683648-2147683647 0-4294967295
BIGINT	8	-2⁶³–2⁶³-1 0–2⁶⁴-1

默认有符号，需要无符号的话，用UNSIGNED INT
插入超过范围的数，最终为临界值
整型的长度代表显示的宽度，如果要使用，需要搭配zerofill使用，对于int(M)，如长度小于M，用0左填充至宽度为M，如果大于M则无影响。没实际意义。

2）小数

类型	所占字节数
DECIMAL(M,D)	M+2	定点数
FLOAT(M,D)	4	浮点数
DOUBLE(M,D)	8	浮点数

D保留小数位数
M整数+小数位数和
MD可以省略，DECIMAL默认为(10,0)，FLOAT和DOUBLE会根据实际插入的值来确定
定点型精确度高一点，一些高精度要求的可以用定点型，如货币汇率等

2、字符型

类型	备注
CHAR(M)	存放长度固定或有限的字符
VARCHAR(M)	存放长度不固定的字符
TEXT	存放长文本
BINARY(M)	存放长度固定或有限的二进制
VARBINARY(M)	存放长度不固定的二进制
BLOB	存放较大的二进制，如图片等
ENUM()	枚举
SET()	集合

M为最多的字符数，“abc”是3个字符，“你好”是两个字符
CHAR是固定长度的字符，可省略M，默认为1，费空间，效率高
VARCHAR是可变长度字符，不可以省略M，M为最大长度，省空间，效率低
BINARY和VARBINARY与CHAR和VARCHAR类似用法
ENUM(“a”,“b”,“c”) ，多选一，只能保存"a",“b”,"c"其中之一，不区分大小写，在mysql5.7中如果插入非列表中的内容，则为null
SET(“a”,“b”,“c”)，多选多，能保存一个或多个abc中的值，如保存"a,b"，不区分大小写，在mysql5.7如果插入非列表中的内容，则报错

3、日期型

	字节	保存
DATE	4	年-月-日
DATETIME	8	年-月-日时-分-秒
TIMESTAMP	4	时间戳，时区影响小，占空小，用的多
TIME	3	时-分-秒
YEAR	1	年

三、SQL字段约束

1、六大约束

约束类型	含义	备注
NOT NULL	非空
PRIMARY KEY	主键	保证值的唯一性，且非空
UNIQUE	唯一	保证值的唯一性，可以为空
DEFAULT	默认	指定字段的默认值
CHECK	检查	保证必须是满足条件的值，mysql不支持
FOREIGN KEY	外键	用于限制两个表的关系

外键说明
- 保证从表的值必须来自于主表的某一列的值，需在从表中添加外键。
- 主表从表对应的字段类型要一致或兼容
- 主表的字段必须是一个key（一般是主键或唯一）
- 插入数据时，必须先插入主表再插入从表
主键与唯一的区别
- 主键具有唯一性，不允许为null，一张表最多一个，可以组合使用（即多个字段为组合为一个主键）但不推荐。
- 唯一具有唯一性，允许为null且在mysql5.7中默认可以有多个null，一张表可以有多个，可以组合使用但不推荐

2、标识列（自增长列）

标识列用AUTO_INCREMENT设置
标识列必须是主键或唯一
一个表中最多一个标识列
标识列类型只能是数值型

有关约束和标识列的使用，在DDL表的管理部分有整理

四、DQL（Data Query Language）数据查询语言

1、语句顺序

书写顺序：SELECT、DISTINCT、FROM、JOIN ON、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT
执行顺序：FROM、 JOIN ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT

2、基础查询（SELECT）

SELECT 查询内容 FROM 表名

(1)查询常量

SELECT 200;
SELECT 'hello';

(2)查询表达式

对于"+"运算符，仅用于数字类型的相加。若运算数为字符，尝试转化为数字，若转换失败，则认为是0;若运算数为null，结果为null。如下：

SELECT 100%3;
SELECT '123'+9;  #结果为 132，'123'-->123
SELECT 'haha'+9;  # 结果为 9，’haha’-->0
SELECT null+9;  # 结果为 null

(3)查询字段

a.表中字段查询

SELECT first_name FROM employees; #查询单个字段
SELECT first_name,last_name FROM employees; #查询多个字段
SELECT * FROM employees; #查询所有字段

b.使用别名（AS）

SELECT first_name AS '名',last_name  '姓' FROM employees; #查询结果使用别名

c.去重（DISTINCT）

SELECT DISTINCT department_id FROM employees; #查询结果去重

(3)查询函数

SQL提供了很多现成函数，常用的大致可分为单行函数和分组函数。

单行函数为处理一条数据，输出一个结果，如对字符串的处理等。
分组函数又称聚合函数、统计函数或组函数，是对多条记录的统计结果，如求和等。

a.单行函数

根据处理的数据类型不同，单行函数又可细分为字符函数、数学函数、日期函数、流程控制函数等

·字符函数

LENGTH(str) 返回字符串长度

		 SELECT LENGTH('hello'); #结果为5
		 SELECT LENGTH(last_name); #结果为last_name字段的长度

CONCAT(str1,str2) 拼接字符串

		SELECT CONCAT(last_name,'-',first_name); #结果为 last_name字段 - first_name字段

UPPER(str) | LOWER(str) 转换为大/小写

		 	SELECT UPPER('hello'); #结果为'HELLO'
		 	SELECT LOWER('HeLLo');#结果为'hello'

SUBSTR(str,pos,len) 截取字符串，字符串索引从1开始

			SELECT SUBSTR('my name is xiaoming',4,4); #结果为'name'
		  	SELECT SUBSTR('my name is xiaoming',4); #结果为'name is xiaoming'

INSTR(str,substr) 返回子串第一次出现的索引，字符串索引从1开始

			 SELECT INSTR('my name is xiaoxiao','xiao'); #结果为12

TRIM(str,substr) 首尾去除规定字符，默认去空格

			   SELECT TRIM('   my  name  is  xiaoming  ');
			   #结果为'my  name  is  xiaoming'
               SELECT TRIM('7' FROM '77my  name 777 is  xiaoming  777');
               #结果为'my  name 777 is  xiaoming  '

LPAD(str,len,padstr) | RPAD(str,len,padstr) 用规定字符左(右)填充至指定长度

			   SELECT LPAD('my',10,'*');#结果为'********my'
               SELECT RPAD('my',10,'ab');#结果为'myabababab'

REPLACE(str,old,new) 字符串替换

			   SELECT REPLACE('my name is xiaoxiao','xiao','da');#结果为'my name is dada'

·数学函数

ROUND(X,D) 四舍五入，保留指定位数，默认为保留整数

			   SELECT ROUND(-1.65); #-2
			   SELECT ROUND(-1.65,1); #-1.7

CEIL(X) | FLOOR(X) 向上|向下取整

			   SELECT CEIL(1.44);#2
			   SELECT FLOOR(1.55);#1

TRUNCATE(X) 截取保留指定小数位

			   SELECT TRUNCATE(2.666,1); #2.6

MOD(X1,X2) 取模

			   SELECT MOD(10,3);#1,符号与被除数一致
			   SELECT MOD(-10,3);#-1
			   SELECT MOD(10,-3);#1

·日期函数

NOW()、CURDATE() 当前时间、当前日期

			   SELECT NOW();#2019-10-19 14:40:54
			   SELECT CURDATE();#2019-10-19

YEAR() | MONTH() | DAY() | HOUR() | MINUTE() | SECOND()

			   SELECT YEAR('2019-10-1');#2019

DATEDIFF(date1,date2) | TIMEDIFF(date1,date2) 两个日期相差的天数|两个时刻相差的时间

			   SELECT DATEDIFF('2019-10-17','2019-10-1');#结果为16，前面的减后面的
			   SELECT TIMEDIFF('2019-10-17 15:30:2','2019-10-1 7:0:0');#结果为 392:30:02

STR_TO_DATE(str,format) 字符串按格式转为日期

DATE_FORMAT(date,format) 日期按格式转为字符串

符号	意义	符号	意义	符号	意义
%Y	四位年月	%y	两位年月	%m	月份(01,02…)
%c	月份(1,2,…)	%d	日(01,02…)	%H	小时(24制)
%h	小时(12制)	%i	分钟(01,02…)	%s	秒(01,02…)

			   SELECT STR_TO_DATE('10-1 2019','%c-%d %Y');#2019-10-01
			   SELECT DATE_FORMAT(NOW(),'%Y.%m.%d');#2019.10.19

·流程控制函数

IF 二选一

			   SELECT IF(10>5,'yes','no');#结果为'yes'

CASE-WHEN-THEN-END 多选一

	用法1，相当于Java的which，判断一个*值*：
   		        SELECT last_name,job_id,CASE job_id
                 	WHEN 'AD_PRES' THEN '1'
                 	WHEN 'AD_VP' THEN '2'
                 	WHEN 'IT_PROG' THEN '3'
                 	ELSE '4'
                 	END  FROM employees;

	用法2，相当于Java的if-else if-else，判断一个*表达式*：
			       SELECT last_name,CASE
                  	WHEN salary<5000 THEN
                  		'<5000'
                  	WHEN salary BETWEEN 5000 AND 10000 THEN
                  		'5000-10000'
                  	ELSE
                  		'>10000'
                  	END  FROM employees;

b.分组函数

分组函数又称为聚合函数、统计函数、组函数，所有分组函数对null值处理为忽略它，而非当做0。常用的分组函数有以下几个：

SUM() 对数值型数据求和，用+相加，符合+的运算法则
AVG() 对数值型数据求平均
MAX() 对所有可比较类型求最大值
MIN() 对所有可比较类型求最小值
COUNT() 统计非空个数

分组函数的调用为：

SELECT SUM(salary) FROM employees;
SELECT COUNT(*) FROM employees;#统计表的总行数
SELECT COUNT(1) FROM employees;#统计表的总行数

3、条件查询（WHERE）

SELECT 查询内容 FROM 表名 WHERE 条件

（1）运算符做条件

sql语法中运算符有:

运算符	含义	运算符	含义
>	大于	<	小于
=	等于,不可用于null	<> 或!=	不等于
<=	小于等于	>=	大于等于
<=>	安全等于，可以用于null

#查询部门编号不等于90的员工信息
SELECT * FROM employees WHERE department_id<>90;

（2）逻辑表达式做条件

sql语法中逻辑表达式有:

表达式	含义
AND	与
OR	或
NOT	非

#查询部门编号大于50小于100的员工信息
SELECT * FROM employees WHERE department_id > 50 AND department_id < 100;

（3）模糊查询

sql中用于模糊查询的关键字有：

关键字	含义	备注
LIKE	模糊匹配	%任意0个或多个字符; _任意1个字符；无法匹配null
BETWEEN a AND b	匹配a-b范围内的内容	包括a和b临界
IN(list)	匹配list中的内容
IS NULL / IS NOT NULL	判断是否为null	建议使用这个而非<=>

#查询last_name第二个字符是a的员工
SELECT * FROM employees WHERE last_name LIKE '_a%';
#查询工资在10000到20000的员工信息，包括10000和20000
SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;
#查询职位为D_VP和FI_MGR的员工信息
SELECT * FROM employees WHERE job_id IN ('D_VP','FI_MGR');
#查询没有奖金的员工信息
SELECT * FROM employees WHERE commission_pct IS NULL;

4、排序查询（ORDER BY）

SELECT 查询内容 FROM 表名 ORDER BY [ASC|DESC]
ASC：升序，默认项
DESC：降序

（1）基本排序

#按月薪降序
SELECT * FROM employees ORDER BY salary DESC;
#按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) salary_total 
FROM employees ORDER BY salary_total DESC;

（2）按函数排序

#按名字长度降序排序
SELECT * 
FROM employees 
ORDER BY LENGTH(last_name) DESC;

（3）按多个字段排序

#先按salary降序排序，同样salary的按employee_id升序排序
SELECT * 
FROM employees 
ORDER BY salary DESC , employee_id ASC;

5、分组查询（GROUP BY）

SELECT 查询内容 FROM 表名 [WHERE 条件] GROUP BY 分组列表 [HAVING 条件]

查询内容中的字段，必须要出现在分组列表中
WHERE是在分组前对原始表筛选，HAVING是在分组后对结果表筛选
分组查询中，若用分组函数做筛选的条件，一定是在HAVING子句中

（1）按单字段分组

#查询每个部门的员工数量
SELECT department_id,COUNT(1) 
FROM employees 
GROUP BY department_id ;

（2）按多字段分组

#查询每个部门，每个职务的平均工资
SELECT AVG(salary),department_id,job_id 
FROM employees 
GROUP BY department_id,job_id;

（3）按函数分组

#按名字长度分组，并筛选出数量大于5的名字长度
SELECT COUNT(1) c,LENGTH(last_name) len_name 
FROM employees 
GROUP BY len_name 
HAVING c>5;

6、连接查询（JOIN）

（1）sql92标准

SELECT 查询列表
FROM 表1,表2
WHERE 连接条件
[AND 查询条件]

a.内连接

sql92标准只支持内连接，内连接即用来查询两张表的"交集"部分，即满足条件的、两张表都存在的部分。内连接又分为等值连接、非等值连接和自连接。

等值连接

等值连接即以两张表的两个字段的值相等为连接条件，进行两张表的连接，如：

#查询每个员工的job_title
SELECT e.last_name,e.job_id,job_title 
FROM employees e,jobs j 
WHERE e.job_id = j.job_id;

非等值连接

非等值连接即以两张表的两个字段的值满足一定条件为连接条件，进行两张表的连接，如：

#查询某个员工的工资等级
SELECT e.last_name,j.grade_level 
FROM employees e,job_grades j 
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

自连接即一张表和本身的连接，因涉及到同一张表的连接，一般要对这张表使用不同的别名加以区分，如：

#查询某个员工及其对应的领导
SELECT e1.last_name 'employee',e2.last_name 'manager'
FROM employees e1,employees e2 
WHERE e1.manager_id = e2.employee_id;

（2）sql99标准

SELECT 查询列表
FROM 表1
[INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER|UNION] JOIN 表2
ON 连接条件
[WHERE 查询条件]

a.内连接（INNER JOIN）

sql99的内连接含义与sql92语法完全一致，只是语法不同。用sql92的三个例子改为sql99语法形式如下：

等值连接

#查询每个员工的job_title
SELECT e.last_name,e.job_id,job_title 
FROM employees e 
INNER JOIN jobs j 
ON e.job_id = j.job_id;

非等值连接

#查询某个员工的工资等级
SELECT e.last_name,j.grade_level 
FROM employees e
INNER JOIN job_grades j 
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

#查询某个员工及其对应的领导
SELECT e1.last_name 'employees',e2.last_name 'manager'
FROM employees e1
INNER JOIN employees e2 
ON e1.manager_id = e2.employee_id;

b.外连接（OUTER JOIN）

在sql99标准中，又增加的外连接的功能。外连接又包括左外连接、右外连接和全外连接。多用来查询一张表中有，另一张表中没有的记录。

外连接会查询主表的所以记录
若主表中的某条记录在从表中有与之对应的记录，则相应的字段为从表的内容；
反正，相应的字段为null。
换句话说，外连接查询结果=内连接查询结果+主表有但从表没有的记录

对于主从表的区分，有以下几种：
左外连接：主表 LEFT JOIN 从表
右外连接：从表 RIGHT JOIN 主表
全外连接：表1 FULL JOIN 表2，其中表1表2分别轮流作为主表，查询结果是两张表的"并集"。另外，全外连接在MySQL中不支持使用。

左外连接（LEFT JOIN ON）

#查询没有迟到记录的员工名
SELECT last_name,late.count_late 
FROM employees
LEFT JOIN late 
ON employees.employee_id = late.employee_id 
WHERE late.count_late IS NULL;

右外连接（RIGHT JOIN ON）

#查询没有迟到记录的员工名
SELECT last_name,late.count_late
FROM late 
RIGHT JOIN employees 
ON employees.employee_id = late.employee_id 
WHERE late.count_late IS NULL;

全外连接（FULL JOIN ON）

全外连接在mysql中不支持，语法如下：

SELECT beauty.name 
FROM boys 
FULL OUTER JOIN beauty 
ON beauty.boyfriend_id = boys.id ;

c.交叉连接（CROSS JOIN）

效果即笛卡尔积形式，即两个表的所有记录都一一匹配一遍，查询的结果一共A×B条记录（A、B分别为两个表的记录数）

SELECT * FROM jobs CROSS JOIN locations;

7、子查询

嵌套在其他语句的SELECT语句为子查询(内查询)，外部的查询语句为主查询(外查询)

可分类为：

标量子查询（结果集只有一行一列）
列子查询（结果集多为一列多行）
- IN、NOT IN：等于/不等于列表中的任意一个
- ANY/SOME：子查询中某一个值满足就行
- ALL：子查询中所以值都满足
行子查询（结果集多为一行多列）
表子查询（结果集有多行多列）

（1）子查询在SELECT后面

只支持标量子查询，如

#查询每个部门的信息加部门的人数
SELECT * ,
(SELECT COUNT(1) FROM employees WHERE employees.department_id = departments.department_id)
 FROM departments;

（2）子查询在FROM后面

支持表子查询，在一个SELECT查询后的表中查询新的内容，如：

#查询每个部门的平均工资的工资等级
SELECT department_id,department_name,a , j.grade_level 
FROM (
	SELECT e.department_id ,d.department_name, AVG(salary) a 
	FROM employees e 
	LEFT JOIN departments d 
	ON  e.department_id=d.department_id 
	GROUP BY e.department_id
) tmp 
INNER JOIN job_grades j 
ON tmp.a BETWEEN j.lowest_sal AND j.highest_sal;

（3）子查询在WHERE/HAVING后面

支持标量子查询、列子查询、行子查询

#标量子查询：查询工资最少的员工信息
SELECT * 
FROM employees 
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

#列子查询：查询location_id为1400或1500或2700的部门中的所有员工姓名
SELECT last_name 
FROM employees 
WHERE department_id IN (
	SELECT department_id 
	FROM departments 
	WHERE location_id IN(1400,1500,2700)
);

#行子查询：查询编号最小并且工资最高的员工信息
#满足行子查询的条件笔记苛刻，所以用的不多
SELECT * 
FROM employees 
WHERE (employee_id,salary) = (
	SELECT MIN(employee_id),MAX(salary) 
	FROM employees
);

（4）子查询在EXISTS后面（相关子查询）

EXISTS(SELECT 语句)：有记录，则为1，无记录，则为0
相关子查询是先执行外查询，在由EXISTS过滤；都能用IN代替

#查询有员工的部门名
SELECT department_name 
FROM departments d 
WHERE EXISTS(
	SELECT * FROM employees e WHERE e.department_id = d.department_id
);
#用IN代替
SELECT department_name 
FROM departments d
 WHERE d.department_id IN (
 	SELECT DISTINCT department_id 
 	FROM employees
 );

8、分页查询（LIMIT）

SELECT 查询列表
FROM 表名
LIMIT offset,size;

offset 要查询的起始索引，从0开始，缺省为0
size 要查询的记录数目

#查询有奖金的员工中工资最高的10个员工的信息
SELECT * 
FROM employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 0, 10;

9、联合查询（UNION）

SELECT完整语句1
UNION [ALL]
SELECT完整语句2
UNION [ALL]
…
将多条查询语句的结果合并为一个结果，结果的字段名为第一个查询的字段。

要求各部分字段列数一致
要求各部分字段顺序一致
UNION默认去重，不去重用UNION ALL

#查询department_id>50和salary>5000的员工信息
SELECT * 
FROM employees 
WHERE department_id > 50 
UNION 
SELECT * 
FROM employees 
WHERE salary > 5000 ;

五、DML（Data Manipulation Language）数据操作语言

1、增（INSERT）

1）方式一

INSERT INTO 表名(字段1，字段2…) VALUE (值1，值2 …)

插入的值的类型要与列的类型一致或兼容
可以为null的列或有默认值的列可以不写（默认为null，或默认值）
列的顺序可以颠倒，与值对应即可

可以省略列名不写，默认是所有列名

INSERT INTO 表名 VALUE (值1，值2 ...)

支持多行插入

INSERT INTO 表名 VALUE (值1，值2 ...),(值1，值2 ...),(值1，值2 ...)...

支持子查询，插入查询的结果

INSERT INTO 表名(字段1,字段2...) SELECT 字段1,字段2... FROM...

2）方式二

INSERT INTO 表名 SET 字段1=值1,字段2=值2…

2、删（DELETE）

1）方式一

a.删除单表记录

DELETE FROM 表名 WHERE 筛选条件

b.删除多表记录（连接删除）

sql92语法

DELETE [表1|表2]
FROM 表1,表2
WHERE 连接条件
AND 筛选条件

sql99语法

DELETE [表1|表2]
FROM 表1
[LEFT|RIGHT|FULL|INNER] JOIN 表2
ON 连接条件
WHERE 筛选条件

2）方式二

TRUNCATE TABLE 表名

3）比较

TRUNCATE不能加筛选条件，只是清空整个表。
DELETE删除后，自增长列从断点开始，可以返回受影响的行数，效率低，可以回滚
TRUNCATE删除后，自增长列从1开始，不可以返回受影响的行数，效率高，不可以回滚

3、改（UPDATE）

1）方式一

a.修改单表记录

UPDATE 表名
SET 字段1=新值1,字段2=新值2…
WHERE 筛选条件

b.修改多表记录（连接修改）

sql92语法

UPDATE 表1,表2
WHERE 连接条件
AND 筛选条件
SET 字段1=新值1,字段2=新值2…

sql99语法

UPDATE 表1
[INNER|LEFT|RIGHT|FULL] JOIN 表2
ON 连接条件
WHERE 筛选条件
SET 字段1=新值1,字段2=新值2…

六、DDL（Data Define Language）数据定义语言

1、库的管理

1）库的创建（CREATE）

CREATE DATABASE IF NOT EXISTS database1;

2）库的修改（ALTER）

#修改字符集
ALTER DATABASE database1 CHARACTER SET utf-8或gbk;

3）库的删除（DROP）

DROP DATABASE IF EXISTS database1;

2、表的管理

1）表的创建（CREATE）

a.普通创建

CREATE TABLE  IF NOT EXISTS table_name(
				字段名 字段类型 ,
				……
				字段名 字段类型 
			)

b.复制创建

#复制表的所有结构
CREATE TABLE new_table LIKE old_table;
#复制表的部分结构
CREATE TABLE new_table SELECT list FROM old_table WHERE 0;
#复制表的所有结构+所有数据
CREATE TABLE new_table SELECT * FROM old_table;
#复制表的部分结构+所有数据
CREATE TABLE new_table SELECT field_list FROM old_table;
#复制表的部分结构+部分数据
CREATE TABLE new_table SELECT field_list FROM old_table WHERE condition

c.带约束创建

创建表时的约束可分为列级约束和表级约束，在mysql中：

列级约束：支持主键、唯一、非空、默认
表级约束：支持主键、唯一、外键
一般的，外键用表级约束添加，其他的用列级约束添加

#创建表时添加列级约束
      CREATE TABLE user(
      	id INT PRIMARY KEY, #主键
      	name VARCHAR(20) NOT NULL, #非空
      	no INT UNIQUE, #唯一
      	flag TINYINT DEFAULT 1, #默认
      )
#创建表时添加表级约束
      CREATE TABLE user(
      	id INT,
      	no INT ,
      	book_id INT,
      	#以下添加表级约束
      	CONSTRAINT pk PRIMARY KEY(id), #主键
      	UNIQUE(no),#唯一，可以省略CONSTRAINT(其他约束也可一省略)
      	CONSTRAINT fk_user_book FOREIGN KEY(book_id) REFERENCES book(id) #外键，建议取个约束名
      )

d.带标识列创建

同约束的设置方法，在创建表时，在后面添加 AUTO_INCREMENT

CREATE TABLE user(
			id INT UNIQUE AUTO_INCREMENT,
			name VARCHAR(20)
)

SET auto_increment_increment=3 #设置步长
SET auto_increment_offset=3 #设置起始值，mysql不支持，但可以通过插入数据时来手动插入一个起始值

2）表的修改（ALTER）

修改表名

ALTER TABLE 表名 RENAME TO  新表名;

修改字段名

ALTER TABLE 表名 CHANGE COLUMN 字段名 新字段名;

修改字段的类型、约束或添加约束

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束类型;

添加新列

ALTER TABLE 表名 ADD COLUMN 字段名 字段类型 约束类型;

删除列

ALTER TABLE 表名 DROP COLUMN 字段名;

删除约束

	ALTER TABLE user DROP PRIMARY KEY;#删除主键
	ALTER TABLE user DROP  FOREIGN KEY  fk_user_book;#删除外键

3）表的删除（DROP）

DROP TABLE IF EXISTS 表名;

七、TCL（Transaction Control Language）事务控制语言

使用事务一般有以下步骤：

step1：关闭自动提交
SET autocommit = 0 #默认为1，自动提交
step2:写多条sql语句
……
……
step3:结束事务
成功：COMMIT 提交事务
失败：ROLLBACK 回滚

即：

SET autocommit = 0; #关闭自动提交
INSERT INTO book values(1,'book1');
INSERT INTO book values(2,'book2');
COMMIT;#提交，插入两条记录成功

SET autocommit = 0; #关闭自动提交
INSERT INTO book values(1,'book1');
INSERT INTO book values(2,'book2');
ROLLBACK;#回滚，不插入记录

SET autocommit = 0; #关闭自动提交
INSERT INTO book values(1,'book1');
SAVEPOINT a;
INSERT INTO book values(2,'book2');
ROLLBACK a;#回滚到a，第一条插入，第二条不插入

八、DCL（Data Control Language）数据控制语言

1、补充用户基本操作

mysql [-h localhost -P 3306] -u root -p;#登录
exit;#退出
show databases;#查看所有库
use db_name;#进入库
select database();#查看当前库
show tables [from dbname];#查看表
desc tablename;#查看表的结构

2、创建用户

CREATE USER 用户名@‘IP地址’ IDENTIFIED BY '密码'；#在指定ip访问
CREATE USER 用户名@’%’  IDENTIFIED BY '密码'； #所有ip都可访问

3、给用户授权

GRANT 权限 ON 库名.表名 to 用户名@’IP地址’;
#如果是所有权限，则权限处用ALL
#如果是多个权限，中间用,隔开
#如果是所有库，库名用*
#如果是所有表，表名用*
#如果是所有IP，ip地址用%

常用权限：
USAGE：默认用，不可删，登录权限
SELECT：查询权限
INSERT：插入权限
UPDATE：更改权限
DELETE：删除权限
CREATE：建表权限
CREATE VIEW：创建视图权限
ALTER：修改表权限
DROP：删除表权限index
INDEX：创建/删除索引
GRANT OPTION：将自己有的权限授予其他人

4、撤销权限

REVOKE 权限 ON 库名.表名 to 用户名@’IP地址’;

5、查看权限

SHOW GRANTS FOR 用户名@’IP地址’;

6、删除用户

DROP USER 用户名@‘IP地址’;

九、视图

视图是在mysql5.1版本出现的新特性，它是一张虚拟表，和普通表一样的用法。但在几个特点的条件下，视图不支持更新，一般也不对视图进行更新。在物理空间中，只保存视图的sql逻辑结构，而不保存具体的数据，在使用时，动态生成查询数据。应用场景：

多个地方用到同样的查询结果
查询语句较为复杂

1、视图创建

CREATE VIEW view_name
AS
SELECT语句

2、视图修改

#方式一
CREATE OR REPLACE VIEW view_name
AS
SELECT 语句
#方式二
ALTER VIEW view_name
AS
SELECT 语句

3、视图删除

DROP VIEW view_name,view_name…;#支持同时删除多个视图

(责任编辑：IT)

搜索

热门标签: