MySql

一、SQL概念

DB
数据库(dlatabase) :存储数据的“仓库”。它保存了一系列有组织的数据。
DBMS
数据库管理系统(Database Management System)。数据库是通过DBMS创
建和操作的容器
SQL
结构化查询语言(Structure Query Language) :专门用来与数据库通信的语
言。

MySql特点：
成本低:开放源代码，一般可以免费试用
性能高:执行很快
简单:很容易安装和使用

DBMS分为两类:
基于共享文件系统的DBMS( Access )
基于客户机——服务器的DBMS（MySQL、Oracle、 SqlServer )

1、DML (Data Manipulation Language):数据操纵语句，用于添
加、删除、修改、查询数据库记录，并检查数据完整性
2、DDL (Data Definition Language):数据定义语句，用于库和
表的创建、修改、删除。
3、DCL (Data Control Language):数据控制语句，用于定义用
户的访问权限和安全级别。

DML
DML用于查询与修改数据记录，包括如下SQL语句:
●INSERT:添加数据到数据库中
●UPDATE:修改数据库中的数据
●DELETE:删除数据库中的数据
●SELECT:选择(查询)数据
➢SELECT是SQL语言的基础，最为重要。

DDL
DDL用于定义数据库的结构，比如创建、修改或删除
数据库对象，包括如下SQL语句:
●CREATE TABLE:创建数据库表
●ALTER TABLE: 更改表结构、添加、删除、修改列长度
●DROP TABLE:删除表
●CREATE INDEX:在表上建立索引
●DROP INDEX:删除索引

DCL
DCL用来控制数据库的访问，包括如下SQL语句:
●GRANT:授予访问权限
C)REVOKE:撤销访问权限
●COMMIT:提交事务处理
●ROLLBACK:事务处理回退
●SAVEPOINT:设置保存点
●LOCK:对数据库的特定部分进行锁定

二、MySql安装与使用

安装

看其他人CSDN

启动和停止MySql服务

方式一:通过计算机管理方式
右击计算机一 管理一服务- -启动或停止MySQL服务
方式二:通过命令行方式.
启动:net start mysq|服务名
停止:net stop mysq|服务名

登录

mysql -h 主机名 -u用户名 -p密码

退出

exit

MySql数据库的使用

不区分大小写
每句话用;或\g结尾
各子句一般分行写
关键字不能缩写也不能分行
用缩进提高语句的可读性

进入mysql,在命令行中输入: mysql -uroot -p## (其中: ##表示密码)
查看mysql中有哪些个数据库: show databases;
使用一.个数据库: use数据库名称;
新建一个数据库: create database数据库名
查看指定的数据库中有哪些数据表:showtables;
建表：
create table customer(
列明 列类型,
列明 列类型
)；
查看表的结构：desc 表名；
删除表：drop table 表名；
查看表中的所有记录: select * from表名;
向表中插入记录: insert into表名(列名列表) values(列对应的值的列表);
查看服务器版本：
方式一：登录到mysql服务端
select version();
方式二:没有登录到mysq1服务端
方式二:没有登录到mysql 1服务端
mysq1 --version .
或
mysql --V

MySql语法规范

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

三、DML语言

1.基础查询

1.特别注意

``表示字段			‘’ 表示常量

2.查询表中所有字段 *

select * from student;

3.查询常量、表达式、函数

#查询常量值
select 100;
#查询表达式
select 100*98;
#查询函数
select version();

4.起别名 关键字：AS

select 100*98 as 结果；
SELECT last_name AS 姓，first_ name AS 名 FROM employees ;

as可省略

SELECT last_name 姓，first_ name 名 FROM employees ;

5.去重 关键字 DISTINCT distinct

select distinct id from student;

6.“+”号作用 仅仅只有一个功能：运算符

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

7.concat 作用：拼接字段。

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

例：显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_ PUT

SELECT
CONCAT(first_name,’,’,last_name,’,’,age,IFNULL (commission_ pct,0)) AS OUT_ PUT
FROM
employees;

2.条件查询

1.语法：

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

2.分类：

​ （1）按条件表达式筛选
​ 条件运算符： > < = != <>(不等于) >= <=
​ （2）按逻辑表达式筛选
​ 逻辑运算符： && || ！
​ and or not
​ （3）模糊查询：
​ like
​ between … and…
​ in
​ is null

​ like
​ % ：代表任意多个字符(包含0个和1个)
​ _ ：代表任意一个字符
​ 通配符不能用于null（意思就是说，在使用通配符搜索时，搜索不到null值，返回的结果中这个属性就没有null值）

