mysql题目修改语句_mysql常用语句及实题训练

基本语句操作

创建数据库：

create database database-name

1

删除数据库：

drop database database-name

1

修改数据名：

RENAME DATABASE db_name TO new_db_name

1

创建新表：

create table table-name(

col1-name type1 [not null] [primary key] [auto_increment],

col2-name type2 ,

……

)

1

2

3

4

5

auto_increment ->自动增长,只针对数值类型

根据旧表创建新表：

create table table_name like table_old_name

1

create table table_name as select col1,col2... from table_old_name;

1

删除表：

drop table table_name;

1

修改表名：

alter table table_name rename table_new_name;

1

增加一个列：

alter table table_name add column col type [not null] ...;

1

删除一个列：

alter table table_name drop column col_name;

1

修改列名：

alter table table_name change column col_old_name col_new_name type [not null]...;

1

修改列的属性：

alter table table_name change column col_old_name col_old_name type [not null]...;

1

选择：

select * from table_name where ...

1

插入：

insert into table_name(col1_name,col2_name...) values(...);

1

拷贝表所有数据：

insert into table1_name(table1_name.col1_name,table1_name.col2_name...) select table2_name.col1_name,

table2_name.col_name...

from table2_name

1

2

3

跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)

insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件

1

删除：

delect from table_name where

1

更新：

update table_name set col1_nameee=value ...where ...

1

查找：

select * from table_name where ...

1

排序：

select * from table_name order by col1_name asc/desc;

1

asc 升序

desc 降序

总数：

select count(*|col_name) as count_name from table_name where ...

1

求和：

select sum(col_name) as sum_name from table_name where ...

1

求均值：

select avg(col_name) as avg_name from table_name where ...

1

最大：

select max(col_name) as max_name from table_name where ...

1

最小：

select min(col_name) as min_name from table_name where ...

1

union和union all

UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。

UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型。

同时，每条 SELECT 语句中的列的顺序必须相同.

**注释：另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

注意：1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名

2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

**

union的用法及注意事项

union:联合的意思，即把两次或多次查询结果合并起来。

要求：两次查询的列数必须一致

推荐：列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样

可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。

如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。

如果不想去掉重复的行，可以使用union all。

如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

SELECT column_name FROM table1

UNION

SELECT column_name FROM table2

1

2

3

Join语法概述

JOIN 按照功能大致分为如下三类：

INNER JOIN(内连接,或等值连接)：取得两个表中存在连接匹配关系的记录。

select * from table1_name inner join table2_name on teable1_name.col = teable2_name.col where ...

1

select * from table1_name inner join table2_name on teable1_name.col = teable2_name.col where table1_name.col is null or table2_name.col is null and...

1

 

LEFT JOIN(左连接)：取得左表(table1)完全记录，即是右表(table2)并无对应匹配记录。

select * from table1_name left join table2_name on teable1_name.col = teable2_name.col where ...

1

select * from table1_name inner join table2_name on teable1_name.col = teable2_name.col where teable2_name.col is null and ...

1

 

RIGHT JOIN(右连接)：与 LEFT JOIN 相反，取得右表(table2)完全记录，即是左表(table1)并无匹配对应记录。

Full join:

select * from A left join B on B.name = A.name

union

select * from A right join B on B.name = A.name;

1

2

3

分组：

select *|count,avg,sum,max,min from table_name group by table_name.col_name

1

子查询：

#in

select * from table1_name where tabel1_name.col1 in (

select table2_name.col1 from teable2_name where...

)

#not in

select * from table1_name where tabel1_name.col1 not in (

select table2_name.col1 from teable2_name where...

)

1

2

3

4

5

6

7

8

between 数值1 and 数值2

select * from table_name where table_name.col between 数值1 and 数值2

1

not between 数值1 and 数值2

select * from table_name where table_name.col between 数值1 and 数值2

1

两张关联表，删除主表中已经在副表中没有的信息：

delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )

1

limit 返回前几条或者中间某几行数据

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

1

having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。

例：

SELECT region, SUM(population), SUM(area)

FROM bbc

GROUP BY region

HAVING SUM(area)>1000000

1

2

3

4

去重：

有时需要查询出某个字段不重复的记录，这时可以使用mysql提供的distinct这个关键字来过滤重复的记录。

select distinct col_name from table_name;

1

实题训练

第一题：

查找最晚入职员工的所有信息

CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` char(1) NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));

输入描述:

无

输出描述:

emp_nobirth_datefirst_namelast_namegenderhire_date

10008

1958-02-19

Saniya

Kalloufi

M

1994-09-15

题解：

select * from employees order by hire_date desc limit 0,1;

1

2

第二题：

查找入职员工时间排名倒数第三的员工所有信息

CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` char(1) NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));

输入描述:

无

输出描述:

emp_nobirth_datefirst_namelast_namegenderhire_date

10005

1955-01-21

