MySQL基础

数据库的相关概念

一、数据库的好处

1. 可以持久化数据到本地
2. 结构化查询

二、数据库的常见概念

1、DB：数据库，存储数据的容器

2、DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理 DB
3、SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

三、数据库存储数据的特点

1、数据存放到表中，然后表再放到库中
2、一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3、表中有一个或多个列，列又称为“字段”，相当于java中“属性”
4、表中的每一行数据，相当于java中“对象”

四、常见的数据库管理系统

mysql、oracle、db2、sqlserver

五、MySQL 的优点

1、开源、免费、成本低
2、性能高、移植性也好
3、体积小，便于安装

DQL 语言

基础查询

一、语法

select 查询列表
from 表名;

二、特点

1、查询列表可以是字段、常量、表达式、函数，也可以是多个
2、查询结果是一个虚拟表

三、示例

1、查询单个字段

select 字段名 from 表名;

SELECT `last_name` FROM `employees`;

2、查询多个字段

select 字段名，字段名 from 表名;

SELECT `last_name`,`email`,`employee_id`
FROM employees;

3、查询所有字段

select * from 表名

SELECT * FROM `employees`;

4、查询常量

select 常量值;
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

SELECT 100 ;

5、查询函数

select 函数名(实参列表);

SELECT DATABASE();
SELECT VERSION();
SELECT USER();

6、查询表达式

select 100/1234;

select 100/1234;

7、起别名

①as
②空格

#方式一：使用as关键字

SELECT USER() AS 用户名;
SELECT USER() AS "用户名";
SELECT USER() AS '用户名';

SELECT last_name AS "姓 名" FROM employees;


#方式二：使用空格


SELECT USER()   用户名;
SELECT USER()   "用户名";
SELECT USER()   '用户名';

SELECT last_name   "姓 名" FROM employees;

8、去重

select distinct 字段名 from 表名;

#需求：查询员工涉及到的部门编号有哪些


SELECT DISTINCT department_id FROM employees;

9、+

作用：做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;结果都为null

-- 需求：查询 first_name 和last_name 拼接成的全名，最终起别名为：姓 名

#方案1：使用+    pass×
SELECT first_name+last_name AS "姓 名"
FROM employees;



#方案2：使用concat拼接函数

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

10、【补充】concat 函数

功能：拼接字符
select concat(字符1，字符2，字符3,...);

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

11、【补充】ifnull 函数

功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值

select ifnull(commission_pct,0) from employees;

12、【补充】isnull 函数

功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0

条件查询

一、语法

select 查询列表
from 表名
where 筛选条件

#案例：查询工资<15000的姓名、工资
SELECT last_name,salary
FROM employees
WHERE salary<15000;

二、筛选条件的分类

1、简单条件运算符

> < = <> != >= <=  <=>安全等于

#案例：查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id <> 100;

2、逻辑运算符

&& and
|| or
!  not

#案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
#方式1：
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;
#方式2：


SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);

3、模糊查询

like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符

#案例1：查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';

4、其他

between and

 功能：判断某个字段的值是否介于xx之间

between and/not between and

#案例1：查询部门编号是30-90之间的部门编号、员工姓名

#方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

in

功能：查询某字段的值是否属于指定的列表之内
a  in(常量值1,常量值2,常量值3,...)
a not in(常量值1,常量值2,常量值3,...)
in/not in
is null /is not null：用于判断null值

#案例1：查询部门编号是30/50/90的员工名、部门编号


#方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

is null/is not null

is null PK <=>
              普通类型的数值         null值               可读性
is null              ×                    √                 √
<=>                  √                    √                 ×

#案例1：查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;

#案例2：查询有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

=		只能判断普通的内容

IS      只能判断NULL值

<=>     安全等于，既能判断普通内容，又能判断NULL值

SELECT *
FROM employees
WHERE salary <=> 10000;

SELECT *
FROM employees
WHERE commission_pct <=> NULL;

排序查询

一、语法

select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc}desc】

二、特点

1、asc ：升序，如果不写默认升序
   desc：降序

2、排序列表 支持 单个字段、多个字段、函数、表达式、别名

