MySQL数据库-基础练习题

---

– 一、创建建数据库、创建建数据表、查看数据库、查看数据 表

---

– 01.查看mysql服务器中所有数据库
show databases;
– 02.进入某一数据库（进入数据库后，才能操作库中的表和表记录）
use mysql;
– 如果没有进入某一库,在对库中的数据进行访问时, 会提示 “No database selected”
select database();
– 查看当前已进入的数据库

– 03.查看当前数据库中的所有表
show tables;
– 04.删除mydb1库
– 语法：drop database 库名;
drop database if exists mydb1;

-- if exists 如果存在...
-- if not exists 如果存在...

/* 思考：当删除的表不存在时，如何避免错误产生？ */

– 05.重新创建mydb1库，指定编码为utf8
– 语法：create database 库名 charset 编码;
create database mydb1 charset utf8;

-- 如果不存在则创建mydb1;
create database if not exists mydb1 charset utf8;

– 06.查看建库时的语句（并验证数据库库使用的编码）
– 语法：show create database 库名;
show create database mydb1;

– 07.进入mydb1库，删除stu学生表(如果存在)
– 语法：drop table 表名;
use mydb1;
drop table if exists stu;

– 08.创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]）
/* 建表的语法：
create table 表名(
列名 数据类型,
列名 数据类型,
…
); */
drop table if exists stu;
create table stu(
id int primary key auto_increment,
name varchar(20) unique,
gender char(1) not null,
birthday date,
score double
);
– alter table stu modify id int auto_increment;

/*
1、主键约束: 如果将一个列设置为主键,那么该列的值就不能重复,且不能为空!! 作用是:作为记录的唯一标识!!
id int primary key
另外, 主键如果是数值类型,可以设置主键为"auto_increment" – 也就是主键自增
设置为自增后，在数据库中会保存一个变量,用于记录当前的id。变量默认值为1,但是1被使用后,再自增1…

2、唯一约束: 如果为一个列添加唯一约束,那么该列的值就不能重复!!(但是可以为空)
name varchar(20) unique
3、非空约束: 如果为一个列添加非空约束,那么该列的值就不能为空!!(但是可以重复)
gender char(1) not null
– 4、外键约束
*/

– 09.查看stu学生表结构
– 查看学生表的建表语句
show create table stu;
– 查看表结构
desc stu;

/* MySQL的数据类型
1、数值类型
int – 对应Java中的int
double – 对应Java中的double
…
2、字符串类型
(1)char(n) – 定长字符串
stuid char(11)
在存储数据时,所占的空间是固定的,就是最大值,如果数据不足,后面会用空格来补全!!
char类型适合存储长度固定的数据,比如学生编号,身份证号等,这样既不会有空间浪费,效率还会比varchar略高!!
(2)varchar(n) – 不定长字符串
name varchar(50)
在存储数据时,会根据数组的实际长度来存储,剩余的空间还会还给数据!!
varchar适合存储长度不固定的数据,虽然效率没有char类型高,但是不会有空间的浪费!!

3、日期类型
date – 年月日
time – 时分秒
datetime – 年月日 时分秒
timestamp – 时间戳
(1)相同点: 显示的格式相同,都是"年月日时分秒"
(2)timestamp实际存储的是从1970年到指定日期的之间的时间毫秒值；
(3)timestamp指定的列的值,可以自动插入或者自动更新；
4、大文本类型
char(n): n的范围是0~255(字符)
varchar(n): (mysql5.0之后)
n的范围是0~65535(字节)
超过255个字符一般使用大文本类型
text: 65535(字节)
bigtext: 4G

*/

---

– ********** 新增、修改、删除表记录 **********

---

– 10.往学生表(stu)中插入记录(数据)
– 插入记录：insert into 表名(列1，列2，列3…) values(值1，值2，值3…);
insert into stu(id,name,gender,birthday,score) values(null,‘tom’,‘1’,‘1988-3-25’,76);
insert into stu values(null,‘andy’,‘0’,‘1989-4-25’,82);
insert into stu values(null,‘王海涛’,‘0’,‘2000-4-25’,60);
insert into stu values(null,‘王海涛3’,‘0’,‘2000-4-25’,60);

