MySQL

一、为什么要学习数据库

数据库的好处

• 1.持久化数据到本地

• 2.可以实现结构化查询，方便管理

二、数据库的相关概念

• 1、DB ：数据库，保存一组有组织的数据的容器
• 2、DBMS ：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
• 3、SQL ：结构化查询语言，用于和DBMS通信的语言

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

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

四、初始MySQL

1、MySQL服务的启动和停止

方式一：

• 计算机——右击管理——服务

方式二：

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

2、MySQL服务的登录和退出

方式一：

• 通过mysql自带的客户端只限于root用户

方式二：

• 通过windows自带的客户端

• 登录：
  mysql 【-h主机名 -P端口号 】-u用户名 -p密码

  退出：
  exit或ctrl+C

3、MySQL的常见命令

• 1.查看当前所有的数据库 show databases;

• 2.打开指定的库 use 库名

• 3.查看当前库的所有表 show tables;

• 4.查看其它库的所有表 show tables from 库名

• 5.创建表

• create table 表名(
  列名 列类型,
  列名 列类型，
  。。。
  );

• 6.查看表结构 desc 表名;

• 7.查看服务器的版本

• 方式一：登录到mysql服务端
  select version();
  方式二：没有登录到mysql服务端
  mysql --version
  或
  mysql --V

4、MySQL语法规范

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

5、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

6、SQL的常见命令

• show databases； 查看所有的数据库
• use 库名； 打开指定 的库
• show tables ; 显示库中的所有表
• show tables from 库名;显示指定库中的所有表
• create table 表名(
  字段名 字段类型,
  字段名 字段类型
  ); 创建表
• desc 表名; 查看指定表的结构
• select * from 表名;显示表中的所有数据

五、DQL语言

1、基础查询

语法：

SELECT 查询列表
FROM 表名

类似于Java中 :System.out.println(要打印的东西);

特点：

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

1.查询表中的单个字段

select last_name from employees;

2.查询表中的多个字段

select last_name,salary,email from employees;

3.查询表中的所有字段

select * from employees;

4.查询常量值

select 100;

5查询表达式

select 100%98;

6.查询函数

select version(); 

7.起别名

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

方式一：使用as

select 100%98 as 结果;
select last_name as 姓,first_name as 名 from employees;

方式二：使用空格

select last_name 姓,first_name 名 from employees;

8.去重

select distinct department_id from employees;

9. + 号的作用

mysql中的+号 只有一个功能：
运算符 select 100+90；两个操作数都为数值型，则做加法运算
select ‘123’+90;其中一方为字符型，试图将字符型数值转换成数值型
如果转换失败，则将字符型数值转换成0 如果转换成功，则继续做加法运算
select null+0 只要其中一方为null，则结果为null

2、条件查询

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

分类：

一、按条件表达式筛选

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

二、逻辑表达式筛选

​ 作用：用于连接条件表达式

​ 逻辑运算符：
​ && || !
​ and or not

​ 三、模糊查询
​ like
​ between and
​ in
​ is null或not null

select * from employees where salary>12000;

select last_name,salary,commission_pct from employees where salary>=10000 and salary <=20000;

like

一般和通配符搭配使用

通配符：

%任意多个字符，包含0个字符

_任意个字符

select * from employees where last_name like '%a%';

select * from employees where last_name like '__n_l%';

特殊情况：字段中有_

使用转义字符

select * from employees where last_name like '_\_%';
select * from employees where last_name like '_$_%' escape '$';#（推荐使用）

between and

包含临界值

select * from employees where employee_id >=100 and employee_id <=120;

select * from employees where employee_id between 100 and 120;

in

列表的值类型必须一致或兼容

不支持通配符

select last_name,job_id from employees where job_id ='it_prot' or job_id ='ad_vp' or job_id ='ad_pres';

select last_name,job_id from employees where job_id in ('it_prot','ad_vp' , 'ad_pres');

is null或is not null

select last_name,commission_pct from employees where commission_pct is null;
或
select last_name,commission_pct from employees where commission_pct <=> null;

安全等于 <=>

不能使用=null

is null ：仅仅可以判断null值

<=> ：既可以判断null值，又可以判断普通的数值，可读性差

3、排序查询

语法
select 查询列表
from 表
where 筛选条件
order by 排序列表[asc|desc]

特点：

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

select * from employees order by salary desc ;#降序
select * from employees order by salary asc;#升序,可以省略不写

按表达式排序

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

按别名排序

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

按字节长度排序

select length(last_name) 字节长度,last_name,salary from employees order by
length(last_name) desc;

