nysql使用09

mysql> #查询操作    (物理查询(代表mysql数据库是如何得到该结果的)  逻辑查询(表示执行查询该产生什么样的结果))
mysql> #现在创建两个表  说明查询的一般步骤
mysql> show tables;
ERROR 1046 (3D000): No database selected
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              |
| charTest       |
| employees      |
| new_emp        |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| updatetime     |
| yeartest       |
| z              |
+----------------+
14 rows in set (0.00 sec)

mysql> create table customers()
    -> ;
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 ')' at line 1
mysql> create table customers()
    -> ;
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 ')' at line 1
mysql> create table customers(
    -> customer_id varchar(10) not null,
    -> city varchar(10) not null,
    -> primary key(customer_id))engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into customers values('Jack1','HangZhou');
Query OK, 1 row affected (0.03 sec)

mysql> insert into customers values('9You','ShangHai');
Query OK, 1 row affected (0.04 sec)

mysql> insert into customers values('TX','HangZhou');
Query OK, 1 row affected (0.03 sec)

mysql> insert into customers values('baidu','HangZhou');
Query OK, 1 row affected (0.04 sec)

mysql>
mysql> create table orders(
    -> order_id int primary ket not null auto_increment,
    -> customer_id varchar(10))engine=innodb;
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 'ket not null auto_increment,
customer_id varchar(10))engine=innodb' at line 2
mysql> create table orders( order_id int primary key not null auto_increment, customer_id varchar(10))engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into orders select NULL,'Jack1';
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into orders select NULL,'Jack1';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into orders select NULL,'9You';
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into orders select NULL,'9You';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into orders select NULL,'9You';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into orders select NULL,'TX';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into orders select NULL,NULL;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

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 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> #现在通过语句建立来自杭州的订单少于2的客户  并且查出踏慢的订单数量 查询结果按照订单数从小到大排序
mysql>
mysql> #首先进行分析   条件：HangZhou  从小到大:order by排序  小于2的:having筛选
mysql> #两个表的相连 可以使用左连接
mysql>
mysql> select c.customer_id, count(o.order_id) as total_orders
    -> from customers as c
    -> left join orders as o
    -> on c.customers_id = o.customers_id
    -> where c.city = 'HangZhou'
    -> group by c.customer_id
    -> having count(o.order_id)<2
    -> order by total_orders desc;
ERROR 1054 (42S22): Unknown column 'c.customers_id' in 'on clause'
mysql> select c.customer_id, count(o.order_id) as total_orders
    -> from customers as c
    -> left join orders as o
    -> on c.customer_id = o.customer_id
    -> where c.city = 'HangZhou'
    -> group by c.customer_id
    -> having count(o.order_id)<2
    -> order by total_orders desc;
+-------------+--------------+
| customer_id | total_orders |
+-------------+--------------+
| TX          |            1 |
| baidu       |            0 |
+-------------+--------------+
2 rows in set (0.00 sec)

mysql> #ok  现在查询到所有数据了
mysql>
mysql> #group by子句把所有的NULL 值分到一组
mysql> #order by子句把所有的NULL 值进行排列在一起
mysql>
mysql> create table ttt(a char(5))engine=innodb;
Query OK, 0 rows affected (0.11 sec)

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

mysql> insert into ttt select NULL;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

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

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

mysql> insert into ttt select NULL;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from ttt;
+------+
| a    |
+------+
| a    |
| NULL |
| b    |
| c    |
| NULL |
+------+
5 rows in set (0.01 sec)

mysql> select a, count(1) from ttt group by a;
+------+----------+
| a    | count(1) |
+------+----------+
| NULL |        2 |
| a    |        1 |
| b    |        1 |
| c    |        1 |
+------+----------+
4 rows in set (0.00 sec)

mysql> select * from customers c
    -> left join orders o
    -> on c.customer_id=o.customer_id and c.city='HangZhou';
+-------------+----------+----------+-------------+
| customer_id | city     | order_id | customer_id |
+-------------+----------+----------+-------------+
| 9You        | ShangHai |     NULL | NULL        |
| baidu       | HangZhou |     NULL | NULL        |
| Jack1       | HangZhou |        1 | Jack1       |
| Jack1       | HangZhou |        2 | Jack1       |
| TX          | HangZhou |        6 | TX          |
+-------------+----------+----------+-------------+
5 rows in set (0.00 sec)

mysql> #还是会有别的城市额。。。。。
mysql> #因为是left join对保留表中被排除的记录进行再次的添加操作
mysql>
mysql> #如果在查询中使用了distinct子句，则会创建一张内存临时表  这张内存临时表结构和上一步产生的虚拟表一样 但是distinct却增加了一个唯一索引 以此来除去重复数据   
mysql>
mysql> exit