3、order by的位置一般放在查询语句的最后（除limit语句之外）

三、示例

1、按单个字段排序

#案例1：将员工编号>120的员工信息进行工资的升序
SELECT *
FROM employees

ORDER BY salary ;

#案例1：将员工编号>120的员工信息进行工资的降序
SELECT *
FROM employees
WHERE employee_id>120
ORDER BY salary DESC;

2、按表达式排序

#案例1：对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

3、按别名排序

#案例1：对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees

ORDER BY 年薪 DESC;

4、按函数的结果排序

#案例1：按姓名的字数长度进行升序
SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

5、按多个字段排序

#案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;

6、补充选学：按列数排序

SELECT * FROM employees
ORDER BY 2 DESC;

SELECT * FROM employees
ORDER BY first_name;

常见函数

一、字符函数

方法	描述
concat(str1,str2)	拼接
substr(str,pos)	截取从 pos 开始的所有字符，起索引从 1 开始
substr(str,pos,len)	截取 len 个从 pos 开始的字符，起始索引从 1 开始
Length(str)	获取字节个数
upper(str)	变大写
lower(str)	变小写
trim(【substr from】str)	去前后之后指定的字符，默认是去空格
left(str,len)	从左边截取指定 len 个数的字符
right(str,len)	从右边截指定 Len 个数的字符
lpad(str,substr,len)	左填充
rpad(str,substr,len)	右填充
strcmp(str1,str2)	比较两个字符的大小
instr(str,substr)	获取 substr 在 str 中的第一次出现的索引

示例：

1. CONCAT 拼接字符

SELECT CONCAT('hello', first_name,last_name) 备注
FROM employees;

2. LENGTH 获取字节长度

SELECT LENGTH('hello');

3. CHAR_LENGTH 获取字符个数

SELECT CHAR_LENGTH('hell,xiaoming');

4. SUBSTRING 截取子串

SELECT SUBSTR('hellowoorld',1,3);
SELECT SUBSTR('hellowoorld',2);

5. INSTR 获取字符第一次出现的索引

SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');
SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');

6. TRIM 去前后指定的字符，默认是去空格

SELECT TRIM('  xiaoming  ') AS a;

7. LPAD/RPAD 左填充/右填充

SELECT LPAD('小明',10,'a');

8. UPPER/LOWER 变大写/变小写

   案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) AS 'OUTPUT'
FROM employees;

9. STRCMP 比较两个字符大小

SELECT STRCMP('aec','aec');
SELECT STRCMP('adc','cfg');

10. LEFT/RIGHT 截取子串

SELECT LEFT('鸠摩智',1);
SELECT RIGHT('鸠摩智',1);

二、数字函数

方法	描述
ceil(x)	向上取整
floor(X)	向下取整
round(x,d)	四舍五入
mod(x,y)	取模/取余
truncate(x,d)	截取，保留小数点后 d 位
abs(x)	求绝对值

示例：

1. ABS 绝对值

SELECT ABS(-2.4);

2. CEIL 向上取整 返回>=该参数的最小整数

SELECT CEIL(-1.77999);
SELECT CEIL(-1.09);
SELECT CEIL(0.09);

3. FLOOR 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);

4. ROUND 四舍五入

SELECT ROUND(4,555);
SELECT ROUND(1.8712345,2);

5. TRUNCATE 截断

SELECT TRUNCATE(1.8776787,2);

6. MOD 取余

SELECT MOD(10,3); // 1
SELECT MOD(-10,3); // -1
SELECT MOD(10,-3); // 1

三、日期函数

方法	描述
now()	获取当前日趋–时间
curtime()	只有时间
curdate()	只有日期
date_format(date,格式)	格式日期为字符
str_to_date(str，格式)	将字符转换为日期
datediff(date1,date2)	获取两个日期之间的天差数
year(date)	获取年
month(date)	获取月

1. NOW

SELECT NOW();

2. CURDATE

SELECT CURDATE();

3. DATEDIFF

SELECT DATEDIFF('1998-7-16','2019-7-13');

4. DATE_FORMAT