4、常见函数

功能：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：

• 1、隐藏了实现细节
• 2、提高代码的重用性

调用：select 函数名(实参列表) [from 表];
特点：函数名、函数功能
分类：

• 1、单行函数
  如 concat、length、ifnull等
• 2、分组函数
  功能：做统计使用

4.1、单行函数

~1、字符函数

~1.1、length 字节长度

select length(‘john’);

select length('你好你好')

注：在utf8编码下，中文占三个字节，所以一个中文的长度是3

~1.2、concat拼接

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

~1.3、substr截取子串

注意：索引从1开始

截取指定索引后面所有的字符

select substr('fjasfjalj',3) as out_put;

截取从指定索引处指定字符长度的字符

select substr('fjasfjalj',1,3)as out_put; 

~1.3、upper转换成大写

select upper('jhon');

~1.4、lower转换成小写

select lower('hoHn');

select concat(upper(last_name),lower(first_name))from employees ;

~1.5、trim去前后指定的空格和字符

去掉前面和后面的空格

select trim('       afsdjfla       ') as out_put; 

去掉前面和后面的指定字符

select trim('a' from 'aaaaaaaaaaaaaaaaaa张aaaaaaaaaaa翠山aaaaaaaaaaaaa') as 
out_put ;

select trim('aa' from 'aaaaaaaaaaaaaaaaa张aaaaaaaaaaa翠山aaaaaaaaaaaaa') as
out_put ; 

~1.6、replace替换

select replace ('周芷诺周芷诺周芷诺张无忌爱上了周芷诺','周芷诺','赵敏') out_put;

~1.7、lpad左填充

用指定的字符实现左填充指定长度

select lpad('殷素素',10,'*') as out_put; 

select lpad('殷素素',2,'*') as out_put;

~1.8、rpad右填充

用指定的字符实现右填充指定长度

select rpad('殷素素',10,'ab') as out_put; 

~1.9、instr返回子串第一次出现的索引

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

select instr('123456','34'); 

~2、数学函数

~2.1、round 四舍五入

select round(1.55); 

如果是复数，先取绝对值，再取整

select round(-1.55); 

保留指定位小数

select round(1.567,2); 

~2.2、floor向下取整

向下取整，返回<=改参数的最小整数

select floor(-9.99); 

~2.3、ceil向上取整

向上取整，返回>=改参数的最小整数

select ceil (1.02);

select ceil (-1.02);

~2.4、mod取余

mod(a,b)=a-(a/b)*b

select mod(20,-3);

select 20%3;

~2.5、truncate截断

select truncate(1.68,1); 

~3、日期函数

~3.1、now当前系统日期+时间

select now();

~3.2、curdate当前系统日期

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

select curdate(); 

~3.3、curtime当前系统时间

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

select curtime(); 

~3.4、str_to_date 将字符转换成日期

将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('2021-09-01 14:21:45','%Y-%c-%d') AS out_put;

select str_to_date('7-3 2021','%d-%m %Y') as out_put;

~3.5、date_format将日期转换成字符

select date_format(now(),'%y年%m月%d日') as out_put; 

~3.6、datediff 获取间隔时间

select datediff(now(),'1986-1-1'); 

~3.7、获取指定的部分，年、月、日、小时、分钟、秒

select year (now()) 年;
select year (2021-09-01) 年;

select month(now()) 月;
select monthname(now())月;

~4、流程控制函数

~4.1、if 处理双分支

select if(10<5,'大','小');

select last_name,commission_pct,if(commission_pct is null,'没奖金','有奖金') 
备注 from employees;

~4.2、case语句 处理多分支

使用一

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
...
else 要显示的值n或语句
end

案例：查询员工的工资，
要求 部门号=30，显示的工资位1.1倍
部门号=40，显示的工资为1.2倍 部门号=50，显示的工资为1.3倍
其它部门，显示为原工资

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 as 新工资
from employees;

使用二

case
 when 条件1 then 要显示的值1或语句1
 when 条件2 then 要显示的值2或语句2
 ...
 else 要显示的值n或语句n
 end

案例：查询员工的工资情况
如果工资>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;

~5、其他函数

select version();#当前版本
select database();#当前打开的数据库
select user();#当前的用户

4.2、分组函数

功能:用于统计使用，称为聚合函数或统计函数
分类:sum 求和、avg 平均值、min 最小值、count 计算个数

特点：

• 1、sum、avg一般用于处理数值型
  ​ max、min、count可以处理任何类型
