MySQL SQL语句基础

SQL语句基础知识的复习与学习

SQL简介

SQL是Structure Query Language(结构化查询语言)的缩写,使用关系数据模型的数据库应用语言。

SQL分类

SQL语句主要分为3类

DDL(Data Definition Language)数据定义语句

定义不同的数据段、数据库、数据表、列、索引等数据库对象的定义。常用关键字:create、drop、alter等。

DML(Data Manipulation Language)数据操纵语句

用于添加、删除、更新和查询数据库记录,并检查数据完整性,常用的语句关键词主要包括insert、delete、update和select等。

DCL(Data Control Language)数据控制语句

用于控制不同数据段直接的许可和访问级别的语句。定义了数据库、表、字段、用户的访问权限和安全级别,主要的关键字包括grant、revoke等。

DDL

DDL是数据定义语言的缩写,是对数据库内部的对象进行创建、删除、修改的操作语言。和DML语言的最大区别是DML只对表内部的操作,而不涉及表的定义、结构的修改,更不会涉及到其他对象。DDL语句更多被数据库管理员(DBA)所使用。

数据库创建

create database dbname;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

删除数据库

drop database dbname;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
MariaDB [information_schema]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [information_schema]> drop database test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [information_schema]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

创建表

create table tablename(column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints, … … , column_name_n column_type_n constraints)
MySQL的表名以目录形式存在磁盘上,表名的字符可以使用任何目录名允许的字符。column_name是列的名字,column_type是列的数据类型,contraints是列的约束条件。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
MariaDB [test]> create table student(name char(32), age int(4), sex int(2));
Query OK, 0 rows affected (0.04 sec)

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 |
+-------+----------+------+-----+---------+-------+
| name | char(32) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
| sex | int(2) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [test]> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`name` char(32) DEFAULT NULL,
`age` int(4) DEFAULT NULL,
`sex` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

删除表

drop table tablename;

1
2
3
4
5
MariaDB [test]> drop table student;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> show tables;
Empty set (0.00 sec)

修改表

表结构的修改一般使用alter table语句。

修改表类型

alter table tablename modify [column] column_definition [first | after col_name]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
MariaDB [test]> desc student;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(32) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
| sex | int(2) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
MariaDB [test]> alter table student modify age int(2);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(32) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| sex | int(2) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

新增表字段

