Mysql 基础
以最常见的学生表查询为基础
数据库与测试的关系
- 检查界面可见输入数据存储
- 检查界面不可见数据存储
- 检查是否符合数据库事务的一致性
聚合函数
- max(字段)：求该字段的最大值
- min(字段)：求该字段的最小值
- avg(字段)：求该字段的平均值
- sum(字段)：对该字段的值求和
- count(*)：统计有多少行、条、个
建表
create table 表名(
字段名 字段类型 约束,
字段名 字段类型 约束
);
插入数据
insert into 表名(字段,字段)
values(值,值);
修改
update 表
set 字段=值,字段=值
where 条件;
删除
delete from cjb; # 按条件逐行删除
truncate table cjb; # 直接删除整张表里的数据，不支持按条件删除
排序
order by 字段 asc # 升序，为默认排序，可以不写
order by 字段 desc # 降序
查询某个区间
limit m,n # 从m+1行开始，连续展示n行
查询去重
select distinct 字段
等值连接
# 2张表
select *
from A,C
where A.主键=C.外键 and 其他条件;
# 3张表
select *
from A,B,C
where A.主键=C.外键 and B.主键=C.外键 and 其他条件;
内连接
# 2张表
select *
from A inner join C on A.主键=C.外键
where 其他条件;
# 3张表
select *
from A inner join C on A.主键=C.外键
inner join B on B.主键=C.外键
where 其他条件;
外连接
# 左外连接
select 字段
from A left join C on A.主键=C.外键
where 其他条件;
# 查询结果：左表的全部记录+主外键关联的记录
# 右外连接
select 字段
from C right join A on A.主键=C.外键
where 其他条件;
# 查询结果：右表的全部记录+主外键关联的记录
汇总
Mysql练习
创建
# 创建学生表
#create：创建 table：表格
CREATE TABLE xsb (
xh VARCHAR ( 20 ) PRIMARY KEY,
xm VARCHAR ( 20 ) NOT NULL,
xb enum ( '男', '女' ),
jg VARCHAR ( 20 ),
bj VARCHAR ( 20 ) DEFAULT '241期',
nl INT,
sfzh CHAR ( 18 ) UNIQUE,
zcrq TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
# 创建课程表
CREATE TABLE kcb (
kch INT PRIMARY KEY,
kcm VARCHAR ( 20 ) NOT NULL
);
# 创建成绩表
CREATE TABLE cjb (
xh VARCHAR ( 20 ),
FOREIGN KEY ( xh ) REFERENCES xsb ( xh ),
kch INT,
FOREIGN KEY ( kch ) REFERENCES kcb ( kch ),
cj INT,
PRIMARY KEY ( xh, kch )
);
向表中添加数据
# 向 学生表 添加数据
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '001', '晓燕', '女', '河北', 18, '1班', '123456789123456788', '2016/7/14 09:41:47' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '002', '张三', '男', '安徽', 20, '1班', '123456389123456789', '2016/4/12 17:19:43' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '003', '晓燕', '女', '河北', 18, '5班', '123456789123456789', '2016/7/14 00:00:00' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '004', '李四', '男', '安徽', 22, '4班', '340322199005247654', '2015/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '005', '李四', '男', '天津', 19, '3班', '340322199004247654', '2015/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '006', '王欣', '女', '河北', 18, '4班', '340322199006247654', '2015/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '007', '王钊', '男', '山西', 20, '2班', '340322199007247654', '2015/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '008', '张三', '男', '河南', 19, '123期', '340322199008247654', '2015/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '009', '班长', '男', '湖南', 21, '1班', '340322199000297654', '2015/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '010', '刘诗诗', '女', '广东', 26, '2班', '340322199607247654', '2015/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '011', '李晨', '女', '北京', 30, '4班', '340322199005267754', '2012/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '012', '李小龙', '男', '新疆', 26, '3班', '340322199000297655', '2015/11/25 23:23:25' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '013', '唐嫣', '女', '安徽', 39, '3班', '123456788890981234', '2016/1/1 00:00:00' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '014', '杨幂', '女', '安徽', 39, '3班', '123456788890981235', '2016/1/1 00:00:00' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '015', '孙丽丽', '女', '安徽', 39, '3班', '123456788890981236', '2016/1/1 00:00:00' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '016', '胡歌', '男', '北京', 20, '3班', '111111111111111111', '2016/4/12 09:42:40' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '017', '赵丽颖 ', '女', '安徽', 39, '3班', '123456788890981237', '2016/1/1 00:00:00' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '018', '邓超', '女', '安徽', 39, '3班', '123456788890981238', '2016/1/1 00:00:00' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '019', '四爷', '男', '山西', 32, '1班', '123456789123446789', '2015/1/2 00:00:00' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '020', '李四光', '男', '广东', 60, '1班', '340322199002242357', '2016/4/7 14:06:39' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '023', '李大小龙', '女', '北京', 27, '1班', '340322199005267758', '2016/4/7 14:14:40' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '024', '范冰冰', '女', '安徽', 39, '3班', '123456788890981239', '2016/1/1 00:00:00' );
INSERT INTO xsb ( `xh`, `xm`, `xb`, `jg`, `nl`, `bj`, `sfzh`, `zcrq` )
VALUES
( '025', '刘亦菲', '女', '安徽', 39, '3班', '123456788890981230', '2016/1/1 00:00:00' );
# 向 课程表 添加数据
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 1, '数据库' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 2, 'C语言' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 3, 'Linux' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 4, '系统测试' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 5, '单元测试' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 6, '项目实践' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 7, '数学课' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 8, '语文课' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 9, '数学课' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 20, '地理课' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 55, '体育课' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 62, '地理课' );
INSERT INTO kcb ( `kch`, `kcm` )
VALUES
( 63, '地理课' );
# 向 成绩表 添加数据
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 1, '004', 96 );
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 1, '005', 30 );
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 2, '006', 76 );
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 3, '002', 90 );
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 3, '008', 50 );
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 3, '011', 86 );
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 4, '012', 34 );
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 6, '009', 0 );
INSERT INTO cjb ( `kch`, `xh`, `cj` )
VALUES
( 7, '008', 59 );
单表查询
#一、单表查询练习
#名字有龙,且3个字
select *
from xsb
where xm like "%龙%" and xm like "___";
select *
from xsb
where xm like "龙__" or xm like "_龙_" or xm like "__龙";
#查询张三的年龄和班级
select nl,bj
from xsb
where xm="张三";
#查询来自于北京的学生的班级
select bj
from xsb
where jg="北京";
#一、单表查询
#1、查询<学生表>，查询学生"张三"的全部基本信息---*
select *
from xsb
where xm="张三";
#2、查询<学生表>，查询学生"张三"或”李四”的基本信息----or
select *
from xsb
where xm="张三" or xm="李四";
#3、查询<学生表>，查询年龄大于20岁且是1班学生的基本信息----and
select *
from xsb
where nl>20 and bj="1班";
#4、查询<学生表>，查询姓名"张三"或”李四”且年龄大于20岁的学生的基本信息---and优先级高于or
select *
from xsb
where (xm="张三" or xm="李四") and nl>20;
#5、查询<学生表>，查询姓"张"学生的姓名，年龄，班级。---‘like’模糊查询，‘ %’任意字符
select xm,nl,bj
from xsb
where xm like "张%";
#6、查询<学生表>，查询姓名中含有"四"字的学生的基本信息----'% %'
select *
from xsb
where xm like "%四%";
#7、查询<学生表>，查询姓名长度为三个字，姓“李”，且最后一个字是“龙”的全部学生的学号，姓名，年龄，班级，身份证号。-------------------‘_’一个任意字符
select xh,xm,nl,bj,sfzh
from xsb
where xm like "李_龙";
select xh as '学号',xm as '姓名',nl as '年龄',bj as '班级',sfzh as '身份证号'
from xsb
where xm like "李_龙";
#8、查询<学生表>，查询姓"张"或者姓”李”的学生的基本信息。---like---or
select *
from xsb
where xm like "张%" or xm like "李%";
#9、查询<学生表>，查询姓"张"并且"所属省份"是"北京"的学生信息---like---and
select *
from xsb
where xm like '张%' and jg='北京';
#10、查询<学生表>，查询"所属省份"是"北京"、”新疆”、”山东”或者"上海"的学生的信息----or---or---or---or
select *
from xsb
where jg="北京" or jg="新疆" or jg="山东" or jg="上海";
#-----‘in’集合---jg in(''xx,''yy)
select *
from xsb
where jg in("北京","新疆","山东","上海");
#11、查询<学生表>，查询姓"张"，但是"所属省份"不是"北京"的学生信息---‘!=’不等于
select *
from xsb
where xm like '张%' and jg != '北京';
#-------------不等于'<>’
select *
from xsb
where xm like '张%' and jg <> '北京';
#12、查询不是姓"张"，且所属省份是"河北"的学生信息-----not like
select *
from xsb
where xm not like '张%' and jg = '河北';
#13、查询所属省份不是"北京"、"新疆"、"山东"、"上海"的学生的信息----‘not in’
select *
from xsb
where jg not in("北京","新疆","山东","上海");
#-----!=----and------!=-----and-----!=-----and----!=---;
select *
from xsb
where jg != "北京" and jg != "新疆" and jg != "山东" and jg != "上海";
select *
from xsb
where jg not in ("北京","新疆","山东","上海");
#14、查询没有填写成绩的学生的学号、课程号和成绩------------------------is null
select xh,kch,cj
from cjb
where cj is null;
#15、查询成绩不为空的学生的学号、课程号和成绩---------------is not null
select xh,kch,cj
from cjb
where cj is not null;
#16、查询成绩在80-95之间的学生的学号、课程号和成绩--------------between------------and
select xh,kch,cj
from cjb
where cj between 80 and 95;
select xh,kch,cj
from cjb
where cj >= 80 and cj <= 95;
#17、给字段起个别名(不改变数据库中的字段)-----------------as
select xh as '学号',xm as '姓名',nl as '年龄',bj as '班级',sfzh as '身份证号'
from xsb;
#18、查询成绩在80-95之间的学生的学号、课程号、成绩，并按成绩升序排列，或降序排列----asc（升序）、desc(降序) order:排序 by:通过...方式
select xh,kch,cj
from cjb
where cj between 80 and 95
order by cj asc;
select xh,kch,cj
from cjb
where cj between 80 and 95
order by cj desc;
#19、查询<学生表>，查询现有学生都来自于哪些不同的省份---去除重复记录---distinct:不同的
select distinct jg
from xsb;
#20、查询<学生表>，查询男女生都来自于哪些不同的省份---distinct 字段1,字段2
select distinct jg,xb
from xsb;
#21、查询<成绩表>，查询没有填写成绩的学生的学号、课程号和成绩，并按学号升序排序---is null---order by 字段名 asc
select xh,kch,cj
from cjb
where cj is null
order by xh asc;
#22、查询<成绩表>，查询全部填写了成绩的学生的选修 信息，并按照“成绩”从高到低进行排序---‘order by 字段名 desc’排序降序
select *
from cjb
where cj is not null
order by cj desc;
#23 、查询班级考试的前8名学生的成绩---limit m,n
select xh,cj
from cjb
order by cj desc
limit 8;
select cj
from cjb
order by cj desc
limit 0,8;
#24、查询所有学生中年龄最大的前10个学生中的第三个人到第七个人的基本信息
select *
from xsb
order by nl desc
limit 2,5;
#求1班的最大年龄是多大-------max(字段)
select max(nl)
from xsb
where bj = "1班";
#二、聚合函数练习
#1、统计<学生表>，统计共有多少个学生---‘count（*）’统计记录数
select count(*)
from xsb;
#2、统计<学生表>，统计年龄大于20岁的学生有多少个
select count(*)
from xsb
where nl>20;
#3、统计<学生表>，注册日期在2000年至2020年的学生人数-->=----and ---<---Between ---and ---区间
select count(*)
from xsb
where zcrq >= "2000-01-01 00:00:00" and zcrq <= "2020-12-31 23:59:59";
select count(*)
from xsb
where zcrq between "2000-01-01 00:00:00" and "2020-12-31 23:59:59";
#4、统计<成绩表>，统计学号为"002"的学生的平均成绩
select avg(cj)
from cjb
where xh="002";
#5、统计<成绩表>，统计学号为"002"的学生的总成绩--- ‘sum（字段名）’求和
select sum(cj)
from cjb
where xh="002";
#6、统计<成绩表>，查询课程号为” 2”的课程的最高成绩--- ‘max（字段名）’最大值
select max(cj)
from cjb
where kch=2;
#7、统计<学生表>，查询所有学生中的最大年龄是多少--- ‘max（字段名）’最大值
select max(nl)
from xsb;
#8、查询今天有多少人入学
select count(*)
from xsb
where zcrq between "2023-06-15 00:00:00" and "2023-06-15 23:59:59";
#9、求008这个学生的最高分、最低分、平均分、总成绩、参加了多少次考试--- 5个聚合函数一起用
select max(cj),min(cj),avg(cj),sum(cj),count(*)
from cjb
where xh = "008";
#三、分组查询练习
select count(*),xb
from xsb
where bj='1班'
group by xb;
#1、统计每个课程的选修人数
select count(*),kch
from cjb
group by kch;
#2、统计每个同学的总成绩
select sum(cj),xh
from cjb
group by xh;
#3、统计<学生表>，统计每个班级中每种性别的学生人数，并按照班级排序
select count(*),bj,xb
from xsb
group by bj,xb
order by bj;
#4、统计<成绩表>，统计每门课程的平均成绩,并按照平均成绩降序排序
select avg(cj),kch
from cjb
group by kch
order by avg(cj) desc;
#5、统计<学生表>，统计每个班级中的最大年龄是多少
select max(nl),bj
from xsb
group by bj;
多表查询
#1、先到xsb查张三的xh
select xh
from xsb
where xm="张三";
#2、再到kcb查数学课的kch
select kch
from kcb
where kcm="数学课";
#3、最后到cjb查（张三的xh、数学课的kch对应的）成绩
select cj
from cjb
where xh in (select xh from xsb where xm="张三")
and kch in(select kch from kcb where kcm="数学课");
#1、读题，看用到几张表---xsb---kcb---cjb
#2、针对每张表写一个单表查询的SQL
#1）把题目中条件用起来---where xm='张三'
#2）查当前表能和外界 建立关联的字段----select xh
#3、进行嵌套
#查询王欣的C语言成绩
select cj
from cjb
where xh in (select xh from xsb where xm="王欣")
and kch in(select kch from kcb where kcm="C语言");
#五、嵌套查询练习
#1、用子查询实现，查询选修“数学课”的全部学生的总成绩
select sum(cj)
from cjb
where kch in(select kch from kcb where kcm="数学课");
#2、用子查询实现，查询2班选修"数据库"课的所有学生的成绩之和
select sum(cj)
from cjb
where xh in(select xh from xsb where bj="2班")
and kch in(select kch from kcb where kcm="数据库");
#3、用子查询实现，查询3班"张三"同学的"测试管理"成绩
select cj
from cjb
where xh in(select xh from xsb where bj="3班" and xm="张三")
and kch in(select kch from kcb where kcm="测试管理");
#选修了3这门课的学生有哪些
select xh from cjb where kch=3;
#选修了7这门课的学生有哪些
select xh from cjb where kch=7;
#同时选修了3和7的学生有哪些
select xh
from cjb
where kch=3
and xh in(select xh from cjb where kch=7);
#同时选修了3或7的学生有哪些
select xh from cjb where kch in(3,7);
#四、等值连接查询练习
select *
from xsb,kcb,cjb
where xsb.xh=cjb.xh and kcb.kch=cjb.kch and xm='张三' and kcm='数学课';
#1、查询"张三"的各科考试成绩，要求显示姓名、课程号和成绩
select xm,kch,cj
from xsb,cjb
where xm="张三" and xsb.xh=cjb.xh;
#2、查询"张三"的各科考试成绩中，哪科没有记录考试成绩，要求显示姓名、课程号和成绩
select xm,kch,cj
from xsb,cjb
where xm="张三" and cj is null and xsb.xh=cjb.xh;
#3、查询"张三"的各门课程成绩，要求显示姓名、课程名称和成绩
select xm,kcm,cj
from xsb,kcb,cjb
where xm="张三" and xsb.xh=cjb.xh and kcb.kch=cjb.kch;
#4、查询123期"张三"的"数学课"课程成绩，要求显示姓名、成绩
select xm,cj
from xsb,kcb,cjb
where bj="123期" and xm="张三" and xsb.xh=cjb.xh and kcm="数学课" and kcb.kch=cjb.kch;
#5、查询所有2020年以前入学的，各班男生的各科考试平均成绩
select bj,avg(cj),kch
from xsb,cjb
where zcrq<'2020-1-1 00:00:00' and xb="男" and xsb.xh=cjb.xh
group by bj,kch;
#2、内连接
#（1）查询张三的课程成绩，并显示学号、姓名、课程号和成绩。
select xsb.xh,xm,kch,cj
from xsb inner join cjb on xsb.xh=cjb.xh
where xm="张三";
#（2）查询“张三”的课程成绩，并显示学号、姓名、课程名和成绩。
select xsb.xh,xm,kcm,cj
from xsb inner join cjb on xsb.xh=cjb.xh inner join kcb on kcb.kch=cjb.kch
where xm="张三";
#综合练习
#1、查询李四的入学时间
select zcrq
from xsb
where xm="李四";
#2、查询和李四在同一时间入学的学生基本信息
select *
from xsb
where zcrq in(select zcrq from xsb where xm="李四");
#3、查询2班年龄在18岁以上的学生成绩
select cj
from xsb,cjb
where bj="2班" and nl>=18 and xsb.xh=cjb.xh;
#4、查询2016年以后入学的学生的数学课成绩
select cj
from xsb,kcb,cjb
where zcrq>='2016-1-1 00:00:00' and xsb.xh=cjb.xh and kcm="数学课" and kcb.kch=cjb.kch;
#5、查询选修了Linux课的学生的基本信息
select *
from xsb,kcb,cjb
where xsb.xh=cjb.xh and kcb.kch=cjb.kch and kcm="Linux";
#3、使用左外连接
#查询所有学生的课程成绩，并显示学号、姓名、课程号和成绩，包括没有选择课程的学生。
select xsb.xh,xm,kch,cj
from xsb left join cjb on xsb.xh=cjb.xh;
#4、使用右外连接
#查询所有学生的课程成绩，并显示学号、姓名、课程号和成绩，包括没有选择课程的学生
select xsb.xh,xm,kch,cj
from cjb right join xsb on xsb.xh=cjb.xh;
增删改查
# 一、新增
# 1、向学生表新增一条记录
# 全部字段
insert into xsb(xh,xm,xb,jg,bj,nl,sfzh,zcrq)
values('030','王宝强','男','河北','241期',18,'123456789012345678','2021-9-15');
# 省略全部字段------前提：8个字段的取值顺序要和学生表中原有的字段顺序一致
insert into xsb
values('031','王宝强','男','河北','241期',18,'123456789012345677','2021-9-15');
# 插入部分字段的数据---xh,xm,nl
insert into xsb(xh,xm,nl)
values('032','刘亦菲',18);
select * from xsb
# 省略字段时，要注意非空约束------xm是非空的，必须要给值
insert into xsb(xh,nl)
values('033',18);
# 2、向学生表里插入多条记录
# 插入多条记录---全部字段
insert into xsb(xh,xm,xb,jg,bj,nl,sfzh,zcrq)
values('034','王宝强','男','河北','241期',18,'123456789012345676','2021-9-15');
insert into xsb(xh,xm,xb,jg,bj,nl,sfzh,zcrq)
values('035','王宝强','男','河北','241期',18,'123456789012345675','2021-9-15');
#插入多条记录时，每条记录的取值之间用逗号分隔---省略全部字段
insert into xsb values
('036','王宝强','男','河北','241期',18,'123456789012345674','2021-9-15'),
('037','王宝强','男','河北','241期',18,'123456789012345673','2021-9-15');
# 3、把一张表的数据拷贝到另外一张表中（2张表：字段类型和长度、约束一致）
create table xsb2(
xh varchar(20) primary key,
xm varchar(20) not null,
xb enum('男','女'),
jg varchar(20),
bj varchar(20) default '241期',
nl int,
sfzh char(18) unique,
zcrq timestamp default current_timestamp
);
select * from xsb2;
# 先把xsb数据查出来，然后再插入到xsb2
insert into xsb2 select * from xsb;
# 二、修改
# 1、把王宝强的名字改成马保国
select *
from xsb
where xm='王宝强'
update xsb
set xm='马保国'
where xm='王宝强';
# 2、将008这个学生的成绩改成100分
select cj
from cjb
where xh='008'
update cjb
set cj=100
where xh='008';
# 3、将范冰冰的年龄在原来的基础上+1岁 nl=nl+1
select nl
from xsb
where xm='范冰冰'
update xsb
set nl=nl+1
where xm='范冰冰'
# 4、把张三的年龄+1岁，班级改为220期
select *
from xsb
where xm='张三'
update xsb
set nl=nl+1,bj='220期'
where xm='张三'
# 5、将张三的数学课成绩修改为60分
# 提示：先把张三的数学课成绩查出来，再写修改SQL，等值或嵌套或内连接
# 原理：
select cj
from xsb,kcb,cjb
where xm="张三" and xsb.xh=cjb.xh and kcm="数学课" and kcb.kch=cjb.kch;
update xsb,kcb,cjb
set cj=60
where xm="张三" and xsb.xh=cjb.xh and kcm="数学课" and kcb.kch=cjb.kch;
# 嵌套
select cj
from cjb
where xh in(select xh from xsb where xm='张三')
and kch in(select kch from kcb where kcm='数学课');
update cjb
set cj=60
where xh in(select xh from xsb where xm='张三')
and kch in(select kch from kcb where kcm='数学课');
# 等值
select cj
from cjb,xsb,kcb
where sb.xh=cjb.xh and kcb.kch=cjb.kch
and xm='张三' and kcm='数学课';
update cjb,xsb,kcb
set cj=70
where xsb.xh=cjb.xh and kcb.kch=cjb.kch and xm='张三' and kcm='数学课';
# 内连接
select cj
from cjb inner join xsb on cjb.xh = xsb.xh
inner join kcb on kcb.kch=cjb.kch
where xm='张三' and kcm='数学课';
update cjb inner join xsb on cjb.xh = xsb.xh
inner join kcb on kcb.kch=cjb.kch
set cj=80
where xm='张三' and kcm='数学课';
# 三、删除
# 1、将成绩表中学号008的所有成绩都删除(删除的是整条记录，修改的是字段)----先查出满足条件的记录，再进行删除
select * from cjb where xh='008';
delete from cjb where xh='008';
delete from 1张表 where 条件;
# 2、删除成绩小于60分的学生的成绩
select * from cjb where cj<60
delete from cjb where cj<60
# 3、删除王欣的C语言成绩
select cj
from cjb
where xh in(select xh from xsb where xm='王欣')
and kch in(select kch from kcb where kcm='C语言')
delete
from cjb
where xh in(select xh from xsb where xm='王欣')
and kch in(select kch from kcb where kcm='C语言')
# 4、把cjb数据清空，用delete和truncate这2种方式实现
select * from cjb
delete from cjb;
truncate table cjb;
# 区别：
# 1、delete是逐行删除，而truncate是直接删除整张表里的数据
# 2、delete支持按条件删除，如：delete from 表 where 条件；而truncate不支持按条件删除
#存储过程
#procedure：存储过程
#set：设置
#declare：声明
# 五、创建存储过程
create procedure proc666()
begin
declare i int;
set i=100;
while i<=1000 do
insert into xsb(xh,xm)values(i,concat('student',i));
set i=i+1;
end while;
end
# 2、执行存储过程
call proc666(); #执行存储过程 call：打电话
select * from xsb
delete from xsb where xh>=100;
create procedure proc666() #procedure：存储过程
begin
declare i int; #声明一个整数类型的变量i
set i=100; #设置变量i的起始值为100
while i<=1000 do #循环条件：当i<=1000时，做下面的事
insert into xsb(xh,xm)values(i,concat('student',i)); #向学生表插入记录，concat('xx',i):将xx和i进行拼接
set i=i+1; #设置i每一次增加1，这样i才会不断的变化
end while;
end
# 四、级联更新、级联删除（了解）
# 建表
create table stu(
id int primary key,
name varchar(10)
);
create table info(
id int primary key,
stu_id int,
foreign key(stu_id) references stu(id) on update cascade on delete cascade,
jg varchar(10)
);
# on update cascade：级联更新，on delete cascade：级联删除
select * from stu;
select * from info;
drop table info;#删除info表，表结构就不存在了
drop table stu;
insert into stu(id,name) values(1,'张三'),(2,'李四'),(3,'王五');
insert into info(id,stu_id,jg) values(1,1,'北京'),(2,2,'山西'),(3,3,'河北'),(4,2,'上海');
# 级联删除：当主表的记录被删除时，从表的记录也跟着被删除，
# 例：学生辍学了，学生表删除此学生信息，成绩表此学生成绩记录也自动被删除
# 1、删除stu中id=1的记录
delete from stu where id=1;
# 级联更新：当主表的主键发生变化时，从表的外键也跟着发生改变，
# 例：学生留级了，需要修改学生表的学号，成绩表的学号也会跟着改变
2、把stu中id=2的记录中id改成5
update stu set id=5 where id=2;
# 七、索引（了解）
# 1、定义：数据库索引是针对数据库表中一列或多列的值进行排序的一种结构
# 2、优点
# 使用数据库索引可以快速访问数据库表中的信息-----快
# 提高系统的性能，加快对表中记录的查找和排序, 提高查询数据的性能----快
# 3、缺点
# 创建索引占用存储空间----占地
# 创建和维护索引耗费时间-----耗时
# 修改或插入数据需要花费更多的时间----耗时
# 4、查看索引：show index from 表名;
show index from xsb;
# 5、创建索引：create index 索引名 on 表名(字段名);
create index mxbc on xsb(nl);
# 6、删除索引：drop index 索引名 on 表名;
drop index mxbc on xsb;
# 比如：一个学生表（t_stu ）有1000条数据，给它id列建个主键和索引，
# 你想查询id=1000；的这条信息，如果没有索引，它就一条一条的比对查找，
# 系统运行1000次才找到，要是创建了索引，你查询id=1000的这条信息，
# 系统只运行一次就找到了。
# 六、表结构修改（补充，了解）
select * from stu;
# 1、重命名表
alter table stu rename stu_new;
# alter：修改 rename：重命名
select * from stu_new;
# 2、修改一个字段的名字和类型，并且指定约束
alter table stu_new change name name_new char(20) not null;
# 3、新增一个字段
alter table stu_new add time timestamp;
#add：添加，time是新添加的字段名，timestamp是新添加字段的类型
# 4、删除表里的某个字段
alter table stu_new drop column time;
#drop删除 column：列
# 5、修改一个字段的类型
alter table stu_new change name_new name_new varchar(20);