SELECT DATE_FORMAT('1998-7-19','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期
SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')入职日期

5. STR_TO_DATE 按指定格式解析字符串为日期类型

SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');

四、流程控制语句

① if(条件，表达式 1, 表达式 2)：如果条件成立，返回表达式 1，否则返回表达式 2

② case 表达式

when 值 1 then 结果 1

when 值 2 then 结果 2

….

else 结果 n

end

③ case

when 条件 1 then 结果 1

when 条件 2 then 结果 2

…

else 结果 n

end

示例：

1. IF 函数

SELECT IF(100>9,'好','坏');

2. 需求：如果有奖金，则显示最终奖金，如果没有，则显示 0

SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct) 奖金
FROM employees;

3. CASE 函数

案例：
部门编号是 30，工资显示为 2 倍
部门编号是 50，工资显示为 3 倍
部门编号是 60，工资显示为 4 倍
否则不变
显示 部门编号，新工资，旧工资

SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END newsalary
FROM employees;

案例：如果工资>20000,显示级别 A
工资>15000,显示级别 B
工资>10000,显示级别 C
否则，显示 D

SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS '等级'
FROM employees

分组函数

方法	描述
sum	求和
avg	求平均数
max	求最大值
min	求最小值
count	计算非空字段值的个数

示例：

1. #案例 1 ：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数。

SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary)
FROM employees

分组查询

一、语法

select 分组函数，分组的字段

from 表名

where 分组前的筛选条件

grounp by 分组列表

having 分组后的筛选

order by 排序列表

二、特点

​ ① 分组列表可以是单个字段、多个字段

​ ② 筛选条件分为两类

	筛选的基表	使用的关键字	位置
分组前筛选	原始表	where	group by 前面
分组后筛选	分组后的结果集	having	group by 后面

三、执行顺序

​ ① from 子句

​ ② where 子句

​ ③ group by 子句

​ ④ having 子句

​ ⑤ select 子句

​ ⑥order by 子句

示例：

​ 1. 查询每个工种的员工平均工资

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

2. 查询哪个部门的员工个数>5

SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

3. 每个工种有奖金的员工的最高工资>12000 的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

4. 查询没有奖金的员工的最高工资>6000 的工种编号和最高工资,按最高工资升序

SELECT MAX(salary) 最高工资, job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;

5. 查询每个工种每个部门的最低工资,并按最低工资降序 (工种和部门都一样，才是一组)

SELECT MIN(salary) 最低工资,job_id,department_id
FROM employees
GROUP BY job_id,department_id

连接查询

说明：当查询中涉及到了多个字段，则需要通过多表连接

笛卡尔乘积：

​ 出现原因：没有有效的连接条件

​ 解决办法：添加有效的连接条件

一、SQL92 语法

语法：

select 查询列表 ①

from 表 1 别名， 表 2 别名 ②

where 连接条件 ③

and 筛选条件 ④

group by 分组列表 ⑤

having 分组后的筛选 ⑥

order by 排序列表 ⑦

执行顺序：② ③ ④ ⑤ ⑥ ① ⑦

1、等值连接

语法：

select 查询列表

from 表 1 别名， 表 2 别名

where 表 1.key= 表 2.key

【and 筛选条件】

【group by 分组字段】

【having 分组后的筛选】

【order by 排序字段】

代码示例：

1. 等值连接：查询女神名和对应的男神名

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

2. 查询有奖金的员工名、部门名

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

3. 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name, d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`department_id`;

4. 查询每个工种的工种名和员工的个数，并且按员工个数降序

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

5. 三表连接: 查询员工名、部门名和所在的城市

SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'

ORDER BY department_name DESC;

2、非等值连接

语法：

select 查询列表

from 表 1 别名,表 2 别名

where 非等值的连接条件

【and 筛选条件】

【group by 分组字段】

【having 分组后的筛选】

【order by 排序字段】

3、自连接

语法：

select 查询列表

from 表 别名 1,表 别名 2

where 等值的连接条件

【and 筛选条件】

【group by 分组字段】

【having 分组后的筛选】

【order by 排序字段】

代码示例：

自连接: 查询 员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

二、SQL99 语法

1、内连接

语法：

select 查询列表 ①

from 表 1 别名 ②

