MySQL基础

数据管理演变

1. 人工档案管理
2. 文件系统管理
3. 数据库管理

数据库管理系统

1. 层次数据库
2. 网格型数据库
3. 关系型数据库
4. 对象型数据库
5. 非关系型数据库

概述
数据库是一个长期存储在计算机内的、有组织的、有共享的、统一管理的数据集合.
在数据库中的数据我们可以用二维表格的形式理解,例如
表格中一列的标题我们称之为字段
表格中一行数据我们称之为一条记录
1
2
3
4
5

数据库由3部分组成
数据库：用于存储数据
数据库管理系统：用户管理数据库的软件
数据库应用程序：为了提高数据库系统的管理能力使用的数据库管理的补充

1
2
3
4
5

对数据库进行查询、修改操作的语言我们称之为SQL
结构化查询语言(Structured Query Language)简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
标准：SQL-92、SQL-99、SQL-2003方言
常用关系型数据库
Oracle、MySQL、SQLServer、...
对象型数据库
PostgreSQL、...
常用的非关系数据库（在中国）
Redis、MongoDB、HBase、...
按照SQL-92标准SQL包含4部分
1、数据库定义语言(DDL)：create、drop、alter、...
2、数据库操作语言(DML)：insert、update、delete、...
3、数据库查询语言(DQL)：select
4、数据库控制语言(DCL)：commit、rollback、grant、revoke、...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

安装MySQL

华为欧拉openEuler在线安装MySQL8

升级系统

sudo yum -y update
1

安装依赖和常用工具

sudo yum -y install vim net-tools wget gcc make cmake lrzsz
1

将 MySQL Yum 存储库添加到系统的存储库列表中

sudo yum -y install https://repo.mysql.com//mysql80-community-release-el8-4.noarch.rpm
1

通过运行以下命令并检查其输出来验证是否已启用和禁用正确的子存储库

sudo yum repolist enabled | grep mysql
1

通过以下命令安装 MySQL

sudo yum -y install mysql-community-server
1

启动 MySQL 服务器

使用以下命令启动 MySQL 服务器：

sudo systemctl start mysqld
1

您可以使用以下命令检查 MySQL 服务器的状态：

sudo systemctl status mysqld
1

使用以下命令开启自启动 MySQL 服务器：

sudo systemctl enable mysqld
1

在服务器初始启动时，假设服务器的数据目录为空，会发生以下情况：

• 服务器已初始化。

• SSL 证书和密钥文件在数据目录中生成。

• validate_password 已安装并启用。

• 创建了一个超级用户帐户'root'@'localhost。超级用户的密码已设置并存储在错误日志文件中。要显示它，请使用以下命令：

  $> sudo grep 'temporary password' /var/log/mysqld.log
  1

  通过使用生成的临时密码登录并为超级用户帐户设置自定义密码，尽快更改 root 密码：

  mysql -uroot -p
  1

修改密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
1
2

笔记

validate_password 默认安装。实现的默认密码策略validate_password要求密码至少包含1个大写字母、1个小写字母、1个数字和1个特殊字符，密码总长度至少为8个字符。

validate_password检查语句中的明文密码。在要求密码长度至少为 8 个字符的默认密码策略下，密码很弱并且语句会产生错误：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'Lihaozhe!!@@1122';
1

不检查指定为散列值的密码，因为原始密码值不可用于检查：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Lihaozhe!!@@1122';
FLUSH PRIVILEGES;
1
2

update mysql.user set host = '%',plugin='mysql_native_password' where user='root';
FLUSH PRIVILEGES;
1
2

exit;
1

sudo systemctl restart mysqld
1

# 使用新密码连接
mysql -h 你自己的IP地址 -uroot -p
1
2

防火墙开放端口

–zone #作用域

–add-port=1935/tcp #添加端口，格式为：端口/通讯协议

–permanent #永久生效，没有此参数重启后失效

firewall-cmd --zone=public --add-port=3306/tcp --permanent
1

重启防火墙

firewall-cmd --reload 
1

Deepin UOS 离线安装MySQL8

升级系统

sudo apt update
sudo apt -y dist-upgrade 
1
2

安装依赖和常用工具

sudo apt -y install vim net-tools wget gcc make cmake lrzsz
sudo apt -y install libmecab2 libjson-perl 
1
2

下载离线安装包

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-server_8.0.29-1debian10_amd64.deb-bundle.tar
1

解压缩安装包

mkdir mysql & tar -xvf mysql-server_8.0.29-1debian10_amd64.deb-bundle.tar -C mysql cd mysql
1

安装MySQL8

cd mysql 
sudo dpkg -i mysql-{common,community-client,community-client-core,community-client-plugins,client,community-server,community-server-core,server}_*.deb
1
2

