mysql数据库知识

mysql数据库知识速查

参考的网址:

数据库设计设计规范

https://mp.weixin.qq.com/s?__biz=MzU4ODI1MjA3NQ==&mid=2247490967&idx=2&sn=fc92dc46ad2b6b2c2964c06f6277c4f7&chksm=fddecb53caa94245107a004935add8c0ccb059830f61d31ef404d172c3b07f87c0f576aa9a62&mpshare=1&scene=23&srcid=0923GipnyxKQhFHoObIlFU9W&sharer_sharetime=1600911081363&sharer_shareid=9d1e76e919cc0b2f3ca23ed1f5ef67a8#rd

数据库面试题

https://mp.weixin.qq.com/s?__biz=MzU2NDg0OTgyMA==&mid=2247493318&idx=2&sn=34920087eca41f3ff4f35639c6a269c4&chksm=fc461b35cb3192236e47fa1fd5d5d54b12b06cf3b6087d0abc21156be3b07844ceec06be6738&mpshare=1&scene=23&srcid=0827OaSW1B5IADXkGqmfARs1&sharer_sharetime=1598848018219&sharer_shareid=9d1e76e919cc0b2f3ca23ed1f5ef67a8#rd

数据库基础知识

https://mp.weixin.qq.com/s?__biz=MzU2NDg0OTgyMA==&mid=2247491492&idx=1&sn=27cdbf23efe2b1b6314e208ccb2b0b17&chksm=fc45e257cb326b41c255bdcaca9ac22a4c3346a4f80049ed82a42d786d05be012f2fd0ed7a41&mpshare=1&scene=1&srcid=0721DKHnEax22hglLw2SnUb2&sharer_sharetime=1597989056779&sharer_shareid=9d1e76e919cc0b2f3ca23ed1f5ef67a8&key=14893ff72db1d5ee08e9ad6c7c246ed8574d81d425aaa607a29774e78616fc91d9cbc7a16c83bf51046a329f8b0a7ce32c6aca53f343a81ae9e03d151f40ee7e2048b186a7774ce3fcd6496213fac4f3f6f7d0b68bb8150edf803a3053dd125295cee7d2989c22bfff7988b7135b244628c94aea8435cc8568a192c0f5e54f56&ascene=1&uin=NjU1MDkwNDgx&devicetype=Windows+10+x64&version=62090529&lang=zh_CN&exportkey=AVg9ULWh5zVsZPvL%2BsVREWQ%3D&pass_ticket=ZG6f%2FwYrFvTypRMoKZ8f4pu0tjnD8CAcG8erua0%2BYMiFE%2F3WINpUHq7X7XcjhyOd

https://mp.weixin.qq.com/s?__biz=MzU2NDg0OTgyMA==&mid=2247488824&idx=1&sn=fd7a3bfb1840fb303edfe71e2047992d&chksm=fc45e8cbcb3261dd0862d298a062956283ac0f9e564134356e3396245c5945515b029b6a2ac5&token=392068071&lang=zh_CN&scene=21#wechat_redirect

自己收藏的一个pdf整理了mysql的基础知识
https://gitee.com/shao_ming314/note/blob/master/%E6%95%B0%E6%8D%AE%E5%BA%93%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/MySQL%E7%AC%94%E8%AE%B0.pdf

关于别的数据库的知识参考:
https://gitee.com/GZzzhsmart/database

1 数据库概述

1.1 什么是数据库

数据库：英文为Database，简称DB

数据库是按照数据结构来组织、存储和管理数据的仓库

简而言之，数据库就是存储数据的仓库。

数据库的分类：

数据库根据存储采用的数据结构的不同可以分为许多种，其中包含早期的层次式数据库、网络式数据库。

目前占市场主流的是关系型数据库。当然还有**非关系****(NoSQL)**型数据库（键值对数据库，例如：MongoDB、Redis）等其他类型的数据库

1.2 什么是关系型数据库？

底层是以二维表的及其之间的关系所组成的数据库，即是关系型数据库。例如：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HvpDmzMW-1603174856590)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image002.jpg)]

1.3 常见的关系型数据库

SQL Server 微软提供（收费、Java中使用不多）

Oracle 甲骨文公司（收费、功能强大、性能优异，Java中使用者很多）

DB2 IBM（收费、中型/大型、银行/电信等企业）

MySQL 瑞典MySQL AB（免费开源、小型、性能也不差、适用于中小型项目、可集群）

SQLite 迷你数据库，嵌入式设备中

…

1.4 名称解释

1、数据库服务器

数据库服务器其实就是一个软件，比如我们安装的mysql软件（或者mariadb软件）

mysql服务器软件需要安装在服务器硬件上（就是一台计算机）才可以让外界来访问

2、数据库

在mysql服务器中，可以创建很多的数据库（database）

通常情况下，一个web站点对应一个数据库

3、数据表

在数据库（database）中，可以创建很多张表（table）

