复制表
1、把整张表，包括数据，全部复制
mysql> create table tea8 select * from tea7;
Query OK, 8 rows affected (0.32 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from tea8;
+—-+—–+——–+———+————-+
| id | age | name | sex | hobby |
+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it |
| 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep |
| 7 | 21 | jimmy | boy | eat,it,girl |
| 8 | 16 | jimmy | boy | sleep,girl |
| 9 | 35 | kitty | girl | eat,sleep |
+—-+—–+——–+———+————-+
8 rows in set (0.00 sec)
2、只复制表的结构
mysql> create table tea71 select * from tea7 where false;
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from tea71;
Empty set (0.00 sec)
mysql> desc tea71;
+——-+—————————————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+—————————————+——+—–+———+——-+
| id | int(5) | NO | | 0 | |
| age | int(3) | NO | | 18 | |
| name | char(10) | NO | | NULL | |
| sex | enum(‘boy’,’girl’,’secrect’) | YES | | girl | |
| hobby | set(‘eat’,’sleep’,’game’,’it’,’girl’) | YES | | NULL | |
+——-+—————————————+——+—–+———+——-+
5 rows in set (0.00 sec)
多表查询
mysql> select * from tea7,tea8;
+—-+—–+——–+———+————-+—-+—–+——–+———+————-+
| id | age | name | sex | hobby | id | age | name | sex | hobby |
+—-+—–+——–+———+————-+—-+—–+——–+———+————-+
| 1 | 18 | lucy | girl | game | 1 | 18 | lucy | girl | game |
| 3 | 18 | bob | secrect | eat,it,girl | 1 | 18 | lucy | girl | game |
| 4 | 18 | lily | girl | eat,it | 1 | 18 | lucy | girl | game |
| 5 | 22 | tarena | girl | eat,sleep | 1 | 18 | lucy | girl | game |
| 6 | 25 | kitty | girl | sleep | 1 | 18 | lucy | girl | game |
| 7 | 21 | jimmy | boy | eat,it,girl | 1 | 18 | lucy | girl | game |
| 8 | 16 | jimmy | boy | sleep,girl | 1 | 18 | lucy | girl | game |
| 9 | 35 | kitty | girl | eat,sleep | 1 | 18 | lucy | girl | game |
| 1 | 18 | lucy | girl | game | 3 | 18 | bob | secrect | eat,it,girl |
| 3 | 18 | bob | secrect | eat,it,girl | 3 | 18 | bob | secrect | eat,it,girl |
| 4 | 18 | lily | girl | eat,it | 3 | 18 | bob | secrect | eat,it,girl |
| 5 | 22 | tarena | girl | eat,sleep | 3 | 18 | bob | secrect | eat,it,girl |
| 6 | 25 | kitty | girl | sleep | 3 | 18 | bob | secrect | eat,it,girl |
| 7 | 21 | jimmy | boy | eat,it,girl | 3 | 18 | bob | secrect | eat,it,girl |
| 8 | 16 | jimmy | boy | sleep,girl | 3 | 18 | bob | secrect | eat,it,girl |
| 9 | 35 | kitty | girl | eat,sleep | 3 | 18 | bob | secrect | eat,it,girl |
| 1 | 18 | lucy | girl | game | 4 | 18 | lily | girl | eat,it |
| 3 | 18 | bob | secrect | eat,it,girl | 4 | 18 | lily | girl | eat,it |
| 4 | 18 | lily | girl | eat,it | 4 | 18 | lily | girl | eat,it |
| 5 | 22 | tarena | girl | eat,sleep | 4 | 18 | lily | girl | eat,it |
| 6 | 25 | kitty | girl | sleep | 4 | 18 | lily | girl | eat,it |
| 7 | 21 | jimmy | boy | eat,it,girl | 4 | 18 | lily | girl | eat,it |
| 8 | 16 | jimmy | boy | sleep,girl | 4 | 18 | lily | girl | eat,it |
| 9 | 35 | kitty | girl | eat,sleep | 4 | 18 | lily | girl | eat,it |
| 1 | 18 | lucy | girl | game | 5 | 22 | tarena | girl | eat,sleep |
| 3 | 18 | bob | secrect | eat,it,girl | 5 | 22 | tarena | girl | eat,sleep |
| 4 | 18 | lily | girl | eat,it | 5 | 22 | tarena | girl | eat,sleep |
| 5 | 22 | tarena | girl | eat,sleep | 5 | 22 | tarena | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep | 5 | 22 | tarena | girl | eat,sleep |
| 7 | 21 | jimmy | boy | eat,it,girl | 5 | 22 | tarena | girl | eat,sleep |
| 8 | 16 | jimmy | boy | sleep,girl | 5 | 22 | tarena | girl | eat,sleep |
| 9 | 35 | kitty | girl | eat,sleep | 5 | 22 | tarena | girl | eat,sleep |
| 1 | 18 | lucy | girl | game | 6 | 25 | kitty | girl | sleep |
| 3 | 18 | bob | secrect | eat,it,girl | 6 | 25 | kitty | girl | sleep |
| 4 | 18 | lily | girl | eat,it | 6 | 25 | kitty | girl | sleep |
| 5 | 22 | tarena | girl | eat,sleep | 6 | 25 | kitty | girl | sleep |
| 6 | 25 | kitty | girl | sleep | 6 | 25 | kitty | girl | sleep |
| 7 | 21 | jimmy | boy | eat,it,girl | 6 | 25 | kitty | girl | sleep |
| 8 | 16 | jimmy | boy | sleep,girl | 6 | 25 | kitty | girl | sleep |
| 9 | 35 | kitty | girl | eat,sleep | 6 | 25 | kitty | girl | sleep |
| 1 | 18 | lucy | girl | game | 7 | 21 | jimmy | boy | eat,it,girl |
| 3 | 18 | bob | secrect | eat,it,girl | 7 | 21 | jimmy | boy | eat,it,girl |
| 4 | 18 | lily | girl | eat,it | 7 | 21 | jimmy | boy | eat,it,girl |
| 5 | 22 | tarena | girl | eat,sleep | 7 | 21 | jimmy | boy | eat,it,girl |
| 6 | 25 | kitty | girl | sleep | 7 | 21 | jimmy | boy | eat,it,girl |
| 7 | 21 | jimmy | boy | eat,it,girl | 7 | 21 | jimmy | boy | eat,it,girl |
| 8 | 16 | jimmy | boy | sleep,girl | 7 | 21 | jimmy | boy | eat,it,girl |
| 9 | 35 | kitty | girl | eat,sleep | 7 | 21 | jimmy | boy | eat,it,girl |
| 1 | 18 | lucy | girl | game | 8 | 16 | jimmy | boy | sleep,girl |
| 3 | 18 | bob | secrect | eat,it,girl | 8 | 16 | jimmy | boy | sleep,girl |
| 4 | 18 | lily | girl | eat,it | 8 | 16 | jimmy | boy | sleep,girl |
| 5 | 22 | tarena | girl | eat,sleep | 8 | 16 | jimmy | boy | sleep,girl |
| 6 | 25 | kitty | girl | sleep | 8 | 16 | jimmy | boy | sleep,girl |
| 7 | 21 | jimmy | boy | eat,it,girl | 8 | 16 | jimmy | boy | sleep,girl |
| 8 | 16 | jimmy | boy | sleep,girl | 8 | 16 | jimmy | boy | sleep,girl |
| 9 | 35 | kitty | girl | eat,sleep | 8 | 16 | jimmy | boy | sleep,girl |
| 1 | 18 | lucy | girl | game | 9 | 35 | kitty | girl | eat,sleep |
| 3 | 18 | bob | secrect | eat,it,girl | 9 | 35 | kitty | girl | eat,sleep |
| 4 | 18 | lily | girl | eat,it | 9 | 35 | kitty | girl | eat,sleep |
| 5 | 22 | tarena | girl | eat,sleep | 9 | 35 | kitty | girl | eat,sleep |
| 6 | 25 | kitty | girl | sleep | 9 | 35 | kitty | girl | eat,sleep |
| 7 | 21 | jimmy | boy | eat,it,girl | 9 | 35 | kitty | girl | eat,sleep |
| 8 | 16 | jimmy | boy | sleep,girl | 9 | 35 | kitty | girl | eat,sleep |
| 9 | 35 | kitty | girl | eat,sleep | 9 | 35 | kitty | girl | eat,sleep |
+—-+—–+——–+———+————-+—-+—–+——–+———+————-+
64 rows in set (0.00 sec)
where子查询
把内层查询结果作为外层的查询条件
例如，查出年龄小于平均年龄的学生的姓名和年龄。
mysql> select avg(age) from tea7;
+———-+
| avg(age) |
+———-+
| 21.6250 |
+———-+
1 row in set (0.00 sec)
mysql> select name,age from tea7 where age < (select avg(age) from tea7);
+——-+—–+
| name | age |
+——-+—–+
| lucy | 18 |
| bob | 18 |
| lily | 18 |
| jimmy | 21 |
| jimmy | 16 |
+——-+—–+
5 rows in set (0.00 sec)
内连接、外连接、左连接、右连接
先准备2张表，作为素材
mysql> create table a_t (a_id int(11) default null,a_name varchar(10) default null,a_part varchar(10) default null);;
Query OK, 0 rows affected (0.23 sec)
mysql> create table b_t (b_id int(11) default null,b_name varchar(10) default null,b_part varchar(10) default null);
Query OK, 0 rows affected (0.34 sec)
mysql> desc a_t；
+——–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+——-+
| a_id | int(11) | YES | | NULL | |
| a_name | varchar(10) | YES | | NULL | |
| a_part | varchar(10) | YES | | NULL | |
+——–+————-+——+—–+———+——-+
3 rows in set (0.00 sec)
mysql> desc b_t;
+——–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+——-+
| b_id | int(11) | YES | | NULL | |
| b_name | varchar(10) | YES | | NULL | |
| b_part | varchar(10) | YES | | NULL | |
+——–+————-+——+—–+———+——-+
3 rows in set (0.00 sec)
mysql> insert into a_t values(1,”pan”,”zongcai”),(2,”wang”,”mishu”),(3,”zhang”,”sheji”),(4,”li”,”yunying”);
Query OK, 4 rows affected (0.14 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into b_t values(2,”wang”,”mishu”),(3,”zhang”,”sheji”),(5,”liu”,”renshi”),(6,”huang”,”shengchan”);
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from a_t;
+——+——–+———+
| a_id | a_name | a_part |
+——+——–+———+
| 1 | pan | zongcai |
| 2 | wang | mishu |
| 3 | zhang | sheji |
| 4 | li | yunying |
+——+——–+———+
4 rows in set (0.00 sec)
mysql> select * from b_t;
+——+——–+———–+
| b_id | b_name | b_part |
+——+——–+———–+
| 2 | wang | mishu |
| 3 | zhang | sheji |
| 5 | liu | renshi |
| 6 | huang | shengchan |
+——+——–+———–+
4 rows in set (0.00 sec)
内连接测试
mysql> select * from a_t inner join b_t on a_id=b_id;
+——+——–+——–+——+——–+——–+
| a_id | a_name | a_part | b_id | b_name | b_part |
+——+——–+——–+——+——–+——–+
| 2 | wang | mishu | 2 | wang | mishu |
| 3 | zhang | sheji | 3 | zhang | sheji |
+——+——–+——–+——+——–+——–+
2 rows in set (0.00 sec)
疑问？和select from有什么区别？
mysql> select * from a_t,b_t where a_id=b_id;
+——+——–+——–+——+——–+——–+
| a_id | a_name | a_part | b_id | b_name | b_part |
+——+——–+——–+——+——–+——–+
| 2 | wang | mishu | 2 | wang | mishu |
| 3 | zhang | sheji | 3 | zhang | sheji |
+——+——–+——–+——+——–+——–+
2 rows in set (0.00 sec)
接上面的疑问，查询过程也差不多。
mysql> explain select * from a_t inner join b_t on a_id=b_id;
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
| 1 | SIMPLE | a_t | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | SIMPLE | b_t | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (Block Nested Loop) |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
2 rows in set, 1 warning (0.00 sec)
mysql> explain select * from a_t,b_t where a_id=b_id;
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
| 1 | SIMPLE | a_t | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | SIMPLE | b_t | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using join buffer (Block Nested Loop) |
+—-+————-+——-+————+——+—————+——+———+——+——+———-+—————————————————-+
2 rows in set, 1 warning (0.00 sec)
关键字：inner join on
语句：select * from a_table a inner join b_table bon a.a_id = b.b_id;
说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
左连接
mysql> select * from a_t;
+——+——–+———+
| a_id | a_name | a_part |
+——+——–+———+
| 1 | pan | zongcai |
| 2 | wang | mishu |
| 3 | zhang | sheji |
| 4 | li | yunying |
+——+——–+———+
4 rows in set (0.00 sec)
mysql> select * from b_t;
+——+——–+———–+
| b_id | b_name | b_part |
+——+——–+———–+
| 2 | wang | mishu |
| 3 | zhang | sheji |
| 5 | liu | renshi |
| 6 | huang | shengchan |
+——+——–+———–+
4 rows in set (0.00 sec)
mysql> select * from a_t a left join b_t b on a_id=b_id;
+——+——–+———+——+——–+——–+
| a_id | a_name | a_part | b_id | b_name | b_part |
+——+——–+———+——+——–+——–+
| 2 | wang | mishu | 2 | wang | mishu |
| 3 | zhang | sheji | 3 | zhang | sheji |
| 1 | pan | zongcai | NULL | NULL | NULL |
| 4 | li | yunying | NULL | NULL | NULL |
+——+——–+———+——+——–+——–+
4 rows in set (0.00 sec)
关键字：left join on / left outer join on
语句：select * from a_table a left join b_table bon a.a_id = b.b_id;
说明：
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
–