-- 给my_student表增加(自增长)id
#alter table my_studentadd
# idint primary key auto_increment;
-- 给班级表增加主键
alter table my_classadd primary
key(name);
【Empty:没有数据(内容)】
-- 插入数据
insert into my_classvalues(
'Python1907','B408');
insert into my_classvalues(
'Python1907','B408');
-- 错误: 主键冲突
-- 冲突处理
insert into my_classvalues(
'Python1907','B407')
on duplicatekey update
-- 更新教室名
room='B407';
insert into my_classvalues(
'Python1903','B408');
-- 主键冲突: 替换方式
replaceinto my_classvalues(
'Python1903','B406');
replaceinto my_classvalues(
'Python1910','B409');
"""
# *****79_蠕虫复制*****
"""
-- 复制创建表的(结构)
create table my_copylike
my_class;
-- 删除主键
alter table my_copydrop primary
key;
-- 蠕虫复制
insert into my_copyselect *from
my_class;
insert into my_copyselect *from
my_copy;
"""
# *****80_limit_truncate*****
"""
-- 更新部分B406变为A406
update my_copyset room='A406'
where room='B406' limit3;
-- 删除数据: 限制记录数为5
delete from my_copywhere room=
'B409' limit5;
-- 给学生表增加主键
alter table my_student modify id
int primary key auto_increment;
-- 清空表, 重置自增长
truncate my_student;
"""
# *****81_高级查询（select 选项_别名_数据源）*****
"""
-- select 选项
select *from my_copy;
select all *from my_copy;
-- 去重
select distinct *from my_copy;
-- 向学生表插入数据
insert into my_student
values(null,'bc20190001','张三','男'),
(null,'bc20190002','李四','男'),
(null,'bc20190003','王花','女'),
(null,'bc20190004','赵六','男'),
(null,'bc20190005','周七','男');
-- 字段别名
select id,
numberas 学号,
nameas 姓名,
sex 性别
from my_student;
-- 多表数据源查询
select *from my_student,my_class;
-- 子查询
select *from (select *from my_student)as s;
"""
# *****82_where子句*****
"""
-- 增加age年龄和height身高字段
alter table my_studentadd age
tinyint unsigned;
alter table my_studentadd height
tinyint unsigned;
-- 增加字段的值: rand取得一个0-1之间的随机数,
floor向下取整 【update:修改【set:设置】
update my_studentset age=floor(
rand()*20+20), height=floor(rand
()*20+170);
-- 找学生id为1、3、5的学生
select *from my_studentwhere id
=1 || id=3 || id=5;-- 逻辑判断
select *from my_studentwhere id
in(1,3,5);-- in表示在集合中
-- 找出身高在180—190之间的学生
select *from my_studentwhere
height >=180 and height <=190;
-- 【between是闭区间包含180、190】
select *from my_studentwhere
heightbetween 180 and 190;
-- between左边的值必须<=右边的值
select *from my_studentwhere
height beteen190 and 180;-- 错误代码
不成立, 相当于height>=190 and height<=180
-- 所有条件都满足
select *from my_studentwhere 1;
"""
# *****83_group by子句*****
"""
-- 根据性别分组【group by:分组】
select *from my_studentgroup by
sex;-- 错误
-- 分组统计: 身高高矮、平均年龄、总年龄
select sex,count(*),max(height),
min(height),avg(age),sum(age)
from my_studentgroup by sex;
-- 修改id为4的记录, 把年龄设置为null
update my_studentset age=null
where id=4;
select sex,count(*),count(age),
max(height),min(height),avg(age),
sum(age)from my_studentgroup by sex;
-- 修改id为1的记录, 把性别设置为女
update my_studentset sex='女'
where id=1;
-- nan
-- nv
select sex,count(*),count(age),
max(height),min(height),avg(age),
sum(age)from my_studentgroup by
sexdesc;
-- 删除班级表原主键
alter table my_classdrop primary
key;
-- 给班级表增加主键
alter table my_classadd id
int primary key auto_increment;
-- 给学生表增加班级表id【ceil:向上取整】
alter table my_studentadd c_id
int;
update my_studentset c_id=ceil(
rand()*3)
-- 多字段分组: 先班级, 后男女【grop by:分组】
select c_id,sex,count(*)from
my_studentgroup by c_id,sex;
-- 多字段排序
select c_id,sex,count(*),
group_concat(name)from
my_studentgroup by c_id,sex;
-- 多字段排序
-- 统计
select c_id,count(*)from
my_studentgroup by c_id;
-- 回溯统计
select c_id,count(*)from
my_studentgroup by c_idwith
rollup;
-- 多字段分组回溯统计
select c_id,sex,count(*)from
my_studentgroup by c_id,sex;
-- 多字段排序
select c_id,sex,count(*),
group_concat(name)from
my_studentgroup by c_id,sexwith
rollup;
"""
# *****84_having子句*****
"""
*****
能够用where尽量用where,where能保证
有效的信息进入内存,提高内存利用率,
减少内存中出现错误数据的概率,
但是,分组、别名之后的不能where
*****
-- 求出所有班级人数大于等于2的学生人数
select c_id ,count(*)from
my_studentgroup by c_idhaving
count(*)>=2;
select c_id ,count(*)from
my_studentwhere count(*)>=2
group by c_id;-- 错误代码
select c_id ,count(*)as total
from my_studentgroup by c_id
having total>=2;
select c_id ,count(*)as toatl
from my_studentwhere total>=2
group by c_id;-- 错误代码
-- having子句进行条件查询
select nameas 名字,numberas
学号from my_studenthaving 名字
like '张%';
select nameas 名字,numberas
学号from my_studentwhere 名字
like '张%';--错误代码
"""
# *****85_orderby(排序)子句*****
"""
-- 排序
select *from my_studentgroup by
c_id;-- 分组, 为了进行统计
select *from my_studentorder by
c_id;-- 排序
-- 多字段排序: 先班级排序, 后性别排序
select *from my_studentorder by
c_id,sexdesc;
"""
# *****86_limit子句:(分页)*****
"""
-- 查询学生, 前两个
select *from my_student limit2;
-- 记录数是从0开始编号
select *from my_student limit0,
2;
-- 记录数是从2开始编号
select *from my_student limit2,
2;
select *from my_student limit4,
2;
"""
# *****87_连接查询子句*****
"""
-- 更改id为班级表的第一列【chang:切换】
alter table my_class change id
idint first;
-- 1、交叉连接【一般没什么用处】
-- my student cross join my_class是数据源
select *from my_studentcross
join my_class;
-- 2、内连接
select *from my_studentinner
join my_classon my_student.c_id=
my_class.id;
select *from my_studentinner
join my_classon c_id=my_class.id;
-- 错误代码
select *from my_studentinner
join my_classon c_id=id;
-- 字段和表的别名
select s.*,c.nameas c_name,c.
room-- 字段别名
from my_studentas s-- 表的别名
inner join my_classas c
on s.c_id=c.id;
-- 把学生表id为5的记录的c_id设置为null
update my_studentset c_id=null
where id=5;
-- where 代替on【通常不用】
select s.*,c.nameas c_name,c.
room-- 字段别名
from my_studentas s-- 表的别名
inner join my_classas c
where s.c_id=c.id;
-- 3、外连接
-- 左连接
select s.*,c.nameas c_name,c.
room-- 字段别名
from my_studentas s
-- 左表为主表: 最终记录数至少不少于左表已有的记录数
left join my_classas c
on s.c_id=c.id;
-- 右连接
select s.*,c.nameas c_name,c.
room-- 字段别名
from my_studentas s
-- 右表为主表: 最终记录数至少不少于右表已有的记录数
right join my_classas c
on s.c_id=c.id;
select s.*,c.nameas c_name,c.
room-- 字段别名
from my_classas c
right join my_studentas s
on s.c_id=c.id;
-- 【内外连接为常用连接】
-- 4、自然连接
-- 自然内连接
select *from my_studentnatural
join my_class;
-- 修改班级表的name字段名为c_name
alter table my_class change name
c_namevarchar(20)not null;
-- 自然左外连接
select *from my_studentnatural
left join my_class;
-- 外连接模拟自然外连接: using
select *from my_studentleft
join my_classusing(id);
"""
*****88_外键增加_删除_约束*****
"""
-- 创建外键
create table my_foreign1(
idint primary key auto_increment,
namevarchar(20)not null comment
'学生姓名',
c_idint comment'班级id',
-- 增加外键【references:对应】
foreign key(c_id)references
my_class(id)
)charset utf8;
-- 创建表
create table my_foreign2(
idint primary key auto_increment,
namevarchar(20)not null comment
'学生姓名',
-- 普通字段
c_idint comment'班级id'
)charset utf8;
-- 增加外键
alter table my_foreign2add
-- 指定外键名
constraint student_class_1
-- 指定外键字段
foreign key(c_id)
-- 引用父表主键
references my_class(id);
-- 删除外键
alter table my_foreign1
drop foreign key my_foreign1_ibfk_1;
-- 插入数据: 外键字段在父表不存在的话
insert into my_foreign2values(
null,'郭富城',4);-- 没有4号班级
insert into my_foreign2values(
null,'项羽',1);
insert into my_foreign2values(
null,'刘邦',2);
insert into my_foreign2values(
null,'韩信',3);
update my_foreign2set c_id=2
where id=4;
-- 更新父表记录【把id为1的改为4】
update my_classset id=4
where id=1;-- 失败: id=1记录已经被学生引用
update my_classset id=4
where id=3;-- 成功: 没有学生引用此班级
"""
*****89_外键条件_约束模式*****
"""
-- 插入数据
insert into my_foreign1
values(null,'马超',3);
-- 增加外键
-- 失败因为没有3号班
alter table my_foreign1add
foreign key(c_id)references
my_class(id);
-- 创建外联: 指定模式: 删除置空, 更新级联
create table my_foreign3(
idint primary key auto_increment,
namevarchar(20)not null,
c_idint,
-- 增加外键
foreign key(c_id)
-- 引用表
references my_class(id)
-- 指定删除模式
on delete set null
-- 指定更新模式
on update cascade
--
)charset utf8;
-- 插入数据
insert into my_foreign3values(
null,'刘备',1),
(null,'曹操',1),
(null,'孙权',1),
(null,'诸葛亮',2),
(null,'周瑜',2);
-- 解除my_foreign2表的外键
alter table my_foreign2drop
foreign key student_class_1;
-- 更新父表主键
update my_classset id=3
where id=1;
-- 删除父表主键
delete from my_class
where id=2;
"""
*****90_联合查询*****
"""
-- 联合查询
select *from my_class
union -- 默认去重
select *from my_class;
select *from my_class
union all -- 不去重
select *from my_class;
select id,c_name,roomfrom
my_class
union all -- 不去重
select name,number,idfrom
my_student;
-- 需求: 男生升序, 女生降序(年龄)
(select *from my_student
where sex='男'
order by ageasc limit9999999)
union
(select *from my_student
where sex='女'
order by agedesc limit9999999);
"""
*****91_子查询1*****
"""
select *from my_student
where c_id=(
-- 1、标量子查询
-- id一定只有一个值(一行一列)
select idfrom my_classwhere
c_name='Python1903');
insert into my_classvalues(1,
'Python1907','B407');
-- 2、列子查询
select *from my_studentwhere
c_idin(select idfrom my_class);
-- any,some,all
select *from my_studentwhere
c_id=any(select idfrom my_class);
select *from my_studentwhere
c_id=some(select idfrom my_class);
select *from my_studentwhere
c_id=all(select idfrom my_class);
-- 所有结果(null除外)
select *from my_studentwhere
c_id!=any(select idfrom my_class);
-- 所有结果(null除外)
select *from my_studentwhere
c_id!=some(select idfrom my_class);
-- 2号班级(null除外)
select *from my_studentwhere
c_id!=all(select idfrom my_class);
update my_studentset height=197
where name='王花';
select *from my_studentwhere
age=(select max(age)from
my_student)
and
height=(select max(height)from
my_student);
-- 3、行子查询
select *from my_student
-- (age,height)称之为行元素
where (age,height)=(select max(
age),max(height)from my_student);
select *from my_studentorder by
agedesc,heightdesc limit1;
-- 4、表子查询
select *from my_studentgroup by
c_idorder by heightdesc;
-- 每个班选出第一个学生, 再按身高排序
-- 表子查询(每班身高最高的学生)
select *from (select *from
my_studentorder by heightdesc
limit9999999)as studentgroup
by c_id;
"""
*****92_子查询2*****
"""
select exists(select *from
my_student);
select exists(select *from
my_studentwhere id=100);
-- exists子查询
select *from my_studentwhere
exists(select *from my_class
where id=1);-- 是否成立
select *from my_studentwhere
exists(select *from my_class
where id=2);
"""
*****93_视图1*****
"""
-- 视图: 单表+多表
create view my_v1as
select *from my_student;
create view my_v2as
select *from my_class;
create view my_v3as
select *from my_studentas s
left join my_classas con s.c_id
=c.id;-- id重复
-- 多表视图
create view my_v3as
select s.*,c.c_name,c.room
from my_studentas s
left join my_classas c
on s.c_id=c.id;
-- 查看视图创建语句
showcreate view my_v3\G
-- 视图使用
select *from my_v1;
select *from my_v2;
select *from my_v3;
-- 修改视图
alter view my_v1as
select id,name,age,sex,
height,c_id
from my_student;
-- 创建视图
create view my_v4as select *
from my_student;
-- 删除视图
drop view my_v4;
-- 多表视图插入数据
insert into my_v3
values(null,'bc20190006','张三丰'
,'男',150,180,1,'Python1907',
'B407');-- 错误
-- 将学生表的学号字段设置成不允许为空
alter table my_student modify
numberchar(10)not null unique;
-- 单表视图插入数据: 视图不包含所有不
-- 允许为空的字段
insert into my_v1
values(null,'张三丰',150,'男',180
,1);-- 失败(学号不允许为空)
"""
*****94_视图2*****
"""
-- 单表视图插入数据
insert into my_v2
values(2,'Python1811','B410');
-- 多表视图删除数据【连接的视图不能删除】
delete from my_v3where id=1;
-- 单表视图删除数据
delete from my_v2where id=4;
-- 多表视图更新数据【不能更新】
update my_v3set c_id=3 where id=
5;
-- 视图: age字段限制更新
create view my_v4as
select *from my_studentwhere
age>30 with check option;
-- 表示视图的数据来源都是年龄大于30岁
-- ,是由where age>30决定的
-- with check
-- option决定通过视图更新的时候, 不能
-- 将已经得到的数据age>30的改成<30的
-- 将视图可以查到的数据改成年龄小于30
update my_v4set age=29 where id=
3;-- 不可修改
-- 可以修改数据: 可以改, 但是视图查不到
update my_v4set age=32 where id=
2;
-- 获取所有班级中最高的一个学生
create view my_v5as
select *from my_studentorder by
heightdesc;
select *from my_v5group by c_id;
select *from my_studentgroup by
c_idorder by heightdesc;
-- 指定算法为临时表算法
create algorithm=temptableview
my_v6as select *from my_student
order by heightdesc;
select *from my_v6group by c_id;
"""
*****95_单表数据备份还原*****
"""
-- 查看MySQL的版本
select @@version;
-- 创建mysam表【engine=myisam:指定存储引擎】
create table my_myisam(
idint
)charset utf8 engine=myisam;
-- 向my_myisam表插入几条记录
insert into my_myisamvalues(1),(
2),(3);
-- 单表数据备份
-- *****只能备份数据不能备份表*****
select *into outfile
'E:/1907_web/student.txt'
from my_student;
select *into outfile
'E:/1907_web/class.txt'
from my_class;
-- ERROR 1290 (HY000):The
-- MySQL server is running with
-- the --secure-file-priv option
-- so it cannot execute this
-- statement
-- 找到 secure_file_priv
-- C:\ProgramData\MySQL\MySQLServer 5.7\my.ini
-- 打开my文件, ctrl+f找到关键字secure,修改路径:
-- 把原路径用#注释掉, 改为:
-- secure-file-priv=""
-- 需要重启MySQL服务器并重新登录认证
-- 指定备份处理方式
select *into outfile
'E:/1907_web/class1.txt'
-- 字段处理
fields
-- 数据使用双引号包裹
enclosedby '"'
-- 使用竖线分隔字段数据
terminatedby '|'
-- 行处理
lines
startingby 'START:'
from my_class;
delete from my_class;
-- 还原数据
load data infile
'E:/1907_web/class1.txt'
into table my_class
-- 字段处理
fields
-- 数据使用双引号包裹
enclosedby '"'
-- 使用竖线分隔字段数据
terminatedby '|'
-- 行处理
lines
startingby 'START:';
"""
*****96_多表整库备份还原*****
"""
-- SQL备份!!!注释不能带入命令中!!!
-- 在C:\Users\刘臻> 下执行此命令
mysqldump -uroot -p1234567 mydatabase my_student > E:/1907_web/student.sql
-- 整库备份
mysqldump -uroot -p1234567 mydatabase > E:/1907_web/mydatabase.sql
-- 还原数据: mysql客户端还原
mysql -uroot -p1234567 mydatabase < E:/1907_web/student.sql
-- SQL指令还原SQL备份【可以使用备份的结构】
source E:/1907_web/student.sql;
"""
*****97_事务1*****
"""
-- 【事务操作是(只针)对数据操作】
-- 创建一个账户表【unique:不可以重复】default:默认值】
create table my_account(
idint primary key auto_increment,
numberchar(16)not null unique
comment'账户',
namevarchar(20)not null,
moneydecimal(10,2)default 0.0
comment'账户余额'
)charset utf8;
-- 插入数据
insert into my_accountvalues
(null,'00000001','张三',1000),
(null,'00000002','李四',2000);
-- 张三转账1000元给李四
update my_accountset money=money
-1000 where id=1;
-- 事务安全
-- 开启事务
starttransaction;
-- 事务操作: 1、李四账户减少
update my_accountset money=money
-1000 where id=2;
-- 事务操作: 2、张三账户增加
update my_accountset money=money
+1000 where id=1;
-- 提交事务
commit;
-- 回滚点操作
-- 开启事务
starttransaction;
-- 事务处理1:张三发工资了, 加钱
update my_accountset money=money
+10000 where id=1;
-- 设置回滚点
savepoint spl;
-- 银行扣税
update my_accountset money=money
-10000*0.05 where id=2;-- 错误
-- 回滚到回滚点
rollback to spl;
-- 继续操作
update my_accountset money=
money-10000*0.05 where id=1;
-- 查看结果
select *from my_account;
-- 提交结果
commit;
-- 显示系统变量autocommit(模糊查询)
show variableslike 'autocommit';
-- 关闭事务自动提交
set autocommit=0;
-- 给李四发工资
update my_accountset money=
money+10000 where id=2;
commit;
update my_accountset money=
money-10000*0.05 where id=2;
"""
*****98_事务2*****
"""
-- 事务的隔离性
starttransaction;
-- 给张三返税, 返500元
update my_accountset money=
money+500 where id=1;
select *from my_account;
-- 另外窗口开启事务
starttransaction;
-- 李四淘宝消费500元
update my_accountset money=
money-500where id=2;
select *from my_account;
commit;
select *from my_account;
-- 回到原张三窗口, 事务回滚
rollback;
select *from my_account;
-- 两边一致
-- 锁机制
starttransaction;
-- 使用非索引字段(name), 行锁自动上升为表锁
update my_accountset money=
money+500 where name='张三';
update my_accountset money=
money+1000 where id=2;
"""
