mysql的使用11

mysql> #exists是一个非常强大的谓词 它允许数据库高效的检查指定查询是否产生某些行
mysql> #通常exists的输入是一个子查询 并关联到外部查询
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 |
+----------------+
| Nums           |
| a              |
| animals        |
| charTest       |
| customers      |
| employees      |
| new_emp        |
| orders         |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| ttt            |
| updatetime     |
| yeartest       |
| z              |
+----------------+
18 rows in set (0.00 sec)

mysql> desc sales;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11)          | NO   | PRI | NULL    | auto_increment |
| date  | datetime         | NO   |     | NULL    |                |
| cost  | int(10) unsigned | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> desc orders;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| order_id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| customer_id | varchar(10) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> desc customers;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| customer_id | varchar(10) | NO   | PRI | NULL    |       |
| city        | varchar(10) | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from customers;
+-------------+----------+
| customer_id | city     |
+-------------+----------+
| 9You        | ShangHai |
| baidu       | HangZhou |
| Jack1       | HangZhou |
| TX          | HangZhou |
+-------------+----------+
4 rows in set (0.00 sec)

mysql> select * from orsers;
ERROR 1146 (42S02): Table 'test.orsers' doesn't exist
mysql> select * from orders;
+----------+-------------+
| order_id | customer_id |
+----------+-------------+
|        1 | Jack1       |
|        2 | Jack1       |
|        3 | 9You        |
|        4 | 9You        |
|        5 | 9You        |
|        6 | TX          |
|        7 | NULL        |
+----------+-------------+
7 rows in set (0.00 sec)

mysql> select customer_id,city from customers as c where city='HangZhou' and exists (select * from orders as o where o.customer_id = c.customer_id);
+-------------+----------+
| customer_id | city     |
+-------------+----------+
| Jack1       | HangZhou |
| TX          | HangZhou |
+-------------+----------+
2 rows in set (0.00 sec)

mysql> select customer_id,city from customers as c where city='HangZhou' and customer_id in (select * from orders as o where o.customer_id = c.customer_id);
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql> select customer_id,city from customers as c where city='HangZhou' and customer_id in (select customer_id from orders);
+-------------+----------+
| customer_id | city     |
+-------------+----------+
| Jack1       | HangZhou |
| TX          | HangZhou |
+-------------+----------+
2 rows in set (0.00 sec)

mysql> #这里使用和in等价的语句
mysql> #当然还有 not exists 这里就不再介绍了
mysql>
mysql>
mysql> #派生表
mysql> #派生表又被称为表子查询 与其他表一样出现在from的子句中，但是从子查询派生出的虚拟表中产生的
mysql> #规则有:
mysql> #列的名称必须是唯一的
mysql> #在某些情况下不支持limit
mysql> select 'c' as a, 'b' as a;
+---+---+
| a | a |
+---+---+
| c | b |
+---+---+
1 row in set (0.00 sec)

mysql> select * from( select 'c' as a, 'b' as a);
ERROR 1248 (42000): Every derived table must have its own alias
mysql>
mysql>
mysql> #子查询可以解决的经典问题
mysql> #行号
mysql> drop table sales;
Query OK, 0 rows affected (0.06 sec)

mysql> creat table sales(
    -> empid varchar(10) not null,
    -> mgrid varchar(10) not null,
    -> qty int not null,
    -> primary key(empid));
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 'creat table sales(
empid varchar(10) not null,
mgrid varchar(10) not null,
qty i' at line 1
mysql>
mysql> creat table sales( empid varchar(10) primary key not null, mgrid varchar(10) not null, qty int not null);
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 'creat table sales( empid varchar(10) primary key not null, mgrid varchar(10) not' at line 1
mysql> create table sales( empid varchar(10) primary key not null, mgrid varchar(10) not null, qty int not null);
Query OK, 0 rows affected (0.09 sec)

mysql> #create写错了   我去！！！
mysql>
mysql> insert into sales values('A','Z',300);
Query OK, 1 row affected (0.03 sec)

mysql> insert into sales values('B','X',100);
Query OK, 1 row affected (0.05 sec)

mysql> insert into sales values('C','Y',200);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales values('D','Y',200);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales values('E','Z',250);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales values('F','Z',300);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales values('G','X',100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into sales values('H','Y',250);
Query OK, 1 row affected (0.03 sec)

mysql> insert into sales values('I','Z',100);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales values('J','Z',100);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales values('K','Y',300);
Query OK, 1 row affected (0.05 sec)

mysql> select * from sales;
+-------+-------+-----+
| empid | mgrid | qty |
+-------+-------+-----+
| A     | Z     | 300 |
| B     | X     | 100 |
| C     | Y     | 200 |
| D     | Y     | 200 |
| E     | Z     | 250 |
| F     | Z     | 300 |
| G     | X     | 100 |
| H     | Y     | 250 |
| I     | Z     | 100 |
| J     | Z     | 100 |
| K     | Y     | 300 |
+-------+-------+-----+
11 rows in set (0.00 sec)

mysql> update sales set mgrid = 'X' where empid='C';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update sales set qty = 200 where empid='K';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from sales;
+-------+-------+-----+
| empid | mgrid | qty |
+-------+-------+-----+
| A     | Z     | 300 |
| B     | X     | 100 |
| C     | X     | 200 |
| D     | Y     | 200 |
| E     | Z     | 250 |
| F     | Z     | 300 |
| G     | X     | 100 |
| H     | Y     | 250 |
| I     | Z     | 100 |
| J     | Z     | 100 |
| K     | Y     | 200 |
+-------+-------+-----+
11 rows in set (0.00 sec)

mysql> update sales set mgrid = 'X' where empid='I';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> #现在来统计行号
mysql> select empid, (select count(*) from sales as t2 where
    -> te.empid <= t1.empid) as rownum from sales as t1;
ERROR 1054 (42S22): Unknown column 'te.empid' in 'where clause'
mysql> select empid, (select count(*) from sales as t2 where t2.empid <= t1.empid) as rownum from sales as t1;
+-------+--------+
| empid | rownum |
+-------+--------+
| A     |      1 |
| B     |      2 |
| C     |      3 |
| D     |      4 |
| E     |      5 |
| F     |      6 |
| G     |      7 |
| H     |      8 |
| I     |      9 |
| J     |     10 |
| K     |     11 |
+-------+--------+
11 rows in set (0.00 sec)

mysql> #可以看出总共有11行数据
mysql> #现在对qty进行排序  并生成序号
mysql>
mysql> select empid,qty,(
    -> select count(*) from sales as t2 where t2.qty<t1.qty
    -> or (t2.qty=t1.qty and t2.empid <= t1.empid)) as rownum from sales as t1
    -> order by qty,empid;
+-------+-----+--------+
| empid | qty | rownum |
+-------+-----+--------+
| B     | 100 |      1 |
| G     | 100 |      2 |
| I     | 100 |      3 |
| J     | 100 |      4 |
| C     | 200 |      5 |
| D     | 200 |      6 |
| K     | 200 |      7 |
| E     | 250 |      8 |
| H     | 250 |      9 |
| A     | 300 |     10 |
| F     | 300 |     11 |
+-------+-----+--------+
11 rows in set (0.00 sec)

mysql> exit