MySQL学习笔记——基础与进阶篇「建议收藏」

 

 

目录

　　一、###MySQL登录和退出

 

　　二、###MySQL常用命令

 

　　三、###MySQL语法规范

 

　　四、###基础查询

 

　　五、###条件查询

 

　　六、###排序查询

 

　　七、###常见函数的学习

 

　　八、###分组查询

 

　　九、###连接查询

 

　　十、###子查询

 

　　十一、###分页查询

 

　　十二、###联合查询

 

　　十三、###DML语言

 

　　十四、###DDL语言

 

　　十五、###常见的数据类型

 

　　十六、###常见的约束

 

　　十七、###标识列

 

　　十八、###TCL

 

　　十九、###视图

 

　　二十、###变量

 

　　二十一、###存储过程和函数

 

 

###1.MySQL登录和退出
登录：
mysql (-h主机名 -p端口号)-u用户名 -p密码
退出：
exit或者ctrl+c

###2.MySQL基础命令
1.查看所有数据库
show databases;
2.打开库
use 库名;
3.查看其他库中的表
show tables from 库名;
4.创建表
creat table 表名(

列名 列类型，
列名 列类型，
。。。
);
5.查看表结构
desc 表名;

###3.MySQL语法规范

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

###4.基础查询

#语法：
select 查询的东西
from 表名;

#特点：
1.查询的东西可以是：字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格。

#1.查询表中的单个字段
SELECT NAME FROM city;

#2.查询表中的多个字段
SELECT NAME,id FROM city;

#3.查询表中所有的字段
SELECT * FROM city;

#4.查询常量值
SELECT 100;
SELECT “beijing”;

#5.查询表达式
SELECT 100*98;

#6.查询函数
SELECT version(); //得到的是查询函数的返回值

#7.为字段起别名
①便于理解
②如果查询的字段有重名情况，使用别名可以区分开

#方式一：使用As
SELECT 100*98 As 结果;
SELECT “beijing” AS 首都; 

#方式二：使用空格
SELECT “beijing” 首都; 

#案例：如果别名中存在关键字或特殊符号，加双引号
（单引号也可，但建议双引号）

#8.去重
SELECT DISTINCT “beijing” from city;

#9.+的作用
运算符： 
例如：select 100+10;//两个操作数都为数值型，则作加法运算
select “123”+90;//一个操作数是字符，另个一是数值，会
尝试将字符型转换为数值型 结果：213;
SELECT DISTINCT “beijing” + 10;//若不能转换，则将字符型
视作0 结果：10;
SELECT DISTINCT “beijing” + null;//若有一方是null，则
结果为null

###5.条件查询

#语法：
select 
查询列表
from
表名
where
筛选条件;
#分类
一、按条件表达式筛选
条件运算符> < = != <= >= 建议不等于用<>
案例：筛选id>50的城市
SELECT 
*
FROM 
city
WHERE
id>50

二、 按逻辑运算符筛选
逻辑运算符：
与：$$ 或：|| 非：!
建议使用： and or not
案例：查询id 50到100的城市：
SELECT 
*
FROM 
city
WHERE
id>50 AND id<100

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

#1.like
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符
_ 任意单个字符
如果查询的中含有通配符，应转译 例如 \_

案例：查找ch开头的国家
SELECT 
*
FROM 
country

WHERE 
NAME LIKE “ch%”

#2.between and

特点：
①包含临界值
②调换顺序不报错，但是错误

案例：查找id在100到120的城市
SELECT 
*
FROM 
city

WHERE 
id BETWEEN 100 AND 120

#3.in
特点：
①in列表的值必须一致或兼容
②不能使用通配符

案例：查询国家代码是CHN和NLD的城市名和ID
SELECT 
id,
NAME
FROM 
city
WHERE 
countrycode IN (“CHN”,”NLD”)

#4.is null
id = null 不成立 
id isnull

###6.排序查询
语法：
select 查询列表
from 表
【where 筛选条件】
order by 排序列表 【asc|desc】
特点：
①asc是升序 desc是降序，如果不写，默认升序
②支持单个字段，也支持多个字段、表达式、函数、别名
③order by子句一般放在查询语句的最后，limit子句除外

