Mysql基础篇

1. 入门

1.1 介绍

名称	介绍	英文
数据库	数据库是数据的集合。数据库通常可以分为三种主要的数据模型：层次模型、网状模型、关系模型。关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定。数据库是一个用于存储和管理数据的仓库。数据按照特定的格式存储，可以对数据库中的数据进行增加、修改、删除和查询操作。数据库的本质是一个文件系统，按照一定的逻辑结构组织数据，以方便高效地访问和维护。而MySQL是一种较为常见的数据库，较为常见的数据库还有Oracle、DB2、SQLServer、SQLite等。	Database(DB)
数据库管理系统	数据库管理系统（DataBase Management System，DBMS）是一个操作和管理数据库的软件。它用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。	Database Management System(DBMS)
SQL	操作关系型数据的编程语言，SQL通常被用于与关系型数据库管理系统（RDBMS）交互，这些系统包括MySQL、Oracle等。	Structured Query Language(SQL)

1.2 环境准备

下载安装mysql

MySQL :: MySQL Community Downloads

Windows平台下提供两种安装文件：MySQL二进制分发版（.msi安装文件）和免安装版（.zip压缩文件）。一般来讲，应当使用二进制分发版，因为该版本提供了图形化的安装向导过程，比其他的分发版使用起来要简单，不再需要其他工具启动就可以运行MySQL。

这里在Windows 系统下推荐下载MSI安装程序；

下载完成，开始安装。

步骤1：双击下载的mysql-installer-community-8.0.33.0.msi文件，打开安装向导。

步骤2：打开“Choosing a Setup Type”（选择安装类型）窗口，在其中列出了5种安装类型，分别是Developer Default（默认安装类型）、Server only（仅服务器）、Client only（仅客户端）、Full（完全安装）、Custom（自定义）。这里选择“Custom（自定义）”类型按钮，单击“Next(下一步)”按钮。

56423d424ef6471eb396df655571619c

步骤3：打开“Select Products” （选择产品）窗口，可以定制需要安装的产品清单。例如，选择“MySQL Server 8.0.33-X64”后，单击“→”添加按钮，即可选择安装MySQL服务器，如图所示。采用通用的方法，可以添加其他你需要安装的产品。

此时如果直接“Next”（下一步），则产品的安装路径是默认的。如果想要自定义安装目录，则可以选中对应的产品，然后在下面会出现“Advanced Options”（高级选项）的超链接。

步骤4：在上一步选择好要安装的产品之后，单击“Next”（下一步）进入确认窗口，如图所示。单击“Execute”（执行）按钮开始安装。

步骤5：安装完成后在“Status”（状态）列表下将显示“Complete”（安装完成），如图所示。

MySQL安装之后，需要对服务器进行配置。具体的配置步骤如下（默认就行）。

其中，“Config Type”选项用于设置服务器的类型。单击该选项右侧的下三角按钮，即可查看3个选项，如图所示。

Development Machine（开发机器）：该选项代表典型个人用桌面工作站。此时机器上需要运行多个应用程序，那么MySQL服务器将占用最少的系统资源。
Server Machine（服务器）：该选项代表服务器，MySQL服务器可以同其他服务器应用程序一起运行，例如Web服务器等。MySQL服务器配置成适当比例的系统资源。
Dedicated Machine（专用服务器）：该选项代表只运行MySQL服务的服务器。MySQL服务器配置成使用所有可用系统资源。

步骤3：单击“Next”（下一步）按钮，打开设置授权方式窗口。其中，上面的选项是MySQL8.0提供的新的授权方式，采用SHA256基础的密码加密方法；下面的选项是传统授权方法（保留5.x版本兼容性）。

步骤4：单击“Next”（下一步）按钮，打开设置服务器root超级管理员的密码窗口，如图所示，需要输入两次同样的登录密码。也可以通过“Add User”添加其他用户，添加其他用户时，需要指定用户名、允许该用户名在哪台/哪些主机上登录，还可以指定用户角色等。此处暂不添加用户，用户管理在MySQL高级特性篇中讲解。

步骤5：单击“Next”（下一步）按钮，打开设置服务器名称窗口，如图所示。该服务名会出现在Windows服务列表中，也可以在命令行窗口中使用该服务名进行启动和停止服务。本书将服务名设置为“MySQL80”。如果希望开机自启动服务，也可以勾选“Start the MySQL Server at System Startup”选项（推荐）。

下面是选择以什么方式运行服务？可以选择“Standard System Account”(标准系统用户)或者“Custom User”(自定义用户)中的一个。这里推荐前者。

步骤6：单击“Next”（下一步）按钮，打开确认设置服务器窗口，单击“Execute”（执行）按钮。

步骤7：完成配置，如图所示。单击“Finish”（完成）按钮，即可完成服务器的配置。

1.3 图形化界面

Workbench(免费): http://dev.mysql.com/downloads/workbench/
navicat(收费，试用版30天): https://www.navicat.com/en/download/navicat-for-mysql
Sequel Pro(开源免费，仅支持Mac OS): http://www.sequelpro.com/
HeidiSQL(免费): http://www.heidisql.com/
phpMyAdmin(免费): https://www.phpmyadmin.net/
SQLyog: https://sqlyog.en.softonic.com/

2. 基本知识

2.1 SQL的通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释: 单行注释:--注释内容或 #注释内容(MySQL特有) 多行注释:/*注释内容 */

2.2 SQL分类

名称	全称	说明
DDL	Data Definition Langyage	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

2.3 DDL

DDL（ Data Definition Language , DDL ）是数据定义语言，用来定义数据库对象：库、表、列等，主要是对表结构的增删改操作，我们可以将其大致分为两个层面，一个是在数据库中进行操作，一个是在表层面进行操作。

1) DDL-数据库操作

查询
查询所有数据库
sql
```
show databases;
```
这四个就是系统安装完毕，自带的数据库
查询当前数据库
sql
```
select database();
```
使用前，必须存在数据库，且使用了use 数据库名字；否则查询出来的为空

创建

sql

create database [if not exists] 数据库名字 [default charset 字符集] [collate 排序规则]

比如

sql

create database mytest default charset utf8mb4;

删除

sql

drop database [if exists] 数据库名字

例如

sql

drop database if exists mtytest;

使用
sql
```
use 数据库名字;
```

2) DDL-表操作-查询

查询当前数据库中所有表
sql
```
show tables;
```
刚创建的数据库，一般为空，所以为了测试数据呢？一般选择sys。系统自带的信息
查询表结构
sql
```
desc 表名字;
```

查询指定表的建表语句

sql

show create table 表名字;

sql

