[TOC]
MySQL客户端命令
- mysql
## 查看命令帮助
- ?
- \?
- help
- \h
## 查看状态
- status
- \s
## 退出
- exit
- quit
- \q
## 结束当前的SQL语句
- \c
- Ctrl + c
- MySQL5.6中：直接退出MySQL
- MySQL5.7中：结束当前SQL语句，类似于\c
## 在MySQL中执行系统命令
- \!
- system 例：system ls -l
## 临时将操作内容和结果记录到指定的文件中（退出数据库结束）
- tee 例：tee /tmp/mysql.log
- \T 例：\T /tmp/mysql.log
## 切换数据库
- use 例：use mysql
- \u 例：\u mysql
## 导入sql文件中的数据（指定要导入的数据的位置）
- source 例：source /tmp/sys.sql
- \. 例：\. /tmp/zls.sql
## 输出内容格式化（key:value）方式，显示数据
- \G 例：mysql> select * from mysql.user\G
### 客户端配置，加入以下内容 显示当前所在数据库及登录用户
[mysql]
prompt="\u@\h:\d>"- mysqladmin
## 想使用mysqladmin前提：服务端必须开启
# 1.修改密码
[root@db03 ~]# mysqladmin -uroot -p123 password '123'
# 2.检测MySQL是否存活
[root@db03 ~]# mysqladmin -uroot -p123 ping
# 3.库外创建数据库
[root@db03 ~]# mysqladmin -uroot -p123 create 库名
# 4.删除数据库
[root@db03 ~]# mysqladmin -uroot -p123 drop 库名
# 5.查看MySQL默认配置
[root@db03 ~]# mysqladmin -uroot -p123 variables
# 6.库外刷新授权表
[root@db03 ~]# mysqladmin -uroot -p123 flush-host
[root@db03 ~]# mysqladmin -uroot -p123 reload
# 7.刷新日志
[root@db03 ~]# mysqladmin -uroot -p123 flush-log
# 8.停库
[root@db03 ~]# mysqladmin -uroot -p123 shutdown数据库配置设置默认字符集
## 修改以创建库的字符集方法
# 先查看所有库的字符集
mysql> show variables like '%char%';
+--------------------------+-----------------------------------+
| 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 | /app/mysql-5.6.50/share/charsets/ |
+--------------------------+-----------------------------------+
# 将不是utf8的库名复制添加到mysql配置文件的[mysqld]下指定utf8
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
character_set_server=utf8
# 重启MySQL
[root@db01 ~]# systemctl restart mysqld
# 重新查看
mysql> show variables like '%char%';
+--------------------------+-----------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /app/mysql-5.6.50/share/charsets/ |
+--------------------------+-----------------------------------+建库建表级别设置字符集
## 建库设置字符集
create database zls111 charset utf8 collate utf8_general_ci;
## 建表设置字符集
create table test(
id int,
name varchar(10)
) default charset utf8;SQL层 SQL语句
DDL（Database Definition Language）数据定义语言
针对库、表的操作
## 数据库：
# 增：建库
create database 库名;
【语法：】
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: https://dev.mysql.com/doc/refman/5.7/en/create-database.html
## 创库方法
create database 库名;
create schema 库名;
例：
1）root@localhost:(none)>create schema hg;
2）root@localhost:(none)>create database hg1;
# 创建库时指定字符集
create database 库名 character set utf8;
create database 库名 charset utf8;
#例
1）root@localhost:(none)>create database hg3 character set utf8;
2）root@localhost:(none)>create database hg4 charset utf8;
# if not exists ：判断要创建的库存不存在 不存在就创建
create database if not exists 库名 charset utf8;
#例：
1）root@localhost:(none)>create database if not exists hg charset utf8;
# collate ：指定校验规则 比如_bin结尾的就是大小写不敏感 _ci就是大小写敏感
create database if not exists 库名 charset utf8 collate utf8_bin;
#例：
1）root@localhost:(none)>create database if not exists hg5 charset utf8 collate utf8_bin;
## 删
drop database 库名;
#例：
1）root@localhost:(none)>drop database hg5;
## 改
# 修改字符集
alter database 库名 charset latin1;
#例：
1）root@localhost:(none)>show create database hg;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| hg | CREATE DATABASE `hg` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
root@localhost:(none)>alter database hg charset utf8;
root@localhost:(none)>show create database hg;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| hg | CREATE DATABASE `hg` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
### 对于表的操作
# 增
## 建表语法：
create table 表名(字段1 数据类型 约束,字段 数据类型 约束....)
# 建表，至少要给的是 表名 字段名称 数据类型
## 要么先切到要建表的库 要么在要建的表名前加库名
#例：
root@localhost:(none)>create table hg.student(id int not null primary key auto_increment,name varchar(10),age tinyint,gender enum('0','1'));
# 可以写成以下格式 方便区分每条字段
root@localhost:(none)>create table hg.student(
-> id int,
-> name varchar(10),
-> age tinyint,
-> gender enum('0','1'));
Query OK, 0 rows affected (0.01 sec)
root@localhost:(none)>desc hg.student;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 数据类型
- int： 整数 范围：-2^31 ~ 2^31 -1
- zerofill：零填充 当int(3) 指定位数后 还是可以写入超过指定位数的的内容
所以约束的不是整型的位数 而是加零填充后 会用0填充到指定位数
- varchar：字符类型 （变长：字符串内容有几个字符 都算占了指定的个数里的几个字符）
- char： 字符类型 （定长：不管字符串内容有几个字符 都算占了指定的个数的字符数）
name varchar(10)
haige # 占了5个
xiaoming # 占了8个
name char(10)
zdh # 占了10个
xiaoming # 占了10个
- bigunt： 较大整数 范围 2^63 ~ 2^63-1
- tinyint： 较小整数 -128 ~ 127（一共255个数 如果指定了非负 那么这个选项的范围就是0~255）
- enum： 枚举类型（指定选项 比如 男，女 那输入的内容只能是 男 或者 女）
例：
enum('f','m')
male female
enum('A','B','C','D')
- datetime： 时间类型 年月日时分秒
- float：单精度浮点数 语法：float (指定数字的位数 不包括小数点)
- double：双精度浮点数 语法：double (指定小数点前面的数的位数,指定小数点后面数的位数)
- bit：位字段值
## 约束
- not null： 非空
- null：可以为空
- primary key： 主键（唯一且非空的）一张表只能有一个主键
- auto_increment： 自增（此列必须是：primary key主键 或者unique key唯一键）
- unique key： 唯一键，输入的内容是唯一的 不能重复 unique key + not null 相当于主键
- default： 设置默认值（比如性别 默认 男）
- unsigned： 无符号（非负）(必须写在数据类型后面，用来约束数据类型)
- comment： 注释该字段
# 例：学生表格
root@localhost:(none)>mysql> create table hg1.stu(
-> sno bigint(20) primary key auto_increment comment '学号(主键)',
-> sname varchar(10) not null comment '学生姓名',
-> sage tinyint unsigned not null comment '学生年龄',
-> ssex enum('0','1') not null default '1' comment '学生性别（1是男，0是女s）默认为男）',
-> sbirthday datetime default null comment '学生生日',
-> class varchar(5) not null comment '学生班级');
Query OK, 0 rows affected (0.00 sec)
mysql> desc hg1.stu;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| sno | bigint(20) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | NULL | |
| sage | tinyint(3) unsigned | NO | | NULL | |
| ssex | enum('0','1') | NO | | 1 | |
| sbirthday | datetime | YES | | NULL | |
| class | varchar(5) | NO | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
## 删
# 删表
drop table 表名;
## 改
# 修改表名语法：
alter table 表名 rename 新表名;
#例
root@localhost:(none)>alter table hg.student rename hg.stu;
### 修改表结构
alter table 表名 add 字段名 数据类型 属性约束;
alter table 表名 add 字段名 数据类型 属性约束 first;
alter table 表名 add 字段名 数据类型 属性约束 after 字段名;
## 添加字段
root@localhost:(none)>alter table hg.stu add hmbb varchar(1) not null;
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 添加字段（将字段放在最后一列）
root@localhost:(none)>alter table hg.stu add pdx varchar(1) not null;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
| pdx | varchar(1) | NO | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 添加字段（放到指定字段的后面）
root@localhost:(none)>alter table hg.stu add zyg int after hmbb;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
| zyg | int(11) | YES | | NULL | |
| pdx | varchar(1) | NO | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 添加字段（将字段放到最前面）
root@localhost:(none)>alter table hg.stu add plb int first;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| plb | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
| zyg | int(11) | YES | | NULL | |
| pdx | varchar(1) | NO | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 一次性添加多个字段
1)
root@localhost:(none)>alter table hg.stu add xlb int,add yz int;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| plb | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
| zyg | int(11) | YES | | NULL | |
| pdx | varchar(1) | NO | | NULL | |
| xlb | int(11) | YES | | NULL | |
| yz | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
2)
root@localhost:(none)>alter table hg.stu add xlb1 int first,add yz1 int after zyg;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| xlb1 | int(11) | YES | | NULL | |
| plb | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
| zyg | int(11) | YES | | NULL | |
| yz1 | int(11) | YES | | NULL | |
| pdx | varchar(1) | NO | | NULL | |
| xlb | int(11) | YES | | NULL | |
| yz | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 删除字段
root@localhost:(none)>alter table hg.stu drop plb;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| xlb1 | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
| zyg | int(11) | YES | | NULL | |
| yz1 | int(11) | YES | | NULL | |
| pdx | varchar(1) | NO | | NULL | |
| xlb | int(11) | YES | | NULL | |
| yz | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
## 字段修改
change
modify
alter table 表名 modify 字段名 数据类型 约束属性;
root@localhost:(none)>alter table hg.stu modify yz1 varchar(10) comment 'mmp';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| xlb1 | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
| zyg | int(11) | YES | | NULL | |
| yz1 | varchar(10) | YES | | NULL | |
| pdx | varchar(1) | NO | | NULL | |
| xlb | int(11) | YES | | NULL | |
| yz | int(11) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+
alter table 表名 change 旧字段名 新字段名 数据类型 约束属性;
root@localhost:(none)>alter table hg.stu change yz pz char(10) comment 'xin';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)>desc hg.stu;
+--------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| xlb1 | int(11) | YES | | NULL | |
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(4) | YES | | NULL | |
| gender | enum('0','1') | YES | | NULL | |
| hmbb | varchar(1) | NO | | NULL | |
| zyg | int(11) | YES | | NULL | |
| yz1 | varchar(10) | YES | | NULL | |
| pdx | varchar(1) | NO | | NULL | |
| xlb | int(11) | YES | | NULL | |
| pz | char(10) | YES | | NULL | |
+--------+---------------+------+-----+---------+-------+DML
# 对表中数据进行操作
# 对数据的增删改
## 增
insert
insert into 表名('字段1','字段2') 值('值1','值2');
# 插入单条数据
#例：以下两种写法都生效
1）
root@localhost:(none)>insert into hg1.stu(sname,sage,sbirthday,class) values('hmbb',3,NOW(),'q1');
Query OK, 1 row affected (0.01 sec)
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | hmbb | 3 | 1 | 2023-07-25 17:00:25 | q1 |
+-----+-------+------+------+---------------------+-------+
2）
root@localhost:(none)>insert hg1.stu(sname,sage,sbirthday,class) values('hmbb',3,NOW(),'q1');
Query OK, 1 row affected (0.00 sec)
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | hmbb | 3 | 1 | 2023-07-25 17:00:25 | q1 |
| 2 | hmbb | 3 | 1 | 2023-07-25 17:05:56 | q1 |
+-----+-------+------+------+---------------------+-------+
# 插入多条数据
#例
1)
root@localhost:(none)> insert hg1.stu(sname,sage,sbirthday,class) values('pdx',3,NOW(),'q1'),('zyg',2,'2021-11-12 00:00:00','q2');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | hmbb | 3 | 1 | 2023-07-25 17:00:25 | q1 |
| 2 | hmbb | 3 | 1 | 2023-07-25 17:05:56 | q1 |
| 3 | pdx | 3 | 1 | 2023-07-25 17:32:48 | q1 |
| 4 | zyg | 2 | 1 | 2021-11-12 00:00:00 | q2 |
+-----+-------+------+------+---------------------+-------+
2)
root@localhost:(none)>insert hg1.stu values(5,'mg',3,'1',NOW(),'q3'),(6,'ssg',2,'0','2021-11-12 00:00:00','q4');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | hmbb | 3 | 1 | 2023-07-25 17:00:25 | q1 |
| 2 | hmbb | 3 | 1 | 2023-07-25 17:05:56 | q1 |
| 3 | pdx | 3 | 1 | 2023-07-25 17:32:48 | q1 |
| 4 | zyg | 2 | 1 | 2021-11-12 00:00:00 | q2 |
| 5 | mg | 3 | 1 | 2023-07-25 17:38:24 | q3 |
| 6 | ssg | 2 | 0 | 2021-11-12 00:00:00 | q4 |
+-----+-------+------+------+---------------------+-------+
## 删
# 危险写法：直接delete删 这样会把这个表里所有的数据都删完
delete
delete from 库名.表名;
delete from 库名.表名 where;
# 要养成习惯 使用delete先在后面加上where（条件） 指定要删的内容
# 注意：一般在删除数据时，我们会根据唯一性（主键，唯一键）的字段，进行删除
root@localhost:(none)>delete from hg1.stu where sno=1 or sno=2;
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 3 | pdx | 3 | 1 | 2023-07-25 17:32:48 | q1 |
| 4 | zyg | 2 | 1 | 2021-11-12 00:00:00 | q2 |
| 5 | mg | 3 | 1 | 2023-07-25 17:38:24 | q3 |
| 6 | ssg | 2 | 0 | 2021-11-12 00:00:00 | q4 |
+-----+-------+------+------+---------------------+-------+
## 删除所有内容（想把数据全部删除也要加where 写一个永远成立的条件即可）
root@localhost:(none)>delete from hg1.stu where 1=1;
Query OK, 5 rows affected (0.00 sec)
root@localhost:(none)> select * from hg1.stu;
Empty set (0.00 sec)
# 删除多个字段内容重复的数据方法： （ and连接多个条件 ）
root@localhost:(none)>select user,host from mysql.user;
+------+-------------+
| user | host |
+------+-------------+
| dev | % |
| rep | 172.16.1.5% |
| root | localhost |
+------+-------------+
3 rows in set (0.00 sec)
root@localhost:(none)>delete from mysql.user where user='dev' and host='%';
Query OK, 1 row affected (0.00 sec)
root@localhost:(none)>select user,host from mysql.user;
+------+-------------+
| user | host |
+------+-------------+
| rep | 172.16.1.5% |
| root | localhost |
+------+-------------+
2 rows in set (0.00 sec)
使用update代替delete做伪删除
## 改
update
update 库.表 set 字段='值';
# update 使用update一定要加条件 不然会把指定字段的所有内容就修改
# 错误写法
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 8 | hmbb | 3 | 1 | 2023-07-25 17:47:39 | q1 |
| 9 | hmbb | 3 | 1 | 2023-07-25 17:47:40 | q1 |
+-----+-------+------+------+---------------------+-------+
root@localhost:(none)>update hg1.stu set sage='1';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 8 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 |
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 |
+-----+-------+------+------+---------------------+-------+
#正确写法（加条件）
root@localhost:(none)>update hg1.stu set sage='2' where sno=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 8 | hmbb | 2 | 1 | 2023-07-25 17:58:47 | q1 |
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 |
+-----+-------+------+------+---------------------+-------+
2 rows in set (0.00 sec)
# 使用update替代delete删除数据（企业中）
# 1.给表中添加状态列
root@localhost:(none)>alter table hg1.stu add status enum('0','1') default '1';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+-------+------+------+---------------------+-------+--------+
| 8 | hmbb | 2 | 1 | 2023-07-25 17:58:47 | q1 | 1 |
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 | 1 |
+-----+-------+------+------+---------------------+-------+--------+
# 2.使用update代替delete删除
root@localhost:(none)>update hg1.stu set status='0' where sno=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+-------+------+------+---------------------+-------+--------+
| 8 | hmbb | 2 | 1 | 2023-07-25 18:09:06 | q1 | 0 |
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 | 1 |
+-----+-------+------+------+---------------------+-------+--------+
# 3.使用select查询时，加上条件
root@localhost:(none)>select * from hg1.stu where status='1';
+-----+-------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+-------+------+------+---------------------+-------+--------+
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 | 1 |
+-----+-------+------+------+---------------------+-------+--------+
### 注意：update修改数据库用户的密码后，需要flush privileges; 刷新数据库 修改其他数据，不需要
DCL
### DCL含义: 控制用户的操作
Database Control Language
数据 控制 语言
## 赋予权限
# 5.6和5.7区别：5.7老版本，grant赋予权限，如果该用户不存在，不会创建用户，5.6和5.7新版本会直接创建用户
# 语法：
grant all on *.* to test@'%' identified by '123';
grant 权限1,权限 on 库.表 to 用户@'主机域' identified by '密码';
# 比如要加上option权限 方法如下;
grant all on *.* to test@'%' identified by '123' with grant option;
## 限制权限（拓展）
# 限制该用户一小时内，查询的次数（连接进入mysql就会查两次）
without max_queries_per_hour
# 限制用户一小时，执行update（改）的次数
max_updates_per_hour
# 限制用户一小时内，连接数据库的次数
max_connections_per_hour
# 限制同一个用户同时可以连接数据库的个数
max_user_connections
# 案例：限制用户一小时内 只能查询3次 只能执行一次update 只能连接一次数据库 同一个用户只能登陆一个
root@localhost:(none)>grant all on *.* to wyk@'%' identified by '123'
-> without max_queries_per_hour 3
-> max_updates_per_hour 1
-> max_connections_per_hour 1
-> max_user_connections 1
-> grant option;
# 像这种限制的权限是没办法回收的 只能删除用户 或者就像下面的 修改它的次数
root@localhost:(none)>grant all on *.* to wyk@'%' identified by '123'
-> with max_queries_per_hour 999
-> max_user_connections 999;
## 回收权限
# revoke语法：
revoke 要回收的权限 on 库.表 from 用户@'主机域';
revoke delete on *.* from wyk@'%';
DQL
### DQL含义： 所有的查都是DQL
Database Query Language
数据 查询 语言
## show
show databases; # 查看数据库
show tables; # 查看表
show tables from 库名; # 查看指定数据库下的表，不用切换数据库
show create database 库名; # 查看建库语句
show create table 表名; # 查看建表语句，也是为了查看注释
show create table 库名.表名; # 查看建表语句，也是为了查看注释（不用切换数据库）
show grants for 用户名@'主机域'; # 查看用户授权语句，也是为了查看该用户的权限
show variables; # 查看所有的内置变量（默认配置）
show variables like '%server'; # 模糊查询（过滤）
show variables like '%server%'; # 用 % 去模糊匹配
show variables like 'server%'; # % 表示匹配指定方位的内容多次或0次
show processlist; # 查看后台执行的sql语句
show full processlist; # 查看完整的后台执行的sql语句
show collation; # 查看所有校验规则
show charset; # 查看所有字符集以及该字符集的默认校验规则
## desc
desc 库.表 # 查看表结构
## 事务控制语句
Transaction Control Language
Database Transaction Language
## select
# 下载练习表 导入到数据库内 该数据导入时 会检测有没有world库 有就删除再创建一个world库 导入数据
[root@db01 ~]# wget https://download.wodeyumengouwo.com/mysql/world.sql
root@localhost:(none)> source /root/world.sql
root@localhost:(none)> show databases;
+--------------------+
| Database |
+--------------------+
| world |
+--------------------+
root@localhost:(none)> use world
root@localhost:world> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
## select 常用简单查询
# 查看该表中所有的字段的记录（所有数据）
select * from 库名.表名;
root@localhost:world> select * from city;
# 查看指定字段的所有数据
select 字段1,字段5,字段... from 表名;
root@localhost:world> select id,name,countrycode from city;
## select 行级查询（翻页功能） （默认为 <=指定的数 ）
select 字段1,字段5,字段... from 表名 limit 指定行数;
# 翻页功能，从1开始查到60 查60行
select id,name,countrycode from city limit 60;
# 翻页功能，从121开始查到180 查60行
select id,name,countrycode from city limit 120,60;
# 翻页功能，从181开始查到241 查60行
select id,name,countrycode from city limit 180,60;
## select 条件查询（精确）
select * from 表名 where 字段='记录';
select * from city where countrycode='CHN';
# 多条件查询 or and
select * from city where countrycode='CHN' or countrycode='USA';
select * from city where countrycode='CHN' and district='heilongjiang';
# 条件查询结合行级查询
select * from city where countrycode='CHN' and district='heilongjiang' limit 10;
# 多条件查询用 in 的方法：
select * from 表名 where 字段 in ('要查询的内容一','要查询内容二');
select * from city where countrycode in ('CHN','USA');
## select 模糊查询
select * from 表名 where like '%记录%';
select * from city where countrycode like '%HN';
select * from city where countrycode like '%HN%';
select * from city where countrycode like 'HN%';
# 条件查询结合行级查询（模糊查询可以接精确查询）
select * from city where countrycode like '%HN%' or countrycode='JPN' limit 10;
## select 排序查询 order by
# 顺序排序
select * from 表名 order by 要排序的记录；
# 不加条件 顺序排序
select * from city order by population;
# 加条件 顺序排序
select * from city where countrycode='CHN' order by population;
# 倒序排序
select * from 表名 order by 要排序的记录 desc;
# 不加条件 倒序排序
select * from city order by population desc;
# 加条件 倒序排序
select * from city where countrycode='CHN' order by population desc;
## select 范围查询
select * from 表名 where 要比较的字段 > 指定对比的值;
可用符号： > < >= <= <> !=
select * from city where population < 10000;
# union all 联合查询 将两条查询命令拼接起来 提高效率
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
### select 分组查询 group by
# 语法：
select 字段1,聚合函数(字段2) from 表名 group by 形容词;
# 聚合函数类型如下：
max()：最大值
min()：最小值
sum()：求和
avg()：求平均值`
count()：统计
# 口诀
1.遇到统计想函数
2.形容词前groupby (要查的数据按group by后面的形容词分组)
3.函数中央是名词
4.列名select后添加
企业级建表
CREATE TABLE `test_table` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`DOMAIN_CODE` varchar(20) NOT NULL COMMENT '考试单位编号',
`EXAM_NAME` varchar(300) NOT NULL COMMENT '考试名称',
`EXAM_TYPE` int(1) NOT NULL COMMENT '考试类型(正式考试，补考)',
`TARGET_EXAM_ID` bigint(20) DEFAULT NULL COMMENT '关联正式考试的ID(如果是补考，该处是必填)',
`EXAM_PICTURE_PATH` varchar(100) DEFAULT NULL COMMENT '图示路径',
`EXAM_BEGIN_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '考试开始时间',
`EXAM_END_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '考试结束时间',
`EXAM_TIME` int(3) NOT NULL COMMENT '考试时长',
`EXAM_NEED_SCORE` int(5) NOT NULL COMMENT '考试所需积分',
`EXAM_PAPER_TYPE` int(1) DEFAULT NULL COMMENT '考试试卷类型（0固定、1随机）',
`EXAM_SCORE` double(6,2) DEFAULT NULL COMMENT '考试总分(关联试卷后回填)',
`EXAM_PASS_SCORE` double(6,2) NOT NULL COMMENT '考试及格分',
`EXAM_COMMIT_NUM` int(2) NOT NULL COMMENT '参考最大次数',
`EXAM_STATUS` int(1) NOT NULL COMMENT '发布状态0未发布，1已发布',
`EXAM_YEAR` varchar(5) NOT NULL COMMENT '年份',
`EXAM_PAPER_ID` bigint(20) DEFAULT NULL COMMENT '关联试卷ID',
`EXAM_DISCRIPTION` varchar(1000) DEFAULT NULL COMMENT '考试备注',
`OPERATOR_USER_ACCOUNT` varchar(20) NOT NULL COMMENT '修改人',
`OPERATOR_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
`TARGET_DOMAIN_CODE` varchar(20) DEFAULT NULL COMMENT '发布目标单位编号（发布时回填）',
`RANK` varchar(100) DEFAULT NULL COMMENT '职务级别（发布时回填）',
`EXAM_DIPLOMA_ID` bigint(20) DEFAULT NULL COMMENT '关联证书',
`DIPLOMA_NAME` varchar(200) DEFAULT NULL COMMENT '证书标题(关联证书后回填',
`DIPLOMA_PICTURE_PATH` varchar(200) DEFAULT NULL COMMENT '证书背景图片保存位置(关联证书后回填)',
`INDUSTRY_CODES` varchar(1000) DEFAULT NULL,
`LANGUAGE` int(2) NOT NULL DEFAULT '1' COMMENT 'è¯è¨€ï¼ˆ0:全部,1:汉è¯,2:ç»´è¯,3:è’è¯,4:哈è¯ï¼‰',
`EXT1` int(1) NOT NULL DEFAULT '1' COMMENT '成绩计入学分的字段标识（0 是，1否）',
`EXT2` int(3) DEFAULT NULL COMMENT '成绩所占比例',
`EXT3` varchar(1) DEFAULT NULL,
`EXT4` varchar(1) DEFAULT NULL,
`EXT5` varchar(1) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `DOMAIN_CODE` (`DOMAIN_CODE`),
KEY `EXAM_PAPER_ID` (`EXAM_PAPER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=365 DEFAULT CHARSET=utf8;
## 主键 唯一键可以写最底下 主键是主序列 KEY `DOMAIN_CODE`，KEY `EXAM_PAPER_ID`是普通序列
## 'è¯è¨€ï¼ˆ0:全部,1:汉è¯,2:ç»´è¯,3:è’è¯,4:哈è¯ï¼‰',是乱码 本来是少数民族语
select 高级用法
传统连接
### 多表连查 类似集合 传统连接 逻辑性强 但不是效率最高的
[50,70,80]
## 创建两个表 内容如下 sid都为主键
表名： student
字段：sid sname
记录：001 zhang3
002 li4
003 wang5
# 建表：
create table student(
sid int(3) zerofill,
sname varchar(10));
# 插入内容
insert into student(sid,sname) value(1,'zhang3'),(2,'li4'),(3,'wang5');
# 查看
select * from student;
+------+--------+
| sid | sname |
+------+--------+
| 001 | zhang3 |
| 002 | li4 |
| 003 | wang5 |
+------+--------+
表名： score
字段： sid smark
记录： 001 50
002 70
003 80
# 建表：
create table score(
sid int(3) zerofill,
smark float(3,1));
# 插入内容
insert into score value(1,50),(2,70),(3,80);
# 查看
show tables;
+----------------+
| Tables_in_zls1 |
+----------------+
| score |
| student |
+----------------+
## 要求：查询出wang5的成绩
### 传统连接： 连表查询的前提：两张表，必须有关联的字段
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 条件;
select student.sname,score.smark
from student,score
where student.sid=score.sid
and student.sname='wang5';
## 传统连接，多表查询
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 等价条件
and 等价条件
......
and 条件;
# 世界上小于100人的人口城市是哪个国家的？
select country.name as 国家,city.name as 城市,city.population as 城市人口数
from city,country
where city.countrycode=country.code
and city.population < 100;
+----------+-----------+-----------------+
| 国家 | 城市 | 城市人口数 |
+----------+-----------+-----------------+
| Pitcairn | Adamstown | 42 |
+----------+-----------+-----------------+
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
国家名 城市名 人口数量 语言
select country.name,city.name,city.population,countrylanguage.language
from country,city,countrylanguage
where city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
and city.population < 100;
+----------+-----------+------------+-------------+
| name | name | population | language |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown | 42 | Pitcairnese |
+----------+-----------+------------+-------------+
自连接（NATURAL JOIN）
### 自动找到等价条件，前提：1.等价条件的列名
## 2.数据值必须一致
# 三张表相同的字段：
city 国家代码：countrycode
country 国家代码： code
countrylanguage 国家代码： countrycode
## 语法：
# 自连接查询：两张表
select 字段1,字段2,字段3,字段4
from 表1相同内容字段 natural join 表2相同内容字段
where 条件;
# 世界上小于100人的人口城市说什么语言？
select city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
select city.countrycode,city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
内连接（join on）
## 语法：
# 内连接查询：两表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件
where 自己的条件;
# 内连接查询：多表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件A
jion 表2
on 等价条件B
.....
where 自己的条件;
### join on 注意点：大表（字段多的表）在前，小表在后
### 世界上小于100人的人口城市是哪个国家的？
select country.name,city.name,city.population
from city join country
on city.countrycode=country.code
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
## 规范写法
select country.name,city.name,city.population,countrylanguage.language
from city join country
on city.countrycode=country.code
join countrylanguage
on city.countrycode=countrylanguage.countrycode
where city.population < 100;
# 可以得出结果但不太规范
select country.name,city.name,city.population,countrylanguage.language
from city join country
join countrylanguage
on city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
where city.population < 100;
外连接
## 左外连接 left join 显示左边表的内容，右边表结果显示NULL
select 字段1,字段2,字段3
from 表1 left jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100 limit 10;
+----------------+-------------+------+
| name | countrycode | name |
+----------------+-------------+------+
| Kabul | AFG | NULL |
| Qandahar | AFG | NULL |
| Herat | AFG | NULL |
| Mazar-e-Sharif | AFG | NULL |
| Amsterdam | NLD | NULL |
| Rotterdam | NLD | NULL |
| Haag | NLD | NULL |
| Utrecht | NLD | NULL |
| Eindhoven | NLD | NULL |
| Tilburg | NLD | NULL |
+----------------+-------------+------+
## 右外连接 right join 显示右边表的内容，左边表结果显示NULL
select 字段1,字段2,字段3
from 表1 right jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city right join country
on city.countrycode=country.code
and city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name |
+------+-------------+----------------------+
| NULL | NULL | Aruba |
| NULL | NULL | Afghanistan |
| NULL | NULL | Angola |
| NULL | NULL | Anguilla |
| NULL | NULL | Albania |
| NULL | NULL | Andorra |
| NULL | NULL | Netherlands Antilles |
| NULL | NULL | United Arab Emirates |
| NULL | NULL | Argentina |
| NULL | NULL | Armenia |
+------+-------------+----------------------+
# 对表中数据进行操作
# 对数据的增删改
## 增
insert
insert into 表名('字段1','字段2') 值('值1','值2');
# 插入单条数据
#例：以下两种写法都生效
1）
root@localhost:(none)>insert into hg1.stu(sname,sage,sbirthday,class) values('hmbb',3,NOW(),'q1');
Query OK, 1 row affected (0.01 sec)
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | hmbb | 3 | 1 | 2023-07-25 17:00:25 | q1 |
+-----+-------+------+------+---------------------+-------+
2）
root@localhost:(none)>insert hg1.stu(sname,sage,sbirthday,class) values('hmbb',3,NOW(),'q1');
Query OK, 1 row affected (0.00 sec)
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | hmbb | 3 | 1 | 2023-07-25 17:00:25 | q1 |
| 2 | hmbb | 3 | 1 | 2023-07-25 17:05:56 | q1 |
+-----+-------+------+------+---------------------+-------+
# 插入多条数据
#例
1)
root@localhost:(none)> insert hg1.stu(sname,sage,sbirthday,class) values('pdx',3,NOW(),'q1'),('zyg',2,'2021-11-12 00:00:00','q2');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | hmbb | 3 | 1 | 2023-07-25 17:00:25 | q1 |
| 2 | hmbb | 3 | 1 | 2023-07-25 17:05:56 | q1 |
| 3 | pdx | 3 | 1 | 2023-07-25 17:32:48 | q1 |
| 4 | zyg | 2 | 1 | 2021-11-12 00:00:00 | q2 |
+-----+-------+------+------+---------------------+-------+
2)
root@localhost:(none)>insert hg1.stu values(5,'mg',3,'1',NOW(),'q3'),(6,'ssg',2,'0','2021-11-12 00:00:00','q4');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 1 | hmbb | 3 | 1 | 2023-07-25 17:00:25 | q1 |
| 2 | hmbb | 3 | 1 | 2023-07-25 17:05:56 | q1 |
| 3 | pdx | 3 | 1 | 2023-07-25 17:32:48 | q1 |
| 4 | zyg | 2 | 1 | 2021-11-12 00:00:00 | q2 |
| 5 | mg | 3 | 1 | 2023-07-25 17:38:24 | q3 |
| 6 | ssg | 2 | 0 | 2021-11-12 00:00:00 | q4 |
+-----+-------+------+------+---------------------+-------+
## 删
# 危险写法：直接delete删 这样会把这个表里所有的数据都删完
delete
delete from 库名.表名;
delete from 库名.表名 where;
# 要养成习惯 使用delete先在后面加上where（条件） 指定要删的内容
# 注意：一般在删除数据时，我们会根据唯一性（主键，唯一键）的字段，进行删除
root@localhost:(none)>delete from hg1.stu where sno=1 or sno=2;
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 3 | pdx | 3 | 1 | 2023-07-25 17:32:48 | q1 |
| 4 | zyg | 2 | 1 | 2021-11-12 00:00:00 | q2 |
| 5 | mg | 3 | 1 | 2023-07-25 17:38:24 | q3 |
| 6 | ssg | 2 | 0 | 2021-11-12 00:00:00 | q4 |
+-----+-------+------+------+---------------------+-------+
## 删除所有内容（想把数据全部删除也要加where 写一个永远成立的条件即可）
root@localhost:(none)>delete from hg1.stu where 1=1;
Query OK, 5 rows affected (0.00 sec)
root@localhost:(none)> select * from hg1.stu;
Empty set (0.00 sec)
# 删除多个字段内容重复的数据方法： （ and连接多个条件 ）
root@localhost:(none)>select user,host from mysql.user;
+------+-------------+
| user | host |
+------+-------------+
| dev | % |
| rep | 172.16.1.5% |
| root | localhost |
+------+-------------+
3 rows in set (0.00 sec)
root@localhost:(none)>delete from mysql.user where user='dev' and host='%';
Query OK, 1 row affected (0.00 sec)
root@localhost:(none)>select user,host from mysql.user;
+------+-------------+
| user | host |
+------+-------------+
| rep | 172.16.1.5% |
| root | localhost |
+------+-------------+
2 rows in set (0.00 sec)## 改
update
update 库.表 set 字段='值';
# update 使用update一定要加条件 不然会把指定字段的所有内容就修改
# 错误写法
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 8 | hmbb | 3 | 1 | 2023-07-25 17:47:39 | q1 |
| 9 | hmbb | 3 | 1 | 2023-07-25 17:47:40 | q1 |
+-----+-------+------+------+---------------------+-------+
root@localhost:(none)>update hg1.stu set sage='1';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 8 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 |
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 |
+-----+-------+------+------+---------------------+-------+
#正确写法（加条件）
root@localhost:(none)>update hg1.stu set sage='2' where sno=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+
| sno | sname | sage | ssex | sbirthday | class |
+-----+-------+------+------+---------------------+-------+
| 8 | hmbb | 2 | 1 | 2023-07-25 17:58:47 | q1 |
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 |
+-----+-------+------+------+---------------------+-------+
2 rows in set (0.00 sec)
# 使用update替代delete删除数据（企业中）
# 1.给表中添加状态列
root@localhost:(none)>alter table hg1.stu add status enum('0','1') default '1';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+-------+------+------+---------------------+-------+--------+
| 8 | hmbb | 2 | 1 | 2023-07-25 17:58:47 | q1 | 1 |
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 | 1 |
+-----+-------+------+------+---------------------+-------+--------+
# 2.使用update代替delete删除
root@localhost:(none)>update hg1.stu set status='0' where sno=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost:(none)> select * from hg1.stu;
+-----+-------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+-------+------+------+---------------------+-------+--------+
| 8 | hmbb | 2 | 1 | 2023-07-25 18:09:06 | q1 | 0 |
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 | 1 |
+-----+-------+------+------+---------------------+-------+--------+
# 3.使用select查询时，加上条件
root@localhost:(none)>select * from hg1.stu where status='1';
+-----+-------+------+------+---------------------+-------+--------+
| sno | sname | sage | ssex | sbirthday | class | status |
+-----+-------+------+------+---------------------+-------+--------+
| 9 | hmbb | 1 | 1 | 2023-07-25 17:54:57 | q1 | 1 |
+-----+-------+------+------+---------------------+-------+--------+
### 注意：update修改数据库用户的密码后，需要flush privileges; 刷新数据库 修改其他数据，不需要DCL
### DCL含义: 控制用户的操作
Database Control Language
数据 控制 语言
## 赋予权限
# 5.6和5.7区别：5.7老版本，grant赋予权限，如果该用户不存在，不会创建用户，5.6和5.7新版本会直接创建用户
# 语法：
grant all on *.* to test@'%' identified by '123';
grant 权限1,权限 on 库.表 to 用户@'主机域' identified by '密码';
# 比如要加上option权限 方法如下;
grant all on *.* to test@'%' identified by '123' with grant option;
## 限制权限（拓展）
# 限制该用户一小时内，查询的次数（连接进入mysql就会查两次）
without max_queries_per_hour
# 限制用户一小时，执行update（改）的次数
max_updates_per_hour
# 限制用户一小时内，连接数据库的次数
max_connections_per_hour
# 限制同一个用户同时可以连接数据库的个数
max_user_connections
# 案例：限制用户一小时内 只能查询3次 只能执行一次update 只能连接一次数据库 同一个用户只能登陆一个
root@localhost:(none)>grant all on *.* to wyk@'%' identified by '123'
-> without max_queries_per_hour 3
-> max_updates_per_hour 1
-> max_connections_per_hour 1
-> max_user_connections 1
-> grant option;
# 像这种限制的权限是没办法回收的 只能删除用户 或者就像下面的 修改它的次数
root@localhost:(none)>grant all on *.* to wyk@'%' identified by '123'
-> with max_queries_per_hour 999
-> max_user_connections 999;
## 回收权限
# revoke语法：
revoke 要回收的权限 on 库.表 from 用户@'主机域';
revoke delete on *.* from wyk@'%';
DQL
### DQL含义： 所有的查都是DQL
Database Query Language
数据 查询 语言
## show
show databases; # 查看数据库
show tables; # 查看表
show tables from 库名; # 查看指定数据库下的表，不用切换数据库
show create database 库名; # 查看建库语句
show create table 表名; # 查看建表语句，也是为了查看注释
show create table 库名.表名; # 查看建表语句，也是为了查看注释（不用切换数据库）
show grants for 用户名@'主机域'; # 查看用户授权语句，也是为了查看该用户的权限
show variables; # 查看所有的内置变量（默认配置）
show variables like '%server'; # 模糊查询（过滤）
show variables like '%server%'; # 用 % 去模糊匹配
show variables like 'server%'; # % 表示匹配指定方位的内容多次或0次
show processlist; # 查看后台执行的sql语句
show full processlist; # 查看完整的后台执行的sql语句
show collation; # 查看所有校验规则
show charset; # 查看所有字符集以及该字符集的默认校验规则
## desc
desc 库.表 # 查看表结构
## 事务控制语句
Transaction Control Language
Database Transaction Language
## select
# 下载练习表 导入到数据库内 该数据导入时 会检测有没有world库 有就删除再创建一个world库 导入数据
[root@db01 ~]# wget https://download.wodeyumengouwo.com/mysql/world.sql
root@localhost:(none)> source /root/world.sql
root@localhost:(none)> show databases;
+--------------------+
| Database |
+--------------------+
| world |
+--------------------+
root@localhost:(none)> use world
root@localhost:world> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
## select 常用简单查询
# 查看该表中所有的字段的记录（所有数据）
select * from 库名.表名;
root@localhost:world> select * from city;
# 查看指定字段的所有数据
select 字段1,字段5,字段... from 表名;
root@localhost:world> select id,name,countrycode from city;
## select 行级查询（翻页功能） （默认为 <=指定的数 ）
select 字段1,字段5,字段... from 表名 limit 指定行数;
# 翻页功能，从1开始查到60 查60行
select id,name,countrycode from city limit 60;
# 翻页功能，从121开始查到180 查60行
select id,name,countrycode from city limit 120,60;
# 翻页功能，从181开始查到241 查60行
select id,name,countrycode from city limit 180,60;
## select 条件查询（精确）
select * from 表名 where 字段='记录';
select * from city where countrycode='CHN';
# 多条件查询 or and
select * from city where countrycode='CHN' or countrycode='USA';
select * from city where countrycode='CHN' and district='heilongjiang';
# 条件查询结合行级查询
select * from city where countrycode='CHN' and district='heilongjiang' limit 10;
# 多条件查询用 in 的方法：
select * from 表名 where 字段 in ('要查询的内容一','要查询内容二');
select * from city where countrycode in ('CHN','USA');
## select 模糊查询
select * from 表名 where like '%记录%';
select * from city where countrycode like '%HN';
select * from city where countrycode like '%HN%';
select * from city where countrycode like 'HN%';
# 条件查询结合行级查询（模糊查询可以接精确查询）
select * from city where countrycode like '%HN%' or countrycode='JPN' limit 10;
## select 排序查询 order by
# 顺序排序
select * from 表名 order by 要排序的记录；
# 不加条件 顺序排序
select * from city order by population;
# 加条件 顺序排序
select * from city where countrycode='CHN' order by population;
# 倒序排序
select * from 表名 order by 要排序的记录 desc;
# 不加条件 倒序排序
select * from city order by population desc;
# 加条件 倒序排序
select * from city where countrycode='CHN' order by population desc;
## select 范围查询
select * from 表名 where 要比较的字段 > 指定对比的值;
可用符号： > < >= <= <> !=
select * from city where population < 10000;
# union all 联合查询 将两条查询命令拼接起来 提高效率
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
### select 分组查询 group by
# 语法：
select 字段1,聚合函数(字段2) from 表名 group by 形容词;
# 聚合函数类型如下：
max()：最大值
min()：最小值
sum()：求和
avg()：求平均值`
count()：统计
# 口诀
1.遇到统计想函数
2.形容词前groupby (要查的数据按group by后面的形容词分组)
3.函数中央是名词
4.列名select后添加
企业级建表
CREATE TABLE `test_table` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`DOMAIN_CODE` varchar(20) NOT NULL COMMENT '考试单位编号',
`EXAM_NAME` varchar(300) NOT NULL COMMENT '考试名称',
`EXAM_TYPE` int(1) NOT NULL COMMENT '考试类型(正式考试，补考)',
`TARGET_EXAM_ID` bigint(20) DEFAULT NULL COMMENT '关联正式考试的ID(如果是补考，该处是必填)',
`EXAM_PICTURE_PATH` varchar(100) DEFAULT NULL COMMENT '图示路径',
`EXAM_BEGIN_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '考试开始时间',
`EXAM_END_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '考试结束时间',
`EXAM_TIME` int(3) NOT NULL COMMENT '考试时长',
`EXAM_NEED_SCORE` int(5) NOT NULL COMMENT '考试所需积分',
`EXAM_PAPER_TYPE` int(1) DEFAULT NULL COMMENT '考试试卷类型（0固定、1随机）',
`EXAM_SCORE` double(6,2) DEFAULT NULL COMMENT '考试总分(关联试卷后回填)',
`EXAM_PASS_SCORE` double(6,2) NOT NULL COMMENT '考试及格分',
`EXAM_COMMIT_NUM` int(2) NOT NULL COMMENT '参考最大次数',
`EXAM_STATUS` int(1) NOT NULL COMMENT '发布状态0未发布，1已发布',
`EXAM_YEAR` varchar(5) NOT NULL COMMENT '年份',
`EXAM_PAPER_ID` bigint(20) DEFAULT NULL COMMENT '关联试卷ID',
`EXAM_DISCRIPTION` varchar(1000) DEFAULT NULL COMMENT '考试备注',
`OPERATOR_USER_ACCOUNT` varchar(20) NOT NULL COMMENT '修改人',
`OPERATOR_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
`TARGET_DOMAIN_CODE` varchar(20) DEFAULT NULL COMMENT '发布目标单位编号（发布时回填）',
`RANK` varchar(100) DEFAULT NULL COMMENT '职务级别（发布时回填）',
`EXAM_DIPLOMA_ID` bigint(20) DEFAULT NULL COMMENT '关联证书',
`DIPLOMA_NAME` varchar(200) DEFAULT NULL COMMENT '证书标题(关联证书后回填',
`DIPLOMA_PICTURE_PATH` varchar(200) DEFAULT NULL COMMENT '证书背景图片保存位置(关联证书后回填)',
`INDUSTRY_CODES` varchar(1000) DEFAULT NULL,
`LANGUAGE` int(2) NOT NULL DEFAULT '1' COMMENT 'è¯è¨€ï¼ˆ0:全部,1:汉è¯,2:ç»´è¯,3:è’è¯,4:哈è¯ï¼‰',
`EXT1` int(1) NOT NULL DEFAULT '1' COMMENT '成绩计入学分的字段标识（0 是，1否）',
`EXT2` int(3) DEFAULT NULL COMMENT '成绩所占比例',
`EXT3` varchar(1) DEFAULT NULL,
`EXT4` varchar(1) DEFAULT NULL,
`EXT5` varchar(1) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `DOMAIN_CODE` (`DOMAIN_CODE`),
KEY `EXAM_PAPER_ID` (`EXAM_PAPER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=365 DEFAULT CHARSET=utf8;
## 主键 唯一键可以写最底下 主键是主序列 KEY `DOMAIN_CODE`，KEY `EXAM_PAPER_ID`是普通序列
## 'è¯è¨€ï¼ˆ0:全部,1:汉è¯,2:ç»´è¯,3:è’è¯,4:哈è¯ï¼‰',是乱码 本来是少数民族语
select 高级用法
传统连接
### 多表连查 类似集合 传统连接 逻辑性强 但不是效率最高的
[50,70,80]
## 创建两个表 内容如下 sid都为主键
表名： student
字段：sid sname
记录：001 zhang3
002 li4
003 wang5
# 建表：
create table student(
sid int(3) zerofill,
sname varchar(10));
# 插入内容
insert into student(sid,sname) value(1,'zhang3'),(2,'li4'),(3,'wang5');
# 查看
select * from student;
+------+--------+
| sid | sname |
+------+--------+
| 001 | zhang3 |
| 002 | li4 |
| 003 | wang5 |
+------+--------+
表名： score
字段： sid smark
记录： 001 50
002 70
003 80
# 建表：
create table score(
sid int(3) zerofill,
smark float(3,1));
# 插入内容
insert into score value(1,50),(2,70),(3,80);
# 查看
show tables;
+----------------+
| Tables_in_zls1 |
+----------------+
| score |
| student |
+----------------+
## 要求：查询出wang5的成绩
### 传统连接： 连表查询的前提：两张表，必须有关联的字段
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 条件;
select student.sname,score.smark
from student,score
where student.sid=score.sid
and student.sname='wang5';
## 传统连接，多表查询
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 等价条件
and 等价条件
......
and 条件;
# 世界上小于100人的人口城市是哪个国家的？
select country.name as 国家,city.name as 城市,city.population as 城市人口数
from city,country
where city.countrycode=country.code
and city.population < 100;
+----------+-----------+-----------------+
| 国家 | 城市 | 城市人口数 |
+----------+-----------+-----------------+
| Pitcairn | Adamstown | 42 |
+----------+-----------+-----------------+
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
国家名 城市名 人口数量 语言
select country.name,city.name,city.population,countrylanguage.language
from country,city,countrylanguage
where city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
and city.population < 100;
+----------+-----------+------------+-------------+
| name | name | population | language |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown | 42 | Pitcairnese |
+----------+-----------+------------+-------------+
自连接（NATURAL JOIN）
### 自动找到等价条件，前提：1.等价条件的列名
## 2.数据值必须一致
# 三张表相同的字段：
city 国家代码：countrycode
country 国家代码： code
countrylanguage 国家代码： countrycode
## 语法：
# 自连接查询：两张表
select 字段1,字段2,字段3,字段4
from 表1相同内容字段 natural join 表2相同内容字段
where 条件;
# 世界上小于100人的人口城市说什么语言？
select city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
select city.countrycode,city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
内连接（join on）
## 语法：
# 内连接查询：两表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件
where 自己的条件;
# 内连接查询：多表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件A
jion 表2
on 等价条件B
.....
where 自己的条件;
### join on 注意点：大表（字段多的表）在前，小表在后
### 世界上小于100人的人口城市是哪个国家的？
select country.name,city.name,city.population
from city join country
on city.countrycode=country.code
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
## 规范写法
select country.name,city.name,city.population,countrylanguage.language
from city join country
on city.countrycode=country.code
join countrylanguage
on city.countrycode=countrylanguage.countrycode
where city.population < 100;
# 可以得出结果但不太规范
select country.name,city.name,city.population,countrylanguage.language
from city join country
join countrylanguage
on city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
where city.population < 100;
外连接
## 左外连接 left join 显示左边表的内容，右边表结果显示NULL
select 字段1,字段2,字段3
from 表1 left jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100 limit 10;
+----------------+-------------+------+
| name | countrycode | name |
+----------------+-------------+------+
| Kabul | AFG | NULL |
| Qandahar | AFG | NULL |
| Herat | AFG | NULL |
| Mazar-e-Sharif | AFG | NULL |
| Amsterdam | NLD | NULL |
| Rotterdam | NLD | NULL |
| Haag | NLD | NULL |
| Utrecht | NLD | NULL |
| Eindhoven | NLD | NULL |
| Tilburg | NLD | NULL |
+----------------+-------------+------+
## 右外连接 right join 显示右边表的内容，左边表结果显示NULL
select 字段1,字段2,字段3
from 表1 right jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city right join country
on city.countrycode=country.code
and city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name |
+------+-------------+----------------------+
| NULL | NULL | Aruba |
| NULL | NULL | Afghanistan |
| NULL | NULL | Angola |
| NULL | NULL | Anguilla |
| NULL | NULL | Albania |
| NULL | NULL | Andorra |
| NULL | NULL | Netherlands Antilles |
| NULL | NULL | United Arab Emirates |
| NULL | NULL | Argentina |
| NULL | NULL | Armenia |
+------+-------------+----------------------+
### DCL含义: 控制用户的操作
Database Control Language
数据 控制 语言
## 赋予权限
# 5.6和5.7区别：5.7老版本，grant赋予权限，如果该用户不存在，不会创建用户，5.6和5.7新版本会直接创建用户
# 语法：
grant all on *.* to test@'%' identified by '123';
grant 权限1,权限 on 库.表 to 用户@'主机域' identified by '密码';
# 比如要加上option权限 方法如下;
grant all on *.* to test@'%' identified by '123' with grant option;
## 限制权限（拓展）
# 限制该用户一小时内，查询的次数（连接进入mysql就会查两次）
without max_queries_per_hour
# 限制用户一小时，执行update（改）的次数
max_updates_per_hour
# 限制用户一小时内，连接数据库的次数
max_connections_per_hour
# 限制同一个用户同时可以连接数据库的个数
max_user_connections
# 案例：限制用户一小时内 只能查询3次 只能执行一次update 只能连接一次数据库 同一个用户只能登陆一个
root@localhost:(none)>grant all on *.* to wyk@'%' identified by '123'
-> without max_queries_per_hour 3
-> max_updates_per_hour 1
-> max_connections_per_hour 1
-> max_user_connections 1
-> grant option;
# 像这种限制的权限是没办法回收的 只能删除用户 或者就像下面的 修改它的次数
root@localhost:(none)>grant all on *.* to wyk@'%' identified by '123'
-> with max_queries_per_hour 999
-> max_user_connections 999;
## 回收权限
# revoke语法：
revoke 要回收的权限 on 库.表 from 用户@'主机域';
revoke delete on *.* from wyk@'%';### DQL含义： 所有的查都是DQL
Database Query Language
数据 查询 语言
## show
show databases; # 查看数据库
show tables; # 查看表
show tables from 库名; # 查看指定数据库下的表，不用切换数据库
show create database 库名; # 查看建库语句
show create table 表名; # 查看建表语句，也是为了查看注释
show create table 库名.表名; # 查看建表语句，也是为了查看注释（不用切换数据库）
show grants for 用户名@'主机域'; # 查看用户授权语句，也是为了查看该用户的权限
show variables; # 查看所有的内置变量（默认配置）
show variables like '%server'; # 模糊查询（过滤）
show variables like '%server%'; # 用 % 去模糊匹配
show variables like 'server%'; # % 表示匹配指定方位的内容多次或0次
show processlist; # 查看后台执行的sql语句
show full processlist; # 查看完整的后台执行的sql语句
show collation; # 查看所有校验规则
show charset; # 查看所有字符集以及该字符集的默认校验规则
## desc
desc 库.表 # 查看表结构
## 事务控制语句
Transaction Control Language
Database Transaction Language
## select
# 下载练习表 导入到数据库内 该数据导入时 会检测有没有world库 有就删除再创建一个world库 导入数据
[root@db01 ~]# wget https://download.wodeyumengouwo.com/mysql/world.sql
root@localhost:(none)> source /root/world.sql
root@localhost:(none)> show databases;
+--------------------+
| Database |
+--------------------+
| world |
+--------------------+
root@localhost:(none)> use world
root@localhost:world> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
## select 常用简单查询
# 查看该表中所有的字段的记录（所有数据）
select * from 库名.表名;
root@localhost:world> select * from city;
# 查看指定字段的所有数据
select 字段1,字段5,字段... from 表名;
root@localhost:world> select id,name,countrycode from city;
## select 行级查询（翻页功能） （默认为 <=指定的数 ）
select 字段1,字段5,字段... from 表名 limit 指定行数;
# 翻页功能，从1开始查到60 查60行
select id,name,countrycode from city limit 60;
# 翻页功能，从121开始查到180 查60行
select id,name,countrycode from city limit 120,60;
# 翻页功能，从181开始查到241 查60行
select id,name,countrycode from city limit 180,60;
## select 条件查询（精确）
select * from 表名 where 字段='记录';
select * from city where countrycode='CHN';
# 多条件查询 or and
select * from city where countrycode='CHN' or countrycode='USA';
select * from city where countrycode='CHN' and district='heilongjiang';
# 条件查询结合行级查询
select * from city where countrycode='CHN' and district='heilongjiang' limit 10;
# 多条件查询用 in 的方法：
select * from 表名 where 字段 in ('要查询的内容一','要查询内容二');
select * from city where countrycode in ('CHN','USA');
## select 模糊查询
select * from 表名 where like '%记录%';
select * from city where countrycode like '%HN';
select * from city where countrycode like '%HN%';
select * from city where countrycode like 'HN%';
# 条件查询结合行级查询（模糊查询可以接精确查询）
select * from city where countrycode like '%HN%' or countrycode='JPN' limit 10;
## select 排序查询 order by
# 顺序排序
select * from 表名 order by 要排序的记录；
# 不加条件 顺序排序
select * from city order by population;
# 加条件 顺序排序
select * from city where countrycode='CHN' order by population;
# 倒序排序
select * from 表名 order by 要排序的记录 desc;
# 不加条件 倒序排序
select * from city order by population desc;
# 加条件 倒序排序
select * from city where countrycode='CHN' order by population desc;
## select 范围查询
select * from 表名 where 要比较的字段 > 指定对比的值;
可用符号： > < >= <= <> !=
select * from city where population < 10000;
# union all 联合查询 将两条查询命令拼接起来 提高效率
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
### select 分组查询 group by
# 语法：
select 字段1,聚合函数(字段2) from 表名 group by 形容词;
# 聚合函数类型如下：
max()：最大值
min()：最小值
sum()：求和
avg()：求平均值`
count()：统计
# 口诀
1.遇到统计想函数
2.形容词前groupby (要查的数据按group by后面的形容词分组)
3.函数中央是名词
4.列名select后添加企业级建表
CREATE TABLE `test_table` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`DOMAIN_CODE` varchar(20) NOT NULL COMMENT '考试单位编号',
`EXAM_NAME` varchar(300) NOT NULL COMMENT '考试名称',
`EXAM_TYPE` int(1) NOT NULL COMMENT '考试类型(正式考试，补考)',
`TARGET_EXAM_ID` bigint(20) DEFAULT NULL COMMENT '关联正式考试的ID(如果是补考，该处是必填)',
`EXAM_PICTURE_PATH` varchar(100) DEFAULT NULL COMMENT '图示路径',
`EXAM_BEGIN_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '考试开始时间',
`EXAM_END_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '考试结束时间',
`EXAM_TIME` int(3) NOT NULL COMMENT '考试时长',
`EXAM_NEED_SCORE` int(5) NOT NULL COMMENT '考试所需积分',
`EXAM_PAPER_TYPE` int(1) DEFAULT NULL COMMENT '考试试卷类型（0固定、1随机）',
`EXAM_SCORE` double(6,2) DEFAULT NULL COMMENT '考试总分(关联试卷后回填)',
`EXAM_PASS_SCORE` double(6,2) NOT NULL COMMENT '考试及格分',
`EXAM_COMMIT_NUM` int(2) NOT NULL COMMENT '参考最大次数',
`EXAM_STATUS` int(1) NOT NULL COMMENT '发布状态0未发布，1已发布',
`EXAM_YEAR` varchar(5) NOT NULL COMMENT '年份',
`EXAM_PAPER_ID` bigint(20) DEFAULT NULL COMMENT '关联试卷ID',
`EXAM_DISCRIPTION` varchar(1000) DEFAULT NULL COMMENT '考试备注',
`OPERATOR_USER_ACCOUNT` varchar(20) NOT NULL COMMENT '修改人',
`OPERATOR_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '修改时间',
`TARGET_DOMAIN_CODE` varchar(20) DEFAULT NULL COMMENT '发布目标单位编号（发布时回填）',
`RANK` varchar(100) DEFAULT NULL COMMENT '职务级别（发布时回填）',
`EXAM_DIPLOMA_ID` bigint(20) DEFAULT NULL COMMENT '关联证书',
`DIPLOMA_NAME` varchar(200) DEFAULT NULL COMMENT '证书标题(关联证书后回填',
`DIPLOMA_PICTURE_PATH` varchar(200) DEFAULT NULL COMMENT '证书背景图片保存位置(关联证书后回填)',
`INDUSTRY_CODES` varchar(1000) DEFAULT NULL,
`LANGUAGE` int(2) NOT NULL DEFAULT '1' COMMENT 'è¯è¨€ï¼ˆ0:全部,1:汉è¯,2:ç»´è¯,3:è’è¯,4:哈è¯ï¼‰',
`EXT1` int(1) NOT NULL DEFAULT '1' COMMENT '成绩计入学分的字段标识（0 是，1否）',
`EXT2` int(3) DEFAULT NULL COMMENT '成绩所占比例',
`EXT3` varchar(1) DEFAULT NULL,
`EXT4` varchar(1) DEFAULT NULL,
`EXT5` varchar(1) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `DOMAIN_CODE` (`DOMAIN_CODE`),
KEY `EXAM_PAPER_ID` (`EXAM_PAPER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=365 DEFAULT CHARSET=utf8;
## 主键 唯一键可以写最底下 主键是主序列 KEY `DOMAIN_CODE`，KEY `EXAM_PAPER_ID`是普通序列
## 'è¯è¨€ï¼ˆ0:全部,1:汉è¯,2:ç»´è¯,3:è’è¯,4:哈è¯ï¼‰',是乱码 本来是少数民族语 select 高级用法
传统连接
### 多表连查 类似集合 传统连接 逻辑性强 但不是效率最高的
[50,70,80]
## 创建两个表 内容如下 sid都为主键
表名： student
字段：sid sname
记录：001 zhang3
002 li4
003 wang5
# 建表：
create table student(
sid int(3) zerofill,
sname varchar(10));
# 插入内容
insert into student(sid,sname) value(1,'zhang3'),(2,'li4'),(3,'wang5');
# 查看
select * from student;
+------+--------+
| sid | sname |
+------+--------+
| 001 | zhang3 |
| 002 | li4 |
| 003 | wang5 |
+------+--------+
表名： score
字段： sid smark
记录： 001 50
002 70
003 80
# 建表：
create table score(
sid int(3) zerofill,
smark float(3,1));
# 插入内容
insert into score value(1,50),(2,70),(3,80);
# 查看
show tables;
+----------------+
| Tables_in_zls1 |
+----------------+
| score |
| student |
+----------------+
## 要求：查询出wang5的成绩
### 传统连接： 连表查询的前提：两张表，必须有关联的字段
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 条件;
select student.sname,score.smark
from student,score
where student.sid=score.sid
and student.sname='wang5';
## 传统连接，多表查询
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 等价条件
and 等价条件
......
and 条件;
# 世界上小于100人的人口城市是哪个国家的？
select country.name as 国家,city.name as 城市,city.population as 城市人口数
from city,country
where city.countrycode=country.code
and city.population < 100;
+----------+-----------+-----------------+
| 国家 | 城市 | 城市人口数 |
+----------+-----------+-----------------+
| Pitcairn | Adamstown | 42 |
+----------+-----------+-----------------+
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
国家名 城市名 人口数量 语言
select country.name,city.name,city.population,countrylanguage.language
from country,city,countrylanguage
where city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
and city.population < 100;
+----------+-----------+------------+-------------+
| name | name | population | language |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown | 42 | Pitcairnese |
+----------+-----------+------------+-------------+
自连接（NATURAL JOIN）
### 自动找到等价条件，前提：1.等价条件的列名
## 2.数据值必须一致
# 三张表相同的字段：
city 国家代码：countrycode
country 国家代码： code
countrylanguage 国家代码： countrycode
## 语法：
# 自连接查询：两张表
select 字段1,字段2,字段3,字段4
from 表1相同内容字段 natural join 表2相同内容字段
where 条件;
# 世界上小于100人的人口城市说什么语言？
select city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
select city.countrycode,city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
内连接（join on）
## 语法：
# 内连接查询：两表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件
where 自己的条件;
# 内连接查询：多表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件A
jion 表2
on 等价条件B
.....
where 自己的条件;
### join on 注意点：大表（字段多的表）在前，小表在后
### 世界上小于100人的人口城市是哪个国家的？
select country.name,city.name,city.population
from city join country
on city.countrycode=country.code
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
## 规范写法
select country.name,city.name,city.population,countrylanguage.language
from city join country
on city.countrycode=country.code
join countrylanguage
on city.countrycode=countrylanguage.countrycode
where city.population < 100;
# 可以得出结果但不太规范
select country.name,city.name,city.population,countrylanguage.language
from city join country
join countrylanguage
on city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
where city.population < 100;
外连接
## 左外连接 left join 显示左边表的内容，右边表结果显示NULL
select 字段1,字段2,字段3
from 表1 left jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100 limit 10;
+----------------+-------------+------+
| name | countrycode | name |
+----------------+-------------+------+
| Kabul | AFG | NULL |
| Qandahar | AFG | NULL |
| Herat | AFG | NULL |
| Mazar-e-Sharif | AFG | NULL |
| Amsterdam | NLD | NULL |
| Rotterdam | NLD | NULL |
| Haag | NLD | NULL |
| Utrecht | NLD | NULL |
| Eindhoven | NLD | NULL |
| Tilburg | NLD | NULL |
+----------------+-------------+------+
## 右外连接 right join 显示右边表的内容，左边表结果显示NULL
select 字段1,字段2,字段3
from 表1 right jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city right join country
on city.countrycode=country.code
and city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name |
+------+-------------+----------------------+
| NULL | NULL | Aruba |
| NULL | NULL | Afghanistan |
| NULL | NULL | Angola |
| NULL | NULL | Anguilla |
| NULL | NULL | Albania |
| NULL | NULL | Andorra |
| NULL | NULL | Netherlands Antilles |
| NULL | NULL | United Arab Emirates |
| NULL | NULL | Argentina |
| NULL | NULL | Armenia |
+------+-------------+----------------------+
### 多表连查 类似集合 传统连接 逻辑性强 但不是效率最高的
[50,70,80]
## 创建两个表 内容如下 sid都为主键
表名： student
字段：sid sname
记录：001 zhang3
002 li4
003 wang5
# 建表：
create table student(
sid int(3) zerofill,
sname varchar(10));
# 插入内容
insert into student(sid,sname) value(1,'zhang3'),(2,'li4'),(3,'wang5');
# 查看
select * from student;
+------+--------+
| sid | sname |
+------+--------+
| 001 | zhang3 |
| 002 | li4 |
| 003 | wang5 |
+------+--------+
表名： score
字段： sid smark
记录： 001 50
002 70
003 80
# 建表：
create table score(
sid int(3) zerofill,
smark float(3,1));
# 插入内容
insert into score value(1,50),(2,70),(3,80);
# 查看
show tables;
+----------------+
| Tables_in_zls1 |
+----------------+
| score |
| student |
+----------------+
## 要求：查询出wang5的成绩
### 传统连接： 连表查询的前提：两张表，必须有关联的字段
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 条件;
select student.sname,score.smark
from student,score
where student.sid=score.sid
and student.sname='wang5';
## 传统连接，多表查询
select 字段1,字段2
from 表名1,表名2
where 等价条件
and 等价条件
and 等价条件
......
and 条件;
# 世界上小于100人的人口城市是哪个国家的？
select country.name as 国家,city.name as 城市,city.population as 城市人口数
from city,country
where city.countrycode=country.code
and city.population < 100;
+----------+-----------+-----------------+
| 国家 | 城市 | 城市人口数 |
+----------+-----------+-----------------+
| Pitcairn | Adamstown | 42 |
+----------+-----------+-----------------+
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
国家名 城市名 人口数量 语言
select country.name,city.name,city.population,countrylanguage.language
from country,city,countrylanguage
where city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
and city.population < 100;
+----------+-----------+------------+-------------+
| name | name | population | language |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown | 42 | Pitcairnese |
+----------+-----------+------------+-------------+### 自动找到等价条件，前提：1.等价条件的列名
## 2.数据值必须一致
# 三张表相同的字段：
city 国家代码：countrycode
country 国家代码： code
countrylanguage 国家代码： countrycode
## 语法：
# 自连接查询：两张表
select 字段1,字段2,字段3,字段4
from 表1相同内容字段 natural join 表2相同内容字段
where 条件;
# 世界上小于100人的人口城市说什么语言？
select city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
select city.countrycode,city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;内连接（join on）
## 语法：
# 内连接查询：两表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件
where 自己的条件;
# 内连接查询：多表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件A
jion 表2
on 等价条件B
.....
where 自己的条件;
### join on 注意点：大表（字段多的表）在前，小表在后
### 世界上小于100人的人口城市是哪个国家的？
select country.name,city.name,city.population
from city join country
on city.countrycode=country.code
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
## 规范写法
select country.name,city.name,city.population,countrylanguage.language
from city join country
on city.countrycode=country.code
join countrylanguage
on city.countrycode=countrylanguage.countrycode
where city.population < 100;
# 可以得出结果但不太规范
select country.name,city.name,city.population,countrylanguage.language
from city join country
join countrylanguage
on city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
where city.population < 100;
外连接
## 左外连接 left join 显示左边表的内容，右边表结果显示NULL
select 字段1,字段2,字段3
from 表1 left jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100 limit 10;
+----------------+-------------+------+
| name | countrycode | name |
+----------------+-------------+------+
| Kabul | AFG | NULL |
| Qandahar | AFG | NULL |
| Herat | AFG | NULL |
| Mazar-e-Sharif | AFG | NULL |
| Amsterdam | NLD | NULL |
| Rotterdam | NLD | NULL |
| Haag | NLD | NULL |
| Utrecht | NLD | NULL |
| Eindhoven | NLD | NULL |
| Tilburg | NLD | NULL |
+----------------+-------------+------+
## 右外连接 right join 显示右边表的内容，左边表结果显示NULL
select 字段1,字段2,字段3
from 表1 right jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city right join country
on city.countrycode=country.code
and city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name |
+------+-------------+----------------------+
| NULL | NULL | Aruba |
| NULL | NULL | Afghanistan |
| NULL | NULL | Angola |
| NULL | NULL | Anguilla |
| NULL | NULL | Albania |
| NULL | NULL | Andorra |
| NULL | NULL | Netherlands Antilles |
| NULL | NULL | United Arab Emirates |
| NULL | NULL | Argentina |
| NULL | NULL | Armenia |
+------+-------------+----------------------+
## 语法：
# 内连接查询：两表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件
where 自己的条件;
# 内连接查询：多表
select 字段1,字段2,字段3
from 表1 jion 表2
on 等价条件A
jion 表2
on 等价条件B
.....
where 自己的条件;
### join on 注意点：大表（字段多的表）在前，小表在后
### 世界上小于100人的人口城市是哪个国家的？
select country.name,city.name,city.population
from city join country
on city.countrycode=country.code
where city.population<100;
# 世界上小于100人的人口城市是哪个国家的,说什么语言？
## 规范写法
select country.name,city.name,city.population,countrylanguage.language
from city join country
on city.countrycode=country.code
join countrylanguage
on city.countrycode=countrylanguage.countrycode
where city.population < 100;
# 可以得出结果但不太规范
select country.name,city.name,city.population,countrylanguage.language
from city join country
join countrylanguage
on city.countrycode=country.code
and city.countrycode=countrylanguage.countrycode
where city.population < 100;## 左外连接 left join 显示左边表的内容，右边表结果显示NULL
select 字段1,字段2,字段3
from 表1 left jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city left join country
on city.countrycode=country.code
and city.population<100 limit 10;
+----------------+-------------+------+
| name | countrycode | name |
+----------------+-------------+------+
| Kabul | AFG | NULL |
| Qandahar | AFG | NULL |
| Herat | AFG | NULL |
| Mazar-e-Sharif | AFG | NULL |
| Amsterdam | NLD | NULL |
| Rotterdam | NLD | NULL |
| Haag | NLD | NULL |
| Utrecht | NLD | NULL |
| Eindhoven | NLD | NULL |
| Tilburg | NLD | NULL |
+----------------+-------------+------+
## 右外连接 right join 显示右边表的内容，左边表结果显示NULL
select 字段1,字段2,字段3
from 表1 right jion 表2
on 等价条件
and 自己的条件;
select city.name,city.countrycode,country.name
from city right join country
on city.countrycode=country.code
and city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name |
+------+-------------+----------------------+
| NULL | NULL | Aruba |
| NULL | NULL | Afghanistan |
| NULL | NULL | Angola |
| NULL | NULL | Anguilla |
| NULL | NULL | Albania |
| NULL | NULL | Andorra |
| NULL | NULL | Netherlands Antilles |
| NULL | NULL | United Arab Emirates |
| NULL | NULL | Argentina |
| NULL | NULL | Armenia |
+------+-------------+----------------------+
