SQl语句

  一、SQL简介

  Sql是美国国家标准局从1986年首次推出的一种关系数据库语言。

  SQL具有数据定义（DDL）数据库操作（DML）数据库控制（DCL）等功能

  1.数据定义

     Sql的数据定义功能能够定义数据库的三级模式结构，即外模式。全局模式和内模式结构

简单的说就是创建一个数据的语句，例如创建数据库，创建数据表等

  2.数据操作：

      SQl的数据操作功能包括对基本表和视图的数据查询、插入。删除和修改，特别是具有很强的数据

查询功能

   3.数据控制

SQL的数据控制主要是对用户的访问权限加以控制，以保证系统的安全性。由DBA负责控制和管理

二、数据定义（DDL）

1、数据的管理

 （1）创建数据库 Creat edataBase Sc

  （2）删除数据库 DROP database Sc

    (3)打开数据库 Use SC

2、数据表管理
（1）、创建数据表：
create table course   //创建表
(
   couid                int not null auto_increment,
   cno                  varchar(10) not null,
   cname                varchar(30) not null,
   primary key (couid)
);
在创建数据表时一定要使用primary key指明主键列。

（2）、列级完整性约束
列级完整性约束—对该列数据的完整性约束条件，有六种：
DEFAULT<常量表达式>：默认值约束
NULL/NOT NULL：空值/非空值约束
PRIMARY KEY：主码约束
UNIQUE：单值约束/惟一值约束
REFERENCES<父表名>(<主码>)：外码约束
CHECK(<逻辑表达式>)：检查约束

（3）、表级完整性约束
表级完整性约束—对数据表的完整性约束条件，有四种：
PRIMARY KEY(<列名>,…)：主码约束
UNIQUE(<列名>,…)：单值约束
FOREIGN KEY(<列名>,…) REFERENCES<父表名>(<主码列名>,…)：外码约束
CHECK(<逻辑表达式>)：检查约束
*  创建唯一索引。表示这个列中的值不允许重复。
create unique index Index_2 on course
(
   cno
);
*  创建外键约束
alter table sc add constraint FK_c_sc foreign key (couid)  //创建外键约束
      references course (couid) on delete restrict on update restrict;

（4）、删除数据表
删除数据表：DROP TABLE course

3、视图管理

（1）、创建视图
create view myview as (select * from course)

（2）、删除视图
drop view myview

二、数据操纵(DML)(新增、修改、删除语句)
每次执行时，新增、修改、删除语句都是只能对一个表的内容进行改变。

在SQL语句中，字符数据和日期数据在书写时用单引号或双引号括起来。

1、insert语句
（1）单行插入
即一次向数据表中只插入一条记录，这是最常见的操作情形。
insert into course(cno, cname) values("A001", "大学语文");

（2）、多行插入（面试经常要考）
这是将一个子查询的结果插入到当前数据表中。
insert into course(cno, cname) (select cno, cname from backup_course);
注意：子查询返回的列的个数和顺序和insert语句中列的个数的顺序要匹配。

2、update语句
update course set cno="10", cname="abc" where couid=2;
注意：update语句执行时，首先是根据where条件查看有那些记录需要更新，如果没有Where条件就表示修改所有记录。

3、delete语句
delete from demo where id=1;
注意：delete语句执行时，首先是根据where条件查看有那些记录需要删除，如果没有Where条件就表示删除所有记录。

三、数据操纵(DML)(查询语句)
SELECT查询语句具有丰富的数据查询功能，能够实现关系运算中的大多数运算，如选择、投影、连接、并等，并且还带有分组、排序、统计等数据处理功能。
SELECT查询语句的结果有多种可能，有可能为空、单值元组、一个多值元组等，若为单值元组时，此查询可以作为一个数据项出现在任何表达式中。

1、select语句格式
SELECT …        规定结果中的列等
NTO…            将查询结果写入到一个基本表中
FROM…           指定要查询的基本表或视图
WHERE…          指定查询的条件或源表的连接条件
GROUP BY…       指定将查询结果分组的依据
HAVING…         筛选出符合条件的分组统计信息
ORDER BY…       将查询结果进行排序

2、单表查询
（1）、单表的简单查询
只对一个表的结果进行查询，这是最简单的select查询语句。
select couid, cno, cname from course;

