Mysql知识总结（进阶）

Mysql知识总结（进阶）

前言：笔记所需数据库在我的博客资源里面

1，等值连接

#案例： 查询女神名和她对应的男生名
USE girls;
SELECT NAME,boyname FROM boys,beauty WHERE beauty.boyfriend_id = boys.id;

#案例：查询员工名和对应的部门名
SELECT last_name,department_name FROM employees,departments
WHERE departments.department_id=employees.department_id;

#为表取别名

/*
提高简洁
区分好
注意：如过使用了别名那么查询字段就别使用原来的名字
*/
#查询员工名，工种号，工种名
SELECT last_name,employees.job_id,job_title
FROM jobs,employees
WHERE employees.job_id= jobs.job_id;

SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.job_id = j.job_id;

#可以加筛选吗？

#案例：查询有奖金的员工，名和部门名
SELECT last_name,department_name,commission_pct
FROM employees AS e,departments AS d
WHERE e.department_id=d.department_id AND commission_pct IS NOT NULL;

#案例：查询城市名中第二个字符为o的部门名和城市名

SELECT city,department_name
FROM locations l,departments d
WHERE l.location_id=d.location_id AND city LIKE ‘_o%’;

#加分组
#案例：查询每个城市的部门个数

#案例：查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d,employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY d.department_name;
#疑问，只有一个部门有奖金？查询如下：
SELECT commission_pct,department_name
FROM departments d,employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL;

#可不可以加排序
#案例：查询每个工种的工种名和员工个数，并且按员工个数降序
SELECT job_title,COUNT()
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT() DESC;

#是否可以实现三表连接？

#案例：查询员工名，部门名和所在城市
SELECT last_name,department_name,city
FROM locations l,employees e,departments d
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;

2，sql99语法（多表查询）

/*
语法：
select 查询列表
from 表一 别名 （连接类型）
join
内连接 inner
外连接
​ 左外left（outer）
​ 右外right（outer）
​ 全外full （outer）
交叉连接 cross
*/

#内连接

#等值连接

#案例一：查询员工名，部门名
SELECT department_name,last_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;

#查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE last_name LIKE ‘%e%’;

#查询部门个数为3的城市名和部门个数（分组+筛选）
SELECT city,COUNT(department_id) 部门个数
FROM locations l
INNER JOIN departments d
ON l.location_id=d.location_id
GROUP BY city
HAVING COUNT(department_id)>3;

#案例：查询哪个部门的员工个数》3的部门名和员工个数，并按个数降序（添加排序）
SELECT department_name,COUNT(employee_id) 员工个数
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name
HAVING 员工个数>3
ORDER BY 员工个数;

#查询员工名，部门名，工种名，并按部门名降序（）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN jobs j ON e.job_id=j.job_id
INNER JOIN departments d
ON d.department_id=e.department_id

ORDER BY department_name DESC;

#非等值连接

#查询员工的工资级别

SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
WHERE salary BETWEEN lowest_sal AND highest_sal;

#查询每个工资级别的个数>2并且按工资级别降序

SELECT COUNT(),grade_level
FROM employees e
INNER JOIN job_grades j
WHERE salary BETWEEN lowest_sal AND highest_sal
GROUP BY grade_level
HAVING COUNT()>2
ORDER BY COUNT(*) DESC;

SELECT COUNT(),grade_level
FROM employees e
INNER JOIN job_grades j
ON salary BETWEEN lowest_sal AND highest_sal
GROUP BY grade_level
HAVING COUNT()>2
ORDER BY COUNT(*) DESC;

#三，自连接

#查询一下员工名字，上级名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id;

外连接

/*
查询一个表中有，另一个表没有的记录

特点：
​ 查询出主表有所有字段，并与从表匹配
​ 如果有匹配值，显示出它的值没有的话显示null
​
有左外和右外的区别
左外 left左边是主表
右外 right右边是主表
*/

#查询男朋友不在男神表的女神名

SELECT b.name,bo.boyname
FROM beauty b
INNER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.boyfriend_id>=5;
#查不出来下面用外连接：

SELECT b.name,bo.boyname
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id;

USE myemployees;
#案例：查询哪个部门没有员工
SELECT department_name,last_name
FROM departments d
LEFT JOIN employees e
ON d.department_id=e.department_id
WHERE last_name IS NULL;

#全外连接
#相对于左外连接和右外连接组合起来（mysql不支持）

#交叉连接
#笛卡尔乘集而已

