mysql常用语句

#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;