通常情况下，一张表用于保存一类数据，例如网站中的所有用户信息会保存在一张表中，所有商品信息会保存在另一张表中。

4、表记录

在数据表（table）中，可以插入很多条表记录

数据表往往保存一类数据，对应java中的一个类。

而一条表记录往往对应java中的一个具体的实例

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-69JjqjFm-1603174856592)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image004.png)]

1.5 什么是SQL语言?

Structured Query Language**：结构化的查询语言**

SQL是操作所有关系型数据库的通用的语言

SQL语言的分类：

a) DDL – 数据库定义语言，指CREATE、ALTER、DROP等操作（ 即创建、删除、修改数据库和数据表 ）

b) DML – 数据操作语言，指INSERT、UPDATE、DELETE等操作（ 即数据表中数据的增、删、改操作 ）

c) DQL – 数据查询语言（指SELECT操作，即数据表中数据的查询操作）

…

其中DQL**，也就是数据查询操作是在开发中使用最多的操作，也是我们学习的重点！！**

2 MySQL数据类型及字段约束

2.1 数值类型

MySQL中支持多种整型，其实很大程度上是相同的，只是存储值的大小范围不同而已。

tinyint：占用1个字节，相对于java中的byte

smallint：占用2个字节，相对于java中的short

int：占用4个字节，相对于java中的int

bigint：占用8个字节，相对于java中的long

其次是浮点类型即：float和double类型

float：4字节单精度浮点类型，相对于java中的float

double：8字节双精度浮点类型，相对于java中的double

2.2 字符串类型

1**、**char(n) 定长字符串，最长255个字符。n表示字符数，例如：

– 创建user表, 指定用户名为char类型，字符长度不超过10

create table user(

username char(10),

…

);

所谓的定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间会用空格填充。（这样会浪费空间）

2**、**varchar(n) 变长字符串，最长不超过 65535个字节，n表示字符数，一般超过255个字节，会使用text类型，例如：

– 创建user表, 指定用户名为varchar类型，长度不超过10

create table user(

username varchar(10)

);

所谓的不定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间可以留给别的数据使用。（节省空间）

3**、大文本（长文本）类型**

最长65535个字节，一般超过255个字符列的会使用text。

– 创建user表,

create table user(

resume text

);

另，text也分多种，其中bigtext存储数据的长度约为4GB。

* 面试题: char(n)、varchar(n)、text都可以表示字符串类型，其区别在于：

(1)char(n)在保存数据时, 如果存入的字符串长度小于指定的长度n，后面会用空格补全，因此可能会造成空间浪费，但是char类型的存储速度较varchar和text快。

因此char类型适合存储长度固定的数据，这样就不会有空间浪费，存储效率比后两者还快！

(2)varchar(n)保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用，因此varchar不会浪费空间。

因此varchar适合存储长度不固定的数据，这样不会有空间的浪费。

(3)text是大文本类型，一般文本长度超过255个字符，就会使用text类型存储。

2.3 日期类型

1**、date**：年月日

2**、time**：时分秒

3**、datetim**e：年月日 时分秒

4**、timestamp**：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。

timestamp最大表示2038年，而datetime范围是1000~9999

timestamp在插入数、修改数据时，可以自动更新成系统当前时间(后面用到时再做讲解)

2.4 字段(列)约束

创建表时, 除了要给每个列指定对应的数据类型, 有时也需要给列添加约束。常见的约束有：主键约束、唯一约束、非空约束、外键约束。

1**、主键(primary key)**

主键是数据表中，一行记录的唯一标识。比如学生的编号，人的身份证号, 例如:

创建user表,指定id为主键, 作为用户的唯一标识

create table stu(

id int primary key,

…

);

主键的特点： 唯一且不能为空！！！

当主键为数值时，为了方便维护，可以设置主键为自增（auto_increment）

示例：创建user表,指定id为主键, 并指定主键自增

create table user(

id int primary key auto_increament,

…

);

自增的作用是, 后期往表中插入记录时, 自增的列可以不用插入值(插入null即可), 数据库会自动插入值(1,2,3…), 例如：

insert into stu values(null, ‘王海涛’,‘男’, ‘1993-1-10’);

insert into stu values(null, ‘刘沛霞’,‘男’, ‘1995-6-15’);

提示：如果建表时没有指定主键自增, 也可以后期修改id为主键自增。

2**、唯一(unique)**

保证所约束的列必须是唯一的，即不能重复出现，例如：用户注册时，保存的用户名不可以重复。

示例：创建user表, 指定用户名不能重复

create table user(

id int primary key auto_increament,

username varchar(50) unique,

…

);

唯一约束特点是不能重复！！(允许为空)

3**、非空(not null)**

保证所约束的列必须是不为空的，即在插入记录时，该列必须要赋值，例如：用户注册时，保存的密码不能为空。

创建user表, 指定密码不能为空

