sql总结

目录[-]

• 最基本的sql语句（针对mysql）：
• 列别名
• 通配符过滤
• 空值处理
• 多值匹配
• 数据分组
• Having语句
• 限制结果集范围
• 去掉重复数据
• UNION语句
• CASE语句
• 索引
• 子查询
• 子查询分类
• 返回单一值
• 返回一列
• 返回多列（一行）
• 返回表（多行多列）
• Exists子查询
• mysql left join,right join,inner join用法分析
• 1.left join(左联接) 
• 2.right join(右联接) 
• 3.inner join(相等联接或内联接) 
• 4.Cross Join 交叉连接
• 简单的多表查询
• 题目1
• 高级篇
• 练习

 

最基本的sql语句（针对mysql）：

创建数据库

create database test;

use test;

删除数据库

drop database test;

创建表（字段中不能有#号）

create table admin(admin_id int,admin_password int);

删除表

drop table admin;

显示表结构

desc admin;

增

 插入   到   表名        字段名                  值

insert into admin(admin_id,admin_password) values(321,321);

 

删

 删除  来自  表名  为   字段名    值

delete from admin where admin_id='321' ;

 

改

 更新   表名 建立  字段名    值    条件   字段名    值

update admin set admin_id='321321' where admin_id='321';

修改表字段类型

alter table admin modify column admin_id varchar(20);

修改表字段名

alter table admin change admin_id id int ;（注意一定要加字段类型，前面字段是需要修改的，后面的是修改后的）

增加表字段名

alter table admin add admin_other varchar(20) [after admin_id];//中括号为可选，指定插入到某列之后，没有before

调整字段顺序

ALTER TABLE admin CHANGE admin_other admin_other varchar(20) after admin_password;

删除表字段

 alter table admin drop column admin_other;

添加主键

 alter table admin add primary key(admin_id);

删除主键

 alter table admin drop primary key(admin_id);

查

 选择    来自   表名   条件   字段名

select*from admin where admin_id='321';

分页

"select * from articles limit " + (pageNo-1)*pageSize + "," + pageSize;//(pageNo-1)*pageSize起始记录，pageSize查询个数

简单查询

1.在查询结果中显示列名
a.用 as关键字：select name as '姓名'   from students order by age

b.直接表示：select name '姓名'   from students order by age

2.精确查找:
a.用 in（或者not in）限定范围：select * from students where native in ('湖南', '四川')
b. between...and：select * from students where age between 20 and 30
c. 比较测试符：（包括=, <>,<,<=,>,>=）select * from students where name = '李山'
d.like:select * from students where name  like  ' 李%' (注意查询条件中有“%”，则说明是 部分匹配，而且还有先后信息在里面，即查找以“李”开头的匹配项。所以若查询有“李”的所有对象，应该命令：'% 李%';若是第二个字为李，则应为'_李%'或'_李'或'_李_'。)
e.[]匹配检查符：select * from courses where cno like ' [AC]%' (表示 或的关系，与"in(...)"类似，而且"[]"可以表示 范围，如：select * from courses where cno like ' [A-C]%')注：关于这个字符我在mysql里用的时候mysql把它当两个普通自符处理的。

[^]stockname like '[^F-M]%' --------- (^排除指定范围)

f.逆序，select * from admin order by admin_id DESC;(升序 ASC)

g.组合条件 and，select * from test where admin_id=1 and admin_password="voip";

h.分组 group by，select * from admin group by admin_id;

i.limit，select * from test limit 0,3  # 查询表中前3条记录

a. count()求总数，如：select count(*)  from students (求学生总人数)
b. avg(列)求平均，如：select avg(mark)  from grades where cno=’B2’

c. max(列)和 min(列)，求最大与最小

列别名

Select xxx as ‘我的列’ from T1;//则我的列将作为表头列

Select ‘我的列’, xxx from T1;//则我的列将作为结果集中第一列的数据

通配符过滤

         Like语句中，”_”表示匹配单个字符，”%”匹配0个或多个字符。

空值处理

         如果没有指定值则为NULL，表示“不知道”。SQL中使用is null、is not null来进行空值判断，查找为null的结果，不能用“=”，或者“<>”，而用is语句，如where xxx is null;where xxx is not null;

多值匹配

         In语句，如where xxx in (2,12,23);或者not in

         Between and语句，如where xxx between 23 and 28;

数据分组

         Group by语句，必须放在where语句之后。

         Select age,name count(*) from employee group by age;//错误，select查询的字段只能为group by中的字段和聚合函数。

Select age,max(salary),name count(*) from employee group by age;//正确

