数据库基本知识SQL

DDL 数据定义语言, create，alter，drop
DML 数据操作语言，insert,delete,update,select
DCL 数据控制语言, grant, revoke
SQL由9个引导词组成
很多数据库系统不区分大小写
创建数据库 create database 数据库名;
创建表 create table 表名（列名 数据类型[Primary key|Unique] [Not null] [, 列名 数据类型 [Not null], …]);
Create table student(S# char(8) not null, Sname char(10),Ssex char(2),
Sage integer, D# char(2), Sclass char(6));

Create table course (Cid character(3),cname character(12),chours integer,credit real(1),T# char(3));

dept (D# char(2),Dname char(10),dean char(10));

teacher(T# char(3),Tname char(10),D# char(2),salary float(2))

SC(S# char(8), c# char(3),score float(1))

insert into 表名[(列名,[,列名]…] values (值,[,值],…);

insert into student values(‘98030101’,’张三’,’男’,20,’03’,’980301’);

插入多条
insert into student values
(‘98040201’,’王三’,’男’,20,’04’,’980402’),
(‘98040202’,’王四’,’男’,21,’04’,’980402’);

查询语句 select 列名 [[,列名]..] from 表名 [where 检索条件];

select sid,sname from student;
select * from student;

检索教师表中所有工资少于1500元或工资大于2000元 并且是03系的教师姓名
select tname
from teacher
where (salary<1500 or salary > 2000) and did=’03’;

求001和002号课程的学生学号
select sid from sc where cid=’001’ or cid=’002’;

既学过001又学过002的学生学号
（思考？）
select sc1.sid sid from sc sc1,sc sc2
where sc1.sid=sc2.sid and sc1.cid=’001’ and sc2.cid=’002’;

使用distinct去重

select DISTINCT sid from sc where score>80;

结果排序问题 order by 列名 [asc|desc]
默认是asc，可省略
desc降序

select sid from sc where cid=’002’ and score>80 order by score desc;

模糊查询问题
列名 [not] like “字符串”

% 匹配0个或多个字符
_ 匹配任意单个字符
\ 转义
\% 表示匹配字符”%”

检索所有姓张的学生学号及姓名
select sid,sname from student where sname like ‘张%’;

select sname from student where sname like ‘张__’;

多表联合查询
select 列名 from 表名1,表名2,… where 检索条件;

按’001’号课成绩由高到低显示所有学生的姓名
select sname from student,sc
where student.sid=sc.sid and sc.cid=’001’
order by score desc;

别名
用as，as可以省略
001号课程成绩有差异的学生
select sc1.sid as student1, sc2.sid as student2
from sc sc1,sc sc2
where (sc1.cid=sc2.cid) and sc1.cid=’001’ and sc1.score>sc2.score;
括号内的漏掉了

select sc1.sid from sc sc1,sc sc2
where sc1.sid=sc2.sid and sc1.cid=’001’ and sc2.cid=’002’ and sc1.score>sc2.score;

delete from 表名 [where 条件表达式];

update 表名
set 列名=表达式|（子查询）
[where 条件表达式]

alter table tablename
[add {colname datatype,…}] 增加新列
[drop{完整性约束名}] 删除完整性约束
[modify{colname datatype,…}] 修改列定义

撤销表 drop table 表名
撤销数据库 drop database 数据库名；
指定当前数据库 use 数据库名
关闭当前数据库 close 数据库名

找出所有课程都不及格的学生姓名
select sname from student where 60 > all (select score from sc
where s3=student.s#)

找出001号课成绩最高的所有学生的学号
select s# from sc where sc.c#=’001’ and sc.score >= all
( select score from sc where sc.c#=’001’)
找出98030101号同学成绩最低的课程号
select c# from sc where sc.s#=’98030101’ and sc.score <=all
(select score from sc where sc.s#=’98030101’)

找出张三同学成绩最低的课程号
select C# from SC,student s
where sname=’张三’ and s.s#=sc.s# and
score <= all( select score from sc
where s#=s.s#);

θ any 和some是一个意思，都是存在一个，尽量不使用any
= some 和 in 等价

exist （子查询） 难点,，多练习
表示 子查询结果有无元组存在

列出至少学过98030101号同学学过所有课程的同学的学号
select distinct s# from sc sc1
where not exists
(select from sc sc2
where sc2.s#=’98030101’ and not exists
(select from sc
where c#=sc2.c# and s#=sc1.s#));

分组 group by 分组条件
聚集函数不能用于where子句
Having子句，分组过滤子句，需要group by支持，写在group by 后面

求不及格课程超过两门的同学的学号
select S# from sc
where score < 60
group by s# having count(*)>2;

distinct 不用于*，必须指定某一列
null不能用= 要用is
is [not] null
null参与比较，结果是false
null参与聚集运算，只有count，其他的都忽略null

定义视图
create view view_name [(列名 [,列名]…)]
as 子查询 [which check option]

定义视图，用于方便用户进行检索操作
带聚集函数的视图，使用了unique或distinct，group by，算术表达式计算的列，单个表的列构成但没有包考主键的不可更新
drop view view_name;

create table companies
(
name char(100),
country char(100)
);

create table trades
(
id int(20),
seller char(100),
buyer char(100),
value int(10)
);

insert into companies values
(‘Alice’,’Wonderland’),
(‘Y-zap’,’Wonderland’),
(‘Absolute’,’Mathlands’),
(‘Arcus’,’Mathlands’),
(‘Lil’,’Underwater’),
(‘None at all’,’Nothingland’);

insert into trades values
(20121107,’Lil’,’Alice’,10),
(20123112,’Arcus’,’Y-zap’,30),
(20120125,’Alice’,’Arcus’,100),
(20120216,’Lil’,’Absolute’,30),
(20120217,’Lil’,’Absolute’,50);

select country,ifnull(sum(value),0) export from companies left join trades on companies.name=trades.seller group by country;

select country,ifnull(sum(value),0) import from companies left join trades on companies.name=trades.buyer group by country;

select a.country,a.export,b.import from
(select country,ifnull(sum(value),0) export from companies left join trades on companies.name=trades.seller group by country) a join
(select country,ifnull(sum(value),0) import from companies left join trades on companies.name=trades.buyer group by country) b on a.country=b.country;

create table transfers(
name varchar(30) not null,
money integer not null);

insert into transfers values
(‘Andrea’,-10000),
(‘Mark’,149513),
(‘Kassidy’,-5016),
(‘Andrea’,17500),
(‘Andrea’,2500),
(‘Jim’,100000),
(‘Jim’,-50),
(‘Jim’,-50),
(‘Jim’,-50),
(‘Kassidy’,-2013);

select name,sum(money) as sum1 from transfers where money>0 group by name;
select name,-sum(money) as sum2 from transfers where money<0 group by name;

select t1.name,ifnull(t1.sum1,0) sum_of_deposits,ifnull(t2.sum2,0) sum_of_withdrawals from (select name,sum(money) as sum1 from transfers where money>0 group by name) t1 left join (select name,-sum(money) as sum2 from transfers where money<0 group by name) t2 on t1.name=t2.name;
select t1.name,ifnull(t1.sum1,0) sum_of_deposits,ifnull(t2.sum2,0) sum_of_withdrawals from (select name,sum(money) as sum1 from transfers where money>0 group by name) t1 left join (select name,-sum(money) as sum2 from transfers where money<0 group by name) t2 on t1.name=t2.name
union
select t2.name,ifnull(t1.sum1,0) sum_of_deposits,ifnull(t2.sum2,0) sum_of_withdrawals from (select name,sum(money) as sum1 from transfers where money>0 group by name) t1 right join (select name,-sum(money) as sum2 from transfers where money<0 group by name) t2 on t1.name=t2.name
order by name;

mysql没有full join，可以使用left join和right join ，然后union。
join是列合并，union是行合并