2021-09-05

2024-03-29 12:58:52

MySQl基础

mysql启动命令
mysql登入命令
mysql常用命令
mysql的语法规范
DQL(Data Query Language)
数据类型
- 整型
- 小数
- - 浮点型
  - 定点型
- 字符型
- enum
- set
- 日期型
常见的约束
标识列
TCL(Transaction Control Language)
视图
变量
- 一、系统变量
- 二、自定义变量
存储过程和函数
流程控制结构
- 一、分支结构
- 二、循环结构

mysql启动命令

注：要以管理员身份打开cmd，否则命令无效

net stop/start 服务名

mysql登入命令

-h 接主机名

-P（大写与密码区分）接端口号

-u 接用户

-p 接密码（p与密码之间不能用空格）

mysql -uroot -p1234或mysql -uroot -p

mysql常用命令

1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(

列名列类型,
列名列类型，
…

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

7.查看当前所在的库
select database();

8.查询表里面的数据

select * from 表名

9.查看mysql版本

select version();

如果未登入mysql客户端

mysql --version

mysql -V

mysql的语法规范

1.不区分大小写，但建议关键字大小写，表名、列名小写

2.每条命令最好用分号结尾

3.每条命令根据需要，可以进行缩进或换行

4.注释

单行注释：#注释文字

单行注释：-- 注释文字

多行注释：/* 注释文字 */

DQL(Data Query Language)

基础查询

语法：
select 查询列表 from 表名
特点：
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格

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

条件查询

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;

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

不支持通配符

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值，又可以判断普通的数值，可读性差

排序查询

语法
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;

常见函数

功能：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) [from 表];
特点：函数名、函数功能
分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数
功能：做统计使用

单行函数

一、字符函数

length 字节长度

select length(‘john’);

select length('是否哈里斯发货')

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

concat 拼凑字符串

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

upper、lower 大小写

select upper('jhon'); 
select lower('hoHn');

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

substr、substring
注意：索引从1开始

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

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

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

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

案例，姓名中首字符大写，其它字符小写然后用_拼接，显示出来

select concat(upper(substr(last_name,1,1)) ,'_',substr(last_name,2)) out_put from employees;

instr

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

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

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 ;

lpad

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

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

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

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

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

replace

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

二、数学函数

round 四舍五入

select round(1.55);

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

select round(-1.55);

保留指定位小数

select round(1.567,2);

ceil

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

select ceil (1.02);

select ceil (-1.02);

floor

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

select floor(-9.99);

truncate 截断

select truncate(1.65,1);

#mod 取余
mod(a,b)=a-(a/b)*b

select mod(10,-3);

select 10%3;

三、日期函数

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

select now();

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

select curdate();

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

select curtime();

**datediff **获取间隔时间

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

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

select year (now()) 年;
select year (2021-08-30) 年;

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

类推

str_to_date

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

select str_to_date('2021-08-30 17:16:23','%Y-%c-%d') as out_put;

select str_to_date('4-2 2021','%d-%m %Y') as out_put;

date_format

将日期转换成字符

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

四、其它函数

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

五、流程控制函数

if 函数

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

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

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;

多行函数(分组函数)

功能:用于统计使用，称为聚合函数或统计函数
分类: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;

employee_id的查询结果没有任何的意义

分组查询

语法

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 ;

连接查询

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

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

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

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

分类：
按年代分类：
sql92标准：仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

按功能分类:
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接

一、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 ;

二、sql 99

语法

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

内连接：inner

外连接：
左外：left 【outer】
右外：right 【outer】
全外：full 【outer】
交叉连接：cross

内连接

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

等值连接

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;

非等值连接

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

自连接

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 ;

外连接

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

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

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

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

左外连接

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;

右外连接

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

全外连接（mysql不支持）

mysql不支持

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

交叉连接

迪卡尔乘积

子查询

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

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

分类：

按子查询出现的位置：

select 后面：仅仅支持标量子查询

from 后面：支持表子查询

where 或 having 后面：标量子查询、列子查询、行子查询

exists 后面（相关子查询）：表子查询

按结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

行子查询（结果有一行多列）

表子查询（结果集一般为多行多列）

where或having后面

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

2、表子查询（多行子查询）

3、行子查询

特点：

1、子查询放在小括号内

2、子查询一般放在条件的右侧

3、标量子查询，一般搭配着单行操作符使用> < >= = <>

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

in 、any/some、all

any:任意一个

all：全部

标量查询

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)

列子查询

返回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');

行子查询

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

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

select后面

仅支持标量子查询

查询每个部门的员工个数

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

查询员工号=102的部门名

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

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 ;

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

分业查询

一、应用场景
当要查询的条目数太多，一页显示不全，需要分页提交sql请求
二、语法

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;

联合查询

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（Data Manipulation Language）

数据操作语言：

插入：insert

修改：update

删除：delete

插入

方式一

语法

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

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

insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
values(13,'唐艺昕','女','1990-4-23','18988888888',null,2);
或
insert into beauty values(13,'唐艺昕','女','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'

修改

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 ='张无忌';

删除

方式一单表的删除

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 bfrom beauty b inner join boys bo on b.boyfriend_id =bo.id where bo.boyName ="张无忌";

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

DDL

库的管理

创建、修改、删除

1、创建

create database 库名;

增加容错性

create database if not exists books;

2、修改

①修改库名

rename database books to 新库名

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

②更改字符集

alter database books character set gbk;

3、删除

drop database books;

drop database if exists books;

表的管理

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、修改

语法

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

①修改列名

alter table book change column publishdate pubDate datetime;

②修改列的类型或约束

alter table book modify column pubdate timestamp;

③添加新列

alter table author add column annual double;

④删除列

alter table author drop column annual;

⑤修改表名

alter table author rename to book_author

3、删除

drop if exists author;

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;

数据类型

数据类型
数值型：
整型
小数：
定点数
浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob(较长的二进制数据)
日期型：

整型

分类：

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

小数

浮点型

float(M,D)

double(M,D)

定点型

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

类型的选择

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

字符型

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

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

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

日期型

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'

常见的约束

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

分类：六大约束

not

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

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

主键和唯一的区别

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

外键

1、要求在从表设置外键关系

2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

3、主表的关联列必须是一个key（一般是主键或唯一）

添加约束的时机：

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

②添加表级约束

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)#外键);

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

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

①添加非空约束

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;#表级约束alter table stinfo add primary key(id);

④添加唯一

alter table stuinfo modify column seat int unique;

三、删除约束

①删除非空约束

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

⑤删除外键

alter table stuinfo drop foreign key majorid;

标识列

又称为自增长列

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

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(Transaction Control Language)

事务控制语言

事务：

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

事务的ACID(acid)属性

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

事物的创建

隐式事务：事物没有明显的开启和结束的标记

比如insert、update、delete语句

delete from 表 where id=1;

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

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

set autocommit=0;

步骤1：开启事物

set autocommit=0;

start transaction;可选的

步骤2：编写事务中的sql语句（select insert update delete）

语句1；

语句2；

…

步骤三：结束事务

commit；提交事务

rollback；回滚事务

#开启事务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 不支持回滚，数据会被删除

视图

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

msyql5.1版本出现的新特性，是通过表动态生成的数据

只保存了sql逻辑，不保存sql语句

特点：

重用sql语句

简化复杂的sql操作，不必知道它的查询细节

保护数据，提高安全性

一、视图的创建

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%';

二、视图的修改

方式一：

没有就创建，有就替代

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查询语句;

三、删除视图

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

四、查看视图

desc myv1;

show create view myv1;

五、视图的更新

会更改原表

insert into myv1 values(...);

update myv1 set last_name='' where

变量

系统变量：

全局变量

会话变量

自定义变量：

用户变量

局部变量

一、系统变量

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

使用语法：

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

作用域：

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

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

二、自定义变量

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

使用步骤

声明

赋值

使用（查看、比较、运算）

1、用户变量

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

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

①声明并初始化

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

②赋值(更新用户变量的值)

方式一：通过set或select

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

方式二：通过select into

select 字段 into 变量名 from 表

③使用

select @用户变量名

2、局部变量

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

应用在begin end中的第一句话

①声明

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

②赋值(更新用户变量的值)

方式一：通过set或select

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

方式二：通过select into

select 字段 into 局部变量名 from 表

③使用

select 局部变量名

存储过程和函数

存储过程

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

1、提高代码的重用性

2、简化操作

3、减少了编译次数并且减少了数据库服务器的连接次数，提高了效率

一、创建过程

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

注意：

1、参数列表包含三部分

参数模式参数名参数类型

举例：

in stuname varchar(20)

参数模式：

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

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

存储过程中的每条语句的结尾必须加分号。

存储过程的结尾可以使用delimiter重新设置

语法:

delimiter 结束标记

例如

delimiter $

二、调用

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 $

二、删除存储过程

drop procedure pl;

三、查看存储过程的信息

show create procedure myp1;

函数

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

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

一、创建语法

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 $

如果无法创建参考文章https://blog.csdn.net/GT_Stone/article/details/84499118

这里我们如下设置

SET GLOBAL log_bin_trust_function_creators = 1;

调用

select myf1()$

二、查看函数

show create function myf1;

三、删除函数

drop function myf3;

流程控制结构

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

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

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

一、分支结构

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

二、循环结构

分类：

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)

码农公寓

MySQl基础

mysql启动命令

mysql登入命令

mysql常用命令

mysql的语法规范

DQL(Data Query Language)

基础查询

条件查询

排序查询

常见函数

单行函数

一、字符函数

二、数学函数

三、日期函数

四、其它函数

五、流程控制函数

多行函数(分组函数)

分组查询

连接查询

一、sql 92标准

1、等值连接

2、非等值连接

3、自连接

二、sql 99

内连接

等值连接

非等值连接

自连接

外连接

左外连接

右外连接

全外连接（mysql不支持）

交叉连接

子查询

where或having后面

标量查询

列子查询

行子查询

select后面

from后面

exists后面

分业查询

联合查询

DML（Data Manipulation Language）

插入

修改

删除

DDL

库的管理

1、创建

2、修改

3、删除

表的管理

1、创建表

2、修改

3、删除

4、表的复制

数据类型

整型

小数

浮点型

定点型

字符型

enum

set

日期型

常见的约束

分类：六大约束

一、创建表时添加约束

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

三、删除约束

标识列

TCL(Transaction Control Language)

视图

变量

一、系统变量

二、自定义变量

存储过程和函数

存储过程