修改密码和可访问主机地址

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

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
flush privileges;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

update mysql.user set host = '%',plugin='mysql_native_password' where user='root';
flush privileges;

quit;

sudo systemctl restart mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14

新建用户和授权

-- 创建密码为 Lihaozhe!!@@6633 的用户 lhz 可以通过 本机访问 
create user 'lhz'@'localhost' identified by 'Lihaozhe!!@@6633';
-- 授权 lhz 用户 可以通过本机 访问 所有数据中所有表 
grant all privileges on *.* to 'lhz'@'localhost' with grant option;
-- 刷新权限
flush privileges;
1
2
3
4
5
6

-- 创建密码为 Lihaozhe!!@@6633 的用户 lhz 可以通过 任意地址访问 密码策略为 mysql_native_password
create user 'lhz'@'%' identified with mysql_native_password by 'Lihaozhe!!@@6633';
-- 授权 lhz 用户 可以通过本机 访问 所有数据中所有表 
grant all privileges on *.* to 'lhz'@'%' with grant option;
-- 刷新权限
flush privileges;
1
2
3
4
5
6

-- 如果遇到 ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
-- 原因是由于root用户没有SYSTEM_USER权限，把权限加入后即可解决：
grant system_user on *.* to 'root';
1
2
3

MySQL忘记密码

1、编辑MySQL配置文件

# openEuler
sudo vim /etc/my.cnf

# Deepin UOS 
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
1
2
3
4
5

2、在配置文件中的[mysqld]下加入skip-grant-tables

openEuler配置如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

skip-grant-tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Deepin UOS 配置如下：

# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log

skip-grant-tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

3、重启MySQL服务

# openEuler
sudo systemctl restart mysqld

# Deepin UOS
sudo systemctl restart mysql
1
2
3
4
5

4、进入MySQL

sudo mysql
1

5、刷新权限

FLUSH PRIVILEGES;
1

6、修改密码

select user,host from mysql.user where user = 'root';

-- 如果查询结果host的值为 localhost 执行
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Lihaozhe!!@@6633';

-- 如果查询结果host的值为 % 执行
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Lihaozhe!!@@6633';

-- 如果遇到 ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
-- 先执行 FLUSH PRIVILEGES; 再修改密码

-- 如果遇到 ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
-- 原因是由于root用户没有SYSTEM_USER权限，把权限加入后即可解决：
grant system_user on *.* to 'root';
1
2
3
4
5
6
7
8
9
10
11
12
13
14

7、刷新权限

FLUSH PRIVILEGES;
1

8、退出MySQL

exit;
1

9、编辑MySQL配置文件

sudo vim /etc/my.cnf
1

10、在配置文件中注释 skip-grant-tables

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# skip-grant-tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

11、重启MySQL服务

sudo systemctl restart mysqld
1

12、使用新密码访问数据库

sudo mysql -uroot -p
1

数据库常用操作

登录数据库

mysql -uroot -p
1

提示信息如下：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.





**deppin mysql** 

sudo apt-get -y install libmecab2 libjson-perl 
sudo dpkg -i mysql-{common,community-client,community-client-core,community-client-plugins,client,community-server,community-server-core,server}_*.deb

mysql -h localhost -uroot -p

use mysql;

select host , user , plugin ,authentication_string from user;

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
flush privileges;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

update mysql.user set host = '%',plugin='mysql_native_password' where user='root';
flush privileges;

quit;

sudo systemctl restart mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

查看所有数据库

show databases;
1

提示信息如下：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

创建数据库

create database `day01`;
1

提示信息如下：

Query OK, 1 row affected (0.00 sec)
1

show databases;
1

+--------------------+
| Database           |
+--------------------+
| day01              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11

查看数据库创建详细过程

show create database `day01`;
1

提示信息如下：

或者：

show create database `day01`\G
1

提示信息如下：

创建数据库同时指定字符集

CREATE DATABASE `day011` DEFAULT CHARACTER SET gbk;
1

SHOW CREATE DATABASE `day011`;
1

提示信息如下：

删除数据库

drop database `day011`;
1

提示信息如下：

Query OK, 0 rows affected (0.00 sec)
1

查看当前使用的数据库

select database();
1

提示信息如下：

+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

切换数据库

use `day01`;
1

提示信息如下：

Database changed
1

select database();
1

+------------+
| database() |
+------------+
| day01      |
+------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

数据表操作

创建数据表

use `day01`;
1

create table `user` (
    `id` int(3) comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名'
);
1
2
3
4
5
6

提示信息如下：

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

查看当前数据库中的所有表

show tables;
1

提示信息如下：

+-----------------+
| Tables_in_day01 |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

查看数据标表创建详细过程

show create table `user`;
1

show create table `user`\G
1

查询MySQL数据库支持的数据库引擎