案例1：查询城市名字和id，按id降序
SELECT 
id AS 序号,NAME AS 名字
FROM 
city
ORDER BY 
id DESC
案例2：按照城市名的字节长度排序【按函数排序】
SELECT 
*,LENGTH(NAME)
FROM 
city
ORDER BY LENGTH(NAME) DESC

案例3：按别名排序

案例4：先按照名字长度排序，再按id排序【按多个字段排序】
SELECT 
*,LENGTH(NAME)
FROM 
city
ORDER BY LENGTH(NAME) DESC,id ASC

###7.常见函数的学习

概念：类似java的方法

调用：select 函数名(参数列表) 【from 表】

分类：
1.单行函数
如 concat、length、ifnull等
2.分组函数
功能：做统计使用，又称作统计函数、聚合函数、组函数

# 单行函数

#一.字符函数

#1.length 获取参数值的字节个数

#2.concat 拼接字符串

#3.upper、lower 变大写，变小写

#4.substr 、substring 截取字符串
注意：索引从1开始
#一个参数：截取从指定索引到结尾
SELECT 
SUBSTR(“李莫愁爱上了陆湛远”,7)
输出陆湛远
#两个参数：截取从指定索引指定长度
SELECT 
SUBSTR(“李莫愁爱上了陆湛远”,1,3)
输出李莫愁

#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR(“杨不悔爱上了殷六侠”,”殷六侠”)
输出7

#6.trim
SELECT TRIM(” 周福利 “)
输出：周福利

SELECT TRIM(“a” FROM “aaaaa周aaaa福aaaaa利aaaaa”)
输出：周aaaa福aaaaa利

#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD(“周福里”,10,”*”)
输出：*******周福里
#8.rpad 用指定的字符实现右填充指定长度

#9.replace 替换
SELECT REPLACE(“周芷若周芷若周芷若张无忌爱上了周芷若周芷若”,”周芷若”,”赵敏”)
输出：赵敏赵敏赵敏张无忌爱上了赵敏赵敏

#二、数学函数

#1.round 四舍五入
先转化为绝对值，再四舍五入
SELECT ROUND(-1.51)
SELECT ROUND(-1.515,2)小数点后保存两位

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

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

#4.truncate 截断
SELECT TRUNCATE(1.6999,1)
输出：1.6

#5.mod 取余
mod(a,b) : a-a/b*b;

#三、日期函数

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

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

#3.curtime 返回当前时间，不含日期

#4.可以获取指定部分，年、月、日、小时、分钟、秒
select year(now()) 年;
select year(“1998-1-1”) 年;
输出1998

select month(now());
9
select monthname(now());
December

#5.str_to_date:将字符转换为指定类型的日期
select str_to_date(“4-3 1992″,”%c-%d %Y”)
输出：1998-03-02
%Y 四位年份的年
%y 2位年份的年
%m 月份（01，02，…,11，12）
%c 月份（1，2，3.。。。，11，12）
%d 日（01，02.。。。）
%H 小时（24小时制）
%h 小时（12小时制）
%i 分钟（00，01，02，….，59）
%s 秒（00，01，02，….，59）

#6.date_format将时间类型转化为字符串
SELECT DATE_FORMAT(NOW(),”%Y年%m月%d日”)
输出：2019年12月06日

#7.datediff（ ， ）
查找两个日期相差的时间

#四、其他函数
version（）查看版本
database（）查看当前数据库
user（）查看当前用户

#五、流程控制函数
#1.if函数： if else的效果
SELECT IF(10>5,”大”,”小”)
输出：大

#2.case函数的使用一： switch case的效果
语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
….
else 要显示的值n或语句n;
end

案例：
SELECT ID 序号, NAME 名字,
CASE id
WHEN 1 THEN “test”
ELSE NAME
END AS 新姓名
FROM city

#3.case 函数的使用二： 类似于多重if
语法：
case 
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
.。。
else 要显示的值n或语句n
end

