SQL/MySQL进阶及查询练习

课程参考

阿里云开发者社区——数据库学习路线

mysql编码问题

1、查看MySQL数据库编码

mysql> show variables like 'char%';

Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8mb4
character_set_system	utf8
character_sets_dir	/usr/local/mysql-8.0.20-macos10.15-x86_64/share/charsets/

• character_set_client客户端接受数据
• character_set_results客户端返回数据

2、修改编码 set character_set_client=gbk 设置只在当前窗口内有效，关闭窗口后还是utf8mb4，因此需设置set character_set_results=gbk，或到my.ini修改总配置文件

[client]
port=3306
[mysql]
Default-character-set=gbk

具体细节还可参考这里

注意可以修改三个变量client、results、connection，然后net stop mysql停止服务器，net start mysql重启服务器，即可完成设置。

备份与恢复

1、数据库导出sql脚本（备份数据库内容，并不是备份数据库）

$ mysqldump -u root -p test emp>/Users/liuyiming/博一下/emp.sql

2、导入sql脚本

$ mysql -u root -p test</Users/liuyiming/博一下/emp2.sql

注意如果数据库删除了，你只有数据库内容（表），则导入时应先创建数据库，再用上述语句导入sql脚本 不要打分号，不要登录，直接在cmd下运行

3、导入sql脚本另一种写法，需登录后，进入数据库

mysql> source /Users/liuyiming/博一下/emp2.sql

约束

是添加在列上，用来约束列的

1、主键约束（唯一标识）：非空、唯一、被引用（外键引用主键）

• 创建表时指定主键的两种方式：

mysql> create table mydb(
    -> empno int primary key,
    -> ename varchar(50)
    -> );
mysql> create table mydb(
    -> empno int,
    -> ename varchar(50),
    -> primary key(empno)
    -> );

• 表已存在指定主键

mysql> alter table mydb add primary key(empno);

• 删除主键

mysql> alter table mydb drop primary key;

2、主键自增长：由于唯一、非空的特点，通常将主键类定义为整型，并设置自增长，不用再手动插入

mysql> create table mydb(
    -> emptno int primary key auto_increment,
    -> ename varchar(50)
    -> );
mysql> insert into mydb values(1111, 'zhangSan');
mysql> insert into mydb values(NULL, 'liSi');
mysql> select * from mydb;

emptno	ename
1111	zhangSan
1112	liSi

mysql> delete from mydb;
mysql> insert into mydb values(NULL, 'zhangSan');
mysql> select * from mydb;

emptno	ename
1113	zhangSan

• 实际使用时，应单独创建一列为主键列，与内容区别开来，例如取名为ID
• 在两个不同数据库中插入主键，可能相同，违背主键唯一特点，因此推荐使用UUID作为主键

3、非空约束：设置某些列不能有NULL值

mysql> create table mydb(
    -> emptno int primary key auto_increment,
    -> ename varchar(50) not null,
    -> );

4、唯一约束：设置某些列不能有重复值

mysql> create table mydb(
    -> emptno int primary key auto_increment,
    -> ename varchar(50) not null unique,
    -> );

即便某列有唯一且非空约束它也不能代替主键约束，因为不能被引用

5、概念模型

• 对象模型（Java的类），可以双向关联，而且引用的是对象，不是一个主键
• 关系模型（数据库的表），只能多方引用一方，而且引用的只是主键，而不是一整行记录
• 实体之间的关系：1对多，1对1，多对多
• 其他称呼：1方是主表，多方是从表

6、外键约束：一张表有一个主键，同时也可能包含一个外键，它是另一张表（或者本表）的主键

• 特点：引用主键、可以重复、可以为空、可以有多个外键

假设存在两张表：员工表 etest，部门表 dtest

mysql> create table etest(
    -> empno int primary key auto_increment,
    -> ename varchar(50),
    -> deptno int,
    -> constraint fk_etest_dtest foreign key(deptno) references dtest(deptno)
    -> );
mysql> alter table etest
    -> add constraint fk_etest_dtest foreign key(deptno) references dtest(deptno)

三种关联关系

1、一对一

将从表的主键作为外键

mysql> create table husband(
    -> hid int primary key auto_increment,
    -> hname varchar(50)
    -> );