Having语句

         在where中不能使用聚合函数，必须使用having，having要位于group by之后。

         Select age,count(*) as 人数 from employee group by age having count(*)>1;

         Having中不能使用select中未参与的列，having不能代替where，作用不一样，having主要是对分组后信息进行过滤，能用的列于select中能用的列是一样的；where是对原始数据进行过滤。

限制结果集范围

         Select top 3 * from employee order by salary desc;

去掉重复数据

         Distinct语句

         对整个结果集进行数据重复处理，而不是针对每一个列。

         Select distinct department,subcompany form employee;// 将department,subcompany看出一个整体不重复的数据

UNION语句

         注意：每个结果集必须有相同的列数，并且每个结果集的列必须类型相容（相同或可转换）

         Select number,name,age,department from employee

Union

         Select idcardnumber,name,age,’临时工，无部门’ from tempemloyee;

         Union合并两个查询的结果集，默认将完全重复的数据行合并为一条。Union要进行重复值扫描，效率低，因此如果不是确定要合并重复行，就用union all。

空值处理函数

         Select isnull(name,’佚名’) as 姓名 from employee;

//isnull(expression,value):如果expression不为空则返回expression，否则返回value。

CASE语句

1.     

Select name,{

         Case level

         When 1 then ‘普通客户’

         When 2 then ‘会员’

When 3 then ‘VIP’

else ‘普通客户’

end

} as 客户类型

From customer;

2.

Select name,

{

Case

When salary<2000 then ‘低收入’

When salary>=2000 and salary<=5000 then ‘中等收入’

Else ‘高收入’

End

} as 收入水平

From employee;

索引

索引相当于字典的目录，提高查询效率。

         缺点：占据空间；降低insert、update、delete的速度，因为修改数据需要同步更新索引。

         使用like模糊匹配、函数、类型转换等即使建立索引还是会造成全局扫描，注意避免。

表连接JOIN

         Join、inner join、left join、right join、full join、cross join。

Select o.billNo,c.name,c.age

from T_orders as o join T_customers as c on o.customerId=c.Id

where c.age>15;

子查询

        

      将一个查询语句作为一个结果集供其他sql语句使用，就像使用普通表一样，被当作结果集的查询语句被称为子查询（语句内部的查询语句）。索引可以使用表的地方几乎都可以子查询来代替。(子查询必须用括号括起来)

Select * from (select * from T2 where age < 30)

 

子查询分类

分类标准：1、子查询出现的位置；2、子查询的返回值形式

 

返回单一值

单值作为子查询,返回一行一列

Select 1 as f1,2,(select min(yearpublish) from book),(select max(yearpublish) from book) as f4;

返回一列

多行单列的子查询

         Select * from reader

         Where yearofjoin in (select distinct yearpublish from book);

返回多列（一行）

在参与比较时，使用括号可以构建一行：

select t_name,gender,c_name from teacher_class where (gender,c_name) = (select distinct gender,c_name from teacher_class where t_name=’李明’ and c_name=’php0115’ limit 1)

返回表（多行多列）

如果用于在from子句内，要求使用一个表，而能是一个结果，应该给这个查询结果起别名

Select * from (select t_name,c_name,days from teacher_class where days >15) as temp where t_name like ‘李’;

外部查询使用的列名，是由子查询指定的

Exists子查询

Exists(subquery)

判断依据：如果子查询可以返回数据则认为exists表达式返回为真，否者返回为假

下面的两个语句完成同样的事情

Select * from teacher_class where exists (select * from teacher where teacher_class.id=t_id);

Select * from teacher_class where id in (select t_id from teacher);

解决思路不同：

Exits先获得每一条teacher_class的数据，然后获得id字段，去teacher表内查找对应值，找到符合条件，相当于多重循环

In 先获得所有id的可能数据，再检索teacher_class数据，判断当前id是否在id集合内

 

 

mysql left join,right join,inner join用法分析

 

下面是例子分析
表A记录如下： 
aID        aNum 
1           a20050111 
2           a20050112 
3           a20050113 
4           a20050114 
5           a20050115 

表B记录如下: 
bID        bName 
1            2006032401 
2           2006032402 
3           2006032403 
4           2006032404 
8           2006032408 

创建这两个表SQL语句如下： 
CREATE TABLE  a 
aID int( 1 ) AUTO_INCREMENT PRIMARY KEY , 
aNum char( 20 ) 
) 
CREATE TABLE b( 
bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , 
bName char( 20 )  
) 

INSERT INTO a 
VALUES ( 1, 'a20050111' ) , ( 2, 'a20050112' ) , ( 3, 'a20050113' ) , ( 4, 'a20050114' ) , ( 5, 'a20050115' ) ; 

INSERT INTO b 
VALUES ( 1, ' 2006032401' ) , ( 2, '2006032402' ) , ( 3, '2006032403' ) , ( 4, '2006032404' ) , ( 8, '2006032408' ) ; 