Kyoichi

Maliniak

M

1989-09-12

select * from employees order by hire_date desc limit 2,1;

1

第三题：

查找各个部门当前(to_date=’9999-01-01’)领导当前薪水详情以及其对应部门编号dept_no

CREATE TABLE `dept_manager` (

`dept_no` char(4) NOT NULL,

`emp_no` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

emp_nosalaryfrom_dateto_datedept_no

10002

72527

2001-08-02

9999-01-01

d001

10004

74057

2001-11-27

9999-01-01

d004

10005

94692

2001-09-09

9999-01-01

d003

10006

43311

2001-08-02

9999-01-01

d002

10010

94409

2001-11-23

9999-01-01

d006

select salaries.*,dept_manager.dept_no from salaries, dept_manager

where salaries.emp_no = dept_manager.emp_no

and salaries.to_date = "9999-01-01"

and dept_manager.to_date = "9999-01-01";

1

2

3

4

第四题：

查找所有已经分配部门的员工的last_name和first_name

CREATE TABLE `dept_emp` (

`emp_no` int(11) NOT NULL,

`dept_no` char(4) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` char(1) NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));

输入描述:

无

输出描述:

last_namefirst_namedept_no

Facello

Georgi

d001

省略

省略

省略

Piveteau

Duangkaew

d006

select last_name,first_name,dept_emp.dept_no from employees,dept_emp where employees.emp_no=dept_emp.emp_no;

1

第五题：

查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

CREATE TABLE `dept_emp` (

`emp_no` int(11) NOT NULL,

`dept_no` char(4) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` char(1) NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));

输入描述:

无

输出描述:

last_namefirst_namedept_no

Facello

Georgi

d001

省略

省略

省略

Sluis

Mary

NULL(在sqlite中此处为空,MySQL为NULL)

select employees.last_name,employees.first_name,dept_emp.dept_no from employees left join dept_emp

on employees.emp_no=dept_emp.emp_no;

1

2

第六题：

查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_no进行逆序

CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` char(1) NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

emp_nosalary

10011

25828

省略

省略

10001

60117

select employees.emp_no,salaries.salary from employees

inner join salaries on employees.emp_no = salaries.emp_no where employees.hire_date = salaries.from_date order by employees.emp_no desc;

1

2

第七题：

查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

emp_not

10001

17

10004

16

10009

18

select salaries.emp_no,count(salaries.salary) as t from salaries group by salaries.emp_no

having count(salaries.salary)> 15;

1

2

第八题：

找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

salary

94692

94409

88958

88070

74057

72527

59755

43311

25828

select salaries.salary from salaries where to_date='9999-01-01' group by salary order by salary desc;

1

第九题：

获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date=’9999-01-01’

CREATE TABLE `dept_manager` (

`dept_no` char(4) NOT NULL,

`emp_no` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

dept_noemp_nosalary

d001

10002

72527

d004

10004

74057

d003

10005

94692

d002

10006

43311

d006

10010

94409

select dm.dept_no,dm.emp_no,s.salary

from salaries s, dept_manager dm

where s.emp_no=dm.emp_no and dm.to_date='9999-01-01' and s.to_date='9999-01-01'

1

2

3

第十题：

获取所有非manager的员工emp_no

CREATE TABLE `dept_manager` (

`dept_no` char(4) NOT NULL,

`emp_no` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` char(1) NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));

输入描述:

无

输出描述:

emp_no

10001

10003

10007

10008

10009

10011

select emp_no from employees where employees.emp_no not in (

select emp_no from dept_manager

);

1

2

3

第十一题：

获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=’9999-01-01’。

结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

CREATE TABLE `dept_emp` (

`emp_no` int(11) NOT NULL,

`dept_no` char(4) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `dept_manager` (

`dept_no` char(4) NOT NULL,

`emp_no` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

输入描述:

无

输出描述:

emp_nomanager_no

10001

10002

10003

10004

10009

10010

select dept_emp.emp_no,dept_manager.emp_no as manager_no from dept_emp left join dept_manager

on dept_emp.dept_no = dept_manager.dept_no where dept_emp.emp_no!=dept_manager.emp_no

and dept_emp.to_date='9999-01-01' and dept_manager.to_date='9999-01-01';

1

2

3

第十二题：

获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

CREATE TABLE `dept_emp` (

`emp_no` int(11) NOT NULL,

`dept_no` char(4) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

输入描述:

无

输出描述:

dept_noemp_nosalary

d001

10001

88958

d002

10006

43311

d003

10005

94692

d004

10004

74057

d005

10007

88070

d006

10009

95409

select dept_emp.dept_no,dept_emp.emp_no,max(salaries.salary) as salary

from dept_emp,salaries where dept_emp.emp_no=salaries.emp_no and dept_emp.to_date='9999-01-01'

and salaries.to_date='9999-01-01' group by dept_emp.dept_no;