show engines;
1

删除数据表

drop table `user`;
1

提示信息如下：

Query OK, 0 rows affected (0.01 sec)
1

创建指定引擎和字符集的数据表

create table `user` (
    `id` int(3) comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名'
) engine=myisam default charset=gbk;
1
2
3
4
5
6

查看数据表结构

describe `user`;
1

提示信息如下：

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| account  | varchar(11) | YES  |     | NULL    |       |
| password | varchar(64) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10

或者

describe `user`\G
1

*************************** 1. row ***************************
  Field: id
   Type: int
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 2. row ***************************
  Field: account
   Type: varchar(11)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 3. row ***************************
  Field: password
   Type: varchar(64)
   Null: YES
    Key: 
Default: NULL
  Extra: 
*************************** 4. row ***************************
  Field: nickname
   Type: varchar(50)
   Null: YES
    Key: 
Default: NULL
  Extra: 
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

向数据表中插入记录

插入单条完整记录

方式一：

insert into `user` (`id`,`account`,`password`,`nickname`) value (1,'a1','123456','u1');
1

提示信息如下：

`day01` Query OK, 1 row affected (0.01 sec)
1

检查插入数据是内容

select * from `user`;
1

查询结果如下：

+------+---------+----------+----------+
| id   | account | password | nickname |
+------+---------+----------+----------+
|    1 | a1      | 123456   | u1       |
+------+---------+----------+----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

方式二：

insert into `user` value (2,'a2','123456','u2');
1

select * from `user`;
1

+------+---------+----------+----------+
| id   | account | password | nickname |
+------+---------+----------+----------+
|    1 | a1      | 123456   | u1       |
|    2 | a2      | 123456   | u2       |
+------+---------+----------+----------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8

插入多条完整记录

insert into `user` values (3,'a3','123456','u3'),(4,'a4','123456','u4'),(5,'a5','123456','u5');
1

提示信息如下：

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
1
2

select * from `user`;
1

+------+---------+----------+----------+
| id   | account | password | nickname |
+------+---------+----------+----------+
|    1 | a1      | 123456   | u1       |
|    2 | a2      | 123456   | u2       |
|    3 | a3      | 123456   | u3       |
|    4 | a4      | 123456   | u4       |
|    5 | a5      | 123456   | u5       |
+------+---------+----------+----------+
5 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11

指定字段插入数据

insert into `user` (`account`,`password`) value ('a1','123456');
1

select * from `user`;
1

+------+---------+----------+----------+
| id   | account | password | nickname |
+------+---------+----------+----------+
|    1 | a1      | 123456   | u1       |
|    2 | a2      | 123456   | u2       |
|    3 | a3      | 123456   | u3       |
|    4 | a4      | 123456   | u4       |
|    5 | a5      | 123456   | u5       |
| NULL | a1      | 123456   | NULL     |
+------+---------+----------+----------+
6 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

约束

默认约束 DEFAULT

当插入数据的时候具有默认约束的字段在没有赋值的情况下或有一个默认值

create table `user` (
    `id` int(3) comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) default 'lihaozhe' comment '用户密码',
    `nickname` varchar(50) comment '用户姓名'
);
1
2
3
4
5
6

describe `user`;
1

+----------+-------------+------+-----+----------+-------+
| Field    | Type        | Null | Key | Default  | Extra |
+----------+-------------+------+-----+----------+-------+
| id       | int         | YES  |     | NULL     |       |
| account  | varchar(11) | YES  |     | NULL     |       |
| password | varchar(64) | YES  |     | lihaozhe |       |
| nickname | varchar(50) | YES  |     | NULL     |       |
+----------+-------------+------+-----+----------+-------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

insert into `user` (`id`,`account`,`nickname`) value (1,'a1','u1');
1

select * from user;
1

+------+---------+----------+----------+
| id   | account | password | nickname |
+------+---------+----------+----------+
|    1 | a1      | lihaozhe | u1       |
+------+---------+----------+----------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7

非空约束 NOT NULL

向数据表中插入记录的时候 具有 NOT NULL 约束的字段必须赋值

create table `user` (
    `id` int(3) not null comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名'
);
1
2
3
4
5
6

describe `user`;
1

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   |     | NULL    |       |
| account  | varchar(11) | YES  |     | NULL    |       |
| password | varchar(64) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)


1
2
3
4
5
6
7
8
9
10
11

insert into `user` (`account`,`nickname`) value ('a1','u1');
1

提示信息如下：

ERROR 1364 (HY000): Field 'id' doesn't have a default value
1

唯一性约束 unique

向数据表中插入记录的时候 具有 unique 约束的字段的值在整个数据表当中是唯一的

唯一性约束是有索引的

