mysql常见sql语句练习 sql练习题

说重点丶

已于 2022-08-10 17:14:48 修改

阅读量8.1k

点赞数 34

分类专栏： MySQL 文章标签： mysql 数据库

于 2020-12-28 10:39:16 首次发布

本文链接：https://blog.csdn.net/weixin_39160689/article/details/111832055

版权

文章目录

1. Mysql基本语句复习

1.1数据库建库操作

这一章会把基本的mysql语句进行复习

打开Sqlyog，新建一个数据库

在这里插入图片描述

1.2建表操作

新建两张学习兴趣班级表：唱歌、跳舞

在这里插入图片描述

CREATE TABLE sing(
s_num VARCHAR(5) NOT NULL UNIQUE COMMENT '身份证号,这儿为了方便以5位算',
s_beginDate DATE COMMENT '入班时间'
#指定存储引擎为INNODB(这个在优化的时候会学习)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE dance(
d_num VARCHAR(5) NOT NULL UNIQUE COMMENT '身份证号,这儿为了方便以5位算',
d_beginDate DATE COMMENT '入班时间',
d_level TINYINT(1) COMMENT '0初级，1中级，2高级'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

删除表语句:(不演示了)

drop table xxxx
truncate table xxx

三者区别(加上delete三个,这是考点需要掌握):

drop:删除表所有数据与表的数据结构，也就是表直接不存在了(无法回滚)

truncate: 会清空表中所有的数据，速度快，不可回滚；实质是删除整张表包括数据再重新创建表(一旦提交不可回滚)

当然，涉及到删除数据delete语句也是删除数据，这儿就一起说了delete from xxx

delete: 逐行删除数据，每步删除都是有日志记录的，可以回滚数据；实质是逐行删除表中的数据

总结:

速度:一般来说: drop> truncate > delete

安全性:小心使用 drop 和 truncate，尤其没有备份的时候.否则哭都来不及
使用上,想删除部分数据行用 delete，注意带上where子句. 回滚段要足够大.
想删除表,当然用 drop；想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,用delete

1.3基本语句

条件查询：**select * from table1 where 范围

**插入：**insert into table1(field1,field2) values(value1,value2)

**删除：**delete from table1 where 范围

更新：update table1 set field1=value1 where 范围

**查找：select * from table1 where field1 like ’%value1%’

排序：select * from table1 order by field1,field2 [desc]
总数：select count(1) as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

去重: select distinct field1 as minvalue from table1

distinct a,b,c,d abcd全部相同才是重复的

注:如果是distinct 多列是以多列一起判断是否重复

分组: select * from table1 group by xxx

group by a,b,c,d abcd全部相同才是一组

注:如果是分组多列是以多列一起判断是否相同分组

自己练习！！！

1.4列操作（重点）

可以看到在上面的sing兴趣班，对应的表中字段少了，入班时间与等级两个字段，现在加入：

· 添加列语句：alter table 表名 add column 列名 varchar(30);

在sing表增加s_level列:

在这里插入图片描述

ALTER TABLE sing ADD COLUMN s_level TINYINT(1);

删除列：alter table 表名 drop column 列名;

修改列名MySQL： alter table 表名 change 原名新名 int;

修改列类型：alter table 表 modify name varchar(22);

这儿只演示了增加列，大家需要记住语句，笔试中可能会遇到

1.5join操作(重点)

为了方便测试，我们在两张表中加入如下数据

在这里插入图片描述

#增加测试数据 唱歌班级
INSERT INTO sing(s_num,s_beginDate,s_level) VALUES
('10001','2000-01-01',1),
('10002','2001-01-01',2),
('10003','2002-01-01',3),
('10004','2003-01-01',3)

#增加测试数据 跳舞班级
INSERT INTO dance(d_num,d_beginDate,d_level) VALUES
('10003','2000-01-01',1),
('10004','2001-01-01',2),
('10005','2002-01-01',3),
('10006','2003-01-01',3)

1 inner join或者 join

查询同时参加了两个兴趣班的同学的身份证号

身份证号就是链接条件

在这里插入图片描述

2 left join

查询sing班级全体同学的身份证号及在dance班的等级

分析:sing全体同学，那么没有参加dance班也需要查出来

在这里插入图片描述

3 right join

查询sing班级全体同学的身份证号及在dance班的等级

分析:sing全体同学，那么没有参加dance班也需要查出来

上题类似，用right join 只需要把sing表移到右边即可

在这里插入图片描述

4…查询只参加了sing兴趣班的同学的身份证号码

在这里插入图片描述

5.查询两个班所有学生的身份证号码

注:union 用法请查看

http://www.w3school.com.cn/sql/sql_union.asp

说明:Oracle数据库支持full join，mysql是不支持full join的

在这里插入图片描述

练习1:(重点)

查询只选了一个兴趣班学生的身份证号码

答案见sql附件最后:

#查询只选了一个兴趣班学生的身份证号码  3.4参加两个班
SELECT s_num FROM sing LEFT JOIN dance ON s_num=d_num WHERE d_num IS NULL
UNION 
SELECT d_num FROM sing RIGHT JOIN dance ON s_num=d_num WHERE s_num IS NULL

子查询方式
select s_num from sing where s_num not in(select d_num from dance)
UNION 
select d_num from dance where d_num not in(select s_num from sing)

1.6case when then else end(重点)

oracle:
select t.id,
       t.name,
       t.age,
       decode(t.sex, '1', '男生', '2', '女生', '其他') as sex
  from STUDENT2 t

case :指定字段

when:当什么条件

then:满足上面的条件就干什么

else:前面的条件都不满足

end:结束语

直接看题来解释吧

1.6.1查询兴趣班sing表，要求字段level，初中高替换123

在这里插入图片描述

也可以写成如下格式

在这里插入图片描述

1.6.2.统计sing班级初中高等级的个数，按如下结果显示

在这里插入图片描述

练习2:(次重点)

国家（country）人口（population）
中国 600
美国 100
加拿大 100
英国 200
法国 300
日本 250
德国 200
墨西哥 50
印度 250

根据这个国家人口数据，统计亚洲和北美洲的人口数量。应该得到下面这个结果。

洲人口
亚洲 1100
北美洲 250
其他 700

其中日本、中国、印度属于亚洲，美国、加拿大、墨西哥属于北美洲

SELECT CASE country WHEN '中国' THEN '亚洲' WHEN '日本' THEN '亚洲' WHEN '印度' THEN '亚洲'
WHEN '美国' THEN '北美洲' WHEN '加拿大' THEN '北美洲' WHEN '墨西哥' THEN '北美洲' ELSE '其它' END '国家'
,SUM(population)
FROM country 
GROUP BY 1

练习：

一张学生成绩表（tb_student）
FName FCores FScores
小明 语文 90
小明 数学 92
小明 英语 89
小红 语文 91
小红 数学 92
小红 英语 80
要求，写出合理的sql语句，得到下面的结果
姓名 语文 数学 英语
小明 90 92 89
小红 91 92 80

1.7日期函数

1.7.1日期类型：

1.DATETIME:

类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以’YYYY-MM-DD HH:MM:SS’格式显示DATETIME值，支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。（“支持”意味着尽管更早的值可能工作，但不能保证他们可以。）

2.DATE:

类型用在你仅需要日期值时，没有时间部分。MySQL检索并且以’YYYY-MM-DD’格式显示DATE值，支持的范围是’1000-01-01’到’9999-12-31’。

3.TIMESTAMP:

每张表的第一个timestamp会随着插入与修改自动更新

1.timestamp ：表示该字段在插入和更新时都不会自动设置为当前时间。

2.timestamp default current_timestamp :字段插入时如果没有时间则自动设置为当前时间，更新没有变化。

3.timestamp on update current_timestamp:字段插入不会自动设置，字段更新时没有指定时间则自动设置为当前时间。

4.default current_timestamp on update current_timestamp:插入和修改字段时如果没有指定时间则会自动设置为当前时间。

可以表示年月日时分秒，你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。

CREATE TABLE test(
i INT,
a TIMESTAMP,
b TIMESTAMP
)
INSERT INTO test(i) VALUES(1) #a字段会自动更新为当前时间 b不会
SELECT * FROM test

4.TIME:

数据类型表示一天中的时间。MySQL检索并且以"HH:MM:SS"格式显示TIME值。支持的范围是’00:00:00’到’23:59:59’。

1.7.2日期函数

1.系统当前时间函数

在这里插入图片描述

2.date_add函数

例如:查询sing表，将所有入班日期加上一天

在这里插入图片描述

我们这儿使用的是date，无法操作时分秒
下面的类似上面的操作，这儿不演示了:
select date_add(now(), interval 1 day); -- 当前时间add 1 day
select date_add(now(), interval 1 hour); -- add 1 hour
select date_add(now(), interval 1 minute); -- ...
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 microsecond);
select date_add(now(), interval 1 week);
select date_add(now(), interval 1 month);
select