• 2、以上分组函数都忽略null值

select sum(salary) 和,
avg(salary) 平均,
min(salary) 最低,
max(salary) 最高,
count(salary) 个数 
from employees;

支持的类型

max和min支持字符、日期、数字…

select max(last_name),min(last_name)from employees;

count支持任意数据类型

结果是该字段不为null的个数

以下函数忽略null值

select 
sum(commission_pct),
avg(commission_pct),
sum(commission_pct)/count(commission_pct),
count(commission_pct) 
from employees;

和distinct搭配

select 
sum(distinct salary),sum(salary),
count(distinct salary),count(salary) 
from employees;

count函数详细介绍

select count(*) from employees;
#里面也可以加常量值，效果一样
select count("fsadfj") from employees;

用于统计行数

效率:

• myisam存储引擎下，count(*)的效率高
• innodb存储引擎下，count(*)和count(1)的效率差不多

和分组函数一同查询有限制

select avg(salary),employee_id from employees;

5、分组查询

语法

select 分组函数，列（要求出现在group by 后面）
from 表
[where 筛选条件]
group by分组列表
[order by 子句]
[having 筛选条件]

注意：查询列表比较特殊，要求分组函数和group by后出现的字段

1、分组查询的筛选条件分为两类

	数据源	位置	关键字
分组前筛选	原始表	group by子句的前面	where
分组后筛选	分组后的结果集	group by子句的后面	having

分组函数做条件肯定是放在having子句中

能用分组前筛选的，就优先考虑分组前筛选

2、group by 子句支持单个字段、多个字段分组（多个字段之间用逗号隔开没有顺序要求）

3、添加排序（排序放在整个分组查询的最后）

查询不同工种的最高工资

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

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

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

会报错，where 不能这么使用，count(*)不属于employees中的字段

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

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

select
	max(salary),
	job_id
from
	employees
where
	commission_pct is not null
group by
	job_id
having
	max(salary)>12000;

commission_pct 字段属于employees

多个字段分组

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

6、多表连接查询

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

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

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

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

6.1、sql 92标准

~1.等值连接

select
	name,
	boyName
from
	boys,
	beauty
where
	beauty.boyfriend_id = boys.id

查询员工名、工种号、工种名

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

select
	last_name,
	e.job_id,
	job_title
from
	employees e,
	jobs j
where
	e.job_id = j.job_id 

查询每个城市的部门个数

select
	count(*) 个数 ,
	l.city
from
	departments d ,
	locations l
where
	d.location_id = l.location_id
group by
	l.city ;

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

select 
	job_title 工种 ,
	count(*) 个数
from
	employees e ,
	jobs j
where
	e.job_id = j.job_id
group by
	job_title 
order by 个数 desc ;

多表连接

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

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 ;

~2.非等值连接

查询员工的工资和工资级别

select
	last_name, 
	salary,
	grade_level
from
	employees e ,
	job_grades jg
where
	salary between jg.lowest_sal and jg.highest_sal ;

~3.自连接

查询员工和上级的名称

select
	e.employee_id ,
	e.last_name,
	e2.employee_id ,
	e2.last_name
from
	employees e,
	employees e2
where
	e.manager_id = e2.employee_id ;

6.2、sql 99标准

语法
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序条件】

~1.内连接

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

~1.1、等值连接

select
	last_name,
	department_name
from
	employees e
inner join departments d 
on
	e.department_id = d.department_id；

三表连接

查询员工名、部门名、工种名、并按部门名排序

注：连接条件放一起也可以

select
	last_name,
	department_name,
	job_title
from
	employees e
inner join departments d 
on
	e.department_id = d.department_id
inner join jobs j 
on
	j.job_id = e.job_id
order by
	department_name;

~1.2、非等值连接

select
	last_name,
	salary ,
	grade_level
from
	employees e
inner join job_grades jg 
on
	salary between jg.lowest_sal and jg.highest_sal

~1.3、自连接

select
	e.employee_id 员工id,
	e.last_name 员工,
	e1.employee_id 上级id,
	e1.last_name 上级
from
	employees e
inner join employees e1
on
	e.manager_id = e1.employee_id ;

~2.外连接

应用场景：用于查询一个表中有，另一个表没有的记录

​如果从表中有和它匹配的，则显示匹配的值

​如果从表中没有和它匹配的值，则显示null

​外连接查询结果=内连接结果+主表中有而从表没有的记录

~2.1、左外连接

select
	b.name,
	bo.*
from
	beauty b
left outer join boys bo 
on
	b.boyfriend_id = bo.id
