SQL基础语法

核心术语：DBMS通过名字来管理

一些要记：

候选键：如果一个属性或者属性集的值能够唯一标识一个关系的元组而又不包含多余的属性(可以被选拔为主键的属性) 关系：二维表选择：行投影：列主键：候选键任选其一实体完整性约束：主属性不能为空参照完整性约束：要么外键值为空要么等于其所参照关系中某个元组的主键值主属性：包含在任意候选键中的属性

创建数据库:

create database basename

创建表:`creat table tablename`

create database student  #创建学生数据库

use student  #使用该数据库

create table student  #创建学生信息表
(
	sno char(9) primary key,  #定义主键
	sname char(10) not null,  #完整性约束不能为空
	ssex char(2) check(ssex='男' or ssex='女'),  #ssex in('男','女') 约束内容为男或女
	sage int check(sage between 0 and 100),		#完整性约束
    sdept char(3)
)
create table course #创建课程表
(
    cno char(10) primary key,
    cname nvarchar(30) not null,
    ccredit int check(ccredit>0),
    cpno char(10) references course(cno),  #外键
    
)
create table sc
(
    sno char(9),
    cno char(10),
    grade int check(grade between 0 and 100),
    primary key(sno,cno),  #一个表只能有一个候选键
    foreign key(sno) references student(sno),	#外键 reference 参照 映射 
    foreign key(cno) references course(cno),
)

alter table student  #选择表
drop pk__student__dddf6446e4dd05ea  #删除 可拖拽

新建索引

create index index_grade
	on sc(grade,sno)

删除索引

	drop index <表名.索引名>
	drop index <索引名> on <表名>

插入元组（行）

insert 
	into student(sno,sname) #into 表名和列名 
	values ('1','aaa')
	#主键不可重复且唯一 
	#列名和学号一一对应
	#一次只能插入一行

示例

insert
	into student(sno,sname,ssex,sage,sdept)
	values ('1','a','boy',18,'ma')
insert 
	into student 
	values ('2','b','gril',18,'is')

修改（更新）

update 表名 set 列名 = 修改值
where 主键

删除

delete from sc
	where sno='3'

删除张立的系别（delete删除元组）

update student set sdept=null
	where sname='张立'

插入刘晨的性别为女（同上）

update student set ssex='女'
	where sname='刘晨'

查询

单表查询

select all(默认为all)||distinct(去掉重复的元组)
	from   从哪查
	where  条件 先选择 再分组 后排序
	group by    having
	order by 列名 asc||desc

查全体学生学号和姓名

select sno,sname
	from student

查询所有数据

select * from student

查询全校年龄大于十八岁的学生的所有信息 *是通配符代表所有列

select * from student
	where sage>18

查询全体学生的姓名及其出生年份

select sname as 姓名, year(getdate())-sage as 出生年份     
from student

查询sc表中的学号（去掉重复值）

select distinct sno from sc

逻辑运算

比较 =,<,>,<=,>=,!=,<> 确定范围 between and , not between and 确定集合 in,not in 字符匹配 like,not like 空值 is null,is not null (必须是is null) 多重条件 and,or

字符串匹配

通配符 like ： like 李% (查找姓李的同学) 通配符 _ 代表任意单个字符：

select sname from student
where sname like '刘%'

查询姓欧阳且全名为*三个汉字的学生的姓名和学号

select sno,sname from student
where sname like '欧阳_'  and len(sname)=3

为防止出错，sname最好设定为varchar 查找学号、成绩，根据成绩降序排序

select cno,grade from sc
where cno=3
order by grade desc

desc 降序 asc 升序（默认）

设置某个字符为转义字符`escape 'ch'`

查找以DB_开头的课程名

select cname from sc
where cname like 'DB\_%i__' escape '\'

distinct去重查找总人数

select count (*)
from student

查询各门课程的选课人数

select cno,count(sno) from sc
group by cno

会先分组后统计人数

查询选修人数>=3的课程

select cno,count(sno) from sc
group by cno having count(cno)>=3

having子句必须和group by一起使用

查询每个顾客的销售总额

select Customer,sum(OrderPrice) from order
group by Customer

多表查询

语法：

查询选修了1，2，3号课程的学生的学号

联结字段必须是可比的，但不一定是相同的

等值联接和非等值联接

比较运算符=为等值联接否则为非等值联接

自身联接

自己联接自己查询每个学生的选课情况凡是两张表里有相同名字的，都要写清楚来自哪个表自然联接

select student.* ,sc.* 
from student, sc
where student.sno=sc.sno

select student.*,cno,grade
from student join sc on student.sno=sc.sno

查询每门课程的间接先选课程

select first.cno,second.cpno
from first join second on first.cpno=second.cno

自身联接求选修了1、2、3号课程的学生学号

select first.sno
from course first,course second,course third
where first.sno=second.sno and second.sno=third.sno and first.cno='1' and second.cno='2' and third.cno='3'

外联接

*= 左外联接 =* 右外联接主体表在哪里用那个联接1

select student*,sno,grade
from student,sc
where student.sno=*sc.sno

外联接在sql sever2005以上版本都不能使用。 查询每门课程及其被选修情况

select cno,sno
from student join sc on sc.sno=student.sno

嵌套查询

位置：

放在where子句。
放在having子句。查询与“刘晨”在同一个系学习的学生的学号、姓名和系别

思路：查询刘晨在那个系别，然后根据系别查询目标数据

## step1: 
select sdept from student
where sname='刘晨'
## 输出为：is

## step2
select sno,sname,sdept from student
from student
where sdept='is'

正确写法

select sno,sname,sdept from student
where sdept in (
	select sdept from student where sname='刘晨'
) and sname !='刘晨'

查询选修了1，2，3号课程的学生的学号和姓名

select sno,sname from student
where sno in (
	select sno from sc
	where cno='1' and sno in (
		select sno from sc
		where cno='2' and sno in (
			select sno from sc
			where cno='3'
		)
	)
)

带有any 和 all 谓词的子查询

查询其他系中比is系中任一学生年龄小的学生名单

select sname,sno 
from student
where sage<any (
	select sage 
		from student 
		where sdept='is' 
) and sdept<>'is'

带有exist谓词的子查询

select sname from student
where sno not in (
	select sno from sc
	where cno='1'
)

查询与刘晨在同一个系别的学生的学号和姓名和学号

select first.sno,first.sname 
from student.first,student.second
where first.sdept=second.sdept and second.sname='刘晨'

查询选修了信息系统的学生的名字和学号

select sno,sname
from student,course,sc
where student.sno=sc.cno and sc.cname=course.cname and cname='信息系统'

视图

只有结构，没有数据

建立视图

create view<视图名> [(<列名>)，，，，]
	as <子查询>
	with check option

对于视图的insert、、、、会更改基本表里面的数据

create view is_student
	as
	select sno,sname,sage
	from student
	where sdept='is'

删除视图

drop view is_student

插入视图

insert into is_student
、、、、、、

作用

简化操作
是用户多种角度看待同一数据
逻辑独立
对机密数据提供安全保密

数据控制

授予权限

grant <权限> 
	on <对象类型> <对象名>
	to <用户>|public
	with grant option ##级联授权

只能授权表级。

grant select on student(sname) to u1,u2,u3

把两张表权限给u1

grant select on student to u1
grant select on sc to u1

把查询student表和修改学号的权限授权给u1，并允许再授权

grant update(sno),select on student to u1 with grant option

原则上，给权限时，要给select权限

撤销权限

revoke <权限>
	on 对象类型 对象名
	from 用户名

收回权限时，级联权限同时会回收

SQL基础语法速成