SQL

数据库
字数统计: 1.3k字   |   阅读时长≈ 5分钟

SQL的总结，结合常见问题

数据库的创建与使用

1
create database test;
2
use test;

创建表

1
create table mytable(
2
	id int not null auto_increment,
3
    col1 int not null default 1,
4
    col2 varchar(45) null,
5
    col3 date null,
6
    primary key(id)
7
);

修改表

1
alter table mytable
2
add col char(20);
3
alter table mytable
4
drop column col;
5
drop table mytable;

插入

1
insert into mytable(cols, col2)
2
values(val1, val2);
1
insert into mytable1(col1, col2)
2
select col1, col2
3
from mytable2;
1
create table newtable as
2
select * from mytable;

更新

1
update mytable
2
set col = val
3
where id = 1;

删除

1
delete from mytable
2
where id = 1;
1
truncate table mytable;

查询

distinct
1
select distinct col1, col2
2
from mytable;
limit
1
select *
2
from mytable
3
limit 5;
4
select *
5
from mytable
6
limit 0, 5;
1
select *
2
from mytable
3
limit 2, 3;
4
# 3-5行

排序

  • ASC升序
  • DESC降序
1
select *
2
from mytable
3
order by col1 desc, col2 asc;

过滤

1
select *
2
from mytable
3
where col is null;
操作符 说明
= 等于
< 小于
> 大于
<> != 不等于
<= !> 小于等于
>= !< 大于等于
BETWEEN 在两个值之间
IS NULL 为 NULL 值

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。

NOT 操作符用于否定一个条件

通配符

  • % 匹配 >=0 个任意字符；
  • _ 匹配 ==1 个任意字符；
  • [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
1
select *
2
from mytable
3
where col like '[^AB]%';

计算字段

1
select col1 * col2 as alias
2
from mytable;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。

1
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
2
FROM mytable;Copy to clipboardErrorCopied

函数

汇总
函 数 说 明
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和 
1
SELECT AVG(DISTINCT col1) AS avg_col
2
FROM mytable;
文本处理
函数 说明
LEFT() 左边的字符
RIGHT() 右边的字符
LOWER() 转换为小写字符
UPPER() 转换为大写字符
LTRIM() 去除左边的空格
RTRIM() 去除右边的空格
LENGTH() 长度
SOUNDEX() 转换为语音值 
1
SELECT *
2
FROM mytable
3
WHERE SOUNDEX(col1) = SOUNDEX('apple')Copy to clipboardErrorCopied
日期和时间处理
  • 日期格式：YYYY-MM-DD
  • 时间格式：HH:MM:SS
函 数 说 明
ADDDATE() 增加一个日期（天、周等）
ADDTIME() 增加一个时间（时、分等）
CURDATE() 返回当前日期
CURTIME() 返回当前时间
DATE() 返回日期时间的日期部分
DATEDIFF() 计算两个日期之差
DATE_ADD() 高度灵活的日期运算函数
DATE_FORMAT() 返回一个格式化的日期或时间串
DAY() 返回一个日期的天数部分
DAYOFWEEK() 对于一个日期，返回对应的星期几
HOUR() 返回一个时间的小时部分
MINUTE() 返回一个时间的分钟部分
MONTH() 返回一个日期的月份部分
NOW() 返回当前日期和时间
SECOND() 返回一个时间的秒部分
TIME() 返回一个日期时间的时间部分
YEAR() 返回一个日期的年份部分 
1
mysql> SELECT NOW();
2
# 2018-4-14 20:25:11
数值处理
函数 说明
SIN() 正弦
COS() 余弦
TAN() 正切
ABS() 绝对值
SQRT() 平方根
MOD() 余数
EXP() 指数
PI() 圆周率
RAND() 随机数

分组

1
select col, count(*) as num
2
from mytable
3
group by col
4
order by num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

1
SELECT col, COUNT(*) AS num
2
FROM mytable
3
WHERE col > 2
4
GROUP BY col
5
HAVING num >= 2;

分组规定：

  • GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
  • 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
  • NULL 的行会单独分为一组；
  • 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

子查询

1
select *
2
from mytable1
3
where col1 in (select col2
4
              from mytable2);

连接

内连接
1
select a.value, b.value
2
from tablea as a inner join tableb as b
3
on a.key = b.key;
1
select a.value, b.value
2
from tablea as a, tableb as b
3
where a.key = b.key;
自连接
1
select e1.name
2
from employee as e1 inner join employee as e2
3
on e1.val = e2.val
4
and e2.name = "jim";
自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

1
select a.val, b.val
2
from tablea as a natural join tableb as b;
外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

检索所有顾客的订单信息，包括还没有订单信息的顾客。

1
SELECT Customers.cust_id, Orders.order_num
2
FROM Customers LEFT OUTER JOIN Orders
3
ON Customers.cust_id = Orders.cust_id;

customers 表：

cust_id cust_name
1 a
2 b
3 c

orders 表：

order_id cust_id
1 1
2 1
3 3
4 3

结果：

cust_id cust_name order_id
1 a 1
1 a 2
3 c 3
3 c 4
2 b Null

组合查询

1
select col
2
from mytable
3
where col = 1
4
union
5
select col
6
from mytable
7
where col = 2;
  • © 2020 QSH
  • Powered by Hexo Theme Ayer
  • PV: UV:

请我喝杯咖啡吧~

支付宝
微信