MYSQL 语句（了解常用语句）

总结内容

1. 基本概念

• 数据库的概念

1.  结构化查询语言（Structured Query Language）简称SQL；
2.  数据库管理系统（Database Management System）简称DBMS；
3.  数据库管理员（Database Administration）简称DBA，功能是确保DBMS的正常高效运行；

• SQL常用的3个部分

1. 数据查询语言（DQL）：其语句也称“数据库检索语句”，用以从表中获得数据，保留字SELECT经常使用，DQL也是所有SQL中用的最多的，其他保留字还有WHERE, ORDER BY, GROUP BY和HAVING这些保留字还与DML一起使用；
2. 数据操作语言（DML）：其余局包括动词INSERT，UPDATE和DELETE。他们分别用于添加，修改和删除表中的行。也称动作语言；
3. 数据定义语言（DDL）：DDL主要用于操作数据库。

2.sql列常用的类型

MySQL:           |         Java:
INT              |         int
BIGINT           |         long
DECIMAL          |         BigDecimal
DATE/DATETIME    |         java.util.Date
VARCHAR          |         String

3.DDL简单操作

3.1 数据库操作

• 连接数据库语句

  mysql -uroot -padmin;

• 查看数据库列表：

show databases

• 创建数据库

create database 数据库名称;

• 删除数据库

drop database 数据库名称;；

• 修改数据库（alter databese）

# 修改数据库编码格式
alter database 数据库名称 charset=编码格式;

• 查看当前数据库下所有数据表

show tables;

3.2 表操作

• 表的约束

1. 非空：NOT NULL，不允许某列的内容为空；
2. 设置列的默认值：DEFAULT；
3. 唯一约束：UNIQUE，该表中，该列的内容必须唯一；
4. 主键约束：PRIMARY KEY，非空且唯一；
5. 主键自增长：AUTO_INCREMENT，从1开始，步长为1；
6. 外键约束：OREIGN KEY，A表中的外键列。A表中的外键列的值必须参照于B表中的某一列（B表主键）。【个人不推荐用，因为需要删除表的时候要把两张表（主表从表）都删除掉，而且使麻烦】

• 建表

1. 建表语法：

列名1  列的类型  [约束],
列名2  列的类型  [约束],
...
列名N  列的类型  [约束]
);
// 注意：最后一行没有逗号

例子：建立一个学生表（t_student) 字段有 id name email age

注意：建表不要使用关键字

## 如果表存在就移除，因为不能存在两个一样名称的表
DROP TABLE IF EXISTS 't_student';
CREATE TABLE t_student(
  id    BIGINT          PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(25)     UNIQUE,
  email VARCHAR(25)     NOT NULL,
  age   INT             DEFAULT  17
  );

• 删除表

1. 删除表语法

DROP TABLE 表名;

4.DML操作

4.1修改操作

语法

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

实战

update  fdy_user
set  name = '张三'
where name = '李四';

注意：不要省略where条件子句，省略的话，全表数据都会被修改。

4.2 插入操作（INSERT INTO VALUE）

• 语法

INSERT INTO 表名 (列1，列2，...) VALUE (值1，值2，...);

• 实战

#  1、插入完整数据记录
insert  into  fdy_user( name, sex, age, pid) value ('张三','男',15);
#  2、插入部分数据记录
INSERT INTO fdy_user (name, age) VALUE ('李四', 16);
#  3、插入查询结果
INSERT INTO fdy_user(name, sex, age) SELECT name,sex,age FROM fdy_user;

4.3 删除操作（DELETE）

• 语法

DELETE FROM 表名 WHERE [条件]

• 实战

// 删除 id 为 2 的学生信息
DELETE FROM fdy_user WHERE id = 2;

注意：Where子句别省略，否则全表的数据都会被删除。

5. DQL操作

 被操作的表

5.1 消除重复元素（DISTINCT）

SELECT DISTINCT 列名, ... FROM 表名;

注意：distinct 放在2个字段前，是2个字段组合后重复才去重，放在第一个后会报错

