声明:本博客截图来自菜鸟教程:http://www.runoob.com/sql/sql-tutorial.htmlhtml
一、SQL LIMIT:用于规定要返回的记录的数目。web
#MySQL语法 select clolume1 from table1 limit number; #eg: SELECT * FROM Websites LIMIT 2;#取前两个;
二、SQL通配符sql
# %:选取 url 以字母 "https" 开始的全部网站: SELECT * FROM Websites WHERE url LIKE 'https%'; # _:选取 name 以一个任意字符开始,而后是 "oogle" 的全部客户: SELECT * FROM Website WHERE name LIKE '_oogle'; # [charlist] :下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的全部网站: SELECT * FROM Websites WHERE name REGEXP '^[GFs]'; # 选取 name 以 A 到 H 字母开头的网站: SELECT * FROM Websites WHERE name REGEXP '^[A-H]'; # 选取 name 不以 A 到 H 字母开头的网站: SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';
三、IN操做符app
#IN 操做符容许您在 WHERE 子句中规定多个值: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);
四、BETWEEN操做符svg
1)BETWEEN 操做符选取介于两个值之间的数据范围内的值。这些值能够是数值、文本或者日期。函数
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
2)NOT BETWEEN 操做符实例网站
SELECT * FROM Website WHERE alexa NOT BETWEEN 1 AND 20;
3)带有 IN 的 BETWEEN 操做符实例url
SELECT * FROM Websites WHERE (alexa BETWEEN 1 AND 20) AND country NOT IN ('USA', 'IND');
4)带有文本值的 BETWEEN 操做符实例3d
SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';
5)带有日期值的 BETWEEN 操做符实例code
SELECT * FROM access_log WHERE date BETWEEN '2016-05-10' AND '2016-05-14';
五、别名
列别名:
表别名:
# 在下面的状况下,使用别名颇有用:
# 在查询中涉及超过一个表
# 在查询中使用了函数
# 列名称很长或者可读性差
# 须要把两个列或者多个列结合在一块儿
六、SQL JOIN
七、SQL UNION :UNION 结果集中的列名老是等于 UNION 中第一个
# SELECT 语句中的列名。 SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; # 取表1和表2中 column_name(s)列不一样的值,至关于合并以后去重 SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2; # 至关于合并 # 带有 WHERE 的 SQL UNION ALL ![带有WHERE的例子,截图自菜鸟](https://img-blog.csdnimg.cn/20190110111546796.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hvbmdkdW55YW5n,size_16,color_FFFFFF,t_70) # INSERT INTO SELECT:从一个表复制数据,而后把数据插入到一个已存在的表中 insert into table2 select * from table1; INSERT INTO table2 (column_name(s)) SELECT column_name(s) FROM table1; INSERT INTO Websites (name, country) SELECT app_name, country FROM apps; # 复制另外一个表的数据 INSERT INTO Websites (name, country) SELECT app_name, country FROM apps WHERE id=1; # 复制另外一个表的第一行数据