MySQL实操

## 基于MySQL

#先启动MySQL服务#第一次登录[root@localhost ~]# mysql -uroot -P3306#密码登录[root@localhost ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.41 Source distribution....mysql> show databases;+--------------------+| Database           |+--------------------+| db1                || information_schema || mysql              || performance_schema || sys                |+--------------------+#登录指定数据库 只能是一个[root@localhost ~]# mysql -uroot -p8520 db1mysql: [Warning] Using a password on the command line interface can be insecure.Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A....#修改root密码#没设置密码时[root@localhost ~]# mysqladmin -uroot -p password '密码'#后续设置密码[root@localhost ~]# mysqladmin -uroot password -p '1230'#这个是新密码 输入老密码验证Enter password: mysqladmin: [Warning] Using a password on the command line interface can be insecure.Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.....

#查看数据库mysql> show databases;+--------------------+| Database           |+--------------------+| db1                || information_schema || mysql              || performance_schema || sys                |+--------------------+5 rows in set (0.00 sec)#查看所在数据库mysql> select database();+------------+| database() |+------------+| NULL       |+------------+1 row in set (0.00 sec)#切换数据库mysql> use db1Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select database();+------------+| database() |+------------+| db1        |+------------+1 row in set (0.00 sec)#查看当前登录用户mysql> select user();+----------------+| user()         |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)#查看当前数据库版本mysql> select version();+-----------+| version() |+-----------+| 8.0.41    |+-----------+1 row in set (0.00 sec)#查看当前用户权限mysql> show privileges;+------------------------------+---------------------------------------+-------------------------------------------------------+| Privilege                    | Context                               | Comment                                               |+------------------------------+---------------------------------------+-------------------------------------------------------+| Alter                        | Tables                                | To alter the table                                    || Alter routine                | Functions,Procedures                  | To alter ....#查看当前用户权限 ，行输出mysql> show privileges\G;*************************** 1. row ***************************Privilege: AlterContext: TablesComment: To alter the table*************************** 2. row ***************************Privilege: Alter routineContext: Functions,ProceduresComment: To alter or drop stored functions/procedures.....mysql> SHow grants for zyy@192.168.157.136;+---------------------------------------------------------------------------------------------------------------------------------------#切换数据库mysql> use db1Database changedmysql> create database db2;Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| db1                || db2                || information_schema || mysql              || performance_schema || sys                |+--------------------+6 rows in set (0.00 sec)#删除数据库mysql> drop database db2;Query OK, 0 rows affected (0.01 sec)#查看字符集mysql> show character set;+----------+---------------------------------+---------------------+--------+| Charset  | Description                     | Default collation   | Maxlen |+----------+---------------------------------+---------------------+--------+| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 || ascii    | US ASCII                        | ascii_general_ci    |      1 || big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 || binary   | Binary pseudo charset           | binary     #查看指定用户权限mysql> show grants for zyy@192.168.157.136;+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------......

表结构操作

#查看数据表mysql> show tables;+---------------+| Tables_in_db1 |+---------------+| t800          |+---------------+1 row in set (0.00 sec)#查看表结构mysql> desc t800;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id    | int          | YES  |     | NULL    |       || name  | varchar(50)  | YES  |     | NULL    |       || sex   | varchar(4)   | YES  |     | NULL    |       || phone | int          | YES  |     | NULL    |       || extra | varchar(255) | YES  |     | 冲冲    |       |+-------+--------------+------+-----+---------+-------+5 rows in set (0.00 sec)#查看表内容mysql> select * from t800;+------+------+------+------------+-------+| id   | name | sex  | phone      | extra |+------+------+------+------------+-------+|    1 | zyy  | 男   | 1326549871 | NULL  ||    2 | zyy1 | 男   | 1326549872 | NULL  ||    3 | hh   | nan  |  147852963 | NULL  ||    3 | hh1  | nan1 |    1478529 | NULL  ||    4 | aa   | bv   |   14752341 | NULL  || NULL | hao  | 男   |       NULL | NULL  |+------+------+------+------------+-------+6 rows in set (0.00 sec)#创建新表mysql> create table t1(id int,name varchar(20),age int,sex varchar(20));Query OK, 0 rows affected (0.02 sec)mysql> desc t1;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | YES  |     | NULL    |       || name  | varchar(20) | YES  |     | NULL    |       || age   | int         | YES  |     | NULL    |       || sex   | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