/* 提示：
 设置编码：set names gbk; -- 通知服务器使用gbk接收客户端发送过去的数据, cmd发送数据默认使用GBK, 服务器接收数据也使用GBK,就不会有乱码问题!!
 这个设置只对当前窗口有效!!
 mysql --default-character-set=gbk -uroot -proot */

– 11.查询stu表所有学生的信息
select * from stu;

– 12.修改stu表中所有学生的成绩，加10分特长分
– 修改语法: update 表名 set 列=值,列=值,列=值…;
update stu set score=score+10;
– mysql不支持+=运算符
select * from stu;

– 13.修改stu表中王海涛的成绩，将成绩改为88分。
update stu set score=88 where name=‘王海涛’;
update stu set score=score/2 where name=‘王海涛’;
/* 提示：where子句用于对记录进行筛选过滤，
保留符合条件的记录，将不符合条件的记录剔除。*/

– 14.删除stu表中所有的记录
– 删除记录语法: delete from 表名 [where条件]
delete from stu;
– 仅删除符合条件的
delete from stu where name=‘王海涛’;

---

– 二、基础查询、where子句查询

---

– 准备数据： 以下练习将使用db10库中的表及表记录，请先进入db10数据库!!!
– 准备数据： 以下练习将使用db10库中的表及表记录，请先进入db10数据库!!!

---

– *************** 基础查询 ***************

---

– 15.查询emp表中的所有员工，显示姓名，薪资，奖金
select name,sal,bonus from emp;

– 16.查询emp表中的所有员工，显示所有列
select * from emp;
select id,name,gender,birthday,dept,job,sal,bonus from emp;
/* 使用 *（星号）的缺点：把不必要的列也查询出来了，而且效率不如直接指定列名 /
id,name ,gender,birthday,dept,job,sal,bonus
– 17.查询emp表中的所有部门和职位
select dept,job from emp;
/ 思考：如果查询的结果中，存在大量重复的记录，如何剔除重复记录，只保留一条？ */
– distinct 用于剔除重复的记录
select distinct dept,job from emp;

---

– ************* WHERE子句查询 *************

---

– 18.查询emp表中薪资大于3000的所有员工，显示员工姓名、薪资
select name, sal from emp
where sal>3000;

– 19.查询emp表中总薪资(薪资+奖金)大于3500的所有员工，显示员工姓名、总薪资
select name 姓名,sal+ifnull(bonus, 0) as 总薪资 from emp
where sal+ifnull(bonus, 0)>3500;
– ifnull(列, 值)函数: 判断指定的列是否包含null值, 如果有null值, 用第二个值替换null值
/* 注意查看上面查询结果中的表头，思考如何将表头中的 sal+bonus 修改为 “总薪资” /
/ 试一试：where中能使用定义好的别名吗？ */
– 在where字句中不能使用列别名!!

– 20.查询emp表中薪资在3000和4500之间的员工，显示员工姓名和薪资
select name,sal from emp
where sal>=3000 and sal<=4500;

select name,sal from emp
where sal between 3000 and 4500;
/*提示: between...and...：在...之间*/

– 21.查询emp表中薪资为 1400、1600、1800的员工，显示员工姓名和薪资
select name,sal from emp
where sal in(1400,1600,1800);

– 22.查询薪资不为1400、1600、1800的员工
select name,sal from emp
where sal not in(1400,1600,1800);

-- delete from emp where id in(1,3,5,7); ##批量删除

– 23.查询emp表中薪资大于4000和薪资小于2000的员工，显示员工姓名、薪资。
select name,sal from emp
where sal>4000 or sal<2000;

– 24.查询emp表中薪资大于3000并且奖金小于600的员工，显示员工姓名、薪资、奖金。
– 处理null值
select name,sal,ifnull(bonus,0)
from emp
where sal>3000 and ifnull(bonus,0)<600;

– 25.查询没有部门的员工（即部门列为null值）
select * from emp
where dept is null;
/* 思考：如何查询有部门的员工（即部门列不为null值） */
select * from emp
where dept is not null;

---

– ************* Like模糊查询 *************

---