create table user(

id int primary key auto_increament,

username varchar(50) unique,

password varchar(50) not null,

…

);

非空约束的特点是： 不能为空值， 即插入数据时该列必须得有值！！

4**、外键约束**

外键是用于表和表之间关系的列，后面在表关系中讲解

3 MySQL建库、建表

3.1 查看数据库、查看数据表

1、查看mysql服务器中所有数据库

show databases;

2、进入某一数据库

use test;

提示，查看已进入的库：select database();

3、查看当前库中的所有表

show tables;

提示：mysql数据库不区分大小写

3.2 创建数据库、创建数据表

1**、创建数据库**

– 删除mydb1库(如果存在)

drop database if exists mydb1;

– 重新创建mydb1库

create database mydb1 charset utf8;

– 查看、进入mydb1库

show databases;

use mydb1;

提示：创建库时，要记得指定编码

2**、创建数据表**

– 删除stu学生表(如果存在)

drop table if exists stu;

– 创建stu学生表

create table stu(

id int, – 学生编号

name varchar(20), – 学生姓名

gender char(1), – 学生性别

birthday date, – 出生年月

score double – 考试成绩

);

– 查看表结构

desc stu;

提示：# 和 – 是sql语句的注释符号。例如：

– 这是注释内容(要注意的是–和注释之间要有空格)

#这是注释内容

4 更新表记录（insert/update/delete）

4.1 insert–插入表记录

插入记录的语法：

insert into 表名(列1,列2…) values(值1, 值2…);

– insert用于向指定的表中插入哪些列, 以及给予哪些值

如果要为所有的列插入值, 可以省略表名后面的列列表，例如：

insert into 表名 values(值1, 值2…);

注意: (1)只能在给所有列插入值时, 才可以省略列列表, 否则会报错

(2)在插入值时, 如果没有省略列列表, 那么列列表和值列表要一一对应

(3)在插入值时, 如果省略了列列表, 那么值列表中值的顺序要和声明列的顺序一致

(4)在插入字符串和日期时, 字符串和日期要用单引号引起来。

– 例如: 往学生表(stu)中插入记录

insert into stu(id,name,gender,birthday,score) values(1, ‘王海涛’,‘男’, ‘1993-1-10’, 78);

insert into stu values(2, ‘齐雷’,‘男’, ‘1995-4-8’, 78);

– 或为指定列插入数据

insert into stu(id, name, gender) values(3, ‘刘沛霞’, ‘女’);

常见问题（FAQ**）：在CMD****中插入记录时如果报如下错误，**

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WcI6EPP5-1603174856594)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image006.jpg)]

因为这里存在中文的乱码问题。

解决方式：先设置编码GBK “set names gbk”, 再插入记录

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TaYG95sO-1603174856596)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image008.jpg)]

因此，在通过CMD访问数据库时，最好一登录就设置 “set names gbk”，即可避免在插入数据时或者查询数据时数据乱码

4.2 update–修改表记录

– 修改stu表中王海涛的成绩，加10分特长分。

update stu set score=score+10 where name=‘王海涛’;

– 查询stu表中王海涛的信息

select * from stu where name=‘王海涛’;

若没有where子句, 则默认修改所有员工

– 修改stu表中所有学生的成绩，加10分特长分。

update stu set score=score+10;

– update stu set score=ifnull(score, 0)+10;

注意: null值和任何值计算结果还是null, 因此, 可以通过ifnull函数将null置为零对待.

4.3 delete–删除表记录

– 删除stu表中的王海涛信息

delete from stu where name=‘王海涛’;

若没有where子句, 则默认删除所有记录

– 删除emp表中的所有信息

delete from stu;

5 查询表记录（select）

* 准备数据：执行《SQL脚本-db10库.txt》中的脚本，创建db10库，创建emp表，并往emp表中插入记录。

5.1 基础查询

– 查询emp表中的所有员工，显示员工姓名、薪资、奖金

select name,sal,bonus from emp;

– 查询emp表中的所有员工，显示所有列。

select * from emp;

提示: distinct关键字，用于剔除指定列中的重复值，例如：

– 查询emp表中的所有部门，剔除重复的部门

select distinct dept from emp;

试一试: 查询emp表中的所有奖金, 剔除重复的值

5.2 where子句查询

对表中的所有记录进行筛选、过滤使用where子句

下面的运算符可以在 WHERE 子句中使用：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LkRcOMlj-1603174856598)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image010.png)]

SQL练习：

– 查询emp表中薪资大于3000的所有员工，显示员工姓名、薪资

select name,sal from emp where sal>3000;

– 查询emp表中总薪资(薪资+奖金)大于3500的所有员工，显示员工姓名、总薪资

select name, sal+bonus from emp where (sal+bonus)>3000;

– 或

select name, sal+bonus as 总薪资 from emp where (sal+bonus)>3000;

提示：(1)as****用于定义别名(仅在查询的结果中作为列的表头显示)，也可以省略as