对表进行操作（列）

mysql> select * from t800;+------+------+------+------------+-------+| id   | name | sex  | phone      | extra |+------+------+------+------------+-------+|    1 | zyy  | 男   | 1326549871 | NULL  ||    2 | zyy1 | 男   | 1326549872 | NULL  ||    3 | hh   | nan  |  147852963 | NULL  ||    3 | hh1  | nan1 |    1478529 | NULL  ||    4 | aa   | bv   |   14752341 | NULL  || NULL | hao  | 男   |       NULL | NULL  |+------+------+------+------------+-------+6 rows in set (0.00 sec)mysql> desc t800;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id    | int          | YES  |     | NULL    |       || name  | varchar(50)  | YES  |     | NULL    |       || sex   | varchar(4)   | YES  |     | NULL    |       || phone | int          | YES  |     | NULL    |       || extra | varchar(255) | YES  |     | 冲冲    |       |+-------+--------------+------+-----+---------+-------+5 rows in set (0.00 sec)#添加新的列，规定类型mysql> alter table t800 add column habit varchar(20);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc t800;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id    | int          | YES  |     | NULL    |       || name  | varchar(50)  | YES  |     | NULL    |       || sex   | varchar(4)   | YES  |     | NULL    |       || phone | int          | YES  |     | NULL    |       || extra | varchar(255) | YES  |     | 冲冲    |       || habit | varchar(20)  | YES  |     | NULL    |       |+-------+--------------+------+-----+---------+-------+6 rows in set (0.00 sec)#更改列名称及类型#CHANGE COLUMN <旧列名> <新列名> <新列类型> #不想改类型就写原类型mysql> desc t800;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | YES  |     | NULL    |       || name  | varchar(50) | YES  |     | NULL    |       || sex   | varchar(4)  | YES  |     | NULL    |       || phone | int         | YES  |     | NULL    |       || sport | int         | YES  |     | NULL    |       || habit | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)#改名但是不该类型mysql> alter table t800 CHANGE sport data int;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc t800;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | YES  |     | NULL    |       || name  | varchar(50) | YES  |     | NULL    |       || sex   | varchar(4)  | YES  |     | NULL    |       || phone | int         | YES  |     | NULL    |       || data  | int         | YES  |     | NULL    |       || habit | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)#更改默认值mysql> alter table t800 alter column data set default haha;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'haha' at line 1#haha 是字符串但未用引号包裹，MySQL 将其解析为列名或关键字而非字符串值，导致语法错误mysql> alter table t800 alter column data set default 'haha';ERROR 1067 (42000): Invalid default value for 'data'#data 列是数值类型，而 'haha' 是字符串，无法隐式转换。mysql> alter table t800 alter column data set default '0';Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc t800;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | YES  |     | NULL    |       || name  | varchar(50) | YES  |     | NULL    |       || sex   | varchar(4)  | YES  |     | NULL    |       || phone | int         | YES  |     | NULL    |       || data  | int         | YES  |     | 0       |       || habit | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+6 rows in set (0.00 sec)#只修改类型mysql> alter table t800 modify habit int;Query OK, 6 rows affected (0.02 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> desc t800;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | YES  |     | NULL    |       || name  | varchar(50) | YES  |     | NULL    |       || sex   | varchar(4)  | YES  |     | NULL    |       || phone | int         | YES  |     | NULL    |       || data  | int         | YES  |     | 0       |       || habit | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+#更改表名mysql> alter table t800 rename to t200;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+---------------+| Tables_in_db1 |+---------------+| t1            || t200          |+---------------+2 rows in set (0.00 sec)#删除某一列mysql> alter table t200 drop data;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc t200;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int         | YES  |     | NULL    |       || name  | varchar(50) | YES  |     | NULL    |       || sex   | varchar(4)  | YES  |     | NULL    |       || phone | int         | YES  |     | NULL    |       || habit | int         | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+5 rows in set (0.00 sec)

pt综合操作