create table `user` (
    `id` int(3) comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名',
    unique key `unique_id` (`id`)
);
1
2
3
4
5
6
7

describe `user`;
1

| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  | UNI | NULL    |       |
| account  | varchar(11) | YES  |     | NULL    |       |
| password | varchar(64) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

insert into `user` (`id`,`account`,`password`,`nickname`) value (1,'a1','123456','u1');
1

Query OK, 1 row affected (0.01 sec)
1

insert into `user` (`id`,`account`,`password`,`nickname`) value (1,'a2','123456','u2');
1

ERROR 1062 (23000): Duplicate entry '1' for key 'user.unique_id'
1

主键约束 PRIMARY KEY

主键 ≈ 非空 + 唯一

主键越是与唯一性约束一样是有索引的

单主键约束

create table `user` (
    `id` int(3) not null comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名',
    unique key `unique_id` (`id`)
);
1
2
3
4
5
6
7

describe `user`;
1

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| account  | varchar(11) | YES  |     | NULL    |       |
| password | varchar(64) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

create table `user` (
    `id` int(3) comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名',
    primary key (`id`)
);
1
2
3
4
5
6
7

describe `user`;
1

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | NO   | PRI | NULL    |       |
| account  | varchar(11) | YES  |     | NULL    |       |
| password | varchar(64) | YES  |     | NULL    |       |
| nickname | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

复合主键约束

create table `user` (
    `id` int(3) comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) comment '用户密码',
    `nickname` varchar(50) comment '用户姓名',
    primary key (`account`,`nickname`)
);
1
2
3
4
5
6
7

describe `user`;
1

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| account  | varchar(11) | NO   | PRI | NULL    |       |
| password | varchar(64) | YES  |     | NULL    |       |
| nickname | varchar(50) | NO   | PRI | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

主键自增长

create table `user` (
    `id` int(3) auto_increment comment '用户编号',
    `account` varchar(11) comment '用户账号',
    `password` varchar(64) default 'lihaozhe' comment '用户密码',
    `nickname` varchar(50) comment '用户姓名',
    primary key (`id`)
);
1
2
3
4
5
6
7

describe `user`;
1

+----------+-------------+------+-----+----------+----------------+
| Field    | Type        | Null | Key | Default  | Extra          |
+----------+-------------+------+-----+----------+----------------+
| id       | int         | NO   | PRI | NULL     | auto_increment |
| account  | varchar(11) | YES  |     | NULL     |                |
| password | varchar(64) | YES  |     | lihaozhe |                |
| nickname | varchar(50) | YES  |     | NULL     |                |
+----------+-------------+------+-----+----------+----------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

insert into `user` (`account`,`nickname`) values ('a1','u1'),('a2','u2'),('a3','u3');
1

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
1
2

select * from `user`;
1

+----+---------+----------+----------+
| id | account | password | nickname |
+----+---------+----------+----------+
|  1 | a1      | lihaozhe | u1       |
|  2 | a2      | lihaozhe | u2       |
|  3 | a3      | lihaozhe | u3       |
+----+---------+----------+----------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

insert into `user` (`id`,`account`,`nickname`) value (5,'a5','u5');
1

select * from `user`;
1

+----+---------+----------+----------+
| id | account | password | nickname |
+----+---------+----------+----------+
|  1 | a1      | lihaozhe | u1       |
|  2 | a2      | lihaozhe | u2       |
|  3 | a3      | lihaozhe | u3       |
|  5 | a5      | lihaozhe | u5       |
+----+---------+----------+----------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

insert into `user` (`account`,`nickname`) values ('a6','u6'),('a7','u7'),('a8','u8');
1

select * from `user`;
1

+----+---------+----------+----------+
| id | account | password | nickname |
+----+---------+----------+----------+
|  1 | a1      | lihaozhe | u1       |
|  2 | a2      | lihaozhe | u2       |
|  3 | a3      | lihaozhe | u3       |
|  5 | a5      | lihaozhe | u5       |
|  6 | a6      | lihaozhe | u6       |
|  7 | a7      | lihaozhe | u7       |
|  8 | a8      | lihaozhe | u8       |
+----+---------+----------+----------+
7 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

外键约束 FOREIGN KEY

一张表的某个字段的值参考另外一张表的主键

创建主表

create table `course` (
    `c_id` int(2) auto_increment comment '课程编号',
    `c_name` varchar(10) comment '课程名称', 
    primary key (`c_id`)
);
1
2
3
4
5

describe `course`;
1

+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| c_id   | int         | NO   | PRI | NULL    | auto_increment |
| c_name | varchar(10) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7

创建从表

create table `student` (
    `stu_id` int(10) auto_increment comment '学生编号',
    `stu_name` varchar(10) comment '学生姓名', 
    `c_id` int(2) comment '课程编号',
    primary key (`stu_id`),
    constraint fk_student_course foreign key (`c_id`) references `course` (`c_id`)
);
1
2
3
4
5
6
7

