MySQL高级进阶 SQL 语句
use yjs;
create table fw (id int,Name char(20),saleroom int(20));
insert into fw values(1,'ylx',7400);
insert into fw values(2,'dl',11000);
insert into fw values(3,'lp',18400);
insert into fw values(4,'jl',11400);
create table yj (Name char(20),region char(20));
insert into yj values('ylx','nanjing');
insert into yj values('dl','nanjing');
insert into yj values('lp','beijing');
insert into yj values('jl','shanghai');
一、SQL 语句
1.SELECT 显示表格中一个或数个栏位的所有资料
语法: SELECI "栏位" FROM "表名";
SELECT name FROM fw;
2.DISTINCT 不显示重复的资料
语法: SELECT DISTINCT "栏位" FROM "表名";
SELECT DISTINCT region FROM yj;
3.WHERE 有条件查询
语法: SELECT "栏位" FROM "表名" WHERE "条件";
SELECT * FROM fw WHERE saleroom < 10000;
4.AND | OR 且 或
语法: SELECT "栏位" FROM "表名" WHERE "条件1" {[AND | OR] "条件2"}+ ;
SELECT * FROM fw WHERE saleroom > 15000 OR (saleroom < 12000 AND saleroom > 10000);
5.IN 显示已知的值的资料
语法: SELECT "栏位" FROM "表名" WHERE "栏位" IN ('值1','值2',...);
SELECT * FROM fw WHERE name IN ('ylx','lp');
6.BETWEEN 显示两个值范围内的资料
语法: SELECT "栏位" FROM "表名" WHERE "栏位" BETWEEN '值1' AND '值2';
SELECT * FROM fw WHERE id BETWEEN '2' AND '4';
二、通配符
1.通常通配符都是跟 LIKE 一起使用的
符号 | 说明 |
---|---|
% | 百分号表示零个、一个或多个字符 |
_ | 下划线表示单个字符 |
例 | 说明 |
---|---|
A_Z | 所有以’A’起头另一个任何值的字符,且以’Z’为结尾的字符串。 |
例 | 'ABZ’和’A2Z’都符合这一个模式,而’AKKZ’并不符合(因为在A和Z之间有两个字符,而不是一个字符)。 |
ABC% | 所有以’ABC’起头的字符串。 |
例 | ‘ABCD’ 和’ABCABC’ 都符合这个模式。 |
%XYZ | 所有以’XYZ’结尾的字符串。 |
例 | ‘WXYZ’和’ZZXYZ’ 都符合这个模式。 |
%AN% | 所有含有’AN’这个模式的字符串。 |
例 | ‘LOS ANGELES’ 和’SAN FRANCISCO’ 都符合这个模式。 |
AN% | 所有第二个字母为’A’ 和第三个字母为’N’的字符串。 |
例 | ‘SAN FRANCISCO’ 符合这个模式,而’LOS ANGELES’则不符合这个模式。 |
2.LIKE 匹配一个模式来找出我们要的资料
语法:SELECT "栏位" FROM "表名" WHERE "栏位" LIKE {模式};
SELECT * FROM fw WHERE saleroom like '%40%';
3.ORDER BY 按关键字排序
语法: SELECT "栏位" FROM "表名" [WHERE "条件"] ORDER BY "栏位" [ASC, DESC];
SELECT * FROM fw ORDER BY saleroom DESC;
#ASC是按照升序进行排序的,是默认的排序方式。
#DESC是按降序方式进行排序。
三、函数
1.数学函数
函数 | 说明 |
---|---|
abs(x) | 返回x的绝对值 |
rand() | 返回0到1的随机数 |
mod(x, y) | 返回x除以y以后的余数 |
power (x, y) | 返回x的y次方 |
round (x) | 返回离x最近的整数 |
round(x, y) | 保留x的y位小数四舍五入后的值 |
sqrt (x) | 返回x的平方根 |
truncate(x,y) | 返回数字x截断为y位小数的值 |
ceil (x) | 返回大于或等于x的最小整数 |
floor (x) | 返回小于或等于x的最大整数 |
greatest (x1,x2…) | 返回集合中最大的值 |
least (x1,x2…) | 返回集合中最小的值 |
SELECT abs(-1),rand(),mod(5,3),power(2,3),round(1.89);
SELECT round(1.8937,3),truncate(1.235,2),ceil(5.2),floor(2.1),least(1.89,3,6.1,2.1);
2.聚合函数
函数 | 说明 |
---|---|
avg () | 返回指定列的平均值 |
count () | 返回指定列中非NULL值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum(x) | 返回指定列的所有值之和 |
SELECT avg(saleroom) FROM fw;
SELECT count(yue) FROM fw;
SELECT count(*) FROM fw;
#count (*)包括了所有的列的行数,在统计结果的时候,不会忽略列值为NULL
#count (列名)只包括列名那一列的行数,在统计结果的时候,会忽略列值为NULL的行
SELECT max(saleroom) FROM fw;
SELECT min(saleroom) FROM fw;
SELECT sum(saleroom) FROM fw;
3.字符串函数
函数 | 说明 |
---|---|
trim() | 返回去除指定格式的值 |
concat(x,y) | 将提供的参数x和y拼接成一个字符串 |
substr(x, y) | 获取从字符串x中的第y个位置开始的字符串,跟substring() 函数作用相同 |
substr(x, y, z) | 获取从字符串x中的第y个位置开始长度为z的字符串 |
length(x) | 返回字符串x的长度 |
replace(x,y, z) | 将字符串z替代字符串x中的字符串y |
upper(x) | 将字符串x的所有字母变成大写字母 |
lower(x) | 将字符串x的所有字母变成小写字母 |
left(x,y) | 返回字符串x的前y个字符 |
right(x,y) | 返回字符串x的后y个字符 |
repeat(x, y) | 将字符串x重复y次 |
space(x) | 返回x个空格 |
strcmp(x, y) | 比较x和y,返回的值可以为-1,0,1 |
reverse(x) | 将字符串x反转 |
SELECT concat(id,name) FROM fw;
如sql_mode开启 了PIPES_AS_CONCAT,"||"视为字符串的连接操作符而非或运算符,和字符串的拼接函数Concat相类似,这和Oracle数据库使用方法一样的
SELECT id || ’ ’ || Name FROM fw WHERE id=2;
SELECT substr(saleroom,3) FROM fw WHERE Name='lp';
SELECT substr(saleroom,2,4) FROM fw;
SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串);
#[位置]:的值可以为LEADING (起头),TRAILING (结尾),BOTH(起头及结尾)。
#[要移除的字符串]:从字串的起头、结尾或起头及结尾移除的字符串。缺省时为空格。
SELECT TRIM(LEADING 'y' FROM 'ylx');
SELECT name,length(saleroom) FROM fw;
SELECT REPLACE(name,'lx','lxern') FROM fw;
1.GROUP BY
对GROUPBY后面的栏位的查询结果进行汇总分组,通常是结合聚合函数一起使用的GROUPBY有一个原则,就是SELECT后面的所有列中,没有使用聚合函数的列,必须出现在GROUPBY后面。
语法: SELECT "栏位1",SUM("栏位2") FROM "表名" GROUP BY "栏位1";
SELECT id,SUM(yue) FROM fw GROUP BY yue ORDER BY yue desc;
1.HAVING
用来过滤由GROUP BY语句返回的记录集,通常与GROUP BY语句联合使用
HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏,那就不需要GR0UPBY子句。
语法: SELECT "栏位1",SUM("栏位2") FR0M "表格名" GROUP BY "栏位1" HAVING(函数条件) ;
SELECT name,SUM(saleroom) FROM fw GROUP BY name HAVING SUM(saleroom) > 15000;
4.别名
栏位別名表格别名
语法: SELECT "表格别名"."栏位1" [AS] "栏位别名" FROM "表格名" [AS] "表格别名";
SELECT A.name id, SUM(A.Saleroom) "Total Saleroom" FROM fw A GROUP BY A.name;
5.子查询
连接表格,在WHERE 子句或HAVING子句中插入另一个SQL语句
语法:SELECT "栏位1" FROM "表格1" WHERE "栏位2" [比较运算符] #外查询
(SELECT "栏位1" FROM "表格2" WHERE "条件"); #内查询
#可以是符号的运算符,例如=、>、<、>=、<=;也可以是文字的运算符,例如LIKE、IN、BETWEEN
SELECT SUM(Saleroom) FROM fw WHERE name IN (SELECT name FROM yj WHERE region='nanjing');
SELECT SUM(A.Saleroom) FROM fw A WHERE A.name IN (SELECT name FROM yj B WHERE B.name=A.name);
6.EXISTS
用来测试内查询有没有产生任何结果,类似布尔值是否为真
#如果有的话,系统就会执行外查询中的SQL语句。若是没有的话,那整个SQL 语句就不会产生任何结果。
语法: SELECT "栏位1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");
SELECT SUM(Saleroom) FROM fw WHERE EXISTS (SELECT * FROM yj WHERE region='nanjing');
总结
SQL 语句 | 命令 |
---|---|
SELECT | 显示表格中一个或数个栏位的所有资料 |
语法 | SELECI “栏位” FROM “表名”; |
例 | SELECT name FROM fw; |
-------------- | ——————————————————————————————— |
DISTINCT | 不显示重复的资料 |
语法 | SELECT DISTINCT “栏位” FROM “表名”; |
例 | SELECT DISTINCT region FROM yj; |
-------------- | ——————————————————————————————— |
WHERE | 有条件查询 |
语法 | SELECT “栏位” FROM “表名” WHERE “条件”; |
例 | SELECT * FROM fw WHERE saleroom < 10000; |
-------------- | ——————————————————————————————— |
AND | OR | 且 或 |
语法 | SELECT “栏位” FROM “表名” WHERE “条件1” {[AND | OR] “条件2”}+ ; |
例 | SELECT * FROM fw WHERE saleroom > 15000 OR (saleroom < 12000 AND saleroom > 10000); |
------------- | ——————————————————————————————— |
IN | 显示已知的值的资料 |
语法: | SELECT “栏位” FROM “表名” WHERE “栏位” IN (‘值1’,‘值2’,…); |
例 | SELECT * FROM fw WHERE name IN (‘ylx’,‘lp’); |
-------------- | ——————————————————————————————— |
BETWEEN | 显示两个值范围内的资料 |
语法 | SELECT “栏位” FROM “表名” WHERE “栏位” BETWEEN ‘值1’ AND ‘值2’; |
例 | SELECT * FROM fw WHERE id BETWEEN ‘2’ AND ‘4’; |
通配符 | 说明 |
---|---|
LIKE | 匹配一个模式来找出我们要的资料 |
语法 | SELECT “栏位” FROM “表名” WHERE “栏位” LIKE {模式}; |
- | SELECT * FROM fw WHERE saleroom like ‘%40%’; |
ORDER BY | 按关键字排序 |
语法 | SELECT “栏位” FROM “表名” [WHERE “条件”] ORDER BY “栏位” [ASC, DESC]; |
- | SELECT * FROM fw ORDER BY saleroom DESC; |
- | ASC是按照升序进行排序的,是默认的排序方式。 |
- | DESC是按降序方式进行排序。 |