CREATE TABLE `demo2` (
   `user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
   `pass_word` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户密码'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='表注释'

3) DDL-表结构-创建

sql

create table 表名(
    字段1 字段1类型[comment 字段1注释],
    字段2 字段1类型[comment 字段2注释],
    ...
)[comment 表注释]

注意：[...]为可选参数，最后一列没有逗号

比如

sql

CREATE TABLE `demo2` (
   `user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户名',
   `pass_word` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户密码'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='表注释'

4) DDL-表操作-数据类型

Mysql中的数据类型有很多，主要分为三类：数值类型，字符串类型，日期时间类型

整形

数据类型	字节数	带符号最小值	带符号最大值	不带符号最大值
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607	16777215
INT	4	-2147483648	2147483647	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	18446744073709551616

即使是带符号的BIGINT，其实也已经是一个天文数字了，什么概念，9223372036854775807我们随便举下例子：

以byte为例可以表示8589934592GB-->8388608TB-->8192PB
以毫秒为例可以表示292471208年

所以从实际开发的角度，我们一定要为合适的列选取合适的数据类型，即到底用不用得到这种数据类型？举个例子：

一个枚举字段明明只有0和1两个枚举值，选用TINYINT就足够了，但在开发场景下却使用了BIGINT，这就造成了资源浪费
简单计算一下，假使该数据表中有100W数据，那么总共浪费了700W字节也就是6.7M左右，如果更多的表这么做了，那么浪费的更多

在开发中，我们会碰到有些定义整型的写法是int(11)，这种写法从我个人开发的角度看我认为是没有多大用，不过作为一个知识点做一下讲解吧。

int(N)我们只需要记住两点：

无论N等于多少，int永远占4个字节
N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效

下面举个例子，写一段SQL：

sql

drop table if exists test_int_width;
create table test_int_width (
    a int(5),
    b int(5) unsigned,
    c int(5) unsigned zerofill,
    d int(8) unsigned zerofill
) engine=innodb charset=utf8;

insert into test_int_width values(1, 1, 1, 1111111111);

select * from test_int_width;

浮点型

整型之后，下面是浮点型，在MySQL中浮点型有两种，分别为float、double，它们三者用一张表格总结一下：

数据类型	字节数	备注
float	4	单精度浮点型
double	8	双精度浮点型

sql

drop table if exists test_float;
create table test_float (
    num float(5, 2)
) engine=innodb charset=utf8;

insert into test_float values(1.233);
insert into test_float values(1.237);
insert into test_float values(10.233);
insert into test_float values(100.233);
insert into test_float values(1000.233);
insert into test_float values(10000.233);
insert into test_float values(100000.233);

select * from test_float;

从这个结果我们总结一下float(M,D)、double(M、D)的用法规则：

D表示浮点型数据小数点之后的精度，假如超过D位则四舍五入，即1.233四舍五入为1.23，1.237四舍五入为1.24
M表示浮点型数据总共的位数，D=2则表示总共支持五位，即小数点前只支持三位数，所以我们并没有看到1000.23、10000.233、100000.233这三条数据的插入，因为插入都报错了

当我们不指定M、D的时候，会按照实际的精度来处理。

定点型

介绍完float、double两种浮点型，我们介绍一下定点型的数据类型decimal类型，有了浮点型为什么我们还需要定点型？写一段SQL看一下就明白了：

sql

drop table if exists test_decimal;
create table test_decimal (
    float_num float(10, 2),
    double_num double(20, 2),
    decimal_num decimal(20, 2)
) engine=innodb charset=utf8;

insert into test_decimal values(1234567.66, 1234567899000000.66, 1234567899000000.66);
insert into test_decimal values(1234567.66, 12345678990000000.66, 12345678990000000.66);

select * from test_decimal;

结果如下：

看到float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而decimal无论写入数据中的数据是多少，都不会存在精度丢失问题，这就是我们要引入decimal类型的原因，decimal类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中。

最后讲一下decimal和float/double的区别，个人总结主要体现在两点上：

float/double在db中存储的是近似值，而decimal则是以字符串形式进行保存的
decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理而decimal在不指定M、D时默认为decimal(10, 0)

日期类型

接着我们看一下MySQL中的日期类型，MySQL支持五种形式的日期类型：date、time、year、datetime、timestamp，用一张表格总结一下这五种日期类型：

数据类型	字节数	格式	备注
date	3	yyyy-MM-dd	存储日期值
time	3	HH:mm:ss	存储时分秒
year	1	yyyy	存储年
datetime	8	yyyy-MM-dd HH:mm:ss	存储日期+时间
timestamp	4	yyyy-MM-dd HH:mm:ss	存储日期+时间，可作时间戳

下面我们还是用SQL来验证一下：

sql

drop table if exists test_time;
create table test_time (
    date_value date,
    time_value time,
    year_value year,
    datetime_value datetime,
    timestamp_value timestamp
) engine=innodb charset=utf8;

insert into test_time values(now(), now(), now(), now(), now());

select * from test_time;

结果如下所识别

MySQL的时间类型的知识点比较简单，这里重点关注一下datetime与timestamp两种类型的区别：

上面列了，datetime占8个字节，timestamp占4个字节
由于大小的区别，datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407
datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间
datetime存储的时间与时区无关，timestamp存储的时间及显示的时间都依赖于当前时区

在实际工作中，一张表往往我们会有两个默认字段，一个记录创建时间而另一个记录最新一次的更新时间，这种时候可以使用timestamp类型来实现：

sql

create_time timestamp default current_timestamp comment "创建时间",
update_time timestamp default current_timestamp on update current_timestamp comment "修改时间",

字符串

blob类型：一般用于存储二进制数据，例如图像、音频、视频等文件。

text类型：一般用于存储文本数据，例如字符串、文章、日志等。适用于存储大型文本文档或字符串。

sql

CHAR(10)    # 定长，即使只存储1个字符也会占用10个字符空间，性能好
VARCHAR(10) # 变长，性能相对char较差

username VARCHAR(50)
gender CHAR(1) # 非男即女，定长

举个例子

sql

CREATE TABLE employee(
    id INT COMMENT '编号',
    workno VARCHAR(10) COMMENT '工号',
    name VARCHAR(10) COMMENT '姓名',
    gender CHAR(1) COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    idcard CHAR(18) COMMENT '身份证号',
    entrydate DATE COMMENT '入职时间'
) COMMENT '员工信息表';

char和varchar

最后看一下常用到的字符型，说到MySQL字符型，我们最熟悉的应该就是char和varchar了，关于char和varchar的对比，我总结一下：

char是固定长度字符串，其长度范围为0~255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0~21844
char实际占用的字节数即存储的字符所占用的字节数，varchar实际占用的字节数为存储的字符+1或+2或+3
MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会

sql

drop table if exists test_string;
create table test_string (
    char_value char(5),
    varchar_value varchar(5)
) engine=innodb charset=utf8;

