mysql 基本操作２

１，select 查找数据

创建stu列表，插入数据结果如下：

mysql> select * from stu; ＃读取列表stu中所有数据
+------+-----------------------+------------+
| name | addr                  | birthday   |
+------+-----------------------+------------+
| 丁   | 海南省海口市          | 1995-08-12 |
| 丙   | 江苏省南京市          | 1997-12-22 |
| 乙   | 北京市东城区          | 1997-09-22 |
| 己   | 河北省石家庄市        | 1996-09-02 |
| 戊   | 四川省成都市          | 1996-09-02 |
| 甲   | 浙江省杭州市          | 1998-02-22 |
+------+-----------------------+------------+
6 rows in set (0.00 sec)

1.1,查找列表中的所有字段

select * from table_name;#例如上面

1.2 查找列表某一特定字段的所有值

select field1,field2,.. from table_name;

mysql> select name from stu;
+------+
| name |
+------+
| 丁   |
| 丙   |
| 乙   |
| 己   |
| 戊   |
| 甲   |
+------+
6 rows in set (0.00 sec)

1.3,查找列表某一条件下的特定字段的值 where条件语句

select field1,field2... from table_name where field;

mysql> select name,birthday from stu where addr ='海南省海口市';
+------+------------+
| name | birthday   |
+------+------------+
| 丁   | 1995-08-12 |
+------+------------+
1 row in set (0.00 sec)

1.4, 模糊选择 like字句

select field1... from table_name where filed like '%';

mysql> select name,birthday from stu where addr  like '北京%';
+------+------------+
| name | birthday   |
+------+------------+
| 乙   | 1997-09-22 |
+------+------------+
1 row in set (0.00 sec)

 2,链接　join

其实就是类似数学集合的交并集

首先建立两个表格

mysql> select * from class;
+----+------+----------+
| id | name | class_id |
+----+------+----------+
|  1 | 戌   |        3 |
|  2 | 丁   |        5 |
|  3 | 丙   |        3 |
|  4 | 甲   |        6 |
|  5 | 戊   |        6 |
|  6 | 乙   |        6 |
+----+------+----------+
6 rows in set (0.00 sec)

mysql> select * from stu;
+------+-----------------------+------------+
| name | addr                  | birthday   |
+------+-----------------------+------------+
| 丁   | 海南省海口市          | 1995-08-12 |
| 丙   | 江苏省南京市          | 1997-12-22 |
| 乙   | 北京市东城区          | 1997-09-22 |
| 己   | 河北省石家庄市        | 1996-09-02 |
| 戊   | 四川省成都市          | 1996-09-02 |
| 甲   | 浙江省杭州市          | 1998-02-22 |
+------+-----------------------+------------+
6 rows in set (0.00 sec)

 

2.1 inner join

取两个数据表中的交集字段信息，就是求交集,然后根据需求显示交集结果．

select field1... from table1 inner join table2 on table1.field=table2.field;  

mysql> select * from class inner join stu on class.name=stu.name;
+----+------+----------+------+--------------------+------------+
| id | name | class_id | name | addr               | birthday   |
+----+------+----------+------+--------------------+------------+
|  2 | 丁   |        5 | 丁   | 海南省海口市       | 1995-08-12 |
|  3 | 丙   |        3 | 丙   | 江苏省南京市       | 1997-12-22 |
|  4 | 甲   |        6 | 甲   | 浙江省杭州市       | 1998-02-22 |
|  5 | 戊   |        6 | 戊   | 四川省成都市       | 1996-09-02 |
|  6 | 乙   |        6 | 乙   | 北京市东城区       | 1997-09-22 |
+----+------+----------+------+--------------------+------------+
5 rows in set (0.00 sec)

mysql> select class_id from class inner join stu on class.name=stu.name;　＃显示两个表中名字相同字段下，class表下的class_id.
+----------+
| class_id |
+----------+
|        5 |
|        3 |
|        6 |
|        6 |
|        6 |
+----------+
5 rows in set (0.00 sec

2.2 　left join

以左边表的数据为基准，左边的表全有，右边的表取左边表中有的．

select * from table1 left join table2 on table1.field=table2.field;

mysql> select * from class left join stu on class.name=stu.name;
+----+------+----------+------+--------------------+------------+
| id | name | class_id | name | addr               | birthday   |
+----+------+----------+------+--------------------+------------+
|  1 | 戌   |        3 | NULL | NULL               | NULL       |
|  2 | 丁   |        5 | 丁   | 海南省海口市       | 1995-08-12 |
|  3 | 丙   |        3 | 丙   | 江苏省南京市       | 1997-12-22 |
|  4 | 甲   |        6 | 甲   | 浙江省杭州市       | 1998-02-22 |
|  5 | 戊   |        6 | 戊   | 四川省成都市       | 1996-09-02 |
|  6 | 乙   |        6 | 乙   | 北京市东城区       | 1997-09-22 |
+----+------+----------+------+--------------------+------------+
6 rows in set (0.00 sec)

2.3 right join

与left join 相反，取右边表中全部，左边表中取右边有的

mysql> select * from class right join stu on class.name=stu.name;
+------+------+----------+------+-----------------------+------------+
| id   | name | class_id | name | addr                  | birthday   |
+------+------+----------+------+-----------------------+------------+
|    2 | 丁   |        5 | 丁   | 海南省海口市          | 1995-08-12 |
|    3 | 丙   |        3 | 丙   | 江苏省南京市          | 1997-12-22 |
|    4 | 甲   |        6 | 甲   | 浙江省杭州市          | 1998-02-22 |
|    5 | 戊   |        6 | 戊   | 四川省成都市          | 1996-09-02 |
|    6 | 乙   |        6 | 乙   | 北京市东城区          | 1997-09-22 |
| NULL | NULL |     NULL | 己   | 河北省石家庄市        | 1996-09-02 |
+------+------+----------+------+-----------------------+------------+
6 rows in set (0.00 sec)