#创建测试表mysql> create table t2(id tinyint,name varchar(40),score decimal(4,2),object varchar(50));Query OK, 0 rows affected (0.01 sec)mysql> desc t2;+--------+--------------+------+-----+---------+-------+| Field  | Type         | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| id     | tinyint      | YES  |     | NULL    |       || name   | varchar(40)  | YES  |     | NULL    |       || score  | decimal(4,2) | YES  |     | NULL    |       || object | varchar(50)  | YES  |     | NULL    |       |+--------+--------------+------+-----+---------+-------+4 rows in set (0.01 sec)#改表名mysql> alter table t2 rename to c2505;Query OK, 0 rows affected (0.00 sec)#修改数据类型mysql> alter table c2505 modify id int;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc c2505;+--------+--------------+------+-----+---------+-------+| Field  | Type         | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| id     | int          | YES  |     | NULL    |       || name   | varchar(40)  | YES  |     | NULL    |       || score  | decimal(4,2) | YES  |     | NULL    |       || object | varchar(50)  | YES  |     | NULL    |       |+--------+--------------+------+-----+---------+-------+4 rows in set (0.01 sec)mysql> desc c2505;+--------+--------------+------+-----+---------+-------+| Field  | Type         | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| object | varchar(50)  | YES  |     | NULL    |       || id     | int          | YES  |     | NULL    |       || name   | varchar(40)  | YES  |     | NULL    |       || score  | decimal(4,2) | YES  |     | NULL    |       |+--------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)#修改字段顺序mysql> alter table c2505 modify column object varchar(50) after name varchar(40);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(40)' at line 1#冗余的字段定义•：AFTER name 后多写了 varchar(40)，这是无效语法。字段位置调整只需指定目标字段名，•无需重复定义目标字段的数据类型mysql> alter table c2505 modify column object varchar(50) after name ;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc c2505;+--------+--------------+------+-----+---------+-------+| Field  | Type         | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| id     | int          | YES  |     | NULL    |       || name   | varchar(40)  | YES  |     | NULL    |       || object | varchar(50)  | YES  |     | NULL    |       || score  | decimal(4,2) | YES  |     | NULL    |       |+--------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)#修改默认值mysql> alter table c2505 modify column object varchar(50) default 'haha';Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc c2505;+--------+--------------+------+-----+---------+-------+| Field  | Type         | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| id     | int          | YES  |     | NULL    |       || name   | varchar(40)  | YES  |     | NULL    |       || object | varchar(50)  | YES  |     | haha    |       || score  | decimal(4,2) | YES  |     | NULL    |       |+--------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)#修改字段是否为空mysql> alter table c2505 modify object varchar(40) not null;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc c2505;+--------+--------------+------+-----+---------+-------+| Field  | Type         | Null | Key | Default | Extra |+--------+--------------+------+-----+---------+-------+| id     | int          | YES  |     | NULL    |       || name   | varchar(40)  | YES  |     | NULL    |       || object | varchar(40)  | #NO   |     | NULL    |       || score  | decimal(4,2) | YES  |     | NULL    |       |+--------+--------------+------+-----+---------+-------+4 rows in set (0.00 sec)#设置字段自动增长#先设置其为主键mysql> alter table c2505 modify id int primary key;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table c2505 modify id int auto_increment;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> desc c2505;+--------+--------------+------+-----+---------+----------------+| Field  | Type         | Null | Key | Default | Extra          |+--------+--------------+------+-----+---------+----------------+| id     | int          | NO   | PRI | NULL    | auto_increment || name   | varchar(40)  | YES  |     | NULL    |                || object | varchar(40)  | NO   |     | NULL    |                || score  | decimal(4,2) | YES  |     | NULL    |                |+--------+--------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

insert

