9.6. mysql入门¶
mariadb的特性
单进程，多线程
插件式存储引擎
开源
提供较多测试组件
9.6.1. 客户端工具¶
mysql命令的重要选项
- --print-default
默认选项
- --verbose
显示详细信息
- -u
指定用户
- -p
指定密码
- -h
服务器主机
Note
mysql中的用户是username@host构成的支持通配,%代表任意长度任意字符，-匹配任意单个字符。
9.6.2. 执行命令¶
查看当前用户
select user();
查看服务器版本
select version()
9.6.3. 数据库操作¶
创建数据库和删除数据库
create database t2;
drop database t2;
查看字符集和排序规则
show character set;
show collation;
show table status from mysql\G
查看数据库引擎
创建表
create table student(id int primary key AUTO_INCREMENT , age int unsigned ,name varchar(30), sex enum('m','f') default 'm');
MariaDB [test]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(10) unsigned | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | enum('m','f') | YES | | m | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [test]> create tables t2 select * from student;
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 'tables t2 select * from student' at line 1
MariaDB [test]> create table t2 select * from student;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> desc t2;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| age | int(10) unsigned | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | enum('m','f') | YES | | m | |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Note
上面可以发现通过select来去创建一个表会丢失主键和自动增长的。
表查看类
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)
MariaDB [test]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(10) unsigned | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | enum('m','f') | YES | | m | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [test]> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(10) unsigned DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`sex` enum('m','f') DEFAULT 'm',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
MariaDB [test]> show table status like 'student';
数据类型
tinyint
smallint
mediumint
int
bigint
float(m,d)
double(m,d)
decimal(m,d)
char(n)
varchar(n)
tinytext
text
mediumtext
longtext
binary(m)
varbinary(m)
enum
blob
date
time
datetime
timestamp
year(2)
year(4)
修饰符
null
not null
default
primary key
unique key
character set name
auto_increment
unsigned
字段修改
MariaDB [test]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(10) unsigned | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | enum('m','f') | YES | | m | |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [test]> alter table student add address varchar(100) after name;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> desc student;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(10) unsigned | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| address | varchar(100) | YES | | NULL | |
| sex | enum('m','f') | YES | | m | |
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
MariaDB [test]> alter table student drop address;
MariaDB [test]> alter table student change name stuname varchar(200);
MariaDB [test]> alter table student modify stuname varchar(300);
MariaDB [test]> desc student;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(10) unsigned | YES | | NULL | |
| stuname | varchar(300) | YES | | NULL | |
| sex | enum('m','f') | YES | | m | |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [test]> alter table student rename stu;
Query OK, 0 rows affected (0.00 sec)
索引
MariaDB [test]> create index index_age on stu(age);
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show index from stu;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| stu | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| stu | 1 | index_age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
MariaDB [test]> drop index index_age on stu;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
dml语句
MariaDB [test]> desc stu;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(10) unsigned | YES | | NULL | |
| stuname | varchar(300) | YES | | NULL | |
| sex | enum('m','f') | YES | | m | |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
MariaDB [test]> insert into stu(age,stuname) values(20,'zhao');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> insert into stu(age,stuname) values(21,'zhao2'),(22,'zhao3');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> insert into stu set age=23,stuname='zhao3';
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select * from stu;
+----+------+---------+------+
| id | age | stuname | sex |
+----+------+---------+------+
| 1 | 20 | zhao | m |
| 2 | 21 | zhao2 | m |
| 3 | 22 | zhao3 | m |
| 4 | 23 | zhao3 | m |
+----+------+---------+------+
4 rows in set (0.00 sec)
MariaDB [test]> update stu set sex='f' where id =4;
MariaDB [test]> update stu set sex='f' where id =4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> delete from stu where id=1;
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> select * from stu;
+----+------+---------+------+
| id | age | stuname | sex |
+----+------+---------+------+
| 2 | 21 | zhao2 | m |
| 3 | 22 | zhao3 | m |
| 4 | 23 | zhao3 | f |
+----+------+---------+------+
3 rows in set (0.00 sec)
dql语句
MariaDB [test]> select * from stu where age between 21 and 22;
+----+------+---------+------+
| id | age | stuname | sex |
+----+------+---------+------+
| 2 | 21 | zhao2 | m |
| 3 | 22 | zhao3 | m |
+----+------+---------+------+
2 rows in set (0.00 sec)
MariaDB [test]> select * from stu where stuname like 'zhao%';
+----+------+---------+------+
| id | age | stuname | sex |
+----+------+---------+------+
| 2 | 21 | zhao2 | m |
| 3 | 22 | zhao3 | m |
| 4 | 23 | zhao3 | f |
+----+------+---------+------+
3 rows in set (0.00 sec)
MariaDB [test]> select * from stu where stuname is not null;
+----+------+---------+------+
| id | age | stuname | sex |
+----+------+---------+------+
| 2 | 21 | zhao2 | m |
| 3 | 22 | zhao3 | m |
| 4 | 23 | zhao3 | f |
+----+------+---------+------+
3 rows in set (0.00 sec)
MariaDB [test]> select * from stu where age in (21,22);
+----+------+---------+------+
| id | age | stuname | sex |
+----+------+---------+------+
| 2 | 21 | zhao2 | m |
| 3 | 22 | zhao3 | m |
+----+------+---------+------+
2 rows in set (0.00 sec)
用户账号
MariaDB [test]> create user 'zhao'@'%' identified by 'oracle';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> drop user 'zhao'@'%';
MariaDB [test]> create user 'zhao'@'%' identified by 'oracle';
MariaDB [test]> set password for 'zhao'@'%' = password('zhao');
[root@102 ~]$ mysqladmin -u root -p password 'newpassword'
授权
MariaDB [test]> grant select ,delete on test.* to 'zhao'@'%' identified by 'oracle';
Query OK, 0 rows affected (0.37 sec)
MariaDB [test]> revoke delete on test.* from 'zhao'@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> show grants for 'zhao'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for zhao@% |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhao'@'%' IDENTIFIED BY PASSWORD '*2447D497B9A6A15F2776055CB2D1E9F86758182F' |
| GRANT SELECT ON `test`.* TO 'zhao'@'%' |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
为了后续的实验，我们导入一个文件，文件内容如下
1-- MySQL dump 10.13 Distrib 5.5.33, for Linux (x86_64)
2--
3-- Host: localhost Database: hellodb
4-- ------------------------------------------------------
5-- Server version 5.5.33-log
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Current Database: `hellodb`
20--
21
22CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;
23
24USE `hellodb`;
25
26--
27-- Table structure for table `classes`
28--
29
30DROP TABLE IF EXISTS `classes`;
31/*!40101 SET @saved_cs_client = @@character_set_client */;
32/*!40101 SET character_set_client = utf8 */;
33CREATE TABLE `classes` (
34 `ClassID` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
35 `Class` varchar(100) DEFAULT NULL,
36 `NumOfStu` smallint(5) unsigned DEFAULT NULL,
37 PRIMARY KEY (`ClassID`)
38) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
39/*!40101 SET character_set_client = @saved_cs_client */;
40
41--
42-- Dumping data for table `classes`
43--
44
45LOCK TABLES `classes` WRITE;
46/*!40000 ALTER TABLE `classes` DISABLE KEYS */;
47INSERT INTO `classes` VALUES (1,'Shaolin Pai',10),(2,'Emei Pai',7),(3,'QingCheng Pai',11),(4,'Wudang Pai',12),(5,'Riyue Shenjiao',31),(6,'Lianshan Pai',27),(7,'Ming Jiao',27),(8,'Xiaoyao Pai',15);
48/*!40000 ALTER TABLE `classes` ENABLE KEYS */;
49UNLOCK TABLES;
50
51--
52-- Table structure for table `coc`
53--
54
55DROP TABLE IF EXISTS `coc`;
56/*!40101 SET @saved_cs_client = @@character_set_client */;
57/*!40101 SET character_set_client = utf8 */;
58CREATE TABLE `coc` (
59 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
60 `ClassID` tinyint(3) unsigned NOT NULL,
61 `CourseID` smallint(5) unsigned DEFAULT NULL,
62 PRIMARY KEY (`ID`)
63) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
64/*!40101 SET character_set_client = @saved_cs_client */;
65
66--
67-- Dumping data for table `coc`
68--
69
70LOCK TABLES `coc` WRITE;
71/*!40000 ALTER TABLE `coc` DISABLE KEYS */;
72INSERT INTO `coc` VALUES (1,1,2),(2,1,5),(3,2,2),(4,2,6),(5,3,1),(6,3,7),(7,4,5),(8,4,2),(9,5,1),(10,5,9),(11,6,3),(12,6,4),(13,7,4),(14,7,3);
73/*!40000 ALTER TABLE `coc` ENABLE KEYS */;
74UNLOCK TABLES;
75
76--
77-- Table structure for table `courses`
78--
79
80DROP TABLE IF EXISTS `courses`;
81/*!40101 SET @saved_cs_client = @@character_set_client */;
82/*!40101 SET character_set_client = utf8 */;
83CREATE TABLE `courses` (
84 `CourseID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
85 `Course` varchar(100) NOT NULL,
86 PRIMARY KEY (`CourseID`)
87) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
88/*!40101 SET character_set_client = @saved_cs_client */;
89
90--
91-- Dumping data for table `courses`
92--
93
94LOCK TABLES `courses` WRITE;
95/*!40000 ALTER TABLE `courses` DISABLE KEYS */;
96INSERT INTO `courses` VALUES (1,'Hamo Gong'),(2,'Kuihua Baodian'),(3,'Jinshe Jianfa'),(4,'Taiji Quan'),(5,'Daiyu Zanghua'),(6,'Weituo Zhang'),(7,'Dagou Bangfa');
97/*!40000 ALTER TABLE `courses` ENABLE KEYS */;
98UNLOCK TABLES;
99
100--
101-- Table structure for table `scores`
102--
103
104DROP TABLE IF EXISTS `scores`;
105/*!40101 SET @saved_cs_client = @@character_set_client */;
106/*!40101 SET character_set_client = utf8 */;
107CREATE TABLE `scores` (
108 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
109 `StuID` int(10) unsigned NOT NULL,
110 `CourseID` smallint(5) unsigned NOT NULL,
111 `Score` tinyint(3) unsigned DEFAULT NULL,
112 PRIMARY KEY (`ID`)
113) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
114/*!40101 SET character_set_client = @saved_cs_client */;
115
116--
117-- Dumping data for table `scores`
118--
119
120LOCK TABLES `scores` WRITE;
121/*!40000 ALTER TABLE `scores` DISABLE KEYS */;
122INSERT INTO `scores` VALUES (1,1,2,77),(2,1,6,93),(3,2,2,47),(4,2,5,97),(5,3,2,88),(6,3,6,75),(7,4,5,71),(8,4,2,89),(9,5,1,39),(10,5,7,63),(11,6,1,96),(12,7,1,86),(13,7,7,83),(14,8,4,57),(15,8,3,93);
123/*!40000 ALTER TABLE `scores` ENABLE KEYS */;
124UNLOCK TABLES;
125
126--
127-- Table structure for table `students`
128--
129
130DROP TABLE IF EXISTS `students`;
131/*!40101 SET @saved_cs_client = @@character_set_client */;
132/*!40101 SET character_set_client = utf8 */;
133CREATE TABLE `students` (
134 `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
135 `Name` varchar(50) NOT NULL,
136 `Age` tinyint(3) unsigned NOT NULL,
137 `Gender` enum('F','M') NOT NULL,
138 `ClassID` tinyint(3) unsigned DEFAULT NULL,
139 `TeacherID` int(10) unsigned DEFAULT NULL,
140 PRIMARY KEY (`StuID`)
141) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
142/*!40101 SET character_set_client = @saved_cs_client */;
143
144--
145-- Dumping data for table `students`
146--
147
148LOCK TABLES `students` WRITE;
149/*!40000 ALTER TABLE `students` DISABLE KEYS */;
150INSERT INTO `students` VALUES (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);
151/*!40000 ALTER TABLE `students` ENABLE KEYS */;
152UNLOCK TABLES;
153
154--
155-- Table structure for table `teachers`
156--
157
158DROP TABLE IF EXISTS `teachers`;
159/*!40101 SET @saved_cs_client = @@character_set_client */;
160/*!40101 SET character_set_client = utf8 */;
161CREATE TABLE `teachers` (
162 `TID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
163 `Name` varchar(100) NOT NULL,
164 `Age` tinyint(3) unsigned NOT NULL,
165 `Gender` enum('F','M') DEFAULT NULL,
166 PRIMARY KEY (`TID`)
167) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
168/*!40101 SET character_set_client = @saved_cs_client */;
169
170--
171-- Dumping data for table `teachers`
172--
173
174LOCK TABLES `teachers` WRITE;
175/*!40000 ALTER TABLE `teachers` DISABLE KEYS */;
176INSERT INTO `teachers` VALUES (1,'Song Jiang',45,'M'),(2,'Zhang Sanfeng',94,'M'),(3,'Miejue Shitai',77,'F'),(4,'Lin Chaoying',93,'F');
177/*!40000 ALTER TABLE `teachers` ENABLE KEYS */;
178UNLOCK TABLES;
179
180--
181-- Table structure for table `toc`
182--
183
184DROP TABLE IF EXISTS `toc`;
185/*!40101 SET @saved_cs_client = @@character_set_client */;
186/*!40101 SET character_set_client = utf8 */;
187CREATE TABLE `toc` (
188 `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
189 `CourseID` smallint(5) unsigned DEFAULT NULL,
190 `TID` smallint(5) unsigned DEFAULT NULL,
191 PRIMARY KEY (`ID`)
192) ENGINE=InnoDB DEFAULT CHARSET=utf8;
193/*!40101 SET character_set_client = @saved_cs_client */;
194
195--
196-- Dumping data for table `toc`
197--
198
199LOCK TABLES `toc` WRITE;
200/*!40000 ALTER TABLE `toc` DISABLE KEYS */;
201/*!40000 ALTER TABLE `toc` ENABLE KEYS */;
202UNLOCK TABLES;
203/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
204
205/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
206/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
207/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
208/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
209/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
210/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
211/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
212
213-- Dump completed on 2013-09-03 2:51:27
排序
MariaDB [(none)]> use hellodb
Database changed
MariaDB [hellodb]> select * from students order by age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
限制行
MariaDB [hellodb]> select * from students order by age limit 2;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students order by age limit 2,4;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
+-------+--------------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
第二种情况是跳过2个取4个。
别名
MariaDB [hellodb]> select s.name as "姓名" , s.age as "年龄" from students as s order by age limit 2,4;
+--------------+--------+
| 姓名 | 年龄 |
+--------------+--------+
| Xue Baochai | 18 |
| Duan Yu | 19 |
| Wen Qingqing | 19 |
| Xi Ren | 19 |
+--------------+--------+
4 rows in set (0.00 sec)
模糊匹配
%通配任意字符任意次数
_通配单个字符
MariaDB [hellodb]> select * from students where name like 'S%'
-> ;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
4 rows in set (0.00 sec)
空值判断
MariaDB [hellodb]> select * from students where classid is null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students where classid is not null;
Warning
空值判断不能使用=。
分组统计
MariaDB [hellodb]> select courseid,avg(score) from scores group by courseid;
+----------+------------+
| courseid | avg(score) |
+----------+------------+
| 1 | 73.6667 |
| 2 | 75.2500 |
| 3 | 93.0000 |
| 4 | 57.0000 |
| 5 | 84.0000 |
| 6 | 84.0000 |
| 7 | 73.0000 |
+----------+------------+
7 rows in set (0.00 sec)
分组统计后过滤
MariaDB [hellodb]> select courseid,avg(score) from scores group by courseid having avg(score) > 80;
+----------+------------+
| courseid | avg(score) |
+----------+------------+
| 3 | 93.0000 |
| 5 | 84.0000 |
| 6 | 84.0000 |
+----------+------------+
3 rows in set (0.00 sec)
连接
MariaDB [hellodb]> select st.name, sc.score from students as st left outer join scores as sc on st.stuid=sc.stuid;
+---------------+-------+
| name | score |
+---------------+-------+
| Shi Zhongyu | 77 |
| Shi Zhongyu | 93 |
| Shi Potian | 47 |
| Shi Potian | 97 |
| Xie Yanke | 88 |
| Xie Yanke | 75 |
| Ding Dian | 71 |
| Ding Dian | 89 |
| Yu Yutong | 39 |
| Yu Yutong | 63 |
| Shi Qing | 96 |
| Xi Ren | 86 |
| Xi Ren | 83 |
| Lin Daiyu | 57 |
| Lin Daiyu | 93 |
| Ren Yingying | NULL |
| Yue Lingshan | NULL |
| Yuan Chengzhi | NULL |
| Wen Qingqing | NULL |
| Tian Boguang | NULL |
| Lu Wushuang | NULL |
| Duan Yu | NULL |
| Xu Zhu | NULL |
| Lin Chong | NULL |
| Hua Rong | NULL |
| Xue Baochai | NULL |
| Diao Chan | NULL |
| Huang Yueying | NULL |
| Xiao Qiao | NULL |
| Ma Chao | NULL |
| Xu Xian | NULL |
| Sun Dasheng | NULL |
+---------------+-------+
32 rows in set (0.05 sec)
MariaDB [hellodb]> select st.name, sc.score from students as st right outer join scores as sc on st.stuid=sc.stuid;
+-------------+-------+
| name | score |
+-------------+-------+
| Shi Zhongyu | 77 |
| Shi Zhongyu | 93 |
| Shi Potian | 47 |
| Shi Potian | 97 |
| Xie Yanke | 88 |
| Xie Yanke | 75 |
| Ding Dian | 71 |
| Ding Dian | 89 |
| Yu Yutong | 39 |
| Yu Yutong | 63 |
| Shi Qing | 96 |
| Xi Ren | 86 |
| Xi Ren | 83 |
| Lin Daiyu | 57 |
| Lin Daiyu | 93 |
+-------------+-------+
15 rows in set (0.00 sec)
MariaDB [hellodb]> select st.name, sc.score from students as st inner join scores as sc on st.stuid=sc.stuid;
+-------------+-------+
| name | score |
+-------------+-------+
| Shi Zhongyu | 77 |
| Shi Zhongyu | 93 |
| Shi Potian | 47 |
| Shi Potian | 97 |
| Xie Yanke | 88 |
| Xie Yanke | 75 |
| Ding Dian | 71 |
| Ding Dian | 89 |
| Yu Yutong | 39 |
| Yu Yutong | 63 |
| Shi Qing | 96 |
| Xi Ren | 86 |
| Xi Ren | 83 |
| Lin Daiyu | 57 |
| Lin Daiyu | 93 |
+-------------+-------+
15 rows in set (0.00 sec)
嵌套查询
MariaDB [hellodb]> select * from students where age > (select age from students where name='Xu Xian');
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.03 sec)