MySQL客戶端工具的使用與MySQL SQL語句

2022 年 7 月 30 日
筆記
MySQL

MySQL客戶端工具的使用

1、MySQL程式的組成

客戶端
- mysql:CLI互動式客戶端程式
- mycli:CLI互動式客戶端程式;使用sql語句時會有提示資訊
- mysql_secure_installation：安全初始化，強烈建議安裝完以後執行此命令
- mysqldump：mysql備份工具
- mysqladmin：官方提供的shell命令行工具
伺服器端
- mysqld

2、MySQL監聽地址

伺服器監聽的兩種socket地址：

socket類型	說明
ip socket	默認監聽在tcp的3306埠，支援遠程通訊
unix sock	監聽在sock文件上(/tmp/mysql.sock,/var/lib/mysql/mysql.sock)僅支援本地通訊

3、資料庫配置文件

資料庫配置文件為：/etc/my.cnf和/etc/my.cnf.d目錄下的配置文件

//修改配置文件，配置字元編碼
[root@localhost ~]# vim /etc/my.cnf
    [mysqld]
    character-set-server=utf8mb4
    collation-server=utf8mb4_general_ci

    [client]
    default-character-set=utf8mb4

    [mysql]
    default-character-set=utf8mb4

4、客戶端工具的使用

//語法：mysql [OPTIONS] [database]
//常用的OPTIONS：
    -uUSERNAME      //指定用戶名，默認為root
    -hHOST          //指定伺服器主機，默認為localhost，推薦使用ip地址
    -pPASSWORD      //指定用戶的密碼
    -P              //指定資料庫監聽的埠，如-P3307
    -S              //指定套接字文件位置，多實例部署MySQL時需要使用
    -V              //查看當前使用的mysql版本
    -e              //不登錄mysql執行sql語句後退出，常用於腳本
    --defaults-file=配置文件	//指定MySQL配置文件位置，用於載入客戶端配置
    