#mysql> select * from c2505;Empty set (0.00 sec)#单行插入，指明列名，对应值mysql> insert c2505 (id,name,object,score) values (1,'hao','zy',77.77);Query OK, 1 row affected (0.00 sec)mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hao  | zy     | 77.77 |+----+------+--------+-------+1 row in set (0.00 sec)#省略列名时，需按表结构顺序提供所有字段的值（自增列可用NULL或DEFAULT，日期时间格式填now（） ）#可用来批量添加mysql> insert c2505 values (2,'hzk','zyy',78.98);Query OK, 1 row affected (0.00 sec)mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hao  | zy     | 77.77 ||  2 | hzk  | zyy    | 78.98 |+----+------+--------+-------+2 rows in set (0.00 sec)#自增列用defaultmysql> insert c2505 values (default,'kkk','yy',78.98);Query OK, 1 row affected (0.00 sec)mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hao  | zy     | 77.77 ||  2 | hzk  | zyy    | 78.98 ||  3 | kkk  | yy     | 78.98 |+----+------+--------+-------+3 rows in set (0.00 sec)#mysql> insert c2505 set name='hk',object='zz';Query OK, 1 row affected (0.00 sec)#插入部分字符mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hao  | zy     | 77.77 ||  2 | hzk  | zyy    | 78.98 ||  3 | kkk  | yy     | 78.98 ||  4 | hk   | zz     |  NULL |+----+------+--------+-------+4 rows in set (0.00 sec)#新插入单个字段（新行）mysql> insert c2505 set object='qwe';Query OK, 1 row affected (0.00 sec)mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zyy    | 92.91 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | erd  | home   | 99.99 ||  6 | NULL | qwe    |  NULL |+----+------+--------+-------+6 rows in set (0.00 sec)

update

#不用where 默认改全部mysql> update c2505 set name='hh',object='tt',score=78.24;Query OK, 5 rows affected (0.00 sec)Rows matched: 5  Changed: 5  Warnings: 0mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hh   | tt     | 78.24 ||  2 | hh   | tt     | 78.24 ||  3 | hh   | tt     | 78.24 ||  4 | hh   | tt     | 78.24 ||  5 | hh   | tt     | 78.24 |+----+------+--------+-------+5 rows in set (0.00 sec)#改某一字段 整列mysql> update c2505 set object='zy';Query OK, 5 rows affected (0.01 sec)Rows matched: 5  Changed: 5  Warnings: 0mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hh   | zy     | 78.24 ||  2 | hh   | zy     | 78.24 ||  3 | hh   | zy     | 78.24 ||  4 | hh   | zy     | 78.24 ||  5 | hh   | zy     | 78.24 |+----+------+--------+-------+5 rows in set (0.00 sec)mysql> update c2505 set name='hzk',object='zy',score=99.990;Query OK, 5 rows affected (0.01 sec)Rows matched: 5  Changed: 5  Warnings: 0mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zy     | 99.99 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | hzk  | zy     | 99.99 |+----+------+--------+-------+5 rows in set (0.00 sec)#修改指定行mysql> update c2505 set name='hzk',object='zyy',score=92.91 where id=1;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zyy    | 92.91 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | hzk  | zy     | 99.99 |+----+------+--------+-------+5 rows in set (0.00 sec)#修改字段内容mysql> update c2505 set object='home' where id=5;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zyy    | 92.91 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | hzk  | home   | 99.99 |+----+------+--------+-------+5 rows in set (0.00 sec)#补#修改指定字段mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zyy    | 92.91 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | erd  | home   | 99.99 ||  6 | NULL | qwe    |  NULL |+----+------+--------+-------+6 rows in set (0.00 sec)mysql> update c2505 set name='pan' where id=6;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zyy    | 92.91 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | erd  | home   | 99.99 ||  6 | pan  | qwe    |  NULL |+----+------+--------+-------+6 rows in set (0.00 sec)#条件更新mysql> update c2505 set name='erd' where object='home' and score=99.99;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zyy    | 92.91 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | erd  | home   | 99.99 |+----+------+--------+-------+5 rows in set (0.00 sec)

select

#去重查询mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zyy    | 92.91 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | erd  | home   | 99.99 |+----+------+--------+-------+mysql> select distinct name from c2505;+------+| name |+------+| hzk  || erd  |+------+2 rows in set (0.00 sec)#统计查询，非空数据mysql> select * from c2505;+----+------+--------+-------+| id | name | object | score |+----+------+--------+-------+|  1 | hzk  | zyy    | 92.91 ||  2 | hzk  | zy     | 99.99 ||  3 | hzk  | zy     | 99.99 ||  4 | hzk  | zy     | 99.99 ||  5 | erd  | home   | 99.99 |+----+------+--------+-------+#去重后mysql> select count(distinct object) from c2505;+------------------------+| count(distinct object) |+------------------------+|                      3 |+------------------------+1 row in set (0.00 sec)#不去重mysql> select count(object) from c2505;+---------------+| count(object) |+---------------+|             5 |+---------------+1 row in set (0.00 sec)#重新定义查询结果表中的列名称

