MySQL入门归档
程序员文章站
2022-07-08 10:19:17
文章目录一、数据库简介二、MySQL操作说明1. 服务启停2. MySQL登录和退出3. MySQL的常见命令4. MySQL的语法规范5. SQL的语言分类三、DQL语言1. 机读顺数2. 条件查询3. 排序查询4. 分页查询四、常见函数1. 单行函数1.1 字符函数1.2 数学函数1.3 日期函数1.4 流程控制函数1.5 其他函数五、分组1. 分组函数2. 分组查询六、多表查询1. 连接查询2. 子查询3. 联合查询七、DML语言1. 插入2. 修改3. 删除八、DDL语句1. 库的管理2. 表的管理...
文章目录
一、数据库简介
1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5、表中的数据是按行存储的，每一行类似于java中的“对象”。
二、MySQL操作说明
1. 服务启停
# windows
net start 服务名（启动服务）
net stop 服务名（停止服务）
# linux
systemctl start mysql
systemctl stop mysql
2. MySQL登录和退出
- 通过mysql自带的客户端 :只限于root用户
- mysql 【-h主机名 -P端口号 】-u用户名 -p密码
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的语法规范
- 不区分大小写,但建议关键字大写，表名、列名小写
- 单行注释：#注释文字
- 单行注释：-- 注释文字
- 多行注释：/* 注释文字 */
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
三、DQL语言
1. 机读顺数
select 查询列表 ⑦
distinct 去重 ⑧
from 表1 别名 ①
连接类型 join 表2 ③
on 连接条件 ②
where 筛选 ④
group by 分组列表 ⑤ 支持select别名
having 筛选 ⑥ 支持select别名
order by 排序列表 ⑨ 支持select别名
limit 起始条目索引，条目数; ⑩
特点：
①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在
② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数
2. 条件查询
条件查询：根据条件过滤原始表的数据，查询到想要的数据
分类：
一、条件表达式
示例：salary>10000
条件运算符：
> < >= <= = != <>
二、逻辑表达式
示例：salary>10000 && salary<20000
逻辑运算符：
and（&&）:两个条件如果同时成立，结果为true，否则为false
or(||)：两个条件只要有一个成立，结果为true，否则为false
not(!)：如果条件成立，则not后为false，否则为true
三、模糊查询
示例：last_name like 'a%'
3. 排序查询
select
要查询的东西
from
表
where
条件
order by 排序的字段|表达式|函数|别名 【asc|desc】
4. 分页查询
应用场景：
实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：
select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;
特点：
1.起始条目索引从0开始
2.limit子句放在查询语句的最后
3.公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page
四、常见函数
1. 单行函数
1.1 字符函数
CONCAT(S1,S2,......,Sn) --连接S1,S2,......,Sn为一个字符串
CONCAT(s, S1,S2,......,Sn) --同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上s
char_length('agg哈哈') --获取字符个数
length('aabbcc') --获取字节个数，和字符集有关
instr('aaabbccbbcc','cc') -- 返回第一次出现cc子串的索引，从1起始
INSERT(str, index , len, instr) --将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) --或 UCASE(s) 将字符串s的所有字母转成大写字母
LOWER(s) --或LCASE(s) 将字符串s的所有字母转成小写字母
LEFT(s,n) --返回字符串s最左边的n个字符
RIGHT(s,n) --返回字符串s最右边的n个字符
LPAD(str, len, pad) --用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad) --用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s) --去掉字符串s左侧的空格
RTRIM(s) --去掉字符串s右侧的空格
TRIM(s) --去掉字符串s开始与结尾的空格
TRIM(【BOTH】s1 FROM s) --去掉字符串s开始与结尾的s1 默认是both
TRIM(【LEADING】s1 FROM s) --去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s) --去掉字符串s结尾处的s1
REPEAT(str, n) --返回str重复n次的结果
REPLACE（str, a, b） --用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2) --比较字符串s1,s2 字典序 -1 0 1
SUBSTRING(s,index,len) --返回从字符串s的index位置其len个字符
substr('abc',1,3) --截取子串上面的简略写法 索引从1开始，3为截取长度，不是截取到第几个
1.2 数学函数
ABS（x） --返回x的绝对值
CEIL（x） --返回大于x的最小整数值 向上取整 1.09 ==》 2
FLOOR（x） --返回大于x的最大整数值
MOD(x,y) --返回x/y的模 取模公式 x%y = x-x/y*y
RAND(x) --返回0~1的随机值
ROUND(x,y) --返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y) --返回数字x截断为y位小数的结果
SQRT(x) --返回x的平方根
POW(x,y) --返回x的y次方
1.3 日期函数
-- 常用日期函数
NOW() --返回当前时间
CURDATE() 或 CURRENT_DATE() --返回当前日期
DATEDIFF(date1,date2) -- 日期相减 得到天数
select datediff('1970-1-1','2020-5-7') -- -18389天 返回time1 - time2的时间间隔
DATE_FORMAT(datetime ,fmt) --按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt) --按照fmt格式对str进行解析，解析为一个日期
--返回当前时间
CURTIME() 或 CURRENT_TIME()
-- 返回当前系统日期时间
SYSDATE()
CURRENT_TIMESTAMP()
LOCALTIME()
LOCALTIMESTAMP()
--返回具体的时间值
YEAR(date)
MONTH(date)
DAY(date)
HOUR(time)
MINUTE(time)
SECOND(time)
-- 返回一年中的第几周
WEEK(date)
WEEKOFYEAR(date)
DAYOFWEEK() 返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date) 返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date) 返回星期：MONDAY,TUESDAY.....SUNDAY
MONTHNAME(date) 返回月份：January，。。。。。
TIMEDIFF(time1, time2) 返回date1 - date2的日期间隔
DATE_ADD(datetime, INTERVALE expr type) 返回与给定日期时间相差INTERVAL时间段的日期时间
| 格式符 | 说明 | 格式符 | 说明 |
|---|---|---|---|
| %Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03。。。） |
| %b | 缩写的月名（Jan.，Feb.，…） | %c | 数字表示月份（1,2,3,…） |
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示月中的天数(01,02…) |
| %e | 数字形式表示月中的天数（1,2,3,4,5…） | ||
| %H | 两位数字表示小数，24小时制（01,02…） | %h和%I | 两位数字表示小时，12小时制（01,02…） |
| %k | 数字形式的小时，24小时制(1,2,3) | %l | 数字形式表示小时，12小时制（1,2,3,4…） |
| %i | 两位数字表示分钟（00,01,02） | %S和%s | 两位数字表示秒(00,01,02…) |
| %W | 一周中的星期名称（Sunday…） | %a | 一周中的星期缩写（Sun.，Mon.,Tues.，…） |
| %w | 以数字表示周中的天数(0=Sunday,1=Monday…) | ||
| %j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 |
| %u | 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 | ||
| %T | 24小时制 | %r | 12小时制 |
| %p | AM或PM | %% | 表示% |
1.4 流程控制函数
if(100>9,'对','错') --处理双分支
IFNULL(value1, value2) --如果value1不为空，返回value1，否则返回value2
-- case函数
-- 使用一： switch语句
case 表达式
when 值1 then 结果1
when 值2 then 结果2
....
else 结果n
end
-- 使用二：
case
when 条件1 then 结果1
when 条件2then 结果2
....
else 结果n
end
1.5 其他函数
version 版本
database 当前库
user 当前连接用户
五、分组
1. 分组函数
sum --求和
max --最大值
min --最小值
avg --平均值
count --计数 统计指定列不为NULL的记录行数；
-
特点：
1、以上五个分组函数都忽略null值，除了count(*)
2、sum和avg一般用于处理数值型 ；max、min、count可以处理任何数据类型
3、都可以搭配distinct(某一列)使用，用于统计去重后的结果
4、count的参数可以支持：字段、*、常量值，一般放1 ，建议使用 count(*)
2. 分组查询
语法：
select 查询的字段，分组函数
from 表
group by 分组的字段
特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
| 针对的表 | 位置 | 关键字 |
|---|---|---|
| 分组前筛选 | 原始表 | group by的前面 |
| 分组后筛选 | 分组后的结果集 | group by的后面 |
4、可以按多个字段分组，字段之间用逗号隔开 ，没有顺序要求
5、可以支持排序
6、having后可以支持别名
六、多表查询
1. 连接查询
传统模式下的连接 ：等值连接——非等值连接
- 等值连接的结果 = 多个表的交集
- n表连接，至少需要n-1个连接条件
- 多个表不分主次，没有顺序要求
- 一般为表起别名，提高阅读性和性能
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ybY7AAIh-1594858963827)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200507105251024.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jPVSxKI1-1594858963831)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200507105225329.png)]
注意第二张图的差集部分的条件
二、sql99语法：通过join关键字实现连接
含义：1999年推出的sql语法
支持：
等值连接、非等值连接 （内连接）
外连接
交叉连接
语法：
select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on 连接条件
【inner|left outer|right outer|cross】join 表3 on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】
好处：语句上，连接条件和筛选条件实现了分离，简洁明了！
三、自连接
案例：查询员工名和直接上级的名称
sql99
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;
sql92
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
2. 子查询
子查询位置：
- 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
按子查询出现的位置：
1. select后面：仅支持标量子查询
-- 案例：查询每个部门的员工个数
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) 个数
FROM departments d;
2. from后面：支持表子查询
--将子查询结果充当一张表，要求必须起别名
--案例：查询每个部门的平均工资的工资等级
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;
3. where或having后面：★
标量子查询（单行） √
列子查询 （多行） √
行子查询
3. exists后面（相关子查询） -- 判断查询结果是否有值
exists(完整的查询语句)
--案例1：查询有员工的部门名 可以使用exists也可以使用in代替
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
-
按结果集的行列数不同：
- 标量子查询（一行一列，一个结果）
- 列子查询（一列多行）
- 行子查询（一行多列）
- 表子查询（任意行任意列）
-
单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空 -
多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替
3. 联合查询
- 如果有关联键，联合查询一般可以用多表联结加or代替
- 要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
语法：
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】
特点：
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重，union all代表不去重
七、DML语言
1. 插入
语法：
insert into 表名(字段名，...)
values(值1，...),(值1，...);
-- 把查询的结果插入一个新表
insert into table_name
select id,name
from test
where id<20;
特点：
1、字段类型和值类型一致或兼容，而且一一对应
2、可以为空的字段，可以不用插入值，或用null填充
3、不可以为空的字段，必须插入值
4、字段个数和值的个数必须一致
5、字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致
2. 修改
修改单表语法：
update 表名
set 字段=新值,字段=新值
【where 条件】
修改多表的记录(级联更新)
-- sql92语法：
update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;
-- sql99语法：
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;
-- 示例 修改没有男朋友的女神的男朋友编号都为2号
UPDATE beauty be
LEFT OUTER JOIN boys bo ON be.boyfriend_id = bo.id
SET be.boyfriend_id = 2
WHERE bo.id IS NULL;
3. 删除
方式1：delete语句
-- 单表的删除：
delete from 表名
[where 筛选条件]
[limit 条目数]
-- 多表的删除(级联删除)
-- delete后面指定删除的表：
-- delete 表1的别名 代表只删除满足条件的表1的记录，
-- delete 表1的别名,表2的别名 代表条件满足两个表的记录都删除
--sql92语法：
delete [表1的别名],[表2的别名]
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;
--sql99语法：
delete [表1的别名],[表2的别名]
from 表1 别名 inner|left|right join 表2 别名
on 连接条件
where 筛选条件;
方式2：truncate语句
truncate table 表名
两种方式的区别【面试题】
1.truncate不能加where条件，而delete可以加where条件
2.truncate的效率高一丢丢
3.truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
4.truncate 删除不能回滚，delete删除可以回滚
八、DDL语句
1. 库的管理
库的管理：
一、创建库
create database 库名
二、删除库
drop database 库名
2. 表的管理
-- 1.创建表
CREATE TABLE IF NOT EXISTS stuinfo(
stuId INT,
stuName VARCHAR(20),
gender CHAR,
bornDate DATETIME
);
-- 更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
2.修改表 alter
语法：ALTER TABLE 表名 ADD|MODIFY|DROP|CHANGE OLUMN 字段名 【字段类型】;
①修改字段名alter table 表名 change column 旧列名 新列名 [类型];
②修改表名ALTER TABLE 表名 RENAME [TO] studentinfo;
③修改字段类型和列级约束ALTER TABLE 表名 MODIFY COLUMN borndate DATE ;
④添加字段ALTER TABLE 表名 ADD COLUMN email VARCHAR(20) [first|after 字段名];
⑤删除字段ALTER TABLE 表名 DROP COLUMN email;
3.删除表DROP TABLE [IF EXISTS] 表名;
3. 表的复制
- 复制可以跨库，格式：库.表名
--1.仅复制表的结构
CREATE TABLE 新表名 LIKE 旧表名;
--2.复制表的结构+数据
CREATE TABLE copy2
SELECT * FROM author;
--只复制部分数据
CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';
--仅复制某些字段
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;
4. 常见数据类型
数字类型
--整型:
tinyint (0~255/-128~127)
smallint (0~65535/-32768~32767)
mediumint
int
bigint (参考手册11.2)
--参数解释:
unsigned 无符号(不能为负) 强行插入负数为0
zerofill 0填充 M 填充后的宽度
举例:
tinyint unsigned;
tinyint(6) zerofill;
--数值型
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(M,D)
--格式:float(M,D) unsigned\zerofill;
M：整数部位+小数部位
D：小数部位，决定保留几位小数 如果小数部分超出则四舍五入保留D位
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
整数和浮点：如果超过范围，则插入临界值
特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！
-- 串与数字之间自动转换
create TABLE num_test(
n1 int,
n2 FLOAT,
n3 DOUBLE
);
INSERT into num_test
VALUES
('123','123','123'),
('123.1','123.12','123.12');
-- 结果：
n1 n2 n3
123 123 123
123 123.12 123.12
字符型
char(m) 定长 m默认1
varchar(m) 变长 必须指定m
text
列 实存字符i 实占空间 利用率
char(M) 0<=i<=M M i/m<=100%
varchar(M) 0<=i<=M i+1,2 i/i+1/2<100%
--binary和varbinary用于保存较短的二进制
--较长的文本：
text
blob(较大的二进制)
enum枚举
-- 限制插入内容，实例只能插入abc，不区分大小写
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集合
-- 存储范围0-64个成员 与enum类似，区别：可以一次插入多，个不区分大小写
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');
日期时间类型:
date只保存日期
time 只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间 受时区和数据库版本影响插入的数据会根据时区而变化
year YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)
date YYYY-MM-DD 如:2010-03-14
time HH:MM:SS 如:19:26:32
datetime YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32
timestamp YYYY-MM-DD HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间
字节 范围 时区等的影响
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;
SHOW VARIABLES LIKE 'time_zoe';
SET time_zone='+9:00';
5. 约束及其操作
NOT NULL：非空，用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认，用于保证该字段有默认值
比如性别
PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
比如学号、员工编号等
UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束，用于引用主表中某列的值
比如学生表的专业编号，员工表的部门编号，员工表的工种编号
-
约束的添加分类：
列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空、默认，其他的都支持 -
查看stuinfo中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM stuinfo;
表级约束
【constraint 约束名】 约束类型(字段名)
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20),
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
修改表时添加约束
1、添加列级约束alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
示例：
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorid INT
)
DESC stuinfo;
#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 stuinfo ADD PRIMARY KEY(id);
#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);
#三、修改表时删除约束
#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 fk_stuinfo_major;
SHOW INDEX FROM stuinfo;
自增长列
- 标识列必是一个key(唯一，主键)
- 一个表只能有一个自增列
- 标识列的类型只能是数值型(int ,double ···)
- 标识列可以通过 SET auto_increment_increment=3;设置步长可以通过 手动插入值，设置起始值
- 查看自增信息：
SHOW VARIABLES LIKE '%auto_increment%';
6. 外键级联操作
级联删除： 删除主表同时删除外键约束的从表的字段
-- 定义外键时，声明级联删除
-- 删除主表的字段时，连着从表被约束的字段一起删除
ALTER TABLE 从表 add CONSISTENT fk_从_主 FOREIGN key (从表被约束字段) REFERENCES 主表(约束字段) ON DELETE CASCADE;
级联置空：删除主表同时置空外键约束的从表的字段
ALTER TABLE 从表 add CONSISTENT fk_从_主 FOREIGN key (从表被约束字段) REFERENCES 主表(约束字段) ON DELETE SET NULL;
九、TCL数据库事务
含义：通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态
1. 特点（ACID）
- 原子性：要么都执行，要么都回滚
- 一致性：保证数据的状态操作前和操作后保持一致 如：转账人和被转账人的钱数总和，转账前后都是一样的，数据还是准确可靠的。
- 隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
- 持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改
相关步骤：
1、开启事务
2、编写事务的一组逻辑操作单元（多条sql语句）
3、提交事务或回滚事务
2. 事务的分类：
- 隐式事务，没有明显的开启和结束事务的标志,insert、update、delete语句本身就是一个事务
- 显式事务，具有明显的开启和结束事务的标志
- 开启事务
取消自动提交事务的功能 - 编写事务的一组逻辑操作单元（多条sql语句）
insert
update
delete
3、提交事务或回滚事务
3. 事务语法
-- 查看事务是否开启
SHOW VARIABLES LIKE 'autocommit';
-- 开启事务
set autocommit=0; --默认开启事务，当前会话有效
start transaction; --可选
-- 提交与回滚
commit;
rollback;
savepoint 断点
commit to 断点
rollback to 断点
--演示savepoint 的使用
SET autocommit=0;
START TRANSACTION;
DELETE FROM account WHERE id=25;
SAVEPOINT a;#设置保存点
DELETE FROM account WHERE id=28;
ROLLBACK TO a;#回滚到保存点
4. 事务隔离级别
在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题
-
丢失修改：T1和T2两个事务都对一-个数据进行修改, T1 先修改，T2 随后修改，T2的修改覆盖了T1的修改。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-m6K3R2zT-1594858963836)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200508221041046.png)]
-
脏读：(读到回滚数据)T1修改一个数据，T2 随后读取这个数据。如果T1撤销了这次修改,那么T2读取的数据是脏数据。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aPiHS6HW-1594858963839)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200508221111259.png)]
- 不可重复读：(两次查询结果不一致)T2读取一个数据，T1 对该数据做了修改。如果T2再次读取这个数据，此时读取的结果和第一次读取的结果不同。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Uws9VXj5-1594858963841)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200508220918769.png)]
- 幻读：(一般针对插入)T1读取某个范围的数据, T2在这个范围内插入新的数据，T1再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qm8FeAMs-1594858963843)(MySQL%E5%9F%BA%E7%A1%80.assets/image-20200508221011223.png)]
| 事务的隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| read uncommitted | √ | √ | √ |
| read committed | X | √ | √ |
| repeatable read | X | X | √ |
| serializable | X | X | X |
设置隔离级别：
set session|global transaction isolation level 隔离级别名;
查看隔离级别：
select @@tx_isolation; -- 5.x
select @@transaction_isolation --8.0
十、视图
含义：理解成一张虚拟的表
- 视图和表的区别：
| 使用方式 | 占用物理空 | |
|---|---|---|
| 视图 | 完全相同 | 不占用，仅仅保存的是sql逻辑 |
| 表 | 完全相同 | 占用 |
- 视图的好处：
1、sql语句提高重用性，效率高
2、和表实现了分离，提高了安全性
1. 视图的创建
CREATE VIEW 视图名
AS
DQL语句;
2. 视图的增删改查
1、查看视图的数据
和查询表一样
2、插入视图的数据
INSERT INTO my_v4(last_name,department_id) VALUES('虚竹',90);
3、修改视图的数据
UPDATE my_v4 SET last_name ='梦姑' WHERE last_name='虚竹';
4、删除视图的数据
DELETE FROM my_v4;
3. 不能更新的视图
-
包含以下关键字的sql语句：
- 分组函数、distinct、group by、having、union或者union all
- 常量视图
- Select中包含子查询
- join
- from一个不能更新的视图
- where子句的子查询引用了from子句中的表
-- where子句的子查询引用了from子句中的表 示例： -- from表和子查询的from表来自同一个表 CREATE OR REPLACE VIEW myv6 AS SELECT last_name,email,salary FROM employees WHERE employee_id IN( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL ); #更新 SELECT * FROM myv6; UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
4. 视图逻辑的更新
方式一：
CREATE OR REPLACE VIEW test_v7
AS
SELECT last_name FROM employees
WHERE employee_id>100;
方式二:
ALTER VIEW test_v7
AS
SELECT employee_id FROM employees;
SELECT * FROM test_v7;
5. 删除视图
DROP VIEW test_v1,test_v2,test_v3;
6. 视图结构的查看
DESC test_v7;
SHOW CREATE VIEW test_v7;
十一、变量
@表示局部变量: 用户自定义的变量
**@@表示全局系统变量变量: 用户不可以自定义，可以修改 **
1. 系统变量
一、全局变量
作用域：针对于所有会话（连接）有效，但不能跨重启
--查看所有全局变量
SHOW GLOBAL VARIABLES;
--查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
--查看指定的系统变量的值
SELECT @@global.autocommit;
--为某个系统变量赋值 不跨重启
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
二、会话变量
作用域：针对于当前会话（连接）有效
--查看所有会话变量
SHOW SESSION VARIABLES;
--查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
--查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
--为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';
2. 自定义变量
一、用户变量
- 会话有效
- 弱类型
- 变量名前必须加@
声明、初始化并赋值一起操作 ：
#赋值操作符：=或:=
#①声明并初始化
SET @变量名=值;
SET @变量名:=值; --推荐
SELECT @变量名:=值;
--方式二：将表中查询的结果赋给一个变量，注意必须是标量查询
SELECT 字段名或表达式 INTO @变量名,@变量名,·····
FROM 表;
-- 示例
SELECT COUNT(*) INTO @num
from employees;
使用：
select @变量名;
二、存储过程中的局部变量
- 仅在定义它的begin end块中有效
- 应用在 begin end中的第一句话
- 声明和赋值分开，不用@起始
声明：
declare 变量名 类型 [default 值];
赋值：
DELIMITER $
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
DECLARE 局部变量名 数据类型 DEFAULT 0; -- 声明并初始化
存储过程体（一组合法的SQL语句）;
END $
使用：
DELIMITER $
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
····
select 变量名
END $
二者的区别：
| 作用域 | 定义位置 | 语法 | |
|---|---|---|---|
| 用户变量 | 当前会话 | 会话的任何地方 | 加@符号，不用指定类型 |
| 局部变量 | 定义它的BEGIN END中 | BEGIN END的第一句话 | 一般不用加@,需要指定类型 |
十二、存储过程
含义：一组经过预先编译的sql语句的集合
-
优点：
提高了sql语句的重用性，减少了开发程序员的压力
提高了效率
减少了传输次数
参数模式：
- in：该参数可以作为输入，也就是该参数需要调用方传入值
- out：该参数可以作为输出，也就是该参数可以作为返回值
- inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要入值，又可以返回值
1. 创建存储过程
create procedure 存储过程名(in|out|inout 参数名 参数类型,...)
begin
存储过程体;
end
- 如果存储过程体仅仅只有一句话，begin end可以省略
- 存储过程体中的每条sql语句的结尾要求必须加分号。
- 存储过程的结尾可以使用 delimiter (定界符)重新设置
-- 定界符使用示例
delimiter $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
sql语句1;
sql语句2;
END $
--调用存储过程
call 存储过程名(实参列表)
2. 删除存储过程
-- sql#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;
3. 查看存储过程的信息
DESC myp2; -- 不支持此方式
SHOW CREATE PROCEDURE 存储过程名;
4. 存储过程示例
--案例2：根据输入的女神名，返回对应的男神名和魅力值
delimiter $
CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT usercp INT)
BEGIN
SELECT boys.boyname ,boys.usercp INTO boyname,usercp
FROM boys
RIGHT JOIN
beauty b ON b.boyfriend_id = boys.id
WHERE b.name=beautyName ;
END $
#调用
CALL myp7('小昭',@name,@cp)$
SELECT @name,@cp$
--4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回
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)$
SELECT @m,@n$
十三、函数
1. 语法
CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
函数体
RETURN 变量;
END
注意： 函数必须有返回值，如不需要返回值用存储过程
1.参数列表 包含两部分：
参数名 参数类型
2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议
return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记
2. 操作
-
查看函数：
SHOW CREATE FUNCTION myf3; -
删除函数：
DROP FUNCTION myf3; -
调用函数：
SELECT 函数名（实参列表）
3. 示例
-- 1.无参有返回
# 案例：返回公司的员工个数
delimiter $
CREATE FUNCTION myf1() RETURNS INT -- 注意返回值
BEGIN
DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c#赋值
FROM employees;
RETURN c; -- 注意return
END $
SELECT myf1()$
-- 2.有参有返回
#案例1：根据员工名，返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal=0; -- 定义用户变量 非局部变量也可以使用
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name = empName;
RETURN @sal; -- 注意返回
END $
4. 函数和存储过程的区别
| 关键字 | 调用语法 | 返回值 | 应用场景 |
|---|---|---|---|
| 函数 FUNCTION | SELECT 函数() | 只能是一个 | 一般用于查询©结果为一个值并返回时，当有返回值而且仅仅一个 |
| 存储过程 PROCEDURE | CALL 存储过程() | 可以有0个或多个 | 一般用于更新 (RUD) |
十四、流程控制结构
1. 分支
一、if函数
语法：if(条件，值1，值2)
特点：可以用在任何位置
二、case语句
-- 情况一：类似于switch
case 表达式
when 值1 then 结果1或语句1(如果是语句，需要加分号)
when 值2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）
-- 情况二：类似于多重if
case
when 条件1 then 结果1或语句1(如果是语句，需要加分号)
when 条件2 then 结果2或语句2(如果是语句，需要加分号)
...
else 结果n或语句n(如果是语句，需要加分号)
end 【case】（如果是放在begin end中需要加上case，如果放在select后面不需要）
三、if elseif语句 只能~用在begin end中
if 情况1 then 语句1;
elseif 情况2 then 语句2;
...
else 语句n;
end if;
示例
-- 案例1：独立使用if-else
-- 创建函数，实现传入成绩，返回评级
delimiter $
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
IF score>90 THEN SET ch='A';
ELSEIF score>80 THEN SET ch='B';
ELSEIF score>60 THEN SET ch='C';
ELSE SET ch='D';
END IF; --注意 结尾加end if
RETURN ch;
END $
-- 案例2：独立使用CASE-WHEN
--创建函数，实现传入成绩，返回评级
CREATE FUNCTION test_case(score FLOAT) RETURNS CHAR
BEGIN
DECLARE ch CHAR DEFAULT 'A';
CASE
WHEN score>90 THEN SET ch='A';
WHEN score>80 THEN SET ch='B';
WHEN score>60 THEN SET ch='C';
ELSE SET ch='D';
END CASE; --注意 在存储过程或函数中分支结构结尾加case
RETURN ch;
END $
SELECT test_case(56)$
三者比较：
| 应用场合 | |
|---|---|
| if函数 | 简单双分支 |
| case结构 | 等值判断 的多分支 |
| if结构 | 区间判断 的多分支 |
2. 循环
- 只能放在BEGIN END里面
分类：
while、loop、repeat
循环控制：
- iterate类似于 continue，继续，结束本次循环，继续下一次
- leave 类似于 break，跳出，结束当前所在的循环
语法：
-- while 循环
[标签:]while 循环条件 do
循环体
end while [标签:];
-- loop循环 没有结束判断，需要配合leave跳出
[标签:]loop
循环体;
end loop [标签:];
-- repeat 循环
[标签:]repeat
循环体;
until 结束循环的条件
end repeat [标签:];
特点：
- 如果要搭配leave跳转语句，需要使用标签，否则可以不用标签
3. 示例
-- 1.没有添加循环控制语句
#案例：批量插入，根据次数插入到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;
END WHILE;
END $
CALL pro_while1(100)$
-- 2.添加leave语句
#案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 1;
a:WHILE i<=insertCount DO
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
IF i>=20 THEN LEAVE a;
END IF;
SET i=i+1;
END WHILE a;
END $
CALL test_while1(100)$
-- 3.添加iterate语句
#案例：批量插入，根据次数插入到admin表中多条记录，只插入偶数次
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
DECLARE i INT DEFAULT 0;
a:WHILE i<=insertCount DO
SET i=i+1;
IF MOD(i,2)!=0 THEN ITERATE a;
END IF;
INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
END WHILE a;
END $
CALL test_while1(100)$
十五、索引
概念
-
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
-
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
-
mysql索引底层的数据结构是B+树
1. 索引分类
1.1 单值索引
- 即一个索引只包含单个列，一个表可以有多个单列索引
随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name);
删除索引：
DROP INDEX idx_customer_name on customer;
1.2 唯一索引
- 索引列的值必须唯一，但允许有空值
随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no)
);
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);
1.3 主键索引
- 设定为主键后数据库会自动建立索引，innodb为聚簇索引
- 新表可以任意更改主键，使用长的旧表不推荐修改，可能有很多其他表进行外键约束
随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id)
);
单独建主键索引：
ALTER TABLE customer
add PRIMARY KEY customer(customer_no);
删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引
1.4 复合索引
- 即一个索引包含多个列
- 复合索引有顺序
随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED AUTO_INCREMENT ,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no,customer_name)
);
单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);
本文地址：https://blog.csdn.net/qq_39711439/article/details/107374902