sql语句的实际应用

一、普通多表连接方式：

select * from 表1,表2...WHERE 表1.d_id = 表2.id;

SELECT * FROM 表1，表2...   (笛卡尔积)

as别名：

SELECT 属性(姓名年龄之类的) FROM 表1 as one,表2 as two WHERE one.d_id = 表2.id;

内连接：查询两张表的公共部分。

SELECT   列名   FROM   表1   INNER   JOIN   表2   ON    表1.列   =   表2.列;

Select * FROM  employe as e INNER JOIN deparment as d ON e.d_id AND e.name = '张三';

(左)外连接：左边表会全被查出来，右边关联的会被查出来

SELECT   e.name,d.deparment_name   FROM   employe as e LEFT OUTER JOIN deparment as dON e.d_id = d.id;SE

(右)外连接：

SELECT e.name,d.deparment_name FROM employe as eRIGHT OUTER JOIN deparment as d ON e.d_id = d.id;

子查询：在查询中还可以有查询

SELECTname as 姓名 FROM student as s WHERE s.d_id = (SELECT id FROM department as d WHERE d.deparment_name = 软件部);

自连接：一张表、必须有id和父id(parent_id)

SELECT s.name as 姓名,f.name as 领导 FROM people as sINNER JOIN people as f ON s.parent_id = f.id AND s.name ='主管';

id

name

Parent_id

 

id

name

1

总

0

1

总

2

经理

1

2

经理

3

主管

2

3

主管

4

高级

3

4

高级

5

初级

4

5

初级

自连接为一张表   原表为左表，右表是抽象象出来的。把左表看成子表，右表看成父表

函数：where后面不能加函数

合并：SELECT * FROM student   GROUP BY age;      (根据年龄相同合并)

合并前：

name

age

number

小明

20

1001

小丽

20

1002

小刚

23

1003

合并后：

name

age

number

小明

20

1001

小刚

23

1003

可以看出，在合并的时候，相同值时，保留第一个。

求和：SELECT SUM(age) as 总年龄FROM student;

求平均：SELECT AVG(age) as 平均年龄 FROM student;

求平均(保留一位)：SELECT ROUND (AVG(age),1) as 平均年龄 FROM student;   (同理保留两位就写2，不保留就是0)

总年龄

 

平均年龄

 

平均年龄

63

 

21.0000000….

 

21.0

求最大：SELECT MAX(age) as 最大年龄 FROM student;

查询有无记录：SELECT COUNT(id)FROM student;             

最大年龄

 

Count(id)

23

 

3

二、sql语句的实际应用：

已知考生表tdb_ks包含：考号 ,姓名,成绩字段

id

number

name

grade

1

1001

小明

89

2

3001

小丽

90

3

2002

张三

88

  1>写出新建考生表SQL语句，要求定义id为主键并且自动增长.

 

mysql> CREATE TABLE tdb_ks(

    -> id int NOT NULL AUTO_INCREMENT PRIMARYKEY,

    -> number int NOTNULL,

    -> name varchar(50)NOT NULL,

    -> grade int NOTNULL );

Query OK, 0 rows affected (0.03 sec)

 

mysql> DESC tdb_ks;

+--------+-------------+------+-----+---------+----------------+

| Field  | Type        | Null | Key | Default | Extra          |

+--------+-------------+------+-----+---------+----------------+

| id     | int(11)     | NO  | PRI | NULL    | auto_increment |

| number | int(11)     |NO   |    | NULL    |                |

| name   | varchar(50) |NO   |    | NULL    |                |

| grade  | int(11)     | NO  |     | NULL    |                |

+--------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

 

   2>向考生表添加记录

 

mysql> INSERT INTO tdb_ks (number,name,grade)VALUES(1001,'小明',89);

mysql> INSERT INTO tdb_ks (number,name,grade)VALUES(1002,'小丽',90);

mysql> INSERT INTO tdb_ks (number,name,grade)VALUES(1003,'张三',88);

mysql> INSERT INTO tdb_ks (number,name,grade)VALUES(1004,'李四',70);

 

   3>查询考生信息

 

mysql> SELECT * FROM tdb_ks;