– 26.查询emp表中姓名中以"刘"开头的员工，显示员工姓名。
select name from emp
where name like ‘刘%’;

/* like进行模糊查询，"%" 表示通配，表示0或多个任意的字符。"_"表示一个任意的字符 */

– 27.查询emp表中姓名中包含"涛"字的员工，显示员工姓名。
select name from emp
where name like ‘%涛%’;

– 28.查询emp表中姓名以"刘"开头并且姓名为2个字的员工，显示员工姓名。
select name from emp
where name like ‘刘_’;

---

– 三、分组查询、聚合函数、排序查询

---

– 29.对emp表按照部门对员工进行分组，查看分组后效果
/* 分组的语法: select 查询的列 from 表名 group by 列名
根据指定的列进行分组 */
select dept, name from emp group by dept;

– 30.对emp表按照职位进行分组, 并统计每个职位的人数, 显示职位和对应人数
select job, count(*) from emp
group by job;
– count(colname)函数: 用于对结果进行统计,统计结果中有多少条记录。如果是分组之后再count，就是统计每一组有多少行记录（即统计每组有多少员工）

– 31.对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资
select dept, max(sal) from emp
group by dept;

– 32.统计emp表中薪资大于3000的员工个数（- count(column)统计某列的行数）
select count(*) from emp
where sal>3000;

-- 注意:聚合函数在统计时会自动剔除null值(即null不参与统计)

– 33.统计emp表中所有员工的薪资总和(不包含奖金)（- sum(column)对某列的值求和）
select sum(sal) from emp;

– 34.统计emp表员工的平均薪资(不包含奖金)（- avg(column)对某列的值求平均值）
select avg(sal) from emp;
select sum(sal)/count() from emp;
select avg(ifnull(bonus,0)) from emp;
select sum(bonus)/count() from emp;
/* 聚合函数:
(1)max(colname)、min(colname) – 求某一列中的最大值、最小值
(2)sum(colname) – 统计某一列的数据之和
(3)avg(colname) – 求某一列的平均值
(4)count(*) – 统计查询结果的记录行数
– 聚合函数会自动剔除null值(即null值不会参数计算或统计)
– 当没有分组时,聚合函数是对整个查询结果进行计算或者统计(可以将整个查询结果看作是一个组)
– 如果进行了分组,聚合函数是对每一个组的记录进行计算或者统计
– 聚合函数不能用在where子句中!!
*/

– 35.查询emp表中所有在1993和1995年之间出生的员工，显示姓名、出生日期。
– year(date)、month(date)、day(date)
select name,birthday from emp
where year(birthday) between 1993 and 1995;

– 36.查询本月过生日的所有员工
select name, birthday from emp
where month(birthday) = month( curdate() );

/* 
curdate() 获取当前日期 年月日
curtime() 获取当前时间 时分秒
sysdate() 获取当前日期+时间 年月日 时分秒 */

---

– **************** 排序查询 *********

---

/*  order by 排序的列 asc 升序(从低到高)
	order by 排序的列 desc 降序(从高到低) */

– 37.对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。
select name,sal from emp
order by sal asc;
/* 默认就是升序，所以asc可以省略不写 */

– 38.对emp表中所有员工奖金进行降序(从高到低)排序，显示员工姓名、奖金。
select name,bonus from emp
order by bonus desc;

---

– *********** 分页查询 ***********

---

/*	在mysql中，通过limit进行分页查询：
	limit (页码-1)*每页显示记录数, 每页显示记录数 */

– 39.查询emp表中的所有记录，分页显示：每页显示5条记录，返回第 1 页。
select * from emp limit 0,5;
– 40.查询emp表中的所有记录，分页显示：每页显示5条记录，返回第 2 页。
select * from emp limit 5,5;
select * from emp limit 10,5;
select * from emp limit 15,5;
…

---

– 三、外键

---

– 准备数据： 以下练习将使用db20库中的表及表记录，请先进入db20数据库!!!
– 准备数据： 以下练习将使用db20库中的表及表记录，请先进入db20数据库!!!
– 外键约束：用来通知数据库两张表列与列之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键。
– 添加外键: foregin key(dept_id) references dept(id)

– 41.尝试删除dept表中的某一个部门