（2）、单表的嵌套查询
嵌套查询实际就是子查询，每个子查询相当于是一个虚表。在实际使用时，在SQL语句中可以作为一个表使用。
   * 别名作用
     在多表操作时不同表中列的名字相同时，为不同表中列取不同的别名进行区分。
     如果列的名字比较长或不好理解，可以取一个更简短的名字，或者是更好理解的名字。

   * 表的别名
     SELECT c.cname FROM (select cno, cname from course) c;
     这里(select cno, cname from course)是一个嵌套查询（子查询），它的列就是查询返回的列，这里是cno和cname。为嵌套查询可以定义一个别名，它可以作为这个虚表的名字使用，就和真实表名一样，这里取的别名是c。在外层的select语句就就可以使用这个别名来引用列，例如c.cname。

   * 列的别名
     SELECT n, m FROM (select cno n, cname m from course) c;
     可以为SQL语句中的列取别名，它和正常的列名一样使用。这里由于n，m没有重名的返回列，因此在外层select中不需要使用c.n、c.m表示。

   * 嵌套子查询的常用情形
     就是判断某个元素是否在子查询的返回值的集合中，操作符使用in实现。
     SELECT * FROM course t where cno in (select cno from backup_course);
     操作符in用于判断course表中cno列的值，在backup_course表中cno列中是否有相同的值。
     在实际项目中，select后面不要使用 * 来表示所有列，虽然语法是正确的，但是有两个问题：一是可能返回一些不使用的列；二是并不能明确的知道返回那些列，容易引起歧义。

3、列操作
在SQL语句中，列最简单的处理就是直接返回列的值。但是在实际项目中，通常会对列值做一些处理。

（1）使用distinct去掉列中重复值。即列有多个重复值时，只会返回一个值，去掉其它的值。
select distinct cname from course;
去掉cname的重复值，每个值只保留一个。
注意：是在返回结果中去掉重复值，不是在数据表中修改数据。

（2）、使用函数操作列
*  count()是一个非常常用的函数，用于统计返回的行数。
   select count(*) total from course;
   使用 * 表示列，它返回当前所有行数，通常会为函数列取一个别名，在JDBC程序中好获得值。
  
   select count(distinct cname) total from course;
   这里没有使用 * 表示列，而是使用指定列，并且限定列值不允许重复

*  sum是加法运算
   select sum(couid) total from course;         

（3）、SQL中提供了大量的函数，使用方式类似。具体内容查SQL开发手册。

4、多表操作
在SQL中，大多数SQL查询都会涉及到多表，这将使用表之间的关系进行关联，从而得出查询结果。

（1）、from选项
提供用于查询的基本表和视图，并可以为每个基本表取一个别名。

在from选项中可以使用多个表，例如：
select c.cno, c.cname from course as c, sc as sc where c.couid = sc.couid;
当使用多表时，一定要使用where语句，否则查询结果是笛卡尔集，数据没有多大作用。
这里as是别名定义的引导词，它是一个可选关键字。

（2）表之间的连接方式
在数据表中有三种连接方式：左连接、右连接和中间连接。

（3)、中间连接
通常大家所写的查询语句就是中间连接，它是查询语句的默认格式。

*  最常见的SQL（默认是中间连接）：
select s.stuname, c.cname, sc.score from student s, course c, sc where s.stuid=sc.stuid and c.couid=sc.couid
这是一个查询每个学生的每门课程的成绩，使用三个表连接起来查询。

*  这是中间连接的实现，它和上面的语句等价，实际编程中不会使用这种方式。
select s.stuname, c.cname, sc.score from student s, sc inner join course c on c.couid=sc.couid where s.stuid=sc.stuid
中间连接的语法，是在from选项中使用inner join course c on c.couid=sc.couid表示，其中inner join表示中间连接，course是被连接的表，on c.couid=sc.couid表示使用on关键字的连接条件。

（4）、左连接
左连接表示在from中声明的表中数据将全部列出，并不关心left join声明表中是否有关联的数据。
select s.stuname, sc.score from student s left join sc on s.stuid=sc.stuid
这里将显示student中所有的学生信息，sc中有数据就显示数据，没有就显示null。

（5）、右连接
右连接表示在right join中声明的表中数据将全部列出，并不关心from声明表中是否有关联的数据。
select s.stuname, sc.score from sc right join student s on s.stuid=sc.stuid
这里将显示student中所有的学生信息，sc中有数据就显示数据，没有就显示null。

5、查询条件
查询条件使用where子句。重点是条件表达式。

（1）、表达式类型
用于查询语句中的逻辑表达式有两种：一般比较式，专门比较式。
一般比较式：<表达式1> <比较符><表达式2>
专门比较式：又叫判断式，它实现单值与集合数据之间的比较，常用有六种格式（ALL、ANY|SOME、[NOT] BETWEEN、 [NOT] EXISTS、 [NOT] IN、 [NOT] LIKE）

