【转】SQL 21天实战练习
转载自：软件测试从业者必备SQL合集V1.0（21天搞定） 作者：
开始之前的，前置准备事项：
1、在自己电脑上安装一个mysql数据库，文章见 ->
http://istester.com/article/258.html
2、找一个mysql客户端链接工具：推荐workbench ，安装包
http://istester.com/article/405.html
第1天作业 ，
1）创建数据库 istester
2）查看数据库创建是否成功
参考答案：
1）创建数据库 istester
create DATABASE istester;
2）查看数据库创建是否成功
show DATABASES;
第2天作业 ，
在昨天创建的isTester数据库下，创建 表 istester 和 表 idoxu
注：SQL的作业，前几天的 库、表，都是打基础；没有这些，后面的增删改查，你也没法玩 ；
1）isTester表的字段、类型、属性：
id INT(10) NOT NULL UNIQUE PRIMARY KEY ,
uname VARCHAR(20) NOT NULL ,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20) ,
address VARCHAR(50) ,
idoxu VARCHAR(20)
2）idoxu表的字段、类型、属性：
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
istester VARCHAR(50) ,
grade INT(10)
参考答案：
创建istester和idoxu表
CREATE TABLE istester (
id INT(10) NOT NULL UNIQUE PRIMARY KEY ,
uname VARCHAR(20) NOT NULL ,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20) ,
address VARCHAR(50) ,
idoxu VARCHAR(20)
);
CREATE TABLE idoxu (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
istester VARCHAR(50) ,
grade INT(10)
);
第3天作业 ，
根据已有的表istester 和 idoxu 创建新表（复制表）
1）复制istester表结构，创建新表istester2
注：可以有两种方式
2）复制idoxu表结构和数据，创建新表idoxu2
3）复制idoxu表结构的 id,stu_id,istester三个字段，创建新表idoxu3
参考答案：
1）复制istester表结构，创建新表istester2
注：可以有两种方式
create table istester2 like istester ;
或
create table idoxu2 as select * from idoxu where 2=1;
这种方法会将old_table中所有的内容都拷贝过来（只是数据记录为空）,用这种方法需要注意,new_table中没有了old_table中的primary key,Extra,auto_increment等属性,需要自己手动加。
2）复制idoxu表结构和数据，创建新表idoxu3
create table idoxu3 as select * from idoxu
3）复制idoxu表结构的 id,stu_id,istester三个字段，创建新表idoxu3
create table idoxu3 as select id,stu_id,istester from idoxu where 1<>1;
第4天作业：
1）删除表 istester2
2）同时删除表idoxu2和idoxu3
3）查看还有哪些表
参考答案：
1）删除表 istester2
drop table istester2 ;
2）同时删除表idoxu2和idoxu3
drop table idoxu2,idoxu3 ;
3）查看还有哪些表
show tables;
第5天作业：
1）修改istester表，添加一个字段istester6 （字符类型VARCHAR，长度100，不允许为空）
2）查看字段istester6 ，是否添加成功
3）修改istester表，删除字段istester6
4）确认字段istester6 ，是否删除成功
参考答案：
1）修改istester表，添加一个字段istester6 （字符类型VARCHAR，长度100，不允许为空）
ALTER table istester add column istester6 VARCHAR(100) NOT NULL;
2）查看字段istester6 ，是否添加成功
desc istester
3）修改istester表，删除字段istester6
ALTER table istester drop istester6
4）确认字段istester6 ，是否删除成功
desc istester
第6天作业：
插入 insert：
1）向表istester插入数据
id = 1 ,uname = idoxu ,idoxu = 2020
id = 2 ,uname = idoxu2 ,idoxu = 2020
id=11,uname = idoxu3,sex=1
id=12,uname = idoxu4,sex=2
id=13,uname = idoxu5,sex=1
id=14,uname = idoxu6,sex=2
2）向表idoxu插入数据
id=4,stu_id=11,c_name=idoxu,grade=90
id=5,stu_id=12,c_name=lin,grade=100
id=6,stu_id=33,c_name=istester,grade=20
参考答案：
插入 insert：
1）向表istester插入数据
id = 1 ,uname = idoxu ,idoxu = 2020
id = 2 ,uname = idoxu2 ,idoxu = 2020
id=11,uname = idoxu3,sex=1
id=12,uname = idoxu4,sex=2
id=13,uname = idoxu5,sex=1
id=14,uname = idoxu6,sex=2
2）向表idoxu插入数据
id=4,stu_id=11,c_name=idoxu,grade=90
id=5,stu_id=12,c_name=lin,grade=100
id=6,stu_id=33,c_name=istester,grade=2
insert into istester(id,uname,idoxu) values(1,"idoxu",2020);insert into istester(id,uname,idoxu) values(2,"idoxu2",2020);
insert into istester(id,uname,sex) values(11,"idoxu3",1),(12,"idoxu4",2),(13,"idoxu5",1),(14,"idoxu6",2);
insert into idoxu(id,stu_id,c_name,grade) values(4,11,"idoxu",90),(5,12,"lin",100),(6,33,"istester",20);
第7天作业 ，
1）查询istester表 id = 1 的内容
2）查询idoxu 表 grade=100 的内容
参考答案：
1）查询istester表 id = 1的内容
select * from istester where id = 1;
2）查询idoxu 表 grade=100 的内容
select * from idoxu where grade =100;
第8天作业 ：
1）查找idoxu表，名称（c_name）包含 “i” 的数据
2）查找istester表，id 包含 “1” 的数据
3）查找istester表，id 包含 “1” 的数据，按id降序
4）查找istester表，id 包含 “1” 的数据 ，取id最大的三个
参考答案：
1）查找idoxu表，名称（c_name）包含 “i” 的数据
select * from idoxu where c_name like '%i%' ;
2）查找istester表，id 包含 “1” 的数据
select * from istester where id like '%1%';
3）查找istester表，id 包含 “1” 的数据，按id降序
select * from istester where id like '%1%' order by id desc ;
4）查找istester表，id 包含 “1” 的数据 ，取id最大的三个
select * from istester where id like '%1%' order by id desc limit 3 ;
第9天作业：
1）找出idoxu表中，分数最高的同学和分数
2）找出idoxu表中，分数最低的同学和分数
参考答案：
-- 1）找出idoxu表中，分数最高的同学和分数；
SELECT c_name,grade as "maxvalue" from idoxu WHERE grade in (select MAX(grade) from idoxu ) ;
-- 2）找出idoxu表中，分数最低的同学和分数；
SELECT c_name,grade as "minvalue" from idoxu WHERE grade in (SELECT MIN(grade) from idoxu );
第10天作业 ，
-- 1) 找出istester表，sex为空的的数据；
-- 2）更新istester表，把sex为空的，设置为0（性别未知）
-- 3）找出idoxu表，grade小于60分的同学
-- 4）更新idoxu表，把grade小于60分的同学，一律改为59分
参考答案：
-- 1) 找出istester表，sex为空的的数据；
select * from istester where sex is null ;
-- 2）更新istester表，sex为空的，设置为0（性别未知）
update istester set sex = 0 where sex is null ;
-- 3）找出idoxu表，grade小于60分的同学
select * from idoxu where grade < 60 ;
-- 4）更新idoxu表，把grade小于60分的同学，一律改为59分
update idoxu set grade = 59 where grade < 60 ;
第11天作业：
-- 1）查找istester表，按id降序
-- 2）查找idoxu表，按grade升序
参考答案：
-- 1）查找istester表，按id降序
select * from istester order by id desc;
-- 2）查找idoxu表，按grade升序
select * from idoxu order by grade;
第12天作业 ，
1）查询istester表，有多少条数据
2）查询istester表，有几种性别类型（sex字段，去重）
参考答案：
1）查询istester表，有多少行数据
select COUNT(sex) from istester;
2）查询istester表，有性别类型数量（sex字段，去重）
select COUNT(DISTINCT sex) from istester;
第13天作业 ，
1）查找idoxu表，学生成绩(grade) 总分
2）查找idoxu表，学生成绩(grade) 平均分
参考答案：
1）查找idoxu表，学生成绩(grade) 总分
select sum(grade) as sumgrade from idoxu ;
2）查找idoxu表，学生成绩(grade) 平均分
select avg(grade) as avggrade from idoxu ;
第14天作业：
1）查找idoxu表，成绩在80 - 100区间的学生 ；
2）查找istester表，id 为 2，11，12 的数据 ；
参考答案：
-- 1）查找idoxu表，成绩在80 - 100区间的学生 ；
select * from idoxu where grade between 80 and 100;
-- 2）查找istester表，id 为 2，11，12 的数据 ；
select * from istester where id in (2,11,12) ;
第15天作业：
1）删除 istester表，id大于12的数据 ；
2）删除idoxu表，分数grade不及格（小于60分）的数据 ；
参考答案：
1）删除 istester表，id大于12的数据 ；
delete from istester where id > 12;
2）删除idoxu表，分数grade不及格（小于60分）的数据 ；
delete from idoxu where grade < 60 ;
第16天作业：
1）造数据 ，把istester表的所有数据，插入到 idoxu表
字段取值规则
id 取id
stu_id 取id
c_name 取 uname
istester 和 grade字段，给默认值 60
参考答案：
insert into idoxu(id,stu_id,c_name,istester,grade) select id,id,uname,60,60 from istester ;
第17天作业：
-- 1）把idoxu表，改名为 idoxu6
-- 2）检查是否修改成功
-- 3）修改表idoxu，把字段istester ，改为istester6（字符类型varchar，长度160 ）
-- 4）检查是否修改成功
参考答案：
-- 1）把idoxu表，改名为 idoxu6
-- ALTER TABLE 旧表名 RENAME TO 新表名 ;
ALTER TABLE idoxu RENAME TO idoxu681 ;
-- 2）检查是否修改成功
show tables;
-- 3）修改表idoxu6，把字段istester ，改为istester6（字符类型varchar，长度160 ）
alter table idoxu6 CHANGE istester istester6 varchar(160);
-- 4）检查是否修改成功
desc idoxu6;
第18天作业：
1）创建数据库 istesterdb6
2）检查数据库是否创建成功 ；
3）进入istesterdb6 库
4）在数据库 istesterdb6 ，创建idoxu表，直接拷贝 istester库idoxu6表的数据和结构 ；
5）检查表是否创建成功 ；
参考答案：
1）创建数据库 istesterdb6
create database istesterdb6 ;
2）检查数据库是否创建成功 ；
show databases ;
3）进入istesterdb6 库
use istesterdb6 ;
4）在数据库 istesterdb6 ，创建idoxu表，直接拷贝 istester库idoxu6表的数据和结构 ；
create table istesterdb6.idoxu as SELECT * from istester.idoxu6;
或
create table idoxu as SELECT * from istester.idoxu6;
5）检查表是否创建成功 ；
show tables;
第19天作业：
idoxu表，分数grade
1）排名前三的学生 和分数 ；
2）排名3 - 6名的学生 和分数 ；
3）排名6名以后的所有学生 和分数 ；
参考答案：
-- 1）排名前三的学生 和分数 ；
SELECT c_name,grade FROM idoxu ORDER BY grade DESC LIMIT 3;
-- 2）排名3 - 6名的学生 和分数 ；
SELECT c_name,grade FROM idoxu ORDER BY grade DESC LIMIT 2,4;
-- 3）排名6名以后的所有学生 和分数 ；
SELECT c_name,grade FROM idoxu ORDER BY grade DESC LIMIT 5,666;
注：这题目的是limit的用法，很多同学写的太复杂 ；
limit是mysql的语法
select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
即取出第3条至第6条，4条记录
第20天作业：
多表（左链接、右链接、内链接）
表 idoxu6 和 表istester
参考答案：
注：主要目的是熟悉这三种玩法，以及观察结果差异 ；
#左连接
SELECT * from idoxu6 a LEFT JOIN istester b on a.stu_id=b.id;
#内连接
SELECT * from idoxu6 a INNER JOIN istester b on a.stu_id=b.id;
#右连接
SELECT * from idoxu6 a RIGHT JOIN istester b on a.stu_id=b.id;
第21天作业（最后1天，结业） ，
1）把表idoxu6，改完idoxu
2）删除库 istesterdb6
3）删除istester表
4）清空idoxu表的数据
参考答案：
1）把表idoxu6，改名为idoxu
rename table idoxu6 to idoxu;
或
alter table idoxu6 rename idoxu;
2）删除库 istesterdb6
drop database istesterdb6;
3）删除istester表
drop table istester;
4）清空idoxu表的数据
truncate table idoxu;
或
delete from idoxu;
posted on 2021-04-12 09:44 crystal1126 阅读(240) 评论(0) 编辑 收藏 举报