ai基础练习

#mysql> select * from student;+------------+--------+--------+------------+-------------+----------+-----------------------+-------------+----------------------+---------------------+---------------------+| student_id | name   | gender | birth_date | enroll_date | major_id | address               | phone       | email                | create_time         | update_time         |+------------+--------+--------+------------+-------------+----------+-----------------------+-------------+----------------------+---------------------+---------------------+|          1 | 张三   | 男     | 2000-05-15 | 2019-09-01  |        1 | 上海市浦东            | 13800138001 | zhangsan@example.com | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 ||          2 | 李四   | 女     | 2001-03-22 | 2020-09-01  |        2 | 上海市浦东新区        | 13800138002 | lisi@example.com     | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 ||          3 | 王五   | 男     | 2000-11-10 | 2019-09-01  |        3 | 广州市天河区          | 13800138003 | wangwu@example.com   | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 ||          4 | 赵六   | 女     | 2001-07-30 | 2020-09-01  |        5 | 深圳市南山区          | 13800138004 | zhaoliu@example.com  | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 ||          5 | 钱七   | 男     | 2000-09-18 | 2019-09-01  |        6 | 成都市武侯区          | 13800138005 | qianqi@example.com   | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 |+------------+--------+--------+------------+-------------+----------+-----------------------+-------------+----------------------+---------------------+---------------------+5 rows in set (0.00 sec)####查询######查询所有男生的姓名、出生日期和联系方式mysql> select name,birth_date,phone from student where gender='男';#参考 SELECT name, birth_date, phone FROM student WHERE gender = '男';+--------+------------+-------------+| name   | birth_date | phone       |+--------+------------+-------------+| 张三   | 2000-05-15 | 13800138001 || 王五   | 2000-11-10 | 13800138003 || 钱七   | 2000-09-18 | 13800138005 |+--------+------------+-------------+3 rows in set (0.00 sec)#查询 2020 年入学（enroll_date）的学生信息，按出生日期升序排列。mysql> select * from student where enroll_date='2020-09-01';#参考SELECT * FROM student WHERE YEAR(enroll_date) = 2020 ORDER BY birth_date ASC;#查询地址包含“浦东”的学生姓名和地址，并去重显示。mysql> select distinct name,address from student where address like '%浦东%';+--------+-----------------------+| name   | address               |+--------+-----------------------+| 张三   | 上海市浦东            || 李四   | 上海市浦东新区        |+--------+-----------------------+2 rows in set (0.00 sec)#查询邮箱域名是 example.com 的学生（使用 LIKE 或字符串函数）。mysql> select name from student where email like '%example.com%';+--------+| name   |+--------+| 张三   || 李四   || 王五   || 赵六   || 钱七   |+--------+5 rows in set (0.00 sec)###插入#####插入mysql> desc student;+-------------+--------------+------+-----+---------+-------+| Field       | Type         | Null | Key | Default | Extra |+-------------+--------------+------+-----+---------+-------+| student_id  | int          | YES  |     | NULL    |       || name        | varchar(255) | YES  |     | NULL    |       || gender      | varchar(255) | YES  |     | NULL    |       || birth_date  | date         | YES  |     | NULL    |       || enroll_date | date         | YES  |     | NULL    |       || major_id    | int          | YES  |     | NULL    |       || address     | varchar(255) | YES  |     | NULL    |       || phone       | varchar(255) | YES  |     | NULL    |       || email       | varchar(255) | YES  |     | NULL    |       || create_time | datetime     | YES  |     | NULL    |       || update_time | datetime     | YES  |     | NULL    |       |+-------------+--------------+------+-----+---------+-------+11 rows in set (0.01 sec)#插入一条信息 mysql> insert student values(6,'孙八','男',now(),now(),6,'北京','13866241100','@qq',now(),now());Query OK, 1 row affected, 2 warnings (0.00 sec)#将 孙八 的电话更新为 110，邮箱更新为 110.com。mysql> update student set phone='110',email='110.com' where name='孙八';Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0#将所有 2019 年入学的学生专业 ID（major_id）改为 7。mysql> update student set major_id=7 where year(enroll_date)='2019';#删除mysql> delete * from student where name='钱七';#删除所有专业 ID（major_id）为 5 或 6 的学生记录。delete from student where major_id in (5,6);#删除 address 为空或 NULL 的学生（需先检查是否存在）。DELETE FROM student WHERE address IS NULL OR address = '';

