一、DDM、DDL
-
DDL
create
alter
drop
-
DML
insert into 表名[()]values()
insert into 目标表名 [()]select 源字段名 from 源表名
[where 添加条件]
update 表名
set 字段名 1=字段名 2 where 条件表达式
delete from 表名 [where 删除条件]
二 、 建表
create database 数据库名
select * from 表名
create table 表名(
id int ,
name varchar(20),
Primary key(id),
foregin key(字段名) references 表名(字段名)
)
alter table 表名 add constraint 名字 foregin Key(字段名)
references 表名
alter table 表名 add qq varchar(20)
alter table 表名 drop column qq
drop table 表名
三 、增删改
insert into 表名(字段名1,字段名2,...) values (数值,'字符串')
-- 注释 主键唯一且非空
delete from 字段名 where 字段名= 新值
-- 表里有外键的先用update 置为null 这样被引用的那张表才可以删除被引用的字段
update 表名 set 字段名=新值 where 字段名=xx
四、单表查询
select * from 表名
select 字段名,字段名 from 表名
-- 给字段命名有三种方式 字段名 as 新字段名 或者 字段名 新字段名
-- 去重
select distinct 字段名 from 表名
-- 加条件 like 在不是使用通配符%时等价于= 但是效率低
select * from 表名 where name like '小于'
-- <> 不等于
-- 条件表示范围可以用 字段名 between 数值 and 数值
-- in 等价于多个or not in
select * from 表名 where 字段名 in(1,2,3)
-- 模糊查询 通配符 % 可以表示0到多个字符 _ 占一个位置姓
select * from 表名 where name like '_陈%'
-- null 的用 is
select * from 表名 where grade is null
五、聚集函数
-- 默认升序 order by.....desc (降序时加上)
select * from 表名 order by 字段名 [desc]
-- 聚集函数
--count(*) 表中统计有多少行
select count(*) from 表名
--count(字段名)表中非空字段名值的个
select count([distinct ]字段名) from 表名
-- sum avg max min .....
select avg(字段名) from 表名 where 条件
-- group by exmaple 求各个课程号以及相应的人数
-- 先想好要分组的字段课程号是 cno 则前面要显示的字段里必包含cno
select cno,count(sno) as num from sc group by cno
-- where 句子不能用聚集函数作为条件表达式 如果非要用的话,要是用group by.....having example 查询平均成绩大于90的学生学号和平均成绩 having 和group by 搭配使用 作为分组条件
select sno,avg(grade) from sc group by sno having avg(grade)>=90
--SQL的执行顺序(重点):
--1.先执行FROM子句: 确定查询哪一张表
--2.接着执行WHERE : 过滤筛选条件
--3.接着做SELECT : 确定选择的列
--4.最后做ORDER BY: 对结果集按照某列排序
--5.在WHERE子句中不能使用SELECT中的别名
六、多表查询
一、内连接
概念 : 内连接查询:查询的结果为两个表匹配到的数据
- 等值连接
select * from a,b where a.id=b.id
--等价于 inner可以省略
select * from a inner join b on a.id =b.id
-
不等连接
select * from a inner join b on a.id <> b.id
二、外连接
- 左连接(左外连接)
左连接查询:查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充
select a.*,b.* from a left join b on a.id=b.id
2.右连接
右连接查询:查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充
select a.*,b.* from a right join b on a.id=b.id;
三、嵌套查询
-- 不相关嵌套查询(子查询不依赖父查询)
-- exmple 查询选修'2017'学生的姓名 stname
-- stu(sname,sno) sc(sno,cno) in 子查询返回值有多个
select sname from stu where sno in (select sno from sc where cno='2017')
-- 也可以用连接
select sname from stu join sc on sc.sno=stu.sno and sc.cno='2017'
-- 相关嵌套查询(将连接放在子查询里面)
select sname from stu where '2017' in (select cno from sc join stu on sc.sno=stu.sno)
-- 带有exists 子查询那只会返回 true false ,每一次取一个sno连接
select sname from stu where exists (select * from sc join stu on sc.sno=stu.sno)
-- 集合查询 union 并
--intersect 交
--except 除
七 、视图
概念: 视图是一张虚表,数据库中只存放视图对应的数据,这些数据仍然存放在原来的基本表中。所以一旦基本表中的数据被改变,从视图中查询的数据也就随之改变。
-- 创建
create view 名
as
select ....
-- 使用
select * from 视图名
八 、存储过程
概念 : 存储过程是事先经过编译并保存在数据库中的一段是sql语句的集合 使用时调用即可
-- 创建
create proc 名
as
begin
select....
end
-- 调用
exec 存储名
-- 删除
drop proc 存储名
-- 带参数无返回值的存储过程
-- 查询 学号为’2017‘的学生
create proc p1 @id varchar(20)
as
begin
select * from sc where id=@id
end
-- 有参数有返回值的存储过程
-- 查询 有没有学号为’2017‘
create proc p2 @id varchar(20),@a varchar(20) output
as
begin
if exists (select * from sc where id=@id)
set @a='1'
else set @a='0'
end
--调用
declare @a varchar(20)
exec p2 '小明',@a output
select @a
九、 触发器
概念 :监视某种情况,并触发某些操作,当对一个表格进行增删改就有可能激活它
-- 学生人数不能超过20
create trigger ti on stu
after insert -- instead of 还没插入时
as
begin
if(select count(*) from stu) >20
begin
print 'error'
rollback tran
end
else
begin
print 'right'
end
end
-- 触发器里面有两张表 inserted deleted 会把数据先放这里
十、函数
-
系统函数
-
用户自定义函数(函数和存储过程很像 不同之处函数多了一个return)
-
标量函数
-
表值函数(返回结果是一张表)
-
-- 计算某门课程的平均分
-- input cno output avgscore
create function fun1(@cno varchar(13))
returns int
as
begin
declare @avgscore int
select @avagscore=avg(grade) from sc where cno=@cno
return @avgscore
end
-- 调用 注意dbo
select dbo.fun1('2017')
-- 输入专业号 返回学生学号和姓名 output table
create function fun2 (@mmo int)
returns @snoSname table(
sno varchar(13),
sname varchar(30)
)
as
begin
--declare @sno varchar(13)
--declare @sname varchar(30)
--sno sname mmo --stu
--select @sno=sno,@sname=sname from stu where mmo=@mno
-- 上面那种做法只能插入一条
insert into @snoSname(sno,sname) select sno,sname from stu where mmo=@mno
return
end
```