describe `student`;
1

+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| stu_id   | int         | NO   | PRI | NULL    | auto_increment |
| stu_name | varchar(10) | YES  |     | NULL    |                |
| c_id     | int         | YES  | MUL | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9

向主表插入记录

insert into `course` (`c_name`) values ('java'),('python');
1

select * from `course`;
1

+------+--------+
| c_id | c_name |
+------+--------+
|    1 | java   |
|    2 | python |
+------+--------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8

向从表插入记录

insert into `student` (`stu_name`,`c_id` ) value ('student01',1);
insert into `student` (`stu_name`,`c_id` ) value ('student02',2);
insert into `student` (`stu_name`,`c_id` ) value ('student03',1);
insert into `student` (`stu_name`,`c_id` ) value ('student04',2);
1
2
3
4

select * from `student`;
1

+--------+-----------+------+
| stu_id | stu_name  | c_id |
+--------+-----------+------+
|      1 | student01 |    1 |
|      2 | student02 |    2 |
|      3 | student03 |    1 |
|      4 | student04 |    2 |
+--------+-----------+------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

insert into `student` (`stu_name`,`c_id` ) value ('student05',3);
1

报错信息如下：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`day01`.`student`, CONSTRAINT `fk_student_course` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`))
1

删除表

如果先删除主表

drop table `course`;
1

报错信息如下：

ERROR 3730 (HY000): Cannot drop table 'course' referenced by a foreign key constraint 'fk_student_course' on table 'student'.
1

正确删除主从表的顺序是先删除从表再删除主表

drop table `student`;
1

drop table `course`;
1

经验：在实际工作中个人推荐使用逻辑外键而非物理外键

修改数据表名称

alter table `user` rename to `tb_user`;
1

修改字段数据类型

alter table `tb_user` modify `password` char(32) comment '用户密码';
1

修改字段约束

alter table `tb_user` modify `password` varchar(64) not null default 'lihaozhe' comment '用户密码';
1

修改字段名称

alter table `tb_user` change `password` `authentication_string` varchar(32);
alter table `tb_user` change `authentication_string` `password` varchar(64) not null default 'lihaozhe' comment '用户密码';
1
2

添加字段

alter table `tb_user` add `gender` enum('F','M') not null default 'M' comment 'F 女性 ， M 男性'; 
alter table `tb_user` add `birthday` date comment '出生日期' first; 
alter table `tb_user` add `uuid` varchar(32) primary key comment '系统唯一标识符' after `id`; 
1
2
3

删除字段

alter table `tb_user` drop `birthday`;
1

调整字段位置

alter table `tb_user` modify `uuid` varchar(32) primary key comment '系统唯一标识符' first; 
alter table `tb_user` modify `uuid` varchar(32) primary key comment '系统唯一标识符' after `id`; 
1
2

修改存储引擎

alter table `tb_user` engine=myisam;
1

删除外键约束

alter table `student` drop foreign key 'fk_student_course';
1

MySQL 数据 备份与还原

备份

# 操作系统控制台
mysqldump day01 -h 192.168.95.111 -uroot -p > ./day01.sql
1
2

还原

方式一

在数据库内还原

create database day01;
use day01;
source /home/lhz/day01.sql;
1
2
3

方式二

在操作系统还原

mysql day01 -h 192.168.95.111 -uroot -p < ./day01.sql
1

数据类型

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

---

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

---

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

---

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

数据库查询语言 DQL

单表查询

查询所有

SELECT * FROM `fruit`;
1

按字段查询

SELECT `f_name`,`f_price` FROM `fruit`;
1

按条件查询指定字段

-- 查询价格 10.2 的水果
SELECT `f_name`,`f_price` FROM `fruit` WHERE `f_price` = 10.2;
1
2

-- 查询apple的价格
SELECT `f_name`,`f_price` FROM `fruit` WHERE `f_name` = 'apple';
1
2

-- 查询价格 高于5.2 低于102 的水果
SELECT `f_name`,`f_price` FROM `fruit` WHERE `f_price` > 5.2 && `f_price` < 10.2;
SELECT `f_name`,`f_price` FROM `fruit` WHERE `f_price` > 5.2 AND `f_price` < 10.2;
SELECT `f_name`,`f_price` FROM `fruit` WHERE `f_price` >= 5.2 && `f_price` <= 10.2;
SELECT `f_name`,`f_price` FROM `fruit` WHERE `f_price` >= 5.2 AND `f_price` <= 10.2;
1
2
3
4
5

-- 查询价格 高于5.2 低于102 的水果
SELECT `f_name`,`f_price` FROM `fruit` WHERE `f_price` BETWEEN 5.2 AND 10.2;
1
2

-- 查询供应商 s_id 为 101 和 102 供应的水果
SELECT `s_id`,`f_name`,`f_price` FROM `fruit` WHERE `s_id` = 101 || `s_id` = 102;
SELECT `s_id`,`f_name`,`f_price` FROM `fruit` WHERE `s_id` = 101 OR `s_id` = 102;
1
2
3

-- 查询价格在 5.2 与 10.2 之间 供应商 s_id 为 101 和 102 供应的水果
SELECT
	`s_id`,
	`f_name`,
	`f_price` 
FROM
	`fruit` 
WHERE
	( `f_price` BETWEEN 5.2 AND 10.2 ) 
	AND 
	( `s_id` = 101 OR `s_id` = 102 )
1
2
3
4
5
6
7
8
9
10
11

IN查询

-- 查询供应商 s_id 为 101 和 102 供应的水果
SELECT `s_id`,`f_name`,`f_price` FROM `fruit` WHERE `s_id` = 101 OR `s_id` = 102;
SELECT `s_id`,`f_name`,`f_price` FROM `fruit` WHERE `s_id` IN (101,102);
1
2
3

-- 查询不是供应商 s_id 为 101 和 102 供应的水果
SELECT `s_id`,`f_name`,`f_price` FROM `fruit` WHERE `s_id` != 101 AND `s_id` != 102;
SELECT `s_id`,`f_name`,`f_price` FROM `fruit` WHERE `s_id` NOT IN (101,102);
1
2
3

模糊查询

-- 查询所有名称 b 开头的水果
SELECT `f_name` FROM `fruit` WHERE `f_name` LIKE 'b%';
1
2

-- 查询所有名称 含有 g 的水果
SELECT `f_name` FROM `fruit` WHERE `f_name` LIKE '%g%';
1
2

-- 查询所有名称 b开头 y结尾 的水果
SELECT `f_name` FROM `fruit` WHERE `f_name` LIKE 'b%y';
1
2

-- 查询所有名称 y结尾 y的前面有5个字符 的水果
SELECT `f_name` FROM `fruit` WHERE `f_name` LIKE '_____y';
1
2

聚合函数

-- 统计水果种类数量
SELECT COUNT(*) AS '总计' FROM `fruit`;
1
2

-- 统计单价最高
SELECT MAX(`f_price`) AS '最高价' FROM `fruit`;
1
2

-- 统计单价最低
SELECT MIN(`f_price`) AS '最低价' FROM `fruit`;
1
2

-- 统计单价总和
SELECT SUM(`f_price`) AS '合计' FROM `fruit`;
1
2

-- 统计平均价格
SELECT SUM(`f_price`)/COUNT(*) AS '合计' FROM `fruit`;
SELECT AVG(`f_price`) AS '平均价' FROM `fruit`;
1
2
3

NULL查询

-- 查询没有绑定邮箱的消费者
SELECT * FROM `customer` WHERE `c_email` IS NULL;
1
2

-- 查询绑定邮箱的消费者
SELECT * FROM `customer` WHERE `c_email` IS NOT NULL;
1
2

-- 统计消费者人数
SELECT COUNT(*) AS '人数' FROM `customer`;
SELECT COUNT(`c_id`) AS '人数' FROM `customer`;
SELECT COUNT(`c_email`) AS '人数' FROM `customer`;
1
2
3
4

-- 查询绑定邮箱的消费者人数
SELECT COUNT(`c_email`) AS '人数' FROM `customer`;
1
2

-- 查询没有绑定邮箱的消费者人数
SELECT COUNT(*) AS '人数' FROM `customer` WHERE `c_email` IS NULL;
1
2

DISTINCT去重

SELECT DISTINCT `s_id` FROM fruit;
1

排序

-- 按照水果价格升序显示
SELECT `f_name`,`f_price` FROM `fruit` ORDER BY `f_price`;
SELECT `f_name`,`f_price` FROM `fruit` ORDER BY `f_price` ASC;
1
2
3

-- 按照水果价格降序显示
SELECT `f_name`,`f_price` FROM `fruit` ORDER BY `f_price` DESC;
1
2

-- 按照供应商升序 如果供应商相同的情况下按照价格降序
SELECT `s_id`,`f_name`,`f_price` FROM `fruit` ORDER BY `s_id` ASC,`f_price` DESC;
1
2

分组查询

-- 每个供应商供应水果种类数量
SELECT `s_id`,COUNT(*) AS '数量' FROM `fruit` GROUP BY `s_id`;
1
2

-- 每个供应商供应水果种类数量 按照供应水果种类数量降序
SELECT `s_id`,COUNT(*) AS 'total' FROM `fruit` GROUP BY `s_id` ORDER BY `total` DESC;
1
2

-- 每个供应商供应水果种类数量 按照供应水果种类数量降序 如果供应水果种类数量相同则按照供应商编号降序
1

SELECT `s_id`,COUNT(*) AS 'total' FROM `fruit` GROUP BY `s_id` ORDER BY `total` DESC,`s_id` DESC;
1

统计消费清单

-- 每笔消费的总金额
-- 1、计算每笔流水的小计 小计  = 数量 × 单价
SELECT `o_num`, `o_item`, `quantity`, `item_price`,( `quantity` * `item_price` ) AS 'sub_total' 
FROM
	`orderitem`;
-- 2、计算每笔流水的的小计 小计  = 数量 × 单价 分组
SELECT `o_num`,GROUP_CONCAT( `quantity` * `item_price` ) AS 'sub_total' 
FROM
	`orderitem` 
GROUP BY
	`o_num`;
-- 3、计算每笔流水的总金额 小计的和
SELECT 
	`o_num`,
	GROUP_CONCAT( `quantity` * `item_price` ) AS 'sub_total',
	SUM( `quantity` * `item_price` ) AS 'total' 
FROM
	`orderitem` 
GROUP BY
	`o_num`;		
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- 每笔消费的总金额 降序
SELECT 
	`o_num`,
	GROUP_CONCAT( `quantity` * `item_price` ) AS 'sub_total',
	SUM( `quantity` * `item_price` ) AS 'total' 
FROM
	`orderitem` 
GROUP BY
	`o_num` ORDER BY `total` DESC;
1
2
3
4
5
6
7
8
9

-- 每笔消费的总金额 高于200 降序
SELECT 
	`o_num`,
	GROUP_CONCAT( `quantity` * `item_price` ) AS 'sub_total',
	SUM( `quantity` * `item_price` ) AS 'total' 
FROM
	`orderitem` 
GROUP BY
	`o_num` 
	HAVING `total` > 200 ORDER BY `total` DESC;
1
2
3
4
5
6
7
8
9
10

分页查询 LIMIT

-- LIMIT 起始位置 , 查询数量
-- (查询页面号 - 1) * 每页记录数 = 起始位置 
-- 总页数 = if(总记录数 % 每页记录数 == 0) ? 总记录数 / 每页记录数 : (总记录数 / 每页记录数) + 1

-- 第一页
SELECT * FROM `fruit` LIMIT 0, 5;

-- 第二页
SELECT * FROM `fruit` LIMIT 5, 5;

-- 第三页
SELECT * FROM `fruit` LIMIT 10, 5;

-- 第四页
SELECT * FROM `fruit` LIMIT 15, 5;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT * FROM `fruit`  ORDER BY `s_id` LIMIT 0,5;
1

多表查询

关联查询

-- 显示所有供应商及其供应的水果信息
SELECT	
	`s_name`,
	`s_city`,
	`s_zip`,
	`s_call`,
	`s`.`s_id`,
	`f`.`s_id`,
	`f_id`,
	`f_name`,
	`f_price` 
FROM
	`supplier` AS s,
	`fruit` AS f 
WHERE
	`s`.`s_id` = `f`.`s_id` 
ORDER BY
	`s`.`s_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

