2014-06-13 SQL

SQL

---

数据定义语言 （Data Definition Language）

• CREATE

CREAT DATABASE MyDatabase； 

CREATE TABLE `user` (
    `user_id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(45) NOT NULL,
    `email` varchar(255) NOT NULL,
    PRIMARY KEY (`user_id`),
    UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• DROP

DROP DATABASE MyDatabase; 

• ALTER

---

数据操纵语言 （Data Manipulation Language）

动作	SQL	描述	示例
创建	INSERT	插入新数据	INSERT INTO user VALUES (‘Tom’, 24);
读取	SELECT	读取数据	SELECT * FROM user WHERE name=‘Tom’;
更新	UPDATE	更新数据	UPDATE user SET name = ‘Alex’ WHERE id=1;
删除	DELETE	删除数据	DELETE FROM user WHERE id=1;

---

SQL查询

1. 简单查询

Select * From user Where id = 1；

2. 子查询

Select * From article Where uid In
    (Select uid From user Where status=1)；

3. JOIN

假定有两张表：

A:             B:
Id  Name       Id  Name
--  ----  --  --  -----
1   张三        1   小明
2   李四        2   张三
3   王五        3   小红
4   赵六        4   王五

1) 内连接

内连接只生成表A和表B中匹配的记录的集合。

Select * From A Inner Join B On A.name = B.name

结果：
Id    Name   Id     Name
---    -------   ---     ----
1      张三     2      张三
3      王五     4      王五

2) 全外连接（MySql不支持）

全外连接生成表A和表B中所有记录的集合。如果匹配，两边都有值，否则以null填充。

Select * From A Full Outer Join B On A.name = B.name

结果：
Id    Name      Id     Name
---    -------   ---     ----
1     张三       2      张三
2     李四       null   null
3     王五       4      王五
4     赵六       null   null
null  null       1      小明
null  null       3      小红

3) 左外连接

左外连接生成表A中所有记录和表B中匹配的记录的集合。如果不匹配，右端以null填充。

Select * From A Left Outer Join B On A.name = B.name

结果：
Id  Name      Id     Name
---    -------   ---     -------
1   张三       2      张三
2   李四       null   null
3   王五       4      王五
4   赵六       null   null

为了获得只在Table A而不在Table B中的记录，使用左外连接，通过where语句来排除右边不想要的部分。

Select * From A Left Outer Join B On A.name = B.name 
Where B.id Is null 

结果：
Id  Name      Id     Name
---    -------   ---     -------
2   李四      null   null
4   赵六      null   null

4) 右外连接

Select * From A Right Outer Join B On A.name = B.name

5) 全连接（Full Join）

将两个表中所有记录做一次笛卡尔乘积，不使用任何匹配或者选取条件。

//以下语句等效：
    Select * From A Funll Join B；
    Select * From A Cross Join B；
支持 ON，WHERE
例如：Select * From A Cross Join B On A.name=B.name Where A.id=1;
    Select * From A, B；
支持 WHERE
例如：Select * From A, B Where A.name=B.name And A.id=1;
    Select * From A Join B；
支持ON，WHERE
例如： Select * From A Join B On A.name=B.name Where A.id=1;

---

SQL语句及常用函数

1. 语句

Group By    //结合统计函数，根据一列或多列对结果进行分组

Order By    //对结果集进行排序

Having      //弥补WHERE语句无法与统计函数一起使用

2. 统计函数

1) Count()

//返回指定列的值的数目
Select Count(column_name) From table_name

2) Sum()

//返回数值列的总和
Select Sum(column_name) From table_name

---

练习

创建表

Student:        sid,  name,  age
Class:          cid,  name
StudentClass:   sid,  cid
//往每张表插入>10行的数据

查询：

• 查询年龄>20的学生的名字

Select name, age From Student Where age>20

• 查询每个班级的学生的数量

Select c.name, sc.num 
    From Class c, (Select cid, Count(sid) num From StudentClass Group By cid) sc 
    Where c.cid = sc.cid

• 查询学生数量>30的班级的名字

Selcet c.name sc.num
    From Class, (Select cid, Count(sid) num From StudentClass Group By cid) sc
    Where c.cid = sc.cid and sc.num > 30

• 查询Tom所在班级的学生的数量

Select Count(sid) From StudentClass Group By cid 
    Having StudentClass.cid in 
    (Select StudentClass.cid From Student, StudentClass Where Student.name = "Tom" and Student.sid = StudentClass.sid)

---