## 基于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 changed?mysql> 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: 0?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 ? | ? ? ? || 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: 0?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 ? | ? ? ? || 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: 0?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 ? | ? ? ? || 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: 0?mysql> 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: 0?mysql> 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: 0?mysql> 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: 0?mysql> 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: 0?mysql> alter table c2505 modify id int auto_increment;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0?mysql> 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: 0?mysql> 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: 0?mysql> 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: 0?mysql> 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: 0?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 | 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: 0?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 | 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: 0?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 |+----+------+--------+-------+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)
![[特殊字符] Python 實戰 | 批量統計中文文檔詞頻并導出 Excel](http://pic.xiahunao.cn/[特殊字符] Python 實戰 | 批量統計中文文檔詞頻并導出 Excel)
 開發環境教程](http://pic.xiahunao.cn/在 Windows 系統上配置 [go-zero](https://go-zero.dev) 開發環境教程)
![[netty5: WebSocketFrame]-源碼分析](http://pic.xiahunao.cn/[netty5: WebSocketFrame]-源碼分析)
ECC橢圓曲線)
