在进入SQL学习之前要了解一些前置知识......

SQL分类

SQL主要分成三大类 DDL,DML,DCL

DDL（Data Definition Languages、数据定义语言）

DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。

DML（Data Manipulation Language、数据操作语言）

DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等。 SELECT是SQL语言的基础，最为重要。

DCL（Data Control Language、数据控制语言）

DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等。

SQL 语言规则和规范

规则

SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进每条命令以 ; 或 \g 或 \G 结束
关键字不能被缩写也不能分行
关于标点符号必须保证所有的()、单引号、双引号是成对结束的
必须使用英文状态下的半角输入方式字符串型和日期时间类型的数据可以使用单引号（' '）表示列的别名，尽量使用双引号（" "），而且不建议省略as

规范

MySQL 在 Windows 环境下是大小写不敏感的
MySQL 在 Linux 环境下是大小写敏感的数据库名、表名、表的别名、变量名是严格区分大小写的关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写
- SQL 关键字、函数名、绑定变量等都大写

注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

进入本章知识点 SELECT 语句

SELECT基本语法

SELECT

    SELECT 查询内容

SELECT......FROM

    SELECT 字段 FROM 表名

别名AS

    SELECT 查询内容 AS "别名"
    SELECT 字段 AS "别名" FROM 表名

去重DISTINCT

    SELECT DISTINCT 去重的字段 FROM 表明

5.空值参与运算

    所有运算符或列值遇到null值，运算的结果都为null

6.着重号

    如果遇到字段名和关键子冲突，需要将字段使用 `字段` 括起来

7.显示表的结构

    DESC 表名;
    DESCRIBE 表名;

8.WHERE 查询

    SELECT 字段1,字段2 ... 字段N 
    FROM 表名
    WHERE 字段 条件

练习题目

1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY

SELECT salary * (1 + IFNULL(commission_pct,0)) * 12  AS 'ANNUAL SALARY  '
FROM employees

2.查询employees表中去除重复的job_id以后的数据

SELECT DISTINCT job_id FROM employees

3.查询工资大于12000的员工姓名和工资

SELECT last_name,salary FROM employees WHERE salary > 12000

4.查询员工号为176的员工的姓名和部门号

SELECT last_name,department_id FROM employees WHERE department_id = 176

5.显示表 departments 的结构，并查询其中的全部数据

DESC departments

排序和分页

排序

语法

    SELECT 
        *
    FROM 表名
    ORDER BY ASC(升序) || DESC(降序)

单列排序

案例1：将employees表中的数据按照薪资进行升序排列

  SELECT * FROM employees ORDER BY salary ASC

多列排序

案例1：将employees表中的数据按照薪资和日期进行升序排列

   SELECT * FROM employees ORDER BY salary,hire_date ASC

注意：在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

分页LIMIT

语法

 SELECT * FROM 表名 LIMIT [位置偏移量,] 行数

案例1. 返回employees表中的前10行数据

SELECT * FROM employees LIMIT 10;
或者
SELECT * FROM employees  LIMIT 0,10;

分页公式：分页显式公式：（当前页数-1）*每页条数，每页条数

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

注意：LIMIT 子句必须放在整个SELECT语句的最后！

练习题：

查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示

SELECT last_name,department_id,12 * (1+IFNULL(commission_pct,0))*salary 
FROM employees ORDER BY 12 * (1+IFNULL(commission_pct,0))*salary DESC,last_name ASC

选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据

SELECT last_name,salary FROM employees WHERE salary NOT IN(8000,17000) ORDER BY salary DESC 
LIMIT 20,20

查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC ,department_id ASC

多表查询

多表查询也称为关联查询

多表链接会出现笛卡尔乘积的现象，笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

如何避免笛卡尔乘积的现象，那就是加入链接的条件

语法：

#SQL1992
SELECT table1.column, table2.column 
FROM table1, table2 
WHERE table1.column1 = table2.column2;


