一、数据库语言
1、数据定义语言(DDL):定义数据库模式;
create table Students
(
student_id char(10) not null,
student_name char(20),
student_sex char(5),
student_class char(10)
primary key(student_id),
foreign key(student_class)references Class
)
2、数据操纵语言(DML):数据的的查询和更新;
select student_id
from Student
where student_sex = 'woman'
二、数据类型
常见的数据类型有:
1、char(n),固定长度字符串,用户指定长度n。
2、varchar(n),可变长度字符串,用户指定最大长度n。
3、int/smallint,整数类型和小整数类型。
4、numeric(p,d),定点数。
5、real,double,precision,浮点数和双精度浮点数。
6、float(n),精度至少为n位的浮点数。
7、date ‘2016 -03 -09’
8、time ‘10:27:00’
9、timestamp ‘2016 -03 -09 10:27:00’
三、SQL常见命令
1、插入
insert into Student (student_id,student_name,student_sex,student_class)values
('2016211333','张三','woman','2016211320');
2、删除
删除Student中所有元组,但是保留关系Student
delete from Student
删除关系Student
drop table Student
3、更新 update
update instructor
set salary = salary * 1.5
where salary < 7000
4、操作关系的属性
增加属性
alert table Student add student_register;
alert table Student add student_register Beijing;
删除属性
alert table Student drop student_register;
5、更名运算 as
select S.student_name
from Student as S , Class=C
where S.sex = 'woman';
6、去重 distinct
select distinct student_name
from Student
where Student.student_sex='women';
7、排列元组显示次序 asc升序,desc降序
select *
from Student
order by Student.student_id asc;
8、between比较运算符
select *
from instructor
where salary between 9000 and 10000;
等价于
select *
from instructor
where salary >= 9000 and salary <= 10000;
9、集合运算 union/intersect/except 并、交、差
10、空值 is null is not null
11、聚集函数
平均值:avg;
最小值:min;
最大值:max;
总和:sum;
计数:count;
12、分组聚集 group by
任何没有出现在group by 字句当中的属性如果出现在select字句中,则该属性只能出现在聚集函数的内部否则为错误的。
一个错误的例子:
select dept_name,ID,avg(salary)
from instructor
group by dept_name
13、having字句
类似where字句,但是对group by才起作用
14、重复元组存在性测试 unique
15、嵌套子查询
with字句
with max_budget(value) as
(
select max(budget)
from department
)
select budget
from department,max_budget
where department.budget = max_budget.value;
from语句中的子查询
select dept_name,avg_salary
from (
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name
)
where avg_salary>4200
标量子查询(可出现在返回单个值的表达式能够出现的任何地方)
select dept_name,
(
select count(*)
from instructor
where department.dept_name = instructor.dept_name
) as num_instructor
from department
16、创建索引