子查询

-- 查找水果名为苹果的供应商 联合查询方式查找
SELECT
	`s`.`s_id`,
	`s_name`,
	`s_city`,
	`s_zip`,
	`s_call`,
	`f_id`,
	`f`.`s_id`,
	`f_name`,
	`f_price` 
FROM
	`supplier` AS s,
	`fruit` AS f 
WHERE
	`s`.`s_id` = `f`.`s_id` 
	AND
	`f_name` = 'apple'
ORDER BY
	`s`.`s_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- 查找水果名为苹果的供应商 子查询方式查找
select `s_id` from `fruit` where `f_name` = 'apple';
select * from `supplier` where  `s_id` = 101;
select * from `supplier` where  `s_id` = (select `s_id` from `fruit` where `f_name` = 'apple');
1
2
3
4

连接查询

内连接 INNER JOIN

• 关键字 INNER JOIN
• 连接查询条件 ON

-- 显示所有供应商及其供应的水果信息
SELECT	
	`s_name`,
	`s_city`,
	`s_zip`,
	`s_call`,
	`s`.`s_id`,
	`f`.`s_id`,
	`f_id`,
	`f_name`,
	`f_price` 
FROM
	`supplier` AS s 
	INNER JOIN
	`fruit` AS f 
ON
	`s`.`s_id` = `f`.`s_id` 
ORDER BY
	`s`.`s_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

