2023.6.12 03.数据库基本操作

1.数据库连接⽅式

2.SQL语⾔

3.Mysql数据库对应与应⽤

4.数据库基本操作

5.数据库增删查改

6.数据库查询语句

6.1单表查询

6.2多表查询

6.3⼦查询

系统数据库

information_schema(虚拟库)

⽤户表信息、列信息、权限信息、字符信息等

#查询有多少个库

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.SCHEMATA;

+----------+

| COUNT(*) |

+----------+

| 4 |

+----------+

#查询mysql库中有多少个表

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA =

'mysql';

+----------+

| COUNT(*) |

+----------+

| 31 |

+----------+

1 row in set (0.00 sec)

performance_schema

主要存储数据库服务器的性能参数

mysql(授权库)

主要存储系统⽤户的权限信息

sys(优化库)

主要存储数据库服务器的性能参数

wing(业务库) ⾃⼰创建实验的库

主要存放业务所需要的库和表

1.数据库连接⽅式

1.1 ⼯作中常⽤到的三种连接⽅式

Java App + JDBC client（其他语⾔也有，⽐如Python的MySQLdb）

MySQL client

MySQL utility

MySQL utility 是指 MySQL 数据库的⼀组命令⾏⼯具，可以⽤于管理和维护 MySQL 数据库。这些⼯具包括：

1. mysql：连接到 MySQL 数据库服务器并执⾏ SQL 语句的命令⾏客户端。

2. mysqldump：将 MySQL 数据库中的数据导出到⽂件中的命令⾏⼯具。

3. mysqlimport：将数据从⽂件导⼊到 MySQL 数据库中的命令⾏⼯具。

4. mysqladmin：⽤于管理 MySQL 服务器的命令⾏⼯具，如启动、停⽌、重启服务器，以及查看服务器状

态等。

5. mysqlcheck：⽤于检查和修复 MySQL 数据库表的命令⾏⼯具。

6. mysql_upgrade：⽤于升级 MySQL 数据库的命令⾏⼯具。

这些⼯具都是免费的，并且可以在 Windows、Linux 和 Mac OS X 等操作系统上使⽤。

1.2 使⽤应⽤程序连接MySQL

应⽤程序使⽤驱动（connector/driver）客户端连接MySQL

MySQL驱动程序涵盖各种主流语⾔

1.3 使⽤命令⾏连接MySQL

安装MySQL客户端软件包

设置环境变量（Linux）

安装MySQL-client

从软件源安装 sudo yum install mysql-client

验证MySQL的安装

mysql -V

命令⾏连接MySQL的两种⽅式

TCP/IP连接（远程连接）

Socket连接（本地连接）

TCP/IP连接（远程连接）

使⽤ mysql -u root -p 可以连接数据库, 但这只是本地连接数据库的⽅式, 在⽣产很多情况下都是连接⽹络中某

⼀个主机上的数据库

-P //指定连接远程数据库端⼝

-h //指定连接远程数据库地址

-u //指定连接远程数据库账户

-p //指定连接远程数据库密码

[root@sql ~]# mysql -h127.0.0.1 -P3306 -uroot -p

Enter password:

mysql 参数帮助

[root@sql ~]# mysql --help

mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Usage: mysql [OPTIONS] [database]

-?, --help Display this help and exit.

-I, --help Synonym for -?

-h, --host=name Connect to host.

-p, --password[=name]

Password to use when connecting to server. If password is

not given it's asked from the tty.

-P, --port=# Port number to use for connection or 0 for default to, in

order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/services, built-in default (3306).

--protocol=name The protocol to use for connection (tcp, socket, pipe,

memory).

each row on new line.

-S, --socket=name The socket file to use for connection.

-u, --user=name User for login if not current user.

-U, --safe-updates Only allow UPDATE and DELETE that uses keys.

-U, --i-am-a-dummy Synonym for option --safe-updates, -U.

-v, --verbose Write more. (-v -v -v gives the table output format).

-V, --version Output version information and exit.

-w, --wait Wait and retry if connection is down.

...

使⽤Socket连接

# 需要指定socket⽂件和⽤户名、密码

mysql -S /tmp/mysql.sock -uroot -p'Wing@123'

本地连接VS远程连接

本地连接只能在MySQL服务器上创建，常⽤作为MySQL状态检查，或程序和MySQL部署在⼀台机器上。

