数据库关键字使用

notes-关键字

.不定期更新此文
.仅以此作为个人笔记，若有错误之处，还望评论指正

/*
举例用数据库-大学数据库模式：
classroom(building, room_number, capacity)
department(dept_name, building, budget)
course(course_id, title, dept_name, credits)
instructor(ID, name, dept_name, salary)
section(course_id, sec_id, semester, year, building, room_number, time_slot_id)
teaches(ID, course_id, sec_id, semester, year)
student(ID, name, dept_name, tot_cred)
takes(ID, course_id, sec_id, semester, year, grade)
advisor(s_ID, i_ID)
time_slot(time_slot_id, day, start_time, end_time)
prereq(course_id, prereq_id)
*/

distinct 查询修饰-去重

eg:
select distinct dept_name
from instructor;

where 子句 中的逻辑连词： and or not < > <= >=……

natural join 自然连接-合并多张表

eg：
select name, course_id
from instructor natural join teacher;

join using 合并时指定哪一项属性相等(通过哪一项属性链接)

eg:
select name,title
from (instructor natural join teacher) join course using(course_id);

as 可用来查询时更名属性名（自定义查询出来的表的属性）或重定义表名

（类似于C语言的 typedef, 为属性或表设置别名）
eg1：
select name as instructor_name,course_id
from instructor, teacher
where instructor.ID = teacher.ID;
eg2:
select T.name， S.course_id
from instructor as T, teacher as S
where T.ID = S.ID;

字符串匹配关键字：
like : 比较运算
% : 匹配任意字符串
_ : 匹配任意字符
regexp : 配合下列正则使用
^: 匹配输入字符串的开始位置
$: 匹配输入字符串的结束位置
{n}: 匹配确定的n次，这里的n必须时一个非负整数

eg1:
Intro% :匹配任何以Intro打头的字符串
%Intro%：匹配任何包含Intro的字符串
_ _ _:(中间的空格只是为了好区分)匹配只含三个字符的字符串
_ _ _%:(中间的空格只是为了好区分)匹配至少含三个字符的字符串
eg2:
select dept_name
from department
where building regexp ‘%Waston%’;
eg3:(以W开头的)
select * from department where building regexp “^W”;
eg4:(以W结尾的)
select * from department where building regexp “$W”;
eg5:
select * from department where building regexp “W{2}”;

escape 定义转义字符
eg:匹配 ab%cd 开头的字符串
like ‘ab%cd%’ escape ‘’;

* : 包含所有
eg:
selct structor.*
from instructor, teacher
where intructor.ID = teacher.ID;

order by 使查询结果排序 默认为升序 升序用关键字 asc 降序用关键字 desc

eg:
select *
from instructor
order by salary desc, name asc;

between and

eg:
select name
from instructor
where salary between 90000 and 100000;
注：在边界取值中不同数据库可能不同 mysql中为包含边界

union intersect except 类似数学中的∪ ∩ - 运算

注：以上关键字在不同数据库可能不支持，需要找等价写法代替，以下只讲union 其他类似
union 合并成一张表（会自动去重）
eg:
(select course_id
from section
where semester = ‘Fall’ and year = 2009)
union
(select course_id
from section
where semester = ‘Spring’ and year = 2010)

null 空值

select name
from instructor
where salary is null;

avg : 平均值

min : 最小值

max : 最大值

sum : 总和

count : 计数

eg1:
select avg(salary) as avg_salary, min(salary) as min_salary, max(salary) as max_salary
from instructor, sum(salary) as sum_salary
where dept_name = ‘Comp. Sci.’;
eg2:
select count(distinct ID)
from instructor;

group by 分组聚集关键字
eg:
select avg(salary) as dept_avg_salary
from instructor
group by dept_name;

having 对分组添加限定条件筛选
注：having子句中的谓词在形成分组后才起作用
eg:
select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name
having avg(salary) > 42000;

