sql常用基础语句

笔记内容来源于菜鸟SQL教程

sql常用语句

去重 distinct

select distinct column_name 
from table_name
1
2

where

select column_name
from table_name
Where  条件
1
2
3

= <>(不等于) > >= < <= BETWEEN（在某个范围内，闭区间），LIKE, IN（针对某个列的多个可能值）

AND & OR

顺序 order by(后面也可以加聚合函数，同having）

select *
from user_profile
order by device_id asc|desc
1
2
3

插入输入insert into

INSERT INTO table_name
VALUES (value1,value2,value3,...);

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
1
2
3
4
5

查询前几条数据 limit

select *
from table_name
limit number

1
2
3
4

like

用于在 WHERE 子句中搜索列中的指定模式

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

SELECT * FROM Websites
WHERE name LIKE 'G%';
1
2
3
4
5
6

sql通配符

% 替代0个或多个字符
_ 替代一个字符
[charlist] 字符列中的任何单一字符
[!charlist]或[^charlist]不在字符列中的任何单一字符
REGEXP 或 NOT REGEXP可以操作正则表达式

# 选取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]';
1
2
3
4
5
6
7
8
9
10
11

在wehere中规定多个值IN

select column_names
from table_name
where column_name in (value1, values2, ...)

select * from websites
where name in ('Google', '菜鸟');
1
2
3
4
5
6

between两个值之间的数据范围内的值/ NOT BETWEEN

select column_names
from table_name
where column_name between value1 and value2;
1
2
3

select * from table_name
where column_name not between value1 and value2;
1
2

带IN的between操作符实例，带有文本值的between操作符实例，带有文本值的not between操作符实例，带有日期值的between操作符实例

别名

列别名

select column_name as alias_name
from table_name;
1
2

表别名

select column_name(s)
from table_name as alias_name

select name, concat(url, ', ', contry) AS site_info
from websites;
1
2
3
4
5

sql 连接（join）

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行

inner join

select column_name
from table1
(inner) join table2
on table1.column_name = table2.column_name;
1
2
3
4

left join

select column_names(s)
from table1
left (outer) join table2
on table1.column_name = table2.column_name;
1
2
3
4

在left join时，使用on和where的区别：
on是在生成临时表时使用的条件，不管on中的条件是否为真，都会返回左边表中的记录
where是在临时表生成后，再对临时表进行过滤的条件。这时没有left join的含义（必须返回左边表的记录），条件不为真的就全部过滤掉。

right join

select column_name(s)
from table1
right join table2
on table1.column_name = table2.column_name;
1
2
3
4

full outer join

select column_name
from table1
full outer join table2
on table1.column_name = table2.column_name
1
2
3
4

union

合并两个或多个select语句的结果，union操作符选取不同的值。如果允许重复的值，使用union all。

select column_name from table1
union
select column_name from table2;
1
2
3

sql函数

分为聚合函数（Aggregate）:
AVG(), COUNT(),
FIRST(第一个记录的值),
LAST(最后一个记录的值),
MAX(),MIN(),SUM()
Scalar函数：
UCASE(将某个字段转为大写)；
LCASE(将某个字段转为小写)；
MID(从某个文本字段提取字符)；
SubString(字段，1，end) 从某个文本字段提取字符；
LEN() 返回某个文本字段的长度
ROUND() 对某个数值字段进行指定小数位数的四舍五入
NOW() 返回当前的系统日期和时间
FORMAT() 格式化某个字段的显示方式

AVG()

选择访问量高于平均访问量的‘site_id’和‘count’

SELECT site_id, count FROM access_log
WHERE count > (SELECT AVG(count) FROM access_log);
1
2

COUNT()

count(column_name) 返回指定列的值的数目，NULL不计入
count(*) 返回表中的记录数
count(distinct column_name)返回指定列的不同值的数目

FIRST() /LAST()只有MS Acess支持first/last函数

返回指定列中第一个记录的值
select first(column_name) from table_name;
sql

select column_name from table_name
order by column_name ASC
limit 1
1
2
3

MAX() /MIN()返回指定列的最大值/最小值

select max(column_name) from table_name;
select min(column_name) from table_name;
1
2

SUM()返回数值列的总数

select sum(column_name) from table_name;
1

GROUP BY语句，结合聚合函数，根据一个或多个列对结果集进行分组。也可以进行多表连接。

select column_name, aggregate_function(column_name)
from table_name
where 条件
group by column_name

select gender, university, count(id) as user_num, avg(active_days_within_30) as avg_active_day, avg(question_cnt) as avg_question_cnt
from user_profile
group by gender, university
1
2
3
4
5
6
7
8

多表连接后也可以用GROUP BY

HAVING子句

where没办法和聚合函数一起使用，having可以筛选分组后的各组数据
where在group by之前，having在group by之后

select websites.name, website.url, sum(access_log.count) as nums
from websites
inner join access_log
on websites.id = access_log.site_id
where websites.alexa < 200
group by websites.name
having sum(access_log.count) > 200;
1
2
3
4
5
6
7

EXISTS运算符

用于判断查询子句是否有记录，如果有一条或多条记录存在则返回True，否则返回False。

select column_name(s)
from table_name
where exists
(select column_name(s) from table_name where condition);
1
2
3
4

EXISTS可以和NOT一同使用，查找出不符合查询语句的记录

UCASE()/UPPER()把字段里面的值转为大写

select UCASE(column_name) from table_names;
1

LCASE()/LOWER()把字段的值全都转为小写

MID()从文本字段中提取字符

select mid(column_name, start[s, length]) from table;
# s 起始位置，必须写, length可选
1
2

LEN()/LENGTH()返回文本字段中值的长度

select len(column_name) from table_name
1

ROUND()把数值字段舍入为指定的小数位数

select round(column, decimals) from table_names
# decimals可选，要返回的小数位数。没写默认整数？
1
2

NOW()返回当前系统的日期和时间

select column_name, NOW() as date
from table_name;
1
2

FORMAT()函数，对字段的显示进行格式化

select FORMAT(column_name, format) from table_name
1