mysql数据库三章_MySQL篇，第三章：数据库知识3

最新推荐文章于 2022-09-22 12:02:59 发布

河岸的翁

最新推荐文章于 2022-09-22 12:02:59 发布

阅读量230

点赞数

文章标签： mysql数据库三章

本文链接：https://blog.csdn.net/weixin_35383324/article/details/113269978

版权

MySQL 数据库 3

索引

1、普通索引(MUL)

2、唯一索引(UNI)

3、主键索引(PRI)

1、使用规则

1、一个表中只能有一个主键(primary)字段

2、对应字段的值不允许重复,且不能为空

3、主键字段的key标志PRI

4、把表中能够唯一标识一条记录的字段设置为主键,通常把表中记录编号的字段设置为主键

2、创建主键(primary key)

1、创建表时创建

1、字段名数据类型 primary key,

2、primary key(字段名)

2、在已有表中创建

alter table 表名 add primary key(字段名);

3、删除

alter table 表名 drop primary key;

1 mysql>use db32 Database changed3 mysql>create table t1(4 ->id int primary key,5 -> name varchar(15) notnull,6 -> sex enum('boy','girl') default 'boy'

7 -> )default charset=utf8;8 Query OK, 0 rows affected (1.44sec)9

10 mysql>desc t1;11 +-------+--------------------+------+-----+---------+-------+

13 +-------+--------------------+------+-----+---------+-------+

14 | id | int(11) | NO | PRI | NULL | |

16 | sex | enum('boy','girl') | YES | | boy | |

17 +-------+--------------------+------+-----+---------+-------+

18 3 rows in set (0.13sec)19

20 mysql> insert into t1 values(1,'zhangsanfeng','boy');21 Query OK, 1 row affected (0.12sec)22

23 mysql> insert into t1 values(1,'zhangwuji','boy');24 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

25 mysql>以上第一种方式\c26 mysql>

27 mysql>

28 mysql>create table t2(29 ->id int,30 -> name char(20),31 -> likes set('boy','girl','study'),32 ->primary key(id)33 ->);34 Query OK, 0 rows affected (0.22sec)35

36 mysql>desc t2;37 +-------+---------------------------+------+-----+---------+-------+

39 +-------+---------------------------+------+-----+---------+-------+

40 | id | int(11) | NO | PRI | NULL | |

43 +-------+---------------------------+------+-----+---------+-------+

44 3 rows in set (0.05sec)45

46 mysql>以上第２种方式\c47 mysql>

48 mysql>primary key 主键索引\c49 mysql>

50 mysql>alter table t2 drop primary key;51 Query OK, 0 rows affected (0.89sec)52 Records: 0 Duplicates: 0 Warnings: 053

54 mysql>desc t2;55 +-------+---------------------------+------+-----+---------+-------+

57 +-------+---------------------------+------+-----+---------+-------+

58 | id | int(11) | NO | | NULL | |

61 +-------+---------------------------+------+-----+---------+-------+

62 3 rows in set (0.08sec)63

64 mysql>alter table t2 add primary key(id);65 Query OK, 0 rows affected (0.52sec)66 Records: 0 Duplicates: 0 Warnings: 067

68 mysql>desc t2;69 +-------+---------------------------+------+-----+---------+-------+

71 +-------+---------------------------+------+-----+---------+-------+

72 | id | int(11) | NO | PRI | NULL | |

75 +-------+---------------------------+------+-----+---------+-------+

76 3 rows in set (0.00sec)77

78 mysql>

View Code

4、自增长属性(auto_increment)

1、作用：通常和主键字段一起配合使用

2、创建

1、创建表时创建

字段名数据类型 primary key auto_increment

2、在已有表中添加自增长属性(modify)

alter table 表名 modify 字段名数据类型 primary key auto_increment

5、删除主键及自增长属性 (注：先删除自增长属性再删除主键 )

1、alter table 表名 modify 字段名数据类型;

2、alter table 表名 drop primay key;

1 mysql>

2 mysql> create tablet3(3 -> id int primary keyauto_increment,4 -> name char(15),5 -> age tinyintunsigned6 ->);7 Query OK, 0 rows affected (0.29sec)8

9 mysql> desct3;10 +-------+---------------------+------+-----+---------+----------------+

12 +-------+---------------------+------+-----+---------+----------------+

13 | id | int(11) | NO | PRI | NULL | auto_increment |

15 | age | tinyint(3) unsigned | YES | | NULL | |

16 +-------+---------------------+------+-----+---------+----------------+

17 3 rows in set (0.06sec)18

19 mysql>

20 mysql>

21 mysql> insert into t3 values(0,'赵敏',30);22 ERROR 1366 (HY000): Incorrect string value: '\xE8\xB5\xB5\xE6\x95\x8F' for column 'name' at row 1

23 mysql> insert into t3 values(0,'zhaomin',30);24 Query OK, 1 row affected (0.04sec)25

26 mysql> insert into t3 values(0,'xiaozhao',30)27 ->;28 Query OK, 1 row affected (0.07sec)29

30 mysql> insert into t3 values(0,'zhouziruo',25);31 Query OK, 1 row affected (0.02sec)32

33 mysql> select * fromt3;34 +----+-----------+------+

35 | id | name | age |

36 +----+-----------+------+

37 | 1 | zhaomin | 30 |

38 | 2 | xiaozhao | 30 |

39 | 3 | zhouziruo | 25 |

40 +----+-----------+------+

41 3 rows in set (0.00sec)42

43 mysql> delect from t3 where id=3;44 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delect from t3 where id=3' at line 1

45 mysql> delete from t3 where id=3;46 Query OK, 1 row affected (0.22sec)47

48 mysql> select * fromt3;49 +----+----------+------+

50 | id | name | age |

51 +----+----------+------+

52 | 1 | zhaomin | 30 |

53 | 2 | xiaozhao | 30 |

54 +----+----------+------+

55 2 rows in set (0.00sec)56

57 mysql> insert into t3 values(0,'zhangwuji',28);58 Query OK, 1 row affected (0.00sec)59

60 mysql> select * fromt3;61 +----+-----------+------+

62 | id | name | age |

63 +----+-----------+------+

64 | 1 | zhaomin | 30 |

65 | 2 | xiaozhao | 30 |

66 | 4 | zhangwuji | 28 |

67 +----+-----------+------+

68 3 rows in set (0.00sec)69

70 mysql> desct3;71 +-------+---------------------+------+-----+---------+----------------+

73 +-------+---------------------+------+-----+---------+----------------+

74 | id | int(11) | NO | PRI | NULL | auto_increment |

76 | age | tinyint(3) unsigned | YES | | NULL | |

77 +-------+---------------------+------+-----+---------+----------------+

78 3 rows in set (0.00sec)79

80 mysql>

View Code

4、外键索引(foreign key)

1、定义

让当前表的字段值在另一个表的范围内选择

2、语法格式

foreign key(参考字段名)

references 被参考表名(被参考字段名)

on delete 级联动作

on update 级联动作

3、案例

表1：缴费信息表(财务)

学号姓名班级缴费金额

1 唐伯虎 AID1712 28000

2 点秋香 AID1712 20000

表2：学生信息表(班主任)

学号姓名缴费金额

1 唐伯虎 28000

1 mysql> usedb3;2 Reading table information for completion of table and columnnames3 You can turn off this feature to get a quicker startup with -A4

5 Databasechanged6 mysql>

7 mysql> create tablejftab(8 -> id int primary key,9 -> name char(20),10 -> class varchar(7),11 -> money int

12 -> ) default charset=utf8;13 Query OK, 0 rows affected (0.28sec)14

15 mysql> descjftab;16 +-------+------------+------+-----+---------+-------+

18 +-------+------------+------+-----+---------+-------+

19 | id | int(11) | NO | PRI | NULL | |

22 | money | int(11) | YES | | NULL | |

23 +-------+------------+------+-----+---------+-------+

24 4 rows in set (0.05sec)25

26 mysql> insert into jftab values

27 -> (1,'唐伯虎','AID1806',28000),28 -> (2,'点秋香','AID1806',20000),29 -> (3,'祝枝山','AID1806',25000);30 Query OK, 3 rows affected (0.18sec)31 Records: 3 Duplicates: 0 Warnings: 0

33 mysql> select * fromjftab;34 +----+-----------+---------+-------+

36 +----+-----------+---------+-------+

37 | 1 | 唐伯虎 | AID1806 | 28000 |

38 | 2 | 点秋香 | AID1806 | 20000 |

39 | 3 | 祝枝山 | AID1806 | 25000 |

40 +----+-----------+---------+-------+

41 3 rows in set (0.00sec)42

43 mysql> create tablebjtab(44 -> stu_id int,45 -> name varchar(20),46 -> money int,47 -> foreign key(stu_id) referencesjftab(id)48 -> on delete cascade

49 -> on update cascade

50 -> )default charset=utf8;51 Query OK, 0 rows affected (0.44sec)52

53 mysql> select * fromjftab;54 +----+-----------+---------+-------+

56 +----+-----------+---------+-------+

57 | 1 | 唐伯虎 | AID1806 | 28000 |

58 | 2 | 点秋香 | AID1806 | 20000 |

59 | 3 | 祝枝山 | AID1806 | 25000 |

60 +----+-----------+---------+-------+

61 3 rows in set (0.00sec)62

63 mysql> descbjtab;64 +--------+-------------+------+-----+---------+-------+

66 +--------+-------------+------+-----+---------+-------+

