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 |
+-------+------+