​ between…and…
​ 区间在 [ x , y ] 闭区间。

​ <=> ：安全等于。

select * from student where id <=> null;

​ 等于

select * from student where id is null;

3.排序查询

order by 排序列表 asc | desc

desc ：降序
asc ：升序（默认）

使用不需要where
支持多个字段、表达式、函数、别名
order by 一般房子查询语句最后面，limit字句除外

4.常见函数

1.单行函数

（1）字符函数

length

select length('john');

concat 拼接字符串

selectconcat(first_name,last_name) from student;

upper、lower

select upper('john');

substr、substring ：截取字符串

注:索引从1开始

SELECT SUBSTR('李莫愁爱上了陆展元，7)
out_ put;

结果：陆展元

SELECT SUBSTR('李莫愁爱上了陆展元',1,3)
out_ put; 

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

结果：李莫愁

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

SELECT INSTR('杨不殷六侠悔爱.上了殷六侠', '殷六侠') AS out_ put;

结果：3

trim ：去除前后

去除前后空格

SELECT LENGTH (TRIM('   张 翠山   ' ) )ASout_ put;

结果：张 翠山

去除前后a

SELECT TRIM('a' FROM ' aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaa') AS out_ put;

结果：张aaaaaaaaaaaa翠山

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

select lpad('田甜向上',2,'*');

结果：**田甜向上

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

select rpad('田甜向上',2,'*');

结果：田甜向上**

replace 替换所以

SELECT REPLACE ('周正若周正右周正若周正若张无总爱上了周正右'，'周正右'，'赵敏') AS out_ put;

结果：赵敏赵敏赵敏赵敏张无忌爱上了赵敏

（2）数学函数

round 四舍五入

select round(2.5);select round(-1.2);

结果：3
-1

ceil 向上取整 返回>=该参数的最小整数

select ceil(-1.02);

结果：-1

floor 向下取整，返回<=该参数的最大整数

select floor(9.9);

结果：9

truncate 截断，保留小数位数。

select truncate(1.654,1);

结果：1.6

mod 取余

select mod(10,3);

结果：1

（3）日期函数

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

select now();

结果：2017-09-29 11:12:38

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

select curdate();

结果：2017-09-29

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

select curtime();

结果：11:13:35

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

year month day …

select year(now()) as 年;

结果：2021

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GSZRMjRr-1627366708834)(C:\Users\刘星\AppData\Roaming\Typora\typora-user-images\image-20210708143512459.png)]

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

select STR_ TO_ DATE('9-13-1999','%m- %d-%Y');

结果：1999-09-13

date_ format 将日期转换成字符

select DATE_ FORMAT('2018/6/6','%Y年%m月%d日');

结果：2018年06月06日

（4）其他函数

select version();		//当前版本select database();		//当前数据库select user();			//当前用户

（5）流程控制函数

if

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

结果：小

case

case函数的使用一: switch case 的效果

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

select salary 原始工资  , department_id,case department_idwhen 30 then salary*1.1when 40 then salary*1.2when 50 then salary*1.3else salaryend as 新工资from employees;

case函数的使用二：类似于多重if

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

select salary,casewhen salary >20000 then 'A'when salary >15000 then 'B'when salary >10000 then 'C'else 'D'end  as  薪资级别from employees;

2.分组函数

功能:用作统计使用，又称为聚合函数或统计函数或组函数

1.分类

​ sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

2.支持类型

sum、avg ：数值类型
max、min、count ：数值与字符

是否忽略null
sum、avg、min、max、count忽略null值。所有分组函数都忽略null值。

可以和distinct搭配

SELECT SUM (DISTINCT salary) , SUM(salary) FROM employees;

count(*)与count(salary)可能不相等
count(1)也是统计行数等于count(*)

一般用count(*)统计行数
效率:
MYISAM存储引擎下，COUNT()的效率高
INNODB存储引擎下，COUNT(*)和COUNT (1)的效率差不多，比COUNT (字段)要高一些

3.分组查询

group by

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

having ：用在group by 之后，作用与where相同。

select count(*),department_idfrom employeesgroup by department_idhaving count(*)>2;

4.分组查询中的筛选条件分为两类

分组前筛选：原始表 group by 的前面 where
分组后筛选：分组后的结果集 group by 的后面 having

5.连接查询

1.笛卡尔积

结果数 = m × n

2.连接查询分类

内连接：
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接

3.等值连接

SELECT NAME, boyNameFROM boys, beautyWHERE beauty . boyfriend_ _id= boys. id;