/*	上面的部门删除成功后，员工表里的某些员工就没有了对应的部门,
	这种我们称之为数据的完整性被破坏了,
	为了避免这种情况，可以在删除之前，查看将要删除的部门下是否还有员工存在，如果有就不要删除;
	或者，让数据库帮我们去维护这样的对应关系，也就是当将要被删除的部门下如果还有员工，
	就阻止删除操作，让数据库帮我们维护这样的对应关系，就需要指定外键。 */

– 42.重新创建db20中的dept和emp表，在创建时，指定emp表中的dept_id列为外键，即这一列要严格参考dept表中的id列, 再次尝试删除dept表中的某一个部门，查看是否能删除成功

---

– 四、关联查询、外连接查询

---

– 准备数据： 以下练习将使用db30库中的表及表记录，请先进入db30数据库!!!
– 准备数据： 以下练习将使用db30库中的表及表记录，请先进入db30数据库!!!
– 43.查询部门和部门对应的员工信息
select * from dept,emp;
/* 上面的查询中包含大量错误的数据, 一般不使用这种查询。
如果只想保留正确的记录，可以通过where条件进行筛选，将符合条件的保留下来，不符合条件的自然就会被剔除。*/
select * from dept,emp
where emp.dept_id = dept.id;

– 44.查询所有部门和部门下的员工，如果部门下没有员工，员工显示为null
select * from dept left join emp
on emp.dept_id=dept.id;

– 45.查询部门和所有员工，如果员工没有所属部门，部门显示为null
select * from dept right join emp
on emp.dept_id=dept.id;

/*	左外连接查询：是将左边表中所有数据都查询出来, 如果在右边表中没有对应的记录, 右边表显示为null即可。
	右外连接查询：是将右边表中所有数据都查询出来, 如果在左边表中没有对应的记录, 左边表显示为null即可。*/

---

– 五、子查询、多表查询

---

– 准备数据： 以下练习将使用db40库中的表及表记录，请先进入db40数据库!!!
– 准备数据： 以下练习将使用db40库中的表及表记录，请先进入db40数据库!!!

– 46.列出薪资比’王海涛’薪资高的所有员工，显示姓名、薪资
– 假设’王海涛’的薪资为2500,列出薪资比2500高的所有员工信息
select name, sal from emp
where sal>2450;
– 查询’王海涛’的薪资
select sal from emp where name=‘王海涛’;
– 合并查询结果
select name, sal from emp
where sal>( select sal from emp where name=‘王海涛’ );

– 47.列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位、部门编号。
– 假设’刘沛霞’的职位为’办事员’,求所有职位为’办事员’的员工信息
select name,job,dept_id from emp
where job=‘办事员’;
– 查询’刘沛霞’的职位
select job from emp where name=‘刘沛霞’;
– 合并查询
select name,job,dept_id from emp
where job=(select job from emp where name=‘刘沛霞’);

– 48.列出薪资比’大数据部’部门(已知部门编号为30)所有员工薪资都高的员工信息，显示员工姓名、薪资和部门名称。
– 外连接查询员工表和部门表,查询所有员工
select e.name, sal, d.name
from emp e left join dept d
on e.dept_id=d.id;
– 求出大数据部门最高薪资
select max(sal) from emp
where dept_id=30;
– 合并查询
select e.name, sal, d.name
from emp e left join dept d
on e.dept_id=d.id
where sal>(
select max(sal) from emp
where dept_id=30
);

– 49.列出在’培优部’任职的员工，假定不知道’培优部’的部门编号， 显示部门名称，员工名称。
– 关联查询两张表
select d.name, e.name
from dept d, emp e
where d.id=e.dept_id;
– 求出在培优部的员工
select d.name, e.name
from dept d, emp e
where d.id=e.dept_id
and d.name=‘培优部’;

