MySQL

注意

安装完后在配置环境变量的时候，如果安装有多个MySQL，记得把当前想要用的版本的Path上移到其他版本Path之上。

本文用到的所有库和表的链接：

链接：https://pan.baidu.com/s/1Z8kNbeElwMOK03ockkOLFA
提取码：qvd8

数据库相关概念

1、DB：数据库，保存一组有组织的数据的容器。

2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据。

3、SQL:结构化查询语言，用于和DBMS通信的语言。

数据库存储数据的特点

1. 将数据放到表中，表再放到库中
2. 一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3. 表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4. 表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5. 表中的数据是按行存储的，每一行类似于java中的“对象”。

MySQL服务的启动和停止

方式一：计算机——右击管理——服务

右键即可启动和停止

方式二：通过命令行

net start 服务名（启动服务）
net stop 服务名（停止服务）

MySQL服务的登录和退出

方式一：通过mysql自带的客户端

仅限ROOT用户

方式二：通过windows自带的客户端

mysql -h 主机名 -P 端口号 -u 用户名 -p

回车然后输入密码即可登陆成功

注意：也可以一条命令中连密码也输入，不过这时密码是明文的，-p后面接密码且不能有空格。

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

如果连的是本地的，可以省略

-h 主机名 -P 端口号

退出

exit

MySQL的常见命令

查看当前所有的数据库

show databases;

打开指定的库

use 库名

查看当前库的所有表

show tables;

查看其它库的所有表

show tables from 库名;

创建表

create table 表名(
	字段名 字段类型,	
	字段名 字段类型
);

查看表结构

desc 表名;

查看服务器的版本

方式一：

登录到mysql服务端

select version();

方式二：

在命令行中

mysql --version

或者

mysql --V

显示表中的所有数据

select * from 表名

MySQL的语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进 或换行
4.注释
单行注释：#注释文字
单行注释：-- 注释文字
多行注释：/* 注释文字 */

SQL的语言分类

DQL（Data Query Language）：数据查询语言 select

DML(Data Manipulate Language):数据操作语言 insert 、update、delete

DDL（Data Define Languge）：数据定义语言 create、drop、alter

TCL（Transaction Control Language）：事务控制语言 commit、rollback

DQL语言的学习

基础查询

语法：

SELECT 要查询的东西【FROM 表名】;

特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

常量值：

SELECT 100;

SELECT "ekertree";

在MySQL中不区分字符与字符串

字段：

SELECT last_name FROM employees;

SELECT last_name,first_name FROM employees;

SELECT * FROM employees;

函数：

SELECT VERSION();

表达式：

SELECT 101 % 2;