where
	bo.id is null;

查询没有员工的部门

select
	d.department_name
from
	departments d
left join employees e 
on
	d.department_id = e.department_id
where e.employee_id is null;

~2.2、右外连接

select
	d.department_name
from
    employees e
right join departments d
on
	d.department_id = e.department_id
where e.employee_id is null;

~2.3、全外连接

mysql不支持

结果=内连接的结果+表1中有单表2中没有的+表2中有但表1中没有的

~3.交叉连接

迪卡尔乘积

7、子查询

出现在其它语句中的select语句中，称为子查询或内查询

外部的查询语句，称为主查询或外查询

分类

按子查询出现的位置：

• select 后面：仅仅支持标量子查询
• from 后面 ：支持表子查询
• where 或 having 后面：标量子查询、列子查询、行子查询
• exists 后面（相关子查询）：表子查询

按结果集的行列数不同：

• 标量子查询（结果集只有一行一列）
• 列子查询（结果集只有一列多行）
• 行子查询（结果有一行多列）
• 表子查询（结果集一般为多行多列）

7.1、where或having后面

• 1、标量子查询（单行子查询）
• 2、表子查询（多行子查询）
• 3、行子查询

特点：

• 1、子查询放在小括号内
• 2、子查询一般放在条件的右侧
• 3、标量子查询，一般搭配着单行操作符使用> < >= = <>

列子查询：一般搭配着多行操作符使用

in 、any/some、all

any:任意一个

all：全部

~ 1.标量查询

select
	*
from
	employees e
where
	salary >(
	select
		salary
	from
		employees e
	where
		last_name = "abel"
);

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

select
	last_name ,job_id,salary 
from
	employees e
where
	job_id =(
	select
		job_id
	from
		employees
	where
		employee_id = 141)
	and salary > (
	select
		salary
	from
		employees
	where
		employee_id = 143);

放在having后面

select
	min(salary) ,
	department_id
from
	employees
group by
	department_id
having
	min(salary)
>(
	select
		min(salary)
	from
		employees e
	where
		department_id = 50)

~2.列子查询

返回location_id是1400或1700的部门中的所有员工姓名

select
	last_name
from
	employees e
where
	department_id in(
	select
		distinct department_id
	from
		departments d
	where
		location_id in (1400, 1700));

返回其它部门中比job_id为’it_prog’部门任一工资低的员工的员工号、姓名、job_id 以及salary

select
	last_name,
	employee_id ,
	job_id ,
	salary
from
	employees
where
	salary <salary any(
	select
		distinct salary
	from
		employees
	where
		job_id = 'it_prog');

~3.行子查询

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

select
	*
from
	employees e
where
	employee_id =(
	select
		min(employee_id)
		from employees)
	and salary =(
	select
		max(salary)
	from
		employees);

或者

select
	*
from
	employees
where
	(employee_id,
	salary)=(
	select
		min(employee_id),
		max(salary)
		from employees);

7.2、select后面

仅支持标量子查询

查询每个部门的员工个数

select
	d.*,
	(
	select
		count(*)
	from
		employees e
	where
		e.department_id = d.department_id)
from
	departments d ;

查询员工号=100的部门名

select(select
	department_name
from
	departments d
inner join employees e 
on
	d.department_id = e.department_id
where
	e.employee_id = 100);

7.3、from后面

每个部门的平均工资的工资等级

select
	ag_dep.*,
	jg.grade_level
from
	(
	select
		avg(salary) ag ,
		department_id
	from
		employees e
	group by
		department_id) ag_dep
inner join job_grades jg 
on
	ag_dep.ag between lowest_sal and highest_sal ;

7.4、exists后面

语法

exists(完整的查询语句)

结果为1或0

select exists (select employee_id from employees);

有值为1

查询有员工的部门名

select
	department_name
from
	departments d
where
	exists (
	select
		*
	from
		employees e
	where
		d.department_id = e.department_id 
);

8、分页查询

1）应用场景

当要查询的条目数太多，一页显示不全，需要分页提交sql请求

2）语法

select 查询列表
from 表
limit 【offset】,size;

注意：
offset代表的是起始的条目索引，
默认为0 size代表的是显示的条目数

公式： 假如要显示的页数为page，
每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

select * from employees e limit 5;	

或

select * from employees e limit 0,5;

9、union联合查询

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

语法

查询语句1
union
查询语句2
union
…

应用场景：

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致

特点：

• 1.要求多条查询语句的查询列表是一致的
• 2.要求多条查询语句的查询的每一列的类型和循序最好一致

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