insert into test_string values('a', 'a');
insert into test_string values(' a', ' a');
insert into test_string values('a ', 'a ');
insert into test_string values(' a ', ' a ');

select length(char_value),length(varchar_value) from test_string;

使用length函数来看一下结果：

验证了我们的结论，char类型数据并不会取最后的空格。

varchar型数据占用空间大小及可容纳最大字符串限制探究

接上一部分，我们这部分来探究一下varchar型数据实际占用空间大小是如何计算的以及最大可容纳的字符串为多少，首先要给出一个结论：这部分和具体编码方式有关，且MySQL版本我现在使用的是5.7，当然5.0之后的都是可以的。

先写一段SQL创建表，utf8的编码格式：

sql

drop table if exists test_varchar;
create table test_varchar (
    varchar_value varchar(100000)
) engine=innodb charset=utf8;

执行报错：

Column length too big for column 'varchar_value' (max = 21845); use BLOB or TEXT instead

按照提示，我们把大小改为21845，执行依然报错：

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

改为21844就不会有问题，因此在utf8编码下我们可以知道varchar(M)，M最大=21844。那么gbk呢：

sql

drop table if exists test_varchar;
create table test_varchar (
    varchar_value varchar(100000)
) engine=innodb charset=gbk;

同样的报错：

Column length too big for column 'varchar_value' (max = 32767); use BLOB or TEXT instead

把大小改为32766，也是和utf8编码格式一样的报错：

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

可见gbk的编码格式下，varchar(M)最大的M=32765，那么为什么会有这样的区别呢，分点详细解释一下：

MySQL要求一个行的定义长度不能超过65535即64K
对于未指定varchar字段not null的表，会有1个字节专门表示该字段是否为null
varchar(M)，当M范围为0<=M<=255时会专门有一个字节记录varchar型字符串长度，当M>255时会专门有两个字节记录varchar型字符串的长度，把这一点和上一点结合，那么65535个字节实际可用的为65535-3=65532个字节
所有英文无论其编码方式，都占用1个字节，但对于gbk编码，一个汉字占两个字节，因此最大M=65532/2=32766；对于utf8编码，一个汉字占3个字节，因此最大M=65532/3=21844，上面的结论都成立
举一反三，对于utfmb4编码方式，1个字符最大可能占4个字节，那么varchar(M)，M最大为65532/4=16383，可以自己验证一下

同样的，上面是表中只有varchar型数据的情况，如果表中同时存在int、double、char这些数据，需要把这些数据所占据的空间减去，才能计算varchar(M)型数据M最大等于多少。

varchar、text和blob

最后讲一讲text和blob两种数据类型，它们的设计初衷是为了存储大数据使用的，因为之前说了，MySql单行最大数据量为64K。

先说一下text，text和varchar是一组既有区别又有联系的数据类型，其联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：

M>255时转为tinytext
M>500时转为text
M>20000时转为mediumtext

所以过大的内容varchar和text没有区别，同事varchar(M)和text的区别在于：

单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来
text可以指定text(M)，但是M无论等于多少都没有影响
text不允许有默认值，varchar允许有默认值

varchar和text两种数据类型，使用建议是能用varchar就用varchar而不用text（存储效率高），varchar(M)的M有长度限制，之前说过，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。

至于text和blob，简单过一下就是text存储的是字符串而blob存储的是二进制字符串，简单说blob是用于存储例如图片、音视频这种文件的二进制数据的。

5) DDL-表结构-修改

添加字段

sql

alter table 表名 add 字段名 类型(长度) [comment  注释] [约束];

案例，为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)

sql

alter table emp add nickname varchar(20) comment "昵称";

修改数据类型

sql

ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);

修改字段名和字段类型

sql

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度)[COMMENT 注释][约束];

案例

将emp表的nickpame字段修改为username，类型为varchar(30)

sql

alter table emp  change nickname username varchar(30) comment "修改后用户名";

删除字段
sql
```
alter table 表名 drop 字段名;
```
案例
将emp表的字段username删除
sql
```
alter table emp drop username;
desc emp;
```
我们就会发现username字段已经删除了

修改表名

sql

alter table 表名 rename to 新表名;

案例:将emp表的表名修改为 employee

sql

ALTER TABLE emp RENAME TO employee;
show tables;

删除表
sql
```
drop table [if exists] 表名;
```
删除指定表，并重新创建该表-其实就是清空表
sql
```
truncate table 表名;
```

2.4 DML

数据操作对象（ Data Manipulation Language , DML ）用于操纵数据库中的各种对象，检索和修改数据。常用的数据操作语句及其说明如下。

(1) INSERT 语句：用于向已经存在的表或视图中插入新的数据。

(2) SELECT 语句：用于查询表或视图中的数据。

(3) UPDATE 语句：用于更新表或视图中的数据。

(4) DELETE 语句：用于删除表或视图中的数据。

DML-添加数据

1.给指定字段添加数据

sql

insert into 表名(字段1,字段2,...) values (值1,值2,...)

2.给全部字段添加数据

sql

INSERT INTO 表名 VALUES (值1,值2, ...);

3.批量添加数据

sql

INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,..),(值1,值2,..),(值1,值2,...);
INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);

注意点：

插入数据时，指定的字段顺序需要与值的顺序是-一对应的。
字符串和日期型数据应该包含在引号中，
插入的数据大小，应该在字段的规定范围内

测试-创建表

sql

-- auto-generated definition
create table employee
(
    id        int              not null
        primary key,
    workno    varchar(10)      null,
    name      varchar(10)      null comment '姓名',
    gender    char             null comment '性别',
    age       tinyint unsigned null comment '年龄',
    idcard    char(18)         null comment 'id卡号',
    entrydate date             null comment '入职日期'
)
    comment '员工';

插入测试

sql

insert into employee(id, workno, name, gender, age, idcard, entrydate) values (1,'1','test','男',10,'123456789987654321','2000-01-01');

批量插入

sql

insert into employee
values (2,'2','test2','男',10,'123456789987654324','1999-01-01'),
       (3,'3','test3','女',10,'123456789987654324','2001-01-01');

DML-修改数据

sql

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

比如

sql

update  employee set name='修改测试' where  id=1;
select * from employee;

修改id为1 的数据，将name修改为小昭,gender修改为女

sql

update  employee set name='小昭' , gender='女' where  id=1;
select * from employee;

将所有的员工入职日期修改为 2008-01-01

sql

update employee set entrydate='2008-01-01';
select * from employee;

这边会提示你警告，直接点击运行。如果不知道怎么点击，可以取巧

sql

update employee set entrydate='2008-01-01' where  id>1;

DDL-删除数据

sql

DELETE FROM 表名[WHERE 条件]

删除员工性别为女的员工

sql

delete from employeewhere gender='女';

