MySQL之SQL语句

SQL 语句分类

DDL(Data Definition Language)数据定义语言

DML(Data Manipulation Language)数据操作语言

DQL(Data Query Language)数据查询语言

DCL(Data Control Language)数据控制语言

SQL语言的规则与规范

•  SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
• 每条命令以 ; 或 \g 或 \G 结束
•  关键字不能被缩写也不能分行
•  关于标点符号

1.    必须保证所有的()、单引号、双引号是成对结束的
2.    必须使用英文状态下的半角输入方式
3.   字符串型和日期时间类型的数据可以使用单引号（' '）表示
4.    列的别名，尽量使用双引号（" "），而且不建议省略as

数据导入指令

在命令行客户端登录mysql，使用source指令导入

mysql> source d:\mysqldb.sql

mysql -uroot -p
password:***
CREATE DATABASE my_db;
USE my_db;
SOURCE d:my_db.sql;

 DDL语句

DDL是数据定义语言,主要是对数据库和数据表的操作

（1）操作数据库

--创建库
create database 库名;
--创建库时判断库是否存在，不存在则创建
create database if not exists 库名;
--查看所有数据库
show databases;
--使用指定数据库
use 库名;
--查看当前指定数据库包含的数据表
show tables;
--查看数据库的结构定义信息
show create database 库名;
--删除数据库
drop database 库名;
--修改数据库的字符集为utf8
alter database 库名 character set utf8;

（2）操作数据表

--创建表
create table 表名(
字段1 类型1,
字段2 类型2,
字段3 类型3,
...........
);
--查看表结构
desc 表名;
--查看创建表的SQL语句
show create table 表名;
--修改表名
alter table 表名 rename to 新的表名;
--添加一个新的字段
alter table 表名 add 字段; 字段类型;
--修改字段名
alter table 表名 rename column 字段名 to 新的字段名;
--修改字段类型(注意类型修改前后数据是否兼容)
alter table 表名 modify column 字段名 新的字段类型;
--删除一个字段
alter table 表名 drop 字段名;
--删除表
drop table 表名;
--删除表时判断表是否存在,若存在则删除
drop table if exists 表名;

DML

DML是数据操作语言,主要是对数据表的操作

（1）插入数据(insert into)

数据表插入数据分为全字段插入数据和部分字段插入数据

--全部列添加数据，有多少列就要写多少值
INSERT INTO 表名 VALUES（值1,值2,…）;

--给指定列添加数据
INSERT INTO 表名 （列1,列2）VALUES (值1,值2);

--批量添加数据
INSERT INTO 表名 VALUES（值1,值2,…）,（值1,值2,…）,（值1,值2,…）…;
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

（2）修改数据(update)

UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;

注意：

1. 修改语句中如果不加条件，则将所有数据都修改！
2. 像上面的语句中的中括号，表示在写sql语句中可以省略这部分

（3）删除数据(delete / truncate)

--删除表中所有数据
delete from 表名;
--删除表中指定的数据
delete from 表名 where 字段 = 值;
--删除表中所有数据(先删除整张表,然后创建一张一样的空表，此方法更高效)
truncate table 表名;

 DQL

DQL是数据查询语言，主要就是select配合其他限制条件的关键字进行查询

（1）无条件查询

--查询表中所有数据
select *from 表名;

（2）查询在...到...之间(between and / && / and)

--查询users表中年龄在18~25岁之间的记录
--方式1 between..and..
select *from users where age between 18 and 25;
--方式2 &&
select *from users where age>=18 && age<=25;
--方式3 and
select *from users where age>=18 and age<=25;

（3）指定条件查询

①单个条件(or / in)
--查询users表中年龄为18,20,25岁的记录
--方式1 or
select *from users where age=18 or age=20 or age=25;
--方式2 in
select *from users where age in(18,20,25);

②多个条件(and)
--查询users表中年龄为23，性别为女，名字为小楠的记录
select *from users where age=23 and gender='女' and name='小楠'; 

(4)查询不为NULL值(is not null),为NULL值(is null)

--查询users表中序号不为空的记录
select *from users where id is not null;
--查询user表中序号为空的记录
select *from users where id is null;

(5)模糊查询（like)

_:单个任意字符
%:多个任意个字符
--查询users表中姓名第一个字为李的记录
select *from users where name like '李%';
--查询users表中姓名第二个字为李的记录
select *from users where name like '_李%';
--查询users表中姓名含有李字的记录
select *from users where name like '%李%';
--查询users表中姓名是两个字的记录
select *from users where name like '__';

(6)去除重复记录查询（distinct)

--查询users表中所在城市不相同的记录
--select distinct 字段 from 表名;
select distinct city from users;

(7)排序查询（order by）

①单个条件
--查询users表中记录，并以年龄升序排序
select *from users order by age; --默认升序
 
--查询users表中记录，并以年龄降序排序
select *from users order by age desc;--desc降序

②多个条件

注意：多个排序条件时，只有当第一个排序条件值一样，才会执行第二个排序条件，以此类推

--查询users表中记录，并体育成绩降序，年龄降序
select *from users order by PE desc,age desc;

（8）聚合函数

聚合函数类型

- AVG()

- SUM()

- MAX() 

- MIN()

- COUNT() 

--可以对**数值型数据**使用AVG 和 SUM 函数。
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   表名
WHERE  job_id LIKE '%REP%';

--可以对**任意数据类型**的数据使用 MIN 和 MAX 函数。
SELECT MIN(字段), MAX(字段)
FROM	表名;

(9)分组查询（group by）

--查询users表中的记录，按照性别分组，查询男，女的体育成绩平均分
select gender,avg(PE) from users group by gender;
--查询users表中的记录，按照性别分组,分别查询男、女的体育成绩平均分,人数
select gender, avg(PE),count(id) from users group by gender;
--查询users表中的记录, 按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人，不参与分组
select gender, avg(PE),count(id) from users where PE > 60 group by gender; 
 --查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人，不参与分组,分组之后,人数要大于2个人
select gender,avg(PE),count(id) from users where PE > 60 group by gender having count(id)>2;

(10)分页查询（limit）

LIMIT [位置偏移量,] 行数
--查询users表中的前10行条记录
select *from users limit 10;
--查询users表中第2~11条记录 (从第2条记录开始累加10条记录)
select *from users limit 1,10;
--查询users表中第5~17条记录 (从第5条记录开始累加13条记录)
select *from users limit 4,13;

（11）内连接查询

内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行

格式
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

（12）子查询

• 子查询（内查询）在主查询之前一次执行完成。
• 子查询的结果被主查询（外查询）使用 。

注意事项
  - 子查询要包含在括号内
  - 将子查询放在比较条件的右侧

  - 单行操作符对应单行子查询，多行操作符对应多行子查询

我们按内查询的结果返回一条还是多条记录，将子查询分为`单行子查询`、`多行子查询`。

单行子查询

单行比较操作符

 HAVING 中的子查询

•  首先执行子查询。
•  向主查询中的HAVING 子句返回结果

--题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);

多行子查询

- 也称为集合比较子查询
- 内查询返回多行
- 使用多行比较操作符

多行比较操作符

 题目：返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary

（13）外连接查询

外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

--左外连接(LEFT OUTER JOIN)
#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

--右外连接(RIGHT OUTER JOIN)
#实现查询结果是B
SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;