#SQL1999
SELECT table1.column, table2.column
FROM table1
INNER JOIN table2
ON table1.column = table2.column

等值链接

如果两个表有两个相同的列，如何区分?

在列名之前加上表名

SELECT employees.last_name, departments.department_name,employees.department_id 
FROM employees, departments 
WHERE employees.department_id = departments.department_id;

表别名的使用

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id 
FROM employees e , departments d 
WHERE e.department_id = d.department_id;

多个表进行链接查询

SELECT 
    e.last_name,d.`department_id`,l.`city`
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id` AND d.`location_id` = l.`location_id`

非等值连接

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自链接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

案例：查询出last_name为 ‘Chen’ 的员工的 manager 的信息。

SELECT 
    m.*
FROM employees e , employees m
WHERE e.`manager_id` = m.manager_id AND e.`last_name` = 'Chen'

非自链接

当我们链接的表不是同一张表的时候就做非自链接
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）
外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。
SQL92 创建链接的方式
在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。

#左外连接 
SELECT last_name,department_name 
FROM employees ,departments 
WHERE employees.department_id = departments.department_id(+); 
#右外连接
SELECT last_name,department_name 
FROM employees ,departments 
WHERE employees.department_id(+) = departments.department_id;

而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

SQL1999 创建链接的方式

UNION

语法

SELECT column,... 
FROM table1 
UNION [ALL] 
SELECT column,... FROM table2

UNION ALL

要记住这里的 7 中JOINS 连接情况

#中图：内连接 A∩B 
SELECT employee_id,last_name,department_name 
FROM employees e 
JOIN departments d 
ON e.`department_id` = d.`department_id`;

#左上图：左外连接 
SELECT employee_id,last_name,department_name 
FROM employees e LEFT 
JOIN departments d 
ON e.`department_id` = d.`department_id`;

#右上图：右外连接 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id`;

#左中图：A - A∩B 
SELECT employee_id,last_name,department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE d.`department_id` IS NULL

#右中图：B-A∩B 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d 
ON e.`department_id` = d.`department_id` 
WHERE e.`department_id` IS NULL

#左下图：满外连接 # 左中图 + 右上图 A∪B 
SELECT employee_id,last_name,department_name 
FROM employees e 
LEFT JOIN departments d 
ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL 
UNION ALL #没有去重操作，效率高 
SELECT employee_id,last_name,department_name 
FROM employees e 
RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;

函数

函数又分成两大类，单行函数和多行函数

单行函数

常用单行函数。

数值函数

1返回某个数的绝对值

SELECT ABS(-100)

2.返回数的符号正数返回1，负数返回-1 0 返回0

SELECT SIGN(1);

SELECT SIGN(0);

SELECT SIGN(-1);

3.返回圆周率的值

SELECT PI()

4.向上取整数

SELECT CEIL(5.1) #6

SELECT CEIL(5.0) #5

SELECT CEIL(-5.0) #-5

SELECT CEIL(-5.1) #-5

SELECT CEIL(-5.6) #-5

SELECT CEILING(5.1);

SELECT CEILING(5.0);

SELECT CEILING(-5.0);

SELECT CEILING(-5.1);

SELECT CEILING(-5.6);

5.向下取整

SELECT FLOOR(5.1); #5

SELECT FLOOR(5.0); #5

SELECT FLOOR(-5.0); #-5

SELECT FLOOR(-5.1); #-6

SELECT FLOOR(-5.6); #-6

6.返回列表中最小值

SELECT LEAST(12,20,-5,0)

7.返回列表中的最大值

SELECT GREATEST(12,20,-5,0)

8.两个数取余数

SELECT MOD(10,20); #10

SELECT MOD(10,-20); #10

SELECT MOD(-10,20); #-10

SELECT MOD(-10,-20); #-10

9.返回0 - 1之间的随机值

SELECT RAND()

10.返回 0- 1之间的随机值其中x的值用作种子值，相同的X值会产生相同的随机数

SELECT RAND(5)

