MySQL学习系列（二）
2018-08-11 本文已影响0人
一只96年的程序猿
1.前文回顾
-- 50部门，工资大于8000的员工，按工资降序排列
SELECT id,
fname,
sal,
dept_id
FROM emps
WHERE dept_id = 50
AND sal>=8000
ORDER BY sal DESC;
-- 电话号码tel包含'44',并把'44'改成'88';
SELECT id,
fname,
tel,
replace(tel,'44','88'),
dept_id
FROM emps
WHERE tel LIKE "%44%";
-- 主管id(mgr_id)是100,120,122 的人数
SELECT count(*)
FROM emps
WHERE mgr_id in(100,120,122);
-- 按主管id分组,求每个主管手下人数(去null)
SELECT mgr_id,
count(*)
FROM emps
WHERE mgr_id IS NOT NULL
GROUP BY mgr_id;
2.having过滤
用来对多行函数结果进行过滤
(1).having 和 where 作用相同，都是条件过滤
(2).where 过滤普通条件，最早执行
(3).having 过滤多行函数结果，分组，求完多行函数后，才执行
(4).)having 跟在 group by 后面
-- 只有一个人的主管id
SELECT mgr_id,
count(*) c
FROM emps
WHERE mgr_id IS NOT NULL
GROUP BY mgr_id HAVING c=1;
+--------+---+
| mgr_id | c |
+--------+---+
| 102 | 1 |
| 201 | 1 |
| 205 | 1 |
+--------+---+
3 rows in set (0.00 sec)
-- 平均工资小于等于5000的岗位
SELECT job_id,
avg(sal) a
FROM emps
GROUP BY job_id HAVING a<=5000;
+----------+-------------+
| job_id | a |
+----------+-------------+
| AD_ASST | 4400.000000 |
| PU_CLERK | 2780.000000 |
| SH_CLERK | 3215.000000 |
| ST_CLERK | 2785.000000 |
+----------+-------------+
4 rows in set (0.00 sec)
3.子查询
-
条件子查询
一个查询的查询结果，作为另一个查询的过滤条件
select .. where a=(select ...)
(1).单值子查询: = > >= < <=
(2).多值子查询:
in
all 比最大值大
any 比最小值大
(3).多列子查询
where (a,b) in (select ...)
where (a,b) = (select ...)
-- 工资小于平均工资的员工
SELECT id,
fname,
sal
FROM emps
WHERE sal<
(SELECT avg(sal)
FROM emps);
+-----+-----------+----------+---------+
| id | fname | sal | dept_id |
+-----+-----------+----------+---------+
| 100 | Steven | 24000.00 | 90 |
| 103 | Alexander | 9000.00 | 60 |
| 108 | Nancy | 12000.00 | 100 |
| 114 | Den | 11000.00 | 30 |
| 121 | Adam | 8200.00 | 50 |
| 145 | John | 14000.00 | 80 |
| 200 | Jennifer | 4400.00 | 10 |
| 201 | Michael | 13000.00 | 20 |
| 203 | Susan | 6500.00 | 40 |
| 204 | Hermann | 10000.00 | 70 |
| 205 | Shelley | 12000.00 | 110 |
+-----+-----------+----------+---------+
-- 只有一个人的部门，查询这些员工
-- 1. 按部门分组求人数，过滤只有一人的部门
-- 2. 用部门id过滤查询员工
SELECT id,
fname,
dept_id
FROM emps
WHERE dept_id IN
(SELECT dept_id
FROM emps
WHERE dept_id IS NOT NULL
GROUP BY dept_id HAVING count(*) = 1);
+-----+----------+---------+
| id | fname | dept_id |
+-----+----------+---------+
| 200 | Jennifer | 10 |
| 203 | Susan | 40 |
| 204 | Hermann | 70 |
+-----+----------+---------+
-
from子查询、行内视图
从查询的查询结果，在查询
select ... from (select ... from) t
-- 平均工资最低的工作岗位
-- 1.按job_id分组求平均工资
SELECT job_id,
avg(sal) a
FROM emps
GROUP BY job_id;
-- 2.查询其平均工资最小值
SELECT min(a)
FROM
(SELECT job_id,
avg(sal) a
FROM emps
GROUP BY job_id) t;
-- 3.按最小工资过滤出job_id
SELECT job_id,
avg(sal) a
FROM emps
GROUP BY job_id HAVING a=
(SELECT min(a)
FROM
(SELECT job_id,
avg(sal) a
FROM emps
GROUP BY job_id) t);
-- 手下人数最多的主管id
-- 1.按主管id分组求人数
SELECT mgr_id,
count(*) c
FROM emps
WHERE mgr_id IS NOT NULL
GROUP BY mgr_id;
+--------+----+
| mgr_id | c |
+--------+----+
| NULL | 1 |
| 100 | 14 |
| 101 | 5 |
| 102 | 1 |
| 103 | 4 |
| 108 | 5 |
...
-- 2.求人数最大值max
SELECT max(c)
FROM
(SELECT mgr_id,
count(*) c
FROM emps
WHERE mgr_id IS NOT NULL
GROUP BY mgr_id) t;
+--------+
| max(c) |
+--------+
| 14 |
+--------+
-- 3.通过max过滤出主管id
SELECT mgr_id,
count(*) count
FROM emps
GROUP BY mgr_id HAVING count=
(SELECT max(c)
FROM
(SELECT mgr_id,
count(*) c
FROM emps
WHERE mgr_id IS NOT NULL
GROUP BY mgr_id) t);
+--------+----+
| mgr_id | c |
+--------+----+
| 100 | 14 |
+--------+----+
4.约束
-
主键约束
数据表中对一行数据的唯一标识
(1).不重复
(2).不能是null值
(3).自动生成索引
一般使用“非业务数据”来作为主键
(1)自动增加 (2)随机生成
USE db1;
-- 主键约束
CREATE TABLE student( id int PRIMARY KEY, stu_num int, name varchar(20));
-- 插入数据
INSERT INTO student
VALUES(1,
1,
'张三');
-- Duplicate entry '1' for key 'PRIMARY'
INSERT INTO student
VALUES(1,
2,
'李四');
-
自增主键
整数类型主键，可以设置自动生成自增的值
CREATE TABLE sutdent (
id int PRIMARY KEY auto_increment,
....
);
-- 修改表，把主键id修改成自增
-- 给student表主键设置自增
ALTER TABLE student MODIFY id int auto_increment;
获取刚产生的自增值
last_insert_id():只获得当前会话产生的自增值
INSERT INTO student(name)
VALUES('王五');
-- 查询自增值
select last_insert_id();
-
外键约束
限制一个字段，只能取指定的主键字段中，存在的值
-- 新建学校表
CREATE TABLE school( id int PRIMARY KEY auto_increment,
name varchar(20));
-- 修改学生表，添加外键字段sch_id
-- 引用school表的主键id
ALTER TABLE student add(sch_id int,
FOREIGN key(sch_id) REFERENCES school(id));
-- 添加两个学校
INSERT INTO school(name)
VALUES('ECUT'),('TEDU');
-- 查询
SELECT *
FROM school;
+----+------+
| id | name |
+----+------+
| 1 | ECUT |
| 2 | TEDU |
+----+------+
SELECT *
FROM student;
+----+---------+--------+--------+
| id | stu_num | name | sch_id |
+----+---------+--------+--------+
| 1 | 1 | 张三 | NULL |
| 2 | NULL | 王五 | NULL |
| 3 | NULL | 赵六 | NULL |
| 4 | NULL | qq | NULL |
| 5 | NULL | aa | NULL |
+----+---------+--------+--------+
--学生1,2,3在ECUT
--学生4,5在TEDU
UPDATE student
SET sch_id =1
WHERE id IN(1,
2,
3);
UPDATE student
SET sch_id =2
WHERE id IN(4,
5);
-- 查询
SELECT *
FROM student;
+----+---------+--------+--------+
| id | stu_num | name | sch_id |
+----+---------+--------+--------+
| 1 | 1 | 张三 | 1 |
| 2 | NULL | 王五 | 1 |
| 3 | NULL | 赵六 | 1 |
| 4 | NULL | qq | 2 |
| 5 | NULL | aa | 2 |
+----+---------+--------+--------+
-
非空
null
-
唯一
自动创建索引
-
检查
1.设置检查条件，约束字段的取值
2.mysql不支持
3.mariadb 支持检查约束
5.多表连接查询(内连接)
两张表，按指定条件，连接成一张表
从连接的结果表中查询
[图片上传失败...(image-4c41d9-1533979159302)]
USE hr;
-- 部门表
SELECT *
FROM depts;
+---------+----------------------+--------+--------+
| dept_id | dept_name | mgr_id | loc_id |
+---------+----------------------+--------+--------+
| 10 | Administration | 200 | 1700 |
| 20 | Marketing | 201 | 1800 |
| 30 | Purchasing | 114 | 1700 |
| 40 | Human Resources | 203 | 2400 |
| 50 | Shipping | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 70 | Public Relations | 204 | 2700 |
| 80 | Sales | 145 | 2500 |
| 90 | Executive | 100 | 1700 |
| 100 | Finance | 108 | 1700 |
| 110 | Accounting | 205 | 1700 |
....
-- 查询员工,显示员工部门名
SELECT e.id,
e.fname,
e.sal,
d.dept_id,
d.dept_name
FROM emps e,
depts d
WHERE e.dept_id = d.dept_id;
+-----+-------------+----------+---------+------------------+
| id | fname | sal | dept_id | dept_name |
+-----+-------------+----------+---------+------------------+
| 200 | Jennifer | 4400.00 | 10 | Administration |
| 201 | Michael | 13000.00 | 20 | Marketing |
| 202 | Pat | 6000.00 | 20 | Marketing |
| 114 | Den | 11000.00 | 30 | Purchasing |
| 115 | Alexander | 3100.00 | 30 | Purchasing |
| 116 | Shelli | 2900.00 | 30 | Purchasing |
| 117 | Sigal | 2800.00 | 30 | Purchasing |
| 118 | Guy | 2600.00 | 30 | Purchasing |
| 119 | Karen | 2500.00 | 30 | Purchasing |
| 203 | Susan | 6500.00 | 40 | Human Resources |
| 120 | Matthew | 8000.00 | 50 | Shipping |
| 121 | Adam | 8200.00 | 50 | Shipping |
| 122 | Payam | 7900.00 | 50 | Shipping |
| 123 | Shanta | 6500.00 | 50 | Shipping |
...
-- 地区表
SELECT *
FROM locations;
+--------+------------------------------------------+-------------+---------------------+-------------------+------------+
| loc_id | street | post | city | state | country_id |
+--------+------------------------------------------+-------------+---------------------+-------------------+------------+
| 1000 | 1297 Via Cola di Rie | 00989 | Roma | NULL | IT |
| 1100 | 93091 Calle della Testa | 10934 | Venice | NULL | IT |
| 1200 | 2017 Shinjuku-ku | 1689 | Tokyo | Tokyo Prefecture | JP |
| 1300 | 9450 Kamiya-cho | 6823 | Hiroshima | NULL | JP |
| 1400 | 2014 Jabberwocky Rd | 26192 | Southlake | Texas | US |
| 1500 | 2011 Interiors Blvd | 99236 | South San Francisco | California | US |
| 1600 | 2007 Zagora St | 50090 | South Brunswick | New Jersey | US |
| 1700 | 2004 Charade Rd | 98199 | Seattle | Washington | US |
...
-- 查询部门，显示部门的城市
SELECT d.dept_id,
d.dept_name,
l.city,
l.state
FROM depts d,
locations l
WHERE d.loc_id = l.loc_id;
+---------+----------------------+---------------------+------------+
| dept_id | dept_name | city | state |
+---------+----------------------+---------------------+------------+
| 10 | Administration | Seattle | Washington |
| 20 | Marketing | Toronto | Ontario |
| 30 | Purchasing | Seattle | Washington |
| 40 | Human Resources | London | NULL |
| 50 | Shipping | South San Francisco | California |
| 60 | IT | Southlake | Texas |
| 70 | Public Relations | Munich | Bavaria |
| 80 | Sales | Oxford | Oxford |
| 90 | Executive | Seattle | Washington |
| 100 | Finance | Seattle | Washington |
| 110 | Accounting | Seattle | Washington |
...
-- 查询部门,显示部门经理名
SELECT d.dept_id,
d.dept_name,
e.fname mgr
FROM depts d,
emps e
WHERE d.mgr_id = e.id;
+---------+------------------+-----------+
| dept_id | dept_name | mgr |
+---------+------------------+-----------+
| 10 | Administration | Jennifer |
| 20 | Marketing | Michael |
| 30 | Purchasing | Den |
| 40 | Human Resources | Susan |
| 50 | Shipping | Adam |
| 60 | IT | Alexander |
| 70 | Public Relations | Hermann |
| 80 | Sales | John |
| 90 | Executive | Steven |
| 100 | Finance | Nancy |
| 110 | Accounting | Shelley |
+---------+------------------+-----------+
-
自连接
一张表看做是两张表进行连接
-- 查询员工,显示员工主管名
SELECT e1.id,
e1.fname,
e1.sal,
e2.fname mgr
FROM emps e1, emps e2
WHERE e1.mgr_id=e2.id;
+-----+-------------+----------+-----------+
| id | fname | sal | mgr |
+-----+-------------+----------+-----------+
| 101 | Neena | 17000.00 | Steven |
| 102 | Lex | 17000.00 | Steven |
| 103 | Alexander | 9000.00 | Lex |
| 104 | Bruce | 6000.00 | Alexander |
| 105 | David | 4800.00 | Alexander |
| 106 | Valli | 4800.00 | Alexander |
| 107 | Diana | 4200.00 | Alexander |
| 108 | Nancy | 12000.00 | Neena |
| 109 | Daniel | 9000.00 | Nancy |
| 110 | John | 8200.00 | Nancy |
| 111 | Ismael | 7700.00 | Nancy |
| 112 | Jose Manuel | 7800.00 | Nancy |
| 113 | Luis | 6900.00 | Nancy |
...
6.外连接
外连接，非标准sql语法，每种数据库的外连接语法有所不同。
sql server :where a.id(+) = b.xid
oracle: where a.id *= b.xid;
mysql:没有相应的非标准sql语法.
-- 内连接格式
SELECT ...
FROM a
JOIN b on(a.id = b.xid)
JOIN c on(...);
-- 外连接格式
SELECT ...
FROM a
LEFT JOIN b on(a.id=b.xid)
SELECT ...
FROM a
RIGHT JOIN b on(a.id=b.xid)