mysql> insert into husband values(NULL, 'zhangSan’);
mysql> insert into husband values(NULL, 'liSi’);
mysql> insert into husband values(NULL, 'wangWu’);
mysql> create table wife(
    -> wid int primary key auto_increment,
    -> wname varchar(50),
    -> constraint fk_wife_hasband foreign key(wid) references husband(hid)
    -> );

wid列唯一、非空、引用hid，因此wid列的取值必须与hid的取值相同，且不能重复，如

mysql> insert into husband values(1, 'zhaoliu’);
mysql> insert into husband values(2, ‘liuqi’);

即wife表中若有一条记录的wid是1，其他记录就不能再是1，因为它是主键；同时在hid中必须存在1这个值，因为wid是外键，这就完成一对一关系。

2、多对多

使用中间表，即需要三张表，在中间表中使用两个外键，分别引用其他两个表的主键

mysql> create table student(
    -> sid int primary key auto_increment,
    -> sname varchar(50)
    -> );
mysql> create table teacher(
    -> tid int primary key auto_increment,
    -> tname varchar(50)
    -> );
mysql> create table stu_tea(
    -> sid int,
    -> tid int,
    -> constraint fs_student foreign key(sid) references student(sid),
    -> constraint fs_teacher foreign key(tid) references teacher(tid)
    -> );
mysql> insert into student values(NULL, 'zhangSan');
mysql> insert into student values(NULL, 'liSi');
mysql> insert into student values(NULL, 'wangWu');
mysql> insert into student values(NULL, 'zhaoLiu');
mysql> insert into student values(NULL, 'qianQi');
mysql> insert into teacher values(NULL, 'chui');
mysql> insert into teacher values(NULL, 'liu');
mysql> insert into teacher values(NULL, 'shi');
mysql> insert into stu_tea values(1,1);
mysql> insert into stu_tea values(2,1);
mysql> insert into stu_tea values(3,1);
mysql> insert into stu_tea values(4,1);
mysql> insert into stu_tea values(5,1);
mysql> insert into stu_tea values(2,2);
mysql> insert into stu_tea values(3,2);
mysql> insert into stu_tea values(4,2);
mysql> insert into stu_tea values(3,3);
mysql> insert into stu_tea values(4,3);
mysql> insert into stu_tea values(5,3);

stu_tea表中存储的就是多对多关系

多表查询

一次性查询多个表，包含三种：合并结果集（了解），连接查询，子查询

1、合并结果集

要求被合并的结果集中，列的类型和列数相同，即结果集的结构一模一样

mysql> select * from ab
    -> union all
    -> select * from cd;

完全相同的行不会被去除

mysql> select * from ab
    -> union
    -> select * from cd;

完全相同的行会被去除

mysql> select deptno from emp
    -> union all
    -> select deptno from dept;

注意是结果集的表结构相同，就能合并

2、连接查询

包含：内连接，外连接，自然连接

2.1 内连接

(1) 方言版：笛卡尔积去除，用在跨表查询

当前员工信息及其部门信息

mysql> select * from emp, dept where emp.deptno=dept.deptno;

所有员工的姓名，工资和部门名称

mysql> select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno=dept.deptno;

简化，给表起别名

mysql> select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno;

(2) 标准版

mysql> select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno;

(3) 自然版

mysql> select e.ename, e.sal, d.dname from emp e natural join dept d;

注意：内连接无法查询出“张三”，因为张三对应的50部门不存在

2.2 外连接

外连接有一主一次，左外即左表为主，即emp为主，那么主表中的所有记录无论满足不满足条件，都打印出来，并且使用NULL补位

mysql> select e.ename, e.sal, d.dname from emp e left outer join dept d on e.deptno=d.deptno;

ename	sal	dname
甘宁	8000	学工部
黛绮丝	16000	销售部
殷天正	12500	销售部
刘备	29750	学工部
谢逊	12500	销售部
关羽	28500	销售部
张飞	24500	教研部
诸葛亮	30000	学工部
曾阿牛	50000	教研部
韦一笑	15000	销售部
周泰	11000	学工部
程普	9500	销售部
庞统	30000	学工部
黄盖	13000	教研部
张三	80000	NULL

mysql> select e.ename, e.sal, ifnull(d.dname, ‘无部门’) as dname from emp e left outer join dept d on e.deptno=d.deptno;

SQL题：查询所有员工名称，工资以及部门名称，当用外连接，考虑全面

2.3 右外连接

mysql> select e.ename, e.sal, d.dname from emp e right outer join dept d on e.deptno=d.deptno;

ename	sal	dname
张飞	24500	教研部
曾阿牛	50000	教研部
黄盖	13000	教研部
甘宁	8000	学工部
刘备	29750	学工部
诸葛亮	30000	学工部
周泰	11000	学工部
庞统	30000	学工部
黛绮丝	16000	销售部
殷天正	12500	销售部
谢逊	12500	销售部
关羽	28500	销售部
韦一笑	15000	销售部
程普	9500	销售部
NULL	NULL	财务部

即没有人在财务部

2.4 全外连接（了解即可）

mysql> select e.ename, e.sal, d.dname from emp e left outer join dept d on e.deptno=d.deptno
—> union
—> select e.ename, e.sal, d.dname from emp e right outer join dept d on e.deptno=d.deptno;

子查询

查询中包含查询（查看select关键字的个数）

查询本公司工资最高的员工的详细信息

mysql> select * from emp where sal=(select max(sal) from emp);

from和where两个位置后都可以出现子查询

mysql> select e.empno, e.ename from (select * from emp where deptno=30) e;

注意要对表起别名

(1) 单行单列

打印工资高于平均工资的所有人

mysql> select * from emp where sal > (select avg(sal) from emp);

(2) 多行单列

打印大于30部门所有人工资的员工的信息

mysql> select * from emp where sal > all (select sal from emp where deptno=30);

打印工资高于任意经理的员工的信息

mysql> select * from emp where sal > any (select sal from emp where job='经理');

(3) 单行多列

打印工资和部门与殷天正完全相同的员工

mysql> select * from emp where (job, deptno) in (select job, deptno from emp where ename='殷天正');

(4) 多行多列，多用在from之后