案例：
SELECT ID 序号, NAME 名字,
CASE 
WHEN id<=10 THEN “A”
WHEN id>10 AND id<=20 THEN “B”
ELSE “C”
END AS 新姓名
FROM city

#二、分组函数
/*
功能：用作统计使用，又称聚合函数或统计函数或组函数

分类：sum求和、avg平均值、max最大值、min最小值、count计算个数
*/

#1、简单的使用
SELECT SUM(id) FROM city
SELECT COUNT(id) FROM city
…..

#2.参数支持哪些类型
sum和avg只支持数值类型 不支持字符型 不支持日期类型

max和min值支持字符型、数字型、日期型

count任何类型都支持 

#3.是否忽略null值
以上分组函数都忽略null值

#4.可以和distinct搭配

#5.count函数的详细介绍
SELECT COUNT(1) FROM city//显示所有字段
SELECT COUNT(*) FROM city

#6. 和分组函数一同查询的字段有限制
和分组函数一同查询的字段要求是group by后的字段

 

 

 

###8.分组查询
语法：
select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组列表
【order by 子句】

特点：
①分组查询中的筛选条件可以分为分组前查询和分组后查询
②分组查询支持单个字段分组，也支持多个字段分组（中间用逗号隔开，顺序可变）
③也可以添加排序（放在最后）

案例1：查询每个国家的最大城市id
SELECT MAX(id) 最大id,`CountryCode`
FROM city
GROUP BY countrycode
ORDER BY 最大id

案例2：查询每个国家的城市数
SELECT COUNT(*),`CountryCode`
FROM city
GROUP BY countrycode

案例3：添加筛选条件
SELECT COUNT(*),`CountryCode`
FROM city
WHERE `CountryCode` =”chn”
GROUP BY countrycode

#添加复杂的筛选条件
案例：查询每个城市数大于50的国家
SELECT COUNT(*) 城市数,`CountryCode`
FROM city
GROUP BY countrycode
HAVING 城市数>50
由于where只能跟在from后面。所以用了新的having

###9.连接查询：
含义：又称多表查询，查询的字段来自不同表时使用

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

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

#一、sql92标准
#1.等值连接
#1.案例：查询城市名对应的国家名
SELECT city.`Name`,country.`Name`
FROM city,country
WHERE city.`CountryCode`=country.`Code`

#2.为表起别名
#与给字段起别名一至
#缩短表名长度，提高效率
#起完别名后则查询字段不能使用原来的表名限定

#3.两个表的顺序可以调换

#4.可以加筛选
#案例：
SELECT a.`Name`,b.`Name`
FROM city a,country b

WHERE a.`CountryCode`=b.`Code`
AND a.`ID`>10

#5.可以分组

#6.可以排序

#7.可以实现三表连接

#2.非等值连接

#3.自连接
使用别名进行区分

#二、sql99语法
/*
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表1 别名
on 连接条件
【where 筛选条件】
【group by】
【order】
*/

#连接类型关键字：
内连接：inner
外连接：
左外 left 【outer】
右外 right 【outer】
全外 full 【outer】
交叉连接：cross

#(一)内连接
select 查询列表
from 表1 别名
innet join 表2 别名
on 连接条件

#案例：
SELECT DISTINCT `CountryCode`,b.`Name`
FROM `city` a
INNER JOIN `country` b
ON a.`CountryCode`=b.`Code`

#(二)外连接
/*
应用场景：用于查询一个表中有，另一个表中没有的记录

特点：
1.外连接查询结果为主表中的所有记录
如果从表中没有和他匹配的值，则显示null
如果又和他匹配的值，则显示匹配的值
外连接查询的结果等于=内连接结果加主表中没有的记录
2.左外连接，left join 左边是主表
右外连接，right join 右边是主表
3.左外和右外互换表的位置也可以实现相同的效果

*/

#案例：
select b.name,bo.*
frome beauty b
left outer join boys bo
on b.id = bo.id

select b.name ,bo.*
frome boys bo
right outer join beauty b
on b.id = bo.id

#全外（msql不支持！）
select b.*,bo.*
from beauty b
full outer join boys b
on b.id = bo.id

