国网技术学院MySQL数据库课堂练习

一、数据库的安装和配置

1. 在虚拟机中安装WINDOWS7操作系统
   正常安装，查询安装教程即可
2. 安装必备组件和MYSQL5.7数据库
   正常安装，查询安装教程即可
3. 修改my.ini配置文件中关于character_set的相关设置
   
   

• 直接补下面的代码即可，作用为设置默认的内部操作字符集

character_set_server=utf8

4. 通过操作系统服务界面停止和启动服务，通过命令行方式启动和停止服务（作者是用mysql8），在linux系统中被称为守护进程

• 方法一，通过直接查找服务启动和停止
  

• 通过命令行停止和启动
  

net stop mysql80

net start mysql80

二、数据库的创建

1. 创建2个数据库 db_studentinfo和db_student

• 查看所有数据库

show databases

• 创建 db_studentinfo 数据库

create database db_studentinfo

• 创建 db_student 数据库

create database db_student

2. 使用包含if no exists选项的命令创建db_student和db_student1观察反馈

create database if not exists db_student;

create database if not exists db_student1;

3. 删除数据库db_student1和db_studentinfo

DROP DATABASE db_student1

DROP DATABASE db_studentinfo

4. 使db_student成为当前数据库

USE db_studentinfo

三、简单查询语句的练习题

• 练习初始化sql文件

1. 使company为当前数据库

USE company;

2. 显示表employees的结构，并查询其中全部数据

SELECT *
FROM employees

3. 显示表departments 的结构，并查询其中的全部数据

SELECT *
FROM departments 

4. 查询部门ID为90的员工所有信息

SELECT *
FROM departments 
WHERE departments.department_id='90'

5. 查询没有奖金的所有人员的员工ID，姓名，和工资

SELECT employee_id,first_name,salary
FROM employees 
WHERE salary=NULL OR salary=0

6. 查询工资大于12000的员工姓名和工资

SELECT first_name,salary
FROM employees 
WHERE salary>12000

7. 查询员工号为176的员工的姓名和部门号

SELECT first_name,department_id
FROM employees 
WHERE employee_id=176

8. 查询员工姓名，员工ID号，员工12个月的工资总和，并起别名为“年
   度工资”

SELECT employee_id,first_name,salary*12 as '年度工资'
FROM employees 

9. 查询员工姓名，员工ID号，员工12个月的收入总和（包含奖金），并
   起别名为“年度收入”

SELECT employee_id,first_name,salary*12*(1+IFNULL(commission_pct,0)) as '年度收入'
FROM employees 

10. 查询employees表中去除重复的job_id以后的数据

SELECT DISTINCT job_id
FROM employees 

11. 查询工资不在5000到12000的员工的姓名和工资

SELECT first_name,salary
FROM employees 
WHERE salary<5000 OR salary>12000

12. 查询在20或50号部门工作的员工姓名和部门号

SELECT first_name,department_id
FROM employees 
WHERE department_id=20 OR department_id=50

13. 查询公司中没有管理者的员工姓名及job_id

SELECT first_name,job_id,manager_id
FROM employees 
WHERE manager_id IS NULL

14. 查询公司中有奖金的员工姓名，工资和奖金级别

SELECT first_name,salary,commission_pct
FROM employees 
WHERE commission_pct IS NOT NULL

15. 查询员工姓名的第三个字母是a的员工姓名

SELECT first_name
FROM employees 
WHERE first_name like '__a%'

16. 查询姓名中有字母a和k的员工姓名

SELECT first_name
FROM employees 
WHERE first_name like '%a%' and first_name like '%k%'

17. 显示出表 employees 表中 first_name 以 'e’结尾的员工信息

SELECT first_name
FROM employees 
WHERE first_name like '%a%' and first_name like '%k%'

18. 显示出表 employees 部门编号在 80-100 之间的姓名、工种

SELECT first_name,job_title
FROM employees
JOIN jobs ON employees.job_id=jobs.job_id
WHERE department_id BETWEEN 80 AND 100

19. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id

SELECT first_name,salary,department_id
FROM employees
WHERE department_id in (100,101,110)

20. 查询所有50号部门人员的员工ID，名字，部门名称，部门所在地

SELECT first_name,department_name,street_address
FROM employees
JOIN departments ON employees.department_id=departments.department_id
JOIN locations ON departments.location_id=locations.location_id
WHERE employees.department_id=50