11.生成指定范围内的随机数计算公式：SELECT FLOOR(x + rand()(y-x));*

SELECT FLOOR(1 + RAND()*(100-1))

*12.四舍五入 * SELECT ROUND(6.5) # 7

SELECT ROUND(6.4) # 6

13.保存小数点后指定位置

SELECT ROUND(6.5,2) #6.50

SELECT ROUND(6.4,2) #6.40

14.返回一个数被截断的结果

SELECT TRUNCATE(6.40,1)

15.一个数值开根号

SELECT SQRT(9) #3

SELECT SQRT(-1) #NULL

进制转换函数

1.将一个数字转换成二进制

SELECT BIN(5);

2.将一个数字转换成十六进制

SELECT HEX(15);

3.将一个数字转换成八进制

SELECT OCT(8);

字符串相关

1.返回字符的ASCII

SELECT ASCII('a')

2.返回字符串的个数

SELECT CHAR_LENGTH('abc')

3.返回字符串的字节数量,和编码有关

SELECT LENGTH('牛')

SELECT LENGTH('a')

4.字符串拼接函数

SELECT CONCAT('a1','a2')

5.字符串拼接，每一个字符串之间加上指定的字符

SELECT CONCAT_WS('-','a1','a2')

6.小写转大写

SELECT UPPER('abc')

7.大写转小写

SELECT LOWER('ABC')

8.将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr

SELECT INSERT('helloworld',2,5,'12345')

9.用字符串b替换字符串str中所有出现的字符串

SELECT REPLACE('ddwanw','w','hh')

10.返回字符串左边的 n个字符

SELECT LEFT('abcdef',2)

11.返回字符串右边的 n个字符

SELECT RIGHT('abcdef',2)

12.去掉字符串左侧空格

SELECT LTRIM(' 123456')

13.去掉字符串右侧空格

SELECT RTRIM('123456 ')

14.去掉首尾空格

SELECT TRIM(' 123456 ')

15.去掉字符串s开始与结尾的s1 ??

SELECT TRIM('12321' FROM '12345')

16.返回一个字符串重复n次的结果

SELECT REPEAT('str',2)

17.返回n个空格

SELECT CONCAT(SPACE(5),'123')

18.比较两个字符串的ASCII的值的大小

SELECT STRCMP('abc','def') # 小于 -1 SELECT STRCMP('abc','abc') # 等于 0 SELECT STRCMP('def','abc') # 大于 1

19.字符串的截取

SELECT SUBSTR('abcdef',1,4) #abcd

20.字符串的反转

SELECT REVERSE('abcdef')

21.某个字符串第一次出现的位置

SELECT LOCATE('ab','dfcab')

22.在多个字符串中返回指定的串

SELECT ELT(2,'45','sd','ew')

23.返回字符串s在字符串列表中第一次出现的位置

SELECT FIELD('s1','12','s1') #找到的话就正常返回

SELECT FIELD('s1','12','s2') #没有找到返回0

日期函数

mysql信息函数

1.当前MySQL的版本号

SELECT VERSION()

2.当前服务器的链接数量

SELECT CONNECTION_ID()

3.查看当前使用的是哪一个数据库

SELECT DATABASE()

SELECT SCHEMA()

4.返回当前mysql的用户名

SELECT USER()

SELECT SESSION_USER()

SELECT CURRENT_USER()

SELECT SYSTEM_USER()

函数类型太多了 ~~~ 不一一列举了，用到哪一个然后去菜鸟教程查即可

聚合函数

常用的聚合函数

1、AVG() 求平均值

2、SUM() 求和

3、MAX() 求最大值

4、MIN() 求最小值

5、COUNT() 求总行数

SELECT COUNT(*) FROM employees                             # 返回总记录数
SELECT COUNT(commission_pct) FROM employees        # 返回总记录数，但是不包含为空的记录

既然使用到了聚合函数就不得不使用 GROUP BY ...... HAVING 了

语法：

SELECT
    字段
