MySQL的DDL、DML、DQL陳述句和單表增、刪、改、查
目錄- MySQL的DDL、DML、DQL陳述句和單表增、刪、改、查
- 實驗準備：
- 實驗開始：
- DDL陳述句
- DML陳述句
- DQL陳述句
- 單表操作的分組統計
實驗準備：
- 初始化設定過的centos虛擬機
- 安裝MySQL資料庫
實驗開始：
DDL陳述句
表：二維關系 設計表：遵循規范
定義：欄位，索引
欄位：欄位名，欄位資料型別，修飾符
約束，索引：應該創建在經常用作查詢條件的欄位上
- 創建和洗掉資料庫
MariaDB [(none)]> create database testdb1; (分號為結束符)
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases; （列出所有資料庫）
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb1 |
+--------------------+
4 rows in set (0.00 sec)
[root@centos7 ~]#tree /data/mysql/testdb1/ （其實就是在MySQL下面建立了一個目錄）
/data/mysql/testdb1/
└── db.opt
[root@centos7 ~]#cat /data/mysql/testdb1/db.opt （也可以查看里面的內容，字符集也可以看到）
default-character-set=latin1
default-collation=latin1_swedish_ci
0 directories, 1 file
MariaDB [testdb1]> drop database testdb1; （洗掉資料庫，同時MySQL下面的testdb1目錄也會洗掉）
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
- 查詢資料庫字符集
MariaDB [(none)]> show create database testdb1; (查看testdb1的資料庫字符集)
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| testdb1 | CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET latin1 */ | （這里最后的為字符集既latin1這個字符集不支持漢字的，）
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
(這樣也可以查看比較好辨認)
MariaDB [(none)]> show create database testdb1\G;
*************************** 1. row ***************************
Database: testdb1
Create Database: CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
MariaDB [(none)]> show create database mysql; （查看MySQL的資料庫字符集）
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
- 修改字符集
MariaDB [(none)]> alter database testdb1 CHARACTER SET utf8mb4; (這個字符集支持全球文字還支持表情包)
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database testdb1;
+----------+---------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------+
| testdb1 | CREATE DATABASE `testdb1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
[root@centos7 ~]#cat /data/mysql/testdb1/db.opt (檔案也會修改)
default-character-set=utf8mb4
default-collation=utf8mb4_general_ci
- 創建一個表做實驗
MariaDB [(none)]> use testdb1;
Database changed
MariaDB [testdb1]> create table test ( id int unsigned auto_increment primary key,name varchar(10) not null,mobile char(11) not null );
Query OK, 0 rows affected (0.01 sec)
MariaDB [testdb1]> desc test; （查看表結構）
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| mobile | char(11) | NO | | NULL | |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> show tables; （列出當前庫里的所有表）
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
(查看表內容，先要進入存在這個表的資料庫) 這個表是我從外部匯入進來的沒有手工創建，需要的話下次我貼出來，
MariaDB [(none)]> use hellodb
Database changed
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
（創建表獲取幫助命令）
MariaDB [hellodb]> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
- 克隆表
MariaDB [hellodb]> create table newstudents select * from students; （通過查詢現存表創建；新表會被直接插入查詢而來的資料）
Query OK, 25 rows affected (0.01 sec)
Records: 25 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newstudents |
| scores |
| students |
| teachers |
| toc |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> select * from newstudents;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
（注意：這樣不會把主鍵之類的復制過來，只是復制資料，表結構不一樣）如下：
MariaDB [hellodb]> desc newstudents;
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| StuID | int(10) unsigned | NO | | 0 | |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
通過復制現存的表的表結構創建，但不復制資料
MariaDB [hellodb]> create table newstudents2 like students;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> select * from newstudents2; （這里查看資料是沒有的）
Empty set (0.00 sec)
MariaDB [hellodb]> desc newstudents2; （但是有表結構）
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
- 表查看
查看支持的engine型別：
MariaDB [hellodb]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)
查看表：
MariaDB [hellodb]> show tables ; （列出當前資料庫中的所有表）
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newstudents |
| newstudents2 |
| scores |
| students |
| teachers |
| toc |
+-------------------+
9 rows in set (0.00 sec)
MariaDB [hellodb]> show tables from mysql; （指明資料庫查看這個資料庫的表）
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
30 rows in set (0.00 sec)
查看表結構：
MariaDB [hellodb]> desc students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
表結構欄位的意思，
1. Field:欄位表示的是別名
2. Type:欄位表示的是列的資料型別
3. Null :欄位表示這個列是否能取空值
4. Key :在mysql中key 和index 是一樣的意思，這個Key列可能會看到有如下的值：PRI(主鍵)、MUL(普通的b-tree索引)、UNI(唯一索引)
5. Default: 列的默認值
6. Extra :其它資訊
查看指定表的創建命令： （既創建程序）
MariaDB [hellodb]> show create table students;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| students | CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint(3) unsigned DEFAULT NULL,
`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看看表的狀態：（查看指定某個表狀態要加單引號）
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'students'\G;
*************************** 1. row ***************************
Name: students
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 25
Avg_row_length: 655
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 26
Create_time: 2019-11-20 21:19:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
查看庫中所有表的狀態：
MariaDB [hellodb]> show table status from hellodb\G;
*************************** 1. row ***************************
Name: classes
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 9
Create_time: 2019-11-20 21:19:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: coc
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 14
Avg_row_length: 1170
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 15
Create_time: 2019-11-20 21:19:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
.
.
.(省略證明我不是來混行數的)
.
.
*************************** 8. row ***************************
Name: teachers
Engine: InnoDB （這里是使用什么存盤引擎的意思）
Version: 10
Row_format: Dynamic
Rows: 4
Avg_row_length: 4096
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 5
Create_time: 2019-11-20 21:19:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 9. row ***************************
Name: toc
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: 1
Create_time: 2019-11-20 21:19:51
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
9 rows in set (0.00 sec)
- 修改洗掉表
洗掉表：
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newstudents |
| newstudents2 |
| scores |
| students |
| students2 |
| teachers |
| toc |
+-------------------+
10 rows in set (0.00 sec)
MariaDB [hellodb]> drop table newstudents2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| newstudents |
| scores |
| students |
| students2 |
| teachers |
| toc |
+-------------------+
9 rows in set (0.00 sec)
查看修改表幫助：
MariaDB [hellodb]> help ALTER TABLE
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
修改表：
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> alter table students rename s1; （修改表名為s1）
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select * from students;
ERROR 1146 (42S02): Table 'hellodb.students' doesn't exist
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| s1 |
| scores |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> alter table s1 add phone varchar(11) after name; （在name欄位后面添加phone欄位） ADD：添加欄位 varchar可變的 字符長度為11為
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> alter table s1 modify phone int; （把phone的資料型別改為int）
Query OK, 25 rows affected (0.01 sec)
Records: 25 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> desc s1
-> ;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| phone | int(11) | YES | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
（這些就不一一解釋了有興趣的可以自己試一試）
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students drop primary key ;
ALTER TABLE students drop primary key ;
DESC students;
ALTER TABLE students DROP age;
DML陳述句
DML: INSERT, DELETE, UPDATE
INSERT 陳述句
功能：一次插入一行或多行資料 語法
UPDATE 陳述句
陳述句用于修改表中的資料， 注意：一定要有限制條件，否則將修改所有行的指定欄位
DELETE陳述句
陳述句用于洗掉表中的行， 注意：一定要有限制條件，否則將清空表中的所有資料
可以在不洗掉表的情況下洗掉所有的行，這意味著表的結構、屬性和索引都是完整的：
DELETE FROM table_name 或者 DELETE * FROM table_name
- INSERT陳述句實驗: 三種陳述句
MariaDB [hellodb]> help insert;
Name: 'INSERT'
Description:
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
- 第一種語法
MariaDB [hellodb]> insert into s1 values(26,'xietingfeng',NULL,23,'M',2,1); (添加一行表記錄)
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
+-------+---------------+-------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
（如果出現這個提示可能是你少寫了一個欄位或者寫錯了）
MariaDB [hellodb]> insert into s1 values(26,'xietingfeng',23,'M',2,1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
注意：如果想省略欄位名的話，只能所有欄位都賦值的話可以省略，單個賦值不可以省略，
如下：挑出三個欄位來賦值，必須對應的寫上，
MariaDB [hellodb]> insert into s1(name,age,classid) values ('liudehua',18,1);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
27 rows in set (0.00 sec) （↑ 這里是因為表結構設定默認不允許為空隨機分配的值）
一次行兩條記錄賦值：
MariaDB [hellodb]> insert into s1(name,age,gender,classid) values('mahuateng',20,'M',3),('wuyanzu',19,'M',4);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
29 rows in set (0.00 sec)
- 第二種語法
MariaDB [hellodb]> insert s1 set name='wuzetian',age=21,gender='F';
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
30 rows in set (0.00 sec)
(使用的不多一般使用第一種了解一下)
- 第三種語法
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> insert s1 (name,classid,gender,age) select name,age,gender,18 from teachers;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
34 rows in set (0.00 sec)
（批量匯入，A表參考B表的結構匯入到B表中，主鍵必須是唯一的不可匯入A表的主鍵）
- UPDATE陳述句
MariaDB [hellodb]> help update; (陳述句幫助)
Name: 'UPDATE'
Description:
Syntax:
Single-table syntax:
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
MariaDB [hellodb]> select * from n1; （不加指定修改會把整個列都修改）
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
34 rows in set (0.00 sec)
MariaDB [hellodb]> update n1 set teacherid=1;
Query OK, 32 rows affected (0.00 sec)
Rows matched: 34 Changed: 32 Warnings: 0
MariaDB [hellodb]> select * from n1;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 1 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 1 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 1 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 1 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | 1 |
| 7 | Xi Ren | NULL | 19 | F | 3 | 1 |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | 1 |
| 9 | Ren Yingying | NULL | 20 | F | 6 | 1 |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | 1 |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | 1 |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | 1 |
| 13 | Tian Boguang | NULL | 33 | M | 2 | 1 |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | 1 |
| 15 | Duan Yu | NULL | 19 | M | 4 | 1 |
| 16 | Xu Zhu | NULL | 21 | M | 1 | 1 |
| 17 | Lin Chong | NULL | 25 | M | 4 | 1 |
| 18 | Hua Rong | NULL | 23 | M | 7 | 1 |
| 19 | Xue Baochai | NULL | 18 | F | 6 | 1 |
| 20 | Diao Chan | NULL | 19 | F | 7 | 1 |
| 21 | Huang Yueying | NULL | 22 | F | 6 | 1 |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | 1 |
| 23 | Ma Chao | NULL | 23 | M | 4 | 1 |
| 24 | Xu Xian | NULL | 27 | M | NULL | 1 |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | 1 |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | 1 |
| 28 | mahuateng | NULL | 20 | M | 3 | 1 |
| 29 | wuyanzu | NULL | 19 | M | 4 | 1 |
| 30 | wuzetian | NULL | 21 | F | NULL | 1 |
| 31 | Song Jiang | NULL | 18 | M | 45 | 1 |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | 1 |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | 1 |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | 1 |
+-------+---------------+-------+-----+--------+---------+-----------+
34 rows in set (0.00 sec)
避免的方法：登陸的時候加-U進入安全模式，改模式下不加where指定不能更改，
[root@centos7 ~]#mysql -U -uroot -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.29-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> update n1 set teacherid=2;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]>
或者修改配檔案： 在mysql下面加上這一行
[root@centos7 ~]#vim /etc/my.cnf.d/mysql-clients.cnf
[mysql]
safe-updates
MariaDB [hellodb]> update n1 set teacherid=2 where stuid=25; （用where指定主鍵的某一范圍修改）
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> select * from n1
-> ;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 1 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 1 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 1 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 1 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | 1 |
| 7 | Xi Ren | NULL | 19 | F | 3 | 1 |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | 1 |
| 9 | Ren Yingying | NULL | 20 | F | 6 | 1 |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | 1 |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | 1 |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | 1 |
| 13 | Tian Boguang | NULL | 33 | M | 2 | 1 |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | 1 |
| 15 | Duan Yu | NULL | 19 | M | 4 | 1 |
| 16 | Xu Zhu | NULL | 21 | M | 1 | 1 |
| 17 | Lin Chong | NULL | 25 | M | 4 | 1 |
| 18 | Hua Rong | NULL | 23 | M | 7 | 1 |
| 19 | Xue Baochai | NULL | 18 | F | 6 | 1 |
| 20 | Diao Chan | NULL | 19 | F | 7 | 1 |
| 21 | Huang Yueying | NULL | 22 | F | 6 | 1 |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | 1 |
| 23 | Ma Chao | NULL | 23 | M | 4 | 1 |
| 24 | Xu Xian | NULL | 27 | M | NULL | 1 |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | 2 |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | 1 |
| 28 | mahuateng | NULL | 20 | M | 3 | 1 |
| 29 | wuyanzu | NULL | 19 | M | 4 | 1 |
| 30 | wuzetian | NULL | 21 | F | NULL | 1 |
| 31 | Song Jiang | NULL | 18 | M | 45 | 1 |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | 1 |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | 1 |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | 1 |
+-------+---------------+-------+-----+--------+---------+-----------+
34 rows in set (0.00 sec)
注意：安全模式下，這樣修改表必須有主鍵才可以修改，
添加主鍵方法：
MariaDB [hellodb]> alter table n1 change stuid stuid int primary key;
- DELETE陳述句：（也要加where） （-U 和組態檔修改都支持）
MariaDB [hellodb]> delete from n1 where stuid=28; （洗掉這一行）
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from n1;
+-------+---------------+-------+-----+--------+---------+-----------+
| stuid | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 24 | Xu Xian | NULL | 27 | M | NULL | 1 |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | 2 |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | 1 |
| 29 | wuyanzu | NULL | 19 | M | 4 | 1 |
| 30 | wuzetian | NULL | 21 | F | NULL | 1 |
| 31 | Song Jiang | NULL | 18 | M | 45 | 1 |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | 1 |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | 1 |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | 1 |
+-------+---------------+-------+-----+--------+---------+-----------+
33 rows in set (0.00 sec)
(第二種清空資料保留表結構的方式)
MariaDB [hellodb]> delete from n1;
Query OK, 33 rows affected (0.00 sec)
MariaDB [hellodb]> select * from n1;
Empty set (0.00 sec)
MariaDB [hellodb]> desc n1; （表結構還在）
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| stuid | int(11) | NO | PRI | NULL | |
| Name | varchar(50) | NO | | NULL | |
| phone | int(11) | YES | | NULL | |
| Age | tinyint(3) unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
DQL陳述句
查詢陳述句：SELECT （單表和多表）
單表操作
語法：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
說明：
- 欄位顯示可以使用別名： col1 AS alias1, col2 AS alias2, ...
- WHERE子句：指明過濾條件以實作“選擇”的功能： 過濾條件：布爾型運算式 算術運算子：+, -, *,
/, % 比較運算子：=,<=>（相等或都為空）, <>, !=(非標準SQL), >, >=, <, <= BETWEEN min_num
AND max_num IN (element1, element2, ...) IS NULL IS NOT NULL SELECT DISTINCT 去除重復
列，范例：SELECT DISTINCT gender FROM students; LIKE: % 任意長度的任意字符 _ 任意單個字
符 RLIKE：正則運算式，索引失效，不建議使用 REGEXP：匹配字串可用正則運算式書寫模式，
同上 邏輯運算子：NOT，AND，OR，XOR - GROUP：根據指定的條件把查詢結果進行“分組”以用于做“聚合”運算 常見聚合函式：avg(), max(),
min(), count(), sum() HAVING: 對分組聚合運算后的結果指定過濾條件 - ORDER BY: 根據指定的欄位對查詢結果進行排序 升序：ASC 降序：DESC
- LIMIT [[offset,]row_count]：對查詢的結果進行輸出行數數量限制
- 對查詢結果中的資料請求施加“鎖” FOR UPDATE: 寫鎖，獨占或排它鎖，只有一個讀和寫操作
LOCK IN SHARE MODE: 讀鎖，共享鎖，同時多個讀操作
范例：
TRUNCATE TABLE tbl_name;
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students
- 單表實驗：
- 展示表里的所有內容*代表所有欄位，也可以挑指定欄位來顯示，
MariaDB [hellodb]> select * from s1;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
.... 省略掉
+-------+---------------+-------+-----+--------+---------+-----------+
34 rows in set (0.00 sec)
- 甚至可以做數字運算
MariaDB [hellodb]> select 2*3;
+-----+
| 2*3 |
+-----+
| 6 |
+-----+
1 row in set (0.01 sec)
MariaDB [hellodb]> select 2*3 from teachers; (指定一個表，表里有幾行做幾次數字運算和AWK很相似)
+-----+
| 2*3 |
+-----+
| 6 |
| 6 |
| 6 |
| 6 |
+-----+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
- 指定欄位名更改 加*的話不行
MariaDB [hellodb]> select '韋小寶',name from teachers;
+-----------+---------------+
| 韋小寶 | name |
+-----------+---------------+
| 韋小寶 | Song Jiang |
| 韋小寶 | Zhang Sanfeng |
| 韋小寶 | Miejue Shitai |
| 韋小寶 | Lin Chaoying |
+-----------+---------------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> select '韋小寶',name,age,gender from teachers;
+-----------+---------------+-----+--------+
| 韋小寶 | name | age | gender |
+-----------+---------------+-----+--------+
| 韋小寶 | Song Jiang | 45 | M |
| 韋小寶 | Zhang Sanfeng | 94 | M |
| 韋小寶 | Miejue Shitai | 77 | F |
| 韋小寶 | Lin Chaoying | 93 | F |
+-----------+---------------+-----+--------+
4 rows in set (0.00 sec)
(只是顯示效果更改)
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
- 對表頭起別名顯示
MariaDB [hellodb]> select '韋小寶',name as "姓名",age "年齡",gender from teachers;
+-----------+---------------+--------+--------+
| 韋小寶 | 姓名 | 年齡 | gender |
+-----------+---------------+--------+--------+
| 韋小寶 | Song Jiang | 45 | M |
| 韋小寶 | Zhang Sanfeng | 94 | M |
| 韋小寶 | Miejue Shitai | 77 | F |
| 韋小寶 | Lin Chaoying | 93 | F |
+-----------+---------------+--------+--------+
4 rows in set (0.01 sec)
- 行過濾：挑出性別不是M的 !=是非標準的SQL語法 標準的為<>
MariaDB [hellodb]> select * from s1 where gender != 'M'; (只能在MySQL資料庫使用)
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
14 rows in set (0.00 sec)
MariaDB [hellodb]> select * from s1 where gender <> 'M'; （所有SQL資料庫都可以使用）
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
14 rows in set (0.00 sec)
- 挑出大于30的
MariaDB [hellodb]> select * from s1 where age >=30;
+-------+--------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+--------------+-------+-----+--------+---------+-----------+
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
+-------+--------------+-------+-----+--------+---------+-----------+
5 rows in set (0.00 sec)
- 挑出20到30之間的
MariaDB [hellodb]> select * from s1 where age >=20 and age <=30;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
15 rows in set (0.00 sec)
MariaDB [hellodb]> select * from s1 where age between 20 and 30; (結果等價于上面的)
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
15 rows in set (0.00 sec)
- 挑出大于30小于20的
MariaDB [hellodb]> select * from s1 where age >=30 or age <=20;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
22 rows in set (0.00 sec)
- 挑出1班，3班，6班的人
MariaDB [hellodb]> select * from s1 where classid in (1,3,6);
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
14 rows in set (0.00 sec)
- 查看班級id為空的
MariaDB [hellodb]> select * from s1 where classid is null;
+-------+-------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+-------------+-------+-----+--------+---------+-----------+
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
+-------+-------------+-------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
- 查看班級id不為空的
MariaDB [hellodb]> select * from s1 where classid is not null;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
31 rows in set (0.00 sec)
- 添加空值必須指定為null
MariaDB [hellodb]> insert students (name,age,gender,classid)values('abc',20,'M',null);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from s1 where classid is null;
+-------+-------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+-------------+-------+-----+--------+---------+-----------+
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 38 | abc | NULL | 20 | M | NULL | NULL |
+-------+-------------+-------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
- 去除calssid重復的列
MariaDB [hellodb]> select distinct classid from s1;
+---------+
| classid |
+---------+
| 2 |
| 1 |
| 4 |
| 3 |
| 5 |
| 7 |
| 6 |
| NULL |
| 45 |
| 94 |
| 77 |
| 93 |
+---------+
12 rows in set (0.00 sec)
- where陳述句模糊搜索匹配w開頭的行 （like）
MariaDB [hellodb]> select * from s1 where name like 'w%';
+-------+--------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+--------------+-------+-----+--------+---------+-----------+
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
+-------+--------------+-------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select * from s1 where name like '%w%'; (這個顯示為包含w的行)
+-------+--------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+--------------+-------+-----+--------+---------+-----------+
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
+-------+--------------+-------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
- 查看字串數量為三的：用三個下劃線來表示
MariaDB [hellodb]> select * from s1 where name like '___';
+-------+------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+------+-------+-----+--------+---------+-----------+
| 38 | abc | NULL | 20 | M | NULL | NULL |
+-------+------+-------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
- rlike '^#'等價于like '#%'
MariaDB [hellodb]> select * from s1 where name rlike '^x';
+-------+-------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+-------------+-------+-----+--------+---------+-----------+
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
+-------+-------------+-------+-----+--------+---------+-----------+
7 rows in set (0.00 sec)
MariaDB [hellodb]> select * from s1 where name like 'x%';
+-------+-------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+-------------+-------+-----+--------+---------+-----------+
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
+-------+-------------+-------+-----+--------+---------+-----------+
7 rows in set (0.00 sec)
- 建立一個表存盤用戶和密碼
MariaDB [hellodb]> create table user (id int primary key,name varchar(10) not null,password varchar(30) not null);
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> insert user value(1,'alice','centos');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> insert user value(2,'bob','linux');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from user;
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 1 | alice | centos |
| 2 | bob | linux |
+----+-------+----------+
2 rows in set (0.00 sec)
- 使用SQL注入（SQL注入漏洞）
MariaDB [hellodb]> select * from user where name='alice' and password='tuituibang'; (密碼不對應回傳空值)
Empty set (0.00 sec)
MariaDB [hellodb]> select * from user where name='alice'; -- ' and password='tuituibang'; （使用特殊符號密碼不對應也可以查看）
+----+-------+----------+
| id | name | password |
+----+-------+----------+
| 1 | alice | centos |
+----+-------+----------+
1 row in set (0.00 sec)
構建一個復雜密碼用戶結果也一樣
MariaDB [hellodb]> insert user values(3,'admin','p@ssw)rd!');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from user;
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | alice | centos |
| 2 | bob | linux |
| 3 | admin | p@ssw)rd! |
+----+-------+-----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select * from user where name='admin'; -- ' and password='tuituibang'; （這里的引號數量是單數）
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 3 | admin | p@ssw)rd! |
+----+-------+-----------+
1 row in set (0.00 sec)
在登陸的時候構建一個奇怪的密碼也可以登錄 （注意這里的引號數量必須是成對的）
MariaDB [hellodb]> select * from user where name='admin' and password='' or '1=1';
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | alice | centos |
| 2 | bob | linux |
| 3 | admin | p@ssw)rd! |
+----+-------+-----------+
3 rows in set, 5 warnings (0.00 sec)
MariaDB [hellodb]> select * from user where name='admin' and password='' or '1'='1';
+----+-------+-----------+
| id | name | password |
+----+-------+-----------+
| 1 | alice | centos |
| 2 | bob | linux |
| 3 | admin | p@ssw)rd! |
+----+-------+-----------+
3 rows in set (0.00 sec)
單表操作的分組統計
- SQL的聚合函式
AVG(column) 回傳某列的平均值
BINARY_CHECKSUM
CHECKSUM
CHECKSUM_AGG
COUNT(column) 回傳某列的行數（不包括NULL值）
COUNT(*) 回傳被選行數（函式回傳在給定的選擇中被選的行數）
COUNT(DISTINCT column) 回傳相異結果的數目
FIRST(column) 回傳在指定的域中第一個記錄的值（SQLServer2000 不支持）
LAST(column) 回傳在指定的域中最后一個記錄的值（SQLServer2000 不支持）
MAX(column) 回傳某列的最高值
MIN(column) 回傳某列的最低值
STDEV(column)
STDEVP(column)
SUM(column) 回傳某列的總和
VAR(column)
VARP(column)
- count 統計列的行數
MariaDB [hellodb]> select count(stuid) from s1;
+--------------+
| count(stuid) |
+--------------+
| 35 |
+--------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select count(classid) from s1;
+----------------+
| count(classid) |
+----------------+
| 31 | （有空值不統計）
+----------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select count(1) from s1; （跟count（主鍵）一樣，只掃描主鍵）
+----------+
| count(1) |
+----------+
| 35 |
+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select count(*) from s1; (和count（主鍵）使用方式一樣，但是在性能上有略微的區別，mysql對前者做了優化，)
+----------+
| count(*) |
+----------+
| 35 |
+----------+
1 row in set (0.00 sec)
- 使用GROUP BY （根據指定的條件把查詢結果進行“分組”以用于做“聚合”運算 常見聚合函式）統計男生女生分別多少個
MariaDB [hellodb]> select gender,count(*) from s1 group by gender ;
+--------+----------+
| gender | count(*) |
+--------+----------+
| F | 14 |
| M | 21 |
+--------+----------+
2 rows in set (0.00 sec)
(起個別名)
MariaDB [hellodb]> select gender 性別,count(*) 人數 from s1 group by gender ;
+--------+--------+
| 性別 | 人數 |
+--------+--------+
| F | 14 |
| M | 21 |
+--------+--------+
2 rows in set (0.00 sec)
- AVG 統計平均年齡
MariaDB [hellodb]> select gender,avg(age) from s1 group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| F | 18.9286 |
| M | 29.1905 |
+--------+----------+
2 rows in set (0.00 sec)
- max 統計男女生最大的年齡
MariaDB [hellodb]> select gender,avg(age),max(age) from s1 group by gender;
+--------+----------+----------+
| gender | avg(age) | max(age) |
+--------+----------+----------+
| F | 18.9286 | 22 |
| M | 29.1905 | 100 |
+--------+----------+----------+
2 rows in set (0.00 sec)
- 分組之后過濾行的話不能加where 要加having (在group by后面使用)
MariaDB [hellodb]> select classid,gender,avg(age) from s1 group by classid,gender;
+---------+--------+----------+ (過濾掉空的行)
| classid | gender | avg(age) |
+---------+--------+----------+
| NULL | F | 21.0000 |
| NULL | M | 49.0000 |
| 1 | F | 19.0000 |
| 1 | M | 21.5000 |
| 2 | M | 32.7500 |
| 3 | F | 18.3333 |
| 3 | M | 23.0000 |
| 4 | M | 23.6000 |
| 5 | M | 46.0000 |
| 6 | F | 20.0000 |
| 6 | M | 23.0000 |
| 7 | F | 18.0000 |
| 7 | M | 23.0000 |
| 45 | M | 18.0000 |
| 77 | F | 18.0000 |
| 93 | F | 18.0000 |
| 94 | M | 18.0000 |
+---------+--------+----------+
17 rows in set (0.00 sec)
MariaDB [hellodb]> select classid,gender,avg(age) from s1 group by classid,gender where classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where classid is not null' at line 1
MariaDB [hellodb]> select classid,gender,avg(age) from s1 group by classid,gender having classid is not null; （HAVING: 對分組聚合運算后的結果指定過濾條件）
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| 1 | F | 19.0000 |
| 1 | M | 21.5000 |
| 2 | M | 32.7500 |
| 3 | F | 18.3333 |
| 3 | M | 23.0000 |
| 4 | M | 23.6000 |
| 5 | M | 46.0000 |
| 6 | F | 20.0000 |
| 6 | M | 23.0000 |
| 7 | F | 18.0000 |
| 7 | M | 23.0000 |
| 45 | M | 18.0000 |
| 77 | F | 18.0000 |
| 93 | F | 18.0000 |
| 94 | M | 18.0000 |
+---------+--------+----------+
15 rows in set (0.00 sec)
- 分組前可以使用where過濾 （在group by之前添加）
MariaDB [hellodb]> select classid,gender,avg(age) from s1 where classid is not null group by classid,gender;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| 1 | F | 19.0000 |
| 1 | M | 21.5000 |
| 2 | M | 32.7500 |
| 3 | F | 18.3333 |
| 3 | M | 23.0000 |
| 4 | M | 23.6000 |
| 5 | M | 46.0000 |
| 6 | F | 20.0000 |
| 6 | M | 23.0000 |
| 7 | F | 18.0000 |
| 7 | M | 23.0000 |
| 45 | M | 18.0000 |
| 77 | F | 18.0000 |
| 93 | F | 18.0000 |
| 94 | M | 18.0000 |
+---------+--------+----------+
15 rows in set (0.00 sec)
- ORDER BY 排序 單欄位排序：默認按照字母正序排序
MariaDB [hellodb]> select * from s1 order by name;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 38 | abc | NULL | 20 | M | NULL | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
35 rows in set (0.00 sec)
- 倒序排序
MariaDB [hellodb]> select * from s1 order by name desc;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 38 | abc | NULL | 20 | M | NULL | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
35 rows in set (0.00 sec)
- 指定asc排序 （asc：以數字大小從小到大排序，空值默認為0排在最前面）
MariaDB [hellodb]> select * from s1 order by classid asc;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 38 | abc | NULL | 20 | M | NULL | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
35 rows in set (0.00 sec)
- 還是按照數字從小到大排序但是空值排在最后面
MariaDB [hellodb]> select * from s1 order by -classid desc;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 38 | abc | NULL | 20 | M | NULL | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
35 rows in set (0.00 sec)
- 多欄位排序 （先對性別排序在對年齡排序）
MariaDB [hellodb]> select * from s1 order by gender , age;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 38 | abc | NULL | 20 | M | NULL | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
35 rows in set (0.00 sec)
- 先對性別降序排序在對年齡正序排序，
MariaDB [hellodb]> select * from s1 order by gender desc,age asc;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 38 | abc | NULL | 20 | M | NULL | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 24 | Xu Xian | NULL | 27 | M | NULL | NULL |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 25 | Sun Dasheng | NULL | 100 | M | NULL | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 30 | wuzetian | NULL | 21 | F | NULL | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
35 rows in set (0.00 sec)
- 多欄位排序之后無法在做過濾 (但是排序前可以)
MariaDB [hellodb]> select * from s1 order by gender desc,age asc where classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where classid is not null' at line 1
MariaDB [hellodb]> select * from s1 order by gender desc,age asc having classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'having classid is not null' at line 1
MariaDB [hellodb]> select * from s1 order by gender desc,age asc on classid is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on classid is not null' at line 1
(先過濾在排序)
MariaDB [hellodb]> select * from s1 where classid is not null order by gender desc,age asc; （和group by 一樣還是在前面加）
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 28 | mahuateng | NULL | 20 | M | 3 | NULL |
| 16 | Xu Zhu | NULL | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | NULL | 22 | M | 2 | 3 |
| 2 | Shi Potian | NULL | 22 | M | 1 | 7 |
| 23 | Ma Chao | NULL | 23 | M | 4 | NULL |
| 26 | xietingfeng | NULL | 23 | M | 2 | 1 |
| 11 | Yuan Chengzhi | NULL | 23 | M | 6 | NULL |
| 18 | Hua Rong | NULL | 23 | M | 7 | NULL |
| 17 | Lin Chong | NULL | 25 | M | 4 | NULL |
| 5 | Yu Yutong | NULL | 26 | M | 3 | 1 |
| 4 | Ding Dian | NULL | 32 | M | 4 | 4 |
| 13 | Tian Boguang | NULL | 33 | M | 2 | NULL |
| 6 | Shi Qing | NULL | 46 | M | 5 | NULL |
| 3 | Xie Yanke | NULL | 53 | M | 2 | 16 |
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | NULL | 19 | F | 3 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 22 | Xiao Qiao | NULL | 20 | F | 1 | NULL |
| 9 | Ren Yingying | NULL | 20 | F | 6 | NULL |
| 21 | Huang Yueying | NULL | 22 | F | 6 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
31 rows in set (0.00 sec)
- 后面的分組欄位對調位置的話顯示效果不一樣但是資料還是一樣的，(排序邏輯不一樣)
MariaDB [hellodb]> select gender,classid,avg(age) from s1 where classid is not null group by gender,classid;
+--------+---------+----------+
| gender | classid | avg(age) |
+--------+---------+----------+
| F | 1 | 19.0000 |
| F | 3 | 18.3333 |
| F | 6 | 20.0000 |
| F | 7 | 18.0000 |
| F | 77 | 18.0000 |
| F | 93 | 18.0000 |
| M | 1 | 21.5000 |
| M | 2 | 32.7500 |
| M | 3 | 23.0000 |
| M | 4 | 23.6000 |
| M | 5 | 46.0000 |
| M | 6 | 23.0000 |
| M | 7 | 23.0000 |
| M | 45 | 18.0000 |
| M | 94 | 18.0000 |
+--------+---------+----------+
15 rows in set (0.00 sec)
MariaDB [hellodb]> select gender,classid,avg(age) from s1 where classid is not null group by classid,gender;
+--------+---------+----------+
| gender | classid | avg(age) |
+--------+---------+----------+
| F | 1 | 19.0000 |
| M | 1 | 21.5000 |
| M | 2 | 32.7500 |
| F | 3 | 18.3333 |
| M | 3 | 23.0000 |
| M | 4 | 23.6000 |
| M | 5 | 46.0000 |
| F | 6 | 20.0000 |
| M | 6 | 23.0000 |
| F | 7 | 18.0000 |
| M | 7 | 23.0000 |
| M | 45 | 18.0000 |
| F | 77 | 18.0000 |
| F | 93 | 18.0000 |
| M | 94 | 18.0000 |
+--------+---------+----------+
15 rows in set (0.00 sec)
- LIMIT [[offset,]row_count]：對查詢的結果進行輸出行數數量限制
正序排序取歲數最小的前10名 limit 作為限制條件
MariaDB [hellodb]> select * from s1 order by age limit 10;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 14 | Lu Wushuang | NULL | 17 | F | 3 | NULL |
| 8 | Lin Daiyu | NULL | 17 | F | 7 | NULL |
| 32 | Zhang Sanfeng | NULL | 18 | M | 94 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
- 跳過前三行取歲數最小的前10名
MariaDB [hellodb]> select * from s1 order by age limit 3,10;
+-------+---------------+-------+-----+--------+---------+-----------+
| StuID | Name | phone | Age | Gender | ClassID | TeacherID |
+-------+---------------+-------+-----+--------+---------+-----------+
| 33 | Miejue Shitai | NULL | 18 | F | 77 | NULL |
| 19 | Xue Baochai | NULL | 18 | F | 6 | NULL |
| 31 | Song Jiang | NULL | 18 | M | 45 | NULL |
| 27 | liudehua | NULL | 18 | F | 1 | NULL |
| 34 | Lin Chaoying | NULL | 18 | F | 93 | NULL |
| 20 | Diao Chan | NULL | 19 | F | 7 | NULL |
| 12 | Wen Qingqing | NULL | 19 | F | 1 | NULL |
| 15 | Duan Yu | NULL | 19 | M | 4 | NULL |
| 7 | Xi Ren | NULL | 19 | F | 3 | NULL |
| 29 | wuyanzu | NULL | 19 | M | 4 | NULL |
+-------+---------------+-------+-----+--------+---------+-----------+
10 rows in set (0.00 sec)
- 去重之后在做過濾 （這樣取出來的數值比較準確）
MariaDB [hellodb]> select distinct age from s1 order by age limit 3; (去重取前三個)
+-----+
| age |
+-----+
| 17 |
| 18 |
| 19 |
+-----+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select distinct age from s1 order by age limit 3,6; （去重跳過前3行取6行）
+-----+
| age |
+-----+
| 20 |
| 21 |
| 22 |
| 23 |
| 25 |
| 26 |
+-----+
6 rows in set (0.00 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/153330.html
標籤：Linux
下一篇：Linux：DHCP服務器的搭建