21. 查询所有IT部门人员的员工ID，部门名称，部门所在地和邮政编码

SELECT first_name,departments.department_name,locations.street_address,locations.postal_code
FROM employees
JOIN departments ON employees.department_id=departments.department_id
JOIN locations ON departments.location_id=locations.location_id
WHERE departments.department_name='IT'

四、创建和管理数据库及表

1. 创建数据库sgccit1,指明字符集为utf8,并在此数据库下执行下述操作

create database if not exists sgccit1 CHARSET utf8mb3 collate utf8mb3_general_ci;

USE sgccit1

# 查看字符集
SELECT @@character_set_database;
#查看排序字符集
SELECT @@collation_database;

a. 创建表it01,包含字段id int(7),it_name varchar(25)

CREATE TABLE it01(
	id int(7),
	it_name varchar(25)
)

b. 创建表it02,所有数据和表结构来自taian2023数据库的employees表

CREATE TABLE it02
as
SELECT *
FROM taian2023.employees

c. 创建表it03,id INT(7);first_name VARCHAR (25);last_name VARCHAR(25);dept_id INT(7)

CREATE TABLE if not exists it03(
	id INT(7),
	first_name VARCHAR (25),
	last_name VARCHAR(25),
	dept_id INT(7)
)

d. 修改表it03的first_name的长度为50,last_name的长度改为50

ALTER TABLE it03 MODIFY COLUMN first_name VARCHAR (50);
ALTER TABLE it03 MODIFY COLUMN last_name VARCHAR (50);

e. 删除表it01

DROP TABLE if EXISTS it01

f. 将表it02重命名为it01

RENAME TABLE it02 TO it01

g. 在表it01和it02中都增加一列test_column varchar(10)

CREATE TABLE it02(
	id int(7),
	it_name varchar(25)
)

ALTER TABLE it01 ADD COLUMN test_column varchar(10);
ALTER TABLE it02 ADD COLUMN test_column varchar(10);

2. 创建数据库Market设置字符集为utf8

create database if not exists Market CHARSET utf8mb3 collate utf8mb3_general_ci;

USE Market;

a. 创建数据表 customers包含以下字段：c_num INT,c_name VARCHAR(50),c_contact VARCHAR(50),c_city
VARCHAR(50),c_birth DATE

CREATE TABLE customers(
	c_num INT,
	c_name VARCHAR(50),
	c_contact VARCHAR(50),
	c_city VARCHAR(50),
	c_birth DATE
)

b. 在c_num字段上添加主键约束和自增约束，在c_birth上增加非空约束

ALTER TABLE customers modify COLUMN c_num INT auto_increment PRIMARY KEY;
ALTER TABLE customers modify COLUMN c_birth DATE NOT NuLL;

c. 将 c_contact 字段移动到 c_birth 字段后面

ALTER TABLE customers modify COLUMN c_contact VARCHAR(50) after c_birth;

d. 将 c_name 字段数据类型改为 varchar(70)

ALTER TABLE customers modify COLUMN c_name VARCHAR(70);

e. 将c_contact字段改名为c_phone

alter table customers change c_contact c_phone VARCHAR(50);

f. 增加c_gender字段到c_name后面，数据类型为char(1)

ALTER TABLE customers ADD COLUMN c_gender char(1) AFTER c_name;

3. 创建数据库sgccit3 设置字符集为utf8

create database if not exists sgccit3 CHARSET utf8mb3 collate utf8mb3_general_ci;

USE sgccit3;

a. 创建表offices包含以下字段：officeCode INT,city VARCHAR(30),address VARCHAR(50),country VARCHAR(50),postalCode VARCHAR(25)

CREATE TABLE offices(
	officeCode INT,
	city VARCHAR(30),
	address VARCHAR(50),
	country VARCHAR(50),
	postalCode VARCHAR(25)
)

b. 创建表employees包含以下字段:empNum INT,lastName VARCHAR(50),firstName VARCHAR(50),mobile VARCHAR(25),code INT,jobTitle VARCHAR(50),birth DATE,note VARCHAR(255),sex VARCHAR(5)