FROM 表名
WHERE 查询条件
GROUP BY 分组 
HAVING 分组后筛选
ORDER BY 排序字段
LIMIT 限制条数

HAVING 一定一定不能单独的去使用，它一定是配合GROUP BY 一块使用的在WHERE的后面不能使用聚合函数

SQL语句的执行顺序 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

表的创建和管理

表的命名规则：

1、数据库名、表名不得超过30个字符，变量名限制为29个

2、必须只能包含 A–Z, a–z, 0–9, _共63个字符

3、数据库名、表名、字段名等对象名中间不要包含空格

4、同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名

5、必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用（着重号）引起来

6、保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型了

MySQL中的数据类型

创建和管理数据库

数据库操作

创建数据库命令,方式一：CREATE DATABASE 数据库名;

创建数据库命令，方式二：CREATE DATABASE 数据库名 CHARACTER SET 字符集;

创建数据库命令，方式三：CREATE DATABASE IF NOT EXISTS 数据库名;

查看所有的数据库：SHOW DATABASES;

查看当前正在使用的数据库：SELECT DATABASE();

查看指定库下所有的表：SHOW TABLES FROM 数据库名;

查看数据库的创建信息：SHOW CREATE DATABASE 数据库名;

切换数据库：use 数据库名

更改数据库字符集：ALTER DATABASE 数据库名 CHARACTER SET 字符集;

删除数据库：DROP DATABASE 数据库名; 或者 DROP DATABASE IF EXISTS 数据库名;

数据表操作

创建表语法1：

CREATE TABLE [IF NOT EXISTS] 表名
( 
    字段1, 数据类型 [约束条件] [默认值], 
    字段2, 数据类型 [约束条件] [默认值], 
    字段3, 数据类型 [约束条件] [默认值], …… [表约束条件]
)

查看表结构：DESC 表名

创建表的语法2：

使用 AS subquery 选项，将创建表和插入数据结合起来

create table 表名(
    colume1,colume2 .... columen
) as 子查询

修改表操作：

追加列：ALTER TABLE 表名 ADD 【COLUMN】字段名字段类型【FIRST|AFTER 字段名】;

修改列：ALTER TABLE 表名 MODIFY 【COLUMN】字段名1 字段类型【DEFAULT 默认值】【FIRST|AFTER 字段名 2】;

修改列名：ALTER TABLE 表名 CHANGE 【column】列名新列名新数据类型;

删除列： ALTER TABLE 表名 DROP 【COLUMN】字段名

删除表

删除表操作：DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

清空表

清空表：TRUNCATE TABLE detail_dept;

CRUD

INSERT

插入语法1：INSERT INTO 表名 VALUES (value1,value2,....);

语法1要注意的点：值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

插入语法2：INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);

语法2注意点：为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,....valuen需要与 column1,...columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误。

插入语法3：

INSERT INTO table_name VALUES 

(value1 [,value2, …, valuen]), 

(value1 [,value2, …, valuen]), 
…… 
(value1 [,value2, …, valuen]);

UPDATE

语法：UPDATE 表名 SET 列名 = 列值

DELETE

语法：DELETE from 表名

视图

常见的数据库对象

图片.png

视图的概述

视图：可以看到的数据。只是提供一部分的数据给展示。视图本身是一种虚拟表，不占有数据，内存空间占用的比较少。视图的创建和删除只是影响视图的本身，不会影响基础表。但是当对视图中的数据进行添加、删除、修改的时候，数据表中的数据会发生相应的变化。

视图操作

1、创建视图

创建视图方式一

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW 视图名称 [(字段列表)] AS 查询语句 [WITH [CASCADED|LOCAL] CHECK OPTION]

创建视图方式二

create  view 视图名称 as 查询语句

例子1：创建部门为80号的视图

CREATE VIEW empview80
AS 
SELECT employee_id,last_name,salary
FROM employees
WHERE department_id = 80;

查看视图中的数据

select * from 视图名称

MYSQL学习上篇