文章目录

展开

[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              |
+------+-------------+----------------------+