CREATE TABLE employees(
	empNum INT,
	lastName VARCHAR(50),
	firstName VARCHAR(50),
	mobile VARCHAR(25),
	`code` INT,
	jobTitle VARCHAR(50),
	birth DATE,
	note VARCHAR(255),
	sex VARCHAR(5)
)

c. 将表employees的mobile字段修改到code字段后面

ALTER TABLE employees modify COLUMN mobile VARCHAR(25) after code;

d. 将表employees的birth字段改名为birthday

alter table employees change birth birthday DATE;

e. 修改sex字段，数据类型为char(1)

ALTER TABLE employees modify COLUMN sex char(1);

f. 删除字段note

alter table employees drop column note;

g. 增加字段名favoriate_activity，数据类型为varchar(100)

alter table employees add column favoriate_activity varchar(100);

h. 将表employees的名称修改为 employees_info

RENAME TABLE employees TO employees_info;

4. 创建数据库db_pet设置字符集为utf8

create database if not exists db_pet CHARSET utf8mb3 collate utf8mb3_general_ci;

USE db_pet;

a. 创建pet表包含以下字段：NAME VARCHAR(20),OWNER
VARCHAR(20),species VARCHAR(20),sex CHAR(1),birth
YEAR,death YEAR，并添加下图记录
b.

NAME	OWNER	species	sex	birth	death
‘Fluffy’	‘harold’	‘Cat’	‘f’	2003	2010
‘Claws’	‘gwen’	‘Cat’	‘m’	2004	null
‘Buffy’	null	‘Dog’	‘f’	2009	null
‘Fang’	‘benny’	‘Dog’	‘m’	2000	null
‘Bowser’	‘diane’	‘Dog’	‘m’	2003	2009
‘Chirpy’	null	‘Bird’	‘f’	2008	null

CREATE TABLE if not exists pet(
	NAME VARCHAR(20),
	OWNER VARCHAR(20),
	species VARCHAR(20),
	sex CHAR(1),
	birth YEAR,
	death YEAR
)

INSERT INTO pet
(name,owner,species,sex,birth,death)
VALUES
('Fluffy','harold','Cat','f',2003,2010),
('Claws','gwen','Cat','m',2004,null),
('Buffy',null,'Dog','f',2009,null),
('Fang','benny','Dog','m',2000,null),
('Bowser','diane','Dog','m',2003,2009),
('Chirpy',null,'Bird','f',2008,null);

c. 添加字段:主人的生日owner_birth DATE类型

ALTER TABLE pet ADD COLUMN owner_birth DATE;

d. 将名称为Claws的猫的主人改为kevin

UPDATE pet
SET owner='kevin'
WHERE name='Claws'

e. 将没有死的狗的主人改为duck

UPDATE pet
SET owner='duck'
WHERE death is NULL

f. 查询没有主人的宠物的名字；

SELECT name
FROM pet
WHERE owner is NULL

g. 查询已经死了的cat的姓名，主人，以及去世时间；

SELECT name,owner,death
FROM pet
WHERE death is not NULL

h. 删除已经死亡的狗

DELETE 
FROM pet
WHERE species='Dog' and death is not NULL 

i. 查询所有宠物信息

SELECT *
FROM pet

5. 创建数据库db_company设置字符集为utf8

create database if not exists db_company CHARSET utf8mb3 collate utf8mb3_general_ci;

a. 切换db_company为当前数据库

USE db_company

b.建立一个表offices结构如下 officeCode int(10),city varchar(50),address varchar(50),country varchar(50),postalCode varchar(15)
c. 将officeCode设置为主键
d. 将city设为非空
e. 将country设置为非空

CREATE TABLE offices(
	officeCode int(10) PRIMARY KEY,
	city varchar(50) NOT NULL,
	address varchar(50),
	country varchar(50) NOT NULL,
	postalCode varchar(15)
)

f.全局方式将postalCode设置为UNIQUE,且命名为uk_off_poscode

ALTER TABLE offices CHANGE country uk_off_poscode varchar(50) UNIQUE;

g. 建立一个表employees结构如下，数据类型请合理自选
employeeNumber int,lastName,firstName,mobile,officeCode int(10),jobTitle,brith,note,sex
h. 将employeeNumber设置为主键
i. 将lastName,firstName,officeCode,jobTitle,birth设置为非空
j. 将mobile设置为唯一
k. 全局模式建立officeCode的外键，主表字段为表officese的officeCode