远程连接在MySQL服务器内外都能连接，适合应⽤服务器和MySQL部署在不同机器上的场景。

使⽤命令⾏连接MySQL的注意事项

socket⼀般存储路径为：/tmp/mysql.sock

# 如果找不到⽂件可以通过tcp连接进来然后通过如下命令查找

show global variables like 'socket';

socket⽂件的权限必须是777

不要将密码直接输⼊在命令⾏⾥，存在安全⻛险！

命令⾏连接MySQL的特点

MySQL命令⾏⾥有丰富的扩展参数

DBA运维管理⼯具⼤多使⽤命令⾏⽅式

多台机器可以同时操作，对于DBA来说⾮常有效率

1.4 连接进⼊之后可以做什么

# 数据库状态

status; #简写 \s

# 展示当前连接

show processlist;

mysql> show processlist;

+----+------+-----------+------+---------+------+----------+------------------+

+----+------+-----------+------+---------+------+----------+------------------+

+----+------+-----------+------+---------+------+----------+------------------+s

Id ：该字段表示当前会话的唯⼀标识符，每个会话都有⼀个唯⼀的 ID。

User ：该字段表示当前会话的⽤户名。

Host ：该字段表示当前会话的主机名或 IP 地址。

db ：该字段表示当前会话正在使⽤的数据库名，如果为 NULL，则表示当前未选择任何数据库。

Command ：该字段表示当前会话正在执⾏的命令类型，如 Query、Sleep、Connect 等。

Time ：该字段表示当前会话已经执⾏的时间，单位为秒。

State ：该字段表示当前会话的状态，如 starting、sending data、locked 等。

Info ：该字段表示当前会话正在执⾏的 SQL 语句或其他信息。

1.5 使⽤图形客户端连接MySQL

常⽤的图形客户端⼯具

Navicat

MySQLWorkBench

#创建⼀个具有 root 权限的⽤户,并授予该⽤户远程连接权限。

CREATE USER 'root'@'%' IDENTIFIED BY 'Wing@123';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

#重新加载权限表，使更改⽣效：

FLUSH PRIVILEGES;

mysql> select user,host from mysql.user;

+---------------+-----------+

| user | host |

+---------------+-----------+

| root | % |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+---------------+-----------+

4 rows in set (0.00 sec)

图形GUI⼯具的优势

操作简单易于上⼿

⽀持图形化的导⼊、导出

可视化界⾯输出，输出可视化

1.6 总结

应⽤程序需要使⽤API接⼝连接MySQL

开发⼯程师可以使⽤图形⼯具连接MySQL

命令⾏客户端才是DBA的最爱

2. SQL语⾔(结构化查询语⾔)

2.1 关系型数据库

数据存放在表中

表的每⼀⾏被称为记录 record

表中所有记录都有相同的字段（列）

mysql> select user,host from mysql.user;

+---------------+-----------+

| user | host | 表头字段列

+---------------+-----------+

| root | % | ⼀⾏称为记录

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+---------------+-----------+

2.2 SQL是什么

Structured Query Language

是⼀种特殊⽬的的编程语⾔，⽤于关系型数据库中的标准数据存取操作

与数据库进⾏沟通的钥匙

2.3 SQL语⾔与数据库

⽤SQL创建表，定义表中的字段

⽤SQL向表中增加，删除，修改记录

⽤SQL从表中查询到想要的记录

⽤SQL操作数据库的⼀切

2.4 SQL语句的分类

