基本语句操作
创建数据库:
create database database-name
删除数据库:
drop database database-name
修改数据名:
RENAME DATABASE db_name TO new_db_name
创建新表:
create table table-name(
col1-name type1 [not null] [primary key] [auto_increment],
col2-name type2 ,
……
)
auto_increment ->自动增长,只针对数值类型
根据旧表创建新表:
create table table_name like table_old_name
create table table_name as select col1,col2... from table_old_name;
删除表:
drop table table_name;
修改表名:
alter table table_name rename table_new_name;
增加一个列:
alter table table_name add column col type [not null] ...;
删除一个列:
alter table table_name drop column col_name;
修改列名:
alter table table_name change column col_old_name col_new_name type [not null]...;
修改列的属性:
alter table table_name change column col_old_name col_old_name type [not null]...;
选择:
select * from table_name where ...
插入:
insert into table_name(col1_name,col2_name...) values(...);
拷贝表所有数据:
insert into table1_name(table1_name.col1_name,table1_name.col2_name...) select table2_name.col1_name,
table2_name.col_name...
from table2_name
跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
删除:
delect from table_name where
更新:
update table_name set col1_nameee=value ...where ...
查找:
select * from table_name where ...
排序:
select * from table_name order by col1_name asc/desc;
asc 升序
desc 降序
总数:
select count(*|col_name) as count_name from table_name where ...
求和:
select sum(col_name) as sum_name from table_name where ...
求均值:
select avg(col_name) as avg_name from table_name where ...
最大:
select max(col_name) as max_name from table_name where ...
最小:
select min(col_name) as min_name from table_name where ...
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
Join语法概述
JOIN 按照功能大致分为如下三类:
INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。
select * from table1_name inner join table2_name on teable1_name.col = teable2_name.col where ...
![这里写图片描述](http://images.cnblogs.com/cnblogs_com/BeginMan/486940/o_sql1.png)
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...
LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
select * from table1_name left join table2_name on teable1_name.col = teable2_name.col where ...
![这里写图片描述](http://images.cnblogs.com/cnblogs_com/BeginMan/486940/o_sql3.png)
select * from table1_name inner join table2_name on teable1_name.col = teable2_name.col where teable2_name.col is null and ...
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;
![这里写图片描述](http://images.cnblogs.com/cnblogs_com/BeginMan/486940/o_sql2.png)
分组:
select *|count,avg,sum,max,min from table_name group by table_name.col_name
子查询:
#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...
)
between 数值1 and 数值2
select * from table_name where table_name.col between 数值1 and 数值2
not between 数值1 and 数值2
select * from table_name where table_name.col between 数值1 and 数值2
两张关联表,删除主表中已经在副表中没有的信息:
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
limit 返回前几条或者中间某几行数据
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。
例:
SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000
去重:
有时需要查询出某个字段不重复的记录,这时可以使用mysql提供的distinct这个关键字来过滤重复的记录。
select distinct col_name from table_name;
实题训练
题目在线测试地址:https://www.nowcoder.com/ta/sql
第一题:
查找最晚入职员工的所有信息
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
|
birth_date
|
first_name
|
last_name
|
gender
|
hire_date
|
---|
10008
|
1958-02-19
|
Saniya
|
Kalloufi
|
M
|
1994-09-15
|
题解:
select * from employees order by hire_date desc limit 0,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_no
|
birth_date
|
first_name
|
last_name
|
gender
|
hire_date
|
---|
10005
|
1955-01-21
|
Kyoichi
|
Maliniak
|
M
|
1989-09-12
|
select * from employees order by hire_date desc limit 2,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_no
|
salary
|
from_date
|
to_date
|
dept_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"
第四题:
查找所有已经分配部门的员工的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_name
|
first_name
|
dept_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;
第五题:
查找所有员工的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_name
|
first_name
|
dept_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;
第六题:
查找所有员工入职时候的薪水情况,给出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_no
|
salary
|
---|
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;
第七题:
查找薪水涨幅超过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_no
|
t
|
---|
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;
第八题:
找出所有员工当前(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;
第九题:
获取所有部门当前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_no
|
emp_no
|
salary
|
---|
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'
第十题:
获取所有非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
);
第十一题:
获取所有员工当前的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_no
|
manager_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';
第十二题:
获取所有部门中当前员工薪水最高的相关信息,给出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_no
|
emp_no
|
salary
|
---|
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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)