67 | stu_id | int(11) | YES | MUL | NULL | |

69 | money | int(11) | YES | | NULL | |

70 +--------+-------------+------+-----+---------+-------+

71 3 rows in set (0.03sec)72

73 mysql> insert into bjtab values

74 -> (1,'唐伯虎',28000),75 -> (2,'点秋香',20000);76 Query OK, 2 rows affected (0.10sec)77 Records: 2 Duplicates: 0 Warnings: 0

79 mysql> select * frombjtab;80 +--------+-----------+-------+

81 | stu_id | name | money |

82 +--------+-----------+-------+

83 | 1 | 唐伯虎 | 28000 |

84 | 2 | 点秋香 | 20000 |

85 +--------+-----------+-------+

86 2 rows in set (0.00sec)87

88 mysql> select * fromjftab;89 +----+-----------+---------+-------+

91 +----+-----------+---------+-------+

92 | 1 | 唐伯虎 | AID1806 | 28000 |

93 | 2 | 点秋香 | AID1806 | 20000 |

94 | 3 | 祝枝山 | AID1806 | 25000 |

95 +----+-----------+---------+-------+

96 3 rows in set (0.00sec)97

98 mysql> insert into bjtab values

99 -> (4,'文征明',23000);100 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db3`.`bjtab`, CONSTRAINT `bjtab_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `jftab` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)101 mysql>

102 mysql> delete from jftab where name='点秋香';103 Query OK, 1 row affected (0.11sec)104

105 mysql> select * fromjftab;106 +----+-----------+---------+-------+

108 +----+-----------+---------+-------+

109 | 1 | 唐伯虎 | AID1806 | 28000 |

110 | 3 | 祝枝山 | AID1806 | 25000 |

111 +----+-----------+---------+-------+

112 2 rows in set (0.01sec)113

114 mysql> select * frombjtab;115 +--------+-----------+-------+

116 | stu_id | name | money |

117 +--------+-----------+-------+

118 | 1 | 唐伯虎 | 28000 |

119 +--------+-----------+-------+

120 1 row in set (0.00sec)121

122 mysql> update jftab set id=8 where id=1;123 Query OK, 1 row affected (0.15sec)124 Rows matched: 1 Changed: 1 Warnings: 0

125

126 mysql> select * frombjtab;127 +--------+-----------+-------+

128 | stu_id | name | money |

129 +--------+-----------+-------+

130 | 8 | 唐伯虎 | 28000 |

131 +--------+-----------+-------+

132 1 row in set (0.00sec)133

134 mysql> select * fromjftab;135 +----+-----------+---------+-------+

137 +----+-----------+---------+-------+

138 | 3 | 祝枝山 | AID1806 | 25000 |

139 | 8 | 唐伯虎 | AID1806 | 28000 |

140 +----+-----------+---------+-------+

141 2 rows in set (0.00sec)142

143 mysql>

View Code

4、删除外键

1、语法格式

alter table 表名 drop foreign key 外键名;

2、注意

1、外键名的查看方式

show create table 表名;

1 mysql>

2 mysql> show create tablebjtab;3 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

4 | Table | Create Table |

5 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