• 实战：

SELECT DISTINCT name  FROM fdy_user;

5.2 算术运算符（+，-，*，/）

• 算术运算符的使用范围
  1）对 number 型数据可以使用算术运算符（+，-，*，/）对数据进行操作；对date型数据可以使用部分算术运算符（+，-）对数据进行操作。

• 算术运算符的优先级
  1）与数学中运算相同

• 实战

// 查询所有货物的id，名称和批发价（折扣价=销售价*折扣）
SELECT id, name, price* discount From fdy_user;

5.3 设置别名（AS）

• 作用
  1）改变列的标题头；
  2）作为计算结果的含义；
  3）作为列的别名；
  4）如果别名使用特殊字符（强烈不建议使用特殊字符），或是强制大小写或有空格时都需要加单引号。

• 语法

// 第一种
SELECT 列名 AS 别名 FROM 表名 [WHERE];
// 第二种
SELECT 列名 别名 FROM 表名 [WHERE];

• 实战

// 查询所有货物的id，名称和折扣价价（折扣价=销售价*折扣）(使用别名）
SELECT id, name, price* discount AS p From fdy_user;

5.4 按格式输出（CONCAT）

1）为了方便用户浏览查询结果数据，有时需要设置查询结果的显示格式，可以使用CONCAT函数来连接字符串。

• 语法

CONCAT(字符串1, 字符串2, ...)

• 实战

// 查询商品的名字和零售价。格式：xxx商品的零售价为：ooo
SELECT CONCAT(name, " 商品的零售价为：", saleprice) FROM fdy_user;

5.5 过滤查询（WHERE）

1）使用WHERE子句限定返回的记录。

• 语法

SELECT <selectList> FROM 表名 WHERE 条件;

注意：WHERE子句在FROM子句之后。

• 实战

// 查询商品名为 麻辣毛蛋  的货品信息
SELECT * FROM fdy_userWHERE name = "麻辣毛蛋";

// 查询批发价大于350的货品信息（折扣价 = 销售价*折扣）
SELECT *, saleprice * discount FROM fdy_user WHERE saleprice * discount > 350;

5.6 比较运算符（=, >, >=, <, <=, !=）

1）不等于：<> 等价 !=；

• 实战

// 查询商品名为 麻辣毛蛋 的货品信息
SELECT * FROM fdy_user WHERE name = "麻辣毛蛋";
// 查询批发价大于350的货品信息（折扣价 = 销售价*折扣）
SELECT *, salePrice * discount FROM fdy_user WHERE salePrice * discount > 350;

5.7 逻辑运算符（AND、OR、NOT）

1）AND：如果组合的田间都是 true，返回true；
2）OR：如果组合的条件之一是true，返回true；
3）NOT：如果给出的条件是false，返回true；如果给出的条件是true，则返回false。

• 实战

// 查询售价在300-400（包括300和400）的货品信息
SELECT * FROM fdy_user WHERE salePrice >= 300 ADN salePrice <= 400;
// 查询分类编号为2, 4的所有货品信息
SELECT * FROM fdy_user WHERE dir_id = 2 OR dir_id = 4;
// 查询编号不为2的所有商品信息
SELECT * FROM fdy_user WHERE NOT dir_id = 2

5.8 范围和集合（BETWEEN AND）

(1)范围匹配：BETWEEN AND 运算符，一般使用在数字类型的范围上。但对于字符数据和日期类型同样可用。
注意：BETWEE AND 使用的是闭区间。

• 语法

// 使用的是闭区间，也就是包括minValue 和 maxValue
WHERE 列名 BETWEEN minValue AND maxValue;

• 实战

// 查询零售价不在 300 - 400 之间的货品信息
SELECT * FROM fdy_user WHERE NOT salePrice BETWEEN 300 AND 400;

(1)集合查询：使用 IN 运算符，判断列的值是否在指定的集合中。

• 语法

WHERE 列名 IN (值1, 值2, ...);

