mysql的使用16

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| MonthlyOrders  |
| Nums           |
| a              |
| animals        |
| charTest       |
| customers      |
| dept_manager   |
| emp            |
| employees      |
| g              |
| mintable       |
| new_emp        |
| orders         |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| ttt            |
| updatetime     |
| x              |
| y              |
| yeartest       |
| z              |
+----------------+
25 rows in set (0.00 sec)

mysql> select * from x union select * from y;
+------+
| a    |
+------+
| a    |
| b    |
| c    |
| w    |
| q    |
+------+
5 rows in set (0.01 sec)

mysql> select * from x;
+------+
| a    |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

mysql> select * from y;
+------+
| a    |
+------+
| a    |
| b    |
| w    |
| q    |
+------+
4 rows in set (0.00 sec)

mysql> #由此可以看出数据重复的部分去除了
mysql>
mysql> #mysql数据库对union distinct的实现方式如下
mysql> #1 创建一张临时表，即虚拟表
mysql> #2 对这张临时表的列添加唯一索引
mysql> #3 将输入的数据插入临时表
mysql> #4 返回虚拟表
mysql>
mysql> select a from x union select a from y;
+------+
| a    |
+------+
| a    |
| b    |
| c    |
| w    |
| q    |
+------+
5 rows in set (0.00 sec)

mysql> #现在我们 将重复选项剔除
mysql> select * from x union all
    -> select * from y;
+------+
| a    |
+------+
| a    |
| b    |
| c    |
| a    |
| b    |
| w    |
| q    |
+------+
7 rows in set (0.00 sec)

mysql> #except
mysql> #except集合操作允许用户找出位于第一个输入中但不属于第二个输入中的行数据
mysql> #except也可以分为except distinct 和 except all
mysql>
mysql> #以上也可使用那个left join 或者not exists
mysql>
mysql> select x.a from x left join y
    -> on x.a = y.a
    -> where y.a is null;
+------+
| a    |
+------+
| c    |
+------+
1 row in set (0.00 sec)

mysql> #x表中c是y表中没有的
mysql>
mysql> select a from x where not exists(select * from y where x.a=y.a);
+------+
| a    |
+------+
| c    |
+------+
1 row in set (0.00 sec)

mysql> create table xx(a char(1), b char(1))engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> create table yy(a char(1), b char(1))engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into xx select 'a','b';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into xx select b',NULL;
    '> insert into xx select 'c','d';
    '> insert into xx select 'c','d';
    '> insert into xx select 'c','d';
    '> insert into xx select 'c','c';
    '> insert into xx select 'e','f';
    '>
    '>
    '> insert into yy select 'a','b';
    '> insert into yy select 'b',NULL;
    '> insert into yy select 'c','d';
    '> insert into yy select 'c','c';
    '> ;
    '> '
    -> ';
    '> exit
    '> ;
    '> exit
    '> exit
    '> Ctrl-C -- exit!
Aborted