(2)where****子句中不能使用列别名（但是可以使用表别名）

– 查询emp表中薪资在3000和4500之间的员工，显示员工姓名和薪资

select name,sal from emp where sal between 3000 and 4500;

– 查询emp表中姓名中以"刘"开头的员工，显示员工姓名。

select name,sal from emp where name like ‘刘%’;

– 查询emp表中姓名以"刘"开头，字数为两个字的员工，显示员工姓名。

select * from emp where name like ‘刘_’;

– 查询emp表中姓名中包含"涛"字的员工，显示员工姓名。

select * from emp where name like ‘%涛%’;

提示："%" 表示任意0或多个字符。"_" 表示任意一个字符

– 查询emp表中薪资为1400、1600、1800的员工，显示员工姓名和薪资

select name,sal from emp where sal in(1400,1600,1800);

– 查询emp表中薪资小于2000和薪资大于4000的员工，显示员工姓名、薪资。

select name,sal from emp where sal<2000 or sal >4000;

– 查询emp表中薪资大于3000并且奖金小于600的员工，显示姓名、薪资、奖金。

select name,sal,bonus from emp where sal>3000 and bonus<600;

5.3 排序查询

对查询的结果进行排序使用 order by关键字。

order by 排序的列 asc 升序 ↑

order by 排序的列 asc 降序 ↓

– 对emp表中所有员工的薪资进行升序(从低到高)排序，显示姓名、薪资。

select name,sal from emp order by sal asc;

– 对emp表中所有员工的总薪资进行降序(从高到低)排序，显示姓名、总薪资。

select name, sal+bonus as 总薪资 from emp order by (sal+bonus) desc;

5.4 分组查询

对所查询的记录可以根据某一列进行分组, 分组使用group by。

– 将员工按照部门进行分组

select * from emp group by dept;

– 对emp表按照部门进行分组, 并统计每个部门的人数, 显示部门和对应人数

select dept 部门名称, count(*) 部门人数 from emp group by dept;

– 对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金)

select max(sal) 总薪资 from emp group by dept;

注意：分组之前使用聚合函数表示对查询的所有记录进行统计计算

分组之后使用聚合函数表示对每一个组中的所有记录进行统计计算。

5.5 聚合函数查询

1**、max()**或min() – 返回某列的最大值或最小值

2**、count()** – 返回某列的行数

3**、sum()** – 返回某列值之和

4**、avg()** – 返回某列的平均值

– 查询emp表中最高薪资

select max(sal) as 最高薪资 from emp;

– 查询emp表中最高总薪资(薪资加奖金)

select max(sal+bonus) as 最高薪资 from emp;

– 统计emp表中薪资大于3000的员工人数

select count(*) from emp where sal>3000;

– 统计emp表中所有员工的总薪资(不包含奖金)

select sum(sal) as 员工总薪资 from emp;

– 统计emp表员工的平均薪资(不包含奖金)

select avg(sal) as 员工总薪资 from emp;

**!!**重要提示：

a) 可以使用count(*)统计记录行数

b) 多个聚合函数可以一起查询

– 例如:根据部门进行分组，统计每个部门员工人数和平均薪资

select dept, count(*) 员工人数, avg(sal) 平均薪资 from emp group by dept;

c) 聚合函数不能用在where子句中

d) 在没有分组的情况下，聚合函数不能和其他普通字段一起查询

– 例如: 查询emp表中薪资最高的员工姓名, 下面的写法是错的:

select name, max(sal) from emp;–结果是错的

– 正确的查询：

select name, sal from emp where sal=(

select max(sal) from emp

);-- 子查询

5.6 其他函数

1**、数值函数**

(1)ceil(数值) – 向上取整

(2)floor(数值) – 向下取整

(3)round(数值) – 向下取整

(4)rand(数值) – 随机数

– emp表中所有员工薪资上涨15.47%, 向上取整。

select name,sal, ceil(sal*1.1547) from emp;

2**、日期函数**

(1)curdate() – 返回当前日期(年月日)

(2)curtime() – 返回当前时间(时分秒)

(3)now() – 返回当前日期+时间(年月日 时分秒)

(4)date_add()、date_sub() – 增加/减少日期

(5)year()、month()、day()、hour()、minute()、second()，分别用来获取日期中的年、月、日、时、分、秒

– 查询系统当前时间。

select now();

– 查询emp表中所有员工的年龄，显示姓名、年龄。

select name,year(curdate()) - year(birthday) 年龄 from emp;

– 查询emp表中所有在1993和1995年出生的，显示姓名、出生日期。

select name,birthday from emp where year(birthday) between 1993 and 1995;

6 外键和表关系

6.1 外键

外键：唯一标识其他表中的一条记录，用来通知数据库两张表列与列之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D1BPdq11-1603174856600)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image012.jpg)]

例如：员工表的部门id列（dept_id）和部门表的id列具有一 一对应的关系, 其中dept_id就是外键。