in 判断元组是否属于后接集合成员
not in 与上相反
注： 后接的集合可以是查询子句，也可以是枚举的集合
eg1:
select distinct course_id
from section
where semester = ‘Fall’ and year = 2009
and course_id in (
select course_id
from section
where semester = ‘Spring’ and year = 2010);
eg2:
select distinct course_id
from section
where semester = ‘Fall’ and year = 2009
and course_id not in (
select course_id
from section
where semester = ‘Spring’ and year = 2010);
eg3:
select distinct name
from instructor
where name not in (‘Morzant’, ‘Einstein’);

some any all

• 用于集合的比较
• some、any ：存在其中任意一个
• all：比所有的

exists

• 作为参数的子查询非空时返回true

drop

• 用于撤销、删除

eg1:#删除test库
drop database test;
eg2:#删除student表
drop table student;
drop table test.student;

check

eg1:#添加check约束：
alter table student add constraint student_chk_1 check(tot_cred >= 0);
eg2:#删除check约束：
alter table student drop check student_chk_1;

外键约束：

• cascade
  在父表上update/delete记录时，同步update/delete掉子表的匹配记录

• set null
  在父表上update/delete记录时，将子表上匹配记录的列设为null (要注意子表的外键列不能为not null)

• no action
  如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

• restrict
  同no action, 都是立即检查外键约束

• set null
  父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

• null、restrict、no action
  删除：从表记录不存在时，主表才可以删除。删除从表，主表不变
  更新：从表记录不存在时，主表才可以更新。更新从表，主表不变

• cascade
  删除：删除主表时自动删除从表。删除从表，主表不变
  更新：更新主表时自动更新从表。更新从表，主表不变

• set null
  删除：删除主表时自动更新从表值为NULL。删除从表，主表不变
  更新：更新主表时自动更新从表值为NULL。更新从表，主表不变

not null 非空

lower():

• 将字符串中大写全部变成小写

default

• 给字段属性设置默认值

index

• 索引,加快查询速度

eg:在某表上的某一字段上创建索引
create index index_name on table_name(column_name)

大对象类型:

clob :字符数据类型(文字)
blob :二进制数据类型(图片/视频)

mysqldump:

• mysql进行数据库备份的必要操作,到处数据库·表以及数据

eg:
$mysqldump -u user -p database_name > file_name;
$password:user_password
(文件生成在本目录)

truncate table table_name :

• 用于删除表中的数据,但不删除表结构，其删除是不可恢复的。
• 其效果类似于delete from（有记录可恢复） 但速率比其高,数据量越大区别越明显

eg:
mysql> CALL load_book_data(100000);
Query OK, 1 row affected (10.26 sec)

mysql> TRUNCATE TABLE books;
Query OK, 0 rows affected (0.01 sec)

mysql> CALL load_book_data(100000);
Query OK, 1 row affected (10.59 sec)

mysql> DELETE FROM books;
Query OK, 100000 rows affected (0.20 sec)

nvl(表达式1, 表达式2) :

• 空值转换判断 若表达式1为空则返回表达式2的值(即将空转换为一个实际的值)
  eg:
  对数字类型:nvl(comm, 0)
  对日期类型:nvl(hiredate, ‘31-dec-99’)
  对日期型:nvl(to_char(comm), ‘No Commission’)

to_date(date_str, date_format_str)

• 将输入的字符串日期格式化成相应的日期

alter table 表名 modify column 字段名 类型

• 修改表中字段的类型

rank() over() 与 dense_rank() over()

• 对数据库中的数据进行排序并添加序号
  eg：
  
  数据：
  
  rank() over():
  select score, rank() over(order by score desc) as ‘rank’ from Scores;
  结果：

+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 3    |
| 3.65  | 4    |
| 3.65  | 4    |
| 3.50  | 6    |
+-------+------+

dense_rank() over():
select score, dense_rank() over(order by score desc) as ‘rank’ from Scores;

+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+