select * from employees e where email like '%a%' or department_id >90;
或者
select * from employees where email like '%a%' union select * from employees 
where department_id >90;

可以查询有相同字段的两张表

六、DML语言

数据操作语言：

插入：insert

修改：update

删除：delete

1、插入语句

方式一

语法

insert into 表名(列名,...)
values(值1,...);

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

insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
values(33,'唐艺昕','女','1990-4-23','18988888888',null,2);
或
insert into beauty values(33,'唐艺昕','女','1990-4-23','18988888888',null,2);

列和值可以调换顺序，但是列和值要对应上

方式二

insert into table_name
set 列名=值,...

insert into beauty set id=19,name='刘涛',phone ='999';

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

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

insert into beauty(id,name,sex,phone)
values(14,'jack','男','412341'),
(13,'唐艺昕','女','1990-4-23'),
(14,'jack','男','412341');

insert into beauty(id,name,phone)
select 26,'宋茜','141234'

2、修改语句

truncate talbe 表名（清空所有内容）

修改单表

update 表名
set 列=新值,列=新值,...
where 筛选条件

update beauty 
set name = 'jack1'
where name = 'jack';

修改多表的记录

语法

sql92

update 表1 别名，表2 别名
set 列=值
where 连接条件 and 筛选条件

sql99

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

update boys bo
inner join beauty b on bo.id =b.boyfriend_id 
set b.phone ='114'
where bo.boyName ='张无忌';

3、删除语句

方式一 单表的删除

delete from 表名 where 筛选条件

delete from beauty where phone like '%9'

方式二 多表的删除

sql92

delete 别名from 表1 别名,表2 别名where 连接条件and 筛选条件；

sql99

语法

delete 表1的别名，表2的别名
from 表1 别名
inner|left|right join 表2 别名 
on 连接条件
where 筛选条件;

delete b
from beauty b 
inner join boys bo on b.boyfriend_id =bo.id 
where bo.boyName ="张无忌";

delete b,bo
from beauty b
 inner join boys bo on b.boyfriend_id =bo.id where bo.boyName ="黄晓明";

七、DDL语言

1、库的管理

1.1、创建

create database 库名;

增加容错性

create database if not exists books;

1.2、修改

• 1）修改库名

rename database books to 新库名

不稳定，不建议修改库名，新版本无法使用

• 2）更改字符集

alter database books character set gbk;

1.3、删除

drop database books;

drop database if exists books;

2、表的管理

2.1、创建表

语法

create table 表名(
列名 列的类型【（长度）约束】，
列名 列的类型【（长度）约束】，  
 ...)