注意：如果查询的字段与表名、关键字的名字相同，则需要加着重号（`）包住，即ESC下面的那个按键。

SELECT `name` FROm t_stu;

起别名

1. 便于理解
2. 如果要查询的字段有重名的情况，使用别名可以区别开来

方式一：

SELECT last_name AS 姓,first_name AS 名 FROM employees;

方式二：

SELECT last_name 姓,first_name 名 FROM employees;

AS直接省略

注意：

如果别名中间有特殊符号：空格、#号等，别名加上引号

SELECT last_name "Out put" FROM employees;

去重

在查询的字段前面加上DISTINCT关键字

SELECT DISTINCT department_id FROM employees;

+号的作用

MySQL中的+号只有一个功能：运算符

两个操作数都为数值型，则做加法运算

SELECT 100+20;

若其中一方为字符型，试图将字符型数值转换成数值型如果转换成功，继续做加法运算

SELECT "123"+77;

如果转换失败，则将字符型数值转换成0

拼接字段

使用CONCAT(str1,str2,....)函数

SELECT CONCAT(last_name,' ',first_name) 姓名 FROM employees;

条件查询

根据条件过滤原始表的数据，查询到想要的数据

语法：

SELECT 		要查询的字段|表达式|常量值|函数	FROM 		表	WHERE 		条件 ;

条件的分类

条件表达式

条件运算符：	> < >= <= = != <>

后面两个都是表示是不等于

逻辑表达式

逻辑运算符：

and（&&）:两个条件如果同时成立，结果为true，否则为false

or(||)：两个条件只要有一个成立，结果为true，否则为false

not(!)：如果条件成立，则not后为false，否则为true

模糊查询

like

一般和通配符搭配使用

通配符：

%：任意多个字符，也可以是0个

SELECT * FROM employees WHERE last_name LIKE '%ab%';

_: 任意单个字符

SELECT * FROM employees WHERE last_name LIKE '_ab%';

注意当你要查找的字符就是通配符时，请在前面加\进行转义

如果不想用\当转义，可以加上ESCAPE语句，更换代表转义的符号

SELECT * FROM employees WHERE last_name LIKE 'asd$_%' ESCAPE '$';

此时美元符号就和\一样代表转义

inis nullis not null

between and

在什么之间

1. 使用提高语句的简洁度
2. 并且其包含临界值
3. 临界值的顺序不能颠倒

SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200;

in

判断某字段的值是否属于in列表中的某一项

1. 提高简洁度
2. in列表的值类型必须一致或者兼容
3. 不支持通配符

SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES');

is null、is not null

用于判断null值

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

安全等于: <=>

安全等于既可以比较null，还可以比较普通数值

SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;

SELECT last_name,commission_pct,salary FROM employees WHERE commission_pct <=> NULL && salary <=> 12000;

排序查询

语法：

select	要查询的东西from	表where 	条件order by 排序的字段|表达式|函数|别名 【asc|desc】

asc代表升序，desc代表降序，如果不填写，默认是升序

select * from employees order by salary desc;

order by后面支持表达式

select *,salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by salary*12*(1+ifnull(commission_pct,0)) desc;

order by 后面也支持使用别名

select *,salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc;

order by支持按多个字段的多个方式排序

select * from employees order by salary,employee_id DESC;

先满足第一个排序条件salary，默认从小到大排序，然后满足第二个排序条件，在salary相同的情况下，employee_id从大到小排序，以此类推。

总结

1. acs代表是升序，desc代表的是降序，如果不写，默认是升序
2. order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3. order by子句一般是放在查询语句的最后面，limit子句除外。

常见函数

单行函数

字符函数

length:获取参数值的字节个数

select length('john');

---

concat: 拼接字符串

select concat('last_name',' ','first_name') 姓名 from employees;

---

upper lower :转换成大写、小写

select upper('john');

---

substring、substr：截取字符串

MySQL中下标从1开始

select substr('john',2);#截取从第二个字符开始的字符串

select substr('john',2,3);#截取从第二个字符开始，长度为3的字符串

---

instr ：返回子串第一次出现的索引，如果找不到返回0

select instr('abesfknekjohnsad','john');

---

trim：删除字符串前后的空白

select trim('   john           ');

还可以自定义删除指定字符

select trim('a' from 'aaaaaajohnaaaaa');

---

lpad、rpad：用指定字符左填充（右填充）使字符串变为指定长度

select lpad('john',10,'a');

如果指定长度小于原本字符串长度，则不会继续填充，反而会从右边进行截断，无论左填充还是右填充，效果都如下图，都只会从右边截断。

select lpad('john',2,'a');
select rpad('john',2,'a');

---

replace：替换

select replace('john love lucy','love','hate');

数学函数

round：四舍五入

select round(1.56);

select round(1.567,2);#保留小数点后两位

---

ceil：向上取整，返回大于该参数的最小整数

select ceil(1.002);

---

floor：向下取整

---

truncate：截断

select truncate(1.002,2);#保留小数点后几位

---

mod：取余

select mod(10,3);10-10/3*3;

select 10%3;

日期函数

now：返回当前系统日期+时间

curdate：返回当前系统日期，不包含时间

curtime：返回当前时间，不包含日期

year()、mouth()、day()、hour()、minute()、sencond():获取指定时间的年月日时分秒

str_to_date:将日期格式的字符转换成指定格式的日期

select STR_TO_DATE(str,format);select STR_TO_DATE('1998-3-2','%Y-%c-%d');

date_format:将日期转换成字符

select DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s');

关于格式：

流程控制函数

if(条件，成立执行，不成立执行)

select IF(10>5,'大','小')；

case：

1.switch case的效果

SELECT	salary 原始工资,department_id,CASE department_id 		WHEN 30 THEN		salary * 1.1 #要显示的值		WHEN 40 THEN		salary * 1.2 		WHEN 50 THEN		salary * 1.3 ELSE salary 	END 新工资 FROM	employees;

2.case使用的方式二

SELECT salary,CASEWHEN salary > 20000 THEN 'A'WHEN salary > 15000 THEN 'B'WHEN salary > 10000 THEN 'C'ELSE 'D'END 工资级别FROM employees;

其他函数

version版本database当前库user当前连接用户

分组函数

​ 做统计使用，又成为统计函数、聚合函数

sum：求和

avg：求平均值

max：求最大值

min：求最小值

count：计算个数

特点：

1. 以上五个分组函数都忽略null值，除了count()

2. sum和avg一般用于处理数值型

3. max、min、count可以处理任何数据类型

4. 都可以搭配distinct使用，用于统计去重后的结果

   select SUM(distinct salary) from employees;
   

5. count的参数可以支持：
   字段、*、常量值

   count(*):用来统计表的总行数，在统计表时，每一行，只要有一个字段不为null，就会被统计上

   select count(*) from employees;
   

   count(常量值)：在表中添加一列这个常量值，所以有多上行就有多少个，所以同样可以计算行数

   select count(1) from employees;
   

   效率

   MYISAM存储引擎下，count(*)效率更高

   INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)高

和分组函数一同查询的字段要求是group by后的字段

分组查询

语法：

select 查询的字段，分组函数from 表【where 筛选语句】group by 分组的字段【order by 子句】

select avg(salary),job_id from employees group by job_id;

案例：查询邮箱包含a字符的部门的平均工资：添加分组前的筛选

select avg(salary),department_id from employees where email like '%e%' group by department_id;

案例：查询哪个部门员工大于2：添加分组后的筛选

select count(*),department_id from employees group by department_id having count(*)>2;

1.查询每个部门的员工数量

2.对1的结果进行筛选，使用了having

where字句在分组聚合前先筛选记录，having字句可以让我们筛选成组后的各种数据。

分组函数作条件放having子句，能用分组前筛选的，优先考虑使用分组前筛选。

按表达式、函数分组

案例：按员工姓名长度分组，查询每一组员工的个数，筛选员工个数>5的有哪些

select count(*),length(last_name) from employees group by length(last_name) having count(*) >5;

按多个字段分组

案例：查询每个部门每个工种的平均工资

select avg(salary),department_id,job_id from  employees group by department_id,job_id order by avg(salary);

同时，group by也支持添加排序，desc降序，asc升序。

连接查询

又称多表查询，当查询的字段来自多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行

发生原因：没有有效的连接条件

避免：添加有效的连接条件

SQL语法的分类

一、按年份分类

1. sql192标准：仅仅支持内连接
2. sql199标准【推荐】：支持内连接、外连接（左外和右外）、交叉连接

二、按功能分类

内连接：

​ 等值连接

​ 非等值连接

​ 自连接

外连接：

​ 左外连接

​ 右外连接

​ 全外连接

交叉连接：

一、SQL92标准

等值连接

SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id = boys.id;

建议为表起别名

1. 提高语句的精简度
2. 区分多个重名的字段

SELECT e.last_name,e.job_id,j.job_title FROM employees e,jobs j WHERE e.job_id = j.job_id;

如果为表起了别名，则查询的字段就不能用原来的表名去限定：FROM->WHERE->SELECT

可以加筛选条件

案例：查询有奖金的部门名和员工名

SELECT	last_name,	department_name FROM	employees e,	departments d WHERE	e.department_id = d.department_id 	AND e.commission_pct IS NOT NULL;

可以加分组

案例：查询每个城市的部门个数

SELECT	COUNT(*) 个数,	city FROM	departments d,	locations l WHERE	d.location_id = l.location_id GROUP BY	city

可以加排序

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT	job_title,	COUNT(*) FROM	employees e,	jobs j WHERE	e.job_id = j.job_id GROUP BY	job_title ORDER BY	COUNT(*) DESC;

可以实现三表连接

案例：查询员工名、部门名、所在城市

SELECT	last_name,	department_name,	city FROM	employees e,	departments d,	locations l WHERE	e.department_id = d.department_id 	AND l.location_id = d.location_id;

总结：

1.等值连接的结果 = 多个表的交集

2.n表连接，至少需要n-1个连接条件

3.多个表不分主次，没有顺序要求

4.一般为表起别名，提高阅读性和性能

5.可以搭配前面的所有子句使用

非等值连接

案例：查询员工的工资和工资级别

SELECT	salary,	grade_level FROM	employees e,	job_grades g WHERE	salary BETWEEN g.lowest_sal 	AND g.highest_sal;

自连接

即自己和自己连接

案例：查询员工名和上级名称

第一次查询时当作是员工表，找到领导编号后，又当作领导表

SELECT	e.employee_id 职员 id,	e.last_name 职员名称,	m.employee_id 领导 id,	m.last_name 领导名称 FROM	employees e,	employees m WHERE	e.manager_id = m.employee_id

二、SQL99标准

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

语句上，连接条件和筛选条件实现了分离，简洁明了

内连接

表的交集部分

语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

等值连接

案例：查询名字中包含e的员工名和工种名

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';

案例：查询部门个数>3的城市名和部门个数

SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
on d.location_id = l.location_id
GROUP BY city
HAVING COUNT(*) > 3;

特点：

1. 可添加排序、分组、筛选
2. inner可以省略
3. 筛选条件放在whehe后面，连接条件放在on后面，提高分离性，便于阅读
4. inner join连接和SQL92语法中的等值连接效果是一样的，都是查询多表的交集

非等值连接

案例：查找工资在最低和最高之间，各个等级工资的工资

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
on e.salary BETWEEN g.lowest_sal AND g.highest_sal;

自连接

案例：查找员工和员工的上司

SELECT e.last_name 员工名,m.last_name 上司名
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';

外连接

应用：用于查询一个表中有，另一个表中没有

特点：

1. 外连接的查询结果为主表中的所有记录
   • 如果从表中有和它匹配的，则显示匹配的值
   • 如果从表中没有和它匹配的，则显示null
   • 外连接查询结果=内连接结果+主表中有而从表中没有的记录
2. 左外连接，left join左边的是主表
3. 右外连接，right join是主表
4. 左外和右外交换两个表的顺序，可以实现同样的效果

左外连接：

左边的主表全部数据和主表和从表的交集数据

案例：找没有男友的女生

SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id IS NULL;

右外连接

右边的主表全部数据和主表和从表的交集数据

案例：查找没有员工的部门

SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

全外连接

MySQL不支持

全外连接可以查询主表有从表没有，或者主表没有从表有的，或者两个都有的，其实也就没有必要分主从表了。

USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id

交叉连接

就是发生笛卡尔效应，没有有效的连接条件，让两个表随便地拼接在一起

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

子查询

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询，在外面的查询语句，称为主查询或外查询。

where、having后面

标量子查询（单行子查询）

案例：查询工资高于Abel的员工

SELECT *
FROM employees
WHERE salary > (
	SELECT salary 
	FROM employees
	WHERE last_name = 'Abel');

列子查询（多行子查询）

1. 返回多行
2. 使用多行比较操作符

案例：查询location_id是1400或者1700的部门中的所有员工的姓名

USE myemployees;
SELECT last_name
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN (1400,1700)
	);

案例：返回其他部门中比job_idwei 'IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id和salary

SELECT
	last_name,
	employee_id,
	job_id,
	salary 
FROM
	employees 
WHERE
	salary < ALL ( 
	SELECT DISTINCT salary 
	FROM employees 
	WHERE job_id = 'IT_PROG' 
	) 
	AND job_id <> ' IT_PROG';

行子查询（多列多行）

案例：查询员工编号最小并且工资最高的员工信息

SELECT * FROM employeesWHERE (employee_id,salary)=(	SELECT MIN(employee_id),MAX(salary)	FROM employees	);

select后面

仅仅支持标量子查询

案例：查询每个部门的人数

SELECT d.*,( SELECT COUNT(*) FROM employees e WHERE e.department_id = d.department_id ) 个数 FROM	departments d;

from后面

案例：查询每个部门的平均工资的工资等级

SELECT ag_dep.*,g.grade_levelFROM (		SELECT AVG(salary) ag,department_id		FROM employees		GROUP BY department_id) ag_dep#将子查询结果充当一张表，要求必须起别名INNER JOIN job_grades gON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

exists后面

相关子查询

语法：

exists(完整的查询语句)

结果：0或者1

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交SQL请求

语法：

select 字段|表达式,...from 表【where 条件】【group by 分组字段】【having 条件】【order by 排序的字段】limit 【起始的条目索引，】条目数;

案例：查询前五条员工信息

SELECT * FROM employees LIMIT 0,5;

如果是从第一个开始查询，0往往可以省略

SELECT * FROM employees LIMIT 5;

特点：

1.起始条目索引从0开始

2.limit子句放在查询语句的最后

3.公式：select * from  表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page

联合查询

union 联合、合并：将多条查询语句的结果合并成一个结果

应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：

1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重

案例：查询部门编号>90或者邮箱包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' OR department_id > 90;

SELECT * FROM employees WHERE email LIKE '%a%'UNIONSELECT * FROM employees WHERE department_id > 90;

DML语言

数据操作语言

插入

方式一

语法：

	insert into 表名(字段名，...)	values(值1，...);

插入的值的类型要与列的类型一致或者兼容

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)VALUES(13,'唐艺昕','女','1990-4-23','1891616262',NULL,2);

列的顺序可以调换

列数和值的个数必须一致

可以省略列名，默认所有列，列的顺序和表中的顺序一致

INSERT INTO beautyVALUES(13,'zhangfei','女','1990-4-23','1891616262',NULL,2);

方式二

语法：

INSERT into 表名set 列名=值,列名=值...

INSERT into beautyset id='6',phone='999';

比较

方式一支持插入多行，方式二不支持

INSERT into beautyVALUES(13,'zhangfei','女','1990-4-23','1891616262',NULL,2),(13,'zhanfei','女','1990-4-23','1891616262',NULL,2),(13,'zhafei','女','1990-4-23','1891616262',NULL,2);

方式一支持子查询，方式二不支持

INSERT INTO beauty(id,NAME,phone)SELECT 26,'宋茜','10086';

修改

修改单表的记录

语法：

update 表名 set 字段=新值,字段=新值【where 条件】

案例：修改beauty表中姓曹的女神的电话为10086

UPDATE beauty SET phone = '100861'WHERE NAME LIKE '周%';

修改多表的记录

update 表1 别名inner |left|right join 表2 别名on 连接条件set 列=值,...where 筛选条件;

案例：修改没有男友的女生的男朋友id为2

UPDATE boys boRIGHT JOIN beauty bon bo.id = b.boyfriend_idSET b.boyfriend_id = 2WHERE b.id IS NULL;

删除

单表删除

方法一：

语法：

DELETE FROM 表名 WHERE 筛选条件

方式二：

TRUNCATE语句

TRUNCATE TABLE boys;

多表删除

方式一：

DELETE 表1的别名,表2的别名FROM 表1 别名INNER|LEFT|RIGHT JOIN 表2 别名 ON 连接条件WHERE 筛选条件;

比较

1.truncate不能加where条件，而delete可以加where条件

2.truncate的效率更高

3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
，delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始

4.truncate删除不能回滚，delete删除可以回滚

5.truncate是删除整个表，delete是可根据条件删除某些项

6.truncate没有返回值，delete有返回值

DDL语言

库的管理

库的创建

语法：

CREATE DATABASE 库名;

如果库已经存在会报错，如果想库存在了也不报错，加上IF NOT EXISTS

CREATE DATABASE IF NOT EXISTS 库名;

库的修改

修改库的字符集

ALTER DATABASE 库名 CHARACTER SET gbk;

库的删除

DROP DATABASE IF EXISTS 库名;

表的管理

表的创建

CREATE TABLE 表名 (	列名 列的类型【(长度)约束】	列名 列的类型【(长度)约束】	列名 列的类型【(长度)约束】	...	);

表的修改

修改列名：

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 列的类型; 

修改列的类型或者约束：

ALTER TABLE 表名 MODIFY COLUMN 列名 列的类型/约束;

添加新列：

ALTER TABLE 表名 ADD COLUMN 列名;

删除列：

DROP COLUMN 列名;

修改表名：

ALTER TABLE 表名 RENAME TO 新表名;

表的删除

DROP TABLE 表名;

表的复制

仅仅复制表的结构：

CREATE TABLE 复制到新的表的表名 LIKE 表名;

复制表的结构和数据：

CREATE TABLE 复制到新的表的表名 SELECT * FROM 要复制的表名;

复制表的结构和部分数据：

CREATE TABLE 复制到新的表的表名 SELECT 列名 FROM 要复制的表名 WHERE 筛选条件;

仅仅复制某些字段，不要数据：

CREATE TABLE 复制到新的表的表名 SELECT 列名 FROM 要复制的表名 WHERE 0;

数据类型

整型

设置无符号和有符号：

CREATE TABLE tab_int(		t1 INT,#有符号		t2 INT UNSIGNED#无符号);

设置长度：

t1 INT(length)

设置位数不够长度，0来填充

t1 INT(length) ZEROFILL

length并不能设置类型长度，只是代表了显示的最大宽度

特点

1. 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字

2. 如果插入数值超过了整型的范围，会报out of range异常，并且插入临界值（与MySQL的版本有关，我用的5.7.26会报错，不插入临界值）

3. 如果不设置长度，会有默认的长度

小数

float(M,D)

double(M,D)

dec(M,D)

decimal(M,D)

特点

• M：整数部位+小数部位的总长度
• D：小数部位长度
• 如果超过范围，则插入临界值（看MySQL版本）
• M和D都可以省略，如果是decimal，则M默认是10，D默认是0；如果是float和double，则会根据插入的数值的精度来决定精度。
• 定点型的精确度较高，如果要求插入数值的技能高度较高如货币运算等则考虑使用

字符型

用于保存较短的文本

char为固定长度字符，较为耗费空间，效率高，varchar为可变长度的字符，比较节省空间，效率低。

枚举类型

用于保存枚举

CREATE TABLE tab_char(		t1 ('a','b','c'));INSERT tab_charVALUES(a);INSERT tab_charVALUES(d);#非枚举的无法插入INSERT tab_charVALUES(c);

5.7.26的MySQL似乎没有enum

Set类型

用于保存集合

说明:和num类型类似，里面可以保存0~64个成员。和Enum类型最大的区别是:SET类型一次可以选取多个成员，而Enum只能选一个
根据成员个数不同，存储所占的字节也不同。

CREATE TABLE tab_set (
		t1 SET('a','b','c','d')
);
INSERT tab_set
VALUES('a');
INSERT tab_set
VALUES('a,b');
INSERT tab_set
VALUES('a,b,c');

binary和varbinary

用于保存较短的二进制

text、blob

用于保存较长的文本

blob用于二进制

日期型

1.Timestamp支持的时间范围较小，取值范围∶19700101080001——2038年的某个时间，Datetime的取值范围:1000-1-1——9999.
-12-31
2.timestamp和实际时区有关﹐更能反映实际的日期，而datetime则只能反映出插入时的当地时区

3.timestamp的属性受Mysql版本和SQLMode的影响很大

CREATE TABLE tab_data(		t1 DATETIME,		t2 TIMESTAMP);INSERT INTO tab_data VALUES(NOW(),NOW());

常见约束

一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠性

1. NOT NULL

   非空，用于保证该字段的值不能为空

2. DEFAULT

   默认，用于保证该字段有默认值

3. UNIQUE

   唯一，用于保证该字段的值具有唯一性，可以为空

4. CHECK

   检查约束，MySQL5.7.26不支持，8开始支持，如性别只能是男女

5. PRIMARY KEY

   主键，用于保证该字段的值具有唯一性，并且非空

6. FOREIGN KEY

   外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表中添加外链约束，用于引用主表中某列的值，如学生表的专业编号

一个字段也可以加上多个约束

添加约束的时机：

1. 创建表时
2. 修改表时

约束的添加分类：

1. 列级约束
2. 表级约束

CREATE TABLE 表名(
		字段名 字段类型 列级约束（六大约束语法上都支持，但外键约束在这没有效果），
		字段名 字段类型，
		表级约束
);

创建表时添加约束

添加列级约束

语法：

直接在字段名和类型后面追加约束类型即可

只支持：默认、非空、主键、唯一

CREATE DATABASE students;USE students;CREATE TABLE stuinfo(		id INT PRIMARY KEY,#主键		stuName VARCHAR(20) NOT NULL,#非空		gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查	  seat INT UNIQUE,#唯一		age INT DEFAULT 18,#默认约束		majorId INT REFERENCES major(id)#外键);CREATE TABLE major(		id INT PRIMARY KEY,		majorName VARCHAR(20));

查看表中所有的索引，包括主键、外键、唯一

SHOW INDEX FROM 表名;

添加表级约束

语法：

在各个字段的最下面，使用

（CONSTRAINT 约束名） 约束类型（字段名）

CONSTRAINT 约束名可要可不要

CREATE TABLE stuinfo(
		id INT,
		stuName VARCHAR(20),
		gender CHAR(1),
	  seat INT,
		age INT,
		majorId INT
		CONSTRAINT pk PRIMARY KEY(id),#主键
		CONSTRAINT uq UNIQUE(seat),#唯一键
		CONSTRAINT ck CHECK(gender='男' OR gender='女')，#检查
		CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)#外键
);

通用的写法：

CREATE TABLE stuinfo(
		id INT PRIMARY KEY,
		stuName VARCHAR(20) NOT NULL,
		gender CHAR(1),
	  seat INT UNIQUE,
		age INT DEFAULT 18,
		majorId INT
		CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)#外键
);

主键和唯一的区别

主键：保证了唯一性，不可为空，一个表中最多有一个，允许组合

唯一：保证唯一性，可以为空，null值可以有多个，null值不受唯一性影响,一个表中可以有多个，允许组合

关于组合：

CREATE TABLE stuinfo(
		id INT PRIMARY KEY(),
		stuName VARCHAR(20) NOT NULL,
		majorid,
		PRIMARY KEY(id,stuName),
		UNIQUE(stuName,majorid)
);

将多个字段组合在一起作为唯一的判断，两个字段都相同才意味着重复

外键的特点

1. 要求在从表设置外键关系
2. 从表的外键列的类型和主表的关联列的类型要求一致或者兼容，名称无要求
3. 主表的关联列必须是一个key（一般是主键、唯一）
4. 插入数据时，先插入主表，再插入从表
5. 删除数据时，先删除从表，再删除主表

修改表时添加约束

添加非空约束：

ALTER TABLE stuinfo MODIFY COLUMN stuname varchar(20) NOT NULL;

添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

添加主键：

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

添加唯一：

1.列级约束：

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

2.表级约束：

ALTER TABLE stuinfo MODIFY ADD UNIQUE(seat);

添加外键：

ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

添加列级约束：

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

添加表级约束：

ALTER TABLE 表名 ADD (CONSTRAINT 约束名) 约束类型（字段名）;

修改表时删除约束

删除非空约束：

ALTER TABLE stuinfo MODIFY COLUMN stuname varchar(20) NULL;

删除默认约束：

ALTER TABLE stuinfo MODIFY COLUMN age INT;

删除主键：

一个表就一个主键，所以不用说明字段

ALTER TABLE stuinfo DROP PRIMARY KEY;

删除唯一：

ALTER TABLE stuinfo DROP INDEX 字段名; 

删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY 外键名;

标识列

创建表时设置标识列

CREATE TABLE tab_identify(
		id INT PRIMARY KEY AUTO_INCREMENT,
		stuname VARCHAR(20)
);
INSERT INTO tab_identify
VALUES(NULL,'niubi');

显示当前自增长的起始值和步长

SHOW VARIABLES LIKE '%auto_increment%';

MySQL中可以通过修改设定这两个值，但是修改起始值没有效果，只有步长才有效果。

但可直接在插入第一个数据时就输入值来确定起始值

INSERT INTO tab_identify
VALUES(10,'niubi');

修改步长：

SET auto_increment_increment=步长;

特点

1. 标识列不一定要和主键搭配，但要求是一个key

2. 一个表最多有一个增长列

3. 标识列的类型只能是数值型

4. 标识列可通过

5. SET auto_increment_increment=数值
   

6. 修改步长

修改表时设置标识列

ALTER TABLE 表名 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

修改表时删除标识列

ALTER TABLE 表名 MODIFY COLUMN id;

TCL语言

事务控制语言

事务：事务由单独单元的一个或多个SQL语句组成，在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体﹐如果单元中某条SQL语句一且执行失败或产生错误﹐整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态﹔如果单元中的所有sQL语句均执行成功﹐则事物被顺利执行。

在mysql中的数据用各种不同的技术存储在文件（或内存）中。

可以通过SHOW ENGINES;来查看MySQL支持的存储引擎

在mysql中用的最多的存储引擎有: innodb，myisam , memory等，其中innodb支持事务，而myisam、memory等不支持事务。

事务的属性：

1.原子性(Atomicity )
原子性是指事务是一个不可分割的工作单位，事务中的探作安么都发生，要么都不发生。
2.一致性( Consistency)
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
3.隔离性（lsolation)
事务的隔离性是指一个事务的执行不能被其他事务干扰,
即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
4.持久性(Durability)
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

事务的创建

隐式事务：事务没有明显的开启和结束的标记比如INSERT、UPDATE、DELETE语句

显示事务：事务具有明显的开启和结束的标记

前提：必须先设置自动提交功能为禁用

SET AUTOCOMMIT=0;

步骤：

1. SET AUTOCOMMIIT=0;
   START TRANSACTION;#可以不输入，设置为0就已经为事务开启状态
   

   2.编写事务中的SQL语句（SELECT、INSERT、UPDATE、DELETE）

   ​ 语句1;

   ​ 语句2;

   ​ ...

   3.结束事务

   ​ 提交事务：commit;

   ​ 回滚事务：rollback;

例子：

CREATE TABLE account(
		id INT PRIMARY KEY AUTO_INCREMENT,
		username varchar(20),
		balance DOUBLE
);
INSERT INTO account
VALUES(NULL,'one',1000),(NULL,'two',1000);

#开启事务SET autocommit=0;START TRANSACTION;#编写一组事务的语句UPDATE account SET balance=500 WHERE username='one';UPDATE account SET balance=1500 WHERE username='two';#结束事务COMMIT；

DELETE和TRUNCATE的区别：

前者可以回滚，后者不可回滚

事务并发问题：

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题

事务的隔离级别

1. 脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
2. 不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
3. 幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

MySQL中默认REPEATABLE READ

Oracle中默认READ COMMITED

查看隔离级别：

SELECT @@tx_isolation;

设置隔离级别：

SET session TRANSACTION ISOLATION LEVEL 隔离级别；

设置数据库系统的全局的隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

回滚点

SAVEPOINT的使用：

SET autocommit=0;START TRANSACTION;DELETE FROM account WHERE id=25;SAVEPOINT a;#设置保存点DELETE FROM account WHERE id=28;ROLLBACK TO a;#回滚到保存点

视图

虚拟表，和普通表一样使用，MySQL5.1版本出现的新特性，是通过表动态生成的数据

好处：

1. 重用sql语句
2. 简化复杂的sql操作，不必知道它的查询细节
3. 保护数据，提高安全性

平常的查询：

SELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.majorId = m.id;WHERE s.stuname LIKE '张%';

把平常经常使用的语句封装成视图

#封装CREATE VIEW v1ASSELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.majorId = m.id;#封装后的查询SELECT * FROM v1 WHERE LIKE '张%';

创建

CREATE VIEW 视图名AS语句

使用

SELECT * FROM 视图名 （WHERE 筛选条件）;

修改

方法一：

CREATE OR REPLACE VIEW 视图名AS查询语句；

方式二：

ALTER VIEW 视图名AS查询语句;

删除

DROP VIEW 视图名,视图名...;

更新

插入

INSERT INTO 视图名 VALUES(字段名);

对原始表也有修改

修改

UPDATE 视图名 SET 字段=值 （WHERE 筛选）;

对原始表也有修改

删除

DELETE FROM 视图名 WHERE 筛选条件; 

包含以下关键字的视图不能进行更新：

• 分组函数、distinct、group by、 having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

视图与表的比较

视图没有占用实际的物理空间，只是保存了SQL的逻辑

表占用了实际的物理空间，保存了数据

变量

系统变量

系统变量有全局变量和会话变量

查看系统全局变量：

SHOW GLOBAL VARIABLES;

查看系统会话变量：

SESSION 可以省略

SHOW （SESSION） VARIABLES;

查看指定某个系统变量的值：

SELECT @@GLOBAL|（SESSION）系统变量名;

为系统变量赋值：

方式一：

SET GLOBAL |(SESSION) 系统变量名 = 值;

方式二：

SET @@GLOBAL|(SESSION) 系统变量名 = 值;

自定义变量

用户变量

自定义变量，针对于当前会话有效，同于会话变量的作用域

声明并初始化

赋值的操作符:=或者:=

SET @用户变量名=值;
或
SET @用户变量名:=值
或
SELECT @用户变量名:=值;#这里只能用:=，如果用=就不知道是比较还是赋值

赋值、更新

方式一：

SET @用户变量名=值;
或
SET @用户变量名:=值
或
SELECT @用户变量名:=值;

方式二：

SELECT 字段 INTO 变量名 FROM 表; 

查看

SELECT @用户变量名;

局部变量

仅仅在定义它的begin end中有效，应用在begin end中的第一句话

声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

赋值

方式一：

SET 局部变量名=值
或
SET 局部变量名:=值
或
SELECT @用户变量名:=值;

方式二：

SELECT 字段 INTO 局部变量名 FROM 表;

使用

SELECT 局部变量名;

存储过程

一组预先编译好的SQL语句的集合，理解成批处理语句

好处：

1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

可以有0个返回，也可以有多个返回，适合做批量插入、批量更新

创建

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END

注意：

参数列表包含三部分

参数模式 参数名    参数类型
IN      stuname  VARCHAR(20);

参数模式：

IN 该参数可以作为输入，也就是该参数需要调用传入值
OUT 该参数可以作为输出，也就是该参数可以作为返回值
INOUT 该参数既可以作为输入又可以作为输出

如果存储过程体仅仅只有一句话，BEGIN END可以省略

存储过程体中的每条SQL语句的结尾要求必须加分号

存储过程的结尾可以使用DELIMITER重新设置结束标记

原因：

就是告诉MySQL解释器，该段命令是否已经结束了，MySQL数据库是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，MySQL将会执行该命令。默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。因为MySQL一遇到分号，它就要自动执行。这种情况下，就需要事先把delimiter换成其它符号，如//或$$。

语法：

DELIMITER 结束标记;

调用

语法：

CALL 存储过程名(实参列表);

空参列表存储过程

USE girls;
DELIMITER $
CREATE PROCEDURE one()
BEGIN
		INSERT INTO admin(username,`password`)
		VALUES('john','0000'),('john','0000'),('john','0000'),('john','0000');
END $
CALL one$

IN模式的存储过程

USE girls;
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `two`(IN beautyName VARCHAR(20))
BEGIN
		SELECT bo.*
		FROM boys bo
		RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
		WHERE b.`name`= beautyName;
END $
CALL two('小昭')$

创建存储过程实现用户是否登陆成功：

USE girls;
DELIMITER $
CREATE PROCEDURE three(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
		DECLARE result INT DEFAULT 0;#声明一个变量并初始化
		SELECT COUNT(*) INTO result#赋值
		FROM admin
		WHERE admin.username=username
		AND admin.`password`= PASSWORD;
		
		SELECT IF(result>0,'成功','失败');
END $
CALL three('张飞','8888')$
		

OUT模式的存储过程

USE girls;
DELIMITER $
CREATE PROCEDURE four(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
		SELECT bo.boyName
		FROM boys bo
		INNER JOIN beauty b ON bo.id = b.boyfriend_id
		WHERE b.`name`=beautyName;
END $ 
CALL four('小昭',@bName)$
SELECT @bName$

变量@bName不需要声明，但是需要赋值以后才可以使用，set语句相当于赋值，call也相当于赋值，因此完全可以省略set语句。

INOUT模式的存储过程

传入a、b两个值，最终a、b的值翻倍并返回

USE girls;
DELIMITER $
CREATE PROCEDURE five(INOUT a INT,INOUT b INT)
BEGIN
		SET a = a*2;
		SET b = b*2;
END $
SET @m=10$
SET @n=20$
CALL five(@m,@n)$
SELECT @m,@n$

删除

DROP PROCEDURE 存储过程名;

查看

SHOW CREATE PROCEDURE 存储过程名;

函数

一组预先编译好的SQL语句的集合，理解成批处理语句

好处：

1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

有且仅有1个返回，适合做处理数据

创建

语法：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
		函数体
END

参数列表包含两个部分：参数名 参数类型

函数体肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不不报错，但不建议

当函数体只有一句话，则可以省略BEGIN END

使用DELIMITER语句设置结束标记

调用

SELECT 函数名(参数列表)

无参有返回

案例：返回公司的员工个数

USE myemployees;
DELIMITER $
CREATE FUNCTION f1() RETURNS INT
BEGIN
		DECLARE c INT DEFAULT 0;#定义变量
		SELECT COUNT(*) INTO c#赋值
		FROM employees;
		RETURN c;
END $
SELECT f1()$

有参有返回

USE myemployees;
DELIMITER $
CREATE FUNCTION f2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
		SET @sal=0;
		SELECT salary INTO @sal
		FROM employees
		WHERE last_name = empName;
		RETURN @sal;
END $
SELECT f2('kochhar')$

查看

SHOW CREATE FUNCTION 函数名;

删除

DROP FUNCTUON 函数名;

流程控制

顺序结构

程序从上网下依次执行

分支结构

IF函数

语法：

IF(表达式1,表达式2,表达式3);

如果表达式1成立，则IF函数返回表达式2的值，否则返回表达式3的值

case结构

实现等值判断：

cASE变量|表达式|字段
WHEN要判断的值THEN返回的值1或者语句1;
WHEN要判断的值 THEN返回的值2或者语句2;
ELSE要返回的值n或者语句n;
END CASE;

实现区间判断：

CASE 
WHEN要判新的值 THEN返回的值1或者语句1;
WHEN要判新的值 THEN返回的值2或者语句2;
ELSE要返回的值或者语句n;
END CASE;

特点：

1. 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方BEGIN END中或者外面
2. 可以作为独立的语句取使用，只能放在BEGIN END中
3. 如果WHEN中的值满足或者条件成立，则执行对应的THEN后面的语句，并且结束CASE，如果都不满足，则执行ELSE中的语句或者值
4. ELSE可以省略，如果ELSE省略了，并且所有WHEN条件都不满足则返回ＮＵＬＬ

IF结构

实现多重分支

IF 条件1
THEN 语句1;
ELSEIF 条件2
THEN 语句2;
...
END IF;

循环结构

while

(标签)WHILE 循环条件 DO
		循环体
END WHILE(标签)；

loop

可以用于模拟简单的死循环

(标签)LOOP
	循环体;
END LOOP;

repeat

(标签)REPEAT
			循环体;
UNTIL 结束循环的条件;
END REPEAT(标签);

循环控制

iterate：类似于continue，继续，结束本次循环，继续下一次。

leave：类似于break，跳出，结束当前所在的循环。