2. SQL
2021-02-18 本文已影响0人
随便写写咯
1 MySQL字符集和排序
1.1 查看MySQL当前支持的字符集
mysql> show character set;
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
#这个utf8并不是真的utf8, 只占3个字节无法表示全球文字的,只是把常用的表示出来, 一些冷门字符,比如表情包是不支持的,正常应该是4个字节才能把全球所有字符表示出来
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
#utfmb4才是真的utf8,占四个字节,支持全球所有文字,包括表情包
1.2 查看默认字符集
CentOS 8, MySQL8.0
mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.17 sec)
CentOS 7, MySQL5.7
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.02 sec)
CentOS 7, MySQL5.6
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.6.48-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.01 sec)
MySQL5.6和5.7版本默认的服务器的字符集都是latin1,因此一旦插入其他字符,比如汉字,显示会乱码.
MySQL 8.0, 客户端和服务器端默认字符集都是utf8mb4
mysql> status
--------------
mysql Ver 8.0.17 for Linux on x86_64 (Source distribution)
Connection id: 8
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.17 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 10 sec
Threads: 2 Questions: 6 Slow queries: 0 Opens: 118 Flush tables: 3 Open tables: 35 Queries per second avg: 0.600
--------------
mysql> show variables like 'character%'
-> ;
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
1.3 如何修改默认字符集
生产环境建议字符集统一为utf8mb4
需要分别修改服务器和客户端
修改MySQL服务器端默认字符集
# 5.7
vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
skip_name_resolve=1
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
[client]
socket=/data/mysql/mysql.sock
default-character-set=utf8mb4
修改MySQL客户端默认字符集
vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4
重启服务
service mysqld restart
再查看
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.7.31-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)
更改客户端和服务器端文件, 会影响不同选项的设置
比如修改client配置文件会影响character_set_client, character_set_connection等
修改server配置文件会影响server和database
客户端和服务器端字符集要一致
1.4 MySQL排序规则
排序规则指的是, 查询数据库时, 如果涉及排序, 是按照字母顺序排序还是ascii码还是其他顺序排序
排序规则会随着字符集修改而变化, 因为不同的字符集有自己默认的排序规则,而排序规则会使用当前字符集默认的排序规则
查看当前使用的排序规则, 和可用的排序规则
mysql> show variables like "collation%";
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)
mysql> show collation;
+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
| armscii8_bin | armscii8 | 64 | | Yes | 1 | PAD SPACE |
...
修改排序规则
排序规则是按照字符集来的, 因此, 修改了字符集后, 排序规则会自然修改, 一般无需单独修改排序规则
数据库存放目录
数据库在mysql就是文件夹, 默认存放在/var/lib/mysql
information_schema是特殊的数据库, 内容并不在磁盘上, 而是在内存中,这种库可以保留一些数据库临时状态信息, 供查询, 没必要持久保存
只有在磁盘持久保存的数据库才能在磁盘目录看见对应目录
2 管理数据库
5.7
2.1 创建数据库
create database if not exists DATABASE;
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
[14:45:28 root@mysql-5-7 ~]#ll /data/mysql
total 122968
-rw-r----- 1 mysql mysql 56 Jun 7 13:44 auto.cnf
-rw------- 1 mysql mysql 1676 Jun 7 13:44 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Jun 7 13:44 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Jun 7 13:44 client-cert.pem
-rw------- 1 mysql mysql 1680 Jun 7 13:44 client-key.pem
-rw-r----- 1 mysql mysql 353 Jun 7 14:36 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jun 7 14:36 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jun 7 14:36 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jun 7 13:44 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jun 7 14:36 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Jun 7 13:44 mysql
-rw-r----- 1 mysql mysql 12273 Jun 7 14:45 mysql.log
-rw-r----- 1 mysql mysql 5 Jun 7 14:36 mysql.pid
srwxrwxrwx 1 mysql mysql 0 Jun 7 14:36 mysql.sock
-rw------- 1 mysql mysql 5 Jun 7 14:36 mysql.sock.lock
drwxr-x--- 2 mysql mysql 8192 Jun 7 13:44 performance_schema
-rw------- 1 mysql mysql 1676 Jun 7 13:44 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Jun 7 13:44 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Jun 7 13:44 server-cert.pem
-rw------- 1 mysql mysql 1676 Jun 7 13:44 server-key.pem
drwxr-x--- 2 mysql mysql 8192 Jun 7 13:44 sys
drwxr-x--- 2 mysql mysql 20 Jun 7 14:45 testdb
查看数据库如何创建的
show create database testdb;
mysql> show create database testdb;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
或者可以查看文件, 显示数据库设置信息
[14:46:39 root@mysql-5-7 ~]#cat /data/mysql/testdb/db.opt
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci
创建数据库时可以不使用服务器全局默认字符集, 而是使用指定的字符集和排序规则
mysql> show create database testdb;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create database testdb2 character set latin1 collate latin1_bin;
Query OK, 1 row affected (0.00 sec)
mysql> show create database testdb2;
+----------+---------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------+
| testdb2 | CREATE DATABASE `testdb2` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */ |
+----------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.2 修改数据库信息
alter database NAME ...
mysql> alter database testdb2 character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database testdb2;
+----------+---------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------+
| testdb2 | CREATE DATABASE `testdb2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
修改了字符集后, 以前的数据是不会受影响的, 只有新插入的数据才按照新的字符集显示
mysql提示的warming信息,可以使用show warnings;查看, 但是必须在执行下一条命令前查看, 因为warnings是显示的上一条命令的告警信息
2.3 删除数据库
drop database DB_NAME;
删除数据库可以drop database 也可以直接删除目录文件
drop database后, 数据库目录文件也就删了; 删除数据库目录文件, show database;也就看不到了
3 MySQL数据类型
图片.png
MySQL包含三大类数据类型:
数字
时间
字符串
3.1 数值类型
3.1.1 整数
整数细分为各种类型, 目的是节约磁盘空间
比如: tinyint, 占用1个字节, 8个bits, 其中最高位是符号位,0表示正数,1表示负数, 所以默认1个字节可以表示的范围是-128 ~ 127(2的0次幂到2的7次幂)共256个数字. 可以加描述符(UNSIGHED),去掉符号位,把8个比特位全用上, 可以表示0-255,共256个数字
适用数值型的修饰符
- AUTO_INCREMENT: 自动递增, 适用于整数类型
mysql> show variables like "auto_inc%";
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
2 rows in set (0.03 sec)
- UNSIGNED: 无符号位
-- tinyint: 1个字节
-- smallint: 2个字节
-- mediumintL3个字节
-- intL 占4个字节, 共32位
-- bigint: 占用8个字节, 共64bits位,可以表示2^64的数据量
把数据定义成整数tinyint, 使用UNSIGNED无符号位后可以用来存储0-255之间的任意整数, 并且每个整数在磁盘只占1个字节
如果用bigint, bigint能存储庞大的整数值, 每个整数在磁盘要占8个字节,消耗磁盘空间
比如: 年龄,tinyint即可, 0-255,磁盘上占一个字节
3.1.2 浮点型
float(m,d)单精度浮点型,8位精度4(字节), m总个数, d小数位
double(m,d)双精度浮点型, 16位精度8(字节), m总个数, d小数位
假设一个字段定义为float(6,3), 如果插入一个数123.45678, 实际数据库里存的是123.456, 但总个数还以实际为准, 即6位
3.2 日期时间类型
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4): 年份
timestamp字段里的时间数据会随其他字段修改的时候自动刷新, 这个数据类型的字段可以存放这条记录最后被修改的时间
3.3 字符
3.3.1 char 和 varchar
char(n): 固定长度, 最多255个字符, n表示最大长度, 超出最大长度就无法存储了, 多的字符会被截断
比如定义姓名是char(5),那么姓名最多5个字符, 超过5个字符的就无法储存
varchar(n): 可变长度, 最多65535个字符, 可表示的数据量大, n表示最大长度
3.3.2 char 和 varchar区别
参考: http://dev.mysql.com/doc/refman/8.0/en/char.html
以latin1字符集为例, 每个字母占一个字节
char vs varchar
storage required看的是字符集, 字符集不同,占用空间也不同, 此处用latin1字符集举例, latin1一个字母占一个字节, utf8中, 英文字母占一个字节, 汉字占3-4个字节
value '空数据':
将字段数据定义成char(4), 如果数据是空数据, 那么在磁盘上,也要占4个字节的磁盘空间;
将字段数据定义成varchar(4), 如果数据是空数据, 那么在磁盘上,只占据一个字节的磁盘空间, 也就是说, varchar类型, 占用空间要多一个字节, 利用一个空白符占位
value 'ab':
如果数据是两个字符, char(4), 仍然占4个字节, 'ab ',在ab后补两个空白占用两个字符, 保证四个字符的固定长度;
如果是varchar(4), 则只存成'ab ',占用3个字节,因为要算上空白的一个字节
value 'abcd':
char(4), 存成'abcd', 磁盘占用4个字节; varchar(4), 存成'abcd ', 磁盘占用5个字节, 因为要加上空格占的一个字节
value 'abcdefgh':
char(4), 只能存到'abcd',因为规定最多4个字符, 磁盘占用4个字节; varchar(4) 也是存成'abcd', 磁盘占用5个字节, 因为加上了空白占的一个字节
由此可见, char是固定长度, 不管实际输入的数据是几个字符, 磁盘空间都是按照最长可保存字符的量来占据空间
varchar,按照实际输入的字符数量再加上一个空白占位符的一个字节来占据磁盘空间
char类型在磁盘存储摆放规则, 都是占固定长度字节, 磁盘检索速度快,但是会浪费空间
varchar类型在磁盘存储摆放不规则,因为是可变长的字节, 磁盘检索速度慢,但是不浪费空间
数据不是稳定的长度, 变化幅度大, 差别大, varchar好
如果数据长度都差不多, 变化幅度不大, char好
具体看数据情况
char和varchar存数据的流程:
char: 申请空间, 存字符+空白字符
varchar: 判断字符长度, 申请空间, 存字符, 申请额外1-2个字节存长度这个数字(字符串长度如果是0-255, 那么占1个字节, 超过255, 用两个字节,一般如果字符超过255了, 那么也不会用MySQL去存)
5.6存数据, 如果超出了定义长度, 那么字符串会被截断存进去
5.7和8.0, 如果超出长度是不让存的, 会报错
3.4 enum 枚举类型
对于数据较为固定的字段, 比如省份, 性别, 国家等变化不会过于频繁,且数据较为固定的列, 可以创建为枚举类型
在创建表时, 将列定义为枚举类型, 并且把所有的数据都先指定到枚举enum中, 这时会给每个数据分配一个索引下标
之后, 插入数据时, 可以利用索引编号插入数据, 加快效率, 节省空间
枚举是一种数据类型, 字段定义成了enum类型, 就不能定义为其他类型了
create table teacher (id int primary key auto_increment, name char(10) not null , city enum('hv','wc','rd') default 'hv');
补充:同一个数据库下的对象不能重名, 即使是属于不同资源的对象也不能重名. 比如一个表叫A, 那么就不能存在名为A的视图
3.5 适用所有类型的修饰符
NULL: 数据列可包含NULL值, 默认值
NOT NULL: 数据列不允许包含NULL值, 为必填选项
DEFAULT: 如何没有输入, 那么给字段赋予一个默认值
PRIMARY KEY: 主键, 所有记录中此字段的值不能重复, 且不能为NULL
UNIQUE KEY: 唯一键, 所有记录中此字段的值不能重复, 但可以为NULL
CHARACTER SET name: 指定一个字符集
3.6 约束和属性
约束:
PK: 某一列设置了PK, 要求数据唯一且非空, 其每张表只能有一个主键, 作为聚簇索引
not null: 非空, 可以重复, 建议每个列都为非空, 提高索引效率
unique key: 唯一约束, 每个表可以有多个unique, 要求非空
unsigned: 对于数字列, 不能为负数
属性:
default: 对于要求非空的列, 设定一个默认值, 插入数据时如果没有指定具体数据, 那么就插入默认值
comment: 给列加注释信息
4 DDL语句
4.1 表操作
4.1.1 创建表
4.1.1.1 直接创建
eg1:
mysql> use testdb2;
Database changed
mysql> create table student ( id int unsigned auto_increment primary key, name varchar(20) not null, age tinyint unsigned, gender enum('M','F') default 'M') auto_increment=10 default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
#这里指定auto_increment=10的效果就是, id列插入的第一行为10, 之后每次自增1
#查看磁盘目录
[15:36:24 root@mysql-5-7 ~]#ll /data/mysql/testdb2
total 112
-rw-r----- 1 mysql mysql 67 Jun 7 14:52 db.opt # 创建数据库生成
-rw-r----- 1 mysql mysql 8654 Jun 7 15:30 student.frm # 创建表生成
-rw-r----- 1 mysql mysql 98304 Jun 7 15:30 student.ibd # 创建表生成
eg2: 日期类型的使用
定义时间戳, 插入数据时自动添加当时的时间
mysql> create table testdate (id int auto_increment primary key, date timestamp default current_timestamp not null);
Query OK, 0 rows affected (0.00 sec)
mysql> desc testdate;
+-------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-----------+------+-----+-------------------+----------------+
2 rows in set (0.07 sec)
mysql> insert into testdate(id) values(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2021-06-07 15:39:09 |
+----+---------------------+
1 row in set (0.01 sec)
mysql> insert into testdate(id) values(2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from testdate;
+----+---------------------+
| id | date |
+----+---------------------+
| 1 | 2021-06-07 15:39:09 |
| 2 | 2021-06-07 15:39:23 |
+----+---------------------+
2 rows in set (0.00 sec)
4.1.1.2 通过查询现存表中的数据来创建新表, 新表会被直接插入查询而来的数据
eg1:
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> create table user select user,host from mysql.user;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> desc user;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| user | char(32) | NO | | | |
| host | char(60) | NO | | | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
此种方式会将源表相应的数据插入到新表, 但是会丢失一部分字段属性
图片.png
4.1.1.3 只克隆现存表的表结构, 不复制数据
mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create table teacher like student;
Query OK, 0 rows affected (0.01 sec)
mysql> desc teacher;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table teacher;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | # AUTO_INCREMENT=10属性不会被克隆, 也就说like, 只克隆表结构和字段属性, 表本身的属性会丢失
4.1.2 查看表结构
- desc student;
mysql> desc student;
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| gender | enum('M','F') | YES | | M | |
+--------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
- show create table student;
mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint(3) unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.1.3 查看表状态
查看库里某个表的状态
show table status like 'table_name'; #水平显示信息
show table status like 'table_name'\G #垂直显示信息
mysql> show table status like 'student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 10
Create_time: 2021-06-07 15:30:01
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
查看库里所有表的状态
show table status from DBNAME; #水平显示信息
show table status from DBNAME\G #垂直显示信息
4.1.4 删除和修改表
删除表
drop table TABLE_NAME
修改表
alter table TABLE_NAME
#修改字段信息
#添加字段: add
add col1 data_type [FIRST|AFTER col_name]
#删除字段: drop
#修改字段
alter (默认值), change (字段名), modify (字段属性)
alter table students rename s1;
alter table s1 add phone varchar(11) after name;
alter table s1 modify phone int;
alter table s1 change column phone mobile char(11); #column关键字可写可不写
alter table s1 change column name name varchar(20) character set utf8;
alter table s1 change id sid int; #修改字段名时, 新的字段名后面一定要加上字段属性, 否则会报错. 如果是主键, 则修改的名字指定属性时不用加主键, 否则会提示已经有了主键
alter table s1 drop column mobile;
alter table s1 character set utf8;
alter table s1 add gender enum('m','f');
desc s1;
添加和删除主键
alter table s1 add primary key(id);
alter table s1 drop primary key;
5 DML语句
DML: INSERT, DELETE, UPDATE
5.1 INSERT语句
功能: 一次插入一行或者多行数据
语法:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
简化写法:
INSERT tbl_name [(col1,...)] VALUES (val1,...),(valn,...)
- 对于auto_increment的字段,插入数据时可以省略
- 字段名可以全部省略,会按照默认的字段顺序插入
- into可以省略
- value或者values都可以
- 如果省略了某个字段,那么字段和数据要一一对应
- 插入字符串要加引号,单引号双引号都可以, 否则报错
mysql> insert student value (1,wang, 18);
ERROR 1054 (42S22): Unknown column 'wang' in 'field list'
- 字段顺序可以自行指定,但是要和value一一对应
- 新版的mariadb或者mysql, 如果插入数据和表的字符集不匹配, 插入数据会报错; 如果是旧的版本, 数据可以插入, 但是查询时会显示乱码
- 字段的字符集优先级最高, 依次是表的字符集优先级, 然后是库的字符集,然后是服务器的字符集. 如果表没有指定字符集, 那么会用库的字符集, 如果库没有指定字符集,那就用服务器的字符集
- 后期修改字符集也只影响新插入的数据, 因此,前期建表的时候就要做好
mysql> insert into student (name,age) values ('david',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (name,age) values ('linux',30);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student (name,age) values ('linux',30);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | wang | 20 | M |
| 10 | david | 30 | M | # 定义了auto_increment, 因此, id会默认从10开始插入
| 11 | linux | 30 | M |
+----+-------+------+--------+
3 rows in set (0.00 sec)
mysql> insert into student (age,name,gender) values (25,'lala','F');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+------+--------+
| id | name | age | gender |
+----+-------+------+--------+
| 1 | wang | 20 | M |
| 10 | david | 30 | M |
| 11 | linux | 30 | M |
| 12 | lala | 25 | F |
+----+-------+------+--------+
4 rows in set (0.00 sec)
将一个表里的信息select出来,插入到另一个表
#这里如果teacher和student的主键数值相同那么会有冲突, 可以select和insert时不带主键
mysql> insert into teacher select * from student;
ERROR 1062 (23000): Duplicate entry '1' for key 'teacher.PRIMARY'
mysql> insert into teacher (name,age,gender) select name,age,gender from student;
一次插入多条记录
mysql> insert into student (name,age,gender) value ("zhou",18,'m'),("wu",20,'f');`
5.2 UPDATE语句
- update 一定要加where条件, 否则全表修改
- 可以修改配置文件或者登录mysql时加-U选项或者把mysql定义成别名mysql -U, 拒绝update时不加where语句
update student set name='linux' where id=1;
登录mysql时加-U选项,可以禁止不加where子句的update
[17:17:28 root@mysql-5-7 ~]#mysql -uroot -p000000 -U
mysql> update student set name = 'haha';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
#-U是安全更新模式, 更新数据时, 必须有where子句, 否则不让更新
修改配置文件
vim /etc/my.cnf
[mysql]
safe-updates
5.3 DELETE 语句
delete用来删表中的记录, 而drop用来删资源, 比如库, 表, 视图, 用户等
和update语句一样, delete一定要跟where子句, 可以通过限制update语句一样的方法现在delete语句
delete from TABLE where CLAUSE;
mysql> delete from student;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
delete vs truncate 清空表对比
-
delete和truncate都可以清空表, 保留表的结构信息, 但是delete不会自动缩减数据在硬盘的空间,就是表对应的在/var/lib/mysql/DATABASE/TABLE下的文件, 也就是不会把硬盘空间清空, truncate可以自动把被删除数据所占的硬盘空间清空
-
truncate比delete删的更快, 数据不可修复
eg:
利用testlog.sql存储过程,生成10万行数据插入到testlog表中, 测试delete和truncate
- testlog.sql 脚本
[23:12:15 root@centos8-2 ~]#cat testlog.sql
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure sp_testlog()
begin
declare i int;
set i = 1;
while i <= 100000
do insert into testlog(name,age) values (concat('wang',i),i);
set i = i +1;
end while;
end$$
delimiter ;
- 将存储过程导入数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
[23:14:14 root@centos8-2 ~]#mysql testdb < testlog.sql
- 调用存储过程
[23:14:21 root@centos8-2 ~]#mysql testdb -e 'call sp_testlog'
- 查看磁盘文件大小
[17:28:06 root@mysql-5-7 ~]#ll /data/mysql/testdb
total 12304
-rw-r----- 1 mysql mysql 67 Jun 7 14:45 db.opt
-rw-r----- 1 mysql mysql 8614 Jun 7 17:26 testlog.frm
-rw-r----- 1 mysql mysql 12582912 Jun 7 17:28 testlog.ibd
在磁盘上会看到以数据库名字命名的目录,以及里面的对应表文件, 另外mysql的information schema库是不在磁盘目录保存的, 保存在内存中
- 查看testlog表数据
mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from testlog limit 10;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | wang1 | 1 |
| 2 | wang2 | 2 |
| 3 | wang3 | 3 |
| 4 | wang4 | 4 |
| 5 | wang5 | 5 |
| 6 | wang6 | 6 |
| 7 | wang7 | 7 |
| 8 | wang8 | 8 |
| 9 | wang9 | 9 |
| 10 | wang10 | 10 |
+----+--------+------+
10 rows in set (0.00 sec)
- 先用delete清空testlog表
mysql> delete from testlog;
Query OK, 100000 rows affected (1.67 sec)
#delete命令清空十万条数据要花1.67秒
- 查看磁盘文件
可以看到testdb数据库大小并没有变小
[17:28:35 root@mysql-5-7 ~]#ll /data/mysql/testdb
total 12304
-rw-r----- 1 mysql mysql 67 Jun 7 14:45 db.opt
-rw-r----- 1 mysql mysql 8614 Jun 7 17:26 testlog.frm
-rw-r----- 1 mysql mysql 12582912 Jun 7 17:29 testlog.ibd
- 即使重启, 磁盘空间也不会被释放
[17:29:54 root@mysql-5-7 ~]#service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!
[17:30:27 root@mysql-5-7 ~]#ll /data/mysql/testdb
total 12304
-rw-r----- 1 mysql mysql 67 Jun 7 14:45 db.opt
-rw-r----- 1 mysql mysql 8614 Jun 7 17:26 testlog.frm
-rw-r----- 1 mysql mysql 12582912 Jun 7 17:29 testlog.ibd
- 利用optimize命令,缩减表大小
mysql> optimize table testlog;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+-------------------------------------------------------------------+
| testdb.testlog | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| testdb.testlog | optimize | status | OK |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)
- 再次查看磁盘文件大小
空间已被释放, 但并不是完全释放, 只是删除了数据, 表的结构信息还是保存的
[17:31:14 root@mysql-5-7 ~]#ll /data/mysql/testdb
total 112
-rw-r----- 1 mysql mysql 67 Jun 7 14:45 db.opt
-rw-r----- 1 mysql mysql 8614 Jun 7 17:31 testlog.frm
-rw-r----- 1 mysql mysql 98304 Jun 7 17:31 testlog.ibd
现在测试truncate命令
利用脚本再生成10万条记录, 也可以在数据库里执行
mysql> call sp_testlog;
[17:31:15 root@mysql-5-7 ~]#ll /data/mysql/testdb
total 12304
-rw-r----- 1 mysql mysql 67 Jun 7 14:45 db.opt
-rw-r----- 1 mysql mysql 8614 Jun 7 17:31 testlog.frm
-rw-r----- 1 mysql mysql 12582912 Jun 7 17:32 testlog.ibd
利用truncate情况表, 查看所花时间和磁盘空间情况
mysql> truncate table testlog;
Query OK, 0 rows affected (0.02 sec)
#同样的数据, 只花了0.014秒
[17:32:43 root@mysql-5-7 ~]#ll /data/mysql/testdb
total 112
-rw-r----- 1 mysql mysql 67 Jun 7 14:45 db.opt
-rw-r----- 1 mysql mysql 8614 Jun 7 17:31 testlog.frm
-rw-r----- 1 mysql mysql 98304 Jun 7 17:33 testlog.ibd
#磁盘空间立即释放
6 DQL 语句
6.1 单表查询
导入实验数据, 创建hellodb数据库
[17:40:33 root@mysql-5-7 ~]#mysql -uroot -p000000 < hellodb_innodb.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb | # hellodb表
| mysql |
| performance_schema |
| sys |
| test |
| testdb |
| testdb2 |
+--------------------+
8 rows in set (0.00 sec)
mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
全表查询
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
- '*' 代表表中所有字段
- 字段和命令以及表里的实际内容大小写不区分
- 表名和库名区分大小写,因为是磁盘文件, Linux系统区分大小写
- Mariadb 10.5 密码用的是password函数加密的也支持MD5和SHA, MySQL8.0用MD5或者SHA,不支持password函数.不同版本的加密函数不一样
- null 不区分大小写
- 插入时没有指明某字段数据, 如果该字段没有默认数据且没有限定非空,那么就是NULL
- select 如果直接接字符, 则会显示字符本身,并且根据表的行数来显示对应次数的字符, 直接打印字符
mysql> select "hello";
+-------+
| hello |
+-------+
| hello |
+-------+
1 row in set (0.01 sec)
MariaDB [hellodb]> select 'hello' from students;
+-------+
| hello |
+-------+
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
| hello |
+-------+
25 rows in set (0.000 sec)
利用select命令, 可以直接打印字符串, 也可以利用mysql函数进行加密
#mysql8.0版本
mysql> select md5("hello");
+----------------------------------+
| md5("hello") |
+----------------------------------+
| 5d41402abc4b2a76b9719d911017c592 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select sha("hello");
+------------------------------------------+
| sha("hello") |
+------------------------------------------+
| aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d |
+------------------------------------------+
1 row in set (0.00 sec)
like模糊搜索
- '任意长度任意字符%'
- '%任意长度任意字符'
- '%任意长度任意字符%' #不要使用,严重影响性能
- '_', 一个任意字符
生产不建议使用正则表达式
MariaDB [hellodb]> select * from students where name like 'shi%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)
分组查询
查看每XXX的数据时, 可以用分组, 一般配合聚合函数, avg,max,min...
使用group by分组后, select 后面跟的必须是group by后面有的字段或者聚合函数.
eg: 统计每个班级学生平均年龄
MariaDB [hellodb]> select avg(age) as avg_age from students group by classid;
+---------+
| avg_age |
+---------+
| 63.5000 |
| 20.5000 |
| 36.0000 |
| 20.2500 |
| 24.7500 |
| 46.0000 |
| 20.7500 |
| 19.6667 |
+---------+
8 rows in set (0.001 sec)
MariaDB [hellodb]> select classid, avg(age) as avg_age from students group by classid;
+---------+---------+
| classid | avg_age |
+---------+---------+
| NULL | 63.5000 | #某些学生没有班级, 所以classid是null, 也就被分到null组了
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+---------+
8 rows in set (0.001 sec)
过滤掉classid为空的数据, 两种逻辑,两种方法
- where 子句 + group by COLUMN 先过滤出非空,再group by分组
MariaDB [hellodb]> select classid, avg(age) as avg_age from students where classid is not null group by classid;
+---------+---------+
| classid | avg_age |
+---------+---------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+---------+
7 rows in set (0.001 sec)
- group by + having COLUMN 先group by分组,再having指定非空的字段
所以, 分组后在过滤要用having
MariaDB [hellodb]> select classid, avg(age) as avg_age from students group by classid having classid;
+---------+---------+
| classid | avg_age |
+---------+---------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+---------+
7 rows in set (0.001 sec)
如果分组了, 那么在select后的字段,只能是和分组的字段一致,或者select后不加字段,只写聚合函数.
在MariaDB中,如果select后跟了非分组字段的字段, 那么也会显示, 显示的是对应的分组中在原表里的第一个数据
eg:
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)
MariaDB [hellodb]> select stuid, avg(age), classid from students group by classid;
+-------+----------+---------+
| stuid | avg(age) | classid |
+-------+----------+---------+
| 24 | 63.5000 | NULL | #stuid=24是表中,属于null组的第一个数据
| 2 | 20.5000 | 1 | #stuid=2 是表中,属于classid=1的第一个数据
| 1 | 36.0000 | 2 |
| 5 | 20.2500 | 3 |
| 4 | 24.7500 | 4 |
| 6 | 46.0000 | 5 |
| 9 | 20.7500 | 6 |
| 8 | 19.6667 | 7 |
+-------+----------+---------+
8 rows in set (0.001 sec)
针对多个字段进行分组
group by后面接的字段顺序, 就是分组顺序
group by gender, classid 就是先按照gender分组, 然后在gender每个小组中再按照classid分组
eg: 查看每个班级, 男生和女生各自的平均年龄
MariaDB [hellodb]> select classid, gender, avg(age) from students group by classid, gender;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| NULL | M | 63.5000 |
| 1 | F | 19.5000 |
| 1 | M | 21.5000 |
| 2 | M | 36.0000 |
| 3 | F | 18.3333 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 5 | M | 46.0000 |
| 6 | F | 20.0000 |
| 6 | M | 23.0000 |
| 7 | F | 18.0000 |
| 7 | M | 23.0000 |
+---------+--------+----------+
12 rows in set (0.001 sec)
MariaDB [hellodb]> select classid, gender, avg(age) from students group by gender, classid;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| 1 | F | 19.5000 |
| 3 | F | 18.3333 |
| 6 | F | 20.0000 |
| 7 | F | 18.0000 |
| NULL | M | 63.5000 |
| 1 | M | 21.5000 |
| 2 | M | 36.0000 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 5 | M | 46.0000 |
| 6 | M | 23.0000 |
| 7 | M | 23.0000 |
+---------+--------+----------+
12 rows in set (0.001 sec)
count 统计数量, 行数
https://www.cnblogs.com/hider/p/11726690.html
select count(*) from TABLE_NAME; 查表一共多少行
select count(字段) from TABLE_NAME; 查某一字段,一个有多少非空数值
select count(任意数字) from TABLE_NAME; 等价于select count(*) from TABLE_NAME;
排序
表的默认排序是按照字段顺序排序, 先第一个字段排序, 然后以此类推, 并且是正序
order by COLUMN_NAME; 默认正序.
数字排序就是按照数字大小, 日期按照日期大小, 字母就是按照字符集的排序顺序
如果某一字段有空,按正序排, null会排在最前面, 倒序是在最后的
如何使得, 按照编号正序, 并且null放在后面?
MariaDB [hellodb]> select * from students order by -classid desc; #在order by的COLUMN名字前面加'-', 两者间可以有空格, 这种方法只针对数字类型
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.001 sec)
limit
过滤, 挑出某些行
limit m,n: 跳过m行, 从第m+1行开始显示, 一直显示n行
6.2 多表查询
多表查询, 即查询结果来自于多张表
- 子查询: 在SQL语句中, 嵌套着查询语句, 性能较差, 基于某语句的查询结果再次进行的查询
- 联合查询: UNION
- 交叉连接: 笛卡尔乘积
- 内连接:
等值连接: 让表之间的字段以"等值"建立连接关系
非等值连接:
自然连接: 去掉重复列的等值连接 - 外连接:
左外连接: FROM 表1 left join 表2 on 表1.col=表2.col
右外连接: FROM 表1 right join 表2 on 表1.col=表2.col - 自连接: 本表和本表进行连接查询
6.2.1 子查询
常用在where子句中的子查询
- 用于比较表达式的子查询; 子查询仅能返回单个值, 也叫标量子查询
#注意, 子查询要加在()里面
#用于比较表达式中的子查询, 子查询只能返回单个值
select name,age from students where age > (select avg(age) from teachers);
- 用于IN中的子查询, 子查询应该单独查询, 并且返回一个或多个值, 重新构成列表
select name, age from students where age in (select age from teachers);
- 用于EXISTS和NOT EXISTS
EXISTS和NOT EXIST子句的返回值是一个bool值. EXISTS内部有一个子查询语句(select ... from ...), 将其称为EXISTS的内查询语句. 其内查询语句返回一个结果集. EXISTS子句根据其内查询语句的结果集空或者非空, 返回一个布尔值. 将外查询表的每一行, 代入内查询作为检验, 如果内查询返回的结果为非空值, 则EXISTS子句返回TRUE值, 外查询的这一行数据便可作为外查询的结果行返回, 否则不能作为结果.
举例: 查teachers表中, 哪些老师有学生, 并显示学生信息?
mysql> select * from students s where exists (select * from teachers t where s.teacherid = t.tid);
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from teachers where exists (select * from students where students.teacherid=teachers.tid);
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
说明:
1. EXISTS 或 (NOT EXISTS) 用在where语句后, 且后面紧跟子查询语句(带括号)
2. EXISTS 或 (NOT EXISTS) 只关系子查询有没有结果, 并不关心子查询的结果具体是什么
3. 上述语句把students的记录逐条代入到EXISTS后面的子查询中, 如果子查询结果集不为空, 也就是学生表中的某条记录的teacherid在老师表中有对应相等的tid, 那么就符合要求, 这样子查询的结果集就是非空, 即说明存在, 那么这条student上的记录就会出现在最终结果集, 否则被排除
4. EXISTS 和 NOT EXISTS的并集正好是外部查询的全集
5. 最终希望显示哪个表的内容, 那么就把这个表放在外部查询
- 用于FROM子句中的子查询
范例: 查询出, 平均年龄大于30岁的班级id(这里平均年龄这类聚合函数是不能放在where后作比较的, 因此, 涉及到聚合函数和常量的比较,要放到having, 先分组, 然后having比较)
方法1. 不用FROM子查询
mysql> select avg(age) as aage, classid from students where classid is not null group by classid having avg(age) > 30; 分组后筛选用having
+---------+---------+
| aage | classid |
+---------+---------+
| 36.0000 | 2 |
| 46.0000 | 5 |
+---------+---------+
2 rows in set (0.00 sec)
方法2. 用FROM子查询, 子查询的结果也是一个结果集, 一个表, 然后从这个表中再做查询
mysql> select s.aage , s.classid from (select avg(age) as aage, classid from students where classid is not null group by classid) as s where s.aage > 30;
+---------+---------+
| aage | classid |
+---------+---------+
| 36.0000 | 2 |
| 46.0000 | 5 |
+---------+---------+
2 rows in set (0.01 sec)
- 子查询用于更新表信息
mysql> update teachers set age=(select avg(age) from students) where tid=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
6.2.2 联合查询
联合查询就是多表纵向合并
要求两张表的字段数量相同
在MySQL中, 合并的字段可以是不同的类型, 但是无意义
字段名不是必须相同的, 最终显示的字段名, 是看哪个表写在了union前.
union并不存在主键冲突的问题, 因为union的结果并不是真实存在的表
范例:
mysql> select * from teachers union select stuid , name, age, gender from students;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
+-----+---------------+-----+--------+
29 rows in set (0.00 sec)
mysql> select stuid , name, age, gender from students union select * from teachers;
+-------+---------------+-----+--------+
| stuid | name | age | gender |
+-------+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-------+---------------+-----+--------+
29 rows in set (0.00 sec)
自己和自己union的结果就是本身
mysql> select * from teachers union select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
如果用了union all, 那么自己和自己union的结果就是两个表落在一起
mysql> select * from teachers union all select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
8 rows in set (0.00 sec)
UNION 和 UNION ALL的区别?
UNION ALL将两个结果的结果落到一起
UNION 将两个结果的结果落到一起, 会自动去掉重复行
6.2.3 交叉连接
笛卡尔乘积: 即 cross join
范例:
mysql> select * from teachers cross join students;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| TID | Name | Age | Gender | StuID | Name | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| 4 | Lin Chaoying | 27 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Miejue Shitai | 77 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
...
6.2.4 内连接
等值连接:
两个表取交集, 找两个表中指定的两个字段的值相同的行
格式:
select <select list> from tableA as A inner join tableB as B on A.col1=B.col2
范例: 查询学生的负责老师的信息
注意: 一旦给表起了别名后, 就必须使用别名, 而不能用原表名了
mysql> select * from students as s inner join teachers as t on s.teacherid = t.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 27 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
范例: 去掉重复字段, 获取指定字段
mysql> select s.name "学生姓名", s.age "学生年龄", s.gender "学生性别", classid "班级", tid "教师编号", t.name "教师姓名" from students as s innerr join teachers as t on s.teacherid = t.tid;
+--------------+--------------+--------------+--------+--------------+---------------+
| 学生姓名 | 学生年龄 | 学生性别 | 班级 | 教师编号 | 教师姓名 |
+--------------+--------------+--------------+--------+--------------+---------------+
| Shi Zhongyu | 22 | M | 2 | 3 | Miejue Shitai |
| Ding Dian | 32 | M | 4 | 4 | Lin Chaoying |
| Yu Yutong | 26 | M | 3 | 1 | Song Jiang |
+--------------+--------------+--------------+--------+--------------+---------------+
3 rows in set (0.00 sec)
不等值连接
找两个表中指定的两个字段的值不相同的行
mysql> select s.name "学生姓名", s.age "学生年龄", s.gender "学生性别", classid "班级", tid "教师编号", t.name "教师姓名" from students as s inner join teachers as t on s.teacherid != t.tid;
+--------------+--------------+--------------+--------+--------------+---------------+
| 学生姓名 | 学生年龄 | 学生性别 | 班级 | 教师编号 | 教师姓名 |
+--------------+--------------+--------------+--------+--------------+---------------+
| Shi Zhongyu | 22 | M | 2 | 4 | Lin Chaoying |
| Shi Zhongyu | 22 | M | 2 | 2 | Zhang Sanfeng |
| Shi Zhongyu | 22 | M | 2 | 1 | Song Jiang |
| Shi Potian | 22 | M | 1 | 4 | Lin Chaoying |
| Shi Potian | 22 | M | 1 | 3 | Miejue Shitai |
| Shi Potian | 22 | M | 1 | 2 | Zhang Sanfeng |
| Shi Potian | 22 | M | 1 | 1 | Song Jiang |
| Xie Yanke | 53 | M | 2 | 4 | Lin Chaoying |
| Xie Yanke | 53 | M | 2 | 3 | Miejue Shitai |
| Xie Yanke | 53 | M | 2 | 2 | Zhang Sanfeng |
| Xie Yanke | 53 | M | 2 | 1 | Song Jiang |
| Ding Dian | 32 | M | 4 | 3 | Miejue Shitai |
| Ding Dian | 32 | M | 4 | 2 | Zhang Sanfeng |
| Ding Dian | 32 | M | 4 | 1 | Song Jiang |
| Yu Yutong | 26 | M | 3 | 4 | Lin Chaoying |
| Yu Yutong | 26 | M | 3 | 3 | Miejue Shitai |
| Yu Yutong | 26 | M | 3 | 2 | Zhang Sanfeng |
+--------------+--------------+--------------+--------+--------------+---------------+
17 rows in set (0.00 sec)
SQL99语法:
select <select list> from tableA as A inner join tableB as B on A.col1=B.col2
SQL92语法:
select <select list> from tableA, tableB where tableA.col1=tableB.col2;
6.2.5 左外连接
语法:
select <select list> from tableA A left [outer] join tableB B on A.col1=B.col2;
其工作原理是, 以left join左边的表为基准, 全部保留, left join 右边的表中, 满足 A.col1=B.col2的行保留, 作为交集, 不满足的排除. 最终结果的行数, 以左边的表为基准, 如果左边的表行数本身多余右边, 那么右边的表除了交集部分保留, 其余行全用NULL补全. 如果左边的行本身没有右边的行多, 那么左边的行完全保留, 右边只保留交集的行
查询的需求类似, 哪些xxx没有yyy, 这样xxx的表就是左边的, 也就是基表
范例: 查看哪些学生没有老师
mysql> select * from students left join teachers on students.teacherid = teachers.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL | #teachers表中tid和students表中的teacherid不等的行, 都用NULL补全
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.00 sec)
mysql> select * from teachers right join students on students.teacherid = teachers.tid;
+------+---------------+------+--------+-------+---------------+-----+--------+---------+-----------+
| TID | Name | Age | Gender | StuID | Name | Age | Gender | ClassID | TeacherID |
+------+---------------+------+--------+-------+---------------+-----+--------+---------+-----------+
| 1 | Song Jiang | 45 | M | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 3 | Miejue Shitai | 77 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Lin Chaoying | 93 | F | 4 | Ding Dian | 32 | M | 4 | 4 |
| NULL | NULL | NULL | NULL | 2 | Shi Potian | 22 | M | 1 | 7 |
| NULL | NULL | NULL | NULL | 3 | Xie Yanke | 53 | M | 2 | 16 |
| NULL | NULL | NULL | NULL | 6 | Shi Qing | 46 | M | 5 | NULL |
| NULL | NULL | NULL | NULL | 7 | Xi Ren | 19 | F | 3 | NULL |
| NULL | NULL | NULL | NULL | 8 | Lin Daiyu | 17 | F | 7 | NULL |
| NULL | NULL | NULL | NULL | 9 | Ren Yingying | 20 | F | 6 | NULL |
| NULL | NULL | NULL | NULL | 10 | Yue Lingshan | 19 | F | 3 | NULL |
| NULL | NULL | NULL | NULL | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| NULL | NULL | NULL | NULL | 12 | Wen Qingqing | 19 | F | 1 | NULL |
| NULL | NULL | NULL | NULL | 13 | Tian Boguang | 33 | M | 2 | NULL |
| NULL | NULL | NULL | NULL | 14 | Lu Wushuang | 17 | F | 3 | NULL |
| NULL | NULL | NULL | NULL | 15 | Duan Yu | 19 | M | 4 | NULL |
| NULL | NULL | NULL | NULL | 16 | Xu Zhu | 21 | M | 1 | NULL |
| NULL | NULL | NULL | NULL | 17 | Lin Chong | 25 | M | 4 | NULL |
| NULL | NULL | NULL | NULL | 18 | Hua Rong | 23 | M | 7 | NULL |
| NULL | NULL | NULL | NULL | 19 | Xue Baochai | 18 | F | 6 | NULL |
| NULL | NULL | NULL | NULL | 20 | Diao Chan | 19 | F | 7 | NULL |
| NULL | NULL | NULL | NULL | 21 | Huang Yueying | 22 | F | 6 | NULL |
| NULL | NULL | NULL | NULL | 22 | Xiao Qiao | 20 | F | 1 | NULL |
| NULL | NULL | NULL | NULL | 23 | Ma Chao | 23 | M | 4 | NULL |
| NULL | NULL | NULL | NULL | 24 | Xu Xian | 27 | M | NULL | NULL |
| NULL | NULL | NULL | NULL | 25 | Sun Dasheng | 100 | M | NULL | NULL |
+------+---------------+------+--------+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
6.2.6 右外连接
左右外连接工作原理相同, 只不过是左右的区别
语法:
select <select list> from tableA A right join tableB B on A.col1=B.col2
范例:
mysql> select * from students as s right join teachers as t on s.teacherid=t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 27 | F |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
等于左外连接
select <select list > from tableB B left join tableA A on Bcol1=A.col2
范例:
mysql> select * from students as s right join teachers as t on s.teacherid=t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M | # students表中teacherid和teachers表中tid不等的行, 用NULL补全
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 27 | F |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
mysql> select * from teachers as t left join students as s on t.tid=s.teacherid;
+-----+---------------+-----+--------+-------+-------------+------+--------+---------+-----------+
| TID | Name | Age | Gender | StuID | Name | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+-------------+------+--------+---------+-----------+
| 1 | Song Jiang | 45 | M | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 2 | Zhang Sanfeng | 94 | M | NULL | NULL | NULL | NULL | NULL | NULL | # students表中teacherid和teachers表中tid不等的行, 用NULL补全
| 3 | Miejue Shitai | 77 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Lin Chaoying | 27 | F | 4 | Ding Dian | 32 | M | 4 | 4 |
+-----+---------------+-----+--------+-------+-------------+------+--------+---------+-----------+
4 rows in set (0.00 sec)
左外连接扩展: 左外连接, 除去交集
左外连接除去交集, 就是只保留右边表中条件字段值为NULL的行
select <select list> from tableA A left join tableB B on A.col1=B.col2 where B.col2 is NULL;
右外连接扩展: 右外连接, 除去交集
右外连接除去交集, 就是只保留左边表中条件字段值为NULL的行
select <select list> from tableA A right join tableB B on A.col1=B.col2 where A.col1 is NULL;
6.2.7 完全外连接
实现逻辑, 两张表, 先取交集, 之后, 左边表不属于交集的行, 保留, 并且, 右边的表会用NULL补全
同时, 右边的表中, 不属于交集的行也会保留, 并且, 左边的表也会有NULL补全
语法:
select <select list> from tableA A full outer join tableB B on A.col1=B.col2;
注意: MySQL不支持上面的full outer join语法
MySQL实现方法:
左外连接 union 右外连接
左外连接和右外连接联合, 重复的部分, 由于union会去重, 所以自动会去掉, 剩下的部分就是完全外连接了
范例:
mysql> select * from students left join teachers on students.teacherid=teachers.tid
-> union
-> select * from students right join teachers on students.teacherid=teachers.tid;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 27 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
26 rows in set (0.00 sec)
6.2.8 完全外连接扩展
即除去完全外连接中的交集部分
可以用左外连接扩展, union 右外连接扩展
范例:
mysql> select * from students left join teachers on students.teacherid=teachers.tid where teachers.tid is null
-> union
-> select * from students right join teachers on students.teacherid=teachers.tid where students.teacherid is null;
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+---------------+------+--------+---------+-----------+------+---------------+------+--------+
23 rows in set (0.00 sec)
6.2.9 自连接
即: 表自身连接自身
范例: 自连接
查询员工对应领导的信息
mysql> select * from emp;
+------+------+----------+
| id | name | leaderid |
+------+------+----------+
| 1 | zhao | NULL |
| 2 | qian | 1 |
| 3 | sun | 2 |
| 4 | li | 3 |
+------+------+----------+
4 rows in set (0.00 sec)
mysql> select * from emp as e inner join emp as l on e.leaderid=l.id;
+------+------+----------+------+------+----------+
| id | name | leaderid | id | name | leaderid |
+------+------+----------+------+------+----------+
| 2 | qian | 1 | 1 | zhao | NULL |
| 3 | sun | 2 | 2 | qian | 1 |
| 4 | li | 3 | 3 | sun | 2 |
+------+------+----------+------+------+----------+
3 rows in set (0.00 sec)
补充: ifnull函数
mysql> select * from emp as e left join emp as l on e.leaderid=l.id;
+------+------+----------+------+------+----------+
| id | name | leaderid | id | name | leaderid |
+------+------+----------+------+------+----------+
| 1 | zhao | NULL | NULL | NULL | NULL |
| 2 | qian | 1 | 1 | zhao | NULL |
| 3 | sun | 2 | 2 | qian | 1 |
| 4 | li | 3 | 3 | sun | 2 |
+------+------+----------+------+------+----------+
4 rows in set (0.00 sec)
mysql> select e.name, ifnull(l.name,"无上级") leader from emp as e left join emp as l on e.leaderid=l.id;
+------+-----------+
| name | leader |
+------+-----------+
| zhao | 无上级 |
| qian | zhao |
| sun | qian |
| li | sun |
+------+-----------+
4 rows in set (0.00 sec)
6.2.10 select语句处理顺序
FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY --> LIMIT
7 MySQL用户管理
相关数据库和表
元数据数据库: mysql
系统授权表: db, host, user, columns_priv, procs_priv, proxies_priv
用户账号格式
'USERNAME'@'HOST'
@'HOST':
主机名: user1@'web1.mysql.org'
IP地址或网络地址
user1@'10.0.0.0/255.255.255.0' # 不支持CIDR
支持通配符; %, 任意长度的任意字符 ; _,任意单个字符
没有localhost权限的账户, 默认无法在MySQL本地连接登录, 因为, 在本地连接会被认为是localhost, 走socket文件
如果想登陆, 需要指定-h IP(MySQL监听的ip)
创建用户: create user
mysql> create user 'USERNAME'@'HOST' [identified by 'PASSWORD'];
默认创建的用户权限是USAGE, 只能连接到数据库, 只能看到information_schema库, 别的库都看不到
用户重命名: rename user
rename user old_user_name to new_user_name; #必须指定正确的格式, USERNAME@'HOST'
删除用户: drop user
drop user 'USERNAME'@'HOST';
范例: 删除默认空用户
drop user ''@'localhost';
修改密码
注意:
MySQL 5.7后, 密码放到了authentication_string字段, password字段被删除了. MySQL 5.6以前, 密码放在password字段.
修改密码方法:
MySQL5.6版本:
修改密码: MySQL 5.6, 密码默认放在password字段
mysql> set password for root@'localhost' = password('centos'); #用set password, 必须使用password函数给密码加密, 否则无法重置密码. 立即生效
Query OK, 0 rows affected (0.00 sec)
5.6版本不支持alter, 5.7开始支持
mysql> alter user root@'localhost' identified by '123';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123'' at line 1
破解root密码:
破解密码要进入跳过授权检查模式, 不支持set password改密码或者alter命令改密码, 需要用update命令来直接更新mysql.user表中的root用户密码, 并且必须使用password函数来加密密码, 否则密码会明文保存, 虽然密码会重置但是之后无法登录
#1. 关闭mysql服务
[00:12:33 root@mysql-4 ~]#service mysqld stop
Shutting down MySQL.. SUCCESS!
#2. 编辑mysql配置文件, 在mysqld配置选项中, 添加 skip-grant-tables 服务器选项
[00:12:39 root@mysql-4 ~]#vim /etc/my.cnf
skip-grant-tables
skip-networking # 同时关闭网络连接, 因为跳过授权表, 任何人都可以登录MySQL, 不安全
#3. 启动mysql服务
[00:12:45 root@mysql-4 ~]#service mysqld start
Starting MySQL. SUCCESS!
#4. 使用mysql命令登录, 这时不需要root密码
[00:11:34 root@mysql-4 ~]#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
...
#5. 使用update命名直接更新mysql.user表的password字段
update mysql.user set password=password('centos') where user='root' and host='localhost'; #这里必须使用password函数修改密码, 否则虽然密码可以修改, 但之后重启服务后是无法登录的
#6. 修改密码后, 使用 flush privileges 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#7. 退出mysql, 关闭mysql服务
[00:12:33 root@mysql-4 ~]#service mysqld stop
Shutting down MySQL.. SUCCESS!
#8. 编辑配置文件, 删除之前添加的 skip-grant-tables 和 skip-networking服务器选项
vim /etc/my.cnf
#skip-grant-tables
#skip-networking
#9. 启动mysql服务
service mysqld start
#10 使用新的密码登录即可
[00:12:54 root@mysql-4 ~]#mysql -uroot -pcentos
MySQL5.7版本:
修改密码:
alter user USERNAME@'HOST' identified by 'PASSWORD'; #立即生效, 自动给密码加密
set password for root@'localhost' = password('centos'); # 或者使用set password
#必须使用password函数加密, 5.7版本开始不推荐使用, 推荐用alter命令修改密码.
#而在8.0中, set password .. password('PASSWORD'); 这一语法已被删除, 使用alter命令修改,
#或者8.0中使用SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string'; # 不用指定加密函数
破解root密码:
#MySQL5.7版本破解root密码过程和5.6版本基本一致, 唯一区别就是修改密码时, 需要修改authentication_string字段, 而5.6是修改password字段
update mysql.user set authentication_string = password('centos') where user = 'root' and host = 'localhost';
MySQL8.0版本:
修改密码:
alter user USERNAME@'HOST' identified by 'PASSWORD'; #立即生效, 自动给密码加密
SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string'; #不用指定加密函数
破解root密码
与5.6和5.7不同的是, 越过授权进入mysql后不能直接修改密码, 需要把authentication_string设置为空, authentication_string=''
然后取消跳过网络和授权, 重启登录mysql, 使用alter user 再修改密码
8 权限管理
MySQL8.0后, 必须先创建用户, 再授权
用户的权限信息保存在mysql.user表
8.1 查看权限类型
3306 [mysql]>show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
8.2 授权操作
语法:
grant 权限 on 对象 to 用户 identified by 密码 [with grant option] # 8.0前
grant 权限 on 对象 to 用户 [with grant option] # 8.0开始
权限:
all --> 管理员
with grant option --> 如果管理员需要给其他用户授权, 也需要with grant option权限, 有了with grant option权限也可以删除其他用户, 和管理员用户
权限1, 权限2,... --> 普通用户只给与固定的权限
对象:
库/表
*.* --> 所有库所有表 --> 管理员
mysql.user --> 针对mysql库的user表
mysql.* --> 针对mysql库所有表 --> 一般给普通用户某个库的权限
mysql.user(user,host,authentication_string) --> 针对列设置权限, 用的较少
- 案例1: 创建一个管理员用户admin1, 能够通过10.0.0.%网段登录数据库, 并且能够给其他用户授权
3306 [mysql]>grant all on *.* to admin1@'10.0.0.%' identified by '123' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
查看用户权限
3306 [mysql]>show grants for admin1@'10.0.0.%';
+----------------------------------------------------------------------+
| Grants for admin1@10.0.0.% |
+----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin1'@'10.0.0.%' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
- 案例2: 授权开发用户, 对于app库的所有表有create, update, delete, insert, select权限
3306 [mysql]>grant create, update, insert, delete, select on app.* to bus1@'10.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
3306 [mysql]>show grants for bus1@'10.0.0.%';
+------------------------------------------------------------------------------+
| Grants for bus1@10.0.0.% |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bus1'@'10.0.0.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `app`.* TO 'bus1'@'10.0.0.%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL授权表
如果是*.*的权限, 可以通过select * from mysql.user \G去查看对应的权限
如果是针对库级别设定的权限, 可以通过select * from mysql.db \G去查看对应权限
如果是针对表级别设定的权限, 可以通过select * from mysql.tables_priv \G去查看对应权限
如果是针对列级别设定的权限, 可以通过select * from mysql.columns_priv \G去查看对应权限
用户基础信息, 用户名, 密码, 登录ip都是在mysql.user表存储
8.3 回收权限操作
想要单纯的添加权限, 只需再次grant授权, 把想要的权限加进去
MySQL不支持重复授权, 对于多余的权限, 想要删除, 需要使用revoke回收
语法:
revoke 权限 on 对象 from 用户
- 案例1: 回收bus1@'10.0.0.%'用户, 对于app库的插入权限
3306 [mysql]>show grants for bus1@'10.0.0.%';
+------------------------------------------------------------------------------+
| Grants for bus1@10.0.0.% |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bus1'@'10.0.0.%' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `app`.* TO 'bus1'@'10.0.0.%' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3306 [mysql]>revoke insert on app.* from bus1@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
3306 [mysql]>show grants for bus1@'10.0.0.%';
+----------------------------------------------------------------------+
| Grants for bus1@10.0.0.% |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bus1'@'10.0.0.%' |
| GRANT SELECT, UPDATE, DELETE, CREATE ON `app`.* TO 'bus1'@'10.0.0.%' |
+----------------------------------------------------------------------+
2 rows in set (0.00 sec)
9 连接管理
- 案例1: 创建admin3用户, 允许通过本地socet文件登录MySQL
create user admin3@'localhost' identified by '123';
mysql -uadmin3 -p123 -S /tmp/mysql.sock
- 案例2: 判断用户是通过远程还是本地socket文件登录
通过show processlist;命令查看
MySQL会为每个客户端的连接分配一个连接线程
3306 [(none)]>show processlist;
+----+--------+------------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+------------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 7 | admin1 | 10.0.0.227:41626 | NULL | Sleep | 4 | | NULL |
+----+--------+------------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
- 案例3: 导入脚本
gunzip world.sql.gz # Oracle官方提供的脚本, 用gunzip解压.gz文件
mysql -uroot -p123456 < world.sql