create table book(id int,#编号bName varchar(20),#图书馆price double,#价格authorId int,#作者编号publishDate datetime#出版日期)

create table author(id int,au_name varchar(20),nation varchar(10))desc author;

2.2、修改

语法

alter  table 表名 add|drop|modify|change column 【列名】【约束】

1）修改列名

alter table book change column publishdate pubDate datetime;

2）修改列的类型或约束

alter table book modify column pubdate timestamp;

3）添加新列

alter table author add column annual double;

4）删除列

alter table author drop column annual;

5）修改表名

alter table author rename to book_author

2.3、删除

drop if exists author;

2.4、表的复制

insert into author values(1,'村上春树','日本'),(2,'莫言','中国'),(3,'冯唐','中国'),(4,'金庸','中国');

①复制表的结构

create table copy like author;

②复制表的结构+数据

create table copy2 select * from author;

③复制结构和部分数据

create table copy3 select * from author where nation ="中国";

④复制部分结构

create table copy4 select id,au_name from author where 0;

3、常见数据类型介绍

3.1、整型

分类	tinyint	smallint	mediumint	int/integer	bright
字节	1	2	3	4	8

默认为有符号

create table tab_int(t1 int,#有符号
t2 int unsigned);#无符号

特点：

• ①如果不设置无符号或者有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
• ②如果插入的数值超出了整型的范围，会报 out of rang异常,报错
• ③如果不设置长度，会有默认的长度

长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

使用zerofill会自动变成无符号型

create table tab_int1 (t1 int(7) zerofill,t2 int(7));

insert into tab_int1 values(123,123);

3.2、小数

3.3、浮点型

float(M,D)

double(M,D)

3.4、定点型

dec(M,D)

decimal(M,D)

M代表数字长度，D代表小数位数

create table tab_float(f1 float(5,2),f2 double(5,2),f3 decimal(5,2));

insert into tab_float values (123.45,123.45,123.45);insert into tab_float values (123.45,123.45,123.456);insert into tab_float values (1234.45,1234.45,1234.45);#超出范围，报错

省略（m,d）

create table tab_float1(f1 float,f2 double,f3 decimal);

类型的选择

所选择的类型越简单越好，能保存数值的情况下类型越小越好（占用空间小）

3.5、字符型

	写法	M的意思	特点
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	空间的耗费比较大	效率高
varchar	varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较省空间	效率低

3.6、enum

不区分大小写

create table tab_char(c1 enum('a','b','c'))

insert into tab_char values ('a');insert into tab_char values ('b');insert into tab_char values ('c');insert into tab_char values ('m');#插入失败insert into tab_char values ('A');

3.7、set

不区分大小写

create table tab_set(s1 set('a','b','c','d'));

insert into tab_set values ('a');insert into tab_set values ('a,b');insert into tab_set values ('a,c,d');insert into tab_set values ('a,d,d');

3.8、日期型

date 只保存日期

time 只保存时间

year 只保存年

	字节	范围	时区等的影响
datetime	8	1000-9999	不受
timestamp	4	1970-2038	受

create table tab_date(t1 datetime,t2 timestamp);insert into tab_date values(now(),now());select * from tab_date;

改变时区

set time_zone='+9:00'

4、常见约束

4.1、分类：六大约束

create table 表名(字段名 字段类型 约束)

not null	非空，用于保证该字段的值不能为空，比如姓名、学号等
default	默认，用于保证该字段有默认值。比如性别
primary key	主键，用于保证该字段的值具有唯一性，并且非空。比如学号
unique	唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
check	检查约束（mysql中不支持）
foreign key reference	外键，用于限制两个表的关系，用于保证字段的值必须来自于主表的关联列的值。在从表中添加外键的约束，用于引用主表中某列的值，学生表的专业编号，员工表的部门标号，员工表的工种编号

主键和唯一的区别

	保证唯一性	是否允许为空	一个表可以有多少个	是否允许组合
主键	√	×	至多有一个	√，不推荐
唯一	√	√	可以有多个	√，不推荐

外键

• 1、要求在从表设置外键关系
• 2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
• 3、主表的关联列必须是一个key（一般是主键或唯一）

添加约束的时机：

• 1、创建表时
• 2、修改表时

约束的添加分类

列级约束：

​ 六大约束语法上都支持，但外键约束没有效果

表级约束：

​ 除了非空，其它都支持

create table 表名(字段名 字段类型 列级约束,字段名 字段类型 ,表级约束)

4.2、创建表时添加约束

1）添加列级约束

检查和外键都没效果

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));

2）添加表级约束