外连接

左外连接 LEFT OUTER JOIN

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充

-- 查找所有订单和产生该订单的消费者信息
SELECT
	`o_num`,
	`o_date`,
	`o`.`c_id`,
	`c`.`c_id`,
	`c_name`,
	`c_address`,
	`c_city`,
	`c_zip`,
	`c_contact`,
	`c_email` 
FROM
	`order` AS o
	LEFT OUTER JOIN `customer` AS c 
	ON `o`.`c_id` = `c`.`c_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 查找所有消费者产生的订单信息
SELECT
	`c_name`,
	`c_address`,
	`c_city`,
	`c_zip`,
	`c_contact`,
	`c_email`,
	`o`.`c_id`,
	`c`.`c_id`,
	`o_num`,
	`o_date`
FROM
	`customer` AS o
	LEFT OUTER JOIN `order` AS c 
	ON `o`.`c_id` = `c`.`c_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

右外连接 RIGHT OUTER JOIN

以右表为主根据条件查询右表数据，如果根据条件查询左表数据不存在使用null值填充

-- 查找所有消费者产生的订单信息
SELECT
	`c_name`,
	`c_address`,
	`c_city`,
	`c_zip`,
	`c_contact`,
	`c_email`,
	`o`.`c_id`,
	`c`.`c_id`,
	`o_num`,
	`o_date`