[root@localhost ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper

[root@localhost ~]# mysql -uroot -pPasswd123! -h127.0.0.1
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 4
Server version: 5.7.38 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> 
//注意，不推薦直接在命令行里直接用-pPASSWORD的方式登錄，而是使用-p選項，然後互動式輸入密碼

[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+

MySQL 數據類型

MySQL支援多種類型，大致可以分為三類：數值、日期/時間和字元串(字元)類型。

1、數值類型

MySQL 支援所有標準 SQL 數值數據類型。

這些類型包括嚴格數值數據類型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似數值數據類型(FLOAT、REAL 和 DOUBLE PRECISION)。

類型	大小	範圍（有符號）	範圍（無符號）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整數值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整數值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整數值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	極大整數值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	單精度浮點數值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	雙精度浮點數值
DECIMAL	對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2	依賴於M和D的值	依賴於M和D的值	小數值

2、日期和時間類型

表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間類型有一個有效值範圍和一個”零”值，當指定不合法的MySQL不能表示的值時使用”零”值。

TIMESTAMP類型有專有的自動更新特性。

類型	大小 ( bytes)	範圍	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/’838:59:59’	HH:MM:SS	時間值或持續時間
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和時間值
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和時間值，時間戳

3、字元串類型

字元串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

類型	大小	用途
CHAR	0-255 bytes	定長字元串
VARCHAR	0-65535 bytes	變長字元串
TINYBLOB	0-255 bytes	不超過 255 個字元的二進位字元串
TINYTEXT	0-255 bytes	短文本字元串
BLOB	0-65 535 bytes	二進位形式的長文本數據
TEXT	0-65 535 bytes	長文本數據
MEDIUMBLOB	0-16 777 215 bytes	二進位形式的中等長度文本數據
MEDIUMTEXT	0-16 777 215 bytes	中等長度文本數據
LONGBLOB	0-4 294 967 295 bytes	二進位形式的極大文本數據
LONGTEXT	0-4 294 967 295 bytes	極大文本數據

注意

char(n) 和 varchar(n) 中括弧中 n 代表字元的個數，並不代表位元組個數，比如 CHAR(30) 就可以存儲 30 個字元。

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

BINARY 和 VARBINARY 類似於 CHAR 和 VARCHAR，不同的是它們包含二進位字元串而不要非二進位字元串。也就是說，它們包含位元組字元串而不是字元字元串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值值。

BLOB 是一個二進位大對象，可以容納可變數量的數據。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在於可容納存儲範圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 類型，可存儲的最大長度不同，可根據實際情況選擇。

MySQL SQL語句

1、DDL操作

1.1 資料庫DDL操作

//創建資料庫
//語法：create database [if not exists] 'da_name';

//創建資料庫zsl
mysql> create database zsl;
Query OK, 1 row affected (0.00 sec)

//查看當前實例有哪些資料庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)

//刪除資料庫
//語法：drop database [if exists] 'da_name';

//刪除資料庫zsl
mysql> drop database zsl;
Query OK, 0 rows affected (0.00 sec)

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

//創建資料庫時,設置資料庫的字符集：
//character set:指定資料庫採用的字符集,utf8不能寫成utf-8,建議使用utf8mb4字符集
//collate:指定資料庫字符集的排序規則,utf8mb4的默認排序規則為utf8mb4_general_ci（通過show character set查看）
mysql> create database dbtest character set utf8mb4 collate utf8mb4_general_ci;

//資料庫客戶端字元編碼需要和服務端字符集保持一致
//SET NAMES:指定客戶端字符集
mysql> SET NAMES utf8mb4;

//字符集配置都可寫入MySQL配置文件中啟動MySQL服務時自動載入

1.2 表DDL操作

//創建表
//語法：create table table_name (col1 datatype 修飾符,col2 datatype 修飾符) ENGINE='存儲引擎類型';

//在資料庫lsz里創建表lsztable
mysql> CREATE DATABASE IF NOT EXISTS lsz;		//創建資料庫lsz
Query OK, 1 row affected (0.00 sec)

mysql> use lsz;		//進入lsz資料庫
Database changed

//創建lsztable表
mysql> create table lsztable (id int(10) not null,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)

//查看當前資料庫有哪些表
mysql> show tables;
+---------------+
| Tables_in_lsz |
+---------------+
| lsztable      |
+---------------+
1 row in set (0.00 sec)

//刪除表
//語法：drop table [ if exists ] 'table_name';

//刪除表lsztable
mysql> drop table lsztable;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

//創建表時，設置欄位、表的字元編碼
mysql> create table tb_course(id int not null primary key auto_increment,course_name vaarchar(50)) default character set utf8 collate utf8_general-ci;

1.3 用戶操作

MySQL用戶帳號由兩部分組成，如’USERNAME’@’HOST’，表示此USERNAME只能從此HOST上遠程登錄；HOST用於限制此用戶可通過哪些主機遠程連接mysql程式。

HOST的值可為：

IP地址，如：172.16.12.129
通配符
- %：匹配任意長度的任意字元，常用於設置允許從任何主機登錄
- _：匹配任意單個字元

//資料庫用戶創建
//語法：create user 'username'@'host' [identified by 'password'];

//創建資料庫用戶zsl
mysql> create user 'zsl'@'127.0.0.1' identified by 'Passwd123!';
Query OK, 0 rows affected (0.00 sec)

//使用新創建的用戶和密碼登錄
[root@localhost ~]# mysql -uzsl -pPasswd123! -h127.0.0.1
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 5
Server version: 5.7.38 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> 

//刪除資料庫用戶
//語法：drop user 'username'@'host'; 

//刪除資料庫用戶zsl
mysql> drop user 'zsl'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

1.4 查看命令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 |
......
......

mysql> show variables like '%char%';		//查看客戶端的字元編碼
+--------------------------------------+----------------------------+
| Variable_name                        | Value                      |
+--------------------------------------+----------------------------+
| character_set_client                 | utf8                       |
| character_set_connection             | utf8                       |
| character_set_database               | latin1                     |
| character_set_filesystem             | binary                     |
| character_set_results                | utf8                       |
| character_set_server                 | latin1                     |
| character_set_system                 | utf8                       |
| character_sets_dir                   | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1                          |
+--------------------------------------+----------------------------+
9 rows in set (0.00 sec)

mysql> select charset(id) from lsztable;		//查看某表中某欄位使用的字元編碼
......
......

mysql> show engines;		//查看當前資料庫支援的所有存儲引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | 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 |
| lsz                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from lsz;		//不進入某資料庫而列出其包含的所有表
+---------------+
| Tables_in_lsz |
+---------------+
| lsztable      |
+---------------+
1 row in set (0.00 sec)

//查看錶結構
//語法：desc [db_name.]table_name;

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

//查看某表的創建命令，可以看到創建表時設置的參數
//語法：show create table table_name;

mysql> show create table lsz.lsztable;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                              |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| lsztable | CREATE TABLE `lsztable` (
  `id` int(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

//查看某表的狀態
//語法：show table status like 'table_name'\G

mysql> show table status like 'lsztable'\G;
*************************** 1. row ***************************
           Name: lsztable
         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: 2022-07-26 08:40:01
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

1.5 alter修改操作

//修改資料庫屬性
//語法：alter adtabase 'db_name' character set charset_name | collate collation_name

//修改資料庫字符集為utf8
mysql> alter database lsz character set utf8;
Query OK, 1 row affected (0.01 sec)

//修改表
//語法：alter table <table_name> [option]

//給lsztable表添加新的一列，先查看錶結構
mysql> desc lsztable;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//給lsztable表添加一列'phone'
mysql> alter table lsztable add phone int(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

//查看錶結構
mysql> desc lsztable;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
| phone | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

//修改lsztable表中'phone'列為'sex'
mysql> alter table lsztable change phone sex varchar(4);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

//修改表名為zsltable
mysql> alter table lsztable rename to zsltable;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_lsz |
+---------------+
| zsltable      |
+---------------+
1 row in set (0.00 sec)

//修改資料庫字元編碼
mysql> altar database dbtest character set utf8 collate utf8_general_ci;

//修改表字元編碼
mysql> altar table tbtest character set utf8 collate utf8_general_ci;

1.6 獲取幫助

//獲取命令使用幫助
//語法：help commond;

mysql> help create table;       //獲取創建表的幫助
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
 ......
 ......

2、DML操作

DML操作包括增(INSERT)、刪(DELETE)、改(UPDATE)、查(SELECT)，均屬針對錶的操作。

2.1 insert語句

//DML操作之增操作insert
//語法：inster [into] table_name [(column_name,...)] {values | value} (value1,...),(...),...

mysql> use lsz;
Database changed

//一次插入一條記錄
mysql> insert zsltable (id,name,age,sex) value(1,'tom',20,'nan');
Query OK, 1 row affected (0.00 sec)

//一次插入多條記錄
mysql> insert zsltable (id,name,age,sex) values(2,'jerry',23,'nv'),(3,'zsl',18,'nan'),(4,'sean',null,'nv');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

2.2 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 lsz;
Database changed

#查看錶所有的內容
mysql> select * from zsltable;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
|  3 | zsl   |   18 | nan  |
|  4 | sean  | NULL | nv   |
+----+-------+------+------+
4 rows in set (0.00 sec)

#查看錶的name欄位內容
mysql> select name from zsltable;
+-------+
| name  |
+-------+
| tom   |
| jerry |
| zsl   |
| sean  |
+-------+
4 rows in set (0.00 sec)

#根據age欄位內容從低到高排列
mysql> select * from zsltable order by age;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  4 | sean  | NULL | nv   |
|  3 | zsl   |   18 | nan  |
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
+----+-------+------+------+
4 rows in set (0.00 sec)

#根據age欄位內容從高到低排列
mysql> select * from zsltable order by age desc;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  2 | jerry |   23 | nv   |
|  1 | tom   |   20 | nan  |
|  3 | zsl   |   18 | nan  |
|  4 | sean  | NULL | nv   |
+----+-------+------+------+
4 rows in set (0.00 sec)

#根據age欄位內容從低到高排列取前兩行
mysql> select * from zsltable order by age limit 2;
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  4 | sean | NULL | nv   |
|  3 | zsl  |   18 | nan  |
+----+------+------+------+
2 rows in set (0.00 sec)

#根據age欄位內容從低到高排列，跳過第一行取下一行
mysql> select * from zsltable order by age limit 1,1;
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  3 | zsl  |   18 | nan  |
+----+------+------+------+
1 row in set (0.00 sec)

#查看錶中年齡大於等於20的行；
mysql> select * from zsltable where age >=20;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
+----+-------+------+------+
2 rows in set (0.00 sec)

#查看錶中年齡大於等於20且名字叫jerry的行；
mysql> select * from zsltable where age >=20 and name = 'jerry';
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  2 | jerry |   23 | nv   |
+----+-------+------+------+
1 row in set (0.00 sec)

#查看錶中年齡18到23的行
mysql> select * from zsltable where age between 18 and 23;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
|  3 | zsl   |   18 | nan  |
+----+-------+------+------+
3 rows in set (0.00 sec)

#查看錶中年齡不為空的
mysql> select * from zsltable where age is not null;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
|  3 | zsl   |   18 | nan  |
+----+-------+------+------+
3 rows in set (0.00 sec)

#查看錶中年齡為空的
mysql> select * from zsltable where age is null;
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  4 | sean | NULL | nv   |
+----+------+------+------+
1 row in set (0.00 sec)

#查找name欄位含有z的行
mysql> select * from zsltable where name like '%z%';
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  3 | zsl  |   18 | nan  |
+----+------+------+------+
1 row in set (0.00 sec)

2.3 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 zsltable;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
|  3 | zsl   |   18 | nan  |
|  4 | sean  | NULL | nv   |
+----+-------+------+------+
4 rows in set (0.00 sec)

mysql> update zsltable set age=25 where name = 'sean';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

2.4 delete語句

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

mysql> select * from zsltable;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
|  3 | zsl   |   18 | nan  |
|  4 | sean  |   25 | nv   |
+----+-------+------+------+
4 rows in set (0.00 sec)

mysql> delete from zsltable where id =3;		//刪除某條記錄
Query OK, 1 row affected (0.00 sec)

mysql> select * from zsltable;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
|  4 | sean  |   25 | nv   |
+----+-------+------+------+
3 rows in set (0.00 sec)

mysql> delete from zsltable;		 //刪除整張表的內容
Query OK, 3 rows affected (0.01 sec)

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

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

2.5 truncate語句

truncate與delete的區別：

語句類型	特點
delete	DELETE刪除表內容時僅刪除內容，但會保留表結構 DELETE語句每次刪除一行，並在事務日誌中為所刪除的每行記錄一項可以通過回滾事務日誌恢複數據非常佔用空間
truncate	刪除表中所有數據，且無法恢復表結構、約束和索引等保持不變，新添加的行計數值重置為初始值執行速度比DELETE快，且使用的系統和事務日誌資源少通過釋放存儲表數據所用的數據頁來刪除數據，並且只在事務日誌中記錄頁的釋放對於有外鍵約束引用的表，不能使用TRUNCATE TABLE刪除數據不能用於加入了索引視圖的表

語句類型

特點

delete

DELETE刪除表內容時僅刪除內容，但會保留表結構 DELETE語句每次刪除一行，並在事務日誌中為所刪除的每行記錄一項可以通過回滾事務日誌恢複數據非常佔用空間

truncate

刪除表中所有數據，且無法恢復表結構、約束和索引等保持不變，新添加的行計數值重置為初始值執行速度比DELETE快，且使用的系統和事務日誌資源少通過釋放存儲表數據所用的數據頁來刪除數據，並且只在事務日誌中記錄頁的釋放對於有外鍵約束引用的表，不能使用TRUNCATE TABLE刪除數據不能用於加入了索引視圖的表

//語法：TRUNCATE table_name;

mysql> select * from zsltable;
+----+-------+------+------+
| id | name  | age  | sex  |
+----+-------+------+------+
|  1 | tom   |   20 | nan  |
|  2 | jerry |   23 | nv   |
|  3 | zsl   |   18 | nan  |
|  4 | sean  |   25 | nv   |
+----+-------+------+------+
4 rows in set (0.00 sec)

mysql> truncate zsltable;
Query OK, 0 rows affected (0.01 sec)

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

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

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

3、DCL操作

3.1 創建授權grant

許可權類型(priv_type)

許可權類型	含義
ALL	所有許可權
SELECT	讀取內容的許可權
INSERT	插入內容的許可權
UPDATE	更新內容的許可權
DELETE	刪除內容的許可權

指定要操作的對象db_name.table_name

表示方式	含義
.	所有庫的所有表
db_name	指定庫的所有表
db_name.table_name	指定庫的指定表

WITH GRANT OPTION：被授權的用戶可將自己的許可權副本轉贈給其他用戶，說白點就是將自己的許可權完全複製給另一個用戶。不建議使用。

//語法：GRANT priv_type,... ON [object_type] db_name.table_name TO 『username'@'host' [IDENTIFIED BY 'password'] [WITH GRANT OPTION];

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

//授權lsz用戶在資料庫本機上登錄訪問所有資料庫
mysql> GRANT ALL ON *.* TO 'lsz'@'localhost' IDENTIFIED BY 'Passwd123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//也可表示為：
mysql> GRANT ALL ON *.* TO 'lsz'@'127.0.0.1' IDENTIFIED BY 'Passwd123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授權lsz用戶在192.168.111.135上遠程登錄訪問hzz資料庫
mysql> GRANT ALL ON lsz.* TO 'lsz'@'192.168.111.135' IDENTIFIED BY 'Passwd123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授權lsz用戶在所有位置上遠程登錄訪問lsz資料庫
mysql> GRANT ALL ON lsz.* TO 'lsz'@'%' IDENTIFIED BY 'Passwd123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//刷新授權表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3.2 查看授權

//查看當前登錄用戶的授權資訊
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.00 sec)

//查看指定用戶lsz的授權資訊
mysql> show grants for 'lsz'@'localhost';
+--------------------------------------------------+
| Grants for lsz@localhost                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lsz'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)

3.3 取消授權REVOKE

//語法：REVOKE priv_type,... ON db_name.table_name FROM 'username'@'host';

mysql> revoke all on *.* from 'lsz'@'192.168.111.135';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

注意：mysql服務進程啟動時會讀取mysql庫中的所有授權表至記憶體中：

GRANT或REVOKE等執行許可權操作會保存於表中，mysql的服務進程會自動重讀授權表，並更新至記憶體中
對於不能夠或不能及時重讀授權表的命令，可手動讓mysql的服務進程重讀授權表

mysql> FLUSH PRIVILEGES;

實戰案例

1.搭建mysql服務

[root@localhost ~]# wget //dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm 
[root@localhost ~]# dnf -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel  --nogpgcheck
[root@localhost ~]# systemctl enable --now mysqld

2.創建一個以你名字為名的資料庫，並創建一張表student，該表包含三個欄位（id，name，age），表結構如下：

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

mysql> create database zsl;
Query OK, 1 row affected (0.00 sec)

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

mysql> create table student(id int(11) primary key auto_increment,name varchar(100) not null,age tinyint(4));
Query OK, 0 rows affected (0.01 sec)

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

3.查看下該新建的表有無內容（用select語句）

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

4.往新建的student表中插入數據（用insert語句），結果應如下所示：

+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

mysql> insert into student (id,name,age) values(1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'seann',28),(5,'zhangsan',26),(6,'zhangsan',20),(7,'lisi',null),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyyi',15),(11,'qiuxiaotian',20);
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | seann       |   28 |
|  5 | zhangsan    |   26 |
|  6 | zhangsan    |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyyi      |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
11 rows in set (0.01 sec)

5.修改lisi的年齡為50

mysql> update student set age=50 where name='lisi';
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  |
+----+------+------+
|  7 | lisi |   50 |
+----+------+------+
1 row in set (0.00 sec)

6.以age欄位降序排序

mysql> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  7 | lisi        |   50 |
|  4 | seann       |   28 |
|  5 | zhangsan    |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangsan    |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyyi      |   15 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+
11 rows in set (0.00 sec)

7.查詢student表中年齡最小的3位同學跳過前2位

mysql> select * from student order by age  limit 2,3;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
| 10 | qiuyyi      |   15 |
|  1 | tom         |   20 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
3 rows in set (0.00 sec)

8.查詢student表中年齡最大的4位同學

mysql>  select * from student order by age desc limit 4;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  7 | lisi     |   50 |
|  4 | seann    |   28 |
|  5 | zhangsan |   26 |
|  3 | wangqing |   25 |
+----+----------+------+
4 rows in set (0.00 sec)

9.查詢student表中名字叫zhangshan的記錄

mysql> select * from student where name = 'zhangsan' ;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | zhangsan |   26 |
|  6 | zhangsan |   20 |
+----+----------+------+
2 rows in set (0.00 sec)

10.查詢student表中名字叫zhangshan且年齡大於20歲的記錄

mysql> select * from student where name = 'zhangsan' and age >20;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  5 | zhangsan |   26 |
+----+----------+------+
1 row in set (0.00 sec)

11.查詢student表中年齡在23到30之間的記錄

mysql> select * from student where age between 23 and 30;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  2 | jerry    |   23 |
|  3 | wangqing |   25 |
|  4 | seann    |   28 |
|  5 | zhangsan |   26 |
+----+----------+------+
4 rows in set (0.00 sec)

12.修改wangwu的年齡為100

mysql> update student set age=100 where name = 'wangwu';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

13.刪除student中名字叫zhangshan且年齡小於等於20的記錄

mysql>  delete from student where name ='zhangsan'and age<=20;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student where name = 'zhangsan' and age<=20;
Empty set (0.00 sec)

Tags: MySQL

MySQL客戶端工具的使用與MySQL SQL語句

MySQL客戶端工具的使用

1、MySQL程式的組成

2、MySQL監聽地址

3、資料庫配置文件

4、客戶端工具的使用

MySQL 數據類型

1、數值類型

2、日期和時間類型

3、字元串類型

MySQL SQL語句

1、DDL操作

1.1 資料庫DDL操作

1.2 表DDL操作

1.3 用戶操作

1.4 查看命令show

1.5 alter修改操作

1.6 獲取幫助

2、DML操作

2.1 insert語句

2.2 select語句

2.3 update語句

2.4 delete語句

2.5 truncate語句

3、DCL操作

3.1 創建授權grant

3.2 查看授權

3.3 取消授權REVOKE

實戰案例

VirMach 便宜 VPS

QNews

MySQL客戶端工具的使用與MySQL SQL語句

MySQL客戶端工具的使用

1、MySQL程式的組成

2、MySQL監聽地址

3、資料庫配置文件

4、客戶端工具的使用

MySQL 數據類型

1、數值類型

2、日期和時間類型

3、字元串類型

MySQL SQL語句

1、DDL操作

1.1 資料庫DDL操作

1.2 表DDL操作

1.3 用戶操作

1.4 查看命令show

1.5 alter修改操作

1.6 獲取幫助

2、DML操作

2.1 insert語句

2.2 select語句

2.3 update語句

2.4 delete語句

2.5 truncate語句

3、DCL操作

3.1 創建授權grant

3.2 查看授權

3.3 取消授權REVOKE

實戰案例

分享此文：

Related Posts

【分散式鎖的演化】電商「超賣」場景實戰

Google 正在做的項目，可能讓索尼真的沒明天了

CS內網橫向移動 模擬滲透實操 超詳細

雷克薩斯LM事故後無法打開車門：同門兄弟ES曾遇類似情況

VirMach 便宜 VPS

QNews

熱門搜尋

CS內網橫向移動模擬滲透實操超詳細