#交叉连接
案例：
select b.*,bo.*
from beauty b
cross join boys bo
on b.id = bo.id 
产生笛卡尔积

#sql92和sql99
功能：sql99功能较多
可读性：sql99可读性更高

###10.子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，成为主查询或外查询

分类：
按子查询出现的位置：
select后面
仅仅标量子查询
from后面
支持表子查询
where或having后面?
标量子查询（单行）
列子查询（多行）

行子查询
exists后面（相关子查询）

表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

*/

#(一)where或having后面的子查询
1.标量子查询（单行子查询）
2.列子查询（多行子查询）

3.行子查询（多行多列）

#一、标量子查询

#特点：
①子查询都要放在小括号内
②子查询一般放在条件的右侧
③标量子查询一般搭配着单行操作符使用
< > >= <= 
列子查询一般搭配多行操作符使用
in、any/some、all
④子查询的执行要优先于主查询

#非法使用标量子查询

#案例：查询谁的工资比Abel高？
#①查询Abel的工资
select salary
from employee
where last_name = “Able”

#②查询员工的信息，满足salary>①的结果
select *
from employee 
where salary > (
select salary
from employee
where last_name = “Able”
) 

#(二)列子查询（多行子查询）
关键字： IN any/some all
#案例：
select *
from employee
where asalry<all(

) 

#(三)行子查询（结果集一行多列或多行多列）
不常用

#二、select后面
/*
#里面仅支持标量子查询
*/

#三、将子查询放在from后面

#四、exists后面（相关子查询）

/*
语法：
exists(完整的查询语句)
结果：
0或1
*/

###11.分页查询
/*

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

语法：

limit offset,size;

offset:要显示条目的起始索引（索引从0开始）
size：要显示的条目个数

*/

#案例：
查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;

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

#应用场景：
要查询的结果来自于多个表，多个表没有直接关系，但查询的信息相同

#注意事项：
①要求多条查询语句的查询列数是一致的
②查询的每列类型和顺序是一致的
③union关键字默认是去重的，如果使用union all会保留所有项

###12.联合查询
/*
union 将多条查询语句的结果合并成一个结果

语法：
查询语句1
union
查询语句2
union
…
*/

#案例1：查询部门编号>90或邮箱中包含a的员工信息
做法1：
select *
from employees
where email like “%a%” or department_id>90

做法2：
select *
from employees
where email like “%a%”
union
select *
from employees 
where department_id >90

#案例2：查询中国用户男士的信息和外国用户男士的信息

###13.DML语言
/*
数据操作语言：
插入：insert
修改：update
删除：delete
*/ 

#一、插入语言：

#方式一：
/*
#语法：
insert into 表名(列名,….) values(值1,…) 

#注意事项：
①插入值的类型要与列的类型一致
②不可以为null的列必须插入值，可以为null的列，写null或直接不写列
③列的顺序可以颠倒
④列和值的个数必须一致
⑤可以省略列名，默认是所有列，而且列的顺序和表中的顺序一致

*/

#方式二：
/*
语法：
insert into 表名
set 列名=值,列名=值,….

*/

#两种方式pk 
①方式1支持多行输入，方式二不支持
②方式1支持子查询，方式二不支持

#二、修改语句

/*

1.修改单表中的记录：
语法：
update 表名
set 列= 新值,…
where 筛选条件;
案例：
update beauty 
set sex = “a”
where name = “苏德间”

2.修改多表中的记录
语法：
update 表1 别名,
inner join 表2 别名
on 连接条件
set 列= 值…
where 筛选条件

#案例1：修改张无忌的女朋友的手机号为112
UPDATE beauty b
INNER JOIN boys bo
ON b.`boyfriend_id` = bo.`id`
SET b.`phone` = “112”
WHERE bo.`boyName` = “张无忌”

#案例2：修改没有男朋友的boyfriendid为5
UPDATE beauty b 
LEFT OUTER JOIN boys bo 
ON b.`boyfriend_id` = bo.`id` 
SET b.`boyfriend_id` = 5 
WHERE bo.`id` IS NULL; 
*/