• 实战

// 查询分类编号为 2，4 的所有货品的 id，货品名称
SELECT id, name FROM fdy_user WHERE dir_id IN (2,4);
// 查询分类编号不为 2, 4 的所有货品的 id，货品名称
SELECT id, dir_id, name FROM fdy_user WHERE NOT dir_id IN (2,4);

5.9 判空（IS NULL)

1）IS NULL：判断列的值是否为空值，非空字符串，空字符串使用 == 判断；

• 语法

WHERE 列名 IS NULL;

• 实战

  // 查询商品名为NULL的所有商品信息
  SELECT * FROM fdy_user WHERE productName IS NULL;
  SELECT * FROM fdy_user WHERE supplier = "";

  结论：使用 = 来判断只能判断空字符串，不能判断 null；而使用 IS NULL 只能判断 null 值，不能判断空字符串。

• 5.10 模糊匹配查询（LIKE，%，_）

  1）LIKE：模糊查询数据使用 LIKE 运算执行通配符；
  2）通配符：% 表示可有零个或多个任意字符; _ 便是需要一个任意字符；

• 语法

WHERE 列名 LIKE "%M_";

• 实战

// 查询货品名称以 毛蛋* 结尾的所有货品信息，例如麻辣毛蛋，这里的 * 表示一个任意字符，它不具备任何意义，只是我出于题目需要才这样写，便于你理解而已
SELECT * FROM fdy_user WHERE productName LIKE "%毛蛋";

5.11 结果排序（ORDER BY）

1）ORDER BY：使用 ORDER BY子句将查询结果进行排序，ORDER BY子句出现在 SELECT 语句的最后；
2）ASC：升序；DESC：降序；

• 实战

// 查询 id，货品名称，分类编号，零售价 按分类编号降序排序，如果分类编号相同再按零售价升序排序
SELECT * FROM fdy_user ORDER BY dir_id DESC, salePrice ASC;

5.12 分组查询（GROUP BY）

1）Group By：分组查询，一般与having一起用，查询的参数只能是统计函数和分组的条件参数，或者是聚合函数，having 后面的条件只能是分组查询的字段或者统计函数；

• 实战

# 查询分组条件之外的值，拼接成字符串
select theme_id, group_concat(dest_id) dest_id, group_concat(dest_name) dest_name from fdy_user group by theme_id having theme_id = 1

5.13 DQL字句的执行顺序

1. FROM字句：从哪张表中去查数据；
2. JOIN table：先确定表，在确定关联条件；
3. ON 条件：表绑定条件；
4. WHERE字句：筛选需要的行数据；
5. GROUP BY子句：分组操作；
6. HAVING：对分组后的记录进行聚合；
7. SELECT字句：筛选需要显示的列数据；（对字段 AS 起别名是在这时候，所以 WHERE 中不能用字段的别名，ORDER BY 中可以使用别名）；
8. DISTINCT：去重；
9. ORDER BY子句：排序操作；
10. LIMIT子句：限制条件；

6. 统计函数