2.4 DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

sql

SELECT
      字段列表
FROM
      表名列表
WHERE
      条件列表
GROUP BY
      分组字段列表
HAVING
      分组后条件列表
ORDER BY
      排序字段列表
LIMIT
      分页参数

基本查询
条件查询(WHERE)
聚合函数(count、max、min、avg、sum
分组查询(GROUP BY)
排序查询(ORDER BY)
分页查询(LIMIT)

DQL-基本查询

查询数据

sql

SELECT 字段1,字段2,字段3,... FROM 表名
SELECT * FROM 表名

设置别名

sql

SELECT 字段1[AS 别名1],字段2[AS 别名2]…. FROM 表名;

去除重复记录

sql

SELECT DISTINCT 字段列表 FROM 表名

测试sql

sql

-- 删除表如果存在
DROP TABLE IF EXISTS employee;

-- 创建表
CREATE TABLE employee
(
    id          INT              NOT NULL
        PRIMARY KEY,
    workno      VARCHAR(10)      NULL COMMENT '编号',
    name        VARCHAR(10)      NULL COMMENT '姓名',
    gender      CHAR             NULL COMMENT '性别',
    age         TINYINT UNSIGNED NULL COMMENT '年龄',
    idcard      CHAR(18)         NULL COMMENT '身份证标号',
    workaddress VARCHAR(50)      NULL COMMENT '工作地址',
    entrydate   DATE             NULL COMMENT '入职日期'
)
    COMMENT '员工';

-- 插入数据
INSERT INTO employee (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES
(1, 'W001', '张三', '女', 25, '110101199001010001', '北京市朝阳区', '2020-01-15'),
(2, 'W002', '李四李四', '男', 20, '110101199002020002', '北京市海淀区', '2019-03-20'),
(3, 'W003', '王五五', '女', 88, '110101199003030003', '北京市朝阳区', '2021-06-01'),
(4, 'W004', '赵六六六六', '男', 26, '110101199004040004', '北京市东城区', '2018-11-10'),
(5, 'W005', '孙七七', '女', 35, NULL, '北京市海淀区', '2017-08-25'),
(6, 'W006', '周八八八八八', '男', 29, '110101199006060006', '北京市西城区', '2021-01-18'),
(7, 'W007', '吴九九', '女', 17, '110101199007070007', '北京市朝阳区', '2019-05-22'),
(8, 'W008', '郑十十', '男', 24, '110101199008080008', '北京市丰台区', '2020-12-14'),
(9, 'W009', '王一', '女', 31, '110101199009090009', '北京市西城区', '2016-07-30'),
(10, 'W010', '冯二', '男', 32, '110101199010100010', '北京市海淀区', '2015-04-12'),
(11, 'W011', '陈三', '女', 29, '110101199011110011', '北京市东城区', '2017-09-27'),
(12, 'W012', '何四四', '男', 26, '110101199012120012', '北京市丰台区', '2020-03-05'),
(13, 'W013', '刘五五五', '女', 34, '110101199101010013', '北京市朝阳区', '2018-06-18'),
(14, 'W014', '黄六', '男', 28, '110101199102020014', '北京市海淀区', '2019-11-29'),
(15, 'W015', '杨七', '女', 33, '11010119910303001X', '北京市西城区', '2021-04-07'),
(16, 'W016', '徐八', '男', 40, '110101199104040016', '北京市丰台区', '2020-08-21'),
(17, 'W017', '朱九', '女', 25, '11010119910505001X', '北京市东城区', '2019-02-10'),
(18, 'W018', '潘十', '男', 31, '110101199106060018', '北京市西城区', '2017-11-15'),
(19, 'W019', '葛一一', '女', 29, '110101199107070019', '北京市朝阳区', '2018-04-02'),
(20, 'W020', '范二二', '男', 32, '110101199108080020', '北京市海淀区', '2021-09-11'),
(21, 'W021', '彭三', '女', 28, '110101199109090021', '北京市东城区', '2020-05-19'),
(22, 'W022', '鲁四', '男', 27, '110101199110100022', '北京市丰台区', '2019-06-25'),
(23, 'W023', '韦五', '女', 35, '110101199111110023', '北京市西城区', '2017-03-14'),
(24, 'W024', '昌六', '男', 33, '110101199112120024', '北京市朝阳区', '2018-10-23'),
(25, 'W025', '马七', '女', 31, '110101199201010025', '北京市东城区', '2021-07-30'),
(26, 'W026', '苗八', '男', 30, '110101199202020026', '北京市海淀区', '2020-02-17'),
(27, 'W027', '宋九', '女', 26, '110101199203030027', '北京市丰台区', '2019-08-06'),
(28, 'W028', '胡十', '男', 28, '110101199204040028', '北京市朝阳区', '2018-01-25'),
(29, 'W029', '凌一', '女', 34, '110101199205050029', '北京市西城区', '2017-05-11'),
(30, 'W030', '任二', '男', 27, '110101199206060030', '北京市东城区', '2021-03-22');

--1.查询指定字段 name，workno，age 返回

sql

select name,workno,age from  employee;

--2.查询所有字段返回

sql

select * from  employee;
select id, workno, name, gender, age, idcard, workaddress, entrydate from  employee;

在工作中，尽量不要用*号，影响效率

--3.查询所有员工的工作地址，起别名

sql

select  workaddress as '工作地址' from employee;

--4.查询公司员工的上班地址(不要重复)

sql

select distinct workaddress as '工作地址' from employee;

DQL-条件查询

sql

SELECT 字段列表 FROM 表名 WHERE 条件列表

测试sql

sql

-- 1.查询年龄等于88 的员工
    select * from employee where age=88;
-- 2.查询年龄小于 20 的员工信息
     select * from employee where age<20;
-- 3.查询年龄小于等于 20 的员工信息
    select * from employee where age=20;
-- 4.查询没有身份证好的员工信息
     select * from employee where idcard is null;
-- 5.查询有身份证号的员工信息
     select * from employee where idcard is not null;
-- 6.查询年龄不等于 88的员工信息
    select * from employee where age!=88;
    select * from employee where age<>88;
-- 7.查询年龄在15岁(包含)到20岁(包含)之间的员工信息
    select * from employee where age >=15 && age<=20;
    select * from employee where age >=15 and age<=20;
    select * from employee where age between 15 and  20; -- 不行写反了
-- 8.查询性别为女且年龄小于 25岁的员工信息
    select * from employee where  gender='女' and  age<25;
-- 9.查询年龄等于18或20或88 的员工信息
     select * from employee where  age =18 or age =20 or age=88;
     select * from employee where  age in(18,20,88);
-- 10.查询姓名为两个字的员工信息
    select * from employee where name like '__';
-- 11.查询身份证号最后一位是X的员工信息
   select * from  employee where idcard like '%X';

DCL-聚合函数

将一列数据作为一个整体，进行纵向计算。

语法

sql

SELECT 聚合函数(字段列表) FROM 表名

测试sql

sql

-- 1.统计该企业员工数量
select count(*)
from employee;
select count(id)
from employee;
select count(idcard)
from employee;
-- 所有的null是不参与count计算的
-- 2.统计该企业员工的平均年龄
select avg(age)
from employee;
-- 3.统计该企业员工的最大年龄
select max(age)
from employee;
-- 4.统计该企业员工的最小年龄
select min(age)
from employee;

-- 5.统计北京市海淀区地区员工的年龄之和
select sum(age)
from employee
where workaddress = '北京市海淀区';

DQL-分组查询

sql

SELECT 字段列表 FROM 表名[WHERE 条件]GROUP BY 分组字段名[HAVING 分组后过滤条件1];

where与having区别

执行时机不同:where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断，而having可以

sql

-- 1.根据性别分组，统计男性员工和女性员工的数量
select gender, count(*)
from employee
group by gender;

-- 2.根据性别分组，统计男性员工和女性员工的平均年龄
select gender, avg(age)
from employee
group by gender;

-- 3.查询年龄小于45的员工 ，并根据工作地址分组，获取员工数量大于等于6的工作地址
select workaddress, count(*) address_count
from employee
where age < 45
group by workaddress
having address_count >= 6;

注意

执行顺序:where >聚合函数>having
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

DQL-排序查询

sql

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

排序方式

ASC:升序(默认值)
DESC:降序

测试-sql

sql

-- 排序查询
-- 1.根据年龄对公司的员工进行升序排序
select * from employee order by age asc ;
-- 2.根据入职时间，对员工进行降序排序
select * from employee order by entrydate desc ;
-- 3.根据年龄对公司的员工进行升序排序，年龄相同 ，再按照入职时间进行降序排序
select * from employee order by age asc ,entrydate desc ;

注意:如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

DQL-分页查询

sql

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

测试sql

sql

-- 分页查询
-- 1.查询第1页员工数据，每页展示10条记录
    select * from employee limit 0,10;
-- 2.查询第2页员工数据，每页展示10条记录,(页码-1)*页展示记录数
    select * from employee limit 10,10;

注意

起始索引从0开始，起始索引=(查询页码-1)*每页显示记录数.
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

案例测试

sql

-- 1.查询年龄为22,23,24,25岁的员工信息。
select * from employee where gender='女' and age in (22,23,24,25)
-- 2.查询性别为 男 ，并且年龄在 20-40 岁(含)以内的姓名为三个字的员工。
    select * from employee where gender='男' and age between 20 and 40 and name like '___';
-- 3.统计员工表中,年龄小于60岁的，男性员工和女性员工的人数。
    select gender,count(*) from  employee where age<60 group by gender ;
-- 4、查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序
    select name,age from employee where age<=35 order by age  , entrydate desc ;
-- 5.查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select  * from employee where gender='男' and age between 20 and 40 order by age ,entrydate limit 5;

DQL-执行顺序

sql

-- 查询年龄大于15的员工的姓名、年龄，并根据年龄进行升序排序
select e.name, e.age eage
from employee e
where e.age > 15
order by eage asc;

2.5 DCL

数据控制语言（ Data Control Language , DCL ）用于安全管理，确定哪些用户可以查或修改数据库中的数据。数据控制语言包括的主要语句及其说明如下。

(1) GRANT ：授予权限，可把语句许可或对象许可的权限授予其他用户和角色。

(2) REVOKE ：收回权限，与 GRANT 的功能相反，但不影响该用户或角色从其他角色中作为成员继承许可权限。

DCL-管理用户

1.查询用户

sql

USE mysql;
SELECT * FROM user;

2.创建用户

sql

CREATE USER '用户名'@'主机名' identified BY '密码';

测试sql

sql

-- 创建用户itcast,只能够在当前主机localhost访问，密码123456;
create user 'itcast'@'localhost' identified by '123456'

会发现系统

接下来测试一下能否登录，需要添加一下mysql变量

运行一下命令行

3.修改用户密码

sql

ALTER USER '用户名'@'主机名'  identified WITH mysal native password BY '新密码'

4.删除用户

sql

DROP USER'用户名'@'主机名’

测试sql

sql

-- 创建用户itcast,只能够在当前主机localhost访问，密码123456;
create user 'itcast'@'localhost' identified by '123456';
-- 创建用户 heima,可以在任意主机访问该数据库，密码123456 ;
create user 'heima'@'%' identified by '123456';
-- 修改用户 heima 的访问密码为 1234;
alter user 'heima'@'%' identified with mysql_native_password by '1234';
-- 删除itcast@localhost用户
drop user 'itcast'@'localhost';

注意

主机名可以使用%通配
这类SQL开发人员操作的比较少，主要是DBA( Database Administrator, 数据库管理员)使用。

DQL-权限控制

1.查询权限

sql

SHOW GRANTS FOR'用户名'@'主机名’

2.授予权限

sql

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名

测试一下sql

sql

grant  all on mytest.* to 'heima'@'%';

3.撤销权限

sql

REVOKE 权限列表 ON 数据库名.表名 FROM'用户名'@'主机名!

测试sql

sql

-- 撤销权限
revoke all on mytest.* from 'heima'@'%';

测试show databases; 发现数据库已经没有了

2.6 函数

函数是指一段可以直接被另一段程序调用的程序或代码

字符串函数

MySQL中内置了很多字符串函数，常用的几个如下

函数	功能
CONCAT(S1,S2....Sn)	字符串拼接，将S1，S2，..Sn拼接成一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串
SUBSTRING_INDEX(str, delimiter, number)	返回从字符串str的第number个出现的分隔符delimiter之前的子串;如果number是正数，那么就是从左往右数，返回第number个分隔符的左边的全部内容;相反，如果number是负数，那么就是从右边开始数，第number个分隔符右边的所有内容

测试sql

sql

select concat('Hello','mysql');


select upper('hello');

select  lpad('01',5,'-');

select  rpad('01',5,'-');

select trim(' Hello Mysql ');


select  substring('Hello Mysql',1,5);

select substring_index('Hello Mysql','M',1); -- Hello 


-- 案例1
SELECT SUBSTRING_INDEX('a*b','*',1); -- a

-- 案例2
SELECT SUBSTRING_INDEX('a*b','*',-1); -- b

-- 案例3
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1); -- c
-- 案例3可拆解成以下两个步骤
SELECT SUBSTRING_INDEX('a*b*c*d*e','*',3); -- a*b*c
SELECT SUBSTRING_INDEX('a*b*c','*',-1); -- c

-- 案例4
-- 如果任一输入参数为NULL，则返回NULL
SELECT SUBSTRING_INDEX('https://www.google.com', null, 2);
SELECT SUBSTRING_INDEX('https://www.google.com', '.', null);
SELECT SUBSTRING_INDEX(null, '.', 2);

-- 案例5
-- 如果number超过了实际分隔符的个数，则返回实际个数的字符串
-- https://www.google.com
SELECT SUBSTRING_INDEX('https://www.google.com', '.', 4);

根据需求完成以下sql编写

由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00001.

sql

-- auto-generated definition
create table test
(
    id     int         not null comment '主键'
        primary key,
    workno varchar(10) null comment '员工编号'
);

INSERT INTO mytest.test (id, workno) VALUES (1, '1');
INSERT INTO mytest.test (id, workno) VALUES (2, '2');
INSERT INTO mytest.test (id, workno) VALUES (3, '3');

然后修改

sql

update test set workno=lpad(workno,5,'0')

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

sql

select ceil(1.1);
select floor(1.9);
select mod(7,4);
select  rand();
select round(2.345,2);
select round(2.344,2);

通过数据库的函数，生成一个六位数的随机验证码。

sql

select lpad(round(rand()*1000000,0),6,'0');

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

测试sql

sql

select  curdate();
select current_time(); -- 14:15:09
select now();-- 2024-07-01 14:15:26
select YEAR(now()) ;-- 2024
select date_add(now(),interval 70 year ); -- 2094-07-01 14:16:24
select date_add(now(),interval 70 month ); -- 2030-05-01 14:16:47
select date_add(now(),interval 70 day ); -- 2024-09-09 14:16:40
select datediff('2021-12-01','2021-10-01');

案例:查询所有员工的入职天数，并根据入职天数倒序排序。

sql

select name,datediff(curdate(),entrydate) as days  from  employee order by days desc ;

流程控制函数

函数	功能
IF(value ,,f)	如果value为true，则返回t，否则返回f
IFNULL(value1 ,value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [val1 ] THEN [res1] ... ELSE [ default] END	如果val1为true，返回res1，….否则返回default默认值
CASE [expr]WHEN [val1 ]THEN [res1] ...ELSE[default] END	如果expr的值等于val1，返回res1，…否则返回default默认值

测试sql

sql

select  if(true,'ok','error'); -- ok
select  ifnull('ok','Default'); -- ok
select  ifnull('','Default'); --
select  ifnull(null,'Default'); -- Default

-- 需求： 查询employee 地址。展示员工姓名和工作地址（北京市朝阳区/北京市海淀区--> 核心区域，其他地方--> 非核心区域)
select  name,case workaddress when '北京市朝阳区' then '核心区域'  when '北京市海淀区' then '核心区域' else '非核心区域' end  as '工作地址' from employee;

2.7 约束

约束就是对数据的一种限制条件，目的是保证表中的记录完整和有效。主要有以下几种：

非空约束(not null)：数据值不能为null
唯一性约束(unique)：数据值只能是唯一存在的，不能有相同的数据
主键约束(primary key) PK：一张表必须有一个主键约束，当前行的数据是非空且唯一
外键约束(foreign key) FK：关联其他表的主键，用于多表设计

约束	描述	关键字
非空约束	限制这字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

注意:约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

根据需求，完成表结构的创建

sql

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null  unique comment '姓名',
    age int check (  age >0 && age <=20) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

插入数据

sql

insert into  user(name, age, status, gender) value ('tom1','19','1','男'),('tom2','17','0','男');

外键约束

删除/更新行为

2.8 多表查询

多表关系

模块之间的关系，分析并设计表结构，由于业务之间相互关联，所项目开发中，在进行数据库表结构设计时，会根据业务需求及业以各个表结构之间也存在着各种联系，基本上分为三种

一对多(多对一) 多对多一对一

一对多

场景选择：每个公司都有很多不同的部门，他们之间各司其职，一个研发部门只管搞研发的工作，每个部门有着多个员工，这就是典型的一对多的模型。

sql

# 员工表和部门表
CREATE TABLE dept_test(
    id INT COMMENT '部门编号',
    name VARCHAR(20) UNIQUE COMMENT '部门名称'
);
INSERT INTO dept_test(id,name) VALUE (1,'研发部'),(2,'市场营销部'),(3,'测试部'),(4,'运维部'),(5,'人事部'),(6,'美工部');
 
CREATE TABLE emp_test(
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  name VARCHAR(20) COMMENT '员工姓名',
  age TINYINT COMMENT '员工年龄',
  job VARCHAR(10) COMMENT '员工岗位',
  dept_id INT COMMENT '员工所属部门'
);
 
INSERT INTO emp_test(id,name,age,job,dept_id) VALUES (1,'张三',21,'Java开发',1),
                                                     (2,'李四',25,'测试岗',3),
                                                     (3,'翠花',18,'测试岗',3),
                                                     (4,'麻子',20,'运维岗',4),
                                                     (5,'秦始皇',120,'嵌入式开发',1),
                                                     (6,'老子',18,'老总',5),
                                                     (7,'小妖怪',21,'PPT',6);

注意：一般情况下为了保证我们的数据的一致性和完整性，一般都需要添加外键约束

多对多

场景选择：最常见的就是学生与课程表之间的关系，每个学生都有着多门课程，每门课程又可以供不同学生选择。

sql

# 课程表与学生表
# 创建学生表
CREATE TABLE students (
  student_id INT PRIMARY KEY COMMENT '主键',
  student_name VARCHAR(50) COMMENT '学生姓名',
  student_number char(3) UNIQUE NOT NULL COMMENT '学号'
);
INSERT INTO students(student_id, student_name, student_number) VALUES(1,'张三','201'),
                                                     (2,'李四','202'),
                                                     (3,'王五','203'),
                                                     (4,'翠花','204'),
                                                     (5,'小妖怪','205');
 
# 创建课程表
CREATE TABLE courses (
  course_id INT PRIMARY KEY COMMENT '主键',
  course_name VARCHAR(50) UNIQUE COMMENT '课程名称'
);
INSERT INTO courses(course_id, course_name) VALUES(1,'人工智能'),
                                                  (2,'java'),
                                                  (3,'php'),
                                                  (4,'spark'),
                                                  (5,'hadoop');

添加外键关系

sql

-- 达到以上的步骤之后，我们的学生表和课程表,之后，很显然我们的不能看到什么关系，这个时候就需要借助第三方表，形成类似于一种映射，拉链的形式绑定起来
CREATE TABLE student_course(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    student_id INT COMMENT '学生ID',
    course_id INT COMMENT '课程ID',
    CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES students(student_id),
    CONSTRAINT fk_course_id FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO student_course(student_id, course_id) VALUES(1,2),
                                                        (1,3),
                                                        (1,1),
                                                        (2,3),
                                                        (3,3),
                                                        (2,1);

这样子我们就能看到关系了

一对一

案例: 用户与用户详情的关系
关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

比如说：如果叫我们描述一个人，我们能从它的身体的基本信息来描述（身高，体重，姓名，年龄），也可以使用受教育信息（小学，中学，大学）等等。

数据准备：

sql

# 一对一的操作
CREATE TABLE tb_user(
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    name VARCHAR(10) COMMENT '姓名',
    age INT COMMENT '年龄',
    gender CHAR(1) COMMENT '1：男 0：女'
) COMMENT '用户基本信息';
 
CREATE TABLE tb_user_edu(
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
    degree VARCHAR(20) COMMENT '学历',
    major VARCHAR(50) COMMENT '专业',
    userid INT UNIQUE COMMENT '用户ID',
    CONSTRAINT fk_user_id FOREIGN KEY (userid) REFERENCES tb_user(id)
)COMMENT '用户教育信息表';

多表查询概述

概述: 指从多张表中查询数据
笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

数据准备

sql

CREATE TABLE dept_query(
    id INT PRIMARY KEY COMMENT '部门编号',
    name VARCHAR(20) UNIQUE COMMENT '部门名称'
);
INSERT INTO dept_query(id,name) VALUE (1,'研发部'),(2,'市场营销部'),(3,'测试部'),(4,'运维部'),(5,'人事部'),(6,'美工部');
 
drop table  if exists emp_query;
# 创建我们的员工表
create table if not exists emp_query
(
    id         int auto_increment primary key comment '主键',
    name       varchar(50) not null comment '员工姓名',
    age        int comment '员工年龄',
    job        varchar(30) comment '员工的职位',
    salary     int default 2000 comment '员工薪资',
    entry_date date comment '员工入职时间',
    manage_id  int comment '直属领导编号',
    dept_id    int comment '所属部门ID'
) comment '员工表';
insert into emp_query(id, name, age, job, salary, entry_date, manage_id, dept_id)
values (1, '李星云', 21, '开发', 15000, '2012-8-12', 2, 1),
       (2, '袁天罡', 300, '老总', 200000, '2000-2-10', null, 5),
       (3, '姬如雪', 19, '测试', 6000, '2019-5-12', 8, 3),
       (4, '上官', 25, '运维', 8000, '2008-10-1', 2, 4),
       (5, '张子凡', 21, '开发', 9000, '2018-8-29', 10, 1),
       (6, '倾国倾城', 28, '销售', 3000, '2015-8-30', 5, 2),
       (7, '温涛', 31, '测试', 5000, '2005-8-12', 2, 3),
       (8, '女帝', 29, '经理', 18000, '2007-8-12', 1, 5),
       (9, '李嗣源', 41, '测试', 12000, '2000-3-12', 2, 3),
       (10, '李淳风', 31, '人事顾问', 15000, '2005-8-12', null, 5),
        (11, 'joker', 31, '新部门', 15000, '2003-8-12', null, 7);

然后进行多表查询

sql

select  * from dept_query,emp_query;

他们是进行笛卡尔积运算的。数据有很多，这是不对的，需要消除无效的笛卡尔积

内连接

内连接相当于查询A、B两个集合之间的交集

隐式连接
语法：select 字段列表 from 表1,表2 where 条件;
显示连接
语法：select 字段列表 from 表1 [inner] join 表2 on 连接条件;

sql


# 内连接：隐式连接和显式连接
SELECT * from emp_query,dept_query where emp_query.dept_id = dept_query.id;

# 显示连接
SELECT * FROM emp_query INNER JOIN dept_query ON emp_query.dept_id=dept_query.id;

# 起别名省略的写法
SELECT * FROM emp_query e JOIN dept_query d ON e.dept_id=d.id;

emp_query 11个员工，dept_query一共6个部门

查出来的结果一共10条。为啥呢？因为是交集，第11条数据 joker属于第7个部门，不存在。

外连接

左外连接:查询左表所有数据，以及两张表交集部分数据

sql

SELECT * FROM emp_query e left join dept_query d on d.id = e.dept_id;

右外连接:查询右表所有数据，以及两张表交集部分数据

sql

SELECT * FROM emp_query e right join dept_query d on d.id = e.dept_id;

自连接

当前表与自身的连接查询，自连接必须使用表别名

比如一张表中存在着一个领导层，通过一张表中的人物关系查询其上层领导。

语法:select 字段列表 from 表A 别名a 表B 别名b on 条件....;

注意：自查询可以是内连接查询，也可以是外连接查询，但是一定要起别名，一定要，否则你怎么辨认。

实例：查询人物层次的领导关系，比如李星云的上层领导是谁？

sql

SELECT temp2.id as id,  temp2.name as '姓名' , temp1.id as '上司id',temp1.name '上司名字' FROM emp_query temp1 JOIN emp_query temp2 ON temp2.manage_id=temp1.id;

可以查看出我们的的执行是先从join后边的表找出对应的关系，采用从后往前推的思想。

联合查询

union all：啥也不管，直接合并

union : 去重

联合查询也就是把两张表的查询结果拼接起来，需求：将公司人员大于35的的员工和薪资低于8000的员工查询出来

sql

# 联合查询：也就是将查询结果拼接在一起（union union all）
SELECT * FROM emp_query WHERE age > 30
UNION ALL
SELECT * FROM emp_query WHERE salary < 12000;

使用union，很显然name没有重复的了

注意点：对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询

根据子查询结果不同，分为以下四类:

标量子查询(子查询结果为单个值)

子查询结果为单个值（数字、字符串、日期），最简单的形式，常用到的操作符：= <> > >= < <=

查询开发部门所有员工的所有信息

sql

/* 子查询：标量子查询
   1、查询研发部门中所有员工的所有信息
 */
#  a.获取部门编号：
SELECT id FROM dept_query WHERE name = '研发部';
 
#  b.查询部门为研发部（编号为1）的所有员工信息
SELECT * FROM emp_query WHERE dept_id = 1;
 
# 合并
SELECT * FROM emp_query WHERE dept_id = (SELECT id FROM dept_query where name = '研发部');

列子查询(子查询结果为一列)

子查询的结果返回是一列（可以是多行），常用到的操作符：IN,NOT IN,ANY,SOME,ALL

操作符	描述
IN	在指定的综合范围内多选一
NOT IN	不在指定的范围
ANY	子查询返回列表中，有任一一个满足即可
SOME	与ANY等同，使用SOME的地方都能使用到ANY
ALL	子查询返回列表的所有值都必须要满足

子查询返回的结果是一行（可以是多列）,常用的操作符：= <> IN NOT IN

sql

# 列查询（子查询返回的结果是一个列值）
 
# a.获取“研发部”和“市场营销部”的编号
SELECT id FROM dept_query WHERE name IN('研发部','市场营销部');
 
# b.查询研发部门和市场营销部门的所有员工信息
SELECT * FROM emp_query WHERE dept_id IN (1,2);
 
# 合并
SELECT * FROM emp_query WHERE dept_id IN (SELECT id FROM dept_query WHERE name IN('研发部','市场营销部'));

查询比研发部任意一名员工工资都高的所有员工信息，即满足一个即可

sql

# b.获取比研发部任意一名员工工资都高的所有员工信息

SELECT * FROM emp_query WHERE salary > ANY(SELECT salary FROM emp_query WHERE dept_id = (SELECT id FROM dept_query WHERE name = '研发部'));

# SOME 和 ANY的效果相同
SELECT * FROM emp_query WHERE salary > SOME(SELECT salary FROM emp_query WHERE dept_id = (SELECT id FROM dept_query WHERE name = '研发部'));

行子查询(子查询结果为一行)

子查询的结果是一行（可以是多列），这种查询称为行子查询，常用的操作符：= <> IN NOT IN

sql

-- 行子查询：子查询的返回结果为一行（可以是多列）
-- 查询与“李星云”薪资及直属领导相同的员工信息
-- a.查询李星云的薪资和直属领导的编号
SELECT salary,manage_id FROM emp_query WHERE name = '李星云';
 
-- b.查询所有人薪资为15000，领导编号为2的员工信息
SELECT * FROM emp_query WHERE (salary,manage_id) = (15000,2);
 
-- 合并
SELECT * FROM emp_query WHERE (salary,manage_id) = (SELECT salary,manage_id FROM emp_query WHERE name = '李星云');

表子查询(子查询结果为多行多列)

子查询返回的结果是多行多列，常用操作符：IN

查询入职时间是2008-3-13后的所有员工信息及其部门的信息

sql

-- 表子查询
-- 查询入职时间是2008-3-13后的所有员工信息及其部门的信息
-- a.查询入职时间在2008-3-13后的所有员工信息
SELECT * FROM emp_query WHERE entry_date > '2015-3-13';
 
-- 查询这部分员工对应的部门信息
SELECT * FROM (SELECT * FROM emp_query WHERE entry_date > '2015-3-13') tmp LEFT JOIN dept_query ON tmp.dept_id = dept_query.id;

根据子查询位置，分为:WHERE之后、FROM之后、SELECT之后

多表查询案例

2.9 事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

数据准备

sql

create table  account(
    id int auto_increment primary key  comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表';

insert into  account(id, name, money) VALUES (null,'张三',2000),(null,'李四',2000);

事务操作

查看/设置事务提交方式

sql

SELECT @@autocommit ;
SET @@autocommit=0;

提交事务

sql

COMMIT

回滚事务

sql

ROLLBACK

测试sql

sql

select @@autocommit;

说明是自动提交事务

第一种方法：修改成手动提交set @@autocommit=0; 然后再commit

sql


set @@autocommit=0;
-- 转账操作(张三给李四转账1000)
-- 1.查询张三账户余额
select * from account where name ='张三';

-- 2.将张三账户余额-1000
update account set money=money-1000 where name ='张三';

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name ='李四';


-- 提交事务
commit;

接下来，我们手动报错

我们可以看到程序已经执行，此时我们再查看钱

虽然报错，但是由于事务没有开启，钱依然存在。

执行，事务就结束了

sql

ROLLBACK;

第二种写法。使用transaction。

首先吧事务变成自动的

sql

set @@autocommit=1;

现在不需要关闭自动提交。

sql

start transaction ;
-- 转账操作(张三给李四转账1000)
-- 1.查询张三账户余额
select * from account where name ='张三';

-- 2.将张三账户余额-1000
update account set money=money-1000 where name ='张三';


程序报错了...

-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name ='李四';


commit ;

rollback ;

事务四大特性

原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

操作	描述
脏读	-个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

事务隔离级别

测试sql，语法

sql

-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
-- 设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

首先查看一下mysql的默认事务隔离级别

开始实验脏读

下面展示一下不可重复读

接下来实验，幻读

sql

set session transaction isolation level  repeatable read  ;

Mysql基础篇 ​

1. 入门 ​

1.1 介绍 ​

1.2 环境准备 ​

1.3 图形化界面 ​

2. 基本知识 ​

2.1 SQL的通用语法 ​

2.2 SQL分类 ​

2.3 DDL ​

1) DDL-数据库操作 ​

2) DDL-表操作-查询 ​

3) DDL-表结构-创建 ​