外键作用: 确保数据库数据的完整性和一致性

添加外键: 例如：foreign key(dept_id) references dept(id)

* 准备数据：执行《SQL脚本-db20库.txt》中的脚本，创建db20库，创建dept和emp表，并往两表中插入记录。

练习：

(1)执行上面的SQL语句，创建两张表（dept和emp），在创建时先不指定dept_id为外键，尝试删除部门表中的某一个部门。（删除具有员工的部门会失败）

(2)将dept和emp表删除重建，在创建时指定dept_id为外键，再次尝试删除部门表中的某一个部门。

6.2 表关系

6.2.1 一对多

一对多，反过来就是多对一，以班级和学生为例：

(1)一个班级中可能会有多个学生(1~*)

(2)一个学生只能属于一个班级(1_{1)，两者合并结果还是1}*

因此，班级表和学生表是一对多的关系

对于一对多的两张表，可以在多的一方添加列，保存一的一方的主键，从而保存两张表之间的关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KyZjINk7-1603174856601)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image014.jpg)]

6.2.2 一对一

以班级和教室为例：

(1)一个班级对应一个教室(1~1)

(2)一个教室也只对应一个班级(1_{1)，两者合并结果还是1}1

因此，班级表和教室表是一对一的关系

对于一对一关系的两张表，可以在任意一张表中添加列，保存另一张表的主键，从而保存两张表之间的关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G4XBmm12-1603174856602)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image016.jpg)]

6.2.3 多对多

以学生和老师为例：

(1)一个学生对应多个老师(1~*)

(2)一个老师也对应多个学生(1_{*)，两者合并结果是*}*

因此，学生表和老师表是多对多的关系

对于多对多的关系，可以拆分成两张一对多的关系，无法在两张表中添加列保存关系，但我们可以添加一张第三方的表（专门保存两张表的关系），保存两张表的主键，从而保存两张表的关系。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-29hPF9i2-1603174856603)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image018.jpg)]

7 多表连接查询

7.1 连接查询

* 准备数据：执行《SQL脚本-db30库.txt》中的脚本，创建db30库，创建dept和emp表，并往两表中插入记录。

连接查询：将两张或者两张以上的表，按照指定条件查询，将结果显示在一张表中。

多张表查询的语法：

select…

from A, B…

where…

如果表名过长，可以为表添加别名以方便书写

select…

from A a**, B** b**…**

where…

上面小写的a和b就是A和B表的别名:

– 查询部门和员工两张表

select *

from dept,emp;

上面查询的结果中存在大量错误的数据, 如果想正确显示部门及部门对应的员工，可以通过where子句从中筛选正确的数据.

– 查询部门和部门下的员工。

select *

from dept d,emp e

where d.id=e.dept_id;

或

select *

from dept d inner join emp e on d.id=e.dept_id;

上面的查询(inner join…on…)方式也叫做内连接查询

7.2 外连接查询

7.2.1 左外连接查询

显示左侧表中的所有记录，如果在右侧表中没有对应的记录，则显示为null

语法：

select …

from a left join b on (a.id=b.xid)

– 查询所有部门和部门下的员工，如果部门下没有员工，显示null

select *

from dept d left join emp e on d.id=e.dept_id;

以上结果会显示（左侧表）所有部门，如果某部门下没有员工，（右侧表）则显示为null

7.2.2 右外连接查询

显示右侧表中的所有记录，如果在左侧表中没有对应的记录，则显示为null

语法：

select …

from a right join b on (a.id=b.xid)

– 查询部门和所有员工，如果员工没有所属部门，显示null

select *

from dept d right join emp e on d.id=e.dept_id;

以上结果会显示（右侧表）所有员工，如果员工没有所属部门，（左侧表）则显示为null

7.3 子查询

* 准备数据：执行《SQL脚本-db40库.txt》中的脚本，创建db40库，创建dept和emp表，并往两表中插入记录

所谓的子查询，其实就是将一个查询得出的结果，作为另外一个查询的条件。

格式：

select…

from…

where…(select…from…)

1、列出薪资比’王海涛’高的所有员工，显示姓名、薪资

– 先查询出’王海涛’的薪资

select sal from emp where name=‘王海涛’;

– 再查询比王海涛薪资(2450)高的员工

select name, sal

from emp

where sal>( select sal from emp where name=‘王海涛’);

2、列出与’刘沛霞’从事相同职位的所有员工，显示姓名、职位、部门。

– 先关联, 查询员工及员工对应的部门

select e.name, e.job, d.name from emp e, dept d where e.dept_id=d.id;

– 再查询’刘沛霞’的职位

select name, job from emp where name=‘刘沛霞’;

– 最后筛选, 筛选出和’刘沛霞’相同职位的员工

select e.name, e.job, d.name from emp e, dept d where e.dept_id=d.id and job=(select job from emp where name=‘刘沛霞’);

