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 +-------+--------------------+------+-----+---------+-------+
12 | Field | Type | Null | Key | Default | Extra |
13 +-------+--------------------+------+-----+---------+-------+
14 | id | int(11) | NO | PRI | NULL | |
15 | name | varchar(15) | NO | | 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 +-------+---------------------------+------+-----+---------+-------+
38 | Field | Type | Null | Key | Default | Extra |
39 +-------+---------------------------+------+-----+---------+-------+
40 | id | int(11) | NO | PRI | NULL | |
41 | name | char(20) | YES | | NULL | |
42 | likes | set('boy','girl','study') | YES | | 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 +-------+---------------------------+------+-----+---------+-------+
56 | Field | Type | Null | Key | Default | Extra |
57 +-------+---------------------------+------+-----+---------+-------+
58 | id | int(11) | NO | | NULL | |
59 | name | char(20) | YES | | NULL | |
60 | likes | set('boy','girl','study') | YES | | 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 +-------+---------------------------+------+-----+---------+-------+
70 | Field | Type | Null | Key | Default | Extra |
71 +-------+---------------------------+------+-----+---------+-------+
72 | id | int(11) | NO | PRI | NULL | |
73 | name | char(20) | YES | | NULL | |
74 | likes | set('boy','girl','study') | YES | | 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 +-------+---------------------+------+-----+---------+----------------+
11 | Field | Type | Null | Key | Default | Extra |
12 +-------+---------------------+------+-----+---------+----------------+
13 | id | int(11) | NO | PRI | NULL | auto_increment |
14 | name | char(15) | YES | | NULL | |
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 +-------+---------------------+------+-----+---------+----------------+
72 | Field | Type | Null | Key | Default | Extra |
73 +-------+---------------------+------+-----+---------+----------------+
74 | id | int(11) | NO | PRI | NULL | auto_increment |
75 | name | char(15) | YES | | NULL | |
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 +-------+------------+------+-----+---------+-------+
17 | Field | Type | Null | Key | Default | Extra |
18 +-------+------------+------+-----+---------+-------+
19 | id | int(11) | NO | PRI | NULL | |
20 | name | char(20) | YES | | NULL | |
21 | class | varchar(7) | YES | | 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
32
33 mysql> select * fromjftab;34 +----+-----------+---------+-------+
35 | id | name | class | money |
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 +----+-----------+---------+-------+
55 | id | name | class | money |
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 +--------+-------------+------+-----+---------+-------+
65 | Field | Type | Null | Key | Default | Extra |
66 +--------+-------------+------+-----+---------+-------+
67 | stu_id | int(11) | YES | MUL | NULL | |
68 | name | varchar(20) | YES | | 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
78
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 +----+-----------+---------+-------+
90 | id | name | class | money |
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 +----+-----------+---------+-------+
107 | id | name | class | money |
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 +----+-----------+---------+-------+
136 | id | name | class | money |
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
19
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 +--------+-------------+------+-----+---------+-------+
34 | Field | Type | Null | Key | Default | Extra |
35 +--------+-------------+------+-----+---------+-------+
36 | stu_id | int(11) | YES | MUL | NULL | |
37 | name | varchar(20) | YES | | NULL | |
38 | money | int(11) | YES | | NULL | |
39 +--------+-------------+------+-----+---------+-------+
40 3 rows in set (0.09sec)41
42 mysql> select * fromjftab;43 +----+-----------+---------+-------+
44 | id | name | class | money |
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 |
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 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 +--------+-------------+------+-----+---------+-------+
77 | Field | Type | Null | Key | Default | Extra |
78 +--------+-------------+------+-----+---------+-------+
79 | stu_id | int(11) | YES | MUL | NULL | |
80 | name | varchar(20) | YES | | NULL | |
81 | money | int(11) | YES | | NULL | |
82 +--------+-------------+------+-----+---------+-------+
83 3 rows in set (0.05sec)84
85 mysql>desc jftab;86 +-------+------------+------+-----+---------+-------+
87 | Field | Type | Null | Key | Default | Extra |
88 +-------+------------+------+-----+---------+-------+
89 | id | int(11) | NO | PRI | NULL | |
90 | name | char(20) | YES | | NULL | |
91 | class | varchar(7) | YES | | 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 +--------+-------------+------+-----+---------+-------+
106 | Field | Type | Null | Key | Default | Extra |
107 +--------+-------------+------+-----+---------+-------+
108 | stu_id | int(11) | YES | MUL | NULL | |
109 | name | varchar(20) | YES | | NULL | |
110 | money | int(11) | YES | | NULL | |
111 +--------+-------------+------+-----+---------+-------+
112 3 rows in set (0.00sec)113
114 mysql>
115 mysql> select * fromjftab;116 +----+-----------+---------+-------+
117 | id | name | class | money |
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 +----+-----------+---------+-------+
137 | id | name | class | money |
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 +-------+---------------------+------+-----+---------+----------------+
165 | Field | Type | Null | Key | Default | Extra |
166 +-------+---------------------+------+-----+---------+----------------+
167 | id | int(11) | NO | PRI | NULL | auto_increment |
168 | name | char(15) | YES | | NULL | |
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 +-------+---------------------+------+-----+---------+-------+
179 | Field | Type | Null | Key | Default | Extra |
180 +-------+---------------------+------+-----+---------+-------+
181 | id | int(11) | NO | PRI | NULL | |
182 | name | char(15) | YES | | 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 +-------+---------------------+------+-----+---------+-------+
193 | Field | Type | Null | Key | Default | Extra |
194 +-------+---------------------+------+-----+---------+-------+
195 | id | int(11) | NO | | NULL | |
196 | name | char(15) | YES | | 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
ls
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
38
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 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+
43 | username | password | uid | gid | comment | homedir | shell |
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 +----------+-------------+------+-----+---------+-------+
457 | Field | Type | Null | Key | Default | Extra |
458 +----------+-------------+------+-----+---------+-------+
459 | username | char(20) | YES | | NULL | |
460 | password | char(1) | YES | | NULL | |
461 | uid | int(11) | YES | | NULL | |
462 | gid | int(11) | YES | | NULL | |
463 | comment | varchar(50) | YES | | NULL | |
464 | homedir | varchar(50) | YES | | NULL | |
465 | shell | varchar(50) | 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 +----------+-------------+------+-----+---------+-------+
477 | Field | Type | Null | Key | Default | Extra |
478 +----------+-------------+------+-----+---------+-------+
479 | username | char(20) | YES | | NULL | |
480 | password | char(1) | YES | | NULL | |
481 | uid | int(11) | YES | | NULL | |
482 | gid | int(11) | YES | | NULL | |
483 | comment | varchar(50) | YES | | NULL | |
484 | homedir | varchar(50) | YES | | NULL | |
485 | shell | varchar(50) | 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 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+
505 | username | password | uid | gid | comment | homedir | shell |
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
5
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 +-------------------+----------+-------+-------+------------------------------------+----------------------------+-------------------+
22 | username | password | uid | gid | comment | homedir | shell |
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
34
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
72 mysql> desc new_t2;73 +-------+---------------------------+------+-----+---------+-------+74 | Field | Type | Null | Key | Default | Extra |75 +-------+---------------------------+------+-----+---------+-------+76 | id | int(11) | NO | | NULL | |77 | name | char(20) | YES | | NULL | |78 | likes | set('boy','girl','study') | YES | | NULL | |79 +-------+---------------------------+------+-----+---------+-------+80 3 rows in set (0.04 sec)81
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
86 mysql> desc new_t2_t2;87 +-------+---------------------------+------+-----+---------+-------+88 | Field | Type | Null | Key | Default | Extra |89 +-------+---------------------------+------+-----+---------+-------+90 | id | int(11) | NO | | NULL | |91 | name | char(20) | YES | | NULL | |92 | likes | set('boy','girl','study') | YES | | NULL | |93 +-------+---------------------------+------+-----+---------+-------+94 3 rows in set (0.00 sec)95
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
8
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
15
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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
