目录
-
- 一、准备
- 二、SQL常用数据类型
- 三、SQL字段约束
- 四、DQL(Data Query Language)数据查询语言
- 五、DML(Data Manipulation Language)数据操作语言
- 六、DDL(Data Define Language)数据定义语言
- 七、TCL(Transaction Control Language)事务控制语言
- 八、DCL(Data Control Language)数据控制语言
- 九、视图
一、准备
下文整理常见SQL语句的用法,使用MySQL5.7测试,参考了尚硅谷MySQL教程及用例。用例数据:
链接: https://pan.baidu.com/s/10_galApgP55qCxltU0rTlA
密码: knno
二、SQL常用数据类型
1、数值型
1)整型
类型 | 所占字节数 | 范围(有符号/无符号) |
---|---|---|
TINYINT | 1 | -128-127 0-255 |
SMALLINT | 2 | -32768-32767 0-65535 |
MEDIUMINT | 3 | -8388608-8388607 0-16777215 |
INT(INTEGER) | 4 | -2147683648-2147683647 0-4294967295 |
BIGINT | 8 | -263–263-1 0–264-1 |
- 默认有符号,需要无符号的话,用UNSIGNED INT
- 插入超过范围的数,最终为临界值
- 整型的长度代表显示的宽度,如果要使用,需要搭配zerofill使用,对于int(M),如长度小于M,用0左填充至宽度为M,如果大于M则无影响。没实际意义。
2)小数
类型 | 所占字节数 | |
---|---|---|
DECIMAL(M,D) | M+2 | 定点数 |
FLOAT(M,D) | 4 | 浮点数 |
DOUBLE(M,D) | 8 | 浮点数 |
- D保留小数位数
- M整数+小数位数和
- MD可以省略,DECIMAL默认为(10,0),FLOAT和DOUBLE会根据实际插入的值来确定
- 定点型精确度高一点,一些高精度要求的可以用定点型,如货币汇率等
2、字符型
类型 | 备注 |
---|---|
CHAR(M) | 存放长度固定或有限的字符 |
VARCHAR(M) | 存放长度不固定的字符 |
TEXT | 存放长文本 |
BINARY(M) | 存放长度固定或有限的二进制 |
VARBINARY(M) | 存放长度不固定的二进制 |
BLOB | 存放较大的二进制,如图片等 |
ENUM() | 枚举 |
SET() | 集合 |
- M为最多的字符数,“abc”是3个字符,“你好”是两个字符
- CHAR是固定长度的字符,可省略M,默认为1,费空间,效率高
- VARCHAR是可变长度字符,不可以省略M,M为最大长度,省空间,效率低
- BINARY和VARBINARY与CHAR和VARCHAR类似用法
- ENUM(“a”,“b”,“c”) ,多选一,只能保存"a",“b”,"c"其中之一,不区分大小写,在mysql5.7中如果插入非列表中的内容,则为null
- SET(“a”,“b”,“c”),多选多,能保存一个或多个abc中的值,如保存"a,b",不区分大小写,在mysql5.7如果插入非列表中的内容,则报错
3、日期型
字节 | 保存 | |
---|---|---|
DATE | 4 | 年-月-日 |
DATETIME | 8 | 年-月-日 时-分-秒 |
TIMESTAMP | 4 | 时间戳,时区影响小,占空小,用的多 |
TIME | 3 | 时-分-秒 |
YEAR | 1 | 年 |
三、SQL字段约束
1、六大约束
约束类型 | 含义 | 备注 |
---|---|---|
NOT NULL | 非空 | |
PRIMARY KEY | 主键 | 保证值的唯一性,且非空 |
UNIQUE | 唯一 | 保证值的唯一性,可以为空 |
DEFAULT | 默认 | 指定字段的默认值 |
CHECK | 检查 | 保证必须是满足条件的值,mysql不支持 |
FOREIGN KEY | 外键 | 用于限制两个表的关系 |
- 外键说明
- 保证从表的值必须来自于主表的某一列的值,需在从表中添加外键。
- 主表从表对应的字段类型要一致或兼容
- 主表的字段必须是一个key(一般是主键或唯一)
- 插入数据时,必须先插入主表再插入从表
- 主键与唯一的区别
- 主键具有唯一性,不允许为null,一张表最多一个,可以组合使用(即多个字段为组合为一个主键)但不推荐。
- 唯一具有唯一性,允许为null且在mysql5.7中默认可以有多个null,一张表可以有多个,可以组合使用但不推荐
2、标识列(自增长列)
- 标识列用AUTO_INCREMENT设置
- 标识列必须是主键或唯一
- 一个表中最多一个标识列
- 标识列类型只能是数值型
有关约束和标识列的使用,在DDL表的管理部分有整理
四、DQL(Data Query Language)数据查询语言
1、语句顺序
- 书写顺序:SELECT、DISTINCT、FROM、JOIN ON、WHERE、GROUP BY、HAVING、ORDER BY、LIMIT
- 执行顺序:FROM、 JOIN ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT
2、基础查询(SELECT)
SELECT 查询内容 FROM 表名
(1)查询常量
SELECT 200;
SELECT 'hello';
(2)查询表达式
对于"+"运算符,仅用于数字类型的相加。若运算数为字符,尝试转化为数字,若转换失败,则认为是0;若运算数为null,结果为null。如下:
SELECT 100%3;
SELECT '123'+9; #结果为 132,'123'-->123
SELECT 'haha'+9; # 结果为 9,’haha’-->0
SELECT null+9; # 结果为 null
(3)查询字段
a.表中字段查询
SELECT first_name FROM employees; #查询单个字段
SELECT first_name,last_name FROM employees; #查询多个字段
SELECT * FROM employees; #查询所有字段
b.使用别名(AS)
SELECT first_name AS '名',last_name '姓' FROM employees; #查询结果使用别名
c.去重(DISTINCT)
SELECT DISTINCT department_id FROM employees; #查询结果去重
(3)查询函数
SQL提供了很多现成函数,常用的大致可分为单行函数和分组函数。
- 单行函数为处理一条数据,输出一个结果,如对字符串的处理等。
- 分组函数又称聚合函数、统计函数或组函数,是对多条记录的统计结果,如求和等。
a.单行函数
根据处理的数据类型不同,单行函数又可细分为字符函数、数学函数、日期函数、流程控制函数等
·字符函数
- LENGTH(str) 返回字符串长度
SELECT LENGTH('hello'); #结果为5
SELECT LENGTH(last_name); #结果为last_name字段的长度
- CONCAT(str1,str2) 拼接字符串
SELECT CONCAT(last_name,'-',first_name); #结果为 last_name字段 - first_name字段
- UPPER(str) | LOWER(str) 转换为大/小写
SELECT UPPER('hello'); #结果为'HELLO'
SELECT LOWER('HeLLo');#结果为'hello'
- SUBSTR(str,pos,len) 截取字符串,字符串索引从1开始
SELECT SUBSTR('my name is xiaoming',4,4); #结果为'name'
SELECT SUBSTR('my name is xiaoming',4); #结果为'name is xiaoming'
- INSTR(str,substr) 返回子串第一次出现的索引,字符串索引从1开始
SELECT INSTR('my name is xiaoxiao','xiao'); #结果为12
- TRIM(str,substr) 首尾去除规定字符,默认去空格
SELECT TRIM(' my name is xiaoming ');
#结果为'my name is xiaoming'
SELECT TRIM('7' FROM '77my name 777 is xiaoming 777');
#结果为'my name 777 is xiaoming '
- LPAD(str,len,padstr) | RPAD(str,len,padstr) 用规定字符左(右)填充至指定长度
SELECT LPAD('my',10,'*');#结果为'********my'
SELECT RPAD('my',10,'ab');#结果为'myabababab'
- REPLACE(str,old,new) 字符串替换
SELECT REPLACE('my name is xiaoxiao','xiao','da');#结果为'my name is dada'
·数学函数
- ROUND(X,D) 四舍五入,保留指定位数,默认为保留整数
SELECT ROUND(-1.65); #-2
SELECT ROUND(-1.65,1); #-1.7
- CEIL(X) | FLOOR(X) 向上|向下取整
SELECT CEIL(1.44);#2
SELECT FLOOR(1.55);#1
- TRUNCATE(X) 截取保留指定小数位
SELECT TRUNCATE(2.666,1); #2.6
- MOD(X1,X2) 取模
SELECT MOD(10,3);#1,符号与被除数一致
SELECT MOD(-10,3);#-1
SELECT MOD(10,-3);#1
·日期函数
- NOW()、CURDATE() 当前时间、当前日期