6.1 常用关键字(COUNT、SUM、MAX、MIN、AVG）

• 概念

1. COUNT(*)：统计表中有多少条数据；
2. SUM(列)：汇总列的总和；
3. MAX(列)：获取某一列的最大值；
4. MIN(列)：获取某一列的最小值；
5. AVG(列)：获取某一列的平均值；

• 实战

// 查询货品表共有多少数据
SELECT COUNT(*) FROM fdy_user;
// 计算所有货品的销售价
SELECT SUM(costPrice) FROM fdy_user;

6.2数学函数

• ceil(num) 返回整数 
• ​floor(num) 返回整数
• ​rand() 0-1之间的随机数
• round() 四舍五入
• power() 幂
• ​sqrt() 算术平方根
• abs(); 绝对值
• sign(); 标记正负零

6.3字符串函数

• concat(str1,str2,...) ​
• insert(str,startIndex,count,insertStr) ​
• lower() ​
• upper() ​
• substring(str,startindex,count) ​
• left('abc123',4) abc1 ​
• right() ​
• length() 字节的长度
• char_length()字符长度 ​
• replace('abc123abc123','a','x')

6.4日期函数

• now()
• date('日期')
• time('日期')
• week('日期')
• adddate('日期',正负数);
• datediff('日期','日期'),两个日期的天差

6.5转换函数

• cast

1. select CAST(123 AS char); 
2. select cast('123' as decimal);

• convert

1. select convert(123 , char);
2. select convert('123' , decimal);

6.6分组函数

select count(*),deptno from emp group by deptno;
select count(*),deptno,job from emp group by deptno,job;

==凡是跟聚合函数一起查询的列，列一定要放在group by 之后==

having分组筛选

select count(*),deptno from emp  group by deptno having count(*)>3;

where和having筛选

select count(*),deptno from emp
where sal>=2000 group by deptno 
having count(*)>1 
order by count(*) desc

==where是筛选from之后，having筛选group by之后 ，排序在最后==

如果有限行，限行在最最后

表链接

需要的数据来自多个表，需要连接

内连接

select * from emp,dept where emp.deptno=dept.deptno
select * from emp [inner] join  dept on emp.deptno=dept.deptno
select * from emp e where exists (select * from dept d where d.deptno=e.deptno);

外连接

左外连接

select * from emp left [outer] join  dept on emp.deptno=dept.deptno

右外连接

select *  from dept right [outer] join  emp on emp.deptno=dept.deptno

交叉连接

select * from emp,dept;
select * from emp cross join dept

select * from emp,dept where emp.deptno=dept.deptno -- 内连接
select * from emp,dept where emp.deptno<>dept.deptno -- 交叉连接减去内连接 

子查询

其实是from之后包含sql,可以完全代替表链接，表链接不能代替子查询。

关系运算符

-- :SALES :
-- ①根据部门名称或的部门id
select  pid from  branch_user  where branch='第二部门'
-- ②根据部门编号获得员工信息
select name from  fdy_user where pid=();
--组合①和②
# 关系运算符
select name from  fdy_user where pid=(select  pid from  branch_user  where branch='第二部门');

in/not in

获得这两个SALES和ACCOUNTING部门员工信息
一、根据部门名称或的部门id
select  pid from  branch_user where  branch in ('第一部门','第二部门')
二根据编号获得员工信息
select  name from  fdy_user where pid in ();
组合案例
select  name from  fdy_user where pid not in (select  pid from  branch_user where  branch in ('第一部门','第二部门'));
取反
select  name from  fdy_user where pid not in (select  pid from  branch_user where  branch in ('第一部门','第二部门'));

exists/not

查SALES部门是否存在员工

select *from fdy_user e where exists (select * from branch_user d where d.pid=e.pid  and branch='第二部门');
select *from fdy_user e where not exists (select * from branch_user d where d.pid=e.pid  and branch='第一部门');

查询没有部门的员工信息

select *from fdy_user e where not exists (select * from branch_user d where d.pid=e.pid );

some/any/all

some和any用法一样

求公司里的所有员工，有低于 ACCOUNTING门的员工的工资的员工信息 
根据部门名称获得部门编号
一select pid from  branch_user where branch ='第一部门';
根据部门编号获得员工底薪
二select  age from fdy_user where pid=(二);

三select * from  fdy_user where  fdy_user.age < some(二) and pid <>(一);


select * from  fdy_user
        where  fdy_user.age < some(select  age from fdy_user
        where pid=(select pid from  branch_user where branch ='第一部门'))
        and pid <>(select pid from  branch_user where branch ='第一部门');

all

求公司里的所有员工，低于 ACCOUNTING门的员工的最低工资的员工信息 
根据部门名称获得部门编号
select  pid from  branch_user where branch='第一部门';
根据部门编号获得员工底薪
select  age from  fdy_user where  pid =(select  pid from  branch_user where branch='第一部门');


select  * from   fdy_user
          where  age < some (select  age from  fdy_user
          where  pid =(select  pid from  branch_user where branch='第四部门'))
          and pid <>(select  pid from  branch_user where branch='第四部门');

分页查询

 分页目的:提高性、有利于交互

select * from fdy_userlimit 1,size
select * from fdy_user limit 6,size
select *from fdy_userlimit 11,size
select * from fdy_userlimit 16,size


int index, -- 第几页
int size,  -- 页大小
select * from fdy_userlimit (index-1)*size+1,size;

表联合

表联合要保证列数一样，把两个表的数据合在一起

select deptno from dept
UNION [ALL]
select ename from emp;

all查出所有重复数据

关于空值（null）

关于mysql中对于字段值为null的查询方式

对于null值查询，无效果

查询运算符（>,<等）、like、between and、in、not in对NULL值查询不起效

对于null值得专属查询

IS NULL/IS NOT NULL（NULL值专用查询）

select 列名 from 表名 where 列 is null;
select 列名 from 表名 where 列 is not null;

总结

以上就是对 MYSQL的 SQL 语句的总结了，代码仅供参考，欢迎讨论交流。

练习表  子查询联系

/*
Navicat MySQL Data Transfer

Source Server         : mysql
Source Server Version : 50540
Source Host           : 127.0.0.1:3306
Source Database       : qq

Target Server Type    : MYSQL
Target Server Version : 50540
File Encoding         : 65001

Date: 2023-08-04 08:27:46
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
                         `gradeId` int(11) NOT NULL,
                         `gradeName` varchar(50) NOT NULL,
                         PRIMARY KEY (`gradeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('1', 's1');
INSERT INTO `grade` VALUES ('2', 's2');
INSERT INTO `grade` VALUES ('3', 'y2');

-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
                          `studentno` int(11) NOT NULL,
                          `subjectno` int(11) NOT NULL,
                          `studentResult` decimal(10,2) DEFAULT NULL,
                          `examdate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES ('10000', '2', '70.00', '2009-02-15 00:00:00');
INSERT INTO `result` VALUES ('10000', '2', '60.00', '2009-02-17 00:00:00');
INSERT INTO `result` VALUES ('10001', '2', '46.00', '2009-02-17 00:00:00');
INSERT INTO `result` VALUES ('10002', '2', '83.00', '2009-02-17 00:00:00');
INSERT INTO `result` VALUES ('10011', '2', '95.50', '2009-02-17 00:00:00');
INSERT INTO `result` VALUES ('20012', '2', '93.00', '2009-02-17 00:00:00');
INSERT INTO `result` VALUES ('30021', '2', '23.00', '2009-02-17 00:00:00');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
                           `studentNO` int(11) NOT NULL,
                           `studentName` varchar(50) NOT NULL,
                           `sex` int(11) DEFAULT NULL,
                           `borndate` datetime NOT NULL,
                           `address` varchar(200) DEFAULT '地址不详',
                           `gId` int(11) NOT NULL,
                           PRIMARY KEY (`studentNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('10000', '郭靖', '1', '1983-09-08 00:00:00', '天津市河西区', '1');
INSERT INTO `student` VALUES ('10001', '李文才', '1', '1989-03-04 00:00:00', '地址不详', '1');
INSERT INTO `student` VALUES ('10002', '李斯文', '1', '1988-08-08 00:00:00', '河南洛阳', '1');
INSERT INTO `student` VALUES ('10011', '武松', '0', '1980-12-31 00:00:00', '河南洛阳', '1');
INSERT INTO `student` VALUES ('20011', '张三', '1', '1990-01-01 00:00:00', '北京海淀区', '1');
INSERT INTO `student` VALUES ('20012', '张秋丽', '0', '1986-06-05 00:00:00', '北京东城区', '1');
INSERT INTO `student` VALUES ('20015', '肖梅', '0', '1992-10-01 00:00:00', '北京东城区', '2');
INSERT INTO `student` VALUES ('30021', '欧阳俊雄', '1', '1992-09-29 00:00:00', '上海市卢湾区', '2');
INSERT INTO `student` VALUES ('30023', '梅超风', '0', '1993-08-30 00:00:00', '广州市天河区', '3');

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
                           `subjectId` int(11) NOT NULL,
                           `subjectName` varchar(50) NOT NULL,
                           `classes` int(11) NOT NULL,
                           `gradeId` int(11) NOT NULL,
                           PRIMARY KEY (`subjectId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES ('1', 'html', '55', '1');
INSERT INTO `subject` VALUES ('2', 'java', '72', '1');

# java
select subjectId from  subject where subjectName  ='java';
# java 课程年级
select gradeId from  subject where subjectName  ='java';
# 最后一次考试时间
select   max(examdate) from  result where  subjectno =(select subjectId from  subject where subjectName  ='java')  ;
# 学生id
select studentno from  result  where examdate in
    (select   max(examdate) from  result where  subjectno =(select subjectId from  subject where subjectName  ='java'));
# 查询
select  *  from  student where  studentNO in (select studentno from  result  where examdate in
      (select   max(examdate) from  result where  subjectno =(select subjectId from  subject where subjectName  ='java'))
      and gid = (select gradeId from  subject where subjectName  ='java')   );
# 查询Java课程最后一次考试未参加的在读学生信息
# 查询Java课程最后一次考试的在读学生信息
# 查询Java课程最后一次考试的未在读学生信息
# 用子查询
# 查询Java
select  subjectId  from  subject where  subjectName='java';
#查询Java 是大几的课程
select  gradeId  from  subject where  subjectName='java';
select gradeName from  grade  where  gradeId = ( select  gradeId  from  subject where  subjectName='java');
# 查询Java考试最后一次时间
select  max(examdate) from  result where  subjectno in (select  subjectId  from  subject where  subjectName='java');
# 查询Java考试最后一次时间都是有谁参加
select studentno from result where examdate=(select  max(examdate) from  result where  subjectno in (select  subjectId  from  subject where  subjectName='java'));

# 查询Java课程最后一次考试未参加的在读学生信息
select * from student where studentNO not in(select studentno from result where examdate=(select  max(examdate) from  result where  subjectno in (select  subjectId  from  subject where  subjectName='java'))) and gId =(select  gradeId  from  subject where  subjectName='java');
# 查询Java课程最后一次考试的在读学生信息
select * from student where studentNO  in(select studentno from result where examdate=(select  max(examdate) from  result where  subjectno in (select  subjectId  from  subject where  subjectName='java'))) and gId =(select  gradeId  from  subject where  subjectName='java');
# 查询Java课程最后一次考试的未在读学生信息
select * from student where studentNO  in(select studentno from result where examdate=(select  max(examdate) from  result where  subjectno in (select  subjectId  from  subject where  subjectName='java'))) and gId !=(select  gradeId  from  subject where  subjectName='java');

# 查询Java课程最后一次考试未参加的在读学生信息
# 查询Java课程最后一次考试的在读学生信息
# 查询Java课程最后一次考试的未在读学生信息
# 用连表查询
# 查询Java课程最后一次考试未参加的在读学生信息
# 好像只能查指一条，其他数据不行
select st.* from  student st
                       left join result   re  on st.studentNO = re.studentno
                       left join subject  su  on su.subjectId   = re.subjectno
                       where st.gId = 1
                       and  re.examdate is null;
# 查询Java课程最后一次考试的在读学生信息
select * from  student st   join result   re  on st.studentNO = re.studentno
                               join subject  su  on su.gradeId   = st.gId
                               where  su.subjectName='java'
                               and   re.examdate = (select max(examdate) from  result );
# 查询Java课程最后一次考试的未在读学生信息
select * from   result   re join student st  on st.studentNO = re.studentno
                            join subject  su  on su.subjectId   = re.subjectno
                            where  su.subjectName='java'
                            and  st.gId  !=1
                            and   re.examdate = (select max(examdate) from  result );