4.非等值连接

范围

5.自连接

把一张表当成多张表用，自己连接自己

select a.employee_id,a.namefrom employees a,employees bwhere a.manager_id=b.employee_id;

6. sql99语法

语法：
select 查询列表
from 表1 别名
[连接类型] join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
分类：
内连接：inner
外连接：
左外：left [outer]
右外：right [outer]
全外：full [outer]
交叉连接：cross

7.内连接

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

inner 可省略

例：查询员工名、部门名(等值连接)

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

例：查询员工的工资级别（非等值连接）

SELECT salary,grade_ levelFROM employees eJOIN job_ _grades gON e.salary BETWEEN g.lowest sal AND g.highest sal;

查询雇佣id和员工名字（自连接）

select a.employee_id,a.namefrom employees ainner join employees bon a.manager_id=b.employee_id;

8.外连接

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

特点：
1.外连接的查询结果为主表中的所有记录。如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null
2.左外连接，left [outer] join 左边的是主表
右外连接，right [outer] join 右边的是主表
全外连接，full [outer] join (mysql不支持)

9.交叉连接

语法：
select 查询列表
from 表1
cross jion 表2

6.子查询

1.概念

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

内部嵌套其他select语句的查询，称为外查询或主查询

如：

select first_name from employees where department_id in(select department_idfrom departmentswhere location_id=1700)

2.分类

1.按出现位置：

select后面：
标量子查询

from后面：
表子查询

where 或者having后面
标量子查询 √
列子查询 √
行子查询

exists后面：
标量子查询
列子查询
行子查询
表子查询

2.按结果集的行列

标量子查询（单行子查询）：结果集为一行一列
列子查询（多行子查询）：结果集为多行一列
行子查询：结果集为一行多列
表子查询：结果集为多行多列

3.where或者having后面

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

特点:
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用

> < >= <= = <>

例1：谁的工资比Abel高?

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

例2：返回job_ id与141号 员工相同，salary比143号员工多的员工姓名，job_ _id和工资