#进阶七 子查询

/*
含义：出现在其他语句中的select语句，称为子查询或内查询
分类：
​ 按查询的位置
​ selecet后面
​ 标量子查询
​ from后面
​ 表子查询
​ where或having后面
​ 标量
​ 列
​ 行 子查询
​ exist后面
​ 表子查询

按结果集行为行列数不同
	标量子查询
	列子查询
	行子查询
	表子查询

*/

#where having 后面子查询

/*
特点：子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用

列子查询一般搭配着多行操作符使用
*/

#标量子查询

#案例一：谁的工资比abel高？
SELECT last_name,salary
FROM employees
WHERE salary>(SELECT salary FROM employees WHERE last_name = ‘Abel’);

#案例二：返回job_id与141号员工相同，salary比143号员工多的姓名job_id和工资
SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
​ SELECT job_id
​ FROM employees
​ WHERE employee_id=141
​ )
AND
salary>(
​ SELECT salary
​ FROM employees
​ WHERE employee_id=143
​ );
​
​
#案例三：放回公司工资最少的员工last_name,job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=
(
​ SELECT MIN(salary)
​ FROM employees
_);

#案例四：查询最低工资大于五十号部门的最低工资的部门id和其最低工资

SELECT IFNULL(department_id,0),MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>
(
​ SELECT MIN(salary)
​ FROM employees
​ WHERE department_id=50
​ );
​ SELECT MIN(salary) FROM employees WHERE department_id=110;

#select 后面

#案例：查询每个部门的员工个数

SELECT d.,(
SELECT COUNT()
FROM employees e
WHERE e.department_id=d.department_id
)
FROM departments d;

#案例二：查询员工号=102的部门名
SELECT d.department_name
FROM departments d
INNER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id=102;

#from后面

/*
将子查询的结果充当一张表要求表必须有别名
/
#案例：查询每个部门平均工资的工资等级
SELECT aaa.,grade_level
FROM job_grades j
INNER JOIN (
SELECT AVG(salary) a
FROM employees e
GROUP BY department_id
) aaa
WHERE a BETWEEN lowest_sal
AND highest_sal;

#进阶八：分页查询

/*
应用场景：
当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
​ select 查询列表
​ from 表名
​ where…
​ limit 起始索引（offset,从0开始），条目数(size)
​
​
特点：
limit放在查询语句的最后
公式：要显示的页数page，每页的条目数size
limit page-1，size
*/
#案例一：查询前五条员工信息
SELECT *
FROM employees
LIMIT 10,15;
#案例三：有奖金的员工信息并且工资较高的前十名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

#DMl语言

/*

DMI：数据库操作语言：

插入：insert
修改：update
删除：delete

#插入语句

语法：
insert into 表名（列名。。。。）value(值1，。。。。。)

*/
#1.插入的值要与列的类型兼容
#可空值的插入
#法一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUE(13,‘王月英’,‘女’,‘1974-8-6’,‘13107866654’,NULL,100 );
SELECT * FROM beauty;
SELECT SYSDATE();
SELECT NOW() 时间;
#法二：
INSERT INTO beauty(id,NAME,sex,borndate,phone,boyfriend_id)
VALUE(14,‘王美莲’,‘女’,‘1999-2-5’,‘13107866654’,100 );

#列的顺序可以调换
INSERT INTO beauty(id,NAME,borndate,phone,boyfriend_id,sex)
VALUE(15,‘王月’,‘1974-8-6’,‘13107866654’,10 ,‘女’);

#可以省略列名但是系统会帮你添加上去默认的全部列名
INSERT INTO beauty()
VALUE(16,‘王伐英’,‘女’,‘1974-8-6’,‘13107866654’,NULL,100 );

#方式二；
/*
语法：
insert into 表名
set 列名=值，列名=值
*/
INSERT INTO beauty
SET id=55,NAME=‘展昭’,phone=1212121212;

SELECT * FROM beauty;

INSERT INTO beauty
VALUE(41,‘小猫按’,‘女’,‘1900-8-9’,‘18848511’,NULL,2),
(43,‘小按’,‘女’,‘1900-8-9’,‘188485181’,NULL,2),
(42,‘猫按’,‘女’,‘1900-8-9’,‘188448511’,NULL,2);

#法一支持子查询，法二不可以
#把查询语句查查询出来的东西写入表内
INSERT INTO beauty(id,NAME,phone)
SELECT 26,‘小四十’,‘1155’;

#修改语句

