#sql
## SELECT
SELECT
c1`
c2`
c3`
FROM t1
INNER JOIN t2 on t1.fk=t2.pk
LEFIT|RIGHT JOIN t3 on t1.fk = t3.pk
GROUP BY 分组
HAVING 过滤条件
ORDER BY 排序
LIMIT 5 OFFSET 1000; 偏移一千条取之后五条
-- 查询
1. 查询staff表 id,name ,salary 的信息
select id,name,salary from staff;
-- 查询特定工资
select id,name,salary*14 as income from staff;
-- 查询年薪income 并且将奖金为null 改为3 与薪水相加
select id,name,bonus,salary,salary+ifnull( bonus,3) as income from staff;
+----+-------+----------+----------+----------+
| id | name | bonus | salary | income |
+----+-------+----------+----------+----------+
| 1 | alice | 50000.00 | 20000.00 | 70000.00 |
| 2 | bob | NULL | 15000.00 | 15003.00 |
| 3 | tom | NULL | 9000.00 | 9003.00 |
| 4 | jack | 30000.00 | 18000.00 | 48000.00 |
| 5 | rose | NULL | 8000.00 | 8003.00 |
| 6 | john | 30000.00 | 19000.00 | 49000.00 |
| 7 | scott | NULL | 10000.00 | 10003.00 |
| 8 | grace | NULL | 13000.00 | 13003.00 |
| 9 | marry | NULL | 3000.00 | 3003.00 |
+----+-------+----------+----------+----------+
## where
-- AND,OR
-- IN, NOT IN();
select * from staff where dept_id in(1,2);
+----+-------+--------+------------+---------+-------+------+----------+----------+
| id | name | gender | birth | dept_id | level | mgr | salary | bonus |
+----+-------+--------+------------+---------+-------+------+----------+----------+
| 1 | alice | female | 1999-04-26 | 1 | L4 | NULL | 20000.00 | 50000.00 |
| 2 | bob | male | 1984-02-01 | 1 | L2 | 1 | 15000.00 | NULL |
| 3 | tom | male | 2001-11-07 | 2 | L1 | 2 | 9000.00 | NULL |
| 4 | jack | male | 1989-12-31 | 2 | L3 | 1 | 18000.00 | 30000.00 |
| 5 | rose | female | 2003-08-26 | 2 | L1 | 4 | 8000.00 | NULL |
+----+-------+--------+------------+---------+-------+------+----------+----------+
5 rows in set (0.01 sec)
-- BETWEEN AND
-- LIKE
- 模糊查询 ‘_’ 代表一个字符 ‘%’代替任意字符
select * from staff where name like '__o%';
+----+-------+--------+------------+---------+-------+------+----------+-------+
| id | name | gender | birth | dept_id | level | mgr | salary | bonus |
+----+-------+--------+------------+---------+-------+------+----------+-------+
| 7 | scott | male | 2000-09-09 | 3 | L2 | 6 | 10000.00 | NULL |
+----+-------+--------+------------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)
-- IS NULL, IS NOT NULL
-- DISTINCT 删除重复值
select distinct level from staff;
+-------+
| level |
+-------+
| L4 |
| L2 |
| L1 |
| L3 |
+-------+
-- 不等于 <>
select * from staff where gender = 2 and level <>'L1';
+----+-------+--------+------------+---------+-------+------+----------+----------+
| id | name | gender | birth | dept_id | level | mgr | salary | bonus |
+----+-------+--------+------------+---------+-------+------+----------+----------+
| 1 | alice | female | 1999-04-26 | 1 | L4 | NULL | 20000.00 | 50000.00 |
| 8 | grace | female | 2004-03-26 | 9 | L2 | 6 | 13000.00 | NULL |
+----+-------+--------+------------+---------+-------+------+----------+----------+
2 rows in set (0.00 sec)
## 聚合函数
- max
- min
- sum
- avg
- count
select
level ,
max(salary),
min(salary),
count(id)
from staff
group by level;
select
level(不能查询分类GROUP之外的列) ,
max(salary),
min(salary),
count(id)
from staff
group by gender;
-- desc 降序 默认升序
select id ,name ,dept_id,salary from staff order by dept_id desc,salary desc;
select id ,name ,salary from staff order by salary desc ;
+----+-------+----------+
| id | name | salary |
+----+-------+----------+
| 1 | alice | 20000.00 |
| 6 | john | 19000.00 |
| 4 | jack | 18000.00 |
| 2 | bob | 15000.00 |
| 8 | grace | 13000.00 |
| 7 | scott | 10000.00 |
| 3 | tom | 9000.00 |
| 5 | rose | 8000.00 |
| 9 | marry | 3000.00 |
+----+-------+----------+
9 rows in set (0.00 sec)
-- limit 3 offset 5
select id ,name ,salary from staff order by salary desc limit 3 offset 5;
+----+-------+----------+
| id | name | salary |
+----+-------+----------+
| 7 | scott | 10000.00 |
| 3 | tom | 9000.00 |
| 5 | rose | 8000.00 |
+----+-------+----------+
3 rows in set (0.00 sec)
-- LIMIT 3,4 4为打印数,3为偏移量
select id ,name ,salary from staff order by salary desc limit 3,4;
+----+-------+----------+
| id | name | salary |
+----+-------+----------+
| 2 | bob | 15000.00 |
| 8 | grace | 13000.00 |
| 7 | scott | 10000.00 |
| 3 | tom | 9000.00 |
+----+-------+----------+
4 rows in set (0.00 sec)
select
dept_id ,
max(salary) as max,
min(salary) as min,
count(id) n
from staff
group by dept_id
order by n desc
limit 3;
select
dept_id ,
max(salary) as max,
min(salary) as min,
avg(salary) as avg,
count(id) n
from staff
group by dept_id
having avg > 12000;
select
dept_id ,
max(salary) as max,
min(salary) as min,
avg(salary) as avg,
count(id) n
from staff
where level != 'L1'
group by dept_id
having avg <> 15000
order by avg desc ;
```sql
## 表连接
- 笛卡尔积
- 内连接(等值连接)
- 外连接
-左外连接
-右外连接
-全连接
#应用
select s.id,s.name,d.name dept_name,city
from staff s, dept d
where dept_id=d.id;
select
s.id,
s.name,
d.name dept,
city
from staff s
join dept d
on s.dept_id = d.id;
select
s.id,
s.name,
d.name dept,
city
from staff s
left join dept d
on s.dept_id = d.id;
select
s.id,
s.name,
d.name dept,
city
from staff s
right join dept d
on s.dept_id = d.id;
//全连接,联合union
select
s.id,
s.name,
d.name dept,
city
from staff s
left join dept d
on s.dept_id = d.id
union
select
s.id,
s.name,
d.name dept,
city
from staff s
right join dept d
on s.dept_id = d.id;
-- 创建一个视图:基于表的查询创建的一个命名的sql语句
create view ()
select
d.id,
d.name,
count(s.id) n
from staff s
right join dept d
on s.dept_id = d.id
group by d.id;
-- 各个城市有几个部门
select
city ,
count(id) n
from dept
group by city;
-- 各个城市有几个员工
create view v_city_staff_num
as
select
d.city,
count(d.id)
from dept d
left join staff s
on s.dept_id=d.id
group by d.city;
--哪个员工没有部门
select
s.name,
s.dept_id,
d.name
from staff s
left join dept d
on s.dept_id=d.id;
--哪个部门没有员工
select
d.name,
count(s.id) n
from dept d
left join staff s
on d.id= s.dept_id
group by d.name;
select
s.id,
b.name ,
count(s.id) n
from staff s
left join staff b
on s.mgr=b.id
group by b.name,s.id;
select
b.id,
count(s.id) n,
b.name boss
from staff s
right join staff b
on s.mgr=b.id
group by b.id,boss;
--
select *
from staff
where dept_id in
(select
dept_id
from staff
where name='tom'
)
and
salary in (
select
salary
from staff
where name = 'tom');
select
d.*
from dept d
left join (select
dept_id,
count(id)
from staff
group by dept_id ) t
on t.dept_id=d.id;
## 窗口函数
- row_number() 生成序号,无并列
- rank() 有并列名次,不连续
- dense_rank() 有并列名次,并且是连续的
select
id,
name,
salary,
row_number() over(order by salary desc) n
from staff;
select
id,
name,
salary,
rank() over(order by salary desc) n
from staff;
select
id,
name,
salary,
dense_rank() over(order by salary desc) n
from staff
limit 3;
select *
from(
select
gender,
id,
name,
salary,
dense_rank() over(partition by gender order by salary desc) n
from staff
)t
where t.n = 1;
select *
from(
select
s.name,
s.salary,
city,
dense_rank() over (partition by city order by salary desc) n
from dept d
left join staff s
on s.dept_id=d.id
) t
where t.n = 1;
create view v_staff_dept1
as
select * from staff where dept_id=1;
//创建表的空结构
create table staff2
as
select * from staff where 1>2;
create table staff_2000
as
select * from staff where birth< '2000-1-1';
## 作业
创建表t1,t2
create table t1
(
id int not null,
name char(10)
);
INSERT into t2 values(1,'b');
INSERT into t2 values(2,'c');
INSERT into t2 values(3,'d');
INSERT into t2 values(4,'e');
INSERT into t2 values(5,'f');
update t1
set
create table t2
(
id int not null,
name char(10)
);
- 交集 内连接
select * from t2 where name in (select name from t1);
+----+------+
| id | name |
+----+------+
| 1 | b |
| 2 | c |
| 3 | d |
+----+------+
- 并集
union
select t1.name from t1
union
select t2.name from t2;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 1 | b |
| 2 | c |
| 3 | d |
| 4 | e |
| 5 | f |
+----+------+
select t1.*,t2.*
from t1
left join t2
on t1.name = t2.name;
select t1.*,t2.*
from t1
right join t2
on t1.name = t2.name;
select t1.*
from t1
join t2
on t1.name = t2.name;
- 差 NOT IN,?它存在另一个不存在
select * from t2 where name not in (select name from t1);
+----+------+
| id | name |
+----+------+
| 4 | e |
| 5 | f |
+----+------+
-- t2-t1
select t2.name, t.id
from t2
left join
(
select * from t2 where name in (select name from t1)
) t
on t2.name=t.name
where t.id is null;
select t1.name,t.id
from t1
left join
(
select * from t2 where name in (select name from t1)
) t
on t1.name= t.name
where t.id is null;
select t1.name,
t2.name,
count(t1.name) ,
count(t2.name)
from t1
join t2
on t1.id=t2.id
group by t1.name,t2.name;
select name
from t1
join
select * from t1
union
select * from t2;
--先全集 然后再和t1 右连接 where is
t1-t2
select
t.id,
t.name,
count(t.id) n
from t1
left join (select t1.* from t1
union
select t2.* from t2) t
on t1.name =t.name
group by t.name
having n <=1;
t2-t1
select
t.name,
count(t.id) n
from t2
left join
(select * from t1
union
select * from t2) t
on t.name=t2.name
GROUP by t.name
having n <=1;