3、列出薪资高于在’大数据部’(已知部门编号为30)就职的所有员工的薪资的员工姓名和薪资、部门名称。

– 查询出’大数据部’的最高薪资

select max(sal) from emp where dept_id=30;

– 关联查询, 查询员工的姓名,薪资, 部门名称

select e.name, e.sal, d.name from emp e, dept d where e.dept_id=d.id and sal>(select max(sal) from emp where dept_id=30);

7.4 多表查询

1、（左外连接）列出所有部门和部门下的员工，如果部门下没有员工, 显示为null。

select d.id, d.name, e.name, e.dept_id

from dept d left join emp e on e.dept_id=d.id;

2、（关联查询）列出在’培优部’任职的员工，假定不知道’培优部’的部门编号。

– 先查询员工及员工所属部门

select e.name, e.dept_id, d.id, d.name

from emp e, dept d

where e.dept_id=d.id;

– 再筛选过滤，查询部门名称为’培优部’的员工

select e.name, d.id, d.name

from emp e, dept d

where e.dept_id=d.id and d.name=‘培优部’;

3、（自连接查询）列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

– 先查询员工表（emp e1）

select name, topid from emp e1;

– 再查询上级表（还是员工表，emp e2）

select id, name from emp e2;

– 最后查询员工及其员工的直接上级

select e1.name,e1.topid, e2.id, e2.name

from emp e1, emp e2

where e1.topid = e2.id;

4、（分组、聚合函数）列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资

– 先查询出各种职位的最低薪资

select job, min(sal) 最低薪资

from emp group by job;

提示：对分组后的记录筛选过滤请使用having替换where，并且having书写在最后

– 再查询出最低薪资>1500的职位

select job, min(sal) 最低薪资

from emp group by job

having min(sal)>1500;

5、（分组、聚合函数查询）列出在每个部门就职的员工数量、平均工资。显示部门编号、员工数量，平均薪资。

select dept_id, count(*) 员工数量, avg(sal) 平均薪资

from emp group by dept_id;

6、（分组、关联、聚合函数查询）查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

– 先关联查询, 查询出员工和员工对应的部门

select d.id, d.name, d.loc, e.name

from emp e, dept d

where e.dept_id=d.id;

– 再根据部门进行分组, 统计每个部门的员工数量

select d.id, d.name, d.loc, count(*) 员工数量

from emp e, dept d

where e.dept_id=d.id group by e.dept_id;

7、（自连接查询）列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

– 关联查询

select e1.id, e1.name, d.name

from emp e1, emp e2, dept d

where e1.dept_id=d.id and e1.topid=e2.id and e1.hdate < e2.hdate;

8 扩展内容

8.1 使用CMD连接MySQL服务器

通过命令行工具可以登录MySQL客户端，连接MySQL服务器，从而访问服务器中的数据。

1**、登录mysql****客户端命令**：mysql -uroot -proot

-u**：**后面的root是用户名，这里使用的是超级管理员root；

-p**：(****小写的p)**后面的root是密码，这是在安装MySQL时就已经指定的密码；

2**、指定主机和端口连接**: mysql -uroot -proot -h127.0.0.1 -P3306

-h**：**后面给出的127.0.0.1是服务器主机名或ip地址，可以省略的，默认连接本机；

-P**：(****大写的P)**后面的3306是连接端口，可以省略，默认连接3306端口；

3**、退出客户端命令：quit或exit或 \q**

FAQ：常见问题:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-guGh7Cbd-1603174856604)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image020.jpg)]

解决方法：复制mysql安装目录下的bin目录, 将bin目录的路径添加到path环境变量中!!

8.2 使用Navicat连接MySQL服务器

1、运行navicat客户端，按照下面的图示连接mysql服务器

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JF1D9S3o-1603174856605)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image022.jpg)]

2、通过navicat发送SQL到mysql，打开所要操作的库（或任意打开一个库），再点击"查询" —> “新建查询”

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9IjKpIMY-1603174856605)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image024.jpg)]

3、在弹出的窗口中，直接书写sql运行。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3OALtGx1-1603174856606)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image026.jpg)]

4、如果要运行指定的SQL，使用鼠标选中执行的SQL语句，右键–>“运行已选择的”

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Boyh9Obg-1603174856607)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image028.jpg)]

8.3 备份和恢复数据库

1、打开CMD窗口(不要登录)，通过命令备份mydb1数据库

备份命令: mysqldump -u用户名 -p 数据库名字 > 数据文件的位置

例如: mysqldump -uroot -p mydb1 > d:/mydb1.sql

输入密码, 如果没有提示错误, 即备份成功, 查询d盘的mydb1.sql文件

