最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一
MySQL实战文章目录
MySQL必会知识点梳理 (必看)【介绍】什么是数据库MySQL数据库RDBMS 术语数据库表的存储位置
【数据类型】一. 整数类型二. 小数类型三. 字符串四. 日期类型五. 复合类型六. 二进制类型
【约束】一. 非空约束(not null)二. 唯一性约束(unique)三. 主键约束(primary key) PK四. 外键约束(foreign key) FK五. 默认值约束 (Default)六. 自增约束(AUTO_INCREMENT)
【常用命令】登录数据库相关命令一. 启动服务二. 关闭服务三. 链接MySQL四. 退出数据库
DDL（Data Definition Languages）语句：即数据库定义语句数据库相关 *** 作一. 创建数据库二. 查看已经存在的数据库三. 选择数据库四. 查看数据库中的表五. 删除数据库六. 设置表的类型1. 查看mysql所支持的引擎类型2. 查看默认引擎
数据库表相关 *** 作一. 创建表二. 查看表定义三. 删除表四. 修改表 (重要)1. 修改表类型2. 增加表字段3. 删除表字段4. 字段改名5. 修改字段排列顺序6.表名修改
DML（Data Manipulation Language）语句：即数据 *** 纵语句一. 添加数据：INSERT1. 插入单条数据（常用）2. 插入单条数据3. 插入多条数据4. 表数据复制
二. 更新数据：UPDATE1. 修改一条数据的某个字段2. 修改多个字段为同一的值3. 使用case when实现批量更新
三. 删除数据：DELETE1. 删除指定数据2. 删除表中全部数据
DQL（Data Query Language）语句：即数据查询语句一. wherer 条件语句二. as 取别名三. distinct 去除重复记录四. group by 分组1. 单个字段分组2. 多个字段分组
五. having 过滤六. order by 排序1. 一个字段，降序（从大到小）2. 多个字段
limit 分页子查询1. in 关键字子查询exists 关键字子查询
多表查询1. 笛卡尔积查询2. 内连接查询3. 左外连接查询4. 右外连接查询
DCL（Data Control Language）语句：即数据控制语句关键字查看用户权限GRANT语句格式语法权限类型(priv_type)授予数据库权限类型
授予表权限类型授予列(字段)权限类型授予创建和删除用户的权限
ON对象类型(object_type)权限级别(priv_level)被授权的用户(user)身份验证方式(auth_option)加密连接(tls_option)用户资源限制(resource_option)权限生效
REVOKE语句语法格式
TCL（Transaction Control Language）语句：事务控制语句事务的使用步骤开启事务提交事务回滚事务查看当前的事务隔离级别设置当前连接事务的隔离级别设置数据库系统的全局的隔离级别
【常用函数】一. 数学函数1. ABS(number)2. PI()3. SQRT(x)4. MOD(x,y)5. CEIL(x)、CEILING(x)6. FLOOR(x)7. FLOOR(x)8. ROUND(x)、ROUND(x,y)9. POW(x,y)和、POWER(x,y)10. EXP(x)11. LOG(x)12. LOG10(x)13. RADIANS(x)14. DEGREES(x)15. SIN(x)、ASIN(x)16. COS(x)、ACOS(x)17. TAN(x)、ATAN(x)18. COT(x)
二. 字符串函数1. CHAR_LENGTH(str)2. CONCAT(s1,s2，...)3. CONCAT_WS(x,s1,s2,...)4. INSERT(s1,x,len,s2)5. LOWER(str)和LCASE(str)、UPPER(str)和UCASE(str)6. LEFT(s,n)、RIGHT(s,n)7. LPAD(s1,len,s2)、RPAD(s1,len,s2)8. LTRIM(s)、RTRIM(s)9. TRIM(s)10. TRIM(s1 FROM s)11. REPEAT(s,n)12. SPACE(n)13. REPLACE(s,s1,s2)14. STRCMP(s1,s2)15. SUBSTRING(s,n,len)、MID(s,n,len)16. LOCATE(str1,str)、POSITION(str1 IN str)、INSTR(str,str1)17. REVERSE(s)18. ELT(N,str1,str2,str3,str4,...)
三. 日期和时间函数1. CURDATE()、CURRENT_DATE()2. CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()3. UNIX_TIMESTAMP()、UNIX_TIMESTAMP(date)4. FROM_UNIXTIME(date)5. UTC_DATE()和UTC_TIME()6. MONTH(date)和MONTHNAME(date)7. DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)8. WEEK(d)9. DAYOFYEAR(d)、DAYOFMONTH(d)10. YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)11. EXTRACE(type FROM date)12. TIME_TO_SEC(time)13. SEC_TO_TIME()14. DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)15. DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)16. ADDTIME(date,expr)、SUBTIME(date,expr)
四. 条件判断函数1. IF(expr,v1,v2)2. IFNULL(v1,v2)3. CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END
五. 系统信息函数1. VERSION()2. CONNECTION_ID()3. USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()4. CHARSET(str)5. COLLATION()
六. 加密函数1. PASSWORD(str)2. MD5(str)3. ENCODE(str, pswd_str)4. DECODE(crypt_str,pswd_str)
七. 其他函数1. FORMAT(x,n)2. CONV(N,from_base,to_base)3. INET_ATON(expr)4. INET_NTOA(expr)5. BENCHMARK(count,expr)6. CONVERT(str USING charset)
【SQL实战练习】创建数据库创建表（并初始化数据）表结构习题1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数2. 查询"01"课程比"02"课程成绩低的学生的信息及课程分数3. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩4. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩5. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩6. 查询"流"姓老师的数量7. 查询学过"流浪法师"老师授课的同学的信息8. 查询没学过"张三"老师授课的同学的信息9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息11. 查询没有学全所有课程的同学的信息12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息14. 查询没学过"邪恶小法师"老师讲授的任一门课程的学生姓名15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩16. 检索"01"课程分数小于60，按分数降序排列的学生信息17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩18. 查询各科成绩最高分、最低分和平均分：19. 按各科成绩进行排序，并显示排名(实现不完全)20. 查询学生的总成绩并进行排名21. 查询不同老师所教不同课程平均分从高到低显示22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩23. 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比24. 查询学生平均成绩及其名次25. 查询各科成绩前三名的记录26. 查询每门课程被选修的学生数27. 查询出只有两门课程的全部学生的学号和姓名28. 查询男生、女生人数29. 查询名字中含有"德"字的学生信息30. 查询同名同性学生名单，并统计同名人数31. 查询1990年出生的学生名单32. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列33. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩34. 查询课程名称为"数学"，且分数低于60的学生姓名和分数35. 查询所有学生的课程及分数情况36. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数37. 查询不及格的课程38. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名39. 求每门课程的学生人数40. 查询选修"死亡歌颂者"老师所授课程的学生中，成绩最高的学生信息及其成绩41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩42. 查询每门功成绩最好的前两名43. 统计每门课程的学生选修人数（超过5人的课程才统计）44. 检索至少选修两门课程的学生学号45. 查询选修了全部课程的学生信息46. 查询各学生的年龄47. 查询本周过生日的学生48. 查询下周过生日的学生49. 查询本月过生日的学生50. 查询下月过生日的学生
【阿里巴巴开发手册】
MySQL必会知识点梳理 (必看) MySQL面试题链接：精品MySQL面试题，备战八月99%必问！过不了面试算我的
评论区评论要资料三个字即可获得MySQL全套资料 ！
【介绍】 什么是数据库 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。 MySQL数据库
MySQL 是开源的，目前隶属于 Oracle 旗下产品。MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL 使用标准的 SQL 数据语言形式。MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。 RDBMS 术语MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
数据库: 数据库是一些关联表的集合。数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。外键：外键用于关联两个表。复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语
数据库表的存储位置MySQL 为关系型数据库(Relational Database Management System), 这种所谓的
关系型可以理解为表格的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格
包括表文件、数据文件以及数据库的选项文件位置：MySQL安装目录\data下存放数据表。目录名对应数据库名，该目录下文件名对应数据表MySQL数据表以文件方式存放在磁盘中：
注：
InnoDB类型数据表只有一个*. frm文件，以及上一级目录的ibdata1文件
MylSAM类型数据表对应三个文件：
存储位置：因 *** 作系统而异，可查my.ini
【数据类型】 MySQL提供的数据类型包括数值类型（整数类型和小数类型）、字符串类型、日期类型、复合类型（复合类型包括enum类型和set类型）以及二进制类型 。 一. 整数类型 整数类型的数，默认情况下既可以表示正整数又可以表示负整数（此时称为有符号数）。如果只希望表示零和正整数，可以使用无符号关键字“unsigned”对整数类型进行修饰。各个类别存储空间及取值范围。 二. 小数类型
decimal(length, precision)用于表示精度确定（小数点后数字的位数确定）的小数类型，length决定了该小数的最大位数，precision用于设置精度（小数点后数字的位数）。
例如： decimal (5,2)表示小数取值范围：999.99～999.99 decimal (5,0)表示： -99999～99999的整数。
各个类别存储空间及取值范围。
三. 字符串 char()与varchar(): 例如对于简体中文字符集gbk的字符串而言，varchar(255)表示可以存储255个汉字，而每个汉字占用两个字节的存储空间。假如这个字符串没有那么多汉字，例如仅仅包含一个‘中’字，那么varchar(255)仅仅占用1个字符（两个字节）的储存空间；而char(255)则必须占用255个字符长度的存储空间，哪怕里面只存储一个汉字。各个类别存储空间及取值范围。 四. 日期类型 date表示日期，默认格式为‘YYYY-MM-DD’； time表示时间，格式为‘HH:ii:ss’； year表示年份； datetime与timestamp是日期和时间的混合类型，格式为’YYYY-MM-DD HH:ii:ss’。datetime与timestamp都是日期和时间的混合类型，区别在于： 表示的取值范围不同，datetime的取值范围远远大于timestamp的取值范围。 将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。 同一个timestamp类型的日期或时间，不同的时区，显示结果不同。各个类别存储空间及取值范围。 五. 复合类型 MySQL 支持两种复合数据类型：enum枚举类型和set集合类型。 enum类型的字段类似于单选按钮的功能，一个enum类型的数据最多可以包含65535个元素。 set 类型的字段类似于复选框的功能，一个set类型的数据最多可以包含64个元素。 六. 二进制类型 二进制类型的字段主要用于存储由‘0’和‘1’组成的字符串，因此从某种意义上将，二进制类型的数据是一种特殊格式的字符串。二进制类型与字符串类型的区别在于：字符串类型的数据按字符为单位进行存储，因此存在多种字符集、多种字符序；而二进制类型的数据按字节为单位进行存储，仅存在二进制字符集binary。
【约束】 约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。下面文章就来给大家介绍一下6种mysql常见的约束，希望对大家有所帮助。 一. 非空约束(not null)
非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
Null类型特征：所有的类型的值都可以是null，包括int、float 等数据类型
二. 唯一性约束(unique) 唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。唯一约束不允许出现重复的值，但是可以为多个null。同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。 三. 主键约束(primary key) PK主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
四. 外键约束(foreign key) FK外键约束是用来加强两个表（主表和从表）的一列或多列数据之间的连接的，可以保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
创建外键约束的顺序是先定义主表的主键，然后定义从表的外键。也就是说只有主表的主键才能被从表用来作为外键使用，被约束的从表中的列可以不是主键，主表限制了从表更新和插入的 *** 作。
自增约束(AUTO_INCREMENT)可以约束任何一个字段，该字段不一定是PRIMARY KEY字段，也就是说自增的字段并不等于主键字段。
但是PRIMARY_KEY约束的主键字段，一定是自增字段，即PRIMARY_KEY 要与AUTO_INCREMENT一起作用于同一个字段。
当插入第一条记录时，自增字段没有给定一个具体值，可以写成DEFAULT/NULL，那么以后插入字段的时候，该自增字段就是从1开始，没插入一条记录，该自增字段的值增加1。当插入第一条记录时，给自增字段一个具体值，那么以后插入的记录在此自增字段上的值，就在第一条记录该自增字段的值的基础上每次增加1。也可以在插入记录的时候，不指定自增字段，而是指定其余字段进行插入记录的 *** 作。
【常用命令】 登录数据库相关命令 一. 启动服务
语法：
mysql> net stop mysql
语法：
mysql> net start mysql
mysql -u用户名 -p密码;
root@243ecf24bd0a:/ mysql -uroot -p123456;
四. 退出数据库 语法：
quit
mysql> quit
DDL（Data Definition Languages）语句：即数据库定义语句
创建对象：CREATE 对象名称;删除对象：DROP 对象名称;修改对象：ALTER 对象名称; 数据库相关 *** 作 一. 创建数据库 语法：对于数据库而言实际上每一张表都表示是一个数据库的对象，而数据库对象指的就是DDL定义的所有 *** 作，例如：表，视图，索引，序列，约束等等，都属于对象的 *** 作，所以表的建立就是对象的建立，而对象的 *** 作主要分为以下三类语法
create database 数据库名字;
mysql> create database sqltest;
二. 查看已经存在的数据库 语法：
show databases;
mysql> show databases;
可以发现，在上面的列表中除了刚刚创建的 mzc-test，sqltest，外，还有另外 4 个数据库，它们都是安装MySQL 时系统自动创建的，其各自功能如下。
information_schema：主要存储了系统中的一些数据库对象信息。比如用户表信息、列信息、权限信息、字符集信息、分区信息等。cluster：存储了系统的集群信息。mysql：存储了系统的用户权限信息。test：系统自动创建的测试数据库，任何用户都可以使用。 三. 选择数据库 语法：use 数据库名;
mysql> use mzc-test;
Database changed代表我们已经选择 sqltest 数据库，后续所有 *** 作将在 sqltest 数据库上执行。有些人可能会问到，连接以后怎么退出。其实，不用退出来，use 数据库后，使用show databases就能查询所有数据库，如果想跳到其他数据库，用use 其他数据库名字。 四. 查看数据库中的表 语法：show
tables;
mysql> show tables;
五. 删除数据库 语法：
drop database 数据库名称;
mysql> drop database mzc-test;
注意：删除时，最好用 `` 符号把表明括起来 六. 设置表的类型 MySQL的数据表类型：
MyISAM、InnoDB、HEAP、 BOB、CSV等
语法：
CREATE TABLE 表名(
#省略代码
）ENGINE= InnoDB;
适用场景：
1. 使用MyISAM：节约空间及响应速度快；不需事务，空间小，以查询访问为主
2. 使用InnoDB：安全性，事务处理及多用户 *** 作数据表；多删除、更新 *** 作，安全性高，事务处理及并发控制
语法：
SHOW ENGINES
结果：
2. 查看默认引擎语法：
SHOW VARIABLES LIKE 'storage_engine';
结果：
数据库表相关 *** 作 一. 创建表
语法：create table 表名 {列名,数据类型,约束条件};
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
注意：表名还请遵守数据库的命名规则，这条数据后面要进行删除，所以首字母为大写。
二. 查看表定义 语法：desc 表名
mysql> desc Student;
虽然 desc 命令可以查看表定义，但是其输出的信息还是不够全面，为了查看更全面的表定义信息，有时就需要通过查看创建表的 SQL 语句来得到，可以使用如下命令实现语法：
show create table 表名 \G;
mysql> show create table Student \G;
从上面表的创建 SQL 语句中，除了可以看到表定义以外，还可以看到表的engine（存储引擎）和charset（字符集）等信息。
\G选项的含义是使得记录能够按照字段竖着排列，对于内容比较长的记录更易于显示。
三. 删除表
语法：drop table 表名
mysql> drop table Student;
ALTER TABLE 表名 MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]例如，修改表 student 的 s_name 字段定义，将 varchar(20)改为 varchar(30)
mysql> alter table Student modify s_name varchar(30);
2. 增加表字段 语法：
ALTER TABLE 表名 ADD [COLUMN] [FIRST | AFTER col_name];例如，表 student 上新增加字段 s_test，类型为 int(3)
mysql> alter table student add column s_test int(3);
3. 删除表字段 语法：
ALTER TABLE 表名 DROP [COLUMN] col_name例如，将字段 s_test 删除掉
mysql> alter table Student drop column s_test;
4. 字段改名 语法：
ALTER TABLE 表名 CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]例如，将 s_sex 改名为 s_sex1，同时修改字段类型为 int(4)
mysql> alter table Student change s_sex s_sex1 int(4);
注意：change 和 modify 都可以修改表的定义，不同的是 change 后面需要写两次列名，不方便。但是 change 的优点是可以修改列名称，modify 则不能。
5. 修改字段排列顺序前面介绍的的字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项first|after column_name，这个选项可以用来修改字段在表中的位置，默认 ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默认都不会改变字段的位置。
例如，将新增的字段 s_test 加在 s_id 之后
语法：alter table 表名 add 列名 数据类型 after 列名;
mysql> alter table Student add s_test date after s_id;
修改已有字段 s_name，将它放在最前面
mysql> alter table Student modify s_name varchar(30) default '' first;
注意：CHANGE/FIRST|AFTER COLUMN 这些关键字都属于 MySQL 在标准 SQL 上的扩展，在其他数据库上不一定适用。
6.表名修改 语法：ALTER TABLE 表名 RENAME [TO] new_tablename例如，将表 Student 改名为 student
mysql> alter table Student rename student;
DML（Data Manipulation Language）语句：即数据 *** 纵语句 用于 *** 作数据库对象中所包含的数据 一. 添加数据：INSERT
Insert 语句用于向数据库中插入数据
1. 插入单条数据（常用）语法：insert into 表名(列名1,列名2,...) values(值1,值2,...)
特点：
插入值的类型要与列的类型一致或兼容。插入NULL可实现为列插入NULL值。列的顺序可以调换。列数和值的个数必须一致。可省略列名，默认所有列，并且列的顺序和表中列的顺序一致。案例：
-- 插入学生表测试数据
insert into Student(s_id,s_name,s_birth,s_sex) values('01' , '赵信' , '1990-01-01' , '男');
语法：INSERT INTO 表名 SET 列名 = 值，列名 = 值
案例：
INSERT INTO student SET s_id='02',s_name='德莱厄斯',s_birth='1990-01-01',s_sex='男'
语法：insert into 表名 values(值1,值2,值3),(值4,值5,值6),(值7,值8,值9);
案例：
INSERT INTO student VALUES('03','艾希','1990-01-01','女'),('04','德莱文','1990-08-06','男'),('05','俄洛依','1991-12-01','女');
上面的例子中，值1,值2,值3),(值4,值5,值6),(值7,值8,值9) 即为 Value List，其中每个括号内部的数据表示一行数据，这个例子中插入了三行数据。Insert 语句也可以只给部分列插入数据，这种情况下，需要在 Value List 之前加上 ColumnName List，
例如：
INSERT INTO student(s_name,s_sex) VALUES('艾希','女'),('德莱文','男');
语法：INSERT INTO 表名 SELECT * from 表名;
案例：
INSERT INTO student SELECT * from student1;
注意：
两个表的字段需要一直，并尽量保证要新增的表中没有数据 二. 更新数据：UPDATEUpdate 语句一共有两种语法，分别用于更新单表数据和多表数据。
注意：没有 WHERE 条件的 UPDATE 会更新所有值！
1. 修改一条数据的某个字段
语法：UPDATE 表名 SET 字段名 =值 where 字段名=值
案例：
UPDATE student SET s_name ='张三' WHERE s_id ='01'
语法：UPDATE 表名 SET 字段名= 值 WHERE 字段名 in ('值1','值2','值3');
案例：
UPDATE student SET s_name = '李四' WHERE s_id in ('01','02','03');
语法：update 表名 set 字段名 = case 字段名 when 值1 then '值' when 值2 then '值' when 值3 then '值' end where s_id in (值1,值2,值3)
案例：
update student set s_name = case s_id when 01 then '小王' when 02 then '小周' when 03 then '老周' end where s_id in (01,02,03)
案例 2：
UPDATE student SET s_birth = CASE s_name
WHEN '小王' THEN
'2019-01-20'
WHEN '小周' THEN
'2019-01-22'
END WHERE s_name IN ('小王','小周');
语法：DELETE FROM 表名 WHERE 列名=值
案例：
DELETE FROM student WHERE s_id='09'
与 SELECT 语句不同的是，DELETE 语句中不能使用 GROUP BY、 HAVING 和 ORDER BY 三类子句，而只能使用WHERE 子句。原因很简单， GROUP BY 和 HAVING 是从表中选取数据时用来改变抽取数据形式的， 而 ORDER BY 是用来指定取得结果显示顺序的。因此，在删除表中数据 时它们都起不到什么作用。`
语法：TRUNCATE 表名;
注意：全部删除，内存无痕迹，如果有自增会重新开始编号。
与 DELETE 不同的是，TRUNCATE 只能删除表中的全部数据，而不能通过 WHERE 子句指定条件来删除部分数据。也正是因为它不能具体地控制删除对象， 所以其处理速度比 DELETE 要快得多。实际上，DELETE 语句在 DML 语句中也 属于处理时间比较长的，因此需要删除全部数据行时，使用 TRUNCATE 可以缩短 执行时间。
案例：
TRUNCATE student1;
DQL（Data Query Language）语句：即数据查询语句 查询数据库中的记录，关键字 SELECT，这块内容非常重要！ 一. wherer 条件语句
语法：select 列名 from 表名 where 列名 =值
where的作用：
用于检索数据表中符合条件的记录搜索条件可由一个或多个逻辑表达式组成，结果一般为真或假搜索条件的组成：
算数运算符 逻辑 *** 作符（ *** 作符有两种写法）比较运算符
注意：数值数据类型的记录之间才能进行算术运算，相同数据类型的数据之间才能进行比较。
表数据
案例 1（AND）：
SELECT * FROM student WHERE s_name ='小王' AND s_sex='男'
案例 2（OR）：
SELECT * FROM student WHERE s_name ='崔丝塔娜' OR s_sex='男'
案例 3（NOT）：
SELECT * FROM student WHERE NOT s_name ='崔丝塔娜'
案例 4（IS NULL）：
SELECT * FROM student WHERE s_name IS NULL;
案例 5（IS NOT NULL）：
SELECT * FROM student WHERE s_name IS NOT NULL;
案例 6（BETWEEN）：
SELECT * FROM student WHERE s_birth BETWEEN '2019-01-20' AND '2019-01-22'
案例 7（LINK）：
SELECT * FROM student WHERE s_name LIKE '小%'
案例 8（IN）：
SELECT * FROM student WHERE s_name IN ('小王','小周')
案例：
SELECT s_name as `name` FROM student
案例
SELECT DISTINCT * FROM student
指定字段
星号表示所有字段手动指定需要查询的字段SELECT DISTINCT s_name,s_birth FROM student
语法：
select 字段名 from 表名 group by 字段名称;
SELECT COUNT(*)FROM student GROUP BY s_sex;
SELECT s_name,s_sex,COUNT(*) FROM student GROUP BY s_name,s_sex;
我们如果要查询男生或者女生，人数大于4的性别
SELECT s_sex as 性别,count(s_id) AS 人数 FROM student GROUP BY s_sex HAVING COUNT(s_id)>4
语法：
select * from 表名 order by 字段名;
SELECT * FROM student ORDER BY s_id DESC;
SELECT * FROM student ORDER BY s_id DESC, s_birth ASC;
语法1:
select * from table_name limit 个数;
语法2:
select * from table_name limit 起始位置,个数;
案例：
查询前三条数据SELECT * FROM student LIMIT 3;
SELECT * FROM student LIMIT 2,3;
注意：起始位置 从0开始
每一页显示的条数 a = 3明确当前页数 b = 2计算起始位置 c = (b-1) * a 子查询 将一个查询语句的结果作为另一个查询语句的条件或是数据来源， 当我们一次性查不到想要数据时就需要使用子查询。经典的使用场景:分页显示
SELECT
*
FROM
score
WHERE
s_id =(
SELECT
s_id
FROM
student
WHERE
s_name = '赵信')
子查询的思路:
要分析 查到最终的数据 到底有哪些步骤根据步骤写出对应的sql语句把上一个步骤的sql语句丢到下一个sql语句中作为条件SELECT
*
FROM
score
WHERE
s_id IN (
SELECT
s_id
FROM
student
WHERE
s_sex = '男')
语法:
select * from 表1 inner join 表2;
在外连接查询中不能使用where 关键字 必须使用on专门来做表的对应关系
4. 右外连接查询
右边的表无论是否能够匹配都要完整显示，左边的仅展示匹配上的记录
DCL（Data Control Language）语句：即数据控制语句 DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。 关键字 GRANTREVOKE 查看用户权限
当成功创建用户账户后，还不能执行任何 *** 作，需要为该用户分配适当的访问权限。可以使用SHOW GRANTS FOR语句来查询用户的权限。
例如：
mysql> SHOW GRANTS FOR test;
+-------------------------------------------+
| Grants for test@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [auth_option] [, user [auth_option]] ...
[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
[WITH {GRANT OPTION | resource_option} ...]
GRANT PROXY ON user
TO user [, user] ...
[WITH GRANT OPTION]
object_type: {
TABLE
| FUNCTION
| PROCEDURE
}
priv_level: {
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
user:
(see Section 6.2.4, “Specifying Account Names”)
auth_option: {
IDENTIFIED BY 'auth_string'
| IDENTIFIED WITH auth_plugin
| IDENTIFIED WITH auth_plugin BY 'auth_string'
| IDENTIFIED WITH auth_plugin AS 'auth_string'
| IDENTIFIED BY PASSWORD 'auth_string'
}
tls_option: {
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'
}
resource_option: {
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
}
授予数据库权限时，priv_type可以指定为以下值：
授予表权限时，priv_type可以指定为以下值：
priv_type的值只能指定为SELECT、INSERT和UPDATE，同时权限的后面需要加上列名列表(column-list)。
授予创建和删除用户的权限
授予列(字段)权限时，priv_type的值指定为CREATE USER权限，具备创建用户、删除用户、重命名用户和撤消所有特权，而且是全局的。
ON
有ON，是授予权限，无ON，是授予角色。如：
-- 授予数据库db1的所有权限给指定账户
GRANT ALL ON db1.* TO 'user1'@'localhost';
-- 授予角色给指定的账户
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
指定权限级别的值有以下几类格式：
*：表示当前数据库中的所有表。.：表示所有数据库中的所有表。db_name.*：表示某个数据库中的所有表，db_name指定数据库名。db_name.tbl_name：表示某个数据库中的某个表或视图，db_name指定数据库名，tbl_name指定表名或视图名。tbl_name：表示某个表或视图，tbl_name指定表名或视图名。db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name指定存储过程名或函数名。 被授权的用户(user)'user_name'@'host_name'
user表中host列的默认值
| host | 说明 |
|---|---|
| % | 匹配所有主机 |
| localhost | localhost不会被解析成IP地址，直接通过UNIXsocket连接 |
| 127.0.0.1 | 会通过TCP/IP协议连接，并且只能在本机访问 |
| ::1 | ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1 |
host_name格式有以下几种：
使用%模糊匹配，符合匹配条件的主机可以访问该数据库实例，例如192.168.2.%或%.test.com；使用localhost、127.0.0.1、::1及服务器名等，只能在本机访问；使用ip地址或地址段形式，仅允许该ip或ip地址段的主机访问该数据库实例，例如192.168.2.1或192.168.2.0/24或192.168.2.0/255.255.255.0；省略即默认为%。 身份验证方式(auth_option) auth_option为可选字段，可以指定密码以及认证插件(mysql_native_password、sha256_password、caching_sha2_password)。 加密连接(tls_option) tls_option为可选的，一般是用来加密连接。 用户资源限制(resource_option) resource_option为可选的，一般是用来指定最大连接数等。| 参数 | 说明 |
|---|---|
| MAX_QUERIES_PER_HOUR count | 每小时最大查询数 |
| MAX_UPDATES_PER_HOUR count | 每小时最大更新数 |
| MAX_CONNECTIONS_PER_HOUR count | 每小时连接次数 |
| MAX_USER_CONNECTIONS count | 用户最大连接数 |
FLUSH PRIVILEGES;
REVOKE语法和GRANT语句的语法格式相似，但具有相反的效果
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM user [, user] ...
REVOKE ALL [PRIVILEGES], GRANT OPTION
FROM user [, user] ...
REVOKE PROXY ON user
FROM user [, user] ...
TCL（Transaction Control Language）语句：事务控制语句
什么是事物？
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行事务的ACID属性
原子性：事务是一个不可分割的工作单位，事务中的 *** 作要么都发生，要么都不发生
一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态
隔离性：一个事务的执行不能被其他事务干扰，即一个事务内部的 *** 作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性：一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他 *** 作和数据库故障不应该对其有任何影响
分类
隐式事务：事务没有明显的开启和结束的标记(比如insert，update，delete语句)
显式事务：事务具有明显的开启和结束的标记(autocommit变量设置为0)
事务的使用步骤 开启事务 默认开启事务SET autocommit = 0 ;
COMMIT;
ROLLBACK ;
select @@tx_isolation;
set session transaction isolation level read uncommitted;
set global transaction isolation level read committed ;
【常用函数】 MySQL提供了众多功能强大、方便易用的函数，使用这些函数，可以极大地提高用户对于数据库的管理效率，从而更加灵活地满足不同用户的需求。本文将MySQL的函数分类并汇总，以便以后用到的时候可以随时查看。
(这里使用 Navicat Premium 15 工具进行演示)
因为内容太多了这里只演示一些常用的
一. 数学函数对数值型的数据进行指定的数学运算，如abs()函数可以获得给定数值的绝对值，round()函数可以对给定的数值进行四舍五入。
1. ABS(number) 作用：返回 number 的绝对值SELECT
ABS(s_score)
FROM
score;
ABS(-86) 返回：86
number 参数可以是任意有效的数值表达式。如果 number 包含 Null，则返回 Null；如果是未初始化变量，则返回 0。
2. PI()例1：pi() 返回：3.141592653589793
例2：pi(2) 返回：6.283185307179586
作用：计算圆周率及其倍数
3. SQRT(x) 作用：返回非负数的x的二次方根 4. MOD(x,y) 作用：返回x被y除后的余数 5. CEIL(x)、CEILING(x) 作用：返回不小于x的最小整数 6. FLOOR(x) 作用：返回不大于x的最大整数 7. FLOOR(x) 作用：返回不大于x的最大整数 8. ROUND(x)、ROUND(x,y) 作用：前者返回最接近于x的整数，即对x进行四舍五入；后者返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位SELECT ROUND(345222.9)
nExp个位数 =0：表示当前小数点的位置
9. POW(x,y)和、POWER(x,y) 作用：返回x的y次乘方的值 10. EXP(x) 作用：返回e的x乘方后的值 11. LOG(x) 作用：返回x的自然对数，x相对于基数e的对数 12. LOG10(x) 作用：返回x的基数为10的对数 13. RADIANS(x) 作用：返回x由角度转化为弧度的值 14. DEGREES(x) 作用：返回x由弧度转化为角度的值 15. SIN(x)、ASIN(x) 作用：前者返回x的正弦，其中x为给定的弧度值；后者返回x的反正弦值，x为正弦 16. COS(x)、ACOS(x) 作用：前者返回x的余弦，其中x为给定的弧度值；后者返回x的反余弦值，x为余弦 17. TAN(x)、ATAN(x) 作用：前者返回x的正切，其中x为给定的弧度值；后者返回x的反正切值，x为正切 18. COT(x) 作用：返回给定弧度值x的余切 二. 字符串函数 1. CHAR_LENGTH(str) 作用：计算字符串字符个数SELECT CHAR_LENGTH('这是一个十二个字的字符串');
SELECT CONCAT('拼接','测试');
SELECT CONCAT_WS('-','测试','拼接','WS')
SELECT INSERT('测试字符串替换',2,1,'牛');
SELECT LOWER('JHGYTUGHJGG'),LCASE('HKJHKJHKJHKJ');
SELECT UPPER('aaaaaa'),UCASE('vvvvv');
SELECT LEFT('左边开始',2),RIGHT('右边开始',2);
SELECT LEFT('左边开始',2),RIGHT('右边开始',2);
SELECT LTRIM(' 左边开始'),RTRIM(' 右边开始 ');
SELECT TRIM(' 是是 ');
SELECT REPEAT('测试',5);
SELECT SPACE(20);
SELECT STRCMP('我我我','我我我');
SELECT STRCMP('我我我','是是是');
SELECT SUBSTRING('测试测试',2,2);
SELECT MID('测试测试',2,2);
SELECT LOCATE('字','获取字符串的位置');
SELECT REVERSE('字符串反转');
SELECT ELT(2,'字符串反转','sssss');
当前时间
SELECT CURRENT_TIMESTAMP()
SELECT LOCALTIME()
SELECT NOW()
SELECT SYSDATE()
SELECT UNIX_TIMESTAMP()
SELECT UTC_DATE()
SELECT UTC_TIME()
SELECT MONTH(NOW())
SELECT MONTHNAME(NOW())
SELECT WEEK(NOW())
SELECT DAYOFYEAR(NOW())
SELECT DAYOFMONTH(NOW())
1970~2069；QUARTER(date)返回date对应一年中的季度，范围是1~4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值
SELECT YEAR(NOW())
SELECT QUARTER(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
SELECT TIME_TO_SEC(NOW())
SELECT SEC_TO_TIME(530)
SELECT VERSION()
SELECT CONNECTION_ID()
SELECT USER()
SELECT CURRENT_USER()
SELECT SYSTEM_USER()
SELECT SESSION_USER()
SELECT CHARSET(555)
SELECT COLLATION('sssfddsfds')
SELECT PASSWORD('mima')
SELECT MD5('mima')
SELECT ENCODE('fdfdz','mima')
SELECT DECODE('fdfdz','mima')
SELECT FORMAT(446.454,2)
更多用法还请参考：http://www.geezn.com/documents/gez/help/117555-1355219868404378.html
【SQL实战练习】 题目来自互联网，建议每道题都在本地敲一遍巩固记忆 ！ 创建数据库 创建表（并初始化数据）-- 学生表
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
insert into student values('01' , '赵信' , '1990-01-01' , '男');
insert into student values('02' , '德莱厄斯' , '1990-12-21' , '男');
insert into student values('03' , '艾希' , '1990-05-20' , '男');
insert into student values('04' , '德莱文' , '1990-08-06' , '男');
insert into student values('05' , '俄洛依' , '1991-12-01' , '女');
insert into student values('06' , '光辉女郎' , '1992-03-01' , '女');
insert into student values('07' , '崔丝塔娜' , '1989-07-01' , '女');
insert into student values('08' , '安妮' , '1990-01-20' , '女');
-- 课程表测试数据
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
-- 教师表测试数据
insert into teacher values('01' , '死亡歌颂者');
insert into teacher values('02' , '流浪法师');
insert into teacher values('03' , '邪恶小法师');
-- 成绩表测试数据
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);
学生表（student）
s_id = 学生编号，s_name = 学生姓名，s_birth = 出生年月，s_sex = 学生性别
课程表（course）
c_id = 课程编号，c_name = 课程名称，t_id = 教师编号
教师表（teacher）
t_id = 教师编号，t_name = 教师姓名
成绩表（score）
s_id = 学生编号，c_id = 课程编号，s_score = 分数
习题
开始之前我们先来看看四张表中的数据。
SELECT
st.*,
sc.s_score AS '语文',
sc2.s_score '数学'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
SELECT
st.*,
s.s_score AS 数学,
s2.s_score AS 语文
FROM
student st
LEFT JOIN score s ON s.s_id = st.s_id
AND s.c_id = '01'
LEFT JOIN score s2 ON s2.s_id = st.s_id
AND s2.c_id = '02'
WHERE
s.s_score < s2.s_score
SELECT
st.s_id AS '学生编号',
st.s_name AS '学生姓名',
AVG( s.s_score ) AS avgScore
FROM
student st
LEFT JOIN score s ON st.s_id = s.s_id
GROUP BY
st.s_id
HAVING
avgScore >= 60
SELECT
st.s_id AS '学生编号',
st.s_name AS '学生姓名',(
CASE
WHEN ROUND( AVG( sc.s_score ), 2 ) IS NULL THEN
0 ELSE ROUND( AVG( sc.s_score ), 2 )
END
)
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
HAVING
AVG( sc.s_score )< 60
OR AVG( sc.s_score ) IS NULL
SELECT
st.s_id AS '学生编号',
st.s_name AS '学生姓名',
COUNT( sc.c_id ) AS '选课总数',
sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '总成绩'
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE '流%'
SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course cs ON cs.c_id = sc.c_id
LEFT JOIN teacher tc ON tc.t_id = cs.t_id
WHERE tc.t_name = '流浪法师'
-- 查询流浪法师教的课
SELECT
cs.*
FROM
course cs
LEFT JOIN teacher tc ON tc.t_id = cs.t_id
WHERE
tc.t_name = '流浪法师'
-- 查询有流浪法师课程成绩的学生id
SELECT
sc.s_id
FROM
score sc
WHERE
sc.c_id IN (
SELECT
cs.c_id
FROM
course cs
LEFT JOIN teacher tc ON tc.t_id = cs.t_id
WHERE
tc.t_name = '流浪法师')
-- 取反，查询没有学过流浪法师课程的同学信息
SELECT
st.*
FROM
student st
WHERE
st.s_id NOT IN (
SELECT
sc.s_id
FROM
score sc
WHERE
sc.c_id IN ( SELECT cs.c_id FROM course cs LEFT JOIN teacher tc ON tc.t_id = cs.t_id WHERE tc.t_name = '流浪法师' )
)
-- 查询学过编号为01课程的同学id
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND cs.c_id = '01';
-- 查询学过编号为02课程的同学id
SELECT
st2.s_id
FROM
student st2
INNER JOIN score sc2 ON sc2.s_id = st2.s_id
INNER JOIN course cs2 ON cs2.c_id = sc2.c_id
AND cs2.c_id = '02';
-- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT
st.*
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND sc.c_id = '01'
WHERE
st.s_id IN (
SELECT
st2.s_id
FROM
student st2
INNER JOIN score sc2 ON sc2.s_id = st2.s_id
INNER JOIN course cs2 ON cs2.c_id = sc2.c_id
AND cs2.c_id = '02'
);
SELECT
a.*
FROM
student a,
score b,
score c
WHERE
a.s_id = b.s_id
AND a.s_id = c.s_id
AND b.c_id = '01'
AND c.c_id = '02';
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND cs.c_id = '01'
WHERE
st.s_id NOT IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course cs ON cs.c_id = sc.c_id
AND cs.c_id = '02'
);
SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
WHERE
st.s_id IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '02'
WHERE
st.s_id
)
AND st.s_id IN (
SELECT
st.s_id
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '03'
WHERE
st.s_id
)
);
SELECT
a.*
FROM
student a
LEFT JOIN score b ON a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
COUNT( b.c_id ) != '3';
SELECT DISTINCT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
WHERE
sc.c_id IN ( SELECT sc2.c_id FROM student st2 LEFT JOIN score sc2 ON sc2.s_id = st2.s_id WHERE st2.s_id = '01' );
SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
GROUP_CONCAT( sc.c_id )=(
SELECT
GROUP_CONCAT( sc2.c_id )
FROM
student st2
LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
WHERE
st2.s_id = '01'
);
SELECT
*
FROM
student
WHERE
s_id NOT IN (
SELECT
sc.s_id
FROM
score sc
INNER JOIN course cs ON cs.c_id = sc.c_id
INNER JOIN teacher t ON t.t_id = cs.t_id
AND t.t_name = '邪恶小法师');
SELECT
st.s_id AS '学号',
st.s_name AS '姓名',
AVG( sc.s_score ) AS '平均成绩'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
WHERE
sc.s_id IN (
SELECT
sc.s_id
FROM
score sc
WHERE
sc.s_score < 60
OR sc.s_score IS NULL
GROUP BY
sc.s_id
HAVING
COUNT( 1 )>= 2
)
GROUP BY
st.s_id
SELECT
st.*
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
AND sc.s_score < '60'
ORDER BY
sc.s_score DESC;
SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
WHERE
sc.c_id = '01'
AND sc.s_score < '60'
ORDER BY
sc.s_score DESC;
SELECT
st.*,
AVG( sc4.s_score ) AS '平均分',
sc.s_score AS '语文',
sc2.s_score AS '数学',
sc3.s_score AS '英语'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
LEFT JOIN score sc3 ON sc3.s_id = st.s_id
AND sc3.c_id = '03'
LEFT JOIN score sc4 ON sc4.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
AVG( sc4.s_score ) DESC;
SELECT
st.*,
( CASE WHEN AVG( sc4.s_score ) IS NULL THEN 0 ELSE AVG( sc4.s_score ) END ) AS '平均分',
( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) AS '语文',
( CASE WHEN sc2.s_score IS NULL THEN 0 ELSE sc2.s_score END ) AS '数学',
( CASE WHEN sc3.s_score IS NULL THEN 0 ELSE sc3.s_score END ) AS '英语'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
LEFT JOIN score sc3 ON sc3.s_id = st.s_id
AND sc3.c_id = '03'
LEFT JOIN score sc4 ON sc4.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
AVG( sc4.s_score ) DESC;
SELECT
cs.c_id,
cs.c_name,
MAX( sc1.s_score ) AS '最高分',
MIN( sc2.s_score ) AS '最低分',
AVG( sc3.s_score ) AS '平均分',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 60
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS '及格率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 70
AND s_score < 80
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS '中等率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 80
AND s_score < 90
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS '优良率',
((
SELECT
COUNT( s_id )
FROM
score
WHERE
s_score >= 90
AND c_id = cs.c_id
)/(
SELECT
COUNT( s_id )
FROM
score
WHERE
c_id = cs.c_id
)) AS '优秀率'
FROM
course cs
LEFT JOIN score sc1 ON sc1.c_id = cs.c_id
LEFT JOIN score sc2 ON sc2.c_id = cs.c_id
LEFT JOIN score sc3 ON sc3.c_id = cs.c_id
GROUP BY
cs.c_id;
SELECT
c1.s_id,
c1.c_id,
c1.c_name,
@score := c1.s_score,
@i := @i + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = "01"
ORDER BY
sc.s_score DESC
) c1,
( SELECT @i := 0 ) a UNION ALL
SELECT
c2.s_id,
c2.c_id,
c2.c_name,
c2.s_score,
@ii := @ii + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = "02"
ORDER BY
sc.s_score DESC
) c2,
( SELECT @ii := 0 ) aa UNION ALL
SELECT
c3.s_id,
c3.c_id,
c3.c_name,
c3.s_score,
@iii := @iii + 1
FROM
(
SELECT
c.c_name,
sc.*
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
WHERE
c.c_id = "03"
ORDER BY
sc.s_score DESC
) c3;
SET @iii = 0;
SELECT
st.s_id,
st.s_name,
( CASE WHEN sum( sc.s_score ) IS NULL THEN 0 ELSE SUM( sc.s_score ) END )
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
ORDER BY
SUM( sc.s_score ) DESC
SELECT
t.t_id,
t.t_name,
AVG( sc.s_score )
FROM
teacher t
LEFT JOIN course c ON c.t_id = t.t_id
LEFT JOIN score sc ON sc.c_id = c.c_id
GROUP BY
t.t_id
ORDER BY
AVG( sc.s_score ) DESC
SELECT
a.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON sc.c_id = c.c_id
AND c.c_id = '01'
ORDER BY
sc.s_score DESC
LIMIT 1,
2
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '02'
ORDER BY
sc.s_score DESC
LIMIT 1,
2
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '03'
ORDER BY
sc.s_score DESC
LIMIT 1,
2
) c;
SELECT
c.c_id,
c.c_name,
(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
AND sc.s_score <= 100 AND sc.s_score > 80
)/(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
) AS '100-85',
((
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
AND sc.s_score <= 85 AND sc.s_score > 70
)/(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
)) AS '85-70',
((
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
AND sc.s_score <= 70 AND sc.s_score > 60
)/(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
)) AS '70-60',
((
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
AND sc.s_score <= 60 AND sc.s_score >= 0
)/(
SELECT
COUNT( 1 )
FROM
score sc
WHERE
sc.c_id = c.c_id
)) AS '85-70'
FROM
course c
ORDER BY
c.c_id
SET @i = 0;
SELECT
a.*,
@i := @i + 1
FROM
(
SELECT
st.s_id,
st.s_name,
round( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END, 2 ) AS agvScore
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
ORDER BY
agvScore DESC
) a
SELECT
a.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '01'
ORDER BY
sc.s_score DESC
LIMIT 0,
3
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '02'
ORDER BY
sc.s_score DESC
LIMIT 0,
3
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_id,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '03'
ORDER BY
sc.s_score DESC
LIMIT 0,
3
) c
SELECT
c.c_id,
c.c_name,
COUNT( 1 )
FROM
course c
LEFT JOIN score sc ON sc.c_id = c.c_id
INNER JOIN student st ON st.s_id = c.c_id
GROUP BY
c.c_id
SELECT
st.s_id,
st.s_name
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
GROUP BY
st.s_id
HAVING
COUNT( 1 ) = 2
SELECT s_sex, COUNT(1) FROM student GROUP BY s_sex
SELECT * FROM student WHERE s_name LIKE '%德%'
select st.s_name,st.s_sex,count(1) from student st group by st.s_name,st.s_sex having count(1)>1
SELECT st.* FROM student st WHERE st.s_birth LIKE '1990%';
SELECT
c.c_id,
c_name,
AVG( sc.s_score ) AS scoreAvg
FROM
course c
INNER JOIN score sc ON sc.c_id = c.c_id
GROUP BY
c.c_id
ORDER BY
scoreAvg DESC,
c.c_id ASC;
SELECT
st.s_id,
st.s_name,
( CASE WHEN AVG( sc.s_score ) IS NULL THEN 0 ELSE AVG( sc.s_score ) END ) scoreAvg
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
scoreAvg > '85';
SELECT
*
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.s_score < 60
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_name = '数学';
SELECT
*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
ORDER BY
st.s_id,
c.c_name;
SELECT
st.s_id,st.s_name,c.c_name,sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
WHERE
st.s_id IN (
SELECT
st2.s_id
FROM
student st2
LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
GROUP BY
st2.s_id
HAVING
MIN( sc2.s_score )>= 70
ORDER BY
st2.s_id
)
SELECT
st.s_id,
c.c_name,
st.s_name,
sc.s_score
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.s_score < 60
INNER JOIN course c ON c.c_id = sc.c_id
SELECT
st.s_id,
st.s_name,
sc.s_score
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
AND sc.s_score >= 80;
SELECT
c.c_id,
c.c_name,
COUNT( 1 )
FROM
course c
INNER JOIN score sc ON sc.c_id = c.c_id
GROUP BY
c.c_id;
SELECT
st.*,
sc.s_score
FROM
student st
INNER JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
INNER JOIN teacher t ON t.t_id = c.t_id
AND t.t_name = '死亡歌颂者'
ORDER BY
sc.s_score DESC
LIMIT 0,1;
SELECT
st.s_id,
st.s_name,
sc.c_id,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
WHERE
(
SELECT
COUNT( 1 )
FROM
student st2
LEFT JOIN score sc2 ON sc2.s_id = st2.s_id
LEFT JOIN course c2 ON c2.c_id = sc2.c_id
WHERE
sc.s_score = sc2.s_score
AND c.c_id != c2.c_id
)>1;
SELECT
a.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '01'
ORDER BY
sc.s_score DESC
LIMIT 0,
2
) a UNION ALL
SELECT
b.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '02'
ORDER BY
sc.s_score DESC
LIMIT 0,
2
) b UNION ALL
SELECT
c.*
FROM
(
SELECT
st.s_id,
st.s_name,
c.c_name,
sc.s_score
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
INNER JOIN course c ON c.c_id = sc.c_id
AND c.c_id = '03'
ORDER BY
sc.s_score DESC
LIMIT 0,
2
) c;
写法 2
SELECT
a.s_id,
a.c_id,
a.s_score
FROM
score a
WHERE
( SELECT COUNT( 1 ) FROM score b WHERE b.c_id = a.c_id AND b.s_score > a.s_score ) <= 2
ORDER BY
a.c_id;
SELECT
c.c_id,
COUNT( 1 )
FROM
score sc
LEFT JOIN course c ON c.c_id = sc.c_id
GROUP BY
c.c_id
HAVING
COUNT( 1 ) > 5
ORDER BY
COUNT( 1 ) DESC,
c.c_id ASC;
SELECT
st.s_id
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
COUNT( 1 )>= 2;
SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
COUNT( 1 )=(
SELECT
COUNT( 1 )
FROM
course)
SELECT
st.*,
TIMESTAMPDIFF(
YEAR,
st.s_birth,
NOW())
FROM
student st
SELECT
st.*
FROM
student st
WHERE
WEEK (
NOW())+ 1 = WEEK (
DATE_FORMAT( st.s_birth, '%Y%m%d' ))
SELECT
st.*
FROM
student st
WHERE
WEEK (
NOW())+ 1 = WEEK (
DATE_FORMAT( st.s_birth, '%Y%m%d' ));
SELECT
st.*
FROM
student st
WHERE
MONTH (
NOW())= MONTH (
DATE_FORMAT( st.s_birth, '%Y%m%d' ));
SELECT
st.*
FROM
student st
WHERE
MONTH (
TIMESTAMPADD(
MONTH,
1,
NOW()))= MONTH (
DATE_FORMAT( st.s_birth, '%Y%m%d' ));
【阿里巴巴开发手册】
点击预览在线版: 阿里巴巴开发手册
内容偏向基础适合各个阶段人员的学习与巩固，如果对您还有些帮助希望给博主点个赞支持一下，感谢！
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）