数据库语句

SQL 命令复习

数据表创建

create table 通讯录(
  姓名 varchar(8),
  性别 char(2),
  年龄 int,
  电话 char(20),
  地址 char(20)
);

视图创建

create view cs_info_view
as
select * from student where 系别='计算机';


create view s_info_view(学号,姓名,性别,系别)
as
select sno,sn,sex,dep from student;


create view s_info_view
as
select sno as 学号,sn as 姓名,sex as 性别,dep as 系别 from student;

索引创建

# 创建表时创建索引

# 创建 S 表并以 sname 字段建立普通索引 

create table S(
  sno char(10),
  sname varchar(20),
  age int,
  sex char(2),
  dept varchar(20),
  index (sname)
);

# 创建 SC 表，并以 sno 和 cno 两字段组合建立名为 uk_sno_cno 多列唯一索引

create table SC(
  sno char(10),
  cno char(15),
  score decimal(4,1),
  unique index uk_sno_cno(sno,cno)
);

# 在已存在的表上创建索引

# 降序普通索引

create index idx_sno on SC(sno desc);
alter table SC add index idx_sno (sno desc);

# 唯一索引

create unique index uk_sno on S(sno);
alter table S add unique index idx_sno (sno);

授权/回收权限命令

# 把创建表权限授权给用户 user1/回收

grant create table to user1;
revoke create table from user1;

# 把查询 order 表的权限授权给用户 user1/回收 

grant select on order to user1 [with grant option];
revoke select on order from user1;

# with grant option 表示允许被授权用户将制定的系统权限再授予其他用户

基本的查询命令

select * from course;
select sno,sname,sage from student;
select cno as 课程名 from course;
select sname as 姓名,2020-sage as 出生年份 from student;

条件查询

# 精确匹配
select * from sc where grade > 85;

# 多重条件
select * from sc
where (cno = 'kc001' or cno ='kc002') and grade > = 85;

# 确定范围
select * from teacher
where sal between 1000 and 1500;
# (等价于 sal> =1000 and sal < =1500>);

# 确定集合
select * from sc
where cno in ('c1','c2'); # (等价于 cno='c1' or cno='c2')
select * from sc
where cno not in ('c1','c2'); # (等价于 cno! ='c1' and cno! ='c2')

# 部分匹配查询（模糊查询）
select * from course
where cname [not] like '%数%';
# “%” 匹配零个及多个任意字符；“_” 匹配一个任意字符

# 空值查询
select * from course
where cpno is null;

# 统计查询
# count() 按列计算个数
# sum()、avg()、max()、min()
# count(*) 用来统计元组个数，不消除重复行，不允许使用 distinct 关键字
select count(*) from student
where dept = '计算机';

连接查询

# 一般连接
select teacher.tno,tn,cno
from teacher,tc
where (teacher.tno=tc.tno) and (tn='刘伟');

# 自身连接
select cx.cno [as] 课程号,cy.cname [as] 课程名,
       cx.cno [as] 先选课程号,cy.cname [as] 先选课程名
from course [as] cx,
     course [as] cy
where cx.cpno = cy.cno;

# JOIN连接

# 交叉连接 （笛卡尔积）
select * from student cross join course;

# 内连接
select * 
from student [inner] join sc on student.sno = sc.sno
where sage > 20 and sex = '男';

# 外连接

# left (right、full) [outer] join 结果包含第一（二）个表中所有行
select *
from student left [outer] join sc
on student.sno = sc.sno;

嵌套查询

# 普通子查询

# 先执行子查询 返回单一值
select * from teacher
where prof = (select prof
              from teacher
              where tn='刘伟')；

# 先执行子查询 返回一组值
# 使用 any
select * from teacher
where tno = any
         (select tno
          from tc
          where cno='c5');
# 使用 in 代替 =any 
select * from teacher
where tno in 
      (select tno
       from tc
       where cno='c5');

# 使用 all
select * from teacher
where sal > all
          (select sal
           from teacher
           where dept='计算机')
          and dept ! = '计算机';

# 使用 exists
# 属于相关子查询
select * from teacher
where [not] exists
      (select *
       from tc
       where tno = teacher.tno
       and cnon = 'c5');

相关子查询

# 先执行外部查询
# 执行次数由父查询表的行数决定

select distinct tn
from teacher
where 'c5'! = all
        (select cno
         from tc
         where tno=teacher.tno);

派生子查询

# 嵌入到 from 子句中
# 子查询为父查询提供派生表（临时表）

select *
from student,(select avg(sage) as 信息系平均年龄
              from student
              where sdept='信息') as info
where sage > info.信息系平均年龄;

分组查询

# 按系别分组
select sdept as 系名,
    count(*) as 人数
from student
group by sdept;

# 学了四门以上课程的学号和课程数
select sno as 学号,
    count(*) as 课程数
from sc
group by sno
having count(*) > =4;

排序

# 排序 (order by)
# desc 降序；asc 升序

select * from student
where sdept='计算机'
order by sage desc；

限量查询

select * from student
order by score desc
limit 5;

# limit n 等价于 limit 0,n
# limit 5,10 从标号为 5 的行开始，取 10 行， 6 - 15 行

增、删、改

# 增

# 每列都要插入，且顺序一致
insert into student
values ('S7','周杰伦','男',21,'古典音乐');

# 其他列有默认值的填默认值，否则 NULL，若有非空值限制则报错
insert into sc(sno,cno)
values ('S7','c1');

# 改

update teacher set dept ='信息'
where tno = '刘伟';

update student set age = age + 1;

# 删

delete from teacher where tn = '刘伟';

delete from teacher;