CREATE TABLE employees(
	employeeNumber int PRIMARY KEY,
	lastName VARCHAR(32) NOT NULL,
	firstName VARCHAR(32) NOT NULL,
	mobile VARCHAR(32) UNIQUE,
	officeCode  int(10) NOT NULL,
	jobTitle VARCHAR(64) NOT NULL,
	brith VARCHAR(16),
	note VARCHAR(64),
	sex char(1),
	FOREIGN KEY (officeCode) REFERENCES officese(officeCode)
)

l. 将表employees的mobile字段修改到officeCode字段后面

#并不会让 UNIQUE 索引消失
ALTER TABLE employees MODIFY COLUMN mobile VARCHAR(32) AFTER officeCode;

m. 将表employees的birth字段改名为employee_birth

ALTER TABLE employees CHANGE birth employee_birth VARCHAR(16);

n. 修改sex字段，数据类型为CHAR(1)，非空约束

ALTER TABLE employees MODIFY COLUMN sex CHAR(1) NOT NULL;

o. 删除字段note

ALTER TABLE employees DROP COLUMN note;

p. 增加字段名favoriate_activity，数据类型为VARCHAR(100)

ALTER TABLE employees ADD COLUMN favoriate_activity VARCHAR(100);

q. 将表employees名称修改为employees_info

ALTER TABLE employees RENAME employees_info;

RENAME TABLE employees TO employees_info;

五、三班科目考试题

1. 建立一个国网新员工三期培训班学员数据库db_emp3，设定为utf8字符集

CREATE DATABASE IF NOT EXISTS db_emp3 CHARSET utf8mb3 collate utf8mb3_general_ci;

USE db_emp3;

a. 按照如下字段建立学员表tb_stud。

字段名	数据类型	含义	约束
stud_id	int	学员ID
stud_name	varchar(25)	学员姓名	非空约束
stud_nickname	varchar(25)	学员昵称和外号	非空且唯一
stud_gender	varchar(10)	学员性别	非空约束
stud_phone	varchar(11)	电话号码	唯一性约束
stud_classid	int	学员所属班级ID	非空约束

b. 为stud_id字段建立命名为pk_stud_id的表级主键约束。

CREATE TABLE tb_stud(
	stud_id int,
	stud_name varchar(25) NOT NULL,
	stud_nickname varchar(25) NOT NULL UNIQUE,
	stud_gender varchar(10) NOT NULL,
	stud_phone varchar(11) UNIQUE,
	stud_classid int NOT NULL,
	CONSTRAINT pk_stud_id PRIMARY KEY (stud_id)
)

c.按照如下字段建立班级表tb_class。

字段名	数据类型	含义	约束
c_classid	int	班级ID	主键约束
c_name	varchar(15)	班级名称	唯一性约束
c_monitor	varchar(10)	班长姓名	非空约束
c_instructor	varchar(10)	辅导员姓名	非空约束

CREATE TABLE tb_class(
	c_classid int PRIMARY KEY,
	c_name varchar(15) UNIQUE,
	c_monitor varchar(10) NOT NULL,
	c_instructor varchar(10) NOT NULL
)

d. 使用单独的命令建立tb_stud表stud_classid的外键，命名为
fk_class_id，引用tb_class表的c_classid字段。

ALTER TABLE tb_stud ADD CONSTRAINT fk_class_id FOREIGN KEY (stud_classid) REFERENCES tb_class(c_classid);

e. 按照下表输入tb_class数据。

班级ID	班级名称	班长姓名	辅导员姓名
1	信息检修一班	曹操	张飞
2	信息检修二班	张辽	刘备
3	信息检修三班	典韦	关羽
4	信息检修四班	李典	赵云
5	信息检修五班	张郃	马超

INSERT INTO
tb_class(c_classid,c_name,c_monitor,c_instructor)
VALUES
(1,'信息检修一班','曹操','张飞'),
(2,'信息检修二班','张辽','刘备'),
(3,'信息检修三班','典韦','关羽'),
(4,'信息检修四班','李典','赵云'),
(5,'信息检修五班','张郃','马超')

f. 在tb_stud中输入如下十名学员的信息。

