SELECT TOP, LIMIT, ROWNUM 子句(取前 n 条记录)
- SQL Server / MS Access语法:
SELECT TOP number|percent column_name(s)
FROM table_name;
- MySQL语法:
SELECT column_name(s)
FROM table_name
LIMIT number;
- Oracle语法:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
取前 n 条记录(MySQL为例)
// 取前5条记录
select * from user limit 5;
// 取后5条信息
select * from user order by id desc limit 5 ;
like (配合通配符进行模糊搜索)
// userName 中以 ‘小’ 开头的
select * from user where userName like '小%';
// age 中以 "2" 结尾的 (数字也可用)
select * from user where age like '%2';
// age 中包含 "2" 的 (数字也可用)
select * from user where age like '%2%';
通配符(配合like使用)
通配符 | 描述 |
% | 匹配0个或多个字符 |
- | 匹配1个字符 |
[abc] | 匹配中括号内部多个字符中任意一个 |
[] | 匹配0个或多个字符 |
[^abc] 或者 [!abc] | 匹配不在方括号内部的字符 |
注意 [abc] 为正则表达式形式 like应替换成 regexp
// 小* 的记录(小李广 不能匹配到,小后面只能跟一个字符)
select * from user where userName like '小_';
// 以 小 开头的所有字符
select * from user where userName like '小%';
// 以 王 结尾的所有字符
select * from user where userName like '%王';
// 包含 t 或 王 开头的字符
select * from user where userName regexp '[t王]';
// 以 t 或 王 开头的字符
select * from user where userName regexp '^[t王]';
// 以 t 或 王 结尾的字符
select * from user where userName regexp '[t王]$';
in 语法
IN 操作符允许您在 WHERE 子句中规定多个值。
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…);
// userName 为'test', 'admin', '小王' 的记录
select * from user where userName in ('test', 'admin', '小王');
between语法
BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
// 介于 A 和 H 之间的记录
select * from user where userName between 'A' and 'H';
// 不是介于 A 和 H 之间的记录
select * from user where userName not between 'A' and 'H';
AS (设置别名)
在下面的情况下,使用别名很有用:
- 在查询中涉及超过一个表
- 在查询中使用了函数
- 列名称很长或者可读性差
- 需要把两个列或者多个列结合在一起
// 查询 user 和 log表中 user.id = log.userId 的相关列
mysql> select user.id, user.userName, user.gender, user.country, log.update from user, log where user.id = log.userId;
// 使用列别名
select userName as 名字, gender as 性别 from user;
+-----------+--------+
| 名字 | 性别 |
+-----------+--------+
| test | 男 |
| admin | 女 |
| 小明 | 男 |
| 小红 | 女 |
| 张三 | 男 |
| 李四 | 男 |
| 王五 | 男 |
| 小王 | NULL |
| 小safari | 女 |
+-----------+--------+
// 使用表别名
mysql> select u.id, u.userName, u.gender,l.update
-> from user as u, log as l
-> where u.id = l.userId;
// 多列拼接为一列作为 userInfo 展示
select id, concat(userName, ',', gender, ',', country) as userInfo from user;
+----+----------------------+
| id | userInfo |
+----+----------------------+
| 1 | test,男,中国 |
| 2 | admin,女,美国 |
| 3 | 小明,男,中国 |
| 4 | 小红,女,英国 |
| 7 | 张三,男,日本 |
| 10 | 李四,男,日本 |
| 12 | 王五,男,韩国 |
| 22 | NULL |
| 23 | 小safari,女,英国 |
+----+----------------------+
// 多表查询放入一列中
select id, concat(user.userName, ',', user.gender, ',', user.country, log.update) as userInfo from user, log where user.id = log.userId;
+----+----------------------------+
| id | userInfo |
+----+----------------------------+
| 1 | test,男,中国2021-05-19 |
| 2 | admin,女,美国2020-06-03 |
+----+----------------------------+
join(连接表)
总结:
- A inner join B 取交集。
- A left join B 取 A 全部,B 没有对应的值为 null。
- A right join B 取 B 全部 A 没有对应的值为 null。
- A full outer join B 取并集,彼此没有对应的值为 null。
对应条件在 on 后面填写。
insert join 与 jion 一样(insert可省略)
在使用 join 时,on 和 where 条件的区别如下:
- on 条件是在生成临时表时使用的条件,它不管 on 中的条件是否为真,都会返回左边表中的记录。
- where 条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有 left join 的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
// 连接两个表中 websites.id = access_log.site_id 的记录 展示指定列
select websites.id, websites.name,access_log.count,access_log.date from websites left join access_log on websites.id = access_log.site_id;
+----+--------------+-------+------------+
| id | name | count | date |
+----+--------------+-------+------------+
| 1 | Google | 230 | 2016-05-14 |
| 1 | Google | 45 | 2016-05-10 |
| 2 | 淘宝 | 10 | 2016-05-14 |
| 3 | 菜鸟教程 | 201 | 2016-05-17 |
| 3 | 菜鸟教程 | 220 | 2016-05-15 |
| 3 | 菜鸟教程 | 100 | 2016-05-13 |
| 4 | 微博 | 13 | 2016-05-15 |
| 5 | Facebook | 545 | 2016-05-16 |
| 5 | Facebook | 205 | 2016-05-14 |
+----+--------------+-------+------------+
join left (有的数据库中是 join ourter left)
返回table1的所有行,即使table2中无匹配。table2中无匹配则输出为null
// 左连接,输出所有左边,右边有则匹配,无则输出为null
select * from websites left join access_log on websites.id = access_log.site_id;
+----+--------------+---------------------------+-------+---------+------+---------+-------+------------+
| id | name | url | alexa | country | aid | site_id | count | date |
+----+--------------+---------------------------+-------+---------+------+---------+-------+------------+
| 1 | Google | https://www.google.cm/ | 1 | USA | 3 | 1 | 230 | 2016-05-14 |
| 1 | Google | https://www.google.cm/ | 1 | USA | 1 | 1 | 45 | 2016-05-10 |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | 4 | 2 | 10 | 2016-05-14 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 9 | 3 | 201 | 2016-05-17 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 7 | 3 | 220 | 2016-05-15 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 2 | 3 | 100 | 2016-05-13 |
| 4 | 微博 | http://weibo.com/ | 20 | CN | 6 | 4 | 13 | 2016-05-15 |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA | 8 | 5 | 545 | 2016-05-16 |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA | 5 | 5 | 205 | 2016-05-14 |
| 6 | 腾讯 | https://www.qq.com | 200 | CN | NULL | NULL | NULL | NULL |
+----+--------------+---------------------------+-------+---------+------+---------+-------+------------+
right join (有些数据库为 right outer join)
与left相似,右边全部输出,左侧匹配则输出,否则输出为null
select * from websites right join access_log on websites.id = access_log.site_id order by access_log.count desc;
+------+--------------+---------------------------+-------+---------+-----+---------+-------+------------+
| id | name | url | alexa | country | aid | site_id | count | date |
+------+--------------+---------------------------+-------+---------+-----+---------+-------+------------+
| 5 | Facebook | https://www.facebook.com/ | 3 | USA | 8 | 5 | 545 | 2016-05-16 |
| 1 | Google | https://www.google.cm/ | 1 | USA | 3 | 1 | 230 | 2016-05-14 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 7 | 3 | 220 | 2016-05-15 |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA | 5 | 5 | 205 | 2016-05-14 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 9 | 3 | 201 | 2016-05-17 |
| NULL | NULL | NULL | NULL | NULL | 10 | 8 | 200 | 2021-05-19 |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN | 2 | 3 | 100 | 2016-05-13 |
| 1 | Google | https://www.google.cm/ | 1 | USA | 1 | 1 | 45 | 2016-05-10 |
| 4 | 微博 | http://weibo.com/ | 20 | CN | 6 | 4 | 13 | 2016-05-15 |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | 4 | 2 | 10 | 2016-05-14 |
+------+--------------+---------------------------+-------+---------+-----+---------+-------+------------+
full outer join
语法:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
union (合并)
union 语法(默认不重复)
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
union all 语法(展示所有包括重复)
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
select into from 和 insert into select
两者的主要区别为: select into from 要求目标表不存在,因为在插入时会自动创建;insert into select from 要求目标表存在。
- 复制表结构及其数据:
create table table_name_new as select * from table_name_old - 只复制表结构:
create table table_name_new as select * from table_name_old where 1=2;
或者:
create table table_name_new like table_name_old - 只复制表数据:
如果两个表结构一样:
insert into table_name_new select * from table_name_old
如果两个表结构不一样:
insert into table_name_new(column1,column2…) select column1,column2… from table_name_old
SQL 约束
not null
不接受空值
// 创建完成后添加不为空约束
ALTER TABLE Persons
MODIFY Age int NOT NULL;
// 删除不为空约束
ALTER TABLE Persons
MODIFY Age int NULL;
unique
不重复,唯一标记一条记录
一张表中unique可以设置多个,但是primary key只能设置一个
// 创建时候设置
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
// 创建完成后修改
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
// 撤销unique约束
ALTER TABLE Persons
DROP INDEX uc_PersonID
primary key
// 表创建时候添加
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
// 表创建之后添加
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
// 撤销约束
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
default
默认值,若插入信息时没有指定值则插入默认值