实验如下: 

1.left join(左联接) 

sql语句如下:  
SELECT * FROM a 
LEFT JOIN  b  
ON a.aID =b.bID 

结果如下: 
aID        aNum                   bID           bName 
1            a20050111         1               2006032401 
2            a20050112         2              2006032402 
3            a20050113         3              2006032403 
4            a20050114         4              2006032404 
5            a20050115         NULL       NULL 
（所影响的行数为 5 行） 

结果说明: 
        left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的. 
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID). 
B表记录不足的地方均为NULL. 

2.right join(右联接) 

sql语句如下:  
SELECT  * FROM a 
RIGHT JOING b  
ON a.aID = b.bID 

结果如下: 
aID        aNum                   bID           bName 
1            a20050111         1               2006032401 
2            a20050112         2              2006032402 
3            a20050113         3              2006032403 
4            a20050114         4              2006032404 
NULL    NULL                   8              2006032408 
（所影响的行数为 5 行） 

结果说明: 
        仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充. 

3.inner join(相等联接或内联接) 

sql语句如下:  
SELECT * FROM  a 
INNER JOIN  b 
ON a.aID =b.bID 

等同于以下SQL句: 
SELECT *  
FROM a,b 
WHERE a.aID = b.bID 

结果如下: 
aID        aNum                   bID           bName 
1            a20050111         1               2006032401 
2            a20050112         2              2006032402 
3            a20050113         3              2006032403 
4            a20050114         4              2006032404 

结果说明: 
        很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录. 
LEFT JOIN操作用于在任何的 FROM 子句中， 

组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即 
使在第二个（右边）表中并没有相符值的记录。  

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2  
说明：table1, table2参数用于指定要将记录组合的表的名称。 
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的 
名称。 
compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。 

如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误。

4.Cross Join 交叉连接

在Mysql中，Cross Join可以用逗号表达式表示，例如(table1, table 2)。在Mysql中，Cross Join 和 Inner Join 是等价的，但是在标准SQL中，它们并不等价，Inner Join 用于带有on表达式的连接，反之用Cross Join。以下两个SQL语句是等价的。
Cross Join 指的是两个table的笛卡尔积。

mysql> select * from table1 inner join table2;//注意没有'on'关键字
mysql> select * from table1 cross join table2;
mysql> select * from (table1, table2);
mysql> select * from table1 nature join table2;
结果集：
+------+------+------+--------+
| id   | book | id   | author |
+------+------+------+--------+
|    1 | java |    2 | zhang  |
|    2 | c++  |    2 | zhang  |
|    3 | php  |    2 | zhang  |
|    1 | java |    3 | wang   |
|    2 | c++  |    3 | wang   |
|    3 | php  |    3 | wang   |
|    1 | java |    4 | li     |
|    2 | c++  |    4 | li     |
|    3 | php  |    4 | li     |
+------+------+------+--------+

简单的多表查询

Mysql多表查询：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

CREATE TABLE IF NOT EXISTS contact(   contact_id int(11) NOT NULL AUTO_INCREMENT,   user_name varchar(255),   nom varchar(255),   prenom varchar(255),   mail varchar(64),   passcode char(64),   PRIMARY KEY(contact_id)   );   CREATE TABLE IF NOT EXISTS droit(   droit_id int( 11 ) NOT NULL AUTO_INCREMENT ,   droit varchar(255),   PRIMARY KEY(droit_id)   );   CREATE TABLE IF NOT EXISTS contactdroit(   contactdroit_id int(11) NOT NULL AUTO_INCREMENT,   contact_id int( 11 ),   droit_id int( 11 ),   PRIMARY KEY( contactdroit_id )   );   Insert into contact(contact_id, user_name) values(1,'user1');   Insert into contact(contact_id, user_name) values(2,'user2');   Insert into contact(contact_id, user_name) values(3,'user3');   Insert into droit(droit_id, droit) values(1,'admin');   Insert into droit(droit_id, droit) values(2,'superuser');   Insert into contactdroit(contact_id, droit_id) values(1, 1);   Insert into contactdroit(contact_id, droit_id) values(2, 1);   Insert into contactdroit(contact_id, droit_id) values(3, 2);       SELECT c.contact_id, d.droit_id, d.droit FROM contact c, contactdroit cd, droit d    where c.contact_id = cd.contact_id   and cd.droit_id = d.droit_id;

结果：

?

1 2 3 4

contact_id     droit_id     droit   1                      1           admin   2                      1           admin   3                  2          superuser

题目1

　　问题描述:

　　为管理岗位业务培训信息，建立3个表:

　　S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄

