SQL查询基本结构由三个子语句构成: select from where
以instructor关系为例: 创建 关系并插入数据
mysql> insert into instructor value('10101','Srinivasan','Comp.Sci.',65000);
Query OK, 1 row affected (0.50 sec)
ID | name | dept_name | salary | |
---|---|---|---|---|
10101 | Srinivasan | Comp.Sci. | 65000.00 | |
12121 | Wu | Finance | 90000.00 | |
15151 | Mozart | Music | 40000.00 | |
22222 | Einstein | Physics | 95000.00 | |
32343 | El Said | History | 60000.00 | |
33456 | Gold | Physics | 87000.00 | |
45565 | Katz | Comp.Sci. | 75000.00 | |
58583 | Califieri | History | 62000.00 | |
76543 | Singh | Finance | 80000.00 | |
76766 | Crick | Biology | 72000.00 | |
83821 | Brandt | Comp.Sci. | 92000.00 | |
98345 | Kim | Elec.Eng. | 80000.00 |
(1)单关系查询语句:
找出所有老师的名字:
mysql> select name from instructor;
+------------+
| name |
+------------+
| Srinivasan |
| Wu |
| Mozart |
| Einstein |
| El Said |
| Gold |
| Katz |
| Califieri |
| Singh |
| Crick |
| Brandt |
| Kim |
+------------+
12 rows in set (0.00 sec)
找出所有老师所在系名:
mysql> select dept_name from instructor;
+-----------+
| dept_name |
+-----------+
| Comp.Sci. |
| Finance |
| Music |
| Physics |
| History |
| Physics |
| Comp.Sci. |
| History |
| Finance |
| Biology |
| Comp.Sci. |
| Elec.Eng. |
+-----------+
12 rows in set (0.00 sec)
强行去除重复:
mysql> select distinct dept_name from instructor;
+-----------+
| dept_name |
+-----------+
| Comp.Sci. |
| Finance |
| Music |
| Physics |
| History |
| Biology |
| Elec.Eng. |
+-----------+
7 rows in set (0.00 sec)
select语句可带算术表达式:
mysql> select ID,name,dept_name,salary/12 from instructor;
+-------+------------+-----------+-------------+
| ID | name | dept_name | salary/12 |
+-------+------------+-----------+-------------+
| 10101 | Srinivasan | Comp.Sci. | 5416.666667 |
| 12121 | Wu | Finance | 7500.000000 |
| 15151 | Mozart | Music | 3333.333333 |
| 22222 | Einstein | Physics | 7916.666667 |
| 32343 | El Said | History | 5000.000000 |
| 33456 | Gold | Physics | 7250.000000 |
| 45565 | Katz | Comp.Sci. | 6250.000000 |
| 58583 | Califieri | History | 5166.666667 |
| 76543 | Singh | Finance | 6666.666667 |
| 76766 | Crick | Biology | 6000.000000 |
| 83821 | Brandt | Comp.Sci. | 7666.666667 |
| 98345 | Kim | Elec.Eng. | 6666.666667 |
+-------+------------+-----------+-------------+
12 rows in set (0.00 sec)
select * 表示选择所有属性:
mysql> select * from instructor;
+-------+------------+-----------+----------+
| ID | name | dept_name | salary |
+-------+------------+-----------+----------+
| 10101 | Srinivasan | Comp.Sci. | 65000.00 |
| 12121 | Wu | Finance | 90000.00 |
| 15151 | Mozart | Music | 40000.00 |
| 22222 | Einstein | Physics | 95000.00 |
| 32343 | El Said | History | 60000.00 |
| 33456 | Gold | Physics | 87000.00 |
| 45565 | Katz | Comp.Sci. | 75000.00 |
| 58583 | Califieri | History | 62000.00 |
| 76543 | Singh | Finance | 80000.00 |
| 76766 | Crick | Biology | 72000.00 |
| 83821 | Brandt | Comp.Sci. | 92000.00 |
| 98345 | Kim | Elec.Eng. | 80000.00 |
+-------+------------+-----------+----------+
12 rows in set (0.00 sec)
找出所有在computer science系并且工资超过70000美元的教师名字
mysql> select name from instructor where dept_name='Comp.Sci.' and salary>70000;
+--------+
| name |
+--------+
| Katz |
| Brandt |
+--------+
2 rows in set (0.00 sec)
(2)多关系语句查询