6 | bjtab | CREATE TABLE`bjtab` (7 `stu_id` int(11) DEFAULT NULL,8 `name` varchar(20) DEFAULT NULL,9 `money` int(11) DEFAULT NULL,10 KEY`stu_id` (`stu_id`),11 CONSTRAINT `bjtab_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `jftab` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

12 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

13 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

14 1 row in set (0.05sec)15

16 mysql> alter table bjtab drop foreign keybjtab_ibfk_1;17 Query OK, 0 rows affected (0.07sec)18 Records: 0 Duplicates: 0 Warnings: 0

20 mysql> show create tablebjtab;21 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

22 | Table | Create Table |

23 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

24 | bjtab | CREATE TABLE`bjtab` (25 `stu_id` int(11) DEFAULT NULL,26 `name` varchar(20) DEFAULT NULL,27 `money` int(11) DEFAULT NULL,28 KEY`stu_id` (`stu_id`)29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

30 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

31 1 row in set (0.00sec)32

33 mysql> insert into bjtab values(10,'xiaoxiao',28);34 Query OK, 1 row affected (0.00sec)35

36 mysql> select * frombjtab;37 +--------+-----------+-------+

38 | stu_id | name | money |

39 +--------+-----------+-------+

40 | 8 | 唐伯虎 | 28000 |

41 | 10 | xiaoxiao | 28 |

42 +--------+-----------+-------+

43 2 rows in set (0.00sec)44

45 mysql> delete from bjtab where stu_id=10;46 Query OK, 1 row affected (0.00sec)47

48 mysql> select * frombjtab;49 +--------+-----------+-------+

50 | stu_id | name | money |

51 +--------+-----------+-------+

52 | 8 | 唐伯虎 | 28000 |

53 +--------+-----------+-------+

54 1 row in set (0.00sec)55

56 mysql>

57 mysql> show create tablebjtab;58 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

59 | Table | Create Table |

60 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

61 | bjtab | CREATE TABLE`bjtab` (62 `stu_id` int(11) DEFAULT NULL,63 `name` varchar(20) DEFAULT NULL,64 `money` int(11) DEFAULT NULL,65 KEY`stu_id` (`stu_id`)66 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

67 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

68 1 row in set (0.00sec)69

70 mysql>

View Code

5、在已有表中添加外键

1、语法格式

alter table 表名 add

foreign key(参考字段名) references

被参考表名(被参考字段名)

on delete 级联动作

on update 级联动作

2、注意

在已有表中添加外键时,会受到表中原有数据的限制

6、级联动作

1、cascade ：数据级联更新

1、当主表删除记录时,如果从表有相关联记录则级联删除

2、当主表更新被参考字段的值时,从表级联更新参考字段的值

2、restrict(默认)

1、当主表删除记录时,如果从表中有相关联记录则不允许主表删除

2、update同 1

3、set null

1、当主表删除记录时,从表中相关联记录外键字段值变为null

2、update 同 1

4、no action

同 restrict,都是立即检查外键限制

7、使用规则

1、两张表被参考字段和参考字段的数据类型要一致

2、被参考字段必须是KEY的一种,通常是primary key

1 mysql>use db3;2 Reading table information for completion of table andcolumn names3 You can turn off this feature to get a quicker startup with -A4

5 Database changed6 mysql>show tables;7 +---------------+

8 | Tables_in_db3 |

9 +---------------+

10 | bjtab |

11 | jftab |

12 | t1 |

13 | t2 |

14 | t3 |

15 +---------------+

16 5 rows in set (0.02sec)17

18 mysql>alter table bjtab add19 ->foreign key(stu_id) references jftab(id)20 ->;21 Query OK, 1 row affected (3.94sec)22 Records: 1Duplicates: 0 Warnings: 023

24 mysql> select * frombjtab;25 +--------+-----------+-------+

26 | stu_id | name | money |

27 +--------+-----------+-------+

28 | 8 | 唐伯虎 | 28000 |

29 +--------+-----------+-------+

30 1 row in set (0.00sec)31

32 mysql>desc bjtab;33 +--------+-------------+------+-----+---------+-------+

35 +--------+-------------+------+-----+---------+-------+

36 | stu_id | int(11) | YES | MUL | NULL | |

38 | money | int(11) | YES | | NULL | |

39 +--------+-------------+------+-----+---------+-------+

40 3 rows in set (0.09sec)41

42 mysql> select * fromjftab;43 +----+-----------+---------+-------+

45 +----+-----------+---------+-------+

46 | 3 | 祝枝山 | AID1806 | 25000 |

47 | 8 | 唐伯虎 | AID1806 | 28000 |

48 +----+-----------+---------+-------+

49 2 rows in set (0.00sec)50

51 mysql> delete from jftab where name='唐伯虎';52 ERROR 1451 (23000): Cannot delete orupdate a parent row: a foreign key constraint fails (`db3`.`bjtab`, CONSTRAINT `bjtab_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `jftab` (`id`))53 mysql> update jftab set id=10 where name='唐伯虎';54 ERROR 1451 (23000): Cannot delete orupdate a parent row: a foreign key constraint fails (`db3`.`bjtab`, CONSTRAINT `bjtab_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `jftab` (`id`))55 mysql>

56 mysql>

57 mysql>show create table bjtab;58 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

59 | Table | Create Table |

61 | bjtab |CREATE TABLE `bjtab` (62 `stu_id` int(11) DEFAULT NULL,63 `name` varchar(20) DEFAULT NULL,64 `money` int(11) DEFAULT NULL,65 KEY `stu_id` (`stu_id`),66 CONSTRAINT `bjtab_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `jftab` (`id`)67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

68 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

69 1 row in set (0.05sec)70

71 mysql>alter table bjtab drop foreign key bjtab_ibfk_1 ;72 Query OK, 0 rows affected (0.16sec)73 Records: 0 Duplicates: 0 Warnings: 074

75 mysql>desc bjtab;76 +--------+-------------+------+-----+---------+-------+

78 +--------+-------------+------+-----+---------+-------+

79 | stu_id | int(11) | YES | MUL | NULL | |

81 | money | int(11) | YES | | NULL | |

82 +--------+-------------+------+-----+---------+-------+

83 3 rows in set (0.05sec)84

85 mysql>desc jftab;86 +-------+------------+------+-----+---------+-------+

88 +-------+------------+------+-----+---------+-------+

89 | id | int(11) | NO | PRI | NULL | |

92 | money | int(11) | YES | | NULL | |

93 +-------+------------+------+-----+---------+-------+

94 4 rows in set (0.00sec)95

96 mysql>alter table bjtab97 ->add foreign key(stu_id) references jftab(id)98 ->on delete set null99 ->on update set null100 ->;101 Query OK, 1 row affected (0.30sec)102 Records: 1Duplicates: 0 Warnings: 0103

104 mysql>desc bjtab;105 +--------+-------------+------+-----+---------+-------+

107 +--------+-------------+------+-----+---------+-------+

108 | stu_id | int(11) | YES | MUL | NULL | |

110 | money | int(11) | YES | | NULL | |

111 +--------+-------------+------+-----+---------+-------+

112 3 rows in set (0.00sec)113

114 mysql>

115 mysql> select * fromjftab;116 +----+-----------+---------+-------+

118 +----+-----------+---------+-------+

119 | 3 | 祝枝山 | AID1806 | 25000 |

120 | 8 | 唐伯虎 | AID1806 | 28000 |

121 +----+-----------+---------+-------+

122 2 rows in set (0.00sec)123

124 mysql> select * frombjtab;125 +--------+-----------+-------+

126 | stu_id | name | money |

127 +--------+-----------+-------+

128 | 8 | 唐伯虎 | 28000 |

129 +--------+-----------+-------+

130 1 row in set (0.00sec)131

132 mysql> delete from jftab where name='唐伯虎';133 Query OK, 1 row affected (0.04sec)134

135 mysql> select * fromjftab;136 +----+-----------+---------+-------+

138 +----+-----------+---------+-------+

139 | 3 | 祝枝山 | AID1806 | 25000 |

140 +----+-----------+---------+-------+

141 1 row in set (0.01sec)142

143 mysql> select * frombjtab;144 +--------+-----------+-------+

145 | stu_id | name | money |

146 +--------+-----------+-------+

147 | NULL | 唐伯虎 | 28000 |

148 +--------+-----------+-------+

149 1 row in set (0.00sec)150

151 mysql>show tables;152 +---------------+

153 | Tables_in_db3 |

154 +---------------+

155 | bjtab |

156 | jftab |

157 | t1 |

158 | t2 |

159 | t3 |

160 +---------------+

161 5 rows in set (0.00sec)162

163 mysql>desc t3;164 +-------+---------------------+------+-----+---------+----------------+

166 +-------+---------------------+------+-----+---------+----------------+

167 | id | int(11) | NO | PRI | NULL | auto_increment |

169 | age | tinyint(3) unsigned | YES | | NULL | |

170 +-------+---------------------+------+-----+---------+----------------+

171 3 rows in set (0.01sec)172

173 mysql>alter table t3 modify id int;174 Query OK, 3 rows affected (0.90sec)175 Records: 3Duplicates: 0 Warnings: 0176

177 mysql>desc t3;178 +-------+---------------------+------+-----+---------+-------+

180 +-------+---------------------+------+-----+---------+-------+

181 | id | int(11) | NO | PRI | NULL | |

183 | age | tinyint(3) unsigned | YES | | NULL | |

184 +-------+---------------------+------+-----+---------+-------+

185 3 rows in set (0.00sec)186

187 mysql>alter table t3 drop primary key;188 Query OK, 3 rows affected (0.64sec)189 Records: 3Duplicates: 0 Warnings: 0190

191 mysql>desc t3;192 +-------+---------------------+------+-----+---------+-------+

194 +-------+---------------------+------+-----+---------+-------+

195 | id | int(11) | NO | | NULL | |

197 | age | tinyint(3) unsigned | YES | | NULL | |

198 +-------+---------------------+------+-----+---------+-------+

199 3 rows in set (0.06sec)200

201 mysql>

View Code

数据导入

1、作用：将文件系统的内容导入到数据库中

2、语法格式

load data infile "文件名"

into table 表名

fields terminated by "分隔符"

lines terminated by "分隔符"

tarena:x:1000:1000:tarena,,,:/home/tarena:/bin/bash

用户名:密码:UID:GID:描述:家目录:登录权限

3、把/etc/passwd 导入到mysql数据库中

4、操作步骤

1、在数据库中创建对应的表

2、查看数据库的默认搜索路径

show variables like "secure_file_priv";

3、将系统文件拷贝到数据库的默认搜索路径中

sudo cp /etc/passwd /var/lib/mysql-files

sudo -i

cd /var/lib/mysql-files

exit

1 mysql>

2 mysql> create tableuserinfo(3 -> username char(20),4 -> password char(1),5 -> uid int,6 -> gid int,7 -> comment varchar(50),8 -> homedir varchar(50),9 -> shell varchar(50)10 ->);11 Query OK, 0 rows affected (0.25sec)12

13 mysql>

14 mysql> show variables like 'secure_file_priv';15 +------------------+-----------------------+

16 | Variable_name | Value |

17 +------------------+-----------------------+

18 | secure_file_priv | /var/lib/mysql-files/ |

19 +------------------+-----------------------+

20 1 row in set (0.65sec)21

22 mysql>

23 mysql>

24 mysql>

25 mysql> sudo cp /etc/passwd /var/lib/mysql-files/\c26 mysql> sudo -i27 ->\c28 mysql> cd /var/lib/mysql-files/\c29 mysql>passwd \c30 mysql>

31 mysql> load data infile '/var/lib/mysql-files/passwd'

32 -> into tableuserinfo33 -> fields terminated by ':'

34 -> lines terminated by '\n'

35 ->;36 Query OK, 44 rows affected (0.11sec)37 Records: 44 Deleted: 0 Skipped: 0 Warnings: 0

39 mysql> selete * fromuserinfo;40 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selete * from userinfo' at line 1

41 mysql> select * fromuserinfo;42 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+

44 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+

45 | root | x | 0 | 0 | root | /root | /bin/bash |

46 | daemon | x | 1 | 1 | daemon | /usr/sbin | /usr/sbin/nologin |

47 | bin | x | 2 | 2 | bin | /bin | /usr/sbin/nologin |

48 | sys | x | 3 | 3 | sys | /dev | /usr/sbin/nologin |

49 | sync | x | 4 | 65534 | sync | /bin | /bin/sync |

50 | games | x | 5 | 60 | games | /usr/games | /usr/sbin/nologin |

51 | man | x | 6 | 12 | man | /var/cache/man | /usr/sbin/nologin |

52 | lp | x | 7 | 7 | lp | /var/spool/lpd | /usr/sbin/nologin |

53 | mail | x | 8 | 8 | mail | /var/mail | /usr/sbin/nologin |

54 | news | x | 9 | 9 | news | /var/spool/news | /usr/sbin/nologin |

55 | uucp | x | 10 | 10 | uucp | /var/spool/uucp | /usr/sbin/nologin |

56 | proxy | x | 13 | 13 | proxy | /bin | /usr/sbin/nologin |

57 | www-data | x | 33 | 33 | www-data | /var/www | /usr/sbin/nologin |

58 | backup | x | 34 | 34 | backup | /var/backups | /usr/sbin/nologin |

59 | list | x | 38 | 38 | Mailing List Manager | /var/list | /usr/sbin/nologin |

60 | irc | x | 39 | 39 | ircd | /var/run/ircd | /usr/sbin/nologin |

61 | gnats | x | 41 | 41 | Gnats Bug-Reporting System (admin) | /var/lib/gnats | /usr/sbin/nologin |

62 | nobody | x | 65534 | 65534 | nobody | /nonexistent | /usr/sbin/nologin |

63 | systemd-timesync | x | 100 | 102 | systemd Time Synchronization,,, | /run/systemd | /bin/false |

64 | systemd-network | x | 101 | 103 | systemd Network Management,,, | /run/systemd/netif | /bin/false |

65 | systemd-resolve | x | 102 | 104 | systemd Resolver,,, | /run/systemd/resolve | /bin/false |

66 | systemd-bus-proxy | x | 103 | 105 | systemd Bus Proxy,,, | /run/systemd | /bin/false |

67 | syslog | x | 104 | 108 | | /home/syslog | /bin/false |

68 | _apt | x | 105 | 65534 | | /nonexistent | /bin/false |

69 | messagebus | x | 106 | 110 | | /var/run/dbus | /bin/false |

70 | uuidd | x | 107 | 111 | | /run/uuidd | /bin/false |

71 | lightdm | x | 108 | 114 | Light Display Manager | /var/lib/lightdm | /bin/false |

72 | whoopsie | x | 109 | 116 | | /nonexistent | /bin/false |

73 | avahi-autoipd | x | 110 | 119 | Avahi autoip daemon,,, | /var/lib/avahi-autoipd | /bin/false |

74 | avahi | x | 111 | 120 | Avahi mDNS daemon,,, | /var/run/avahi-daemon | /bin/false |

75 | dnsmasq | x | 112 | 65534 | dnsmasq,,, | /var/lib/misc | /bin/false |

76 | colord | x | 113 | 123 | colord colour management daemon,,, | /var/lib/colord | /bin/false |

77 | speech-dispatcher | x | 114 | 29 | Speech Dispatcher,,, | /var/run/speech-dispatcher | /bin/false |

78 | hplip | x | 115 | 7 | HPLIP system user,,, | /var/run/hplip | /bin/false |

79 | kernoops | x | 116 | 65534 | Kernel Oops Tracking Daemon,,, | / | /bin/false |

80 | pulse | x | 117 | 124 | PulseAudio daemon,,, | /var/run/pulse | /bin/false |

81 | rtkit | x | 118 | 126 | RealtimeKit,,, | /proc | /bin/false |

82 | saned | x | 119 | 127 | | /var/lib/saned | /bin/false |

83 | usbmux | x | 120 | 46 | usbmux daemon,,, | /var/lib/usbmux | /bin/false |

84 | tarena | x | 1000 | 1000 | tarena,,, | /home/tarena | /bin/bash |

85 | sshd | x | 121 | 65534 | | /var/run/sshd | /usr/sbin/nologin |

86 | mysql | x | 122 | 129 | MySQL Server,,, | /nonexistent | /bin/false |

87 | mongodb | x | 123 | 65534 | | /var/lib/mongodb | /bin/false |

88 | redis | x | 124 | 131 | | /var/lib/redis | /bin/false |

89 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+

90 44 rows in set (0.00sec)91

92 mysql> select * fromuserinfo\G;93 *************************** 1. row ***************************

94 username: root95 password: x96 uid: 0

97 gid: 0

98 comment: root99 homedir: /root100 shell: /bin/bash101 *************************** 2. row ***************************

102 username: daemon103 password: x104 uid: 1

105 gid: 1

106 comment: daemon107 homedir: /usr/sbin108 shell: /usr/sbin/nologin109 *************************** 3. row ***************************

110 username: bin111 password: x112 uid: 2

113 gid: 2

114 comment: bin115 homedir: /bin116 shell: /usr/sbin/nologin117 *************************** 4. row ***************************

118 username: sys119 password: x120 uid: 3

121 gid: 3

122 comment: sys123 homedir: /dev124 shell: /usr/sbin/nologin125 *************************** 5. row ***************************

126 username: sync127 password: x128 uid: 4

129 gid: 65534

130 comment: sync131 homedir: /bin132 shell: /bin/sync133 *************************** 6. row ***************************

134 username: games135 password: x136 uid: 5

137 gid: 60

138 comment: games139 homedir: /usr/games140 shell: /usr/sbin/nologin141 *************************** 7. row ***************************

142 username: man143 password: x144 uid: 6

145 gid: 12

146 comment: man147 homedir: /var/cache/man148 shell: /usr/sbin/nologin149 *************************** 8. row ***************************

150 username: lp151 password: x152 uid: 7

153 gid: 7

154 comment: lp155 homedir: /var/spool/lpd156 shell: /usr/sbin/nologin157 *************************** 9. row ***************************

158 username: mail159 password: x160 uid: 8

161 gid: 8

162 comment: mail163 homedir: /var/mail164 shell: /usr/sbin/nologin165 *************************** 10. row ***************************

166 username: news167 password: x168 uid: 9

169 gid: 9

170 comment: news171 homedir: /var/spool/news172 shell: /usr/sbin/nologin173 *************************** 11. row ***************************

174 username: uucp175 password: x176 uid: 10

177 gid: 10

178 comment: uucp179 homedir: /var/spool/uucp180 shell: /usr/sbin/nologin181 *************************** 12. row ***************************

182 username: proxy183 password: x184 uid: 13

185 gid: 13

186 comment: proxy187 homedir: /bin188 shell: /usr/sbin/nologin189 *************************** 13. row ***************************

190 username: www-data191 password: x192 uid: 33

193 gid: 33

194 comment: www-data195 homedir: /var/www196 shell: /usr/sbin/nologin197 *************************** 14. row ***************************

198 username: backup

199 password: x200 uid: 34

201 gid: 34

202 comment: backup

203 homedir: /var/backups204 shell: /usr/sbin/nologin205 *************************** 15. row ***************************

206 username: list207 password: x208 uid: 38

209 gid: 38

210 comment: Mailing List Manager211 homedir: /var/list212 shell: /usr/sbin/nologin213 *************************** 16. row ***************************

214 username: irc215 password: x216 uid: 39

217 gid: 39

218 comment: ircd219 homedir: /var/run/ircd220 shell: /usr/sbin/nologin221 *************************** 17. row ***************************

222 username: gnats223 password: x224 uid: 41

225 gid: 41

226 comment: Gnats Bug-Reporting System (admin)227 homedir: /var/lib/gnats228 shell: /usr/sbin/nologin229 *************************** 18. row ***************************

230 username: nobody231 password: x232 uid: 65534

233 gid: 65534

234 comment: nobody235 homedir: /nonexistent236 shell: /usr/sbin/nologin237 *************************** 19. row ***************************

238 username: systemd-timesync239 password: x240 uid: 100

241 gid: 102

242 comment: systemd Time Synchronization,,,243 homedir: /run/systemd244 shell: /bin/false245 *************************** 20. row ***************************

246 username: systemd-network247 password: x248 uid: 101

249 gid: 103

250 comment: systemd Network Management,,,251 homedir: /run/systemd/netif252 shell: /bin/false253 *************************** 21. row ***************************

254 username: systemd-resolve255 password: x256 uid: 102

257 gid: 104

258 comment: systemd Resolver,,,259 homedir: /run/systemd/resolve260 shell: /bin/false261 *************************** 22. row ***************************

262 username: systemd-bus-proxy263 password: x264 uid: 103

265 gid: 105

266 comment: systemd Bus Proxy,,,267 homedir: /run/systemd268 shell: /bin/false269 *************************** 23. row ***************************

270 username: syslog271 password: x272 uid: 104

273 gid: 108

274 comment:275 homedir: /home/syslog276 shell: /bin/false277 *************************** 24. row ***************************

278 username: _apt279 password: x280 uid: 105

281 gid: 65534

282 comment:283 homedir: /nonexistent284 shell: /bin/false285 *************************** 25. row ***************************

286 username: messagebus287 password: x288 uid: 106

289 gid: 110

290 comment:291 homedir: /var/run/dbus292 shell: /bin/false293 *************************** 26. row ***************************

294 username: uuidd295 password: x296 uid: 107

297 gid: 111

298 comment:299 homedir: /run/uuidd300 shell: /bin/false301 *************************** 27. row ***************************

302 username: lightdm303 password: x304 uid: 108

305 gid: 114

306 comment: Light Display Manager307 homedir: /var/lib/lightdm308 shell: /bin/false309 *************************** 28. row ***************************

310 username: whoopsie311 password: x312 uid: 109

313 gid: 116

314 comment:315 homedir: /nonexistent316 shell: /bin/false317 *************************** 29. row ***************************

318 username: avahi-autoipd319 password: x320 uid: 110

321 gid: 119

322 comment: Avahi autoip daemon,,,323 homedir: /var/lib/avahi-autoipd324 shell: /bin/false325 *************************** 30. row ***************************

326 username: avahi327 password: x328 uid: 111

329 gid: 120

330 comment: Avahi mDNS daemon,,,331 homedir: /var/run/avahi-daemon332 shell: /bin/false333 *************************** 31. row ***************************

334 username: dnsmasq335 password: x336 uid: 112

337 gid: 65534

338 comment: dnsmasq,,,339 homedir: /var/lib/misc340 shell: /bin/false341 *************************** 32. row ***************************

342 username: colord343 password: x344 uid: 113

345 gid: 123

346 comment: colord colour management daemon,,,347 homedir: /var/lib/colord348 shell: /bin/false349 *************************** 33. row ***************************

350 username: speech-dispatcher351 password: x352 uid: 114

353 gid: 29

354 comment: Speech Dispatcher,,,355 homedir: /var/run/speech-dispatcher356 shell: /bin/false357 *************************** 34. row ***************************

358 username: hplip359 password: x360 uid: 115

361 gid: 7

362 comment: HPLIP system user,,,363 homedir: /var/run/hplip364 shell: /bin/false365 *************************** 35. row ***************************

366 username: kernoops367 password: x368 uid: 116

369 gid: 65534

370 comment: Kernel Oops Tracking Daemon,,,371 homedir: /

372 shell: /bin/false373 *************************** 36. row ***************************

374 username: pulse375 password: x376 uid: 117

377 gid: 124

378 comment: PulseAudio daemon,,,379 homedir: /var/run/pulse380 shell: /bin/false381 *************************** 37. row ***************************

382 username: rtkit383 password: x384 uid: 118

385 gid: 126

386 comment: RealtimeKit,,,387 homedir: /proc

388 shell: /bin/false389 *************************** 38. row ***************************

390 username: saned391 password: x392 uid: 119

393 gid: 127

394 comment:395 homedir: /var/lib/saned396 shell: /bin/false397 *************************** 39. row ***************************

398 username: usbmux399 password: x400 uid: 120

401 gid: 46

402 comment: usbmux daemon,,,403 homedir: /var/lib/usbmux404 shell: /bin/false405 *************************** 40. row ***************************

406 username: tarena407 password: x408 uid: 1000

409 gid: 1000

410 comment: tarena,,,411 homedir: /home/tarena412 shell: /bin/bash413 *************************** 41. row ***************************

414 username: sshd415 password: x416 uid: 121

417 gid: 65534

418 comment:419 homedir: /var/run/sshd420 shell: /usr/sbin/nologin421 *************************** 42. row ***************************

422 username: mysql423 password: x424 uid: 122

425 gid: 129

426 comment: MySQL Server,,,427 homedir: /nonexistent428 shell: /bin/false429 *************************** 43. row ***************************

430 username: mongodb431 password: x432 uid: 123

433 gid: 65534

434 comment:435 homedir: /var/lib/mongodb436 shell: /bin/false437 *************************** 44. row ***************************

438 username: redis439 password: x440 uid: 124

441 gid: 131

442 comment:443 homedir: /var/lib/redis444 shell: /bin/false445 44 rows in set (0.00sec)446

447 ERROR:448 No query specified449

450 mysql> delect fromuserinfo;451 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delect from userinfo' at line 1

452 mysql> delete fromuserinfo;453 Query OK, 44 rows affected (0.05sec)454

455 mysql> descuserinfo;456 +----------+-------------+------+-----+---------+-------+

458 +----------+-------------+------+-----+---------+-------+

461 | uid | int(11) | YES | | NULL | |

462 | gid | int(11) | YES | | NULL | |

466 +----------+-------------+------+-----+---------+-------+

467 7 rows in set (0.00sec)468

469 mysql> alter table userinfo add 'ziduan' 'shujuleixing'ater \c470 mysql>

471 mysql> select * fromuserinfo;472 Empty set (0.00sec)473

474 mysql>

475 mysql> descuserinfo;476 +----------+-------------+------+-----+---------+-------+

478 +----------+-------------+------+-----+---------+-------+

481 | uid | int(11) | YES | | NULL | |

482 | gid | int(11) | YES | | NULL | |

486 +----------+-------------+------+-----+---------+-------+

487 7 rows in set (0.00sec)488

489 mysql> load date infile '/var/lib/mysql-files/passwd'

490 -> into tableuserinfo491 -> fields terminated by ':'

492 -> lines terminated by '\n'

493 ->;494 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'date infile'/var/lib/mysql-files/passwd'

495 into table userinfo496 fields terminated' at line 1

497 mysql> load data infile '/var/lib/mysql-files/passwd' into table userinfo fields terminated by ':' lines terminated by '\n';498 Query OK, 44 rows affected (0.06sec)499 Records: 44 Deleted: 0 Skipped: 0 Warnings: 0

500

501 mysql>

502 mysql>

503 mysql> select * fromuserinfo;504 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+

506 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+

507 | root | x | 0 | 0 | root | /root | /bin/bash |

508 | daemon | x | 1 | 1 | daemon | /usr/sbin | /usr/sbin/nologin |

509 | bin | x | 2 | 2 | bin | /bin | /usr/sbin/nologin |

510 | sys | x | 3 | 3 | sys | /dev | /usr/sbin/nologin |

511 | sync | x | 4 | 65534 | sync | /bin | /bin/sync |

512 | games | x | 5 | 60 | games | /usr/games | /usr/sbin/nologin |

513 | man | x | 6 | 12 | man | /var/cache/man | /usr/sbin/nologin |

514 | lp | x | 7 | 7 | lp | /var/spool/lpd | /usr/sbin/nologin |

515 | mail | x | 8 | 8 | mail | /var/mail | /usr/sbin/nologin |

516 | news | x | 9 | 9 | news | /var/spool/news | /usr/sbin/nologin |

517 | uucp | x | 10 | 10 | uucp | /var/spool/uucp | /usr/sbin/nologin |

518 | proxy | x | 13 | 13 | proxy | /bin | /usr/sbin/nologin |

519 | www-data | x | 33 | 33 | www-data | /var/www | /usr/sbin/nologin |

520 | backup | x | 34 | 34 | backup | /var/backups | /usr/sbin/nologin |

521 | list | x | 38 | 38 | Mailing List Manager | /var/list | /usr/sbin/nologin |

522 | irc | x | 39 | 39 | ircd | /var/run/ircd | /usr/sbin/nologin |

523 | gnats | x | 41 | 41 | Gnats Bug-Reporting System (admin) | /var/lib/gnats | /usr/sbin/nologin |

524 | nobody | x | 65534 | 65534 | nobody | /nonexistent | /usr/sbin/nologin |

525 | systemd-timesync | x | 100 | 102 | systemd Time Synchronization,,, | /run/systemd | /bin/false |

526 | systemd-network | x | 101 | 103 | systemd Network Management,,, | /run/systemd/netif | /bin/false |

527 | systemd-resolve | x | 102 | 104 | systemd Resolver,,, | /run/systemd/resolve | /bin/false |

528 | systemd-bus-proxy | x | 103 | 105 | systemd Bus Proxy,,, | /run/systemd | /bin/false |

529 | syslog | x | 104 | 108 | | /home/syslog | /bin/false |

530 | _apt | x | 105 | 65534 | | /nonexistent | /bin/false |

531 | messagebus | x | 106 | 110 | | /var/run/dbus | /bin/false |

532 | uuidd | x | 107 | 111 | | /run/uuidd | /bin/false |

533 | lightdm | x | 108 | 114 | Light Display Manager | /var/lib/lightdm | /bin/false |

534 | whoopsie | x | 109 | 116 | | /nonexistent | /bin/false |

535 | avahi-autoipd | x | 110 | 119 | Avahi autoip daemon,,, | /var/lib/avahi-autoipd | /bin/false |

536 | avahi | x | 111 | 120 | Avahi mDNS daemon,,, | /var/run/avahi-daemon | /bin/false |

537 | dnsmasq | x | 112 | 65534 | dnsmasq,,, | /var/lib/misc | /bin/false |

538 | colord | x | 113 | 123 | colord colour management daemon,,, | /var/lib/colord | /bin/false |

539 | speech-dispatcher | x | 114 | 29 | Speech Dispatcher,,, | /var/run/speech-dispatcher | /bin/false |

540 | hplip | x | 115 | 7 | HPLIP system user,,, | /var/run/hplip | /bin/false |

541 | kernoops | x | 116 | 65534 | Kernel Oops Tracking Daemon,,, | / | /bin/false |

542 | pulse | x | 117 | 124 | PulseAudio daemon,,, | /var/run/pulse | /bin/false |

543 | rtkit | x | 118 | 126 | RealtimeKit,,, | /proc | /bin/false |

544 | saned | x | 119 | 127 | | /var/lib/saned | /bin/false |

545 | usbmux | x | 120 | 46 | usbmux daemon,,, | /var/lib/usbmux | /bin/false |

546 | tarena | x | 1000 | 1000 | tarena,,, | /home/tarena | /bin/bash |

547 | sshd | x | 121 | 65534 | | /var/run/sshd | /usr/sbin/nologin |

548 | mysql | x | 122 | 129 | MySQL Server,,, | /nonexistent | /bin/false |

549 | mongodb | x | 123 | 65534 | | /var/lib/mongodb | /bin/false |

550 | redis | x | 124 | 131 | | /var/lib/redis | /bin/false |

551 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+

552 44 rows in set (0.00sec)553

554 mysql>

View Code

数据导出

1、作用

将数据库中表的记录保存到系统文件里

2、语法格式

select ... from 表名

into outfile "文件名"

fields terminated by "分隔符"

lines terminated by "分隔符"

3、练习

1、把userinfo表中的用户名、密码和uid号三个字段导出到userinfo.txt中

2、将库名:mysql库中user表中的User、Host两个字段的值导出到 user2.txt

4、注意

1、导出的内容由SQL查询语句决定

2、执行导出命令时路径必须指定在对应的数据库目录下

3、show variables like "secure_file_priv";

show variables like "%secure%";

1 mysql> select username,password,uid fromuserinfo;2 +-------------------+----------+-------+

3 | username | password | uid |

4 +-------------------+----------+-------+

5 | root | x | 0 |

6 | daemon | x | 1 |

7 | bin | x | 2 |

8 | sys | x | 3 |

9 | sync | x | 4 |

10 | games | x | 5 |

11 | man | x | 6 |

12 | lp | x | 7 |

13 | mail | x | 8 |

14 | news | x | 9 |

15 | uucp | x | 10 |

16 | proxy | x | 13 |

17 | www-data | x | 33 |

18 | backup | x | 34 |

19 | list | x | 38 |

20 | irc | x | 39 |

21 | gnats | x | 41 |

22 | nobody | x | 65534 |

23 | systemd-timesync | x | 100 |

24 | systemd-network | x | 101 |

25 | systemd-resolve | x | 102 |

26 | systemd-bus-proxy | x | 103 |

27 | syslog | x | 104 |

28 | _apt | x | 105 |

29 | messagebus | x | 106 |

30 | uuidd | x | 107 |

31 | lightdm | x | 108 |

32 | whoopsie | x | 109 |

33 | avahi-autoipd | x | 110 |

34 | avahi | x | 111 |

35 | dnsmasq | x | 112 |

36 | colord | x | 113 |

37 | speech-dispatcher | x | 114 |

38 | hplip | x | 115 |

39 | kernoops | x | 116 |

40 | pulse | x | 117 |

41 | rtkit | x | 118 |

42 | saned | x | 119 |

43 | usbmux | x | 120 |

44 | tarena | x | 1000 |

45 | sshd | x | 121 |

46 | mysql | x | 122 |

47 | mongodb | x | 123 |

48 | redis | x | 124 |

49 +-------------------+----------+-------+

50 44 rows in set (0.00sec)51

52 mysql> select username,password,uid fromuserinfo53 -> into outfile '/var/lib/mysql-file/userinfo.txt'

54 -> fields terminated by ' '

55 -> lines terminated by '\n'

56 ->;57 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

58 mysql>

59 mysql>

60 mysql> select username,password,uid fromuserinfo61 -> into outfile '/var/lib/mysql-files/userinfo.txt'

62 -> fields terminated by ' '

63 -> lines terminated by '\n'

64 ->;65 Query OK, 44 rows affected (0.04sec)66

67 mysql>

68 root@tedu:/var/lib/mysql-files# ls69 passwd userinfo.txt70 root@tedu:/var/lib/mysql-files# cat userinfo.txt71 root x 0

72 daemon x 1

73 bin x 2

74 sys x 3

75 sync x 4

76 games x 5

77 man x 6

78 lp x 7

79 mail x 8

80 news x 9

81 uucp x 10

82 proxy x 13

83 www-data x 33

84 backup x 34

85 list x 38

86 irc x 39

87 gnats x 41

88 nobody x 65534

89 systemd-timesync x 100

90 systemd-network x 101

91 systemd-resolve x 102

92 systemd-bus-proxy x 103

93 syslog x 104

94 _apt x 105

95 messagebus x 106

96 uuidd x 107

97 lightdm x 108

98 whoopsie x 109

99 avahi-autoipd x 110

100 avahi x 111

101 dnsmasq x 112

102 colord x 113

103 speech-dispatcher x 114

104 hplip x 115

105 kernoops x 116

106 。。。。。107

108 ####109 mysql> select database();110 +------------+

111 | database() |

112 +------------+

113 | db3 |

114 +------------+

115 1 row in set (0.01sec)116

117 mysql> select User,Host from mysql.user;118 +------------------+-----------+

119 | User | Host |

120 +------------------+-----------+

121 | debian-sys-maint | localhost |

122 | mysql.session | localhost |

123 | mysql.sys | localhost |

124 | root | localhost |

125 +------------------+-----------+

126 4 rows in set (0.31sec)127

128 mysql> select User,Host from mysql.user

129 -> into outfile '/var/lib/mysql-files/user2.txt'

130 -> fields terminated by ' '

131 -> lines terminated by '\n'

132 ->;133 Query OK, 4 rows affected (0.00sec)134

135 mysql>

136 root@tedu:/var/lib/mysql-files# ls137 passwd user2.txt userinfo.txt138 root@tedu:/var/lib/mysql-files# cat user2.txt139 debian-sys-maint localhost140 mysql.session localhost141 mysql.sys localhost142 root localhost143 mysql> show variables like '%secure%';144 +--------------------------+-----------------------+

145 | Variable_name | Value |

146 +--------------------------+-----------------------+

147 | require_secure_transport | OFF |

148 | secure_auth | ON |

149 | secure_file_priv | /var/lib/mysql-files/ |

150 +--------------------------+-----------------------+

151 3 rows in set (0.06sec)152

153 mysql>

154 mysql> show variables like '%char%';155 +--------------------------+----------------------------+

156 | Variable_name | Value |

157 +--------------------------+----------------------------+

158 | character_set_client | utf8 |

159 | character_set_connection | utf8 |

160 | character_set_database | latin1 |

161 | character_set_filesystem | binary |

162 | character_set_results | utf8 |

163 | character_set_server | latin1 |

164 | character_set_system | utf8 |

165 | character_sets_dir | /usr/share/mysql/charsets/ |

166 +--------------------------+----------------------------+

167 8 rows in set (0.00sec)168

169 mysql>

View Code

4、表的复制

1、表的复制

1、语法格式

create table 表名 select 查询命令;

2、练习

1、复制userinfo表的前10行,userinfo3 \c

2、复制userinfo表的用户名、密码、uid三个字段的2-10条记录,userinfo4

2、只复制表结构

1、语法格式

create table 表名 select ... where false; (create table 表名 select ... where 0; )

3、注意

1、复制表的时候不会把原有表的键属性复制过来

1 mysql> create tableuserinfo22 -> select * fromuserinfo;3 Query OK, 44 rows affected (0.58sec)4 Records: 44 Duplicates: 0 Warnings: 0

6 mysql>show tables;7 +---------------+

8 | Tables_in_db3 |

9 +---------------+

10 | bjtab |

11 | jftab |

12 | t1 |

13 | t2 |

14 | t3 |

15 | userinfo |

16 | userinfo2 |

17 +---------------+

18 7 rows in set (0.01sec)19

20 mysql> select * fromuserinfo2;21 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+

23 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+

24 | root | x | 0 | 0 | root | /root | /bin/bash |

25 | daemon | x | 1 | 1 | daemon | /usr/sbin | /usr/sbin/nologin |

26 | bin | x | 2 | 2 | bin | /bin | /usr/sbin/nologin |

27 | sys | x | 3 | 3 | sys | /dev | /usr/sbin/nologin |

28 | sync | x | 4 | 65534 |s29 #######30 mysql> create tableuserinfo331 -> select * from userinfo limit 10;32 Query OK, 10 rows affected (25.42sec)33 Records: 10 Duplicates: 0 Warnings: 0

35 mysql> create tableuserinfo436 -> select * from userinof limit 1,9;37 ERROR 1146 (42S02): Table 'db3.userinof' doesn't exist38 mysql> create table userinfo4 select * from userinfo limit 1,9;39 Query OK, 9 rows affected (0.37 sec)40 Records: 9 Duplicates: 0 Warnings: 041

42 mysql> show tables;43 +---------------+44 | Tables_in_db3 |45 +---------------+46 | bjtab |47 | jftab |48 | t1 |49 | t2 |50 | t3 |51 | userinfo |52 | userinfo2 |53 | userinfo3 |54 | userinfo4 |55 +---------------+56 9 rows in set (0.00 sec)57

58 mysql> desc t2;59 +-------+---------------------------+------+-----+---------+-------+60 | Field | Type | Null | Key | Default | Extra |61 +-------+---------------------------+------+-----+---------+-------+62 | id | int(11) | NO | PRI | NULL | |63 | name | char(20) | YES | | NULL | |64 | likes | set('boy','girl','study') | YES | | NULL | |65 +-------+---------------------------+------+-----+---------+-------+66 3 rows in set (0.07 sec)67

68 mysql> create table new_t2 select * from t2 where false;69 Query OK, 0 rows affected (0.19 sec)70 Records: 0 Duplicates: 0 Warnings: 071

82 mysql> create table new_t2_t2 select * from t2 where 0;83 Query OK, 0 rows affected (0.48 sec)84 Records: 0 Duplicates: 0 Warnings: 085

96 mysql>

View Code

嵌套查询

1、定义

把内层的查询结果作为外层查询的条件

2、语法格式

select 查询语句 where 条件 (select 查询语句);

3、练习

1、把uid的值小于这个字段的平均值的用户名和uid显示出来

2、查找userinfo表中用户名在 mysql库下的user表Host值为localhost并且User值是root 的用户名

1 mysql> select avg(uid) fromuserinfo;2 +-----------+

3 | avg(uid) |

4 +-----------+

5 | 1581.5227 |

6 +-----------+

7 1 row in set (0.11sec)8

9 mysql> select username,uid fromuserinfo10 -> where

11 -> uid < 1581.5227;12 +-------------------+------+

13 | username | uid |

14 +-------------------+------+

15 | root | 0 |

16 | daemon | 1 |

17 | bin | 2 |

18 | sys | 3 |

19 | sync | 4 |

20 | games | 5 |

21 | man | 6 |

22 | lp | 7 |

23 | mail | 8 |

24 | news | 9 |

25 | uucp | 10 |

26 | proxy | 13 |

27 | www-data | 33 |

28 | backup | 34 |

29 | list | 38 |

30 | irc | 39 |

31 | gnats | 41 |

32 | systemd-timesync | 100 |

33 | systemd-network | 101 |

34 | systemd-resolve | 102 |

35 | systemd-bus-proxy | 103 |

36 | syslog | 104 |

37 | _apt | 105 |

38 | messagebus | 106 |

39 | uuidd | 107 |

40 | lightdm | 108 |

41 | whoopsie | 109 |

42 | avahi-autoipd | 110 |

43 | avahi | 111 |

44 | dnsmasq | 112 |

45 | colord | 113 |

46 | speech-dispatcher | 114 |

47 | hplip | 115 |

48 | kernoops | 116 |

49 | pulse | 117 |

50 | rtkit | 118 |

51 | saned | 119 |

52 | usbmux | 120 |

53 | tarena | 1000 |

54 | sshd | 121 |

55 | mysql | 122 |

56 | mongodb | 123 |

57 | redis | 124 |

58 +-------------------+------+

59 43 rows in set (0.00sec)60

61 mysql>

62 mysql> select username,uid fromuserinfo63 -> where

64 -> uid < (select avg(uid) fromuserinfo);65 +-------------------+------+

66 | username | uid |

67 +-------------------+------+

68 | root | 0 |

69 | daemon | 1 |

70 | bin | 2 |

71 | sys | 3 |

72 | sync | 4 |

73 | games | 5 |

74 | man | 6 |

75 | lp | 7 |

76 | mail | 8 |

77 | news | 9 |

78 | uucp | 10 |

79 | proxy | 13 |

80 | www-data | 33 |

81 | backup | 34 |

82 | list | 38 |

83 | irc | 39 |

84 | gnats | 41 |

85 | systemd-timesync | 100 |

86 | systemd-network | 101 |

87 | systemd-resolve | 102 |

88 | systemd-bus-proxy | 103 |

89 | syslog | 104 |

90 | _apt | 105 |

91 | messagebus | 106 |

92 | uuidd | 107 |

93 | lightdm | 108 |

94 | whoopsie | 109 |

95 | avahi-autoipd | 110 |

96 | avahi | 111 |

97 | dnsmasq | 112 |

98 | colord | 113 |

99 | speech-dispatcher | 114 |

100 | hplip | 115 |

101 | kernoops | 116 |

102 | pulse | 117 |

103 | rtkit | 118 |

104 | saned | 119 |

105 | usbmux | 120 |

106 | tarena | 1000 |

107 | sshd | 121 |

108 | mysql | 122 |

109 | mongodb | 123 |

110 | redis | 124 |

111 +-------------------+------+

112 43 rows in set (0.08sec)113

114 mysql>

115 mysql> select username fromuserinfo116 -> where username in

117 -> (select User from mysql.user where Host='localhost' and User='root');118 +----------+

119 | username |

120 +----------+

121 | root |

122 +----------+

123 1 row in set (0.08sec)124

125 mysql>

View Code

test

多表查询

1、两种方式

1、select 字段名列表 from 表名列表; #笛卡尔积

select * from tt1,tt2;

2、select 字段名列表 from 表名列表 where 条件;

2、练习

1、显示省和市的信息

2、显示省、市、县的信息

1 mysql> 1,user1 包含　username uid shell 前两条\c2 mysql>

3 mysql> create tablett14 -> select username,uid,shell fromuserinfo5 -> limit 2;6 Query OK, 2 rows affected (0.46sec)7 Records: 2 Duplicates: 0 Warnings: 0

9 mysql> 2, tt2 包含　username uid gid 前３条\c10 mysql> create tablett211 -> select username,uid,gid fromuserinfo12 -> limit 3;13 Query OK, 3 rows affected (0.69sec)14 Records: 3 Duplicates: 0 Warnings: 0

16 mysql> select * fromtt1;17 +----------+------+-------------------+

18 | username | uid | shell |

19 +----------+------+-------------------+

20 | root | 0 | /bin/bash |

21 | daemon | 1 | /usr/sbin/nologin |

22 +----------+------+-------------------+

23 2 rows in set (0.00sec)24

25 mysql> select * fromtt2;26 +----------+------+------+

27 | username | uid | gid |

28 +----------+------+------+

29 | root | 0 | 0 |

30 | daemon | 1 | 1 |

31 | bin | 2 | 2 |

32 +----------+------+------+

33 3 rows in set (0.00sec)34

35 mysql> select * fromtt1,tt2;36 +----------+------+-------------------+----------+------+------+

37 | username | uid | shell | username | uid | gid |

38 +----------+------+-------------------+----------+------+------+

39 | root | 0 | /bin/bash | root | 0 | 0 |

40 | daemon | 1 | /usr/sbin/nologin | root | 0 | 0 |

41 | root | 0 | /bin/bash | daemon | 1 | 1 |

42 | daemon | 1 | /usr/sbin/nologin | daemon | 1 | 1 |

43 | root | 0 | /bin/bash | bin | 2 | 2 |

44 | daemon | 1 | /usr/sbin/nologin | bin | 2 | 2 |

45 +----------+------+-------------------+----------+------+------+

46 6 rows in set (0.02sec)47

48 mysql> 1,tt1 tt2 表中，ｕｉｄ号相同的信息\c49 mysql> select * fromtt1, tt250 -> where

51 -> tt1.uid =tt2,uid;52 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uid' at line 3

53 mysql> select * from tt1, tt2 where tt1.uid =tt2.uid;54 +----------+------+-------------------+----------+------+------+

55 | username | uid | shell | username | uid | gid |

56 +----------+------+-------------------+----------+------+------+

57 | root | 0 | /bin/bash | root | 0 | 0 |

58 | daemon | 1 | /usr/sbin/nologin | daemon | 1 | 1 |

59 +----------+------+-------------------+----------+------+------+

60 2 rows in set (0.03sec)61

62 mysql>

63 mysql>

64 mysql>show tables;65 +---------------+

66 | Tables_in_db3 |

67 +---------------+

68 | bjtab |

69 | city |

70 | jftab |

71 | new_t2 |

72 | new_t2_t2 |

73 | sheng |

74 | t1 |

75 | t2 |

76 | t3 |

77 | tt1 |

78 | tt2 |

79 | userinfo |

80 | userinfo2 |

81 | userinfo3 |

82 | userinfo4 |

83 | xian |

84 +---------------+

85 16 rows in set (0.00sec)86

87 mysql> select * fromsheng;88 +----+--------+--------------------+

89 | id | S_ID | S_name |

90 +----+--------+--------------------+

91 | 1 | 130000 | 河北省 |

92 | 2 | 140000 | 山西省 |

93 | 3 | 150000 | 内蒙古自治区 |

94 | 4 | 160000 | 辽宁省 |

95 | 5 | 170000 | 黑龙江省 |

96 +----+--------+--------------------+

97 5 rows in set (0.00sec)98

99 mysql> select * fromcity;100 +----+--------+-----------------+------------+

101 | id | C_ID | C_name | CFather_ID |

102 +----+--------+-----------------+------------+

103 | 1 | 131100 | 石家庄市 | 130000 |

104 | 2 | 131101 | 沧州市 | 130000 |

105 | 3 | 131102 | 廊坊市 | 130000 |

106 | 4 | 131103 | 衡水市 | 130000 |

107 | 5 | 131104 | 太原市 | 140000 |

108 | 6 | 131105 | 呼和浩特市 | 150000 |

109 | 7 | 131106 | 包头市 | 150000 |

110 | 8 | 131107 | 沈阳市 | 160000 |

111 | 9 | 131108 | 大连市 | 160000 |

112 | 10 | 131109 | 无锡市 | 320000 |

113 | 11 | 131110 | 徐州市 | 320000 |

114 | 12 | 131111 | 常州市 | 320000 |

115 +----+--------+-----------------+------------+

116 12 rows in set (0.01sec)117

118 mysql> select * fromxian;119 +----+--------+-----------+------------+

120 | id | X_ID | X_name | XFather_ID |

121 +----+--------+-----------+------------+

122 | 1 | 132100 | 河东区 | 131100 |

123 | 2 | 132101 | 正定县 | 131100 |

124 | 3 | 132102 | 固安县 | 131102 |

125 | 4 | 132102 | 香河县 | 131102 |

126 | 5 | 132103 | 哈哈 | 131112 |

127 +----+--------+-----------+------------+

128 5 rows in set (0.00sec)129

130 mysql> 1、显示省和市的信息\c131 mysql>

132 mysql> selectsheng.S_name,city.C_name133 -> fromsheng,city134 -> where

135 -> sheng.S_ID=city.CFather_ID;136 +--------------------+-----------------+

137 | S_name | C_name |

138 +--------------------+-----------------+

139 | 河北省 | 石家庄市 |

140 | 河北省 | 沧州市 |

141 | 河北省 | 廊坊市 |

142 | 河北省 | 衡水市 |

143 | 山西省 | 太原市 |

144 | 内蒙古自治区 | 呼和浩特市 |

145 | 内蒙古自治区 | 包头市 |

146 | 辽宁省 | 沈阳市 |

147 | 辽宁省 | 大连市 |

148 +--------------------+-----------------+

149 9 rows in set (0.00sec)150

151 mysql> 2、显示省、市、县的信息\c152 mysql> selectsheng.S_name,city.C_name,X_name153 -> fromsheng,city,xian154 -> where

155 -> sheng.S_ID=city.CFather_ID and city.C_ID=xian.XFather_ID;156 +-----------+--------------+-----------+

157 | S_name | C_name | X_name |

158 +-----------+--------------+-----------+

159 | 河北省 | 石家庄市 | 河东区 |

160 | 河北省 | 石家庄市 | 正定县 |

161 | 河北省 | 廊坊市 | 固安县 |

162 | 河北省 | 廊坊市 | 香河县 |

163 +-----------+--------------+-----------+

164 4 rows in set (0.00sec)165

166 mysql>

View Code

7、连接查询

1、内连接

1、定义

从表中删除与其他被连接表中没有匹配到的行

2、语法格式

select 字段名列表 from 表1

inner join 表2 on 条件;

3、练习

1、显示省市信息,没有匹配的不显示

2、显示省市县的信息

2、外连接

1、左连接

1、定义

以左表为主显示查询结果

2、语法

slect 字段名列表 from 表1 left join 表2 on 条件;

3、练习

1、显示省市的信息,以左表为准

2、显示省市的信息,以右表为准

3、显示省市区的信息,要求市全部显示

2、右连接

1 mysql>show tables;2 +---------------+

3 | Tables_in_db3 |

4 +---------------+

5 | bjtab |

6 | city |

7 | jftab |

8 | new_t2 |

9 | new_t2_t2 |

10 | sheng |

11 | t1 |

12 | t2 |

13 | t3 |

14 | tt1 |

15 | tt2 |

16 | userinfo |

17 | userinfo2 |

18 | userinfo3 |

19 | userinfo4 |

20 | xian |

21 +---------------+

22 16 rows in set (0.00sec)23

24 mysql> select * fromsheng;25 +----+--------+--------------------+

26 | id | S_ID | S_name |

27 +----+--------+--------------------+

28 | 1 | 130000 | 河北省 |

29 | 2 | 140000 | 山西省 |

30 | 3 | 150000 | 内蒙古自治区 |

31 | 4 | 160000 | 辽宁省 |

32 | 5 | 170000 | 黑龙江省 |

33 +----+--------+--------------------+

34 5 rows in set (0.00sec)35

36 mysql> select * fromcity;37 +----+--------+-----------------+------------+

38 | id | C_ID | C_name | CFather_ID |

39 +----+--------+-----------------+------------+

40 | 1 | 131100 | 石家庄市 | 130000 |

41 | 2 | 131101 | 沧州市 | 130000 |

42 | 3 | 131102 | 廊坊市 | 130000 |

43 | 4 | 131103 | 衡水市 | 130000 |

44 | 5 | 131104 | 太原市 | 140000 |

45 | 6 | 131105 | 呼和浩特市 | 150000 |

46 | 7 | 131106 | 包头市 | 150000 |

47 | 8 | 131107 | 沈阳市 | 160000 |

48 | 9 | 131108 | 大连市 | 160000 |

49 | 10 | 131109 | 无锡市 | 320000 |

50 | 11 | 131110 | 徐州市 | 320000 |

51 | 12 | 131111 | 常州市 | 320000 |

52 +----+--------+-----------------+------------+

53 12 rows in set (0.01sec)54

55 mysql> select * fromxian;56 +----+--------+-----------+------------+

57 | id | X_ID | X_name | XFather_ID |

58 +----+--------+-----------+------------+

59 | 1 | 132100 | 河东区 | 131100 |

60 | 2 | 132101 | 正定县 | 131100 |

61 | 3 | 132102 | 固安县 | 131102 |

62 | 4 | 132102 | 香河县 | 131102 |

63 | 5 | 132103 | 哈哈 | 131112 |

64 +----+--------+-----------+------------+

65 5 rows in set (0.00sec)66

67 mysql> 1、显示省和市的信息\c68 mysql>

69 mysql> selectsheng.S_name,city.C_name70 -> fromsheng,city71 -> where

72 -> sheng.S_ID=city.CFather_ID;73 +--------------------+-----------------+

74 | S_name | C_name |

75 +--------------------+-----------------+

76 | 河北省 | 石家庄市 |

77 | 河北省 | 沧州市 |

78 | 河北省 | 廊坊市 |

79 | 河北省 | 衡水市 |

80 | 山西省 | 太原市 |

81 | 内蒙古自治区 | 呼和浩特市 |

82 | 内蒙古自治区 | 包头市 |

83 | 辽宁省 | 沈阳市 |

84 | 辽宁省 | 大连市 |

85 +--------------------+-----------------+

86 9 rows in set (0.00sec)87

88 mysql> 2、显示省、市、县的信息\c89 mysql> selectsheng.S_name,city.C_name,X_name90 -> fromsheng,city,xian91 -> where

92 -> sheng.S_ID=city.CFather_ID and city.C_ID=xian.XFather_ID;93 +-----------+--------------+-----------+

94 | S_name | C_name | X_name |

95 +-----------+--------------+-----------+

96 | 河北省 | 石家庄市 | 河东区 |

97 | 河北省 | 石家庄市 | 正定县 |

98 | 河北省 | 廊坊市 | 固安县 |

99 | 河北省 | 廊坊市 | 香河县 |

100 +-----------+--------------+-----------+

101 4 rows in set (0.00sec)102

103 mysql>内连接104 -> 1、显示省市信息,没有匹配的不显示\c105 mysql> selectsheng.S_name,city.C_name106 -> from

107 ->sheng108 -> inner joincity109 -> on sheng.S_ID=city.CFather_ID;110 +--------------------+-----------------+

111 | S_name | C_name |

112 +--------------------+-----------------+

113 | 河北省 | 石家庄市 |

114 | 河北省 | 沧州市 |

115 | 河北省 | 廊坊市 |

116 | 河北省 | 衡水市 |

117 | 山西省 | 太原市 |

118 | 内蒙古自治区 | 呼和浩特市 |

119 | 内蒙古自治区 | 包头市 |

120 | 辽宁省 | 沈阳市 |

121 | 辽宁省 | 大连市 |

122 +--------------------+-----------------+

123 9 rows in set (0.00sec)124

125 mysql>

126 mysql> 2、显示省市县的信息\c127 mysql> selectsheng.S_name,city.C_name,xian.X_name128 -> from sheng inner joincity129 -> on sheng.S_ID=city.CFather_ID130 -> inner join xian on city.C_ID=xian.XFather_ID;131 +-----------+--------------+-----------+

132 | S_name | C_name | X_name |

133 +-----------+--------------+-----------+

134 | 河北省 | 石家庄市 | 河东区 |

135 | 河北省 | 石家庄市 | 正定县 |

136 | 河北省 | 廊坊市 | 固安县 |

137 | 河北省 | 廊坊市 | 香河县 |

138 +-----------+--------------+-----------+

139 4 rows in set (0.00sec)140

141 mysql> select sheng.S_name as Sheng,city.C_name as Shi,xian.X_name as Xian from sheng inne ID=xian.XFather_ID;142 +-----------+--------------+-----------+

143 | Sheng | Shi | Xian |

144 +-----------+--------------+-----------+

145 | 河北省 | 石家庄市 | 河东区 |

146 | 河北省 | 石家庄市 | 正定县 |

147 | 河北省 | 廊坊市 | 固安县 |

148 | 河北省 | 廊坊市 | 香河县 |

149 +-----------+--------------+-----------+

150 4 rows in set (0.04sec)151

152 mysql> select sheng.S_name as Sheng,city.C_name as Shi,xian.X_name as Xian from sheng inner join city on sheng.S_ID=city.CFather_ID inner join xian on city.C_ID=xian.XFather_ID;153 +-----------+--------------+-----------+

154 | Sheng | Shi | Xian |

155 +-----------+--------------+-----------+

156 | 河北省 | 石家庄市 | 河东区 |

157 | 河北省 | 石家庄市 | 正定县 |

158 | 河北省 | 廊坊市 | 固安县 |

159 | 河北省 | 廊坊市 | 香河县 |

160 +-----------+--------------+-----------+

161 4 rows in set (0.00sec)162

163 mysql> 外连接-左连接164 -> 1、显示省市的信息,以左表为准\c165 mysql> select sheng.S_name,city.C_name fromsheng166 -> left joincity167 -> on sheng.S_ID=city.CFather_ID;168 +--------------------+-----------------+

169 | S_name | C_name |

170 +--------------------+-----------------+

171 | 河北省 | 石家庄市 |

172 | 河北省 | 沧州市 |

173 | 河北省 | 廊坊市 |

174 | 河北省 | 衡水市 |

175 | 山西省 | 太原市 |

176 | 内蒙古自治区 | 呼和浩特市 |

177 | 内蒙古自治区 | 包头市 |

178 | 辽宁省 | 沈阳市 |

179 | 辽宁省 | 大连市 |

180 | 黑龙江省 | NULL |

181 +--------------------+-----------------+

182 10 rows in set (0.00sec)183

184 mysql> 外连接-右连接\c185 mysql> select sheng.S_name,city.C_name fromsheng186 -> right joincity187 -> on sheng.S_ID=city.CFather_ID;188 +--------------------+-----------------+

189 | S_name | C_name |

190 +--------------------+-----------------+

191 | 河北省 | 石家庄市 |

192 | 河北省 | 沧州市 |

193 | 河北省 | 廊坊市 |

194 | 河北省 | 衡水市 |

195 | 山西省 | 太原市 |

196 | 内蒙古自治区 | 呼和浩特市 |

197 | 内蒙古自治区 | 包头市 |

198 | 辽宁省 | 沈阳市 |

199 | 辽宁省 | 大连市 |

200 | NULL | 无锡市 |

201 | NULL | 徐州市 |

202 | NULL | 常州市 |

203 +--------------------+-----------------+

204 12 rows in set (0.00sec)205

206 mysql> 3、显示省市区的信息,要求市全部显示\c207 mysql> select sheng.S_name,city.C_name,xian.X_name fromsheng208 -> right join city on sheng.S_ID=city.CFather_ID209 -> left join xian on city.C_ID=xian.XFather_ID;210 +--------------------+-----------------+-----------+

211 | S_name | C_name | X_name |

212 +--------------------+-----------------+-----------+

213 | 河北省 | 石家庄市 | 河东区 |

214 | 河北省 | 石家庄市 | 正定县 |

215 | 河北省 | 廊坊市 | 固安县 |

216 | 河北省 | 廊坊市 | 香河县 |

217 | 河北省 | 沧州市 | NULL |

218 | 河北省 | 衡水市 | NULL |

219 | 山西省 | 太原市 | NULL |

220 | 内蒙古自治区 | 呼和浩特市 | NULL |

221 | 内蒙古自治区 | 包头市 | NULL |

222 | 辽宁省 | 沈阳市 | NULL |

223 | 辽宁省 | 大连市 | NULL |

224 | NULL | 无锡市 | NULL |

225 | NULL | 徐州市 | NULL |

226 | NULL | 常州市 | NULL |

227 +--------------------+-----------------+-----------+

228 14 rows in set (0.00sec)229

230 mysql>

View Code

河岸的翁

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql数据库三章_MySQL篇，第三章：数据库知识3

MySQL 数据库 3索引1、普通索引(MUL)2、唯一索引(UNI)3、主键索引(PRI)1、使用规则1、一个表中只能有一个主键(primary)字段2、对应字段的值不允许重复,且不能为空3、主键字段的key标志PRI4、把表中能够唯一标识一条记录的字段设置为主键,通常把表中记录编号的字段设置为主键2、创建主键(primary key)1、创建表时创建1、字段名数据类型 primary key...
复制链接

扫一扫

mysql数据库 三章_MySQL篇，第三章：数据库知识3

mysql数据库三章_MySQL篇，第三章：数据库知识3