MySQL数据库—SQL汇总

栏目: IT技术 · 发布时间: 4年前

点击上方“ 涛哥聊Python ”,选择“星标”公众号

重磅干货,第一时间送达

转自:扣丁课堂

一、 SQL 常用数据类型

1

数值型

a、整型 MySQL数据库—SQL汇总

默认有符号,需要无符号的话,用UNSIGNED INT。

插入超过范围的数,最终为临界值。

整型的长度代表显示的宽度,如果要使用,需要搭配zerofill使用,对于int(M),如长度小于M,用0左填充至宽度为M,如果大于M则无影响。没实际意义。

b、小数 MySQL数据库—SQL汇总

D保留小数位数。

M整数+小数位数和。

MD可以省略,DECIMAL默认为(10,0),FLOAT和DOUBLE会根据实际插入的值来确定。

定点型精确度高一点,一些高精度要求的可以用定点型,如货币汇率等。

2

字符型

MySQL数据库—SQL汇总

M为最多的字符数,“abc”是3个字符,“你好”是两个字符。

CHAR是固定长度的字符,可省略M,默认为1,费空间,效率高。

VARCHAR是可变长度字符,不可以省略M,M为最大长度,省空间,效率低。

BINARY和VARBINARY与CHAR和VARCHAR类似用法。

ENUM(“a”,“b”,“c”) ,多选一,只能保存"a",“b”,"c"其中之一,不区分大小写,在 mysql 5.7中如果插入非列表中的内容,则为null。

SET(“a”,“b”,“c”),多选多,能保存一个或多个abc中的值,如保存"a,b",不区分大小写,在mysql5.7如果插入非列表中的内容,则报错。

3

日期型

MySQL数据库—SQL汇总

二、SQL字段约束

1

六大约束

MySQL数据库—SQL汇总

外键说明

保证从表的值必须来自于主表的某一列的值,需在从表中添加外键。

主表从表对应的字段类型要一致或兼容。

主表的字段必须是一个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)