/*

1.修改单表的记录

update 表名
set 列=新值，列=新值，。。。。
where 筛选

2.修改多表的记录

delete 表
from 表1 别名，表2，别名
where 连接条件
and 筛选条件；

sql99语法
delete 表1的别名，表2的别名
from 表1 别名
inner right left join 表2 别名 on 连接条件
where 筛选条件；

*/

#姓王的电话都改成110
UPDATE beauty
SET phone=‘110’
WHERE NAME LIKE ‘王%’;

#修改boys表中ID=2的名称为张飞魅力值为10
UPDATE boys SET boyname=‘张飞’
WHERE ID=2;

SELECT * FROM boys;

#删除语句

#删除张无忌的女朋友的信息
SELECT *FROM boys;
DELETE b
FROM
boys bo
INNER JOIN
beauty b
ON bo.id=b.boyfriend_id
WHERE bo.boyName=‘张无忌’;

SELECT * FROM beauty;

#删除黄晓明和他女朋友的所有信息
DELETE bo,b
FROM boys bo
INNER JOIN beauty b
ON bo.id=b.boyfriend_id
WHERE bo.boyName=‘黄晓明’;

#方式二；truncate 语句
#案例：将魅力值大于100的男生删除
#truncate 清空数据

#delete 和 truncate大PK
/*

1.delete可以加where条件而truncate不能
2.truncate删除，效率更高
3.假如要删除自增长列，用delete删除后再插入数据是从断点开始
而truncate删除的时候再插入数据，自增长从0开始
4.truncate没有返回值而delete有
5.truncate删除不能回滚

*/

数据库的管理

#库的创建

CREATE DATABASE IF NOT EXISTS books;

#库的修改

#一般不修改库

#库的删除

DROP DATABASE IF EXISTS books;

#二、表的管理

#表的创建（重要）

/*create table 表名（）；
​ 列名 列的类型(（长度） 约束),
​ 列名 列的类型(（长度） 约束),
​ 列名 列的类型(（长度） 约束),
​ …
​ 列名 列的类型(（长度） 约束);
*/

CREATE TABLE book(
​ id INT,
​ bBood VARCHAR(20),
​ authorId INT,
​ publishDate DATETIME

);
DESC book;

#表的修改

/*
语法：

alter table 表名 add\modify\drop\change\rename column 列名（类型，约束）

*/

#1.修改列名

ALTER TABLE book CHANGE COLUMN pubdate pubDate DATETIME;
DESC book;
#2.修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#3.添加列
ALTER TABLE book ADD COLUMN annual1 DOUBLE;
#4.删除列
ALTER TABLE book DROP COLUMN annual1;
#5.修改表名
ALTER TABLE book RENAME TO boos;
SELECT * FROM book;
DESC book;

CREATE TABLE author(
​ id INT(10),
​ au_name VARCHAR(20),
​ nation VARCHAR(10)

);

#表的复制

INSERT INTO author VALUE
(1,‘村上春树’,‘日本’),
(2,‘莫言’,‘中国’),
(3,‘冯唐’,‘中国’),
(4,‘金庸’,‘中国’);

#仅仅复制表的结构
CREATE TABLE copy LIKE author;

#复制表的结构和数据
CREATE TABLE copy2
SELECT * FROM author;

#只复制部分数据
CREATE TABLE copy3
SELECT *FROM author
WHERE nation=‘中国’;

#只复制表的部分结构
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;

#表的删除

delete from 表名
truncate table 表名

#删除的通用写法
drop database 库名
BLE;
#4.删除列
ALTER TABLE book DROP COLUMN annual1;
#5.修改表名
ALTER TABLE book RENAME TO boos;
SELECT * FROM book;
DESC book;

CREATE TABLE author(
​ id INT(10),
​ au_name VARCHAR(20),
​ nation VARCHAR(10)

);

#表的复制

INSERT INTO author VALUE
(1,‘村上春树’,‘日本’),
(2,‘莫言’,‘中国’),
(3,‘冯唐’,‘中国’),
(4,‘金庸’,‘中国’);

#仅仅复制表的结构
CREATE TABLE copy LIKE author;

#复制表的结构和数据
CREATE TABLE copy2
SELECT * FROM author;

#只复制部分数据
CREATE TABLE copy3
SELECT *FROM author
WHERE nation=‘中国’;

#只复制表的部分结构
CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;

#表的删除

delete from 表名
truncate table 表名

#删除的通用写法
drop database 库名
drop table 表名