#三、删除语句

/*

#方式一：delete
语法：
delete from 表名 where 筛选条件

多表删除
#案例一：删除手机号0结尾的
delete from beauty where phone like “%0”

#方式二：truncate
语法：truncate table 表名

#两种方式pk
①delete可以添加where条件，另一个不能
②truncate删除效率高一丢丢
③如果使用delete删除所有后，再插入数据，自增长列从断点开始
truncate删除后自增长列从1开始
④truncate删除没有返回值，delete删除有返回值
⑤truncate删除不能回滚，delete可以回滚

*/

###14.DDL语言
/*
数据定义语言

库和表的管理

#一、库的管理
创建、修改、删除
#二、表的管理
创建、修改、删除

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

*/

#一、库的管理

#1.库的创建

#语法：
create datebase 库名;

#案例：
CREATE DATABASE books

#2.库的修改（基本不修改，会导致数据丢失，不安全）

#更改库的字符集
alter database books character set gbk

#3.库的删除

drop database if exists books

#二、表的管理
/*
alter table 表名 add|drop|modify|change column 

*/

#1.表的创建
create table 表名(
列名 列的类型【（长度） 约束】,
….
)

#2.表的修改
#①修改列名
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 table book_author;

#通用的写法
drop database if exists 旧库名;
create database 新库名;

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

#4.表的复制
#1.仅仅赋值表的结构
CREATE TABLE copy LIKE book_author`copy`

#2.复制表的结构+数据（或部分数据）
CREATE TABLE copy2 
SELECT * FROM book_author
【where】

只复制某些字段
CREATE TABLE copy3
SELECT id,au_name
FROM book_author

 

 

###15.常见的数据类型

/*
数值型：
整形：
小数：
定点数
浮点数

字符型：
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）

日期型：

*/

#一、整形
/*
分类：
tinyint、smallint mediumint int/integer、bigint

特点：
①如果不设置无符号，则默认有符号
②超出范围默认插入临界值，且会报异常
③如果不设置长度，会有默认长度
长度代表了显示的最大宽度，如果不够会用0填充，需搭配zerofill使用

*/
#1.如何设置有符号和无符号
unsigned 约束关键字

#二、小数
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)

特点：
①M:整数部位+小数部位
D:小数部位
如果超过范围，则插入临界值
②M和D可以省略
如果是decimal，则M默认是10，D默认是0
如果是float和double则会根据插入数值的精度确认精度
③定点型的精确度较高，如果对插入数值的精度要求较高可以使用，如货币

#原则：
所选择的类型越简单越好，能保存数值的类型越小越好

#三、字符型
/*
较短的文本：
char
varchar

较长的文本：
text
blob

*/
特点：

#char(M)
#varchar(M)
char和varchar区别：carchar可变，char不可变
varchar省空间 但是char效率较高
例如性别，可以用char
char的m可以省略，默认1 ，varchar不能省略

#enum类型： 枚举类型
案例：
create table season(
s1 ENUM(“春”,”夏”,”秋”,”冬”)
)
insert into season values (“春”)

#set类型：
create table tab_set(
s1 set(“a”,”b”,”c”,”d”)
)
insert into tab_set values (“a”)
insert into tab_set values (“a,b”)

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

#四、日期型
分类：
date 只保存日期
time 只保存时间
year 只保存年

datetime 日期+时间 受时区英雄
timestamp 日期+时间 不受时区影响

###16.常见的约束
/*

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

分类： 六大约束
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认的值
比如性别
PRIMATY KEY:主键，用于保证该字段的值具备唯一性并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如：座位号
CHECK:检查约束【mysql不支持】
比如：年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如：学生表的专业编号，员工表的部门编号

添加约束的时机：
1.创建表时
2.修改表时

约束的添加分类：
列级约束
六大约束语法上都支持，但外键约束没有效果

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

*/

#一、创建表时添加约束
#1.添加列级约束
语法：
直接在字段名和类型名后面追加约束

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 #默认

)