SELECT last_ name, job_id, salaryFROM employeesWHERE job_ _id = (SELECT job_ _idFROM employeesWHERE employee_id = 141AND salary> (SELECT salaryFROM employeesWHERE employee_ id = 143);

例3：返回公司工资最少的员工的last_ name, job_ _id和salary

select last_name,job_id,salaryfrom employees where salary =(select min(salary)from employees);

例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

select department_id,min(salary)from employeesGROUP BY department_idhaving min(salary)>(select min(salary)from employeeswhere department_id=50);

2.列子查询

一般搭配着多行操作符使用

IN/NOT IN  ANY/SOME ALL

IN/NOT IN：等于列表中的任意一个
ANY/SOME：和子查询返回的某一个值比较
ALL：和子查询返回的所有值比较

例1：返回location id是1400或1700的部门中的所有员工姓名

select last_namefrom employeeswhere department_id in(select department_idfrom departmentswhere location_id IN(1400,1700));

4.select后面的标量子查询

例1：查询每个部门的员工个数

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

例2：查询员工号=102的部门名

select (select department_namefrom department dinner join employees eon d.department_id=e.department_idwhere e.employee_id=102);

5.from后面的表子查询

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

SELECT
ag_dep.*,g.grade_level
FROM 
(
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id
)ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal
;

6.exists后面的子查询（相关子查询）

exists(完整的查询语句)

例1：查询有员工名和部门名

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

7.分页查询

语法：

select查询列表
from 表1
[ 连接类型 join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段 ]
limit offset, size;
offset要显示条目的起始索引(起始索引从0开始)mysql中其余都是从1开始。
size 要显示的条目个数

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

select * from employees limit 0,5;

例2：查询第11条一第25条

SELECTFROMemployees LIMIT 10, 15; .

特点:
①limit语句放在查询语句的最后
②公式：
要显示的页数page，每页的条目数size
select查询列表
from表
limit (page-1) *size, size;

size=10
page：页码
index：索引

page	index
1	0
2	10
3	20

8.union联合查询

将多条查询语句的结果合并成- -个结果

1.语法：

​ 查询语句1
​ union
​ 查询语句2

2.应用场景：

要查询的结果来自于多个表，但查询的信息一致

例1：查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname,csex FROM t_ca WHERE csex='男 'UNIONSELECT t_ id, tName, tGender FROM t_ua WHERE tGender= 'male' ;

3.特点

查询列数要相同，
结果相同会去除相同行，如果不要去除相同行，加ALL

如：

SELECT id,cname,csex FROM t_ca WHERE csex='男 'UNION ALLSELECT t_ id, tName, tGender FROM t_ua WHERE tGender= 'male' ;

四、DML语言

DML：数据操作语言
插入：insert
修改：update
删除：delete

1.插入语句

语法1：支持插入多行

​ 支持子查询

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

例1：

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

例2：

INSERT INTO beauty (id, NAME, phone)SELECT id, boyname, '1234567'FROM boysWHERE id<3;

语法2：不支持插入多行

insert into 表名 set 列名=值,列名=值,列名=值;

2.修改语句

update

语法1：修改单表

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

例1：修改beauty表中姓唐的女神的电话为13899888899

UPDATE beauty SET phone = '13899888899 '
WHERE NAME LIKE '唐号' ;

语法2：修改多表

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

例2：修改张无忌的女朋友的手机号为114

UPDATE boys bo
INNER JOIN beauty b 
ON bo.id=b.boyfriend_id
SET b.phone='114'
WHERE  bo.boyName='张无忌';

3.删除语句

1.delete

语法1：单表的删除

delete from 表名 where 筛选条件;

例1：删除雇佣id为100的用户

delete from employees where employee_id=100;

语法2：多表的删除

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

例2：删除张无忌的信息以及他女朋友的信息

delete b,bofrom beauty binner join boys boon b.boyfriend_id=bo.idwhere bo.boyName='张无忌';

2.truncate（清空数据）

语法：多表的删除

truncate 不能加where等其他语句或条件

truncate table 表名;

例2：

truncate table boys;

3.truncate与delete比较

1.truncate的效率比delete效率高一点

2.假如要删除的表中有自增长列：
delete删除后，再插入数据，自增长列的值从断点开始，
truncate删除后，再插入数据，自增长列的值从1开始。

3.truncate删除没有返回值，delete删除有 返回值

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

五、DDL语言

DDL：数据定义语言

库和表的管理

库和表的创建、修改、删除

创建：create
修改：alter
删除：drop

1.库的管理

1.库的创建

语法：

create database [if not exists] 库名;

例1：

create database if not exists book;

2.库的修改

1.修改名字

rename database 库名 to 新库名;

此语句已经过时，不可再用

2.更改库的字符集

alter database 库名 character set utf8;

3.库的删除

语法：

drop database if exists book;

2.表的管理

1.表的创建

语法：

create table [if not exists] 表名(	字段名 字段类型(长度) [约束],	字段名 字段类型(长度) [约束],	字段名 字段类型(长度) [约束],	...	字段名 字段类型(长度) [约束])

2.表的修改

1.添加列

语法：

alter table 表名 add columnn 列名 列类型 	约束	[first | after 字段名];

例1：

alter table student add column stuAge int after stuName;

2.修改例的类型和约束

语法：

alter table 表名 modify column 新类型 [新约束];

例2：

alter table student modify column int(10) primary key;

3.修改列名

语法：

alter table 表名 change column 列名 新列名 类型(长度);

这里的类型是原类型+长度

例3：

alter table student change column stuName student_Name  varchar(20);

4.删除列

语法：

alter table 表名 drop column 列名;

例1：

alter table stu drop column stuAge;

5.修改表名

语法：

alter table 表名 rename 新表名;

例：

alter table user rename stu_user;

3.表的删除

语法：

drop table 表名;

例：

drop table user if exists;

4.表的赋值

1.仅仅复制表的结构

语法:

create table 复制的表名 like 表名;

例1：

create table user2 like user;

2.复制表结构和数据

语法：

create table 复制的表名
selete * from 表名;

例2：

create table user3
select * from user;

3.通用写法

1.创建数据库

语法:

drop database if exists 旧库名;create database 新库名;

2.创建表

语法：

drop table if exists 旧表名;create table 表名();

4.mysql中的数据类型

1.分类

数值型：整数
小数：定点数
浮点数

字符型：
较短的文本：char ,varchar
较长的文本：text , blob(较长的二进制数据)
日期型：

2.整型

1.分类：

类型	tinyint	smallint	mediumint	int/integer	bigint
字节	1	2	3	4	8

2.使用有符号和无符号
默认有符号
使用无符号，加上关键字unsigned
例：

drop table if exists table1create table	table1(	--有符号	t1 int ,	--无符号	t2 int unsigned)

如果数值超出范围，插入的是数值范围的临界值

3.zerofill关键字

int(7)表示显示结果该列的宽度，如果不够用0填充，默认不会出现0进行填充。需要使用zerofill进行填充，且将自动变为无符号

例：

create table table1(	id int(7) zerofill,	length int(7))

3.小数

1.浮点型：

float(M,D)
doublee(M,D)
默认M和D会根据插入的数值的精度来决定精度

2.定点型：

dec(M,D)
decimal(M,D)
M默认是10 ，D默认是0

M和D：

M表示有效位数，D表示小数点后的有效位数

定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

4.字符型

较短的文本：

类型	char	varchar
字节	1	2
特点	固定长度	可变长度
耗费的空间	比较耗费	比较节省
效率	高	低

char 可以省略长度，默认为1
varchar不可以省略长度

了解enum，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') ;

binary和varbinary用于保存较短的二进制

较长的文本：
text
blob

5.日期类型

分类：

日期和时间类型	date	datetime	timestamp	time	year
字节	4	8	4	3	1
形式	1000-01-01	1000-01-01 00:00:00	1000-01-01 00:00:00	-838:59:59	1901

date只保存日期
time 只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间

5.mysql常见约束

1.含义

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

2.分类：六大约束

not null : 非空，用于保证该字段的值不能为空。比如姓名、学号
default : 默认，用于保证该字段有默认值。比如性别
primary key : 主键，用于保证该字段的值具有唯一性，并且非空。比如学号、员工编号等。
unique : 唯一，用于保证该字段的值具有唯一性，但可以为空。比如座位号
check : 检查约束【mysql不支持】，比如年龄、性别
foreign key ：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联表的列的值。比如员工的部门编号。

3.添加约束的时机：

1.创建表时：

1.添加列级约束

语法：

字段名  字段类型  约束类型

例1：添加列级约束

create table student(
	--主键约束
	id int primary key,
	--非空
	name varchar(25) not null,
	检查约束
	sex char(1) check(sex='男' or sex='女'),
	--唯一
	seat int unique,
	--默认
	age int default 18 ,
	--外键
	majorId int foreign key references major(id)
);
create table major(
	id int primary key,
	name varchar(15)
);

2.添加表级约束

语法：

constraint 约束名称  约束类型(字段名) 

外键

[constraint 约束名称 ] 约束类型(字段名) references 另一个表(另一个表的字段)

例2：添加表级约束

create table student(	id int,	name varchar(25),	sex char(1),	seat int,	age int,	majorId int,	--主键	constraint pk primary key(id),	constraint uq unique(seat),	constraint ck check(sex='男' or sex='女'),	constraint fk_stuindo_major foreign key(majorId)  references major(id));

2.修改表时：

1.添加非空约束

语法：

alert table 表名 modify column 字段名 类型(长度) not null;

2.添加默认约束

语法：

alert table 表名 modify column 字段名 类型(长度) default 值;

3.添加主键

1.列级约束

语法：

alert table 表名 modify column 字段名 类型(长度) primary key;

2.表级约束

语法：

alert table 表名 add primary key(字段名);

4.添加唯一约束

1.列级约束

语法:

alert table 表名 modify column 字段名  类型(长度)  unique;

2.表级约束

语法：

alert table 表名 	add unique(字段名);

5.添加外键

语法:

alert table 表名 add constraint 外键名称 foreign key(字段名)  refierences 外键的表(外键的字段名);

4.约束的添加分类

1.列级约束：

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

2.表级约束

除了非空、默认，其他约束都支持

3.查看表中所有索引:

show index from 表名;

5.主键和唯一的区别

约束类型	保证唯一性	是否允许为空	是否可多个	是否允许组合
主键	是	否	最多有一个	允许，但不推荐
唯一	是	是	可多个	允许，但不推荐

6.外键的特点

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

7.修改表时删除约束

1.删除非空约束

语法：

alert table 表名 modify column 字段名 类型(长度) null;

2.删除默认约束

语法：

alert table 表名 modify column 字段名 类型(长度);

3.删除主键

语法:

alert table 表名 drop primary key ;

4.删除唯一

语法：

alert table 表名 drop index 字段名 ;

5.删除外键

语法：

alert table 表名 drop foreign key 外键名;

8.标识列（自增长列）

含义：可以不用手动的插入值，系统提供默认的序列值，默认从1开始，偏移为1

auto_increment

更改偏移：

set auto_increment_increment=值;

特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key(主键、唯一、外键或者自定义key)
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型(INT,FLOAT等)
4、标识列可以通过 SET auto_increment_increment=3;设置步长

六、TCL语言

Transaction Control Language : 事务控制语言

1.事务概念

事务:

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

一个最小的不可再分的工作单元。

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

2.特点(ACID)

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

3.事务的创建

1.隐式事务

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

2.显示事务

事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

set autocommit=0;

步骤1：开启事务

set autocommit=0；
start transaction;

步骤2：编写事务中的sql语句
语句1；
语句2；
…

步骤3：结束事务
commit; //提交事务
rollback； //回滚事务

4.数据库的隔离级别

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

1.并发问题

1.脏读:对于两个事务T1, T2, T1读取了已经被T2更新但还没有被提交的字段之后,若T2回滚, T1读取的内容就是临时且无效的.
2.不可重复读:对于两个事务T1, T2, T1读取了一一个字段,然后T2更新了该字段之后, T1再次读取同一个字段,值就不同了。
3.幻读:对于两个事务T1, T2,T1从一个表中读取了一个字段,然后T2在该表中插入了一些新的行。之后,如果T1再次读取同一个表,就会多出几行。

数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力。使它们不会相互影响,避免各种并发问题。

一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱。

2.隔离级别

READ UNCOMMITED(读未提交数据)：允许事务读取未被其他事务提交的变更，脏读、不可重复读和幻读的问题都会出现。
READ COMMITED(读已提交数据)：只允许事务读取已经被其它事务提交的变更,可以避免脏读.但不可重复读和幻读问题仍然可能出现
REPEATABLE READ(可重复读)：确保事务可以多次从一个字段中读取相同的值.在这个事务持续期间，禁止其他事物对这个字段进行更新.可以避免脏读和不可重复读,但幻读的问题仍然存在.
SERIALIZABL E(串行化)：确保事务可以从一个表中读取相同的行，在这个事务持续期间,禁止其他事务对该表执行插入,更新和删除操作。所有并发问题都可以避免,但性能十分低下。

3.默认支持的事务级别

Oracle支持的2种事务隔离级别: READ COMMITED,SERIALIZABLE。Oracle 默认的事务隔离级别为: READ
COMMITED
Mysql支持4种事务隔离级别.Mysql默认的事务隔离级别默认为:REPEATABLE READ

4.设置隔离级别

每启动一个mysql程序,就会获得一个单独的数据库连。每个数据库连接都有一个全局变量@@tx_isolation,表示当前的事务隔离级别。
查看当前的隔离级别: SELECT @ @tx_ _isolation;
设置当前mySQL连接的隔离级别:
set transaction isolation level read committed;
设置数据库系统的全局的隔离级别:
set global transaction isolation level read committed;

5.savepoint：保存点

savepoint搭配rollback使用

演示savepoint的使用

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

6.delete与truncate在事务中的区别

delete：在使用事务会根据事务的提交或回滚
truncate : 在使用事务时不会根据事务的提交或回滚来进行相应操作，truncate不管是执行还是回滚都会执行

七、视图

一、含义

mysql5.1版本出现的新特性，本身是一个虚拟表，它的数据来自于表，通过执行时动态生成。
好处：
1、简化sql语句
2、提高了sql的重用性
3、保护基表的数据，提高了安全性

二、创建

create view 视图名
as
查询语句;

三、修改

方式一：

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

方式二：

alter view 视图名
as
查询语句

四、删除

drop view 视图1，视图2,…;

五、查看

desc 视图名;
show create view 视图名;

八、变量

1.分类

系统变量：
全局变量
会话变量
自定义变量：
用户变量
局部变量

2.系统变量

1.概念

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

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

2.语法

1.查看所有系统变量

show global |  session variables;

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

show global | session.系统变量名  like  '%变量%'；

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

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

4.为某个给系统变量赋值

方式一:

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

方式二：

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

**注：**如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果什么都不写，默认session。

3.系统变量中的全局变量

1.作用域

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

2.查看所有的全局变量

show global variables;

3.查看部分的全局变量

show global variables like '%变量%';

4.为某个指定的全局变量赋值

set @@global.系统变量名=值

例：

set @@global autocommit=0;   

4.系统变量中的会话变量

1.作用域

仅仅针对于当前会话(连接)

1.查看所有的会话变量

show variables;
或者
show session variables;

2.查看部分的会话变量

show session variables like '%变量%'；
或者
show variables like '%变量%'；

3.查看指定的某个会话变量

select @@session.变量名；
或者
select @@变量名；

4.为某个会话变量赋值

set @@session.变量名=值；
或者
set session 变量名=值；

3.自定义变量

1.概念

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

2.用户变量

1.作用域

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

2.声明并初始化

赋值的操作符： =或 :=

set @用户变量名=值；
或
set @用户变量名:=值；
或
select @用户变量名:=值；

例1：

set @name='john';

3.赋值

set @用户变量名=值；
或
set @用户变量名:=值；
或
select @用户变量名:=值；
或
select 字段(或变量) into @用户变量名

例2：

select count(1) into @count
from employees;

3.局部变量

1.作用域

仅仅在定义它的begin end 中有效，且

2.声明

declare 变量名 类型；
或
declare 变量名 类型 default 值；

3.赋值

set 局本变量名=值；
或
set 局本变量名:=值；
或
select @局本变量名:=值；
或
select 字段 into 局本变量名 from 表；

4.使用

select 局本变量名；

九、存储过程和函数

1.存储过程

1.存储过程概念

说明：都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字。
好处：
1、提高重用性
2、sql语句简单
3、减少了和数据库服务器连接的次数，提高了效率

2.存储过程创建

create procedure 存储过程名(参数模式  参数名 参数类型)
begin
	存储过程体
end

注：
1、参数列表包含三部分：参数模式 参数名 参数类型
举例:
IN stuname VARCHAR(20)
参数模式:
IN: 该参数可以作为输入，也就是该参数需要调用方传入值
OUT:该参数可以作为输出，也就是该参数可以作为返回值
INOUT:该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
2、如果存储过程体仅仅只有一句话，BEGIN END可以省略
存储过程体中的每条sQL语句的结尾要求必须加分号。
存储过程的结尾可以使用 DELIMITER 重新设置
语法:
DELIMITER 结束标记
案例:
DELIMITER S

3.存储过程调用

call 存储过程名（实参列表）

在cmd中执行call需要加$ ,navicat不需要，但要加分号

1.空参列表

案例1：插入到admin表中五条记录

delimiter  $
create procedure  myp1()
begin
	insert into admin(username,password) values('zs','0000'),('ls','0000'),('ws','0000'),('as','0000'),('zg','0000');
end $

调用

call myp1() $

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

in可以省略

案例2：创建存储过程实现根据女神名，查询对应的男神信息

delimiter $
create procudure myp2(in beautyName varchar(20))
begin 
	select bo.*
	from boys bo
	left join beauty b on bo.id=b.id
	where b.name=beautyName;
end $

调用

call myp2('柳岩') $

案例3：创建存储过程实现，用户是否登录成功

delimiter $
CREATE PROCEDURE myp4|(IN username VARCHAR(20) , IN PASSWORD VARCHAR(20) )
BEGIN
DECLARE result VARCHAR(20) DEFAULT '' ;#声明并初始化
SELECT COUNT(*) INTO result #赋值
FROM admin
WHERE admin. username = use rname
AND admin. password = PASSWORD;
SELECT result; #使用
END $

#调用

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

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

案例1:根据女神名，返回对应的男神名

CREATE PROCEDURE myp5 (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 myp5(' 小昭' , @bName) $
SELECT @bName $

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

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

delimiter $
CREATE PROCEDURE myp8 (INOUT a INT , INOUT b INT)
BEGIN
SET a=a*2 ;
SET b=b*2 ;
END $
#调用
SET @m=10$
SET @n=20$
CALL myp8 (@m, @n)S
SELECT @m, @n$

4.查看存储过程

show create procedure 存储过程名；

5.删除存储过程

drop procedure  c

2.函数

1.函数概念

说明：都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字。
好处：
1、提高重用性
2、sql语句简单
3、减少了和数据库服务器连接的次数，提高了效率

2.函数与存储过程的区别

存储过程:可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数:有且仅有1个返回，适合做处理数据后返回一个结果

3.创建函数语法

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

注：
1.参数列表包含两部分，参数名 参数类型
2.函数体:肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return值;
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记

4.调用语法

select 函数名(参数列表)；

例1：无参有返回，返回公司的员工个数

delimiter $
create function muyf1() return int
begin
	declare  c int default 0;
	select count(*)  into c
	from employees;
	return c;
end $
#调用
select myf1() $

例2：有参有返回，根据员工名返回他的工资

delimiter $
create function myf2(empName varchar(20)) return double
begin
	set @sal=0;
	select salary into @sal
	from employees
	where last_name=empName; 
	retrun @sal;
end $

select myf2('K_ing');

5.函数的查看

show create function 函数名;

6.函数的删除

drop function 函数名;

十、流程控制结构

1.分类

顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上，重复执行一段代码

1.分支结构

1.if函数

功能:实现简单的双分支
语法:
if (表达式1,表达式2 ,表达式3)
执行顺序:
如果表达式1成立，则if函 数返回表达式2的值，否则返回表达式3的值
应用:任何地方

2.case结构

情况1:类似于java 中的switch语句，一般用于实现等值判断

语法:

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

情况2:类似于java中的多重IF语句，一般用于实现区间判断

语法:

CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
ELSE 要返回的值n或语句n;.
END CASE;

特点:

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

例：根据传入的成绩，来显示等级，比如传入的成绩: 90-100， 显示A， 80-90， 显示B，60-80， 显示C，否则，显示D

CREATE PROCEDURE test_ case (IN score INT )
]BEGIN
CASE 
WHEN score>=90 AND score<=100 THEN SELECT ' A' ;
WHEN score>=80 THEN SELECT ' B' ;
WHEN score>=60 THEN SELECT 'C' ;
ELSE SELECT 'D' ;
END CASE; 
END $
CALL test_ case(95) $

3.if结构

功能：实现多分支
语法：

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

**位置：**只能放在begin end中

2.循环结构

1.位置

只能放在begin end中

1.分类

while、loop、repeat

2.循环控制语句：

iterate：类似于continue， 继续，结束本次循环，继续下一次
leave：类似于break,跳出，结束当前所在的循环

3.while

语法:

[名称:] while 循环条件 do
循环体
end while [名称] ;

2、loop
语法:

[名称: ] 1oop
循环体
end loop [名称] ;

3、repeat
语法:

[名称:] repeat
循环体
until结束条件
end repeat [名称] ;

对比

①这三种循环都可以省略名称，但如果循环中添加了循环控制语句 (leave或iterate)则必须添加名称
②loop一般用于实现简单的死循环
while先判断后执行
repeat先执行后判断,无条件至少执行一-次
tion muyf1() return int
begin
declare c int default 0;
select count(*) into c
from employees;
return c;
end $
#调用
select myf1() $

例2：有参有返回，根据员工名返回他的工资

delimiter $
create function myf2(empName varchar(20)) return double
begin
set @sal=0;
select salary into @sal
from employees
where last_name=empName;
retrun @sal;
end $

select myf2(‘K_ing’);

### 5.函数的查看

show create function 函数名;

### 6.函数的删除

drop function 函数名;

# 十、流程控制结构

## 1.分类

顺序结构:程序从上往下依次执行
分支结构:程序从两条或多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上，重复执行一段代码

## 1.分支结构

### 1.if函数

功能:实现简单的双分支
语法:
if (表达式1,表达式2 ,表达式3)
执行顺序:
如果表达式1成立，则if函 数返回表达式2的值，否则返回表达式3的值
应用:任何地方

### 2.case结构



#### 情况1:类似于java 中的switch语句，一般用于实现等值判断

语法:

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

#### 情况2:类似于java中的多重IF语句，一般用于实现区间判断

语法:

CASE
WHEN 要判断的条件1 THEN 返回的值1或语句1;
WHEN 要判断的条件2 THEN 返回的值2或语句2;
ELSE 要返回的值n或语句n;.
END CASE;

#### 特点:

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

例：根据传入的成绩，来显示等级，比如传入的成绩: 90-100， 显示A， 80-90， 显示B，60-80， 显示C，否则，显示D

CREATE PROCEDURE test_ case (IN score INT )
]BEGIN
CASE
WHEN score>=90 AND score<=100 THEN SELECT ’ A’ ;
WHEN score>=80 THEN SELECT ’ B’ ;
WHEN score>=60 THEN SELECT ‘C’ ;
ELSE SELECT ‘D’ ;
END CASE;
END $
CALL test_ case(95) $

### 3.if结构

功能：实现多分支
语法：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
…
else 语句n;
end if;

**位置：**只能放在begin end中

## 2.循环结构

### 1.位置

只能放在begin end中

### 1.分类

while、loop、repeat

### 2.循环控制语句：

iterate：类似于continue， 继续，结束本次循环，继续下一次
leave：类似于break,跳出，结束当前所在的循环

### 3.while

语法:

[名称:] while 循环条件 do
循环体
end while [名称] ;

2、loop 
语法:

[名称: ] 1oop
循环体
end loop [名称] ;

3、repeat
语法:

[名称:] repeat
循环体
until结束条件
end repeat [名称] ;

### 对比

①这三种循环都可以省略名称，但如果循环中添加了循环控制语句      	(leave或iterate)则必须添加名称
②loop一般用于实现简单的死循环
	while先判断后执行
	repeat先执行后判断,无条件至少执行一-次