SQL常用语句复习

整理一下一些常用的SQL语句并提供一下练习题
链接：https://pan.baidu.com/s/1Xecawl66l9NwJ6Ab90ToAQ
提取码：mxd2

创建表数据库

create database 数据库名
USE 数据库名 --使用数据库

创建表

create table 表名
(
表字段名 类型 约束
)
设置主键使用primary key关键字
设置外键使用 foreign key (主键字段名) references 要外连的表名(字段)

获取库表

select 
  TABLE_SCHEMA AS '数据库',
    TABLE_NAME AS '表名',
    column_name AS '字段名称',
    COLUMN_TYPE AS '字段类型',
    IS_NULLABLE AS '是否为空',
   COLUMN_DEFAULT AS '默认值',
   column_comment AS '字段备注'
from 
information_schema. COLUMNS
where table_name = '表名' or table_name = '表名'

简单查询

select * from 表名
select * from 表名 where 条件

连接查询

select * from 表1,表2 where 表1.表字段=表2.表字段 and 条件

匹配查询,使用like关键字

如查询姓李的信息
select * from 表名 where 字段名 like ‘李%’

如查询不包含‘数’的信息
select * from 表名 where 字段名 not like ‘%数%’

如查询学号中第5、6位是“01”的学生信息
select * from 表名 where 字段名 like ‘____01%’

分组查询

HAVING 条件表达式：用来限制分组后的显示，符合条件表达式的结果将被显示。
WITH ROLLUP：将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。
COUNT()函数：统计记录的条数。
SUM()函数：计算字段的值的总和。
AVG()函数：计算字段的值的平均值。
MAX()函数：查询字段的最大值。
MIN()函数：查询字段的最小值。

子查询，使用in关键字

select * from 表1 where 表1.字段名 in (select 表1.字段名 from 表2 where 条件)

集合查询

select * from 表名 where 条件
union
select * from 表名 where 条件

创建视图

create view 视图名 as
select * from 表名 where 条件

数据完整性约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(字段名约束条件)
如添加约束：限制教师的性别取值只能是“男”或“女”

ALTER TABLE Teacher ADD CONSTRAINT A_27 CHECK (Tsex LIKE '男' OR Tsex LIKE '女')

添加约束：限制课程的学分取值在0.5—6之间

ALTER TABLE Course ADD CONSTRAINT a_24 CHECK (Credits>=0.5 AND Credits<=6)

添加约束：限制班级名取值不能重复

ALTER TABLE Class ADD CONSTRAINT a_25 UNIQUE (Clname)

给产品表添加一列用于存储库存金额：列名为Total ，类型为decimal(18,2)，并计算所有产品的库存金额

ALTER TABLE Products ADD Total DECIMAL(18,2)
UPDATE Products SET Total=Quantity*Price

SQL语句程序设计

1．判断“赵良明”同学的考试平均分是否大于85分,若大于显示“赵良明同学符合三好学生条件”,否则显示“赵良明同学不符合三好学生条件”。

if((select avg(score) from sc,student where sc.sno=student.sno and sname='赵良明')>85)
print '赵良明同学符合三好学生条件'
else 
print'赵良明同学不符合三好学生条件'

2.显示01001课程的成绩等级(含学号,课程号,成绩等级),而且按学号排序。(85—100 优; 70—84 良;60–69 及格;0—59 不及格)

SELECT Sno AS 学号,Cno AS 课程号,Score AS 成绩,
CASE
	WHEN Score >=85 THEN '优'
	WHEN Score >=70 THEN '良'
	WHEN Score >=60 THEN '及格'
	WHEN Score <60 THEN '不及格'
END	AS 成绩等级 FROM SC ORDER BY Sno;

存储过程

创建一个存储过程:统计某个系学生的人数,人数作为输出参数返回给调用者。 然后执行该存储过程统计“计算机系”学生的人数。

create procedure t1 --存储过程名
@a1 char(20),@a2 int output -- 定义两个变量@a1 @a2
as
select @a2=count(s.sno)  -- 把求出的人数赋值到@a2返回
from department as d,student as s,class as c
where c.dno=d.dno and c.clno=s.clno and d.dname=@a1 --把@a1传来的值作为条件
group by d.dname

执行使用语句

declare @a1  char(20) --创建变量@a1
exec t1 '计算机系', @a1 output --调用t1传值
print @a1

触发器

在SC 表定义一个更新和插入触发器,在此触发器中保证成绩在0—100分之间

CREATE TRIGGER SC_1
ON SC FOR UPDATE,INSERT
AS 
IF EXISTS
(SELECT * FROM INSERTED WHERE Score>100 OR Score<0)
BEGIN
 PRINT '成绩必须在-100分之间！'
 ROLLBACK TRAN  --回滚操作，当不符合触发器设定的规则时，撤销操作。
END

数据库只显示前5条

select * from 表名 limit 10;

5 种连接 left join、right join、inner join，full join cross join

left join：左连接

select * from A
left join B
on A.aID = B.bID

right join：右连接

select * from A
right join B
on A.aID = B.bID

inner jon:内连接

select * from A
innerjoin B
on A.aID = B.bID

full jon：全外连接

select * from table1 a full join table2 b on a.id=b.id

cross join：交叉连接

select * from table1 a crossjoin table2 b ；
也可以写为 select * from table1,table2;