提示: (1)备份数据库的命令后面不要添加分号(😉

(2)备份数据库只是备份数据库中的表, 不会备份数据库本身

2、登录到MySQL客户端，删除mydb1库

– 删除mydb1库

drop database mydb1;

– 查询所有库, 是否还存在mydb1库

show databases;

由于备份时, 没有备份数据库本身, 所以在恢复库中的数据前, 需要先创建好要恢复的库

– 创建mydb1数据库

create database mydb1 charset utf8;

提示: 此时的mydb1库是空的, 没有任何表

3、在CMD窗口中(不要登录)，通过命令恢复mydb1数据库

– 在CMD窗口中(不要登录)

恢复命令: mysql -u用户名 -p 数据库名字 < 数据文件的位置

例如: mysql -uroot -p mydb1 < d:/mydb1.sql

输入密码, 如果没有提示错误, 即恢复成功, 下面进行验证

– 在登录状态下, 选择mydb1库, 查询其中的表是否恢复了回来

use mydb1;

show tables;

8.4 SQL中的特殊字符

8.4.1 SQL语句的注释符号

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SvhNy0mX-1603174856608)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image030.jpg)]

在MySQL客户端中，– 和 # 是注释符号

– 注释内容（注意–后面的空格不能省略）

#注释内容

8.4.2 取消SQL语句的执行—\c

取消当前SQL语句的执行，例如：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mtp9ebCx-1603174856609)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image032.jpg)]

当在cmd中书写了SQL语句，又想取消执行，在SQL语句的后面添加一个 \c 可以取消当前SQL语句的执行!!

8.4.3 SQL语句无法结束执行的问题

有时在cmd中编写的SQL，后面结尾添加了分号，但是当回车时却怎么也执行不了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wg5VU9Pb-1603174856610)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image034.jpg)]

仔细查看，才发现是SQL语句中的引号没有结束的原因。解决方法：

在下面敲一个单引号**'和\c**，回车即可结束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XQl6r86A-1603174856611)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image036.jpg)]

8.5 修改表操作

现创建学生表：

use test; – 进入test库

drop table if exists stu; – 删除学生表(如果存在)

create table stu( – 创建学生表

id int, – 学生id

name varchar(20), – 学生姓名

gender char(1), – 学生性别

birthday date – 出生年月

);

8.5.1 新增列

语法：ALTER TABLE tabname ADD col_name datatype [DEFAULT expr][,ADD col_name datatype…];

1、往stu表中添加score列，double类型

alter table stu add score double;

8.5.2 修改列

语法：ALTER TABLE tabname MODIFY (col_name datatype [DEFAULT expr][,MODIFY col_name datatype]…);

1、修改id列，将id设置为主键

alter table stu modify id int primary key;

2、修改id列，将id主键设置为自动增长

alter table stu modify id int auto_increment;

8.5.3 删除列

语法：ALTER TABLE tabname DROP [COLUMN] col_name;

1、删除stu表中的score列

alter table stu drop score;

8.6 添加或删除主键及自增长

思考：a) 在建表时，如何为id指定主键约束和自增?

b) 建好的表，如何通过修改添加主键约束和自增?

c) 如何删除表中的主键约束和自增?

1、创建stu学生表, 不添加主键自增, 查看表结果

use mydb1; – 切换到mydb1库

drop table if exists stu; – 删除stu学生表(如果存在)

create table stu( – 重建stu学生表, 没有主键自增

id int,

name varchar(20),

gender char(1),

birthday date

);

desc stu; – 查看表结构

表结构如下: 没有主键约束和自增。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TF4kUP8d-1603174856612)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image038.jpg)]

2、如果表没有创建, 或者要删除重建, 在创建时可以指定主键或主键自增

drop table if exists stu; – 删除stu表

create table stu( – 重新创建stu表时, 指定主键自增

id int primary key auto_increment,

name varchar(20),

gender char(1),

birthday date

);

desc stu; – 查看表结构

表结构如下: 已经添加了主键约束和自增。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0i2DpXc1-1603174856612)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image040.jpg)]

3、如果不想删除重建表，也可以通过修改表添加主键或主键自增

再次执行第1步, 创建stu学生表, 不添加主键自增, 查看表结果

– 例如: 将stu学生表中的id设置为主键和自动增长

alter table stu modify id int primary key auto_increment;

desc stu; – 查看表结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l7MXmtI1-1603174856613)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image041.jpg)]

如果只添加主键约束, 不设置自增

alter table stu modify id int primary key;

如果已经添加主键约束, 仅仅设置自增，但需注意:

(1)如果没有设置主键, 不可添加自增

(2)只有当主键是数值时, 才可以添加自增

alter table stu modify id int auto_increment;

4、如果想删除主键自增

– 删除主键自增时, 要先删除自增

alter table stu modify id int;

– 再删除主键约束

alter table stu drop primary key;

desc stu; – 查看表结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vSOQTjag-1603174856614)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image038.jpg)]

8.7 添加或删除外键约束

8.7.1 添加外键方式一：建表时添加外键

现有部门表如下：

– 创建部门表