学员ID	姓名	昵称	性别	电话号码	班级ID
1	宋江	呼保义	男	13100000000	3
2	卢俊义	玉麒麟	男	13200000000	3
3	吴用	智多星	男	13300000000	2
4	柴进	小旋风	男	13400000000	2
5	武松	行者	男	13500000000	1
6	扈三娘	一丈青	女	13600000000	1
7	时迁	鼓上蚤	男	13700000000	4
8	鲁智深	花和尚	男	13800000000	4
9	林冲	豹子头	男	13900000000	5
10	杨志	青面兽	男	18800000000	5
11	李逵	黑旋风	男	18900000000	2

INSERT INTO
tb_stud(stud_id,stud_name,stud_nickname,stud_gender,stud_phone,stud_classid)
VALUES
(1,'宋江','呼保义','男','13100000000',3),
(2,'卢俊义','玉麒麟','男','13200000000',3),
(3,'吴用','智多星','男','13300000000',2),
(4,'柴进','小旋风','男','13400000000',2),
(5,'武松','行者','男','13500000000',1),
(6,'扈三娘','一丈青','女','13600000000',1),
(7,'时迁','鼓上蚤','男','13700000000',4),
(8,'鲁智深','花和尚','男','13800000000',4),
(9,'林冲','豹子头','男','13900000000',5),
(10,'杨志','青面兽','男','18800000000',5),
(11,'李逵','黑旋风','男','18900000000',2)

g. 在表tb_stud中删除学员ID为4的记录。

DELETE
FROM tb_stud
WHERE tb_stud.stud_id=4

h. 修改表tb_stud结构，增加一列所属省公司，字段名为stud_prov数据类型合理范围内自定义，不设置约束，并保存语句截图和表结构查询截图。

ALTER TABLE tb_stud ADD COLUMN stud_prov VARCHAR(64);

i. 构造一个多表查询，查询所有学员的姓名，昵称，班级名称，班长姓名和辅导员姓名，输出时将昵称和姓名组合在一起形成一个字段显示，截图命令和输出结果。

SELECT tb_stud.stud_name,tb_stud.stud_nickname,tb_class.c_name,tb_class.c_monitor,tb_class.c_instructor
FROM tb_stud
JOIN tb_class ON tb_stud.stud_classid=tb_class.c_classid

六、创建用户的课堂实操：

a. 创建一个aric的用户，所有主机可以登录，初始密码设置为abcdef。该用户对db_backup数据库下所有表拥有查询，更新和删除的权限。并进行如下操作：

CREATE USER `aric`@`%` IDENTIFIED BY 'abcdef';
GRANT Delete, Select, Update ON `db_backup`.* TO `aric`@`%`;

b. 使用root用户将其密码修改为’aaabbb’。

alter user `aric`@`%` identified by 'aaabbb'; 

c. 查看aric用户的权限

show grants for `aric`@`%`;

#查看当前用户的权限
show grants;

d. 收回aric用户的删除权限

revoke Delete on db_backup.* from `aric`@`%`;

#刷新权限
flush privileges;

e. 删除aric用户

DROP USER `aric`@`%`;

七、备份的课堂练习

a. 备份company数据库到桌面自定文件夹命名为company1.sql

#就是正常生成sql文件
mysqldump -uroot -proot --databases company >C:\Users\ljj\Desktop\company1.sql

b. 备份company数据库的employees表到桌面自定文件夹emp_1.sql

#就是正常生成sql文件
mysqldump -uroot -proot --databases company --tables employees>C:\Users\ljj\Desktop\emp_1.sql

c. 备份company数据库的departments表到桌面自定文件dep_1.sql

#就是正常生成sql文件
mysqldump -uroot -proot --databases company --tables employees>C:\Users\ljj\Desktop\dep_1.sql

d. 删除company数据库

Drop DATABASE company;

e. 把备份的company1.sql恢复到数据库管理系统中

#就是正常使用生成sql文件
mysql -uroot -proot --databases company <C:\Users\ljj\Desktop\company1.sql

f. 新建数据库company2

CREATE DATABASE IF NOT EXISTS company2 CHARSET utf8mb3 collate utf8mb3_general_ci

g. 把备份的employees和departments表恢复到company2

mysql -uroot -proot --databases company2 --tables employees<C:\Users\ljj\Desktop\emp_1.sql
mysql -uroot -proot --databases company2 --tables employees<C:\Users\ljj\Desktop\dep_1.sql