SQL中select使用

你的名字 2022-07-16 04:09 175阅读 0赞

### 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