create table stuinfo1(id int,stuname varchar(20),gender char(1),seat int,age int,majorid int,constraint pk primary key(id),#主键constraint ug unique(seat),constraint ck check(gender='男' or gender='女'),constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键)

constraint pk可以省略pk是取的名字

通用的写法

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,    constraint fk_stuinfo_major foreign key(majorid) references major(id)#外键);

4.3、修改表时添加约束类型

drop table if exists stuinfo;create table stuinfo (id int,stuname varchar(20),gender char(1),seat int,age int,majorid int);

1）添加非空约束

alter table stuinfo modify column stuname varchar(20) not null;

2）添加默认约束

alter table stuinfo modify column age int default 18;

3）添加主键

#列级约束alter table stuinfo modify column id int primary key;#表级约束alter table stinfo add primary key(id);

4）添加唯一

alter table stuinfo modify column seat int unique;

4.4、删除约束

1）删除非空约束

alter table stuinfo modify column stuname varchar(20) null;

2）删除默认约束

alter table stuinfo modify column age int;

3）删除主键

#表级alter table stuinfo drop primary key;

4）删除唯一

alter table stuinfo drop index seat;

5）删除外键

alter table stuinfo drop foreign key majorid;

5、标识列

又称为自增长列

含义：可以不用手动的插入值，系统提供默认的序列值

create table tab_identity(id int primary key auto_increment,name varchar(20));

多次执行

insert into tab_identity values(null,'join');

set auto_increment_increment=3;

更改步长

特点

• 表示列必须和key搭配
• 一个表至多有一个标识列
• 标识列的类型只能是数值型

修改表时设置标识列

alter table tab_identity modify column id int primary key auto_increment;

修改表时修改标识列

alter table tab_identity modify column id int;

八、TCL语言

事务控制语言

事务：

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

1、事务的ACID(acid)属性

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

2、事物的分类：

• 1、 隐式事务：事物没有明显的开启和结束的标记
  比如insert、update、delete语句

• 2、显式事务：事务具有明显的开启和结束的标志

  ①、开启事务
  取消自动提交事务的功能
  ②、编写事务的一组逻辑操作单元（多条sql语句）
  insert
  update
  delete

  ③、提交事务或回滚事务

3、使用到的关键字

set autocommit=0;
start transaction;
commit;
rollback;

savepoint  断点
commit to 断点
rollback to 断点

4、事物的隔离级别

事务并发问题如何发生？

当多个事务同时操作同一个数据库的相同数据时

事务的并发问题有哪些？

脏读：一个事务读取到了另外一个事务未提交的数据
不可重复读：同一个事务中，多次读取到的数据不一致
幻读：一个事务读取数据时，另外一个事务进行更新，导致第一个事务读取到了没有更新的数据

如何避免事务的并发问题？

通过设置事务的隔离级别
1、READ UNCOMMITTED
2、READ COMMITTED 可以避免脏读
3、REPEATABLE READ 可以避免脏读、不可重复读和一部分幻读
4、SERIALIZABLE可以避免脏读、不可重复读和幻读

设置隔离级别：

set session|global  transaction isolation level 隔离级别名;

查看隔离级别：

select @@tx_isolation;

#开启事务set autocommit=0;
#编写一组事务的语句
update account set balance=1000 where username='a';
update account set balance=1500 where username='b';
#结束事务
commit;

回滚

#开启事务SET autocommit=0;#编写一组事务的语句UPDATE account SET balance=800 WHERE username='a';UPDATE account SET balance=800 WHERE username='b';#结束事务ROLLBACK

不执行，数据不变

delete和truncate在事务中的区别

set autocommit=0;start transaction;delete from account;rollback;

set autocommit=0;start transaction;truncate table account;rollback;

delete支持回滚

truncate 不支持回滚，数据会被删除

九、视图

含义：虚拟表，和普通表一样使用

视图的好处

• 1、sql语句提高重用性，效率高
• 2、和表实现了分离，提高了安全性

1、视图的创建

create view 视图名
as
查询语句

#创建create view myv1asselect last_name,department_name,job_titlefrom employees e join departments d on e.department_id =d.department_id join jobs j on j.job_id=e.job_id ;#使用select * from myv1 where last_name like '%a%';

2、视图的修改

方式一：

没有就创建，有就替代

create or replace view 视图名as查询语句;

create or replace view myv1asselect last_name,department_name,job_titlefrom employees e join departments d on e.department_id =d.department_id join jobs j on j.job_id=e.job_id ;

方式二

alter view 视图名as查询语句;

3、删除视图

drop view 视图名，视图名...;

4、查看视图

desc myv1;

show create view myv1;

5、视图的更新

会更改原表

insert into myv1 values(...);

update myv1 set last_name='' where

十、变量

1、系统变量

说明： 变量由系统提供，不是用户定义，属于服务器层面

使用语法：

1、查看所有的系统变量

show global variables;#全局变量show session variables;#会话变量 session可以省略

2、查看满足条件的部分系统变量

show global|session variables like '%char%'

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

select @@global|session.系统变量名;

4、为某个系统变量赋值

方式一

set global | session 系统变量名=值; 

方式二

set @@global|session.系统变量名=值;

注：
如果是全局级别，则需要加globa，如果是会话级别，则需要加session，如果不写，则默认session

作用域：
全局变量：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启

会话变量：只对当前会话和连接有效

2、自定义变量

变量是用户自定义的，不是由系统提供的

2.1、用户变量

作用域：针对当前会话和连接有效，同于会话变量的作用域

赋值的操作符为： =或:=

1）声明并初始化

set @用户变量名=值;set @用户变量名:=值;select @用户变量名:=值;

2）赋值(更新用户变量的值)

方式一：通过set或select

set @用户变量名=值;
set @用户变量名:=值;
select @用户变量名:=值;

方式二：通过select into

select 字段 into 变量名 from 表

3）使用

select @用户变量名

2.2、局部变量

作用域：仅仅在定义它的begin end 中有效

应用在begin end中的第一句话

1）声明

declare 变量名 类型;
declare 变量名 类型 defalut 值;

2）赋值(更新用户变量的值)

方式一：通过set或select

set 局部变量名=值;
set 局部变量名:=值;
select @局部变量名:=值;

方式二：通过select into

select 字段 into 局部变量名 from 表

3）使用

select 局部变量名

十一、存储过程和函数

1、存储过程

含义：一组预先编译好的sql语句的集合，理解成批处理语句

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

1.1、创建过程

create procedure 存储姓名(参数列表)begin方法体(一组合法的sql语句)end

注意：

• 1、参数列表包含三部分
  参数模式 参数名 参数类型

举例：

in stuname varchar(20)

参数模式：

in:该参数可以作为输入，也就是该参数需要调用方传入值out:该参数可以作为输出，也就是该参数可以作为返回值inout:该参数即可以作为输出，也就是该参数既需要传入值，又可以返回值

• 2、如果存储过程仅仅只有一句话，begin end可以省略

   存储过程中的每条语句的结尾必须加分号。
   存储过程的结尾可以使用delimiter重新设置
  

语法：delimiter 结束标记
例如 delimiter $

1.2、调用

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

1、空参列表

插入到admin表中五条记录

delimiter $create procedure myp1()begin 	insert into admin(username,`password`)	values('john1','0000'),('lily','00000'),('rose','00000'),('jack','00000'),('tom','00000');end $

要在cmd中执行

调用

call myp1()$

2、创建带in模式参数的存储过程

create procedure myp2(in beautyname varchar(20) )begin 	select * 	from boys bo	right join beauty b on bo.id=b.boyfriend_id 	where b.name=beautyname;end $

调用

call myp2('王语嫣') $

创建存储过程实现判断用户是否登入成功

create procedure myp3(in username varchar(20),in PASSWORD varchar(20))begin	declare result int default 0;#声明并初始化		select count(*)into result#赋值	from admin a	where a.username=username	and a.password=PASSWORD;    select if(result>0,'success','fail'); end $

调用

call myp3('张飞','8888')$

3、创建带out模式的存储过程

create procedure myp4(in beautyName varchar(20),out boyName varchar(20))begin	select bo.boyname into boyname	from boys bo	inner join beauty b on bo.id=b.boyfriend_id 	where b.name=beautyName;end $

调用

call myp4('王语嫣',@bName)$ select @bName $

4、创建带inout模式参数的存储过程

传入a和b两个值，最终a和b都翻倍并返回

create procedure myp5(inout a int,inout b int)begin 		set a=a*2;    set b=b*2;end $

set @a=1 $set @b=2 $call myp5(@a,@b) $select @a,@b $

1.2、删除存储过程

drop procedure pl;

1.3、查看存储过程的信息

show create procedure myp1;

2、函数

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

函数：有且仅有一个返回，适合做处理数据后返回一个结果

2.1、创建语法

create function 函数名(参数列表) returns 返回类型begin函数体end

注意

函数体：肯定会有return语句，如果没有会报错

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

return 值；

函数体中仅有一句话，则可以省略begin end

使用 delimiter 语句设置结束标记

delimiter s;

create function myf1() returns intbegin	declare c int default 0;select	count(*)into	cfrom	employees;return c;end $

如下设置

SET GLOBAL log_bin_trust_function_creators = 1;

调用

select myf1()$

2.2、查看函数

show create function myf1;

2.3、删除函数

drop function myf3;

十二、流程控制结构

顺序结构：程序从上往下依次执行

分支结构：程序从两条或多条路径中选择一条去执行

循环结构：程序在满足一定条件的基础上，重复执行一段代码

1、分支结构

1、if函数

功能：实现简单的分支

语法：

select if(表达式1，表达式2，表达式3)

执行顺序

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

应用：任何地方

2、case

参考流程控制函数

3、if结构

功能：实现多重分支

语法

if 条件1 then 语句1;elseif 条件2 then 语句2;...end if;

应用在begin end中

delimiter $create function test_if(score int)returns charbegin	if score>=90 and score<=100 then return 'A';elseif score>80 then return 'B';elseif score>60 then return 'C';else return 'D';end if;end $

select test_if(80);

2、循环结构

分类：

while、loop、repeat

循环控制：

iterate类似于continue 继续

leave类似于 break 跳出

1、while

语法：

while 循环条件 do循环体;end while 【标签】;

2、loop

语法

loop循环体end loop 【标签】;

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

3、repeat

语法：

【标签:】repeat循环体;
until 结束循环的条件;
end repeat 【标签】;

批量插入，根据次数插入到admin表中多条记录

delimiter $ 
create procedure pro_while1(in insertCount int)
begin	
declare i int default 1;    while i<= insertCount Do    insert into admin(username,`password`)values (concat('Rose',i),'666');    set i=i+1;    if i>=20 then leave a;    
end while a;
end $

call pro_while1(10)