(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; #查询结果去重

(4)查询函数

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() 当前时间、当前日期

SELECT NOW();#2019-10-19 14:40:54

SELECT CURDATE();#2019-10-19

YEAR() | MONTH() | DAY() | HOUR() | MINUTE() | SECOND()

SELECT YEAR('2019-10-1');#2019

DATEDIFF(date1,date2) | TIMEDIFF(date1,date2) 两个日期相差的天数|两个时刻相差的时间

SELECT DATEDIFF('2019-10-17','2019-10-1');#结果为16,前面的减后面的

SELECT TIMEDIFF('2019-10-17 15:30:2','2019-10-1 7:0:0');#结果为 392:30:02

STR_TO_DATE(str,format) 字符串按格式转为日期

DATE_FORMAT(date,format) 日期按格式转为字符串

MySQL数据库—SQL汇总

SELECT STR_TO_DATE('10-1 2019','%c-%d %Y');#2019-10-01

SELECT DATE_FORMAT(NOW(),'%Y.%m.%d');#2019.10.19

流程控制函数

IF 二选一

SELECT IF(10>5,'yes','no');#结果为'yes'

CASE-WHEN-THEN-END 多选一

用法1,相当于 Java 的which,判断一个*值*:

SELECT last_name,job_id,CASE job_id

WHEN 'AD_PRES' THEN '1'

WHEN 'AD_VP' THEN '2'

WHEN 'IT_PROG' THEN '3'

ELSE '4'

END FROM employees; 

用法2,相当于Java的if-else if-else,判断一个*表达式*:

SELECT last_name,CASE

WHEN salary<5000 THEN

'<5000'

WHEN salary BETWEEN 5000 AND 10000 THEN

'5000-10000'

ELSE

'>10000'

END FROM employees;

b、分组函数

分组函数又称为聚合函数、统计函数、组函数,所有分组函数对null值处理为忽略它,而非当做0。常用的分组函数有以下几个:

SUM() 对数值型数据求和,用+相加,符合+的运算法则

AVG() 对数值型数据求平均

MAX() 对所有可比较类型求最大值

MIN() 对所有可比较类型求最小值

COUNT() 统计非空个数

分组函数的调用为:

SELECT SUM(salary) FROM employees;

SELECT COUNT(*) FROM employees;#统计表的总行数

SELECT COUNT(1) FROM employees;#统计表的总行数

3

条件查询(WHERE)

(1)运算符做条件

sql语法中运算符有:

MySQL数据库—SQL汇总

#查询部门编号不等于90的员工信息

SELECT * FROM employees WHERE department_id<>90;

(2)逻辑表达式做条件

sql语法中逻辑表达式有:

MySQL数据库—SQL汇总

#查询部门编号大于50小于100的员工信息

SELECT * FROM employees WHERE department_id > 50 AND department_id < 100;

(3)模糊查询

sql中用于模糊查询的关键字有:

MySQL数据库—SQL汇总

#查询last_name第二个字符是a的员工

SELECT * FROM employees WHERE last_name LIKE '_a%';

#查询工资在10000到20000的员工信息,包括10000和20000

SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;

#查询职位为D_VP和FI_MGR的员工信息

SELECT * FROM employees WHERE job_id IN ('D_VP','FI_MGR');

#查询没有奖金的员工信息

SELECT * FROM employees WHERE commission_pct IS NULL;

4

排序查询(ORDER BY)  

ASC:升序,默认项

DESC:降序

(1)基本排序

#按月薪降序

SELECT * FROM employees ORDER BY salary DESC;

#按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) salary_total

FROM employees ORDER BY salary_total DESC;

(2)按函数排序

#按名字长度降序排序

SELECT *

FROM employees

ORDER BY LENGTH(last_name) DESC;

(3)按多个字段排序

#先按salary降序排序,同样salary的按employee_id升序排序

SELECT *

FROM employees

ORDER BY salary DESC , employee_id ASC;

5

分组查询(GROUP BY)

SELECT 查询内容 FROM 表名 [WHERE 条件] GROUP BY 分组列表 [HAVING 条件]

查询内容中的字段,必须要出现在分组列表中

WHERE是在分组前对原始表筛选,HAVING是在分组后对结果表筛选

分组查询中,若用分组函数做筛选的条件,一定是在HAVING子句中

(1)按单字段分组

#查询每个部门的员工数量

SELECT department_id,COUNT(1)

FROM employees

GROUP BY department_id ;

(2)按多字段分组

#查询每个部门,每个职务的平均工资

SELECT AVG(salary),department_id,job_id

FROM employees

GROUP BY department_id,job_id;

(3)按函数分组

#按名字长度分组,并筛选出数量大于5的名字长度

SELECT COUNT(1) c,LENGTH(last_name) len_name

FROM employees

GROUP BY len_name

HAVING c>5;

6

连接查询(JOIN)

(1)sql92标准

a、内连接

sql92标准只支持内连接,内连接即用来查询两张表的"交集"部分,即满足条件的、两张表都存在的部分。内连接又分为等值连接、非等值连接和自连接。

等值连接

等值连接即以两张表的两个字段的值相等为连接条件,进行两张表的连接,如:

#查询每个员工的job_title

SELECT e.last_name,e.job_id,job_title

FROM employees e,jobs j

WHERE e.job_id = j.job_id;

非等值连接

非等值连接即以两张表的两个字段的值满足一定条件为连接条件,进行两张表的连接,如:

#查询某个员工的工资等级

SELECT e.last_name,j.grade_level

FROM employees e,job_grades j

WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

自连接即一张表和本身的连接,因涉及到同一张表的连接,一般要对这张表使用不同的别名加以区分,如:

#查询某个员工及其对应的领导

SELECT e1.last_name 'employee',e2.last_name 'manager'

FROM employees e1,employees e2

WHERE e1.manager_id = e2.employee_id;

(2)sql99标准

a、内连接(INNER JOIN)

sql99的内连接含义与sql92语法完全一致,只是语法不同。用sql92的三个例子改为sql99语法形式如下:

等值连接

#查询每个员工的job_title

SELECT e.last_name,e.job_id,job_title

FROM employees e

INNER JOIN jobs j

ON e.job_id = j.job_id;

非等值连接

#查询某个员工的工资等级

SELECT e.last_name,j.grade_level

FROM employees e

INNER JOIN job_grades j

ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接

#查询某个员工及其对应的领导

SELECT e1.last_name 'employees',e2.last_name 'manager'

FROM employees e1

INNER JOIN employees e2

ON e1.manager_id = e2.employee_id;

b、外连接(OUTER JOIN)

在sql99标准中,又增加的外连接的功能。外连接又包括左外连接、右外连接和全外连接。多用来查询一张表中有,另一张表中没有的记录。

外连接会查询主表的所以记录

若主表中的某条记录在从表中有与之对应的记录,则相应的字段为从表的内容;

反正,相应的字段为null。

换句话说,外连接查询结果=内连接查询结果+主表有但从表没有的记录。

对于主从表的区分,有以下几种:

左外连接:主表 LEFT JOIN 从表

右外连接:从表 RIGHT JOIN 主表

全外连接:表1 FULL JOIN 表2,其中表1表2分别轮流作为主表,查询结果是两张表的"并集"。另外,全外连接在MySQL中不支持使用。

左外连接(LEFT JOIN ON)

#查询没有迟到记录的员工名

SELECT last_name,late.count_late

FROM employees

LEFT JOIN late

ON employees.employee_id = late.employee_id

WHERE late.count_late IS NULL;

右外连接(RIGHT JOIN ON)

#查询没有迟到记录的员工名

SELECT last_name,late.count_late

FROM late

RIGHT JOIN employees

ON employees.employee_id = late.employee_id

WHERE late.count_late IS NULL;

全外连接(FULL JOIN ON)

全外连接在mysql中不支持,语法如下:

SELECT beauty.name

FROM boys

FULL OUTER JOIN beauty

ON beauty.boyfriend_id = boys.id ;

c、交叉连接(CROSS JOIN)

效果即笛卡尔积形式,即两个表的所有记录都一一匹配一遍,查询的结果一共A×B条记录(A、B分别为两个表的记录数)

SELECT * FROM jobs CROSS JOIN locations;

7

子查询

嵌套在其他语句的SELECT语句为子查询(内查询),外部的查询语句为主查询(外查询)

可分类为:

标量子查询(结果集只有一行一列)

列子查询(结果集多为一列多行)
IN、NOT IN:等于/不等于列表中的任意一个
ANY/SOME:子查询中某一个值满足就行
ALL:子查询中所以值都满足

行子查询(结果集多为一行多列)

表子查询(结果集有多行多列)

(1)子查询在SELECT后面

只支持标量子查询,如:

#查询每个部门的信息加部门的人数

SELECT * ,

(SELECT COUNT(1) FROM employees WHERE employees.department_id = departments.department_id)

FROM departments;

(2)子查询在FROM后面

支持表子查询,在一个SELECT查询后的表中查询新的内容,如:

#查询每个部门的平均工资的工资等级

SELECT department_id,department_name,a , j.grade_level

FROM (

SELECT e.department_id ,d.department_name, AVG(salary) a

FROM employees e

LEFT JOIN departments d

ON e.department_id=d.department_id

GROUP BY e.department_id

) tmp

INNER JOIN job_grades j

ON tmp.a BETWEEN j.lowest_sal AND j.highest_sal;

(3)子查询在WHERE/HAVING后面

支持标量子查询、列子查询、行子查询

#标量子查询:查询工资最少的员工信息

SELECT *

FROM employees

WHERE salary = (

SELECT MIN(salary)

FROM employees

);

#列子查询:查询location_id为1400或1500或2700的部门中的所有员工姓名

SELECT last_name

FROM employees

WHERE department_id IN (

SELECT department_id

FROM departments

WHERE location_id IN(1400,1500,2700)

);

#行子查询:查询编号最小并且工资最高的员工信息

#满足行子查询的条件笔记苛刻,所以用的不多

SELECT *

FROM employees

WHERE (employee_id,salary) = (

SELECT MIN(employee_id),MAX(salary)

FROM employees

);

(4)子查询在EXISTS后面(相关子查询)

EXISTS(SELECT 语句):有记录,则为1,无记录,则为0

相关子查询是先执行外查询,在由EXISTS过滤; 都能用IN代替

#查询有员工的部门名

SELECT department_name

FROM departments d

WHERE EXISTS(

SELECT * FROM employees e WHERE e.department_id = d.department_id

);

#用IN代替

SELECT department_name

FROM departments d

WHERE d.department_id IN (

SELECT DISTINCT department_id

FROM employees

);

8

分页查询(LIMIT)

offset 要查询的起始索引,从0开始,缺省为0

size 要查询的记录数目

#查询有奖金的员工中工资最高的10个员工的信息

SELECT *

FROM employees

WHERE commission_pct IS NOT NULL

ORDER BY salary DESC

LIMIT 0, 10;

9

联合查询(UNION)

将多条查询语句的结果合并为一个结果,结果的字段名为第一个查询的字段。

要求各部分字段列数一致

要求各部分字段顺序一致

UNION默认去重,不去重用UNION ALL

#查询department_id>50和salary>5000的员工信息

SELECT *

FROM employees

WHERE department_id > 50

UNION

SELECT *

FROM employees

WHERE salary > 5000 ;


本文转自网络,如有侵权及时联系

MySQL数据库—SQL汇总

抖音,到底威胁了美国什么?

30 个 Python 的最佳实践、小贴士和技巧

简述 Linux 系统及与windows相比好在哪

MySQL数据库—SQL汇总

MySQL数据库—SQL汇总


以上所述就是小编给大家介绍的《MySQL数据库—SQL汇总》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

七周七语言

七周七语言

Bruce A.Tate / 巨成、戴玮、白明 / 人民邮电出版社 / 2012-5-8 / 59.00元

内容简介: 从计算机发展史早期的Cobol、Fortran到后来的C、Java,编程语言的家族不断壮大。除了这些广为人知的语言外,还涌现了Erlang、Ruby等后起之秀,它们虽被喻为小众语言,但因其独特性也吸引了为数不少的追随者。 Bruce A. Tate是软件行业的一名老兵,他有一个宏伟目标:用一本书的篇幅切中要害地探索七种不同的语言。本书就是他的成果。书中介绍了Ruby、Io、......一起来看看 《七周七语言》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码

html转js在线工具
html转js在线工具

html转js在线工具