1>.什么是数据库
- 数据库就是用来存储和管理数据的容器！
容器有哪些：
变量、数组、集合、StringBuider、File等
关系结构模型数据库：使用二维表格来存储数据；MySQL就是关系型数据库
2>. 常见的关系型数据库
- 关系型数据库存放的都是实体之间的关系,“存储的数据都是 以行和列的形式体现”
Oracle（神喻）：甲骨文（最高) DB2：IBM ； SQL Server：微软 ； MySQL：甲骨文；Sybase：赛尔斯
3>. 非关系型数据库
4>. 理解数据库
- 我们现在所说的数据库泛指“关系型数据库管理系统（RDBMS - Relational database management system）”，即“数据库服务器”
2. SQL概述
1>. 什么是sql
SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。SQ标准（ANSI/ISO）有：
SQL-92：1992年发布的SQL语言标准；
SQL:1999：1999年发布的SQL语言标签；
SQL:2003：2003年发布的SQL语言标签；
虽然SQL可以用在所有关系型数据库中， 但很多数据库还都有标准之后的一些语法， 我们可以称之为“方言”。例如MySQL中的LIMIT 语句就是MySQL独有的方言，其它数据库都不 支持！当然，Oracle或SQL Server都有自己的方言。
2>. Mysql的语法规范
①. 不区分大小写，但建议关键字大写，表名、列名小写
②. 每条命令最好用分号结尾
③. 每条命令根据需要，可以进行缩进或换行
④. 注释 单行注释：#注释文字 -- 注释文字多行注释：/* 注释文字 */
注意 - - 后有一个空格
3>.分类 [ 掌握 ]
①. DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
②. DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据） [ 增、删、改、表记录]
③. DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。
④. DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
3. mysql基本的命令
1>. mysql服务的启动和停止
a. 开启：net start mysql b. 关闭：net stop mysql
2>.mysql服务的登录和退出
登录：mysql -h localhost -P(大写) 3306 -u root -p 或mysql -u root -p
注意：h和localhost、P（大写）和3306、u和root之间空不空格都可以
退出：exit quit
3>.mysql的常用命令
①. 查看当前所有的数据库show databases;
②. 打开指定的库use 库名;
③. 查看当前库的所有表show tables;
④. 查看其它库的所有表show tables from 库名;
⑤. 创建表create table 表名(列名 列类型,列名 列类型)
⑥. 查看表结构desc 表名;
⑦. 查看服务器的版本
a. 登录到mysql服务端select version();
b. 没有登录到mysql服务端mysql --version 或mysql -V
①. DDL
DDL：数据定义语言，用来定义数据库对象：库、表、列等；
4. DDL: 数据定义语言[ 库和表的管理 ]
1>.库的管理：创建、修改、删除
①. 库的创建: create database [if not exists] 库名；
②. 库的删除：drop database [if exists] 库名;
③. 修改数据库编码：
a.alter database 库名 character set utf8
b.create database 数据库名称 character set 字符集名;
④. a. 查询某个数据库的字符集:查询某个数据库的创建语句：show create database 数据库名称; b. 查询所有数据库的名称:show databases;
⑤. a. 查询当前正在使用的数据库名称：select database( ); 使用数据库；b.use 数据库名称; 有用
注意：在Mysql中所有的UTF-8编码都不能使用中间的 ‘ - ’即UTF-8要书写成 UTF8;
#案列:创建库Books create database If not exists Books; #更改库的字符集 alter database books character set gbk; alter database book character set utf8; #库的删除 drop database if exists books;
2>.数据类型介绍[了解]
数据类型
1.整数类型 ：int [ age int ，]
2.浮点型：float(5,2)、double(5,2) 2：小数点后保留两位； 5表示小数位+整数位
3.字符型：char（规定长度的字符）varchar（可变长度的字符）
特点：char（M）:可以省略，默认为1。varchar（M）:不可以省略
varchar： zhangsan （8个字符） 张三 （2个字符）
4.日期型： [ 用单引号引和双引号都可以 ]
date：只保存日期[yyyy-MM-dd]
time：保存时间[HH:mm:ss]
datetime：保存时期+时间
timestamp:时间戳类型， yyyy-MM-dd HH:mm:ss
重点：如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
3>. 表的管理：创建、修改、删除[掌握]
①. 表的创建
create table[ if not exists] 表名（
字段名 列的类型[（长度） 列的约束]，
字段名 列的类型[（长度） 列的约束]，
字段名 列的类型[（长度） 列的约束]，
…
字段名 列的类型[（长度） 列的约束]
）
②. 表的修改： alter table 表名 add|drop|modify|change column 列名[列的类型 约束]
alter table 表名 character set 字符集名称;
③. 表的删除：drop table[ if exists ] 表名；
#①. 修改列名 COLUMN 可以省略 alter table book change COLUMN publishdate pubDate dateTime; desc book; #②. 修改列的类型或约束 alter table book modify column pubDate TIMESTAMP; #③. 添加新列 alter table author add COLUMN annual double; desc author; #④. 删除列 alter table author drop COLUMN annual; #⑤. 修改表名 alter table author rename to book_author;
4>.表的复制
#仅仅复制表的结构 create table copy like employees; #复制表的结构+数据 create table copy2 select * from employees; #仅仅复制某些字段：不要数据 create table copy4 select employee_id from employees where 0;
5>. 常见约束
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性
1. 六大约束
①. Not null:非空，用于保证该字段的值不能为空
②. default:默认，用于保证该字段有默认的值,性别
③. primary key :主键，用于保证该字段的值具有唯一性，并且非空比如，学号，员工编号等
④. unique: 唯一，用于保证该字段的值具有唯一性，可以为空。比如座位号
⑤. check：检测约束[ mysql中不支持 ]
⑥. foreign key:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表关联列的,在从表添加外检约束，用于引用主表中某列的值
比如：学生表的专业编号，员工表的部门编号，员工表的工种编号
2. 添加约束的时机
①.创建表时 ②.修改表时
3. 约束的添加分类：
①.列级约束： 六大约束语法上都支持，但外键约束没有效果
②.表级约束:除了非空和默认，其他都支持
4.添加列级约束
create database student; use student; create table stuinfo( id int primary key,#主键 stuName varchar(20) not null,#非空 gender char(1) CHECK(gender='男' or gender='女'),#检查 seat int unique,#唯一 age int default 18,#默认约束 ); create table major( id int PRIMARY key, majorName VARCHAR(20) );
5. 添加表级约束[ 默认和非空不支持 ]
实际的工作中，一般不写constraint 名称
#语法：[constraint 约束名] 约束类型(字段名)
drop table if exists stuinfo;
drop table if exists major;
create database student; use student; create table stuinfo( id int primary key,#主键 stuName varchar(20) not null,#非空 gender char(1) CHECK(gender='男' or gender='女'),#检查 seat int unique,#唯一 age int default 18,#默认约束 ); create table major( id int PRIMARY key, majorName VARCHAR(20) );
6.通用写法[掌握]
create table if not exists stuinfo( id int primary key, stuname varchar(20) not null, sex char(1), age int default 18, seat int unique, majorId int, #constraint fk_stuinfo_major foreign key(majorId) references major(id) foreign key(majorId) references major(id) );
7.外键[掌握]
- ①. 主表关联的列必须是一个key（一般是主键或唯一）
- ②. 插数据时，先插入主表，再插入从表
- ③. 删除数据时，先删除从表，再删除主表
8.表修改时删除约束
#表修改时删除约束 #1.删除非空约束 alter table stuinfo modify column stuname varchar(20) null; #2.删除默认约束 alter table stuinfo modify column age int; #3.删除唯一 alter table stuinfo drop index seat; #4.删除主键 alter table stuinfo drop primary key; alter table stuinfo modify column id int; #5.删除外键 alter table stuinfo drop foreign key fk_stuinfo_major; #向表emp2的id列中添加primary key约束(my_emp_id_pk) alter table emp2 modify column id int primary key; alter table emp2 add constraint my_emp_id_pk primary key(id) #3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。 ALTER TABLE emp2 ADD COLUMN dept_id INT; ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);
6>. 练习 [ 重点掌握 ]
use housework; -- 一对多 多对多练习 #tab_category create table if not exists tab_category( cid int primary key auto_increment, cname varchar(20) ); #tab_route create table if not exists tab_route( rid int primary key auto_increment, rname varchar(20), price float(5,1), rdate timestamp, cid int ); alter table tab_route add foreign key(cid) references tab_category(cid); #tab_user create table if not exists tab_user( uid int primary key auto_increment, username varchar(20) ); #tab_favorite create table if not exists tab_favorite( rid int, date timestamp, uid int, foreign key(rid) references tab_route(rid), constraint kf_user_fav foreign key(uid) references tab_user(uid) );
②. DML语言[ 插入、修改、删除 ]
DML(Data Manipulation Language):数据操作语言，用来定义数据库记录（数据）;
5. DML语言[ 插入、修改、删除][掌握]
1>.插入insert
- 语法（一）：
①. insert into 表名(列名…)values(值1,…)；
②. insert into 表名 values(值1,…)；
③. insert into 表名(列名…)values(值1,…)，(值2，…)；
insert into 表名 values(值1,…)，(值2，…)；[全列名]
- 语法（2）:
insert into 表名 set 列名=值，列名=值，....
1.插入的值的类型要与列的类型一致或兼容
2.列的顺序可以调换
3.列数和值的个数必须一致
4.可以省略列名，默认是所有列，而且列的顺序和表中的顺序一致
#1.插入的值的类型要与列的类型一致或兼容(方式1) insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id) values(13,'唐艺昕','女','1990-4-23','18988888',null,2); #方式一支持插入多行，方式不部支持 insert into beauty VALUES(15,'唐艺','女','1990-4-23','18988888',null,2),(16,'唐昕','女','1990-4-23','18988888',null,2); # 方式2 insert into beauty set id=14,name='唐智',phone='999';
2>.修改update
- ①. 修改单表的记录：
update 表名 set 列=新值,列=新值....where[删选条件] - ②. 修改多表的记录[补充]:
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值…
where[删选条件]
#1.修改beauty表中姓唐的女神电话为138998899 update beauty set phone='18774149735' where name like '唐%'; #2.修改张无忌的女朋友的手机号为114 update boys b inner join beauty be on b.id=be.boyfriend_id set phone='114' where b.boyName='张无忌';
3>. 删除delete
①. delete from 表名 where[删选条件]
②. truncate table 表名；不可以加where,清空表
delete Pk truncate
1.delete 可以加where 条件，truncate不 能加
2.truncate删除，效率高一丢丢
3.假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始,而truncate删除后，再插入数据，自增长列的值从1开始
4.truncate删除没有返回值，delete删除有返回值
5.truncate删除不能回滚，delete删除可以回滚.
#方式一：delete #单表的删除：删除手机号以9结尾的女神信息 delete from beauty where phone like '%9'; #方式二：truncate truncate table 表名;
③. DQL语言学习
DQL[Data Query Language]: 数据查询语言，用来查询记录
DQL
1.基础查询
- 语法：
select 查询列表 from 表名;
特点：
①.查询的结果是一个虚拟的表格
②.查询列表可以是:表中的字段、常量值、表达式、函数
- ①. 查询表中单个字段、多个字段、所有字段
SELECT last_name from employees; SELECT last_name,salary,email FROM employees; SELECT * FROM employees;
②. 查询常量值：select 100
③. 查询表达式：select 100*98;
④. 查询函数 SELECT VERSION();
⑤. 起别名(AS关键字as可以省略)SELECT 100%98 AS 结果;
⑥. 去重DISTINCT SELECT DISTINCT department_id FROM employees;
⑦. 查询员工名和姓连接成一个字段，并显示为 姓名 concat()
SELECT concat(last_name,first_name) AS 姓名 FROM employees;
- ⑧.
+号的作用:运算符 - sql中的+号：仅仅只有一个功能：运算符
SELECT 100+99; 两个操作数都是数值型，则做加法运算
SELECT “123”+9; 其中一方为字符型，试图将字符数值转换成数值型
如果转换成功，则继续做加法运算；
如果转换失败，则将字符型数值转换成0
select null+10，只要有一方为null，则结果为nul
2. 条件查询
语法: select 查询列表 from 表名 where 筛选条件
1>. 按条件表达式筛选
条件运算符:> < = !=(<>:不等于) >= <=
2>. 按逻辑表达式筛选
- 逻辑运算符:
and or not
select last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;
3>. 模糊查询[like between and / in /is null]
- ①.
like:一般和通配符搭档使用
通配符: %:任意多个字符，包含0个字符 _:任意单个字符
#案例1：查询员工名中包含字符a的员工信息 SELECT * from employees where last_name like '%a%'; #案列2：查询出员工名中第二个字符为a，第三个字符为v的员工和工资 SELECT last_name,salary from employees where last_name like '_av%' #案列3：查询员工名中第二个字符为_的员工名 SELECT last_name from employees where last_name like '_$_%' ESCAPE '$';
②. between and:select * from employees where employee_id BETWEEN 100 and 120;
a. 使用between and可以提高语句的简洁度
b.包含两个临界值
c.两个临界值不要调换顺序
③. in关键字 in [ 类型一样，类型一样 ]
含义：判断某字段的值是否属于in列表中的某一项
查询员工的工种编号是：IT_prog、 AD_VD、AD_PRES中的一个员工名和工种编号：select last_name,job_id from employees where job_id IN('IT_prog','AD_VD','AD_PRES');
④. is null 和 is not null
查询没有奖金和员工名和奖金率 select last_name,commission_pct from employees where commission_pct is null; 查询有奖金和员工名和奖金率 select last_name,commission_pct from employees where commission_pct is NOT null;
3. 排序查询
语法：select 查询列表 from 表[where 筛选条件]
order by 排序列表 [asc(升序/降序:desc)]
- ①. order by子句中可以支持单个字段、多个字段、表达式、函数、别名
- ②. order by 子句一般是放在查询语句的最后面，limit子句除外
- ③. 默认是升序
注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件
#按员工信息,要求工资从高到低排序 select * from employees order by salary desc; #按员工信息,要求工资从低到高排序 select * from employees ORDER BY salary asc; select * from employees ORDER BY salary; #查询部门编号>=90的员工信息，按入职时间的先后进行排序[添加筛选条件] select * from employees where department_id >=90t ORDER BY hiredate asc; #按年薪的高低显示员工的信息和年薪[按表达式排序] select *,salary*12*(1+IFNULL(commission_pct,0)) as 年薪 from employeesT ORDER BY salary*12*(1+IFNULL(commission_pct,0)) desc; #按姓名的长度显示员工的姓名和工资 select LENGTH(last_name) 字节长度,last_name,salary from employees order by LENGTH(last_name) DESC; #查询员工信息，要求先按工资升序排序，再按照员工编号排序降序[按多个字段排序] select * FROM employees ORDER BY salary asc,employee_id DESC; #查询邮箱中包含e的员工信息，并先按邮箱的字节数降序，再按部门号升序 select *,LENGTH(email) from employees where email like '%a%' ORDER BY LENGTH(email) asc,department_id DESC;
常见函数介绍
调用：slect 函数名 (实参列表) [from 表]
分类：
①. 单行函数 concat( ) length( ) ifnull( )
②. 分组函数：做统计使用
1>. 单行函数
1. 字符函数
①. LENGTH(str):获取参数值的字节个数select LENGTH('join');
②. CONCAT(str1,str2,…)：拼接字符select concat('aa','bb');
③.upper()：大写 LOWER():小写
#将姓变大写，名变小写，然后拼接 SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 from employees;
- ④. substr() substring():截取从这个字符开始
#注意：索引从1开始 select substr('我有一个弟弟',2);#有一个弟弟 #截取从指定索引处指定字符长度 select substring('我有一个弟弟',2,4)；#有一个弟
2. 数字函数
①. round ： 四舍五入
SELECT round(1.45);#round(1.567,2)：小数点后保留两位
②. ceil：向上取整
③. floor： 向下取整
3. 日期函数
now():返回当前系统日期+时间
4. 其他函数
#select version(); #select database();
#1.显示系统时间（注意：日期+时间） select now(); #2.查询员工号 、姓名 、工资，以及工资提高百分之20%后的结果 select employee_id,last_name,salary*1.2 from employees; #3.将员工的姓名按首字母排序，并写出姓名的长度 select LENGTH(last_name) 长度,substr(last_name,1,1) as 首字符,last_name from employees order by 首字符;
2>. 分组函数
分类 : 求和、avg平均值、max最大值、min最小值、count计算个数
这五个参数支持哪些类型
1.sum、avg一般只处理数值型；max、min、count可以处理任何类型
2.以上的分组函数都忽略null值
3.和distinct搭档 五个都使用
4.一般使用count（*）统计行数
5.和分组函数一同查询的字段是group by后的字段
注意：聚合函数的计算，排除了null值
解决方案：
①. 选择不包含非空的列进行计算 [ 主键 ]
②. 使用IfNull函数
#1.简单实用 其他四个也试用 select sum(salary) from employees; #和distinct搭档 五个都使用 SELECT sum(DISTINCT salary),sum(salary) from employees; #统计个数 SELECT count(*) from employees;//建议使用这个 SELECT count(1) from employees;
练习
#查询公司员工工资的最大值、最小值、平均值、总和 select max(salary) 最大值,min(salary) 最小值,avg(salary) 平均值,sum(salary) 和 from employees; #查询员工表中的对入职时间和最小入职时间相差的天数 select DATEDIFF(max(hiredate),min(hiredate)) from employees; #查询部门编号为90的员工个数 select count(*) from employees where department_id=90;
5.分组查询[所有、每个、各个]
select 字段 from 表
where 条件
[GROUP BY 分组的列表]
[having 分组后的筛选 ]
[order by 子句]
①. 分组查询中的筛选条件分为两类
where 和 having的区别：
1.where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来
2.where后不可以跟聚合函数，having可以进行聚合函数的判断
②. gourp by 子句支持单个字段分组，多个字段分组(多个字段用逗号隔开没有顺序)
③. 也可以添加排序(排序放在整个分组查询的最后)
注意 : 查询列表必须特殊，要求是聚合函数和group by后出现的字段
#1.查询每个工种的最高工资[简单的分组查询] SELECT max(salary),job_id from employees GROUP BY job_id; #2.查询每个位置上的部门个数 SELECT count(*) 部门个数 from departments GROUP BY location_id; #添加筛选条件 #3.查询邮箱中包含a字符的，每个部门的平均工资 select avg(salary) 平均工资,department_id from employees where email like '%a%' GROUP BY department_id; #4.查询有奖金的每个领导手下员工的最高工资(掌握) select sum(salary) 最高工资,manager_id from employees where commission_pct is not null GROUP BY manager_id; #添加复杂的筛选条件 #5.查询哪个部门的员工个数>2 #(1)查询每个部门的员工个数 select count(*),department_id from employees GROUP BY department_id; #(2)根据（1）的结果进行筛选，查询哪个部门员工个数大于2 #添加分组后的筛选(掌握) select count(*),department_id from employees GROUP BY department_id having count(*)>2; #6.查询每个工种有奖金的员工的最高工资>12000的工种编号(job_id)和最高工资(掌握) #(1)查询每个工种有奖金的员工的最高工资 #(2)根据(1)结果筛选,最高工资>12000 select max(salary),job_id from employees where commission_pct is not null GROUP BY job_id having max(salary)>12000; #7.查询领导编号>102的每个领导手低下的最低工资>5000的领导编号的是哪个，以及最低工资 select min(salary) 最低工资,manager_id from employees where manager_id>102 GROUP BY manager_id having min(salary)>5000; #按表达式函数分组 #8.按员工姓名和长度分组，查询每一个员工的个数，筛选员工个数>5的有哪些？ #(1)查询每个长度的员工个数 SELECT count(*),LENGTH(last_name) len_name from employees GROUP BY LENGTH(last_name) having count(*)>5; #(2)筛选员工个数>5 #按多个字段分组[掌握] #查询每个部门每个工种的平均工资 SELECT AVG(salary),department_id,job_id from employees GROUP BY department_id,job_id #添加排序 #查询每个部门每个工种的平均工资并且按平均工资的高低显示[掌握] SELECT AVG(salary),department_id,job_id from employees GROUP BY department_id,job_id ORDER BY AVG(salary) desc;
练习
#查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者员工不计算在内[掌握] select min(salary) 最低工资,manager_id from employees where manager_id is not null GROUP BY manager_id having 最低工资>6000; #查询所有部门编号(department_id)，员工数量employee_id和工资平均值，并按平均工资降序 select department_id 部门编号,count(*) 员工数量,avg(salary) from employees GROUP BY department_id ORDER BY avg(salary) desc;
6.分页查询[掌握]
语法：
select 查询列表
from 表
[join type] join 表2
on 连接条件
[where 筛选条件]
[GROUP BY 分组的字段]
[having 分组后的筛选]
[ORDER BY 排序的字段]
limit [offset],size;
offset:开始的索引,每页查询的条数;(起始索引从0开始)
size:要显示的条目个数
特点：
①. limit语句放在查询语句的最后
②. 公式 limit (page-1)*size,size;
SELECT * FROM student LIMIT 0,3; -- 第1页 SELECT * FROM student LIMIT 3,3; -- 第2页 SELECT * FROM student LIMIT 6,3; -- 第3页
#1.查询前五条的员工信息 select * from employees limit 0,5; select * from employees limit 5; #2.查询第11条到第25条 select * from employees limit 10,15; #3.有奖金的员工信息，并且工资较高的前10名显示出来 select salary from employees where commission_pct is not null ORDER BY salary @desc limit 0,10;
④. 级联查询
7. 级联查询
语法：更新和删除可以分开写，也可以写在一起
ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称)
on update cascade on delete cascade
①. 级联更新：on update cascade
级联更新：当主表中的数据进行了更新后，从表中的数据随着也进行了更新
②. 级联删除：on delete cascade
级联删除：当删除了主表中的数据，从表中的数据也随着消失
use test; #创建一个员工表 create table if not exists emps( id int primary key auto_increment, dep_id int, name varchar(20), age int ); #创建一个部门表 create table if not exists dep( id int primary key auto_increment, dep_name varchar(220) ); #添加约束 alter table emps add foreign key(dep_id) references dep(id); alter table emps drop foreign key emps_ibfk_1; #添加数据 insert into dep values(1,'程序员'),(2,'医生'); insert into emps values(1,1,'唐智',23),(2,1,'唐洋',10),(3,2,'小幸',22); #查询表 select * from emps; select * from dep; #通过级联更新 alter table emps add constraint fk_emps_dep foreign key(dep_id) references dep(id) on update cascade; #当我们对部门的id进行更新的时候,会使得员工表emps中的数据也对应更改 update dep set id=5 where id=1; #通过级联删除 alter table emps add constraint fk_emps_dep foreign key(dep_id) references dep(id) on delete cascade; #当我们把主表中的的数据1删除掉的时候,这个时候从表与之关联的数据也删除了 delete from dep where id=1;
8. 多表查询
笛卡尔积：有两个集合A,B .取这两个集合的所有组成情况
要完成多表查询，需要消除无用的数据
1>. 内连接可以追加分组、排序sql92标准
隐式内连接：使用where条件消除无用的数据
select 字段 from 表1，表2
where 表1.key=表2.key或非等值条件
and 筛选条件
[GROUP BY 分组的列表]
[having 分组后的筛选 ]
[order by 子句]
注意:为表起了别名，则查询的字段就不能使用原来的表去限定
⑤. SQL 99 连接
2>. sql99 [ 推荐 ]
语法:
select 查询列表
from 表1 别名[连接类型 可以省略]
join 表2 别名
on 连接条件
[where筛选条件]
[group by分组]
[having 筛选条件]
内连接 ：[ inner ] join
外连接：
左外：left[outer] join
右外：right[outer] join
全外：full[OUTER] join
#数据的准备 use test; # 创建部门表 create table dept( id int primary key auto_increment, name varchar(20) ); insert into dept (name) values ('开发部'),('市场部'),('财务部'); # 创建员工表 create table emp ( id int primary key auto_increment, name varchar(10), gender char(1), -- 性别 salary double, -- 工资 join_date date, -- 入职日期 dept_id int, foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键) ); #插入数据 insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1); insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2); insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2); insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3); insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1); select * from emp; select * from dept;
2. 外连接
应用场景:用于查询一个表有，另一个表没有的记录
特点 ：
1.外连接的查询结果为主表中的所有记录。
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询的结果=内连接结果+主表中有而从表没有的记录
2.左外连接，left join左边的是主表
右外连接，right join右边的是主表
3.左外和右外交换两个表的顺序，可以实现同样的效果
4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
①. 左外连接
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
* 查询的是左表所有数据以及其交集部分。
* 例子：
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称 SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
②. 右外连接
语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
* 查询的是右表所有数据以及其交集部分。
* 例子：
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id;
- ③. 全外连接
3>. 子查询
1. 什么是子查询
- 查询中嵌套查询，称嵌套查询为子查询
-- 查询工资最高的员工信息 -- 1 查询最高的工资是多少 9000 SELECT MAX(salary) FROM emp; -- 2 查询员工信息，并且工资等于9000的 SELECT * FROM emp WHERE emp.`salary` = 9000; -- 一条sql就完成这个操作。子查询 SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
2. 子查询不同情况
①. 子查询的结果是单行单列的
子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =
– 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
②. 子查询的结果是多行单列的
子查询可以作为条件，使用运算符in来判断
– 查询’财务部’和’市场部’所有的员工信息
SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’;
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
– 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’);
- ③. 子查询的结果是多行多列的
子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息 select * from emp e inner join dept d on e.dept_id=d.id where e.`join_date` > '2011-11-11'; -- 内连接 SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
4>. 练习
这6个练习题目要求重点掌握
use test; -- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id dname VARCHAR(50), -- 部门名称 loc VARCHAR(50) -- 部门所在地 ); -- 添加4个部门 INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'),(20,'学工部','上海'),(30,'销售部','广州'),(40,'财务部','深圳'); -- 职务表，职务名称，职务描述 CREATE TABLE job ( id INT PRIMARY KEY, jname VARCHAR(20), description VARCHAR(50) ); -- 添加4个职务 INSERT INTO job (id, jname, description) VALUES (1, '董事长', '管理整个公司，接单'), (2, '经理', '管理部门员工'), (3, '销售员', '向客人推销产品'), (4, '文员', '使用办公软件'); -- 员工表 CREATE TABLE emp ( id INT PRIMARY KEY, -- 员工id ename VARCHAR(50), -- 员工姓名 job_id INT, -- 职务id mgr INT , -- 上级领导 joindate DATE, -- 入职日期 salary DECIMAL(7,2), -- 工资 bonus DECIMAL(7,2), -- 奖金 dept_id INT, -- 所在部门编号 CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id), CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id) ); -- 添加员工 INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20), (1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30), (1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30), (1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20), (1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30), (1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30), (1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10), (1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20), (1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10), (1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30), (1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20), (1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30), (1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20), (1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10); -- 工资等级表 CREATE TABLE salarygrade ( grade INT PRIMARY KEY, -- 级别 losalary INT, -- 最低工资 hisalary INT -- 最高工资 ); -- 添加5个工资等级 INSERT INTO salarygrade(grade,losalary,hisalary) VALUES (1,7000,12000), (2,12010,14000), (3,14010,20000), (4,20010,30000), (5,30010,99990);
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述 select e.id,e.ename,e.salary,d.dname,d.loc from emp e inner join dept d on e.dept_id=d.id; -- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置 select e.id,e.ename,e.salary,d.dname,d.loc,j.jname,j.description from emp e inner join dept d on e.dept_id=d.id inner join job j on e.job_id=j.id; -- 3.查询员工姓名，工资，工资等级 select e.ename,e.salary,s.* from emp e inner join salarygrade s on e.salary between s.losalary and s.hisalary; -- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级 select e.ename,e.salary, j.jname,j.description, d.dname,d.loc, s.* from emp e inner join job j on e.job_id=j.id inner join dept d on e.job_id=j.id inner join salarygrade s on e.salary between s.losalary and s.hisalary -- 5.查询出部门编号、部门名称、部门位置、部门人数 select e.dept_id,d.dname,d.loc,e.total from (select e.dept_id,count(id) total from emp e group by e.dept_id) e inner join dept d on e.dept_id=d.id -- 使用内连接 select d.*,count(e.id) from dept d inner join emp e on d.id=e.dept_id group by e.dept_id -- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询 select e1.id,e1.ename,e1.mgr ,e2.ename 上级领导 from emp e1 left join emp e2 on e1.mgr=e2.id
