赞
踩
目录
准备两张表
- 内连接:将两个表中字段相同的数据列出来
- select * from info A inner join location B on A.name=B.name;
- #使用 using 时,两个表中字段必须相同
- select * from info A inner join location B using(name);
-
- 左连接:将左表所有数据列出,右表name相同值的数据列出来,没有以NULL代替
- select * from info A left join location B on A.name=B.name;
- 右连接:将右表字段值列出来,左表相同的值也显示出来,没有以NULL代替
- select * from info A right join location B on A.name=B.name;
- #一次查询两个表,效率比内连接低
- select * from info A ,location B where A.name=B.name;
- #对不同城市的年龄总和进行降序排序
- select city,sum(A.age) from info A ,location B where A.name=B.name group by city order by sum(A.age) desc;
- 语法:CREATE VIEW "视图表名" AS "SELECT 语句";
-
- #查询结果输出到新建的view视图中去
- create view V_AB as select A.name,city,age from info A,location B where A.name=B.name;
- #查看结果
- select * from V_AB;
-
- #删除view视图
- drop view ‘视图表名’;
- drop view V_AB;
- 修改view视图的数据,直接修改源表数据
-
- update info set age=99 where name='tom';
-
- select * from V_AB order by age desc;
- 当字段保留源表字段
- #修改视图中数据
- update V_AB set age=70 where name='tom';
-
- 当改变视图字段名称,视图包含两个表数据。
- create view V_ABC as select B.name,sex,city,age number from info A,location B where A.name=B.name;
- update V_ABC set number=70 where name='tom';
- view中字段值经过函数处理
- create view V_ABCD as select city,sum(A.age) from info A,location B where A.name=B.name group by city;
- update V_ABCD set sum(A.age)=70 where city='nanjing';
联集,将两个SQL语句的结果合并起来,两个SQL语句所产生的字段需要是同样的数据记录种类
- UNION :生成结果的数据记录值将没有重复,且按照字段的顺序进行排序
- 语法:[SELECT 语句 1] UNION [SELECT 语句 2];
-
- UNION ALL :将生成结果的数据记录值都列出来,无论有无重复
- 语法:[SELECT 语句 1] UNION ALL [SELECT 语句 2];
-
-
- insert into location values ('tom','male');
- select * from location;
- select * from info;
- select name from info union select name from location;
- select name from info union all select name from location;
- 取两个SQL语句结果的交集
- #取两个SQL语句结果的交集,有重复
- select A.name from info A inner join location B on A.name=B.name;
- select A.name from info A inner join location B using(name);
- #取两个SQL语句结果的交集,且没有重复
- select name from info where name in (select name from location);
- select distinct A.name from info A inner join location B on A.name=B.name;
- select A.name from info A inner join location B on A.name=B.name group by A.name;
- 显示第一个SQL语句的结果,且与第二个SQL语句没有交集的结果,且没有重复
- select distinct name from info where name not in (select name from location);
- select distinct A.name from info A left join location B using(name) where B.name is null;
- 显示两个表中没有交集的值
- #创建派生表,其中A表为派生表
- select A.name from (select distinct name from info union all select name from location) A group by A.name having count(*)=1;
- #创建视图
- create view V_union as select name from info union all select name from location;
- select name from V_union group by name having count(*)=1;
- 是 SQL 用来做为 IF-THEN-ELSE 之类逻辑的关键字
- 语法:
- SELECT CASE ("字段名")
- WHEN "条件1" THEN "结果1"
- WHEN "条件2" THEN "结果2"
- ...
- [ELSE "结果N"]
- END
- FROM "表名";
- # "条件" 可以是一个数值或是公式。 ELSE 子句则并不是必须的。
-
- select name,case name
- when 'jack' then age*2
- when 'mary' then age-15
- else age
- end 'new age' from info;
- # 'new age' 是新定义的字段名
- create table t1 (name varchar(20));
- #插入无值
- insert into t1 values ('');
- #插入空值
- insert into t1 values ();
-
- select * from t1;
- #查看字符串长度
- select length(null),length('zhangsan'),length('');
- #过滤字段值为null值的数据
- select * from t1 where name is null;
- #过滤字段值不为null值,但不能过滤空值
- select * from t1 where name is not null;
- #将空值过滤
- select * from t1 where name != '';
- select * from t1 where name <> '';
匹配模式 | 描述 | 实例 |
^ | 匹配文本的开始字符 | ‘^bd’ 匹配以 bd 开头的字符串 |
$ | 匹配文本的结束字符 | ‘qn$’ 匹配以 qn 结尾的字符串 |
. | 匹配任何单个字符 | s.t’ 匹配任何 s 和 t 之间有一个字符的字符串 |
* | 匹配零个或多个在它前面的字符 | ‘fo*t’ 匹配 t 前面有任意个 o |
+ | 匹配前面的字符 1 次或多次 | ‘hom+’ 匹配以 ho 开头,后面至少一个m 的字符串 |
字符串 | 匹配包含指定的字符串 | ‘clo’ 匹配含有 clo 的字符串 |
p1|p2 | 匹配 p1 或 p2 | ‘bg|fg’ 匹配 bg 或者 fg |
[……] | 匹配字符集合中的任意一个字符 | ‘[abc]’ 匹配 a 或者 b 或者 c |
[^……] | 匹配不在括号中的任何字符 | ‘[^ab]’ 匹配不包含 a 或者 b 的字符串 |
{n} | 匹配前面的字符串 n 次 | ‘g{2}’ 匹配含有 2 个 g 的字符串 |
{n,m} | 匹配前面的字符串至少 n 次,至多m 次 | ‘f{1,3}’ 匹配 f 最少 1 次,最多 3 次 |
- 语法:SELECT "字段" FROM "表名" WHERE "字段" REGEXP {模式};
- #查找name字段中包含 'on'
- select * from info where name regexp 'on';
- #查找name字段中以 y 结尾的
- select * from info where name regexp 'y$';
- #查找name字段中以 y 结尾的或以 j 开头的
- select * from info where name regexp 'y$|^j';
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。