4) DDL-表操作-数据类型 ​

整形 ​

浮点型 ​

定点型 ​

日期类型 ​

字符串 ​

char和varchar ​

5) DDL-表结构-修改 ​

2.4 DML ​

DML-添加数据 ​

DML-修改数据 ​

DDL-删除数据 ​

2.4 DQL ​

DQL-基本查询 ​

DCL-聚合函数 ​

DQL-分组查询 ​

DQL-排序查询 ​

DQL-分页查询 ​

DQL-执行顺序 ​

2.5 DCL ​

DCL-管理用户 ​

DQL-权限控制 ​

2.6 函数 ​

字符串函数 ​

数值函数 ​

日期函数 ​

流程控制函数 ​

2.7 约束 ​

2.8 多表查询 ​

多表关系 ​

一对多 ​

多对多 ​

一对一 ​

多表查询概述 ​

内连接 ​

外连接 ​

自连接 ​

联合查询 ​

子查询 ​

多表查询案例 ​

2.9 事务 ​

事务操作 ​

事务四大特性 ​

并发事务问题 ​

事务隔离级别 ​

Mysql基础篇

1. 入门

1.1 介绍

1.2 环境准备

1.3 图形化界面

2. 基本知识

2.1 SQL的通用语法

2.2 SQL分类

2.3 DDL

1) DDL-数据库操作

2) DDL-表操作-查询

3) DDL-表结构-创建

4) DDL-表操作-数据类型

整形

浮点型

定点型

日期类型

字符串

char和varchar

5) DDL-表结构-修改

2.4 DML

DML-添加数据

DML-修改数据

DDL-删除数据

2.4 DQL

DQL-基本查询

DCL-聚合函数

DQL-分组查询

DQL-排序查询

DQL-分页查询

DQL-执行顺序

2.5 DCL

DCL-管理用户

DQL-权限控制

2.6 函数

字符串函数

数值函数

日期函数

流程控制函数

2.7 约束

2.8 多表查询

多表关系

一对多

多对多

一对一

多表查询概述

内连接

外连接

自连接

联合查询

子查询

多表查询案例

2.9 事务

事务操作

事务四大特性

并发事务问题

事务隔离级别