（2）、一般比较式
条件表达式类似于Java中关系表达式。只是其中使用数据表中的列代替了Java中的变量。
比较运算符：>、>、<、<=、<>，AND、OR、!。
     SELECT * FROM course c where couid <> 3;              //显示couid不为3的记录
     SELECT * FROM course c where !(couid <> 3);           //显示couid为3的记录
     SELECT * FROM course c where !((couid + 1) <> 3);     //显示couid为2的记录

（3）、BETWEEN ... AND
判断数字是否在两个数之间。
      SELECT * FROM sc where score between 80 and 100;  
      注意：小的值在between之后，大的值在and之后。比较时包含这两个值在范围内。

（4）、[not] exits
从外层查询中取出每条记录，依次和子查询进行比较。如果在子查询中有返回值，则exits表达式返回true，那么这条记录就会返回。如果子查询中没有返回值则返回false，那么这条记录就不会返回。
      select * from course c where exists (select sc.couid from sc where c.couid=sc.couid);
      逐条记录判断(select sc.couid from sc where c.couid=sc.couid)返回结果集中是否有值。

（5）、[not] in
判断值是否在某个集合（子查询）范围中。
      SELECT * FROM course t where cno in (select cno from backup_course);

（6）、 [not] like
like用于模糊查找
      select stuname, sex from student where stuname like '%语12%';
      模糊查找时注意表达式的写法：
           '%语%'：表示只要字符串中有"语"字就匹配
           '语%'：表示字符串必须以"语"字开头
           '%语'：表示字符串必须以"语"字结尾

（7）、子查询返回单个值的比较
当子查询只返加一个列，并且这个列中只有一个值，因此子查询返回的实际是单个值。现在可以将子查询作为一个值使用，例如比较大小等。
       select * from course c where couid = (select couid from sc where id=1);
       (select couid from sc where id=1)返回一个一行一列的结果，即单个值。

6、order by
该选项用于对查询结果按<排序列名1>的值进行升序(asc)或降序(desc)排列，若不指定排序方式，则默认按升序(asc)排列。
该选项只能用在最外层的查询语句中，不能在子查询中使用。
      SELECT * FROM course c order by couid asc;
      SELECT * FROM course c order by couid desc;

6、GROUP BY
使查询结果只包含按指定列的值进行分组的统计信息，即按指定的列名对投影或选择后得到的所有元组进行分组。
    select sc.couid,c.cname,avg(sc.score) score from sc,course c where sc.couid=c.couid group by sc.couid;
    按列分组后，通常会在select的列清单中对列进行操作，如求平均、求和、计算总数等。
    avg(sc.score)就是记录每个分组中所有成绩的平均值。

7、having
通常用于GROUP BY子句之后，用来从分组统计结果中筛选出符合<逻辑表达式>的部分统计结果。
    select sc.couid,c.cname,avg(sc.score) score from sc,course c where sc.couid=c.couid group by sc.couid having score>65;
    having是对分组后得到的结果进行处理。
    having score>65表示只显示分组后，平均分大于70的组。

四、如何编写SQL语句

1、首先判断是什么类型的数据操作？
   对于数据操作而言，只有四种语句：insert、update、delete、select。

2、分析操作涉及到那些数据，分析这些数据分布在那些数据表中？
   要找出待操作的数据表。

3、分析数据表之间的关系？
   数据表之间关系通常是通过外键实现。
   在第2步中已经找到了与数据相关的表，这里还可以通过关系分析，可能会找到一些关联操作的中间表。

4、编写CUD语句
   这种语句通常是对单表中数据进行更改。主要分析：
   （1）、更改的数据项
   （2）、数据行的查找条件。在条件中注意子查询的使用。

5、编写R语句
   这种语句通常会对多表操作，并且会涉及到较复杂的关系。主要分析：
   （1）、返回的数据列
   （2）、涉及的数据表和数据表之间的关系。判断当前表之间使用的连接类型，有三种：中间连接、左连接、右连接。
   （3）、查询条件设计。需要参考需求，看需求中有那些条件约束，根据约束选择运算符。注意可能使用子查询。
   （4）、查看有无分组统计要求，如果有则需要判断根据那个列进行分组统计。
   （5）、最后看是否需要对查询结果排序，如果有则确定排序列。

查询每个学生选修每门课程的有关数据（姓名、课程名和成绩）
SELECT * FROM course c
WHERE  EXISTS
(SELECT cid
 FROM  sc
 WHERE c.cid=sc.cid
 GROUP BY cid
 HAVING COUNT(*) > 3
)