单表查询

mysql> select * from student;+------------+--------+--------+------------+-------------+----------+-----------------------+-------------+----------------------+---------------------+---------------------+| student_id | name   | gender | birth_date | enroll_date | major_id | address               | phone       | email                | create_time         | update_time         |+------------+--------+--------+------------+-------------+----------+-----------------------+-------------+----------------------+---------------------+---------------------+|          1 | 张三   | 男     | 2000-05-15 | 2019-09-01  |        1 | 上海市浦东新区        | 13800138001 | zhangsan@example.com | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 ||          2 | 李四   | 女     | 2001-03-22 | 2020-09-01  |        2 | 上海市浦东新区        | 13800138002 | lisi@example.com     | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 ||          3 | 王五   | 男     | 2000-11-10 | 2019-09-01  |        3 | 广州市天河区          | 13800138003 | wangwu@example.com   | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 ||          4 | 赵六   | 女     | 2001-07-30 | 2020-09-01  |        5 | 深圳市南山区          | 13800138004 | zhaoliu@example.com  | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 ||          5 | 钱七   | 男     | 2000-09-18 | 2019-09-01  |        6 | 成都市武侯区          | 13800138005 | qianqi@example.com   | 2025-07-01 15:21:32 | 2025-07-01 15:21:32 |+------------+--------+--------+------------+-------------+----------+-----------------------+-------------+----------------------+---------------------+---------------------+5 rows in set (0.00 sec)

去重查询

mysql> select distinct address from student;+-----------------------+| address               |+-----------------------+| 上海市浦东新区        || 广州市天河区          || 深圳市南山区          || 成都市武侯区          |+-----------------------+4 rows in set (0.00 sec)

去重计数

mysql> select count(distinct address) from student;+-------------------------+| count(distinct address) |+-------------------------+|                       4 |+-------------------------+1 row in set (0.00 sec)

重新定义查询结果表中的列名称

#重新定义查询结果表中的列名称#通过AS定义的别名（包括列别名、表别名、子查询别名）•仅作用于当前查询语句，查询结束后别名即失效#创建视图时可为表或列定义持久化别名，后续查询可直接使用视图名mysql> select student_id as '位次',birth_date as '生日' from student as t;+--------+------------+| 位次   | 生日       |+--------+------------+|      1 | 2000-05-15 ||      2 | 2001-03-22 ||      3 | 2000-11-10 ||      4 | 2001-07-30 ||      5 | 2000-09-18 |+--------+------------+5 rows in set (0.00 sec)#as可省略 mysql> select student_id '位次',birth_date '生日' from student t;

聚合函数查询

mysql> desc major;+-------------+--------------+------+-----+---------+-------+| Field       | Type         | Null | Key | Default | Extra |+-------------+--------------+------+-----+---------+-------+| major_id    | int          | YES  |     | NULL    |       || major_name  | varchar(255) | YES  |     | NULL    |       || dept_id     | int          | YES  |     | NULL    |       || credits_req | int          | YES  |     | NULL    |       || description | varchar(255) | YES  |     | NULL    |       || create_time | datetime     | YES  |     | NULL    |       || update_time | datetime     | YES  |     | NULL    |       |+-------------+--------------+------+-----+---------+-------+7 rows in set (0.00 sec)

求和

mysql> select sum(credits_req) from major;+------------------+| sum(credits_req) |+------------------+|              925 |+------------------+1 row in set (0.00 sec)

求平均值

SELECT AVG(column_name) AS alias_name FROM table_name [WHERE condition];mysql> select avg(credits_req) from major;+------------------+| avg(credits_req) |+------------------+|         154.1667 |+------------------+1 row in set (0.00 sec)mysql> select avg(credits_req) as '平均值' from major;+-----------+| 平均值    |+-----------+|  154.1667 |+-----------+1 row in set (0.00 sec)

最大值