alter table tablename add [column] column_definition [first | after col_name]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [test]> alter table student add birthday date;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> desc student;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| name | char(32) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| sex | int(2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除表字段

alter table tablename drop [column] column_name;

1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [test]> alter table student drop column birthday;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(32) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| sex | int(2) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

字段改名

alter table tablename change [column] old_col_name column_definition [first | after col_name]

1
2
3
4
5
6
7
8
9
10
11
12
13
MariaDB [test]> alter table student change sex grade int(4);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(32) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| grade | int(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)

change和modify都可以修改表定义,但change后面需要写两次列名,不方便。不过change的优点是可以修改列名称,modify则不能。

修改字段排列顺序

字段增加和修改(add/change/modify)中,都有可选项first|after column_name,该选项可以修改字段在表中的位置,默认add增加新字段加再表的最后位置,而change/modify默认不该表字段位置。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MariaDB [test]> alter table student add sex int(2) after name;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [test]> desc student;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(32) | YES | | NULL | |
| sex | int(2) | YES | | NULL | |
| age | int(2) | YES | | NULL | |
| grade | int(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

表改名

alter table tablename rename [to] new_tablename;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> alter table student rename to student_new;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| student_new |
+----------------+
1 row in set (0.00 sec)

DML

DML操作是指对数据库中表记录的操作,主要包括表记录的插入(insert)、更新(update)、删除(delete)和查询(select)。

插入记录

insert into tablename (field1, field2, ……, fieldn) values(value1, value2, ……, valuen);

1
2
3
4
5
6
7
8
9
10
MariaDB [test]> insert into student (name, sex, age) values("haha", 1, 10);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from student;
+------+------+------+-------+
| name | sex | age | grade |
+------+------+------+-------+
| haha | 1 | 10 | NULL |
+------+------+------+-------+
1 row in set (0.00 sec)

也可以不指定字段名称,但values的顺序要和字段排列顺序一致,且不能缺少任何一个。

1
2
3
4
5
6
7
8
9
10
11
MariaDB [test]> insert into student values("gaga", 2, 14, 1);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from student;
+------+------+------+-------+
| name | sex | age | grade |
+------+------+------+-------+
| haha | 1 | 10 | NULL |
| gaga | 2 | 14 | 1 |
+------+------+------+-------+
2 rows in set (0.00 sec)

在MySQL中insert语句可一次插入多条记录。在插入大量记录时,节省很多网络开销,大大提高插入效率。
insert into tablename (field1, field2, ……, fieldn)
values
(value1_1, value1_2, ……, value1_n),
(value2_1, value2_2, ……, value2_n),
… …
(valuen_1, valuen_2, ……, valuen_n);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [test]> insert into student (name, sex, grade) values
-> ("h1", 1, 3),
-> ("h3", 0, 4),
-> ("h5", 1, 5);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [test]> select * from student;
+------+------+------+-------+
| name | sex | age | grade |
+------+------+------+-------+
| haha | 1 | 10 | NULL |
| gaga | 2 | 14 | 1 |
| h1 | 1 | NULL | 3 |
| h3 | 0 | NULL | 4 |
| h5 | 1 | NULL | 5 |
+------+------+------+-------+
5 rows in set (0.00 sec)

更新记录

update tablename set field1=value1, field2=value2, ……, fieldn=valuen [where condition];

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [test]> update student set sex=0, grade=0 where name= 'haha';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [test]> select * from student;
+------+------+------+-------+
| name | sex | age | grade |
+------+------+------+-------+
| haha | 0 | 10 | 0 |
| gaga | 2 | 14 | 1 |
| h1 | 1 | NULL | 3 |
| h3 | 0 | NULL | 4 |
| h5 | 1 | NULL | 5 |
+------+------+------+-------+
5 rows in set (0.00 sec)

MySQL中update可以同时更新多个表中数据。
update t1, t2, ……, tn set t1.field1=expr1, t2.field2=expr2, ……, tn.fieldn=exprn [where condition];

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
MariaDB [test]> select * from home;
+------+--------+
| id | addr |
+------+--------+
| 1 | hhhhhh |
| 2 | jjjjj |
+------+--------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from student;
+------+------+------+-------+
| name | sex | age | grade |
+------+------+------+-------+
| haha | 0 | 10 | 0 |
| gaga | 2 | 14 | 1 |
| h1 | 1 | NULL | 3 |
| h3 | 0 | NULL | 4 |
| h5 | 1 | NULL | 5 |
+------+------+------+-------+
5 rows in set (0.00 sec)

MariaDB [test]> update student, home set student.grade=10, home.id=20 where student.name='haha';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

MariaDB [test]> select * from home;
+------+--------+
| id | addr |
+------+--------+
| 20 | hhhhhh |
| 20 | jjjjj |
+------+--------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from student;
+------+------+------+-------+
| name | sex | age | grade |
+------+------+------+-------+
| haha | 0 | 10 | 10 |
| gaga | 2 | 14 | 1 |
| h1 | 1 | NULL | 3 |
| h3 | 0 | NULL | 4 |
| h5 | 1 | NULL | 5 |
+------+------+------+-------+
5 rows in set (0.00 sec)

删除记录

delete from tablename [where condition];

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [test]> select * from student;
+------+------+
| name | age |
+------+------+
| haha | 1 |
| hehe | 2 |
| gaga | 3 |
+------+------+
3 rows in set (0.00 sec)

MariaDB [test]> delete from student where age=2;
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> select * from student;
+------+------+
| name | age |
+------+------+
| haha | 1 |
| gaga | 3 |
+------+------+
2 rows in set (0.00 sec)

delete t1,t2,…tn from t1,t2,…tn [where condition];
如果 from 后面的表名用别名,则 delete 后面的也要用相应的别名,否则会提示语法错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
MariaDB [test]> select * from boy;
+------+-------+
| name | score |
+------+-------+
| haha | 10 |
| gaga | 30 |
+------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from student;
+------+------+
| name | age |
+------+------+
| haha | 1 |
| gaga | 3 |
+------+------+
2 rows in set (0.00 sec)

MariaDB [test]> delete boy, s from boy, student s where s.age=1 and boy.name=s.name;
Query OK, 2 rows affected (0.00 sec)

MariaDB [test]> select * from student;
+------+------+
| name | age |
+------+------+
| gaga | 3 |
+------+------+
1 row in set (0.00 sec)

MariaDB [test]> select * from boy;
+------+-------+
| name | score |
+------+-------+
| gaga | 30 |
+------+-------+
1 row in set (0.00 sec)

查询记录

基本语法
select * from tablename [where condition];

查询不重复记录(去重)

使用distinct关键字实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MariaDB [test]> select age from student;
+------+
| age |
+------+
| 3 |
| 3 |
| 4 |
+------+
3 rows in set (0.00 sec)

MariaDB [test]> select distinct age from student;
+------+
| age |
+------+
| 3 |
| 4 |
+------+
2 rows in set (0.00 sec)

条件查询

使用where关键字实现

1
2
3
4
5
6
7
MariaDB [test]> select * from student where age=4;
+------+------+
| name | age |
+------+------+
| tete | 4 |
+------+------+
1 row in set (0.00 sec)

排序和限制

排序使用order by关键字实现

1
select * from tablename [where condition] [order by field1 [desc|asc] , field2 [desc|asc],......fieldn [desc|asc]]

desc 和 asc 是排序顺序关键字,desc表示按照字段进行降序排列,asc则表示升序排列,如果不写此关键字默认是升序排列。order by后面可以跟多个不同的排序字段,并 且每个排序字段可以有不同的排序顺序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
MariaDB [test]> select * from student;
+------+------+
| name | age |
+------+------+
| gaga | 3 |
| haha | 3 |
| tete | 4 |
| haha | 1 |
| tete | 2 |
+------+------+
5 rows in set (0.00 sec)

MariaDB [test]> select * from student order by age;
+------+------+
| name | age |
+------+------+
| haha | 1 |
| tete | 2 |
| gaga | 3 |
| haha | 3 |
| tete | 4 |
+------+------+
5 rows in set (0.00 sec)

MariaDB [test]> select * from student order by age , name desc;
+------+------+
| name | age |
+------+------+
| haha | 1 |
| tete | 2 |
| haha | 3 |
| gaga | 3 |
| tete | 4 |
+------+------+
5 rows in set (0.00 sec)

限制显示个数使用limit关键字

1
select ... ... [limit offset_start, row_count]

offset_start起始偏移量 row_count显示行数,默认情况下起始偏移量为0,只写显示行数,显示前n条记录。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
MariaDB [test]> select * from student limit 2;
+------+------+
| name | age |
+------+------+
| gaga | 3 |
| haha | 3 |
+------+------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from student limit 3, 2;
+------+------+
| name | age |
+------+------+
| haha | 1 |
| tete | 2 |
+------+------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from student;
+------+------+
| name | age |
+------+------+
| gaga | 3 |
| haha | 3 |
| tete | 4 |
| haha | 1 |
| tete | 2 |
+------+------+
5 rows in set (0.00 sec)

聚合

1
2
3
4
5
6
select [field1, field2, ...... fieldn] fun_name
from tablename
[where wehre_contition]
[group by field1, field2, ... fieldn]
[with rollup]
[having where_contition]

fun_name聚合操作(聚合函数),常用的有:sum(求和)、count(总个数)、max(最大值)、min(最小值)
group by关键字表示要进行分类聚合的字段
where rollup可选语法,表示是否对分类聚合的结果进行再汇总
having 关键字表示对分类后的结果再进行条件的过滤

having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤,而 where 是在聚合前就对记录进行过滤,如果逻辑允许,我们尽可能用 where 先过滤记录,这样因为结果集减小,将对聚合的效率大大提高,最后再根据逻辑看是否用 having 进行再过滤

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
MariaDB [test]> select * from student_score;
+------+--------+-------+-------+-------+
| name | shuxue | yuwen | yinyu | class |
+------+--------+-------+-------+-------+
| gaga | 1 | 3 | 4 | 1 |
| fef | 9 | 23 | 21 | 2 |
| ii | 45 | 123 | 42 | 1 |
| gr | 56 | 34 | 86 | 2 |
| ce | 45 | 23 | 98 | 2 |
| gr | 1 | 3 | 4 | 3 |
| khj | 9 | 23 | 21 | 4 |
| ko | 45 | 123 | 42 | 4 |
| po | 56 | 34 | 86 | 5 |
| ds | 45 | 23 | 98 | 3 |
+------+--------+-------+-------+-------+
10 rows in set (0.00 sec)

MariaDB [test]> select sum(shuxue), max(yinyu), min(yuwen) from student_score;
+-------------+------------+------------+
| sum(shuxue) | max(yinyu) | min(yuwen) |
+-------------+------------+------------+
| 312 | 98 | 3 |
+-------------+------------+------------+
1 row in set (0.00 sec)

MariaDB [test]> select class, sum(shuxue), sum(yinyu) from student_score group by class;
+-------+-------------+------------+
| class | sum(shuxue) | sum(yinyu) |
+-------+-------------+------------+
| 1 | 46 | 46 |
| 2 | 110 | 205 |
| 3 | 46 | 102 |
| 4 | 54 | 63 |
| 5 | 56 | 86 |
+-------+-------------+------------+
5 rows in set (0.00 sec)

MariaDB [test]> select class, sum(shuxue), sum(yinyu) from student_score group by class with rollup;
+-------+-------------+------------+
| class | sum(shuxue) | sum(yinyu) |
+-------+-------------+------------+
| 1 | 46 | 46 |
| 2 | 110 | 205 |
| 3 | 46 | 102 |
| 4 | 54 | 63 |
| 5 | 56 | 86 |
| NULL | 312 | 502 |
+-------+-------------+------------+
6 rows in set (0.00 sec)

MariaDB [test]> select class, sum(shuxue), sum(yinyu) from student_score group by class with rollup having sum(shuxue) > 50;
+-------+-------------+------------+
| class | sum(shuxue) | sum(yinyu) |
+-------+-------------+------------+
| 2 | 110 | 205 |
| 4 | 54 | 63 |
| 5 | 56 | 86 |
| NULL | 312 | 502 |
+-------+-------------+------------+
4 rows in set (0.00 sec)

表连接

表连接分为内连接和外连接,内连接仅选出两张表中互相存在的记录,而外连接会选出其他不匹配的记录。
外连接分为左连接和右连接

  1. 左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
  2. 右连接:包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    MariaDB [test]> select * from student;
    +------+------+
    | name | age |
    +------+------+
    | gaga | 3 |
    | haha | 3 |
    | tete | 4 |
    | haha | 1 |
    | tete | 2 |
    | fee | 2 |
    | ko | 3 |
    +------+------+
    7 rows in set (0.00 sec)

    MariaDB [test]> select * from student_score;
    +------+--------+-------+-------+-------+
    | name | shuxue | yuwen | yinyu | class |
    +------+--------+-------+-------+-------+
    | gaga | 1 | 3 | 4 | 1 |
    | fef | 9 | 23 | 21 | 2 |
    | ii | 45 | 123 | 42 | 1 |
    | gr | 56 | 34 | 86 | 2 |
    | ce | 45 | 23 | 98 | 2 |
    | gr | 1 | 3 | 4 | 3 |
    | khj | 9 | 23 | 21 | 4 |
    | ko | 45 | 123 | 42 | 4 |
    | po | 56 | 34 | 86 | 5 |
    | ds | 45 | 23 | 98 | 3 |
    +------+--------+-------+-------+-------+
    10 rows in set (0.00 sec)

    MariaDB [test]> select * from student, student_score where student.name=student_score.name;
    +------+------+------+--------+-------+-------+-------+
    | name | age | name | shuxue | yuwen | yinyu | class |
    +------+------+------+--------+-------+-------+-------+
    | gaga | 3 | gaga | 1 | 3 | 4 | 1 |
    | ko | 3 | ko | 45 | 123 | 42 | 4 |
    +------+------+------+--------+-------+-------+-------+
    2 rows in set (0.00 sec)

    MariaDB [test]> select * from student left join student_score on student.name=student_score.name;
    +------+------+------+--------+-------+-------+-------+
    | name | age | name | shuxue | yuwen | yinyu | class |
    +------+------+------+--------+-------+-------+-------+
    | gaga | 3 | gaga | 1 | 3 | 4 | 1 |
    | ko | 3 | ko | 45 | 123 | 42 | 4 |
    | haha | 3 | NULL | NULL | NULL | NULL | NULL |
    | tete | 4 | NULL | NULL | NULL | NULL | NULL |
    | haha | 1 | NULL | NULL | NULL | NULL | NULL |
    | tete | 2 | NULL | NULL | NULL | NUULL | NULL |
    | fee | 2 | NULL | NULL | NULL | NULL | NULL |
    +------+------+------+--------+-------+-------+-------+
    7 rows in set (0.00 sec)

    MariaDB [test]> select * from student right join student_score on student.name=student_score.name;
    +------+------+------+--------+-------+-------+-------+
    | name | age | name | shuxue | yuwen | yinyu | class |
    +------+------+------+--------+-------+-------+-------+
    | gaga | 3 | gaga | 1 | 3 | 4 | 1 |
    | ko | 3 | ko | 45 | 123 | 42 | 4 |
    | NULL | NULL | fef | 9 | 23 | 21 | 2 |
    | NULL | NULL | ii | 45 | 123 | 42 | 1 |
    | NULL | NULL | gr | 56 | 34 | 86 | 2 |
    | NULL | NULL | ce | 45 | 23 | 98 | 2 |
    | NULL | NULL | gr | 1 | 3 | 4 | 3 |
    | NULL | NULL | khj | 9 | 23 | 21 | 4 |
    | NULL | NULL | po | 56 | 34 | 86 | 5 |
    | NULL | NULL | ds | 45 | 23 | 98 | 3 |
    +------+------+------+--------+-------+-------+-------+
    10 rows in set (0.00 sec)

子查询

1
2
3
4
5
6
7
8
MariaDB [test]> select * from student where name in (select name from student_score);
+------+------+
| name | age |
+------+------+
| gaga | 3 |
| ko | 3 |
+------+------+
2 rows in set (0.00 sec)

某些情况下子查询和表连接可以相互转化

记录联合

union和union all用于实现记录联合

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
MariaDB [test]> select name from student union all select name from student_score;
+------+
| name |
+------+
| gaga |
| haha |
| tete |
| haha |
| tete |
| fee |
| ko |
| gaga |
| fef |
| ii |
| gr |
| ce |
| gr |
| khj |
| ko |
| po |
| ds |
+------+
17 rows in set (0.00 sec)

MariaDB [test]> select name from student union select name from student_score;
+------+
| name |
+------+
| gaga |
| haha |
| tete |
| fee |
| ko |
| fef |
| ii |
| gr |
| ce |
| khj |
| po |
| ds |
+------+
12 rows in set (0.00 sec)

union 和 union all的区别在于union all将结果集直接合并在一起,而union将union all的结果进行一次distinct,去除重复记录的结果。

DCL

DCL语句主要是DBA用来管理系统中的对象权限所使用的,一般开发人员很少使用。
grant和revoke分别授出和收回用户权限。