MySQL篇，第三章：数据库知识3
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 db3 2 Database changed 3 mysql> create table t1( 4 -> id int primary key, 5 -> name varchar(15) not null, 6 -> sex enum('boy','girl') default 'boy' 7 -> )default charset=utf8; 8 Query OK, 0 rows affected (1.44 sec) 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.13 sec) 19 20 mysql> insert into t1 values(1,'zhangsanfeng','boy'); 21 Query OK, 1 row affected (0.12 sec) 22 23 mysql> insert into t1 values(1,'zhangwuji','boy'); 24 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 25 mysql> 以上第一种方式\c 26 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.22 sec) 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.05 sec) 45 46 mysql> 以上第２种方式\c 47 mysql> 48 mysql> primary key 主键索引\c 49 mysql> 50 mysql> alter table t2 drop primary key; 51 Query OK, 0 rows affected (0.89 sec) 52 Records: 0 Duplicates: 0 Warnings: 0 53 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.08 sec) 63 64 mysql> alter table t2 add primary key(id); 65 Query OK, 0 rows affected (0.52 sec) 66 Records: 0 Duplicates: 0 Warnings: 0 67 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.00 sec) 77 78 mysql>
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 table t3( 3 -> id int primary key auto_increment, 4 -> name char(15), 5 -> age tinyint unsigned 6 -> ); 7 Query OK, 0 rows affected (0.29 sec) 8 9 mysql> desc t3; 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.06 sec) 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.04 sec) 25 26 mysql> insert into t3 values(0,'xiaozhao',30) 27 -> ; 28 Query OK, 1 row affected (0.07 sec) 29 30 mysql> insert into t3 values(0,'zhouziruo',25); 31 Query OK, 1 row affected (0.02 sec) 32 33 mysql> select * from t3; 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.00 sec) 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.22 sec) 47 48 mysql> select * from t3; 49 +----+----------+------+ 50 | id | name | age | 51 +----+----------+------+ 52 | 1 | zhaomin | 30 | 53 | 2 | xiaozhao | 30 | 54 +----+----------+------+ 55 2 rows in set (0.00 sec) 56 57 mysql> insert into t3 values(0,'zhangwuji',28); 58 Query OK, 1 row affected (0.00 sec) 59 60 mysql> select * from t3; 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.00 sec) 69 70 mysql> desc t3; 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.00 sec) 79 80 mysql>
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> use db3; 2 Reading table information for completion of table and column names 3 You can turn off this feature to get a quicker startup with -A 4 5 Database changed 6 mysql> 7 mysql> create table jftab( 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.28 sec) 14 15 mysql> desc jftab; 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.05 sec) 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.18 sec) 31 Records: 3 Duplicates: 0 Warnings: 0 32 33 mysql> select * from jftab; 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.00 sec) 42 43 mysql> create table bjtab( 44 -> stu_id int, 45 -> name varchar(20), 46 -> money int, 47 -> foreign key(stu_id) references jftab(id) 48 -> on delete cascade 49 -> on update cascade 50 -> )default charset=utf8; 51 Query OK, 0 rows affected (0.44 sec) 52 53 mysql> select * from jftab; 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.00 sec) 62 63 mysql> desc bjtab; 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.03 sec) 72 73 mysql> insert into bjtab values 74 -> (1,'唐伯虎',28000), 75 -> (2,'点秋香',20000); 76 Query OK, 2 rows affected (0.10 sec) 77 Records: 2 Duplicates: 0 Warnings: 0 78 79 mysql> select * from bjtab; 80 +--------+-----------+-------+ 81 | stu_id | name | money | 82 +--------+-----------+-------+ 83 | 1 | 唐伯虎 | 28000 | 84 | 2 | 点秋香 | 20000 | 85 +--------+-----------+-------+ 86 2 rows in set (0.00 sec) 87 88 mysql> select * from jftab; 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.00 sec) 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.11 sec) 104 105 mysql> select * from jftab; 106 +----+-----------+---------+-------+ 107 | id | name | class | money | 108 +----+-----------+---------+-------+ 109 | 1 | 唐伯虎 | AID1806 | 28000 | 110 | 3 | 祝枝山 | AID1806 | 25000 | 111 +----+-----------+---------+-------+ 112 2 rows in set (0.01 sec) 113 114 mysql> select * from bjtab; 115 +--------+-----------+-------+ 116 | stu_id | name | money | 117 +--------+-----------+-------+ 118 | 1 | 唐伯虎 | 28000 | 119 +--------+-----------+-------+ 120 1 row in set (0.00 sec) 121 122 mysql> update jftab set id=8 where id=1; 123 Query OK, 1 row affected (0.15 sec) 124 Rows matched: 1 Changed: 1 Warnings: 0 125 126 mysql> select * from bjtab; 127 +--------+-----------+-------+ 128 | stu_id | name | money | 129 +--------+-----------+-------+ 130 | 8 | 唐伯虎 | 28000 | 131 +--------+-----------+-------+ 132 1 row in set (0.00 sec) 133 134 mysql> select * from jftab; 135 +----+-----------+---------+-------+ 136 | id | name | class | money | 137 +----+-----------+---------+-------+ 138 | 3 | 祝枝山 | AID1806 | 25000 | 139 | 8 | 唐伯虎 | AID1806 | 28000 | 140 +----+-----------+---------+-------+ 141 2 rows in set (0.00 sec) 142 143 mysql>
4、删除外键
1、语法格式
alter table 表名 drop foreign key 外键名;
2、注意
1、外键名的查看方式
show create table 表名;
1 mysql> 2 mysql> show create table bjtab; 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.05 sec) 15 16 mysql> alter table bjtab drop foreign key bjtab_ibfk_1; 17 Query OK, 0 rows affected (0.07 sec) 18 Records: 0 Duplicates: 0 Warnings: 0 19 20 mysql> show create table bjtab; 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.00 sec) 32 33 mysql> insert into bjtab values(10,'xiaoxiao',28); 34 Query OK, 1 row affected (0.00 sec) 35 36 mysql> select * from bjtab; 37 +--------+-----------+-------+ 38 | stu_id | name | money | 39 +--------+-----------+-------+ 40 | 8 | 唐伯虎 | 28000 | 41 | 10 | xiaoxiao | 28 | 42 +--------+-----------+-------+ 43 2 rows in set (0.00 sec) 44 45 mysql> delete from bjtab where stu_id=10; 46 Query OK, 1 row affected (0.00 sec) 47 48 mysql> select * from bjtab; 49 +--------+-----------+-------+ 50 | stu_id | name | money | 51 +--------+-----------+-------+ 52 | 8 | 唐伯虎 | 28000 | 53 +--------+-----------+-------+ 54 1 row in set (0.00 sec) 55 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 67 +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 68 1 row in set (0.00 sec) 69 70 mysql>
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 and column names 3 You can turn off this feature to get a quicker startup with -A 4 5 Database changed 6 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.02 sec) 17 18 mysql> alter table bjtab add 19 -> foreign key(stu_id) references jftab(id) 20 -> ; 21 Query OK, 1 row affected (3.94 sec) 22 Records: 1 Duplicates: 0 Warnings: 0 23 24 mysql> select * from bjtab; 25 +--------+-----------+-------+ 26 | stu_id | name | money | 27 +--------+-----------+-------+ 28 | 8 | 唐伯虎 | 28000 | 29 +--------+-----------+-------+ 30 1 row in set (0.00 sec) 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.09 sec) 41 42 mysql> select * from jftab; 43 +----+-----------+---------+-------+ 44 | id | name | class | money | 45 +----+-----------+---------+-------+ 46 | 3 | 祝枝山 | AID1806 | 25000 | 47 | 8 | 唐伯虎 | AID1806 | 28000 | 48 +----+-----------+---------+-------+ 49 2 rows in set (0.00 sec) 50 51 mysql> delete from jftab where name='唐伯虎'; 52 ERROR 1451 (23000): Cannot delete or update 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 or update 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.05 sec) 70 71 mysql> alter table bjtab drop foreign key bjtab_ibfk_1 ; 72 Query OK, 0 rows affected (0.16 sec) 73 Records: 0 Duplicates: 0 Warnings: 0 74 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.05 sec) 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.00 sec) 95 96 mysql> alter table bjtab 97 -> add foreign key(stu_id) references jftab(id) 98 -> on delete set null 99 -> on update set null 100 -> ; 101 Query OK, 1 row affected (0.30 sec) 102 Records: 1 Duplicates: 0 Warnings: 0 103 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.00 sec) 113 114 mysql> 115 mysql> select * from jftab; 116 +----+-----------+---------+-------+ 117 | id | name | class | money | 118 +----+-----------+---------+-------+ 119 | 3 | 祝枝山 | AID1806 | 25000 | 120 | 8 | 唐伯虎 | AID1806 | 28000 | 121 +----+-----------+---------+-------+ 122 2 rows in set (0.00 sec) 123 124 mysql> select * from bjtab; 125 +--------+-----------+-------+ 126 | stu_id | name | money | 127 +--------+-----------+-------+ 128 | 8 | 唐伯虎 | 28000 | 129 +--------+-----------+-------+ 130 1 row in set (0.00 sec) 131 132 mysql> delete from jftab where name='唐伯虎'; 133 Query OK, 1 row affected (0.04 sec) 134 135 mysql> select * from jftab; 136 +----+-----------+---------+-------+ 137 | id | name | class | money | 138 +----+-----------+---------+-------+ 139 | 3 | 祝枝山 | AID1806 | 25000 | 140 +----+-----------+---------+-------+ 141 1 row in set (0.01 sec) 142 143 mysql> select * from bjtab; 144 +--------+-----------+-------+ 145 | stu_id | name | money | 146 +--------+-----------+-------+ 147 | NULL | 唐伯虎 | 28000 | 148 +--------+-----------+-------+ 149 1 row in set (0.00 sec) 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.00 sec) 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.01 sec) 172 173 mysql> alter table t3 modify id int; 174 Query OK, 3 rows affected (0.90 sec) 175 Records: 3 Duplicates: 0 Warnings: 0 176 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.00 sec) 186 187 mysql> alter table t3 drop primary key; 188 Query OK, 3 rows affected (0.64 sec) 189 Records: 3 Duplicates: 0 Warnings: 0 190 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.06 sec) 200 201 mysql>
数据导入
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 table userinfo( 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.25 sec) 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.65 sec) 21 22 mysql> 23 mysql> 24 mysql> 25 mysql> sudo cp /etc/passwd /var/lib/mysql-files/ \c 26 mysql> sudo -i 27 -> \c 28 mysql> cd /var/lib/mysql-files/ \c 29 mysql> passwd \c 30 mysql> 31 mysql> load data infile '/var/lib/mysql-files/passwd' 32 -> into table userinfo 33 -> fields terminated by ':' 34 -> lines terminated by '\n' 35 -> ; 36 Query OK, 44 rows affected (0.11 sec) 37 Records: 44 Deleted: 0 Skipped: 0 Warnings: 0 38 39 mysql> selete * from userinfo; 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 * from userinfo; 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.00 sec) 91 92 mysql> select * from userinfo\G; 93 *************************** 1. row *************************** 94 username: root 95 password: x 96 uid: 0 97 gid: 0 98 comment: root 99 homedir: /root 100 shell: /bin/bash 101 *************************** 2. row *************************** 102 username: daemon 103 password: x 104 uid: 1 105 gid: 1 106 comment: daemon 107 homedir: /usr/sbin 108 shell: /usr/sbin/nologin 109 *************************** 3. row *************************** 110 username: bin 111 password: x 112 uid: 2 113 gid: 2 114 comment: bin 115 homedir: /bin 116 shell: /usr/sbin/nologin 117 *************************** 4. row *************************** 118 username: sys 119 password: x 120 uid: 3 121 gid: 3 122 comment: sys 123 homedir: /dev 124 shell: /usr/sbin/nologin 125 *************************** 5. row *************************** 126 username: sync 127 password: x 128 uid: 4 129 gid: 65534 130 comment: sync 131 homedir: /bin 132 shell: /bin/sync 133 *************************** 6. row *************************** 134 username: games 135 password: x 136 uid: 5 137 gid: 60 138 comment: games 139 homedir: /usr/games 140 shell: /usr/sbin/nologin 141 *************************** 7. row *************************** 142 username: man 143 password: x 144 uid: 6 145 gid: 12 146 comment: man 147 homedir: /var/cache/man 148 shell: /usr/sbin/nologin 149 *************************** 8. row *************************** 150 username: lp 151 password: x 152 uid: 7 153 gid: 7 154 comment: lp 155 homedir: /var/spool/lpd 156 shell: /usr/sbin/nologin 157 *************************** 9. row *************************** 158 username: mail 159 password: x 160 uid: 8 161 gid: 8 162 comment: mail 163 homedir: /var/mail 164 shell: /usr/sbin/nologin 165 *************************** 10. row *************************** 166 username: news 167 password: x 168 uid: 9 169 gid: 9 170 comment: news 171 homedir: /var/spool/news 172 shell: /usr/sbin/nologin 173 *************************** 11. row *************************** 174 username: uucp 175 password: x 176 uid: 10 177 gid: 10 178 comment: uucp 179 homedir: /var/spool/uucp 180 shell: /usr/sbin/nologin 181 *************************** 12. row *************************** 182 username: proxy 183 password: x 184 uid: 13 185 gid: 13 186 comment: proxy 187 homedir: /bin 188 shell: /usr/sbin/nologin 189 *************************** 13. row *************************** 190 username: www-data 191 password: x 192 uid: 33 193 gid: 33 194 comment: www-data 195 homedir: /var/www 196 shell: /usr/sbin/nologin 197 *************************** 14. row *************************** 198 username: backup 199 password: x 200 uid: 34 201 gid: 34 202 comment: backup 203 homedir: /var/backups 204 shell: /usr/sbin/nologin 205 *************************** 15. row *************************** 206 username: list 207 password: x 208 uid: 38 209 gid: 38 210 comment: Mailing List Manager 211 homedir: /var/list 212 shell: /usr/sbin/nologin 213 *************************** 16. row *************************** 214 username: irc 215 password: x 216 uid: 39 217 gid: 39 218 comment: ircd 219 homedir: /var/run/ircd 220 shell: /usr/sbin/nologin 221 *************************** 17. row *************************** 222 username: gnats 223 password: x 224 uid: 41 225 gid: 41 226 comment: Gnats Bug-Reporting System (admin) 227 homedir: /var/lib/gnats 228 shell: /usr/sbin/nologin 229 *************************** 18. row *************************** 230 username: nobody 231 password: x 232 uid: 65534 233 gid: 65534 234 comment: nobody 235 homedir: /nonexistent 236 shell: /usr/sbin/nologin 237 *************************** 19. row *************************** 238 username: systemd-timesync 239 password: x 240 uid: 100 241 gid: 102 242 comment: systemd Time Synchronization,,, 243 homedir: /run/systemd 244 shell: /bin/false 245 *************************** 20. row *************************** 246 username: systemd-network 247 password: x 248 uid: 101 249 gid: 103 250 comment: systemd Network Management,,, 251 homedir: /run/systemd/netif 252 shell: /bin/false 253 *************************** 21. row *************************** 254 username: systemd-resolve 255 password: x 256 uid: 102 257 gid: 104 258 comment: systemd Resolver,,, 259 homedir: /run/systemd/resolve 260 shell: /bin/false 261 *************************** 22. row *************************** 262 username: systemd-bus-proxy 263 password: x 264 uid: 103 265 gid: 105 266 comment: systemd Bus Proxy,,, 267 homedir: /run/systemd 268 shell: /bin/false 269 *************************** 23. row *************************** 270 username: syslog 271 password: x 272 uid: 104 273 gid: 108 274 comment: 275 homedir: /home/syslog 276 shell: /bin/false 277 *************************** 24. row *************************** 278 username: _apt 279 password: x 280 uid: 105 281 gid: 65534 282 comment: 283 homedir: /nonexistent 284 shell: /bin/false 285 *************************** 25. row *************************** 286 username: messagebus 287 password: x 288 uid: 106 289 gid: 110 290 comment: 291 homedir: /var/run/dbus 292 shell: /bin/false 293 *************************** 26. row *************************** 294 username: uuidd 295 password: x 296 uid: 107 297 gid: 111 298 comment: 299 homedir: /run/uuidd 300 shell: /bin/false 301 *************************** 27. row *************************** 302 username: lightdm 303 password: x 304 uid: 108 305 gid: 114 306 comment: Light Display Manager 307 homedir: /var/lib/lightdm 308 shell: /bin/false 309 *************************** 28. row *************************** 310 username: whoopsie 311 password: x 312 uid: 109 313 gid: 116 314 comment: 315 homedir: /nonexistent 316 shell: /bin/false 317 *************************** 29. row *************************** 318 username: avahi-autoipd 319 password: x 320 uid: 110 321 gid: 119 322 comment: Avahi autoip daemon,,, 323 homedir: /var/lib/avahi-autoipd 324 shell: /bin/false 325 *************************** 30. row *************************** 326 username: avahi 327 password: x 328 uid: 111 329 gid: 120 330 comment: Avahi mDNS daemon,,, 331 homedir: /var/run/avahi-daemon 332 shell: /bin/false 333 *************************** 31. row *************************** 334 username: dnsmasq 335 password: x 336 uid: 112 337 gid: 65534 338 comment: dnsmasq,,, 339 homedir: /var/lib/misc 340 shell: /bin/false 341 *************************** 32. row *************************** 342 username: colord 343 password: x 344 uid: 113 345 gid: 123 346 comment: colord colour management daemon,,, 347 homedir: /var/lib/colord 348 shell: /bin/false 349 *************************** 33. row *************************** 350 username: speech-dispatcher 351 password: x 352 uid: 114 353 gid: 29 354 comment: Speech Dispatcher,,, 355 homedir: /var/run/speech-dispatcher 356 shell: /bin/false 357 *************************** 34. row *************************** 358 username: hplip 359 password: x 360 uid: 115 361 gid: 7 362 comment: HPLIP system user,,, 363 homedir: /var/run/hplip 364 shell: /bin/false 365 *************************** 35. row *************************** 366 username: kernoops 367 password: x 368 uid: 116 369 gid: 65534 370 comment: Kernel Oops Tracking Daemon,,, 371 homedir: / 372 shell: /bin/false 373 *************************** 36. row *************************** 374 username: pulse 375 password: x 376 uid: 117 377 gid: 124 378 comment: PulseAudio daemon,,, 379 homedir: /var/run/pulse 380 shell: /bin/false 381 *************************** 37. row *************************** 382 username: rtkit 383 password: x 384 uid: 118 385 gid: 126 386 comment: RealtimeKit,,, 387 homedir: /proc 388 shell: /bin/false 389 *************************** 38. row *************************** 390 username: saned 391 password: x 392 uid: 119 393 gid: 127 394 comment: 395 homedir: /var/lib/saned 396 shell: /bin/false 397 *************************** 39. row *************************** 398 username: usbmux 399 password: x 400 uid: 120 401 gid: 46 402 comment: usbmux daemon,,, 403 homedir: /var/lib/usbmux 404 shell: /bin/false 405 *************************** 40. row *************************** 406 username: tarena 407 password: x 408 uid: 1000 409 gid: 1000 410 comment: tarena,,, 411 homedir: /home/tarena 412 shell: /bin/bash 413 *************************** 41. row *************************** 414 username: sshd 415 password: x 416 uid: 121 417 gid: 65534 418 comment: 419 homedir: /var/run/sshd 420 shell: /usr/sbin/nologin 421 *************************** 42. row *************************** 422 username: mysql 423 password: x 424 uid: 122 425 gid: 129 426 comment: MySQL Server,,, 427 homedir: /nonexistent 428 shell: /bin/false 429 *************************** 43. row *************************** 430 username: mongodb 431 password: x 432 uid: 123 433 gid: 65534 434 comment: 435 homedir: /var/lib/mongodb 436 shell: /bin/false 437 *************************** 44. row *************************** 438 username: redis 439 password: x 440 uid: 124 441 gid: 131 442 comment: 443 homedir: /var/lib/redis 444 shell: /bin/false 445 44 rows in set (0.00 sec) 446 447 ERROR: 448 No query specified 449 450 mysql> delect from userinfo; 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 from userinfo; 453 Query OK, 44 rows affected (0.05 sec) 454 455 mysql> desc userinfo; 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.00 sec) 468 469 mysql> alter table userinfo add 'ziduan' 'shujuleixing' ater \c 470 mysql> 471 mysql> select * from userinfo; 472 Empty set (0.00 sec) 473 474 mysql> 475 mysql> desc userinfo; 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.00 sec) 488 489 mysql> load date infile '/var/lib/mysql-files/passwd' 490 -> into table userinfo 491 -> 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 userinfo 496 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.06 sec) 499 Records: 44 Deleted: 0 Skipped: 0 Warnings: 0 500 501 mysql> 502 mysql> 503 mysql> select * from userinfo; 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.00 sec) 553 554 mysql>
数据导出
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 from userinfo; 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.00 sec) 51 52 mysql> select username,password,uid from userinfo 53 -> 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 from userinfo 61 -> 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.04 sec) 66 67 mysql> 68 root@tedu:/var/lib/mysql-files# ls 69 passwd userinfo.txt 70 root@tedu:/var/lib/mysql-files# cat userinfo.txt 71 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.01 sec) 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.31 sec) 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.00 sec) 134 135 mysql> 136 root@tedu:/var/lib/mysql-files# ls 137 passwd user2.txt userinfo.txt 138 root@tedu:/var/lib/mysql-files# cat user2.txt 139 debian-sys-maint localhost 140 mysql.session localhost 141 mysql.sys localhost 142 root localhost 143 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.06 sec) 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.00 sec) 168 169 mysql>
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 table userinfo2 2 -> select * from userinfo; 3 Query OK, 44 rows affected (0.58 sec) 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.01 sec) 19 20 mysql> select * from userinfo2; 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 | s 29 ####### 30 mysql> create table userinfo3 31 -> select * from userinfo limit 10; 32 Query OK, 10 rows affected (25.42 sec) 33 Records: 10 Duplicates: 0 Warnings: 0 34 35 mysql> create table userinfo4 36 -> select * from userinof limit 1,9; 37 ERROR 1146 (42S02): Table 'db3.userinof' doesn't exist 38 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: 0 41 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: 0 71 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: 0 85 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>
嵌套查询
1、定义
把内层的查询结果作为外层查询的条件
2、语法格式
select 查询语句 where 条件 (select 查询语句);
3、练习
1、把uid的值小于这个字段的平均值的用户名和uid显示出来
2、查找userinfo表中用户名在 mysql库下的user表Host值为localhost并且User值是root 的用户名
1 mysql> select avg(uid) from userinfo; 2 +-----------+ 3 | avg(uid) | 4 +-----------+ 5 | 1581.5227 | 6 +-----------+ 7 1 row in set (0.11 sec) 8 9 mysql> select username,uid from userinfo 10 -> 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.00 sec) 60 61 mysql> 62 mysql> select username,uid from userinfo 63 -> where 64 -> uid < (select avg(uid) from userinfo); 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.08 sec) 113 114 mysql> 115 mysql> select username from userinfo 116 -> 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.08 sec) 124 125 mysql>
test
多表查询
1、两种方式
1、select 字段名列表 from 表名列表; #笛卡尔积
select * from tt1,tt2;
2、select 字段名列表 from 表名列表 where 条件;
2、练习
1、显示省和市的信息
2、显示省、市、县的信息
1 mysql> 1,user1 包含 username uid shell 前两条\c 2 mysql> 3 mysql> create table tt1 4 -> select username,uid,shell from userinfo 5 -> limit 2; 6 Query OK, 2 rows affected (0.46 sec) 7 Records: 2 Duplicates: 0 Warnings: 0 8 9 mysql> 2, tt2 包含 username uid gid 前３条\c 10 mysql> create table tt2 11 -> select username,uid,gid from userinfo 12 -> limit 3; 13 Query OK, 3 rows affected (0.69 sec) 14 Records: 3 Duplicates: 0 Warnings: 0 15 16 mysql> select * from tt1; 17 +----------+------+-------------------+ 18 | username | uid | shell | 19 +----------+------+-------------------+ 20 | root | 0 | /bin/bash | 21 | daemon | 1 | /usr/sbin/nologin | 22 +----------+------+-------------------+ 23 2 rows in set (0.00 sec) 24 25 mysql> select * from tt2; 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.00 sec) 34 35 mysql> select * from tt1,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.02 sec) 47 48 mysql> 1,tt1 tt2 表中，ｕｉｄ号相同的信息\c 49 mysql> select * from tt1, tt2 50 -> 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.03 sec) 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.00 sec) 86 87 mysql> select * from sheng; 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.00 sec) 98 99 mysql> select * from city; 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.01 sec) 117 118 mysql> select * from xian; 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.00 sec) 129 130 mysql> 1、显示省和市的信息\c 131 mysql> 132 mysql> select sheng.S_name,city.C_name 133 -> from sheng,city 134 -> 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.00 sec) 150 151 mysql> 2、显示省、市、县的信息\c 152 mysql> select sheng.S_name,city.C_name,X_name 153 -> from sheng,city,xian 154 -> 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.00 sec) 165 166 mysql>
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.00 sec) 23 24 mysql> select * from sheng; 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.00 sec) 35 36 mysql> select * from city; 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.01 sec) 54 55 mysql> select * from xian; 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.00 sec) 66 67 mysql> 1、显示省和市的信息\c 68 mysql> 69 mysql> select sheng.S_name,city.C_name 70 -> from sheng,city 71 -> 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.00 sec) 87 88 mysql> 2、显示省、市、县的信息\c 89 mysql> select sheng.S_name,city.C_name,X_name 90 -> from sheng,city,xian 91 -> 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.00 sec) 102 103 mysql> 内连接 104 -> 1、显示省市信息,没有匹配的不显示\c 105 mysql> select sheng.S_name,city.C_name 106 -> from 107 -> sheng 108 -> inner join city 109 -> 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.00 sec) 124 125 mysql> 126 mysql> 2、显示省市县的信息\c 127 mysql> select sheng.S_name,city.C_name,xian.X_name 128 -> from sheng inner join city 129 -> on sheng.S_ID=city.CFather_ID 130 -> 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.00 sec) 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.04 sec) 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.00 sec) 162 163 mysql> 外连接-左连接 164 -> 1、显示省市的信息,以左表为准\c 165 mysql> select sheng.S_name,city.C_name from sheng 166 -> left join city 167 -> 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.00 sec) 183 184 mysql> 外连接-右连接\c 185 mysql> select sheng.S_name,city.C_name from sheng 186 -> right join city 187 -> 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.00 sec) 205 206 mysql> 3、显示省市区的信息,要求市全部显示\c 207 mysql> select sheng.S_name,city.C_name,xian.X_name from sheng 208 -> right join city on sheng.S_ID=city.CFather_ID 209 -> 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.00 sec) 229 230 mysql>