#2.添加表级约束
语法：
在各个字段的最下面
【CONSTRAINT 约束名】 约束类型（字段名）
取名可以省略（中括号内的）

CREATE TABLE stuinfo(
id INT ,
stuname VARCHAR(20), 
gender CHAR(1) ,
seat INT ,
age INT ,
magorid INT,

CONSTRAINT pk PRIMARY KEY(ID),
CONSTRAINT ck UNIQUE(seat),
CONSTRAINT fk_s_m FOREIGN KEY(magorid) REFERENCES magor(`m_id`)
)

#通用写法：
外键约束用表级，其他都用列级约束

#主键和唯一的对比：
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ X 至多有一个 √(组合主键，不推荐)
唯一 √ √ 可以有多个 √(不推荐)

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

#二、修改表时添加约束
/*
1.添加列级约束：
alter table 表名 modify column 字段名 字段类型 新约束

2.添加表级约束：
alter table 表名 add 【constraint 约束名】 约束类型(字段名)

*/

#1.添加非空、默认约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOTNULL

#2.添加主键、唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id)

#3.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_s_m FOREIGN KEY(majorid) REFRENCES major(id)

#三、修改表时删除约束
基本与添加一致
#删除唯一
alter table s drop index 约束名
#删除外键
alter table s drop foreign key 约束名

###17.标识列
/*

又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值

特点：
①标识列不一定和主键搭配，但一定要和key搭配
②一个表中最多有一个标识列
③标识列的类型只能是数值型，一般是int
④标识列可以通过
set auto_increment_increment= 3 
设置步长
可以通过设置第一个值的来决定开始数值

*/

#一、创建表时设置标识列
CREATE TABLE test1(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)

)

#二、修改表时设置标识列
alter table s modify colunm id primary key auto_increment

###18.TCL
/*

Transaction Control Language 事务控制语言

sql支持insert,update,delete

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

案例： 实时转账
张三非：1000
郭襄： 1000
update 表 set 张三非的余额= 500 whrer name = ‘张三非’
update 表 set 郭襄的余额 = 1500 where name = ‘郭襄’

#事务所具备的属性/特点：（ACID）
1.原子性（atomiciy）
事务是一个不可分割的工作单位，要么都发生，要么都不发生
2.一致性（consistency）
事务必须使数据库从一个一致性状态变换到另一个一致性的状态
3.隔离性（isolation）
事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务的操作及
使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能相互干扰
4.持久性（durability）
指一个事务一旦被提交，他对数据库中数据的改变是永久性的

#事务的创建

#隐式事务：事务没有明显的开启和结束的标记
比如：insert update delete

#显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能禁用
set autocommit = 0;

步骤1：开启事务
set autocommit = 0;
start transaction; 
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
…
步骤3：结束事务
commit;提交事务
rollback;回滚事务

#并发事务
1.事务的并发问题是如何发生的？
多个事务同时操作同一个数据库的相同数据时
2.并发的问题有那些？
脏读：一个事务读取了其他事务还没提交的数据，读到的是其他事务‘更新的事务’
不可重复读：一个事务多次读取，结果不一样
幻读：一个事务读取了其他事务没有提交的事务，读到了其他事务‘插入的事务’
3.如何解决并发问题？
设置隔离级别

#事务的隔离级别：
　　　　　　　　　　　　　　脏读 　　 幻读　　 不可重复度
read uncommitted(读未提交)： √ 　　　　 √　　　　   √
read committed(读已提交):　　 x 　　　　 √ 　　　　 √
repeatable read(可重复读):　　 x　　　　 x 　　　　 √
serializable(串行化)：　　　　 x　　　　  x　　　　   x
mysql中默认第三个隔离级别 repeatable read
orical中默认第二个隔离级别 read commited

查看隔离级别：
select @@tx_isolation;
设置隔离级别：
set session|global transaction isolation level 隔离级别;

savepoint 节点名; 设置保存点
配合rollback to 节点名; 使用。

*/