FROM
	`order` AS o
	RIGHT OUTER JOIN `customer` AS c 
	ON `o`.`c_id` = `c`.`c_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 查找所有订单和产生该订单的消费者信息
SELECT
	`o_num`,
	`o_date`,
	`o`.`c_id`,
	`c`.`c_id`,
	`c_name`,
	`c_address`,
	`c_city`,
	`c_zip`,
	`c_contact`,
	`c_email` 
FROM
	`customer` AS o
	RIGHT OUTER JOIN `order` AS c 
	ON `o`.`c_id` = `c`.`c_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

自连接

-- 查询某省份所辖所有城市
SELECT * FROM region_address  WHERE parent_code = 22;
1
2

ANY SOME

SELECT `num1`  FROM `tbl1`  WHERE `num1` > ANY ( SELECT `num2` FROM `tbl2` );
SELECT `num1`  FROM `tbl1`  WHERE `num1` > SOME ( SELECT `num2` FROM `tbl2` );
1
2

ALL

SELECT `num1`  FROM `tbl1`  WHERE `num1` > ALL ( SELECT `num2` FROM `tbl2` );
1

exists

-- 查找供应商 名字为 李昊哲 供应的水果 内连接查询
SELECT
	`s`.`s_id`,
	`s_name`,
	`f_name` 
FROM
	`fruit` AS f
	INNER JOIN `supplier` AS s ON `f`.`s_id` = `s`.`s_id` 
	AND `s_name` = 'ACME' 
ORDER BY
	`s`.`s_id`;
1
2
3
4
5
6
7
8
9
10
11

-- 查找供应商 名字 是否存在
select exists (SELECT `s_id` FROM `supplier` WHERE `s_name` = 'ACME' );
select exists (SELECT `s_id` FROM `supplier` WHERE `s_name` = '李昊哲' );
1
2
3

CREATE TABLE `user` ( 
    `mobile` VARCHAR ( 11 ), 
    `password` VARCHAR ( 64 ) DEFAULT '123456',
    PRIMARY KEY ( `mobile` ) 
);
1
2
3
4
5

INSERT INTO `user` (`mobile`) value ('15311484568');
1

INSERT INTO `user` (`mobile`) SELECT '15311484568' FROM DUAL
	WHERE NOT EXISTS 
	(SELECT `mobile` FROM `user` WHERE `mobile` = '15311484568');
1
2
3

INSERT INTO `user` (`mobile`) SELECT '18515697037' FROM DUAL
	WHERE NOT EXISTS 
	(SELECT `mobile` FROM `user` WHERE `mobile` = '18515697037');
1
2
3

更新数据表数据 UPDATE

UPDATE `user` set `password` = 'lihaozhe' WHERE `mobile` = '15311484568';
1

删除数据表数据 DELETE

CREATE TABLE `teacher` ( 
    `id` int ( 11 ) AUTO_INCREMENT, 
    `nickname` VARCHAR ( 64 ),
    PRIMARY KEY ( `id` ) 
);
1
2
3
4
5

INSERT INTO `teacher` (`nickname`) values ('李昊哲'),('李胜龙'); 
1

DELETE FROM `teacher` WHERE `id` = 2;
1

INSERT INTO `teacher` (`nickname`) values ('lhz'); 
1

清空数据表

DELETE

DELETE FROM `teacher`;
1

INSERT INTO `teacher` (`nickname`) values ('李胜龙'); 
1

TRUNCATE

TRUNCATE `teacher`;
1

INSERT INTO `teacher` (`nickname`) values ('李昊哲'); 
1