create table dept(

id int primary key auto_increment, – 部门编号

name varchar(20) – 部门名称

);

要求创建员工表，并在员工表中添加外键关联部门主键

– 创建员工表

create table emp(

id int primary key auto_increment, – 员工编号

name varchar(20), – 员工姓名

dept_id int, – 部门编号

foreign key(dept_id) references dept(id) – 指定dept_id为外键

);

8.7.2 添加外键方式二：建表后添加外键

现有部门表和员工表：

– 创建部门表

create table dept(

id int primary key auto_increment, – 部门编号

name varchar(20) – 部门名称

);

– 创建员工表

create table emp(

id int primary key auto_increment, – 员工编号

name varchar(20), – 员工姓名

dept_id int – 部门编号

);

如果表已存在，可以使用下面这种方式：

alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);

其中 add constraint fk_dept_id 表示新增列，列名为fk_dept_id (名字由自己定义)

foreign key(dept_id)中的dept_id为外键

8.7.3 删除外键

1、首先通过 “show create table 表名”语法，查询含有外键表的建表语句，例如：

show create table emp;

显示结果如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HSj2wgM5-1603174856615)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image043.jpg)]

其中，emp_ibfk_1是在创建表时，数据库为外键指定的一个名字，删除这个名字即可删除外键关系，例如：

alter table emp drop foreign key emp_ibfk_1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ipcbKtBD-1603174856616)(file:///C:\Users\Admin\AppData\Local\Temp\msohtmlclip1\01\clip_image045.jpg)]

外键删除成功！

8.8 where中不能使用列别名

SQL语句的书写顺序:

select * | 列名 – 确定要查询的列有哪些

from 表名 – 确定查询哪张表

where 条件 – 通过筛选过滤, 剔除不符合条件的记录

group by 分组的列 – 指定根据哪一列进行分组

having 条件 – 通过条件对分组后的数据进行筛选过滤

order by 排序的列 – 指定根据哪一列进行排序

limit (countPage-1)*rowCount, rowCount – 指定返回第几页记录以及每页显示多少条

SQL语句的执行顺序:

from 表名 – 确定查询哪张表

where 条件 – 通过筛选过滤, 剔除不符合条件的记录

select * | 列名 列别名 – 确定要查询的列有哪些,

group by 分组的列 – 指定根据哪一列进行分组

having 条件 – 通过条件对分组后的数据进行筛选过滤

order by 排序的列 – 指定根据哪一列进行排序

limit (countPage-1)*rowCount, rowCount

* 关于where***中不能使用列别名但是可以使用表别名?

是因为, 表别名是声明在from中, from先于where执行, 先声明再使用没有问题, 但是列别名是声明在select中, where先于select执行, 如果先使用列别名, 再声明, 这样执行会报错!!

nt primary key auto_increment, – 部门编号

name varchar(20) – 部门名称

);

– 创建员工表

create table emp(

id int primary key auto_increment, – 员工编号

name varchar(20), – 员工姓名

dept_id int – 部门编号

);

如果表已存在，可以使用下面这种方式：

alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);

其中 add constraint fk_dept_id 表示新增列，列名为fk_dept_id (名字由自己定义)

foreign key(dept_id)中的dept_id为外键

8.7.3 删除外键

1、首先通过 “show create table 表名”语法，查询含有外键表的建表语句，例如：

show create table emp;

显示结果如下：

[外链图片转存中…(img-HSj2wgM5-1603174856615)]

其中，emp_ibfk_1是在创建表时，数据库为外键指定的一个名字，删除这个名字即可删除外键关系，例如：

alter table emp drop foreign key emp_ibfk_1;

[外链图片转存中…(img-ipcbKtBD-1603174856616)]

外键删除成功！

8.8 where中不能使用列别名

SQL语句的书写顺序:

select * | 列名 – 确定要查询的列有哪些

from 表名 – 确定查询哪张表

where 条件 – 通过筛选过滤, 剔除不符合条件的记录

group by 分组的列 – 指定根据哪一列进行分组

having 条件 – 通过条件对分组后的数据进行筛选过滤

order by 排序的列 – 指定根据哪一列进行排序

limit (countPage-1)*rowCount, rowCount – 指定返回第几页记录以及每页显示多少条

SQL语句的执行顺序:

from 表名 – 确定查询哪张表

where 条件 – 通过筛选过滤, 剔除不符合条件的记录

select * | 列名 列别名 – 确定要查询的列有哪些,

group by 分组的列 – 指定根据哪一列进行分组

having 条件 – 通过条件对分组后的数据进行筛选过滤

order by 排序的列 – 指定根据哪一列进行排序

limit (countPage-1)*rowCount, rowCount

* 关于where***中不能使用列别名但是可以使用表别名?

是因为, 表别名是声明在from中, from先于where执行, 先声明再使用没有问题, 但是列别名是声明在select中, where先于select执行, 如果先使用列别名, 再声明, 这样执行会报错!!