mysql数据库基础

mysql数据库基础

1、数据库分为：

• MySQL：MySQL，MariaDB，Percona-Server
• PostgreSQL：简称为pgsql
• Oracle
• MSSQL

纪录： 一条记录就是一行数据

字段： 一列就是一个字段

**约束：**constraint，向数据表提供的数据要遵守的限制

• 主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
  • 一个表只能存在一个
• 惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
  • 一个表可以存在多个
• 外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
• 检查性约束

**索引：**将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

2、关系型数据库常见组件：

• 数据库：database
• 表：table，由行（row）和列（column）组成
• 索引：index
• 视图：view
• 用户：user
• 权限：privilege
• 存储过程：procedure
• 存储函数：function
• 触发器：trigger
• 事件调度器：event scheduler

3、SQL语句：

​ 有三种类型：

• DDL：Data Defination Language，数据定义语言（对数据库表进行操作）

  CREATE：创建
  DROP：删除
  ALTER：修改

• DML：Data Manipulation Language，数据操纵语言（对表里面的数据进行操作）

  INSERT：向表中插入数据
  DELETE：删除表中数据
  UPDATE：更新表中数据
  SELECT：查询表中数据

• DCL：Data Control Language，数据控制语言

  GRANT：授权

  REVOKE：移除授权

4、数据库安装：

三种安装方法：

• 源代码：编译安装
• 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
• 程序包管理器管理的程序包：
  • rpm：有两种
    • OS Vendor：操作系统发行商提供的
    • 项目官方提供的
  • deb

mysql的安装：

1> 准备工作

[root@localhost ~]# yum -y install wget  
//下载mysql包
[root@localhost ~]# wget -O /usr/src/mysql57-community-release-el7-10.noarch.rpm \
http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug    mysql57-community-release-el7-10.noarch.rpm  
kernels
[root@localhost src]# rpm -ivh mysql57-community-release-el7-10.noarch.rpm

//查看mysql专属yum仓
[root@localhost src]# cd /etc/yum.repos.d/
[root@localhost yum.repos.d]# ls
CentOS-8.repo               mysql-community.repo
CentOS-Base.repo            mysql-community-source.repo
CentOS-SIG-ansible-29.repo  redhat.repo

//清缓存 建立缓存
[root@localhost ~]# yum clean all
[root@localhost ~]# yum makecache

[root@localhost ~]# cat /etc/yum.repos.d/mysql-community.repo 
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/

//安装mysql所需依赖包
[root@localhost ~]#  wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-server-5.7.43-1.el7.x86_64.rpm
[root@localhost ~]#  wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-common-5.7.43-1.el7.x86_64.rpm
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-devel-5.7.43-1.el7.x86_64.rpm
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-client-5.7.43-1.el7.x86_64.rpm
[root@localhost ~]# wget http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql-community-libs-5.7.43-1.el7.x86_64.rpm

//查看软件包是否成功下载到本地
[root@localhost ~]# ls
anaconda-ks.cfg
mysql-community-client-5.7.43-1.el7.x86_64.rpm
mysql-community-common-5.7.43-1.el7.x86_64.rpm
mysql-community-devel-5.7.43-1.el7.x86_64.rpm
mysql-community-libs-5.7.43-1.el7.x86_64.rpm
mysql-community-server-5.7.43-1.el7.x86_64.rpm

//安装rpm包
[root@localhost ~]# yum -y install *.rpm

2> 配置mysql