　　C (C#,CN ) C#,CN 分别代表课程编号、课程名称

　　SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩

--1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名

SELECT SN,SD FROM S   WHERE [S#] IN(SELECT [S#] FROM C,SC WHERE C.[C#]=SC.[C#]AND CN='税收基础')

--2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位

SELECT S.SN,S.SD FROM S,SC WHERE S.[S#]=SC.[S#] AND SC.[C#]='C2'

--3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位

SELECT SN,SD FROM S WHERE [S#] NOT IN(SELECT [S#] FROM SC   WHERE [C#]='C5')

--4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

SELECT SN,SD FROM S WHERE [S#] IN(SELECT [S#] FROM SC RIGHT JOIN C ON SC.[C#]=C.[C#] GROUP BY [S#] HAVING COUNT(*)=COUNT([S#]))

--5. 查询选修了课程的学员人数

SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC

--6. 查询选修课程超过5门的学员姓名和所属单位

SELECT SN,SD FROM S WHERE [S#] IN(SELECT [S#] FROM SC GROUP BY [S#] HAVING COUNT(DISTINCT [C#])>5)

--7.学生所有的成绩表单

SELECT S.S# as 学号,S.SN as 姓名,S.SD AS 职业 , SS.[语文],SS.[英语] ,SS.[数学],SS.[政治],SS.[物理],SS.[化学] FROM S ,
(SELECT SC1.S#,[语文]=SC1.G ,[英语]=SC2.G ,[数学]=SC3.G,[政治]=SC4.G,[物理]=SC5.G,[化学]=SC6.G 
from SC SC1,SC SC2,SC SC3,SC SC4,SC SC5,SC SC6,C C1,C C2 ,C C3,C C4,C C5,C C6
WHERE C1.C#=SC1.C#   AND C2.C#=SC2.C#   AND C3.C#=SC3.C# AND C4.C#=SC4.C# AND C5.C#=SC5.C# AND C6.C#=SC6.C#
AND SC1.S#=SC2.S# and SC2.S#=SC3.S#   and SC3.S#=SC4.S# and SC4.S#=SC5.S# and SC5.S#=SC6.S#    AND C1.CN='语文' AND C2.CN='英语'   AND C3.CN='数学'   AND C4.CN='政治'   AND C5.CN='物理'   AND C6.CN='化学'   )SS WHERE S.S#=SS.S#   -- and S.S#='1'

--8.列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
SELECT S.S#,S.SN,AVG_SCGRADE=AVG(SC.G) FROM S,SC,
(SELECT S# FROM SC WHERE G<60 GROUP BY S# HAVING COUNT(DISTINCT C#)>=2 )A 
WHERE S.S#=A.S# AND SC.S#=A.S# GROUP BY S.S#,S.SN

 

高级篇

1.复制表结构和数据，但是不复制索引和外键：
create table a select * from b;
2.复制表结构和索引和外键，不复制数据：
create table a like b;
3.只复制数据：
insert into a select * from b;
4.移动表到另一个库
rename table a.t to b.t;
5.删除重复记录
--新建一个临时表
create table tmp as select * from youtable group by name ;
 
--删除原来的表
 
drop table youtable ;
 
--重命名表
 
alter table tmp rename youtable;
--新建一个临时表
create table tmp like youtable;
 
--取出不重复的数据
 
insert into select * from youtable group by name;
 
--清空原来的表
 
truncate youtable;
 
--插入原来的表
 
insert into youtable select * from tmp;
 
--重命名表
 
drop table tmp;
6.重命名数据库
到/var/lib/mysql/
修改对应数据库的文件夹名即可
7.时间格式
SELECT FROM_UNIXTIME( 1249488000,'%Y-%m-%d %H:%i:%s') ;
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%Y-%m-%d %H:%i:%s');
 
SELECT UNIX_TIMESTAMP('2009-08-06') ;
8.mysql日志
--查看日志
show binary logs;
 
show master logs;
 
--清除日志
 
PURGE MASTER LOGS TO 'mysql-bin.000035';
 
--手动删除10天前的mysql binlog日志
 
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
9.获得更新记录数
select ROW_COUNT();
10.获得找到的记录数
select FOUND_ROWS();
11.获得插入的id
select LAST_INSERT_ID();
12.创建特殊表名
SET sql_mode='ANSI_QUOTES';
create table "a-b" (a int);
13.插入不重复数据
insert into node (name) select 'a' where no exists(select id from node where id=2 and name='a')
14.uuid
select replace(uuid(), '-', '');
15.添加一个远程用户，名为username密码为password
GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY 'password' WITH GRANT OPTION;
16.从文件导入数据
LOAD DATA INFILE '/tmp/result100.txt' INTO TABLE analy_ip_file2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
17.添加主键
alter table userconfig add id int(4) auto_increment primary key;
18.查看mysql参数
show variables like '%max%';

练习

MySQL约束,子查询,多表的练习