– 50.(自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
/*
查询的列: e1.name, e2.id, e2.name
emp e1: 员工表
emp e2: 上级表
关联条件: e1.topid=e2.id */
select e1.name, e2.id, e2.name
from emp e1, emp e2
where e1.topid=e2.id;

– 51.列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资
– 根据职位进行分组,求出每种职位的最低薪资
select job, min(sal) from emp
group by job;
– 求出最低薪资大于1500的职位
select job, min(sal) from emp
group by job
having min(sal)>1500;

/*	where和having都用于筛选过滤，但是：
	where用于在分组之前进行筛选, having用于在分组之后进行筛选
	并且where中不能使用列别名和聚合函数, having中可以使用别名和聚合函数 */

– 52.列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。
/*
列: dept_id, count(*), avg(sal)
表: emp
分组: dept_id /
select dept_id,count() 人数, avg(sal) 平均薪资
from emp
group by dept_id;

– 53.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
– 关联查询两张表(dept, emp)
select * from dept d, emp e
where d.id=e.dept_id;
– 替换要显示的列和统计部门人数
select d.id,d.name,d.loc,count(*)
from dept d, emp e
where d.id=e.dept_id
group by d.name;

– 54.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
/*
列: e1.id, e1.name, d.name
表: emp e1: 员工表
emp e2: 上级表
dept d: 部门表
关联条件: e1.topid=e2.id
e1.dept_id=d.id
e1.hdate<e2.hdate */
select e1.id, e1.name, d.name
from emp e1, emp e2, dept d
where e1.topid=e2.id and
e1.dept_id=d.id and
e1.hdate<e2.hdate;

– 55.列出每个部门薪资最高的员工信息，显示部门编号、员工姓名、薪资
– 查询emp表中所有员工的部门编号、姓名、薪资
select dept_id, name, sal
from emp;
– 查询emp表中每个部门的最高薪资，显示部门编号、最高薪资
select dept_id, max(sal) from emp
group by dept_id;
– 第二次查询的结果作为一张临时表和第一次查询进行关联查询
select emp.dept_id, name, sal
from emp,(select dept_id, max(sal) maxsal from emp group by dept_id) t
where emp.dept_id=t.dept_id
and emp.sal=t.maxsal;

/*
1、如何同时查询部门和员工的信息
select * from dept,emp;
– 上面的查询结果中包含大量错误的数据,这种查询叫做"笛卡尔积查询"
所谓的笛卡尔积查询就是,如果有两张表,一张表有m条数据, 另外一张表有n条数据,笛卡尔积查询就是m*n条
2、如何查询部门和对应的员工信息
select * from dept,emp
where dept_id=dept.id;
3、如何查询所有部门和对应的员工信息（这里要查询所有部门，即使某些部门没有员工，员工显示为null即可）
select * from dept left join emp
on dept_id=dept.id;
4、如何查询所有员工和对应的部门信息（这里要查询所有员工，即使某些员工没有部门，部门显示为null即可）
select * from dept right join emp
on dept_id=dept.id;
– 如果是查询两张甚至两张以上的表,在查询时,只想查询两张表中对应的数据,此时请使用关联查询!!
– 如果是查询两张甚至两张以上的表,在查询时,要获取其中一张表的所有记录(比如,所有部门或者所有员工)，此时可以使用外连接查询，因为只有外连接查询才会在关联的基础上，显示其中一张表的所有数据！！
*/

/* 插入数据失败的解决方案!!
1、创建库使用的编码也是utf8
create database mydb1 charset utf8;
创建库时,如果不指定编码,编码可能是latin1,latin1中不能保存中文数据!!
2、建表时,即使不指定,表的编码默认和数据库的编码一致
3、插入记录之前, 设置set names gbk;
由于cmd发送的数据编码默认的gbk
所以这里需要指定服务器接收数据的编码也是gbk
服务器将GBK的数据存入mydb1(utf8)会进行数据的转换!!
*/

/* ---------------------------------
where子句中不能使用列别名(可以使用表别名), 因为where子句比select先执行!!
SQL语句的书写顺序：
select…
from…
where…
group by…
having…
order by…
limit…
…
SQL语句的执行顺序：
from… – 确定要查询的是哪张表 (定义表别名)
where… – 从整张表的数据中进行筛选过滤
select… – 确定要显示哪些列 (定义列别名)
group by… – 根据指定的列进行分组
having… – 对分组之后的数据进行筛选过滤
order by… – 根据指定的列进行排序
…
----------------------------------------*/