sql 易错点 和简单练习

1 方括号 []  通配符

用来指定一个字符集，它必须匹配通配符的一个字符。

eg ：查询名字里面有S或者Q的人

   select A from B where A.name like '[SQ]%'

2 常用文本处理函数

LOWER（）  将字符串转换为小写

UPPER（）将字符串转换为大写

3 sql聚集函数

 AVG（）返回某列的平均值

COUNT（）返回某列的行数

MAX（）返回某列的最大值

MIN（）返回某列的最小值

SUM（）返回某列值之和

上诉函数忽略列值为null的行

4 having和where

where在数据分组前进行过滤，having在数据分组后进行过滤。

使用having 应结合GROUP BY 子句，

5 select子句及其顺序

SELECT         要返回的列或者表达式     

FROM            从中检索数据的表

WHERE         行级过滤

GROUP BY    分组说明

HAVING         组级过滤

ORDER BY    输出顺序排序

6 union 使用规则

union必须由两条或者以上的select语句组成，语句之间用关键字union分割。

union中的每个查询必须包含相同的列，表达式或聚集函数

列数据类型必须兼容，类型不必完全相同，但必须是DBMS 可以隐含转换的类型

7 insert 语句

insert into A (A.ID,A.NAME)

VALUES('1','SAI')

或者 insert into A(A.ID,A.NAME)

SELECT A.ID,A.NAME FROM B

insert 通常只插入一行，要插入多行，必须执行多个insert语句，insert select 是一个例外，他可以用一条insert插入多行，不管select语句返回多少行，都将被insert插入

8 update

UPDATE A SET A.name = 'Case'

WHERE A.id = '1'

update语句总是要以更新的表名开始，set命令用来将新值赋给给更新的列

9 delete

DELETE FROM A

where A.id ='1'

10表创建基础

利用create table 创建表，必须给出以下信息：

          新表的名字，在关键字create table之后给出

         表列的名字和定义，用逗号分隔

         有的DBMS还要求指定表的位置

输入：

  CREATE TABLE B

（

id  CHAR(10) primary key NOT NULL

name CHAR(254)    NOT NULL

price DECIMAL（8，2） NOT NULL

）  

11 事务 

BEGIN TRANSACTION

DELETE  A where A.ID =1

DELETE B where B.ID =2

COMMIT TRANSACTION

12 外键

 外键是表中的一列，其值必须列在另一表的主建中，外键是用保证引用完整性的重要部分。

eg：

  CREATE TABLE Orders

（

order_id  INTEGER NOT NULL PRIMARY KEY,

order_date DATETIME  NOT NULL

customer_id  CHAR(10) NOT NULL REFERENCES Customers(customer_id)

）

相同的工作也可以再alter table中用constraint语法来完成

ALTER TABLE Oerders

ADD CONSTRAINT

FOREINGN KEY(customer_id) REFERENCES Customers(customer_id)

13 唯一约束

唯一约束是用来保证一列（或一组列）中的数据是唯一的，类似于主键，但与主键存在以下区别。

表可包含多个唯一约束，但每个表只允许一个主键

唯一约束列可包含null值

唯一约束列可修改和更新

唯一约束列的值可重复使用

唯一约束不能用来定义外键

14 索引

根据数据库的功能，可以在 数据库设计 器中创建四种索引：

1普通索引:最基本的索引类型，没有唯一性之类的限制。

2 唯一索引 : 唯一索引是不允许其中任何两行具有相同索引值的索引。

3主键索引:简称为主索引，数据库表中一列或列组合（字段）的值唯一标识表中的每一行。该列 称为表的主键。

4 聚集索引 : 在 聚集索引 中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引

索引优点

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

索引缺点

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

在开始创建索引前，应记住以下内容：

索引改善检索操作的性能，但降低了数据插入，修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。

索引数据可能要占用大量的存储空间

并非所有数据都适合做索引，取值不多的数据不如具有更多可能值的数据。

索引用于数据过滤和数据排序

可以在索引中定义多个列

索引用CREATE INDEX语句创建

CREATE INDEX product_name_ind

on Product（product_name);

索引必须唯一命名，这里的索引名product_name_ind在关键字CREATE INDEX之后定义。

on 用来指定被索引的表，而索引中包含的列（此例中仅有一列），在表名后的圆括号中给出。

sql 语句练习：

1以Cno升序、Degree降序查询Score表的所有记录。

SELECT * FROM SCORE ORDER BY CNO ASC,DEGREE DESC;

2查询Score表中的最高分的学生学号和课程号。

SELECT SNO,CNO FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);

SELECT SNO,CNO FROM SCORE ORDER BY DEGREE DESC LIMIT 1;

3查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select avg(degree),cno

from score

where cno like '3%'

group by cno

having count(sno)>= 5;

4 查询最低分大于70，最高分小于90的Sno列

SELECT SNO FROM SCORE GROUP BY SNO HAVING MIN(DEGREE)>70 AND MAX(DEGREE)<90;

5 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

SELECT * FROM score s WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE) GROUP BY SNO HAVING  

COUNT(SNO)>1 ORDER BY DEGREE ;

6查询存在有85分以上成绩的课程Cno.

SELECT CNO FROM SCORE GROUP BY CNO HAVING MAX(DEGREE)>85;

select distinct cno from score where degree in (select degree from score where 

degree>85);

7查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof

select tname,prof from teacher where depart='计算机系' and prof not in (select prof from 

teacher where depart='电子工程系');

8查询成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序

SELECT * FROM SCORE WHERE DEGREE> ANY (SELECT DEGREE FROM SCORE

WHERE CNO='3-245') ORDER 

BY DEGREE DESC

9查询所有“女”教师和“女”同学的name、sex和birthday.

SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT WHERE SSEX='女'

UNION

SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER WHERE TSEX='女';

10查询成绩比该课程平均成绩低的同学的成绩表

select * from score where degree < (select avg (degree) from score)

11 查询至少有2名男生的班号。

SELECT CLASS FROM STUDENT A WHERE SSEX='男' GROUP BY CLASS HAVING COUNT(SSEX)>1;