【inner】 join 表 2 on 连接条件 ③

【inner】 join 表 3 on 连接条件 ③

where 筛选条件 ④

group by 分组列表 ⑤

having 分组后的筛选 ⑥

order by 排序列表 ⑦

执行顺序： ② ③ ④ ⑤ ⑥ ① ⑦

特点：

​ ① 表的顺序可以调换

​ ② 内连接的结果=多表的交集

​ ③ n 表连接至少需要 n-1 个连接条件

分类：

1. 等值连接

   2. 非等值连接
   3. 自连接

代码示例:

1. 查询员工名和部门名

SELECT last_name,department_name
FROM departments d
JOIN employees e
ON e.`department_id`=d.department_id;

2. 添加筛选条件: 查询部门编号>100 的部门名和所在的城市名

SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;

3. 添加分组+筛选+排序: 查询部门中员工个数>10 的部门名，并按员工个数降序

SELECT department_name,COUNT(*) 员工个数
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

4. 非等值连接: 查询部门编号在 10-90 之间的员工的工资级别，并按级别进行分组

SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary`AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.`grade`

5. 自连接: 查询员工名和对应的领导名

SELECT e.`last_name`, m.last_name
FROM employees e
JOIN employees  m
ON e.`manager_id`=m.employee_id;

2、外连接

语法：

select 查询列表

from 表 1 别名

left | right| full 【outer】 join 表 2 别名 on 连接条件

where 筛选条件

group by 分组后的筛选

order by 排序列表

特点：

​ ① 查询的结果 = 主表的所有的行，如果从表和它比配的将显示匹配行，如果从表没有匹配的则显示 null

​ ② left join 左边的就是主表，right join 右边的就是主表， full jion 两边都是主表

​ ③ 一般用于查询除了交集部分的剩余的不匹配的行

代码示例：

1. 查询所有女神记录，以及对应的男神名,如果没有对应的男神，则显示为 null。

左连接

SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

右连接：

SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN beauty b ON b.`boyfriend_id` = bo.`id`;

子查询

一、含义

• 嵌套在其他语句内部的 select 语句称为子查询或内查询
• 外面的语句可以是 insert、update、select 等，一般 select 作为外面的语句较多
• 外面如果为 select 语句，则此语句称为外查询或主查询

二、分类

按出现位置

select 后面

​ 仅仅支持标量子查询

from 后面

​ 表子查询

where 或 having 后面

​ 标量子查询

​ 列子查询

​ 行子查询

​ 表子查询

按结果集的行列

标量子查询(单行子查询)：结果集为一行一列

列子查询(多行子查询):结果集为多行一列

行子查询：结果集为多行多列

表子查询：结果集为多行多列

代码示例：

1. 查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
    );

2. 返回 job_id 与 141 号员工相同，salary 比 143 号员工多的员工姓名，job_id 和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id =(
      SELECT job_id
      FROM employees
      WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

3. 多行子查询:返回 location_id 是 1400 或 1700 的部门中的所有员工姓名

SELECT last_name
FROM employees
WHERE department_id IN(
    # distinct 去重
    SELECT distinct department_id
    FROM departments
    WHERE location_id IN(1400,1700)
    );

4. 放在 select 后面: 查询部门编号是 50 的员工个数

SELECT (
	SELECT COUNT(*)
	FROM employees
	WHERE department_id = 50
) 个数;

5. 放在 from 后面: 查询每个部门的平均工资的工资级别

SELECT dep_ag.department_id,dep_ag.ag,g.grade
FROM sal_grade g
JOIN (

	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id

) dep_ag ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

6. 放在 exists 后面

SELECT EXISTS(
	SELECT *
	FROM employees
	WHERE last_name='Abel'
) 有无Able;

分页查询

一、应用场景

当要查询的条目数太多，一页显示不全

二、语法

select 查询列表

from 表

limit【offset】size

注意：

• offset 代表的是起始条目的索引，默认开始从 0 开始
• size 代表的是条目数

公式：

假如要显示的页数为 page,每一页的条目数为 size

select 查询列表

from 表

limit(page - 1)* size, size

代码示例：

1. 查询员工信息表的前 5 条

SELECT * FROM employees LIMIT 0,5;

2. 查询年薪最高的前 10 名

SELECT last_name, salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC
LIMIT 0,10

3. 查询有奖金的，且工资较高的第 11 名到第 20 名

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10,10;

联合查询

一、含义

​ union: 合并、联合，将多次查询的结果合并成为一个点结果

二、语法

查询语句 1

​ union【all】

查询语句 2

​ union【all】

…..

三、意义

1. 将一条比较复杂的查询语句拆分成为多条语句
2. 适用于查询多个表的时候，查询的列表是一致

四、特点

1. 要求多查询列数必须一致
2. 要求多条查询语句的查询的各列类型、顺序最好一致
3. union 去重，union all 包含重复项

代码示例：

1. 查询所有国家的年龄>20 岁的用户信息

SELECT * FROM usa WHERE uage > 20 UNION
SELECT * FROM chinese WHERE age >20

2. 查询所有国家的用户姓名和年龄

SELECT uname,uage FROM usa
UNION
SELECT age,'name' FROM chinese

3. union 自动去重/union all 可以支持重复项

SELECT 1,'小明'
UNION ALL
SELECT 1,'小明'
UNION  ALL
SELECT 1,'小明'
UNION  ALL
SELECT 1,'小明' ;

DDL 语言

库的管理

① 创建库

create database 【if not exists】库名 【characster set 字符集名】

② 修改表

alter database 库名 character set 字符集名；

③ 删除表

drop database 【if exists】 库名；

代码示例：

1. 创建数据库

CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;

2. 删除数据库

DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;

表的管理

① 创建表

create table【if not exists】表名（

​ 字段名 字段类型【约束】，

​ 字段名 字段类型【约束】，

​ 字段名 字段类型【约束】，

​ ……

）

② 修改表

1.添加列

alter table 表名 add conlumu 列名 类型【firat|after】

2.修改列的类型或约束

alter table 表名 modify column 列名 新类型【新约束】

3.修改列名

alter table 表名 change column 旧列名 新列名 类型

4.删除列

alter table 表名 drop column 列名

5.修改表名

alter table 表名 raname【to】 新表名

③ 删除表

drop table【if exists】 表名

④ 复制表

① 复制表的结构

creato table 表名 like 旧表

② 复制表的结构+数据

create table 表名 select 查询列表 from 旧表【where 筛选】

代码示例：

1. 没有添加约束

CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT ,
	stuname VARCHAR(20),
	stugender CHAR(1),
	email VARCHAR(20),
	borndate DATETIME

);

2. 添加约束

CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT PRIMARY KEY,#添加了主键约束
	stuname VARCHAR(20) UNIQUE NOT NULL,#添加了唯一约束+非空
	stugender CHAR(1) DEFAULT '男',#添加了默认约束
	email VARCHAR(20) NOT NULL,
	age INT CHECK( age BETWEEN 0 AND 100),#添加了检查约束，mysql不支持
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)#添加了外键约束

);

3. 修改表

#1.修改表名

ALTER TABLE stuinfo RENAME TO students;

#2.添加字段
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL;

DESC students;

#3.修改字段名

ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;

#4.修改字段类型

ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;

#5.删除字段

ALTER TABLE students DROP COLUMN birthday;

DESC students;

4. 删除表

DROP TABLE IF EXISTS students;

5. 复制表

#仅仅复制表的结构

CREATE TABLE newTable2 LIKE major;

#复制表的结构+数据

CREATE TABLE newTable3 SELECT * FROM girls.`beauty`;


#案例：复制employees表中的last_name,department_id,salary字段到新表 emp表，但不复制数据
CREATE TABLE emp
SELECT lat_name,department_id,salary
FROM myemployees.`employees`
WHERE 1=2;

数据类型

一、数值型

1、整型

tinyint 、 sallint、mediumint、int/integer、bigint

1 2 3 4 8

特点：

① 都可以设置无符号和有符号，默认通过 unsigned 设置无符号

② 如果超出范围，会报 out or range 异常，插入临界值

③ 长度可以不指定，默认会有一个长度，长度代表显示的最大宽度，如果不够则左边用 0 填充，但需要搭配 zerofill，并且默认变为无符号整型。

2、浮点型

定点数：decimal(M,D)

浮点数：

​ float(M,D) 4

​ double(M,D) 8

特点：

① M 代表整数+ 小数部位的个数，D 代表小数整位

② 如果超出范围，则报 out or range 异常，并且插入临界值

③ M 和 D 都可以省略，但对于定点数，M 默认为 10， D 默认为 0

④ 如果精度要较高，则优先考虑使用定点数

二、字符型

char 、varchar、binary、enum、set、text、blob

char: 固定长度的字符，写法为 char(M),最大长度不能超过 M，其中 M 可以省略，默认为 1

varchar: 可变长度的字符，写法为 varchar(M),最大长度不能 M，不可省略 M

三、日期型

year 年

date 日期

time 时间

datetime 日期时间 8

timestamp 日期+时间 4 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间

常见的约束

一、常见的约束

约束	描述
NOT NUll	非空，该字段的值必填
UnIQUE	唯一，该字段的值不可重复
DEFAULT	默认，该字段的值不用手动插入有默认值
CHECK	检查，mysql 不支持
PRIMARY KEY	主键，该字段的值不可重复并且非空 unique+not null
FPREIGN KEY	外键，该字段的值引用了另外的标的字段

主键和唯一

1.区别：

​ ① 一个表至多有一个主键，但可以有多个唯一

​ ② 主键不允许为空，唯一可以为空

2.相同点：

​ 都具有唯一性

​ 都支持组合键，但不推荐

外键：

① 用于限制两个表的关系，从表的字段值引用了主表的某个字段值

② 外键列和主表的被引用列要求类型一致，意义一样，名称无要求

③ 主表的被引用列要求是一个 key(一般就是主键)

④ 插入数据，先插入主表

​ 删除数据，先删除从表

可以通过以下两种方式来删除主表的记录

代码示例：

​ 方式一：级联删除

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

​ 方式二：级联置空

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

二、创建表的添加约束

create table 表名（

​ 字段名 字段类型 not null ,#非空

​ 字段名 字段类型 primary key,#主键

​ 字段名 字段类型 unique,#唯一

​ 字段名 字段类型 default 值， #默认

​ constraint 约束名 foreign key(字段名) references 主表(被引用列 1)

）

注意：

	支持类型	可以起约束名
列级约束	除了外键	不可以
表级约束	除了非空和默认	可以，但对主键无效

列级约束可以在一个字段上追加多个，中间用空格隔开，没有顺序要求

三、修改表时添加或删除约束

1.非空

​ 添加非空

​ alter table 表名 modify column 字段名 字段类型 not null;

​ 删除非空

​ alter table 表名 modify column 字段名 字段类型;

2.默认

​ 添加默认

​ alter table 表名 modify columu 字段名 字段类型 default 值;

​ 删除默认

​ alter table 表名 modify column 字段名 字段类型;

3.主键

​ 添加主键

​ alter table 表名 add【 constraint 约束名】 primary key(字段名)；

​ 删除主键

​ alter table 表名 add drop primary key;

4.唯一

​ 添加唯一

​ alter table 表名 add【constraint 约束名】 unique(字段名);

​ 删除唯一

​ alter table 表名 drop index 索引名;

5.外键

​ 添加外键

​ alter table 表名 add【constraint 约束名】 foreign key(字段名) references 主表(被引用列)

​ 删除外键

​ alter table 表名 drop foreign key 约束名;

四、自增长列

特定：

​ 1.不用手动插入值，可以自动提供序列值，默认从 1 开始，步长为 1

​ auto_increment_increment

​ 如果要更改起始值：手动插入值

​ 如果要更改步长：更改系统变量

​ set auto_increment_increment=值

​ 2.一个表至多有一个自增长列

​ 3.自增长列只能支持数值型

​ 4.自增长列必须为一个 key

创建表时设置自增长列

​ create table 表(

​ 字段名 字段类型 约束 auto_increment

​ )

修改表时设置自增长列

​ alter table 表 modify column colum 字段名 字段类型 约束 auto_increment

删除自增长列

​ alter table 表 modify column 字段名 字段类型 约束

DML 语言

插入

1、方式一

语法：

insert into 表名(字段名,…) values(值,…);

特点：

​ 1、要求值的类型和字段的类型要一致或兼容

​ 2、字段的个数和顺序不一定与原始表中的字段个数和顺序一致 但必须保证值和字段一一对应

​ 3、假如表中有可以为 null 的字段，注意可以通过以下两种方式插入 null 值

​ ① 字段和值都省略 ② 字段写上，值使用 null

​ 4、字段和值的个数必须一致

​ 5、字段名可以省略，默认所有列

2、方式二

语法：

insert into 表名 set 字段=值,字段=值,…;

两种方式 的区别：

​ 1.方式一支持一次插入多行，语法如下： insert into 表名【(字段名,..)】 values(值，..),(值，…),…;

​ 2.方式一支持子查询，语法如下： insert into 表名 查询语句;

修改

1、修改单表的记录

语法：update 表名 set 字段=值，字段=值【where 筛选条件】

2、修改多表的记录【补充】

语法：

update 表 1 别名

left|right|inner join 表 2 别名 on

连接条件 set 字段=值，字段=值

【where 筛选条件】

删除

1、使用 delete

删除单表的记录

​ 语法：delete 别名 1，别名 2 from 表 1 别名

​ inner| left | right join 表 2 别名 on 连接条件

​ 【where 筛选条件】

2、 使用 truncate

语法：truncate table 表名

3、两种方式的区别

#【面试题】delete 和 truncate 的区别

1.delete 可以添加 WHERE 条件

​ TRUNCATE 不能添加 WHERE 条件，一次性清除所有数据

2.truncate 的效率较高

3.如果删除带自增长列的表，

​ 使用 DELETE 删除后，重新插入数据，记录从断点处开始

​ 使用 TRUNCATE 删除后，重新插入数据，记录从 1 开始

​ SELECT * FROM gradeinfo;

​ TRUNCATE TABLE gradeinfo;

​ INSERT INTO gradeinfo(gradename)VALUES(‘一年级’),(‘2 年级’),(‘3 年级’);

4.delete 删除数据，会返回受影响的行数

​ TRUNCATE 删除数据，不返回受影响的行数

5.delete 删除数据，可以支持事务回滚

​ TRUNCATE 删除数据，不支持事务回滚

代码示例：

1. 添加

INSERT INTO stuinfo values(1,'吴倩','女','wuqian@qq.com','2001.1.2');

2. 修改(修改年龄<20 的专业编号为 3 号，且邮箱更改为 xx@qq.com)

UPDATE stuinfo SET majorid = 3,email = 'xx@qq.com'
WHERE age<20;

3. 删除

#案例1：删除姓李所有信息
DELETE FROM stuinfo
WHERE stuname LIKE '李%'；
#案例2：删除表中所有数据
TRUNCATE TABLE stuinfo;

事务

1、含义

事务:一条或多条 sql 语句组成一个执行单位，一组 sql 语句要么都执行要么不执行

2、特点

A 原子性：一个事务是不可再分割的整体，要么都执行要么都不执行

C 一致性：一个事务可以使数据从一个状态切换到另一个一致的状态

I 隔离性： 一个事务不受其他事务的干扰，多个事务相互隔离的

D 持久性：一个事务一旦提交了，则永久的持久化到本地

3、事务的使用步骤

隐式（自动）事务：没有明显的开始的结束，本身就是一条事务可以自动提交，比如 insert、update、delete 显式事务：具有明显的开启和结束。

使用显式事务：

① 开启事务

set autocommit = 0;
start transaction：#可以省略

设置回滚点：

提交：commit:

回滚：rollback;

回滚到指定的地方；rollback to 回滚点名：

4、并发事务

脏读：一个事务读取其他事务还没有提交的数据，读到的是其他事务“更新”的数据

不可重复读：一个事务多次读取，结果不一样

幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务”插入”的数据

隔离级别:

	脏读	不可重复读	幻读
read uncommitted:读未提交	×	×	×
read committed:读已提交	√	×	×
repeatable read：可重复读	√	√	×
serializable：串行化	√	√	√