SQL中select使用

SELECT的语法

SELECT [DISTINCT] <目标表的列名或列表达式序列>
FROM  <基本表名或视图序列>
[WHERE <行条件表达式>]
[GROUP BY <列名序列>  
    [HAVING <组条件表达式>]]
[ORDER BY <列名[ASC|DESC]>]
[UNION SELECT...]

SQL中select语句执行过程：

1、读取from子句中基本表、视图的数据，将硬盘中的数据加载到缓冲区，执行迪卡儿积操作
2、选取满足where子句中给出的条件表达式的元组
3、按group子句中指定的列的值分组，
4、提取满足having子句中组条件表达式的那些组
5、按select子句中给出的列名或表达式输出
6、按distinct去重
7、存在union则进行并操作
8、order子句对输出的目标表进行排序，ASC升序，DESC降序

基本表的创建

//创建教师表
mysql> create table T(
    -> tid char(4) not null,
    -> tname char(8) not null,
    -> title char(10),
    -> primary key(tid));
Query OK, 0 rows affected (0.29 sec)

//创建课程表
mysql> create table C(
    -> cid char(4),
    -> cname char(10) not null,
    -> tid char(4),
    -> primary key(cid),
    -> foreign key(tid) references T(tid));
Query OK, 0 rows affected (0.06 sec)

//创建学生表
mysql> create table S(
    -> sid char(4) not null,
    -> sname char(8) not null,
    -> age smallint,
    -> sex char(1),
    -> primary key(sid));
Query OK, 0 rows affected (0.33 sec)

//创建选课表
mysql> create table SC(
    -> sid char(4),
    -> cid char(4),
    -> score smallint,
    -> primary key(sid,cid),
    -> foreign key(sid) references S(sid),
    -> foreign key(cid) references C(cid));
Query OK, 0 rows affected (0.16 sec)

插入数据

教师表T

mysql> insert into T values("1","苹果","高数");
Query OK, 1 row affected (0.09 sec)

mysql> insert into T values("2","栗子","英语");
Query OK, 1 row affected (0.07 sec)

mysql> insert into T values("3","葡萄","数据库");
Query OK, 1 row affected (0.00 sec)

课程表C

mysql> insert into C values("1","高数","1");
Query OK, 1 row affected (0.01 sec)

mysql> insert into C values("2","英语","2");
Query OK, 1 row affected (0.12 sec)

mysql>  insert into C values("3","数据库","3");
Query OK, 1 row affected (0.01 sec)

学生表S

mysql> insert into S values("1","haha","18","F");
Query OK, 1 row affected (0.04 sec)

mysql> insert into S values("2","Mary","22","M");
Query OK, 1 row affected (0.02 sec)

mysql> insert into S values("3","xixi","22","M");
Query OK, 1 row affected (0.01 sec)

选课表SC

mysql> insert into SC values("1","1","90");
Query OK, 1 row affected (0.05 sec)

mysql> insert into SC values("2","1","80");
Query OK, 1 row affected (0.05 sec)

mysql> insert into SC values("1","3","90");
Query OK, 1 row affected (0.01 sec)

mysql> insert into SC values("1","2","70");
Query OK, 1 row affected (0.01 sec)

select操作

1、检索课程号为1课程的学生学号和姓名
mysql> select S.sid,sname from S,SC where S.sid=SC.sid and cid="1";

//使用子查询，嵌套查询
mysql> select sid,sname from S where sid IN (select sid from SC where cid="1");

2、检索至少选修栗子老师所授一门课程的学生学号与姓名
mysql> select S.sid,sname from S,SC,C,T WHERE S.sid=SC.sid AND T.tid=C.tid AND C.cid=SC.cid AND tname="栗子";

3、检索SC表中男同学选修的课程号（因为一门课程可以有多个男同学选，所以用distinct）
mysql> select distinct cid from S,SC where SC.sid=S.sid and sex="M";
//group by也可以去重
mysql> select cid from S,SC where SC.sid=S.sid and sex="M" group by cid;

4、使用group by可以按指定的列去重
mysql> select * from S;
+-----+-------+------+------+
| sid | sname | age  | sex  |
+-----+-------+------+------+
| 1   | haha  |   18 | F    |
| 2   | Mary  |   22 | M    |
| 3   | xixi  |   22 | M    |
| 4   | xixi  |   22 | M    |
| 5   | Mary  |   22 | M    |
+-----+-------+------+------+

mysql> select * from S group by sname,age,sex;
+-----+-------+------+------+
| sid | sname | age  | sex  |
+-----+-------+------+------+
| 1   | haha  |   18 | F    |
| 2   | Mary  |   22 | M    |
| 3   | xixi  |   22 | M    |
+-----+-------+------+------+

5、删除出自动编号外其他字段冗余的字段报错
mysql> delete from S where sid not in (select min(sid) as sid from S group by sname,age,ssex);
//ERROR 1093 (HY000): You can't specify target table 'S' for update in FROM clause
//解决办法:先把查询的结果生成一个临时表，然后从临时表中删除

6、删除出自动编号外其他字段冗余的字段
mysql> delete from S where sid not in (select sid from (select min(sid) AS sid from S grroup by sname,age,sex) as temp);

7、查询学生中每一门分数都大于89的学生
mysql> select name,socre from sc;
+------+-------+
| name | socre |
+------+-------+
| xixi |    89 |
| xixi |    90 |
| Mary |    90 |
| Mary |    70 |
| kk   |    90 |
+------+-------+
5 rows in set (0.00 sec)

mysql> select name from sc group by name having min(socre)>89;
+------+
| name |
+------+
| kk   |
+------+
1 row in set (0.00 sec)

8、复制sc表到b表

mysql> desc b;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(10) | YES  |     | NULL    |       |
| course | varchar(20) | YES  |     | NULL    |       |
| socre  | smallint(6) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mysql> insert into b(name,course,socre) select name,course,socre from sc;
Query OK, 5 rows affected (0.18 sec)
Records: 5  Duplicates: 0  Warnings: 0