###19.视图
/*

含义：虚拟表，他的数据来自表，通过执行时动态生成

意义：①重用sql语句
②简化复杂的sql语句
③保护数据，提高安全性

#视图与表
创建语法及关键字 是否实际占用物理空间 使用
视图 create table 占用较小只是保存了sql逻辑 一般只是用查询
表 create view 是，保存了实际数据 增删改查

*/
#案例：查询J开头的员工的姓名和部门名
SELECT first_name ,department_name
FROM employees a
INNER JOIN departments b
ON a.`department_id` = b.`department_id`
WHERE a.`first_name` LIKE “J%”

建立视图：
CREATE VIEW v1
AS
SELECT first_name ,department_name
FROM employees a
INNER JOIN departments b
ON a.`department_id` = b.`department_id`

从视图中查询：
SELECT * FROM v1
WHERE first_name LIKE “J%”

#一、创建视图

#语法：
create view 视图名
as
查询语句;

# 

#二、视图的修改

#方式一：
create or replace view 视图名
as
查询语句;

#方式二：
alter view 视图名
as
查询语句;

#三、删除视图
#语法：
drop view 视图名,视图名,….;
DROP VIEW v1,v2

#四、查看视图
desc 视图
show create view 视图

#五、视图的更新
#1.插入
insert into 视图 values()
与表的插入类似

#2.修改
与表的更新类似

#3.修改
DELETE FROM v1 
WHERE last_name = “展飞”

#具备以下特点的视图是不允许更新的
1.包含 group by,distinct,分组函数,having,union ,union all
2.常量视图
3.select中包含子查询
4.join
5.from一个不能更新的视图
6.where子句的子查询引用了from子句中的表

###20.变量
/*

系统变量：
全局变量
会话变量

自定义变量
用户变量
局部变量

*/

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

#使用的语法：
1.查看所有的系统变量
show global|【session】 variables;
2.查看满足条件的部分系统变量
show global|【session】 variables like “%char%”
3.查看指定的某个系统变量的值
select @@ gloable|【session】.系统变量名; //跨连接有效
4.为某个系统变量复制
set gloabal|【session】 系统变量名 = 值;
注意：如果是全局级别，需要加global
如果是会话级别，则需要加session 
如果不写，默认session

#1、全局变量
#作用域：服务器每次启动将为所有的全局变量赋初始值，修改针对于所有的连接都有效，
但是不能跨重启(重启会初始化)

#2、会话变量
#作用域：仅仅针对当前会话(连接)有效

#二、自定义变量
/*

说明：变量由用户定义的，不是系统的

#使用步骤：
声明
赋值
使用（查看、比较、运算）

*/

#1.用户变量
#作用域：针对有当前会话（连接）有效的，同于会话变量的作用域

#应用：可以放在begin end任何地方
#①声明并初始化
赋值的操作符：= 或 :=
set @用户变量名 = 值;
set @用户变量名:=值;
select @用户变量名 :=值;

#②赋值(更新用户变量的值)
方式一：
set @用户变量名 = 值;
set @用户变量名:=值;
select @用户变量名 :=值;

方式二：通过select 字段 into @变量名

select 字段 into @变量名
from 表;

#③使用(查看用户变量的值)
select @用户变量

#2.局部变量
#作用域：仅仅再定义在他的begin end 中有效

#应用：应用在begin end中的第一句话

#①声明
declear 变量名 类型;
declear 变量名 类型 default 值;

#②赋值
方式一：
set 局部变量名 = 值;
set 局部变量名:=值;
select @局部变量名 :=值;

方式二：通过select 字段 into 变量名

select 字段 into 变量名
from 表;

#③使用
select 局部变量名;

#3.对比用户变量和局部变量
作用域 定义和使用的位置 语法
用户变量 当前会话 会话中的任何位置 必须加@符号，不用限定类型
局部变量 begin end中 只能在begin end中，且为第一句 一般不用加@符号，需要限定类型

###21.存储过程和函数
/*

#类似于java中的方法

#好处：
1.提高代码的重用性
2.简化操作

*/

#存储过程

#含义：一组预先编译好的sql语句的集合。

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