//启动mysql
[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld

//查看3306是否监听
[root@localhost ~]# ss -anlt
State  Recv-Q Send-Q  Local Address:Port   Peer Address:Port Process 
LISTEN 0      128           0.0.0.0:22          0.0.0.0:*            
LISTEN 0      128              [::]:22             [::]:*            
LISTEN 0      80                  *:3306              *:*     

3> 设置密码

//查看密码 **注意： 此处密码只能使用一次，用此密码设置新密码
[root@localhost ~]# grep password /var/log/mysqld.log 
2023-08-30T11:29:48.834726Z 1 [Note] A temporary password is generated for root@localhost: rfjrohR%D24w      

//设置密码 -u：指定用户   -p：指定密码     密码必须包括：大写 小写 数字 特殊字符
[root@localhost ~]# mysql -uroot -p'rfjrohR%D24w'

设置新密码 1：

mysql> set password = password('Luchengyang111!');
mysql> quit
明文登录：
[root@localhost ~]# mysql -uroot -pLuchengyang111!
mysql> 
//表示登录成功
密文登录：
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> 

设置新密码 2：

//  用这种方式将密码的复杂性策略关掉，设置出来的密码就是没有大小写的区别
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'luchengyang';
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysql -uroot -pluchengyang
mysql> 

4> 避免mysql自动升级

//查看mysql包
[root@localhost ~]# rpm -qa | grep mysql
mysql-community-client-5.7.43-1.el7.x86_64
mysql57-community-release-el7-10.noarch
mysql-community-common-5.7.43-1.el7.x86_64
mysql-community-server-5.7.43-1.el7.x86_64
mysql-community-libs-5.7.43-1.el7.x86_64
mysql-community-devel-5.7.43-1.el7.x86_64

//为避免mysql自动升级，这里需要卸载最开始安装的yum源
[root@localhost ~]# rpm -e mysql57-community-release

mysql的程序组成：

• 客户端
  • mysql：CLI交互式客户端程序（在脚本中使用）
  • mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
  • mysqldump：mysql备份工具
  • mysqladmin
• 服务器端
  • mysqld
• 数据库：（图形化）
• ​ workbench（mianfei）
• ​ navicat（shoufei）

安全初始化：（刚进入系统是执行一遍）
[root@localhost ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 输入密码
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 50 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 
是否更改root用户密码
 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
是否移除其他用户
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 
不允许root用户远程登陆

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
是否移除测试速度和访问的权限
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.


Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
是否重新加载权限表
Success.

All done! 

mysql工具的使用：

//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
    -uUSERNAME      //指定用户名，默认为root
    -hHOST          //指定服务器主机，默认为localhost，推荐使用ip地址
    -pPASSWORD      //指定用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
    -V              //查看当前使用的mysql版本
    -e          //不登录mysql执行sql语句后退出，常用于脚本

1、查看版本

[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.43, for Linux (x86_64) using  EditLine wrapper

2、 不登录mysql执行sql语句

//查看数据库
[root@localhost ~]# mysql -uroot -pluchengyang -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

3、新建一台主机，连接r8-2的数据库

在新主机上：

[root@localhost ~]# yum -y install mariadb

在mysql数据库主机上设置：
[root@localhost ~]#  mysql -uroot -pluchengyang
mysql> grant all on *.* to 'tom'@'192.168.134.151' identified by 'luchengyang123';（此处的密码可重新设置）
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;（刷新特权）

//关闭两台主机的防火墙
[root@localhost ~]# systemctl disable --now firewalld

在受控机上登录：
[root@localhost ~]# mysql -utom -pluchengyang123 -h192.168.134.148
MySQL [(none)]> 

真实机连接虚拟机数据库

1》用cmd查看ip

C:\Users\Administrator>ipconfig

在mysql主机上添加访问权限

[root@localhost ~]# mysql -uroot -pluchengyang      //登陆
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.43 MySQL Community Server (GPL)

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

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

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

mysql> grant all on *.* to tom@192.168.134.1 identified by 'luchenyang';  
Query OK, 0 rows affected, 1 warning (0.00 sec)          

mysql> flush privileges;      //刷新权限
Query OK, 0 rows affected (0.00 sec)

连接：

查看linux权限

//查看受控机权限
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.01 sec)

//查看真机的权限
mysql> show grants for 'tom'@192.168.134.1;
+------------------------------------------------------+
| Grants for tom@192.168.134.1                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'tom'@'192.168.134.1' |
+------------------------------------------------------+
1 row in set (0.01 sec)

服务器监听的两种socket地址

socket类型	说明
ip socket	默认监听在tcp的3306端口，支持远程通信
unix sock	监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock） 仅支持本地通信 server地址只能是：localhost，127.0.0.1

创建授权grant

权限类型(priv_type)

权限类型	代表什么？
ALL	所有权限
SELECT	读取内容的权限
INSERT	插入内容的权限
UPDATE	更新内容的权限
DELETE	删除内容的权限

指定要操作的对象db_name.table_name

表示所有库中的所有表 ： * . *

指定库的所有表： db_name

指定库的指定表： db_name.table_name

1、授权

GRANT priv_type,…（权限类型） ON [object_type]（对象的类型） db_name.table_name（对象的表） TO ‘username’@‘host’（客户端：某用户 某主机） [IDENTIFIED BY ‘password’] [WITH GRANT OPTION];（赋予权限）

在客户端上设置：
[root@localhost ~]#  mysql -uroot -pluchengyang
mysql> grant all on *.* to 'tom'@'192.168.134.151' identified by 'luchengyang123';（此处的密码可重新设置）
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;（刷新特权）

[root@localhost ~]# systemctl disable --now firewalld

在主控机上登录：
[root@localhost ~]# mysql -utom -pluchengyang123 -h192.168.134.151
MySQL [(none)]> 

2、取消授权

//语法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';
mysql> revoke all on *.* from tom@192.168.134.151;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;（刷新特权）

[root@localhost ~]# mysql -utom -pluchengyang123 -h192.168.134.151
//会发现登陆不成功

#查看是否删除
mysql> show grants for tom@192.168.134.151;
+-----------------------------------------------+
| Grants for tom@192.168.134.151                |
+-----------------------------------------------+
| GRANT USAGE ON *.* TO 'tom'@'192.168.134.151' |
+-----------------------------------------------+
1 row in set (0.05 sec)
USAGE就是没有权限

注意：mysql服务进程启动时会读取mysql库中的所有授权表至内存中：

• GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
• 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权表

mysql> FLUSH PRIVILEGES;

mysql数据库操作

DDL操作

1》 数据库操作

创建数据库：

//创建数据库
//语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME';
mysql> create database if not exists lu;  //表示如果不存在的情况下创建，不会报错
Query OK, 1 row affected (0.00 sec)

mysql> show databases;  //查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lu                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

//删除数据库
//语法：DROP DATABASE [IF EXISTS] 'DB_NAME';
mysql> drop database if exists lu;
Query OK, 0 rows affected (0.01 sec)

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

表操作：

mysql> show databases;   //查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lu                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use lu;     //进入数据库
Database changed
mysql> create table student (id int not null,name varchar(100) not null,age tinyint);      //创建表 #创建一个名为student的表
                                            int #写入的数据必须是整数
                                            not null #这个这段不能为空
                                            varchar(20) #字母最大20个
                                            tinyint	#无符号值最大255
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;     //查看表
+--------------+
| Tables_in_lu |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

#查看表的结构
mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

mysql> drop table student;    //删除表
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;    //查看表是否删除
Empty set (0.00 sec)

用户操作：

mysql用户帐号由两部分组成，如’USERNAME’@‘HOST’，表示此USERNAME只能从此HOST上远程登录

这里（‘USERNAME’@‘HOST’）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

• IP地址，如：192.168.134.151
• 通配符
  • %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
  • _：匹配任意单个字符

命令大写小写的区别：

//数据库创建用户
语法：CREATE USER 'username'@'host' [IDENTIFIED BY 'password'];
//创建数据库用户luchengyang
mysql> CREATE USER 'luchengyang'@'192.168.134.151' IDENTIFIED BY 'luchengyang';
Query OK, 0 rows affected (0.00 sec)

//使用新创建的用户和密码登录
[root@localhost ~]# mysql -uluchengyang -pluchengyang -h192.168.134.148;
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> 
//删除数据库用户
//语法：DROP USER 'username'@'host'; 

查看命令show

查看命令SHOW
mysql> SHOW CHARACTER SET;   //查看支持的所有字符集
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

mysql> SHOW ENGINES;  //查看当前数据库支持的所有存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> SHOW DATABASES;   //查看数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lu                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> SHOW TABLES FROM lu;  //不进入某数据库而列出其包含的所有表
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)  

mysql> SHOW CREATE TABLE lu.student;  //查看某表怎么创建的命令
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                             |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


//查看某表的状态
mysql> use lu;   //进入数据库
Database changed
mysql> SHOW TABLE STATUS LIKE 'student'\G   //查看表的状态
*************************** 1. row ***************************
           Name: student   表名
         Engine: InnoDB    表存储引擎
        Version: 10        表版本
     Row_format: Dynamic   格式：分开
           Rows: 0         行数
 Avg_row_length: 0         平均行长度
    Data_length: 16384     数据长度
Max_data_length: 0         最大数据长度
   Index_length: 0         缩进长度
      Data_free: 0         空闲数据
 Auto_increment: NULL      是否自动增长
    Create_time: 2023-08-31 14:17:02   创建时间
    Update_time: NULL      更新时间
     Check_time: NULL      检查时间
      Collation: latin1_swedish_ci   字符密码
       Checksum: NULL      检查校验值
 Create_options:           创建表格要加的选项
        Comment:           描述信息
1 row in set (0.00 sec)
//此处：
SHOW TABLE STATUS LIKE 'student%'\G     //%：表示任意长度
SHOW TABLE STATUS LIKE 'student_'\G    //_：表示后面接一个字符的如（1-9）

获取帮助

//获取命令使用帮助
//语法：HELP keyword;
mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW {CHARACTER SET | CHARSET} [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
...
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where: {
    LIKE 'pattern'
  | WHERE expr
}

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
https://dev.mysql.com/doc/refman/5.7/en/extended-show.html.

URL: https://dev.mysql.com/doc/refman/5.7/en/show.html

查看进程列表

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 10 | root | localhost | lu   | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

读锁（共享锁S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。 这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改
写锁（互斥锁X）：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。 若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。 InnoDB所有的DML操作默认加写锁。 select可以通过for update加写锁，并且会锁住所有索引，不仅仅是索引覆盖的索引。

//设置一个读锁
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> create database test;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
                                //无法创建
mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 12 | root | localhost | NULL | Sleep   |  467 |          | NULL             |
| 13 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

退出之后锁就消失

[root@localhost ~]# mysql -uroot -pLuchengyang111!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.39 MySQL Community Server (GPL)

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

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

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


mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
| 11 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

1、 INSERT语句

//DML操作之增操作insert
//语法：INSERT [INTO] table_name [(column_name,...)] {VALUES | VALUE} (value1,...),(...),...
进入数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lu                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use lu
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_lu |
+--------------+
| student      |
+--------------+
1 row in set (0.00 sec)

mysql> desc student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert student(id,name,age) value(1,'test',15);    //一次插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | test |   15 |
+----+------+------+
1 row in set (0.00 sec)

mysql> insert student(id,name,age) values(2,'jerry',19),(3,'zhangsan',23),(4,'lisi',28),(5,'wangwu',20);          //一次插入多条记录
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

SELECT语句

字段column表示法

表示符	代表什么？
*	所有字段
as	字段别名，如col1 AS alias1 当表名很长时用别名代替

条件判断语句WHERE

操作类型	常用操作符
操作符	>，<，>=，<=，=，!= BETWEEN column# AND column# LIKE：模糊匹配 RLIKE：基于正则表达式进行模式匹配 IS NOT NULL：非空 IS NULL：空
条件逻辑操作	AND OR NOT

ORDER BY：排序，默认为升序（ASC）

ORDER BY语句	意义
ORDER BY ‘column_name’	根据column_name进行升序排序
ORDER BY ‘column_name’ DESC	根据column_name进行降序排序
ORDER BY ’column_name’ LIMIT 2	根据column_name进行升序排序 并只取前2个结果
ORDER BY ‘column_name’ LIMIT 1,2	根据column_name进行升序排序 并且略过第1个结果取后面的2个结果

//DML操作之查操作select
//语法：SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];
mysql> use school;
Database changed
mysql> select * from student;   //查看所有的列
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select name from student;   //查看name这一列数据
+----------+
| name     |
+----------+
| test     |
| jerry    |
| zhangsan |
| lisi     |
| wangwu   |
+----------+
5 rows in set (0.00 sec)

mysql> select * from student order by age;   //降序排列
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  5 | wangwu   |   20 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from student order by age desc;   //升序排列
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  4 | lisi     |   28 |
|  3 | zhangsan |   23 |
|  5 | wangwu   |   20 |
|  2 | jerry    |   19 |
|  1 | test     |   15 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> select * from student order by age limit 3;  //升序排列并只取前3个结果
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | test   |   15 |
|  2 | jerry  |   19 |
|  5 | wangwu |   20 |
+----+--------+------+
3 rows in set (0.00 sec)

mysql> select * from student order by age limit 1,3;  //进行升序排序并且略过第1个结果取后面的3个结果
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   19 |
|  5 | wangwu   |   20 |
|  3 | zhangsan |   23 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from student where age >= 20;  //查看表中数据age大于等于20的行
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from student where age >= 20 and name = 'lisi';  //查看表中数据age大于等于20的行并且name=lisi的行
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | lisi |   28 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from student where age between 18 and 26;   //查看表中数据age大于18小于26的行
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  5 | wangwu   |   20 |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from student where age is not null;  //查看表中age不为空的行
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

update语句

//DML操作之改操作update
//语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,...] [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   28 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> update student set age = 25 where name = 'lisi';  //将lisi的age修改为25
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where name = 'lisi';
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | lisi |   25 |
+----+------+------+
1 row in set (0.00 sec)

delete语句

//DML操作之删操作delete
//语法：DELETE FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n];

mysql> select * from student;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   25 |
|  5 | wangwu   |   20 |
+----+----------+------+
5 rows in set (0.00 sec)

mysql> delete from student where id = 5;  //删除id为5的一行记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;   //删除整张表的内容
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | test     |   15 |
|  2 | jerry    |   19 |
|  3 | zhangsan |   23 |
|  4 | lisi     |   25 |
+----+----------+------+
4 rows in set (0.00 sec)

mysql> delete from student;    //再次查看发现已经没有表
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

truncate语句

truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间
truncate	删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表

//语法：TRUNCATE table_name;

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | test |   16 |
+----+------+------+
1 row in set (0.01 sec)

mysql> truncate student;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | tinyint(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)