mysql> select max(credits_req) from major;+------------------+| max(credits_req) |+------------------+|              165 |+------------------+1 row in set (0.00 sec)

最小值

mysql> select min(credits_req) from major;+------------------+| min(credits_req) |+------------------+|              145 |+------------------+1 row in set (0.00 sec)

mydumper完整备份

完全备份

mysqldump --all-databases -uroot > all.sqlmysqldump -A > all.sql[root@localhost ~]# mysqldump --all-databases -uroot -p > all.sqlEnter password: [root@localhost ~]# ls1.txt  视频  下载  all.sql               init.sh   mysql.bak.tar.gz公共   图片  音乐  anaconda-ks.cfg       ip.txt    reboot.sh模板   文档  桌面  initial-setup-ks.cfg  jilu.txt[root@localhost ~]# cat all.sql -- MySQL dump 10.13  Distrib 8.0.41, for Linux (x86_64)---- Host: localhost    Database: -- -------------------------------------------------------- Server version       8.0.41.....#备份的过程就是先删除在创建，创建的时候会上锁

恢复

#模拟删除mysql> drop database test;Query OK, 9 rows affected (0.05 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema |+--------------------+3 rows in set (0.00 sec)#恢复[root@localhost ~]# mysql -uroot -p < all.sql Enter password: mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.00 sec)

指定数据库的备份

[root@localhost ~]# mysqldump --databases test -uroot -p > test.sqlEnter password: [root@localhost ~]# ls公共  图片  音乐     anaconda-ks.cfg       ip.txt     #test.sql模板  文档  桌面     initial-setup-ks.cfg  jilu.txt视频  下载  all.sql  init.sh               reboot.sh#恢复测试mysql> drop database test;Query OK, 9 rows affected (0.04 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema |+--------------------+3 rows in set (0.00 sec)[root@localhost ~]# mysql -uroot -p < test.sql Enter password: mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+4 rows in set (0.00 sec)#备份指定表[root@localhost ~]# mysqldump test class -p > test_class.sqlEnter password: [root@localhost ~]# ls公共  图片  音乐     anaconda-ks.cfg       ip.txt     test_class.sql模板  文档  桌面     initial-setup-ks.cfg  jilu.txt   test.sql视频  下载  all.sql  init.sh               reboot.sh#恢复 要指明所在表[root@localhost ~]# mysql -uroot -p test < test_class.sql Enter password:

增量备份---二进制备份

修改配置文件

#先完整备份#屏蔽系统的 生成自己的二进制日志vim /etc/my.cnf  [mysqld]log-bin=mysql-binbinlog_format="statement"#或者[root@localhost mysql]# vim /etc/my.cnf.d/mysql-server.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysql/mysqld.logpid-file=/run/mysqld/mysqld.pidlog-bin=mysql-bin   1binlog_format="statement"   1

模拟情况

mysql> insert college value(4,'商学院')-> ;Query OK, 1 row affected (0.00 sec)mysql> select * from college;+------------+--------------------+| college_id | name               |+------------+--------------------+|          1 | 计算机学院         ||          2 | 经济管理学院       ||          3 | 机械工程学院       ||          4 | 商学院             |+------------+--------------------+4 rows in set (0.00 sec)mysql> delete from college where college_id=4-> ;Query OK, 1 row affected (0.00 sec)

查看二进制文件

[root@localhost ~]# cd /var/lib/mysql[root@localhost mysql]# mysqlbinlog mysql-bin.000002# The proper term is pseudo_replica_mode, but we use this compatibility alias# to make the statement usable on server versions 8.0.24 and older./*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;# at 4.....#寻找备份位置[root@localhost mysql]# mysqlbinlog --start-position=3520 --stop-position=3630 mysql-bin.000002# The proper term is pseudo_replica_mode, but we use this compatibility alias# to make the statement usable on server versions 8.0.24 and older./*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;.....#恢复[root@localhost mysql]# mysqlbinlog --start-position=3520 --stop-position=3630 mysql-bin.000002 | mysql -uroot -pEnter password: #mysql> select * from college;+------------+--------------------+| college_id | name               |+------------+--------------------+|          1 | 计算机学院         ||          2 | 经济管理学院       ||          3 | 机械工程学院       ||          4 | 商学院             |+------------+--------------------+4 rows in set (0.00 sec)