#一、创建语法
#语法：
create procedure 储存过程名(参数列表)
begin
存储过程体(一组合法的sql语句)
end
注意：
1.参数列表包含三部分
参数模式 参数名 参数类型
举例 IN stuname VARCHAR(20)

参数模式：
IN：该参数可以作为输入，也就是说该参数需掉用方法传入值
OUT：该参数可以作为输出，也就是该参数可以作为返回值
INOUT：该参数既可以作为输入，又可以作为输出，即该参数既可以传入值，也可以返回值
2.如果存储过程体仅仅只有一句话，begin end 可以省略
存储过程体中的每条sql语句的结尾必须加分号
存储过程的结尾可以使用delimiter重新设置
语法： delimiter 结束标记
案例: delimiter $

#二、调用语法
#语法：
call 存储过程名(实参列表);

#1.空参列表
#案例：插入到admin表中五条记录
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 
INSERT INTO admin (username,PASSWORD)
VALUES(“j2″,”0000”),(“j3″,”0000”),(“j4″,”0000”),(“j5″,”0000”),(“j1″,”0000”);
END $
调用：
CALL myp1()$

#2.创建带in模式参数的存储过程
#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN 
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;
END $

call myp2(“关晓彤”)$
#案例2：创建存储过程实现，用户是否登录成功
DELIMITER $
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,”成功”,”失败”);

END $

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

#案例1：根据女神名，返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT bName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO bName
FROM boys bo
INNER JOIN beauty b ON b.boyfriend_id = bo.id
WHERE b.name = beautyName;

END$

调用：
DELIMITER $
CALL myp5(“周芷若”,@bName)$
SELECT @bName$

#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回

#二、删除存储过程
语法：drop procedure 存储过程

#三、查看存储过程的信息
show create procedure myp2;

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

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

#一、创建语法
create function 函数名(参数列表) returns 返回类型
begin
函数体
end

/*
注意：
1.参数列表 包含两部分：
参数名 参数类型

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

3.函数体只有一句话，可以省略begin end

4.使用delimiter语句作为设置结束标记

*/

#二、调用语法
select 函数名(参数列表)

#1.无参有返回
#案例1：返回公司的员工个数
CREATE FUNCTION myf1()RETURNS INT 
BEGIN 
DECLARE c INT DEFAULT 0; 
SELECT COUNT(*) INTO c 
FROM employees; RETURN c; 
END$

SELECT myf1()

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

#三、查看函数
show create function 函数名

#四、删除函数
drop function 函数名

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

*/

#一、分支结构
#1.if函数
功能：实现简单的双分支

语法：select if(表达式1,表达式2,表达式3)

执行顺序：
如果表达式一成立，则if函数返回表达式2的值，否则返回表达式3的值

应用：任何地方

#2.case结构

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

语法：
case 变量|表达式|字段
when 要判断的值1 then 返回的值1或语句1;
when 要判断的值2 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;

特点：
①既可以作为表达式，嵌套在其他语句中使用，begin end中或begin end后面
可以作为独立的语句去使用，只能放在begin end中

②如果when中的值满足或条件成立，则执行对应的then后面的语句，并结束case
如果都不满足，则执行else中的语句或值

③else可以省略，如果else省略并且所有when都不满足时，则返回null

#案例：创建存储过程，根据传入成绩，来显示等级，如果传入90-100 A 80-90B 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$

#3.if结构
功能：实现多重分支

语法：
if 条件一 then 语句1;
elseif 条件2 then 语句2;
…
【else 语句n;】
end if;

应用：
在begin end中

#案例：创建存储过程，根据传入成绩，来显示等级，如果传入90-100 A 80-90B 60-80 C 其他D

#二、循环结构
分类：
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`) VALUE (“rose”,”6666″);
SET i = i +1;
END WHILE;
END$
CALL pro_while1(100)$

#添加一个leave语句
#案例：和上一个相同，如果次数》20则停止
DELIMITER $
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username ,`password`) VALUE (CONCAT(“rose”,i),”6666″);
IF i>=20 THEN LEAVE a;
END IF; 
SET i = i +1;
END WHILE a;
END$
CALL pro_while1(100)$