+----+--------+------+-------+

| id | number | name | grade |

+----+--------+------+-------+

|  1 |   1001 | 小明    |   89 |

|  2 |   1002 | 小丽    |   90 |

|  3 |   1003 | 张三     |  100 |

|  4 |   1004 | 李四     |   70 |

+----+--------+------+-------+

 

   4>根据姓名更改考生信息

mysql> UPDATE tdb_ks SET grade = 100 WHERE name = '张三';

 

mysql> SELECT * FROM tdb_ks;

+----+--------+------+-------+

| id | number | name | grade |

+----+--------+------+-------+

|  1 |   1001 | 小明    |   89 |

|  2 |   1002 | 小丽    |   90 |

|  3 |   1003 | 张三     |  100 |

|  4 |   1004 | 李四     |   70 |

 

 

   5>根据考号删除考生

 

mysql> DELETE FROM tdb_ks WHERE number = 1004;

 

mysql> SELECT * FROM tdb_ks;

+----+--------+------+-------+

| id | number | name | grade |

+----+--------+------+-------+

|  1 |   1001 | 小明    |   89 |

|  2 |   1002 | 小丽    |   90 |

|  3 |   1003 | 张三     |  100 |

+----+--------+------+-------+

  6>查询所有学生总成绩

 

mysql> SELECTSUM(grade) AS 总成绩 FROM tdb_ks;

+--------+

| 总成绩     |

+--------+

|    279 |

+--------+

 

  7>查询高于平均分的所有学生

 

mysql> SELECTROUND(AVG(grade)) AS 平均分 FROM tdb_ks;

+--------+

| 平均分      |

+--------+

|     93 |

+--------+

 

mysql> SELECT *FROM tdb_ks WHERE grade > (SELECT AVG(grade) FROM tdb_ks);

+----+--------+------+-------+

| id | number | name| grade |

+----+--------+------+-------+

|  3 |  1003 | 张三    |   100 |

+----+--------+------+-------+

 

1. 假下有一张订单表信息如下:

id

order_date

Order_price

customer_name

1

2008/12/29

1000

布鲁斯

2

2008/11/23

1600

卡特

3

2008/10/05

700

布鲁斯

4

2008/09/28

500

布鲁斯

5

2008/08/06

2100

安迪

6

2008/07/21

100

卡特

 

 1>查询客户布鲁斯订单总金额.

 

mysql>SELECT custmer, SUM(order_price) FROM orders GROUP BY custmer HAVING custmer ='bush';

+---------+------------------+

|custmer | SUM(order_price) |

+---------+------------------+

|bush    |             2850 |

+---------+------------------+

1 rowin set (0.01 sec)

 

 

 2>查询订单总金额在1800到3000间的客户

 

mysql>SELECT custmer,SUM(order_price) FROM orders GROUP BY custmer HAVINGSUM(order_price) BETWEEN 1800 AND 3000;

+---------+------------------+

|custmer | SUM(order_price) |

+---------+------------------+

|adams   |             2000 |

|bush    |             2850 |

+---------+------------------+

 

4. 有如下职工表

.        

1>查找麻主管的直接领导

mysql>SELECT * FROM people;

+----+--------+-----------+

| id| name   | parent_id |

+----+--------+-----------+

|  1 | 范总       |         0 |

|  2 | 何经理      |         1 |

|  3 | 麻主管       |         2 |

|  4 | 刘主管       |         2 |

|  5 | 李高级      |         3 |

|  6 | 王中级      |         3 |

|  7 | 张初级      |         4 |

+----+--------+-----------+

7rows in set (0.02 sec)

 

mysql>SELECT s.name AS 员工, f.name AS领导 FROM people AS s

    -> INNER JOIN people AS f

    -> ON s.parent_id = f.id

    -> AND s.name = '麻主管';

+--------+--------+

| 员工      |领导     |

+--------+--------+

| 麻主管       |何经理      |

+--------+--------+

1 rowin set (0.00 sec)

3.查询总金额小于2000的人：

Select  customer_name,SUM(Order_price) FROM orders GROUP BY customer_name HAVINGSUM(order_price)<2000;