1.DDL(数据库定义语⾔ //开发⼈员)

数据库、表、视图、索引、存储过程、函数、CREATE DROP ALTER

2.DML(数据库操作语⾔ //开发⼈员)

插⼊数据 INSERT、删除数据 DELETE、更新数据UPDATE

3.DQL(数据库查询语⾔ //运维、开发⼈员)

查询数据 SELECT

4.DCL(数据库控制语⾔ //运维⼈员)

控制⽤户的访问权限 GRANT、REVOKE

example:

mysql> select user,host from mysql.user;

+---------------+-----------+

| user | host | 表头字段列

+---------------+-----------+

| root | % | ⼀⾏称为记录

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+---------------+-----------+

# 查看当前有哪些数据库

show databases;

#创建数据库

create database wing;

# 使⽤名为wing的数据库

use wing;

# 创建⼀张学⽣表

create table stu(

id int(10),

name varchar(20),

age int(10),

primary key(id));

# 每⼀张表都需要包含⼀个主键，主键唯⼀标识⼀条记录，唯⼀的字段，不可重复不能为空，通过`primary key`关

键字来定义。

# 查看创建好的表

show create table stu;

# 新加⼀个字段

alter table stu add column gender varchar(20);

# 修改⼀个字段

alter table stu modify column gender varchar(40);

# 删除⼀个字段

alter table stu drop column gender;

# 删除表

drop table stu;

# 查看当前数据库中的表

show tables;

# 向表中插⼊数据

insert into stu(id,name,age) values(1,'wing',28);

# 插⼊全部字段时可以只写表名

insert into stu values(2,'nss',29);

# 查看刚才添加的数据,"*"代表查询全部字段

select * from stu;

# 如果只想查询两个字段，则只写要查询的字段名

select name, age from stu;

# 也可以根据某个条件进⾏查询，⽐如只查询id为1的记录

select name age from stu where id=1;

# 更新语句

update stu set age=29 where id=1;

# 删除表中的数据

delete from stu where id=1;

3. MySQL数据库对象与应⽤

3.1 MySQL数据类型

3.1.1 Number不⽌⼀种

整形

浮点型

整形

TINYINT

SMALLINT

MEDIUMINT

INT

BIGINT

数据储存最⼩单位是bit byte Kb Mb Gb 1024

1 Byte = 8bit bit只有0和1 11111111 最⼤ 256

1Byte储存⼤⼩范围

⽆符号:2^7-1=127

有符号: 2^7=-128

⽼⽣常谈的问题

int(11) VS int(21) 存储空间，还是存储范围有区别？

答案是：两者完全⼀样，只是在显示的时候补全0的位数不⼀样。

可以通过下⾯的例⼦来验证

create table t(a int(11) zerofill, b int(21) zerofill);

insert into t values (1, 1);

select * from t;

括号中的数字并不表示存储空间或存储范围的⼤⼩，⽽是⽤于指定显示宽度，即在显示查询结果时，为了对

⻬和美观，会在数字前⾯补上空格或 0，使得所有数字的显示宽度都相同。因此， int(11) 和 int(21) 在

存储空间和存储范围上没有区别，只是在显示的时候补全 0 的位数不同。

3.1.2 浮点型

FLOAT(M, D)

DOUBLE(M, D)

在 MySQL 中， FLOAT(M,D) 中的 M 和 D 分别表示浮点数的总位数和⼩数位数。其中， M 的范围是从 1 到

24， D 的范围是从 0 到 23。例如， FLOAT(8,2) 表示⼀个 8 位浮点数，其中有 2 位⼩数。在这种情况下，

该浮点数的取值范围为 -999999.99 到 999999.99。需要注意的是， FLOAT 类型的精度是不确定的，因此在

进⾏精确计算时，建议使⽤ DECIMAL 类型。

精度丢失问题

精度丢失

⼀个例⼦：

drop table t;

create table t(a int(11), b float(7, 4));

insert into t values (2, 123.12345);

select * from t;

3.1.3 DECIMAL 定点数-更精确的数字类型

DECIMAL类型确实是⼀种更精确的数字类型，适⽤于需要⾼精度计算的场景，⽐如货币交易等。DECIMAL(M,N)

中，M代表总精度，N代表⼩数点右侧的位数（标度）。

DECIMAL

⾼精度的数据类型，常⽤来存储交易相关的数据

DECIMAL(M,N).M代表总精度，N代表⼩数点右侧的位数（标度）

1 < M < 254, 0 < N < 60;

存储空间变⻓

3.1.4 经验之谈

存储性别、省份、类型等分类信息时选择TINYINT、char(1)或者ENUM

BIGINT存储空间更⼤，INT和BIGINT之间通常选择BIGINT

交易等⾼精度数据选择使⽤DECIMAL

3.2 字符型

3.2.1 存储⽤户名的属性

CHAR

VARCHAR

TEXT

3.2.2 CAHR与VARCHAR

CHAR和VARCHAR存储的单位都是字符

CHAR存储定⻓，容易造成空间的浪费

VARCHAR存储变⻓，节省存储空间

3.2.3 字符与字节的区别

字符和字节是两个不同的概念。⼀个字符可以由⼀个或多个字节组成，具体取决于所使⽤的字符集和编码⽅

式。在MySQL中，常⽤的字符集包括GBK、UTF-8、UTF-8MB4等。

GBK是双字节编码，UTF-8是三字节编码，UTF-8MB4是四字节编码。

UTF8MB4 是 MySQL 数据库中⼀种字符编码⽅式，它是 UTF8 编码的⼀个扩展，⽀持更⼴泛的字符集。

UTF8MB4 中的 "MB4" 表示 "4 bytes maximum"，也就是说它可以⽀持 4 字节⻓的字符，⽽ UTF8 只能⽀持

3 字节⻓的字符。

对于UTF8MB4号称占⽤四字节但是并不绝对。如果在utf8可以覆盖到的范围则仍然占⽤3字节。

UTF8MB4 编码最有优势的应⽤场景是⽤于 emoji 表情在内的各种字符，这些字符在传统的 UTF8 编码中⽆

法表示。同时，UTF8MB4 也可以⽤来存储各种语⾔的⽂字，包括中⽂、⽇⽂、韩⽂等等。

a = 96

1byte=8bit = 256

3.2.4 emoji表情

MySQL版本 > 5.5.3

JDBC驱动版本 > 5.1.13

库和表的编码设为utf8mb4

3.2.5 TEXT与CHAR和VARCHAR的区别

CHAR和VARCHAR存储单位为字符

TEXT存储单位为字节，总⼤⼩为65535字节，约为64KB

CHAR数据类型最⼤为255字符

VARCHAR数据类型为变⻓存储，可以超过255个字符

TEXT在MySQL内部⼤多存储格式为溢出⻚，效率不如CHAR

⼀个例⼦

drop table t;

create table t (a char(256));

create table t (a varchar(256));

3.2.6 存储头像

BLOB

BINARY

在MySQL中，BLOB和BINARY都是⽤来存储⼆进制数据的数据类型。

BLOB是Binary Large Object（⼆进制⼤对象）的缩写，⽤来存储⼤型⼆进制数据，例如图像、⾳频、视频等⽂

件。BLOB类型可以存储最⼤为65,535字节的数据，如果需要存储更⼤的数据，需要使⽤MEDIUMBLOB、

LONGBLOB等类型。

BINARY是⽤来存储固定⻓度的⼆进制数据，例如加密密钥等。BINARY类型可以存储最⼤为255字节的数据，如果

需要存储更⼤的数据，需要使⽤VARBINARY类型。

性能太差，不推荐

3.2.7 经验之谈

CHAR与VARCHAR定义的⻓度是字符⻓度不是字节⻓度

存储字符串推荐使⽤VARCHAR(N),N尽量⼩

虽然数据库可以存储⼆进制数据，但是性能低下，不要使⽤数据库存储⽂件⾳频等⼆进制数据分布式储存系

统ceph minio

3.3 时间类型

3.3.1 存储⽣⽇信息

DATE

TIME

DATETIME

TIMESTAMP

3.3.2 时间类型的区别在哪⾥

存储空间上的区别

DATE三字节，如：2015-05-01

TIME三字节，如：11:12:00

TIMESTAMP，如：2015-05-01 11::12:00

DATETIME⼋字节，如：2015-05-01 11::12:00

存储精度的区别

DATE精确到年⽉⽇

TIME精确到⼩时分钟和秒

TIMESTAMP、DATETIME都包含上述两者

3.3.3 TIMESTAMP VS DATETIME

存储范围的区别

TIMESTAMP存储范围：1970-01-01 00::00:01 to 2038-01-19 03:14:07

DATETIME的存储范围：1000-01-01 00:00:00 to 9999-12-31 23:59:59

MySQL在5.6.4版本之后，TimeStamp和DateTime⽀持到微妙

字段类型与市区的关联关系

TIMESTAMP会根据系统时区进⾏转换，DATETIME则不会

3.3.4 字段类型和时区的关系

国际化的系统

⼀个例⼦：

create table test (a datetime, b timestamp);

select now();

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

select * from test;

set time_zone = '+00:00';

select * from test;

#调整时间为默认东⼋区

set time_zone = '+08:00'

set time_zone = '+00:00';

这是MySQL中设置时区的语句，将时区设置为UTC（协调世界时）+0:00，也就是格林威治标准时间。在

MySQL中，时区的设置会影响到⽇期和时间的存储和显示，因此在使⽤MySQL时，需要根据实际情况设置合

适的时区。可以根据⾃⼰所在的时区，设置不同的时区参数，例如set time_zone = '+08:00'可以将时区设置

为东⼋区。

3.3.5 BIGINT如何存储时间类型

应⽤程序将时间转换为数字类型(时间戳)

3.4 类型总结

基本上我们学习任何关于数据库或者开发语⾔都存在数据类型

数字: 整数、浮点数Float 、DECIMAL

字符串: char varchar TEXT

时间:TIME、DATE 、TIMESTAMP、DATETIME

等等

3.4 MySQL数据对象

3.4.1 MySQL常⻅的数据对象有哪些

DataBase/Schema

Table

Index

View/Trigger/Function/Procedure

3.4.2 库、表、⾏层级关系

⼀个DataBase对应⼀个Schema

⼀个Schema包含⼀个或多个表

⼀个表⾥⾯包含⼀个或多个字段

⼀个表⾥包含⼀条或多条记录

⼀个表包含⼀个或多个索引

3.4.3 多DataBase⽤途

业务隔离

资源隔离

3.4.4表上有哪些常⽤的数据对象

索引

约束

视图、触发器、函数、存储过程

4.数据库基本操作

4.1 查看数据库信息版本

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.7.30 |

+-----------+

1 row in set (0.01 sec)

mysql> \s

--------------

mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper

Connection id: 5

Current database:

Current user: root@localhost

SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.30 MySQL Community Server (GPL)

Protocol version: 10

Connection: 127.0.0.1 via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

Uptime: 26 min 45 sec

Threads: 1 Questions: 300 Slow queries: 0 Opens: 452 Flush tables: 1 Open tables:

223 Queries per second avg: 0.186

mysql常⽤特殊命令

\h ：显示帮助信息，包括所有可⽤的 MySQL 命令和特殊命令

\c ：清除当前输⼊的命令

\q ：退出 MySQL 命令⾏⼯具。

\G ：将查询结果按列格式化，并以每⾏⼀个字段的⽅式显示。

\t：将查询结果转换为纯⽂本格式。

\n：将查询结果转换为纯⽂本格式，并在每⾏末尾添加换⾏符。

\d：设置语句分隔符

4.3 创建数据库DDL

注意

--------------

#其他⽅式查看等等

[root@sql ~]# mysql -V

mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper

[root@sql ~]# mysql -uroot -p'Wing@123'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.7.30 MySQL Community Server (GPL)

[root@sql ~]# mysql --help

mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper

[root@sql ~]# mysqladmin --help

mysqladmin Ver 8.42 Distrib 5.7.30, for linux-glibc2.12 on x86_64

`mysqladmin` 的版本信息。具体解释如下：

- `Ver 8.42` 表示版本号为 8.42。

- `Distrib 5.7.30` 表示该版本是基于 MySQL 5.7.30 发⾏的。

- `for linux-glibc2.12` 表示该版本是为 Linux 操作系统和 glibc2.12 运⾏时库编译的。

- `on x86_64` 表示该版本是为 64 位 x86 架构的处理器编译的。

4.2 mysql常⽤特殊命令

\h ：显示帮助信息，包括所有可⽤的 MySQL 命令和特殊命令

\c ：清除当前输⼊的命令

\q ：退出 MySQL 命令⾏⼯具。

\G ：将查询结果按列格式化，并以每⾏⼀个字段的⽅式显示。

\t：将查询结果转换为纯⽂本格式。

\n：将查询结果转换为纯⽂本格式，并在每⾏末尾添加换⾏符。

\d：设置语句分隔符

4.3 创建数据库DDL

注意

--------------

#其他⽅式查看等等

[root@sql ~]# mysql -V

mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper

[root@sql ~]# mysql -uroot -p'Wing@123'

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.7.30 MySQL Community Server (GPL)

[root@sql ~]# mysql --help

mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper

[root@sql ~]# mysqladmin --help

mysqladmin Ver 8.42 Distrib 5.7.30, for linux-glibc2.12 on x86_64

`mysqladmin` 的版本信息。具体解释如下：

- `Ver 8.42` 表示版本号为 8.42。

- `Distrib 5.7.30` 表示该版本是基于 MySQL 5.7.30 发⾏的。

- `for linux-glibc2.12` 表示该版本是为 Linux 操作系统和 glibc2.12 运⾏时库编译的。

- `on x86_64` 表示该版本是为 64 位 x86 架构的处理器编译的。

mysql> create database wing;

Query OK, 1 row affected (0.00 sec)

//以分号结尾

o数据库名称严格区分⼤⼩写

数据库名称必须是唯⼀

数据库名称不允许使⽤数字

数据库名称不能使⽤关键字命名 create select

4.4 查看当前的库内容

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| wing |

+--------------------+

5 rows in set (0.00 sec)

//执⾏命令不区分⼤⼩写

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

| wing |

+--------------------+

5 rows in set (0.00 sec)

4.5 删除数据库

mysql> drop database wing;

Query OK, 0 rows affected (0.07 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.01 sec)

//删除库下的表

mysql> drop table wing.t1;

4.6 查询某个库的表

//use进⼊对应库

mysql> use wing;

Database changed

//列出当前库下⾯的表

mysql> show tables;

Empty set (0.00 sec)

//查询某个库下的表结构

mysql> desc mysql.slow_log;

+----------------+---------------------+------+-----+----------------------+-----------

---------------------+

+----------------+---------------------+------+-----+----------------------+-----------

---------------------+

| rows_sent | int(11) | NO | | NULL |

| rows_examined | int(11) | NO | | NULL |

| db | varchar(512) | NO | | NULL |

| last_insert_id | int(11) | NO | | NULL |

| insert_id | int(11) | NO | | NULL |

+----------------+---------------------+------+-----+----------------------+-----------

---------------------+

12 rows in set (0.00 sec)

//查看某张表的建表语句

mysql> show create table mysql.slow_log\G

*************************** 1. row ***************************

Table: slow_log

Create Table: CREATE TABLE `slow_log` (

`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE

CURRENT_TIMESTAMP(6),

`user_host` mediumtext NOT NULL,

`query_time` time(6) NOT NULL,

`lock_time` time(6) NOT NULL,

`rows_sent` int(11) NOT NULL,

`rows_examined` int(11) NOT NULL,

`db` varchar(512) NOT NULL,

`last_insert_id` int(11) NOT NULL,

`insert_id` int(11) NOT NULL,

`server_id` int(10) unsigned NOT NULL,

`sql_text` mediumblob NOT NULL,

`thread_id` bigint(21) unsigned NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

1 row in set (0.00 sec)

5.数据库增删查改

在 MySQL 管理软件中, 可以通过 SQL 语句中的 DML 语⾔来实现数据的操作, 包括如下:

INSERT 数据插⼊

UPDATE 数据更新

DELETE 数据删除

1.准备操作环境数据表

#创建数据⽂件

mysql> create database wing;

mysql> use wing;

mysql> create table t1(id int,

name varchar(10),

sex enum('man','gril'),

age int);

#查看表字段

mysql> desc t1;

+-------+--------------------+------+-----+---------+-------+

+-------+--------------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| sex | enum('man','gril') | YES | | NULL | |

| age | int(11) | YES | | NULL | |

+-------+--------------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

2.插⼊数据 INSERT 语句

#1.插⼊完整数据, 顺序插⼊: INSERT INTO 表名(字段1,字段2,字段n) VALUES (值1,值2,值 n);

mysql> insert into t1(id,name,sex,age) values ("1","wing","man","18");

Query OK, 1 row affected (0.01 sec)

#1.插⼊完整数据, 推荐⽅式 INSERT INTO 表名 VALUES (值1,值2,值n);

mysql> insert into t1 values("2","wing1","gril","10");

Query OK, 1 row affected (0.01 sec)

#2.指定字段插⼊, INSERT INTO 表名(字段2,字段3…) VALUES (值 2,值3…);

mysql> insert into t1(name,sex,age) values ("wing2","man","20");

Query OK, 1 row affected (0.00 sec)

#3.插⼊多条记录, INSERT INTO 表名 VALUES (值1,值2,值n),(值1,值2,值n);

mysql> insert into t1 values

("3","wing3","man","18"),

("4","wing4","man","18"),

("5","wing5","man","18");

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;

+------+-------+------+------+

| id | name | sex | age |

+------+-------+------+------+

| 1 | wing | man | 18 |

| 2 | wing1 | gril | 10 |

| NULL | wing2 | man | 20 |

| 3 | wing3 | man | 18 |

| 4 | wing4 | man | 18 |

| 5 | wing5 | man | 18 |

+------+-------+------+------+

6 rows in set (0.00 sec)

3.更新数据 UPDATE 语句

//语法: 更新表设置字段1=值1, 字段2=值2, WHERE 条件;

1.查看需要修改的表的字段 desc

2.查询对⽤的字段 select

3.更新对应的表字段 update

4.添加对应的where条件,精准修改

//示例1: 将t1表中, name字段等于wing1的改为update_w1

mysql> update t1 set name="update_w1" where name="wing1";

Query OK, 2 rows affected (0.01 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from t1;

+------+-----------+------+------+

| id | name | sex | age |

+------+-----------+------+------+

| 1 | wing | man | 18 |

| 2 | update_w1 | gril | 10 |

| NULL | wing2 | man | 20 |

| 3 | wing3 | man | 18 |

| 4 | wing4 | man | 18 |

| 5 | wing5 | man | 18 |

+------+-----------+------+------+

6 rows in set (0.00 sec)

//示例2: 修改密码示例, 查看表字段内容

mysql> select user,host,authentication_string from mysql.user;

+---------------+-----------+-------------------------------------------+

| user | host | authentication_string |

+---------------+-----------+-------------------------------------------+

| root | localhost | *4927A5B79C852EA2CE585A7679C5C26DF683F18C |

| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

+---------------+-----------+-------------------------------------------+

3 rows in set (0.00 sec)

//更新字段

mysql> update mysql.user set

authentication_string=password("Wing@123")

where user='root' and host='localhost';

Query OK, 0 rows affected, 1 warning (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

4.删除数据 DELETE

语法: DELETE FROM 表名 WHERE CONITION;

//删除字段包含update_wing

mysql> delete from t1 where name="update_w1";

Query OK, 2 rows affected (0.01 sec)

mysql> select * from t1;

+------+-------+------+------+

| id | name | sex | age |

+------+-------+------+------+

| 1 | wing | man | 18 |

| NULL | wing2 | man | 20 |

| 3 | wing3 | man | 18 |

| 4 | wing4 | man | 18 |

| 5 | wing5 | man | 18 |

+------+-------+------+------+

5 rows in set (0.00 sec)

//清空表数据

mysql> truncate t1;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;

Empty set (0.00 sec)

6.数据库查询语句

6.1单表查询

在学习查询前, 需要定义好对应数据进⾏查询

编号 id int

姓名 name varchar(30)

性别 sex enum

⽇期 time date

职位 post varchar(50)

描述 job varchar(100)

薪⽔ salary double(15,2)

办公区域: office

部⻔编号 dep_id int

#创建表

mysql> CREATE TABLE wing.t2(

id int primary key AUTO_INCREMENT not null,

name varchar(30) not null,

sex enum('man','gril') default 'man' not null,

time date not null,

post varchar(50) not null,

job varchar(100),

salary double(15,2) not null,

office int,

dep_id int );

#插⼊数据

mysql> insert into wing.t2(name,sex,time,post,job,salary,office,

dep_id) values

('jack','man','20230502','instructor','teach',5000,501,100),

('tom','man','20230503','instructor','teach',5500,501,100),

('robin','man','20230502','instructor','teach',8000,501,100),

('alice','gril','20230502','instructor','teach',7200,501,100),

('wing','man','20230502','hr','hrcc',600,502,101),

('harry','man','20230502','hr', NULL,6000,502,101),

('trf','gril','20230506','sale','salecc',20000,503,102),

('test','gril','20230505','sale','salecc',2200,503,102),

('dog','man','20230505','sale', NULL,2200,503,102),

('alex','man','20230505','sale','',2200,503,102)

1.简单查询

//查看表字段与表信息

mysql> desc t2;

+--------+--------------------+------+-----+---------+----------------+

+--------+--------------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| sex | enum('man','gril') | NO | | man | |

| job | varchar(100) | YES | | NULL | |

| office | int(11) | YES | | NULL | |

| dep_id | int(11) | YES | | NULL | |

+--------+--------------------+------+-----+---------+----------------+

9 rows in set (0.00 sec)

#1.查询所有数据

mysql> select * from t2;

+----+-------+------+------------+------------+--------+----------+--------+--------+

+----+-------+------+------------+------------+--------+----------+--------+--------+

| 1 | jack | man | 2023-05-02 | instructor | teach | 5000.00 | 501 | 100 |

| 2 | tom | man | 2023-05-03 | instructor | teach | 5500.00 | 501 | 100 |

| 3 | robin | man | 2023-05-02 | instructor | teach | 8000.00 | 501 | 100 |

| 4 | alice | gril | 2023-05-02 | instructor | teach | 7200.00 | 501 | 100 |

| 5 | wing | man | 2023-05-02 | hr | hrcc | 600.00 | 502 | 101 |

| 6 | harry | man | 2023-05-02 | hr | NULL | 6000.00 | 502 | 101 |

| 7 | trf | gril | 2023-05-06 | sale | salecc | 20000.00 | 503 | 102 |

| 8 | test | gril | 2023-05-05 | sale | salecc | 2200.00 | 503 | 102 |

| 9 | dog | man | 2023-05-05 | sale | NULL | 2200.00 | 503 | 102 |

| 10 | alex | man | 2023-05-05 | sale | | 2200.00 | 503 | 102 |

+----+-------+------+------------+------------+--------+----------+--------+--------+

10 rows in set (0.00 sec)

#2.指定字段查询

mysql> select name,salary,dep_id from t2;

+-------+----------+--------+

+-------+----------+-----------+

| name | salary | salary*14 |

+-------+----------+-----------+

| jack | 5000.00 | 70000.00 |

2.单条件查询

单条件查询

多条件查询

关键字 BETWEEN AND

关键字 IS NULL

关键字 IN 集合查询

关键字 LIKE 模糊查询

#1.单条件查询

mysql> select name,post from t2 where post='hr';

+-------+------+

| name | post |

+-------+------+

| wing | hr |

| harry | hr |

+-------+------+

2 rows in set (0.00 sec)

#2.多条件查询

mysql> select name,post,salary from t2 where post='hr' and salary >5000;

+-------+------+---------+

| name | post | salary |

+-------+------+---------+

| harry | hr | 6000.00 |

+-------+------+---------+

1 row in set (0.00 sec)

#3.查找薪资范围在8000-2000,使⽤BETWEEN区间

mysql> select name,salary from t2 where salary between 8000 and 20000;

+-------+----------+

| name | salary |

+-------+----------+

| robin | 8000.00 |

| trf | 20000.00 |

+-------+----------+

2 rows in set (0.00 sec)

#4.查找部⻔为Null, 没有部⻔的员⼯

mysql> select name,job from t2 where job is null;

+-------+------+

| name | job |

+-------+------+

| harry | NULL |

| dog | NULL |

+-------+------+

2 rows in set (0.00 sec)

#查找有部⻔的员⼯

mysql> select name,job from t2 where job is not null;

+-------+--------+

| name | job |

+-------+--------+

| jack | teach |

| tom | teach |

| robin | teach |

| alice | teach |

| wing | hrcc |

| trf | salecc |

| test | salecc |

| alex | |

+-------+--------+

8 rows in set (0.00 sec)

#查看部⻔为空的员⼯

mysql> select name,job from t2 where job='';

+------+------+

| name | job |

+------+------+

| alex | |

+------+------+

1 row in set (0.00 sec)

#5.集合查询

mysql> select name,salary from t2 where salary=4000 OR salary=5000 OR salary=8000;

mysql> select name,salary from t2 where salary in(4000,5000,8000);

+-------+---------+

| name | salary |

+-------+---------+

| jack | 5000.00 |

| robin | 8000.00 |

+-------+---------+

2 rows in set (0.01 sec)

#6.模糊查询like, 通配符%

mysql> select * from t2 where name like 'al%';

+----+-------+------+------------+------------+-------+---------+--------+--------+

+----+-------+------+------------+------------+-------+---------+--------+--------+

| 4 | alice | gril | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |

| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |

+----+-------+------+------------+------------+-------+---------+--------+--------+

2 rows in set (0.00 sec)

#通配符__

mysql> select * from t2 where name like 'al__';

+----+------+-----+------------+------+------+---------+--------+--------+

+----+------+-----+------------+------+------+---------+--------+--------+

| 10 | alex | man | 2018-02-05 | sale | | 2200.00 | 503 | 102 |

+----+------+-----+------------+------+------+---------+--------+--------+

1 row in set (0.00 sec)

3.查询排序

单列排序

多列排序

#1.按单列排序, 按薪⽔从低到⾼排序, 默认ASC

mysql> select * from t2 ORDER BY salary ASC;