目录[-]
最基本的sql语句(针对mysql):
创建数据库
create database test;
use test;
删除数据库
drop database test;
创建表(字段中不能有#号)
create table admin(admin_id int,admin_password int);
删除表
drop table admin;
显示表结构
desc admin;
增
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%';