SQL学习笔记（MySQL为例）

关系数据模型
关系（Relation）：一个关系对应通常说的一张表
元组（Tuple）：表中的一行即为一个元组
属性（Attribute）：表中的一列即为一个属性，给每一个属性起一个名称即属性名
主键（Key）：表中的某个属性组，它可以唯一确定一个元组
域（Domain）：属性的取值范围
分量：元组中的一个属性值
DDL
create
create database 数据库名 default character set 字符集 collate 校对集
注：数据库名称不能重复，不能出现除字母、数字和下划线以外的字符，不能超过30个字符，不能与系统关键字冲突
use 数据库名
创建名为class16的数据库，并查看


查看MySQL支持的字符集命令：SHOW CHARACTER SET
查看MySQL字符集支持的校对集命令：SHOW COLLATION



create table 表名(属性(数据类型) 约束) engine=表类型 default charset=字符集
常用数据类型:int/float/varchar/date/time
注：varchar 必须要有字符串长度
primary key 主键约束：能够唯一标识一行的属性组
foreign key 外键约束：A表中的属性是另外一个B表中能够唯一确定一行记录的键，这个属性就称为A表的外键，其中B表被称为主表或外表，A表称为从表
on delete/on update cascade/set null/no action
unique 唯一约束
not null 非空约束
null 空值约束
注：如果同一个属性有多个约束，中间使用空格分隔开
show tables ：查看所有创建的表
desc 表名 : 查看表的属性
创建表student


alter
alter database 数据库名 deault character set 字符集 collate 校对集
修改字符集，一般对数据库的修改就是修改字符集


alter table 表名 add 属性名 数据类型(约束)
增加age 属性


alter table 表名 modify 属性名 数据类型(约束)
将age 属性数据类型修改为tinyint


注：modify 用于修改属性数据类型，而change可修改属性名称
alter table 表名 change 属性原名 属性新名 数据类型(约束)
将age 属性改名为age1，数据类型修改为int


alter table 表名 drop 属性名
删除属性age1


当表中只有一个属性id，进行删除





注：如果表中只有一个属性，不允许删除
alter table 表名 add constraint 约束别名 约束
为name添加唯一约束


注：不能用此命令添加非空约束可使用modify
alter table 表名 drop 约束
注：无法用drop删除唯一约束，唯一约束也是索引，用index 关键字删除





alter table 表名 rename to 新表名
修改表student为student666


rename table 原表名 to 新表名
修改表student666为student


drop
drop database 数据库名称
删除数据库class16；





drop table 表名
删除表student


DML
insert
insert into 表名(属性1,属性2,······)values(值1,值2······)
只向


值与属性要一一对应，数据类型要匹配，满足属性约束
不指定属性时默认向表中所有属性插入数据
insert into 表名1(属性1,….) select 表名2.属性1,…. from 表名2
update
update 表名 set 属性1=新值1,属性2=新值2·······where条件子句
修改周舟为周洲


注：where 条件子句很重要
delete
delete from 表名 where 条件子句
删除id为2006612的记录


truncate
truncate 表名 ：删除表中的数据
插入数据，用truncate 删除





truncate比delete速度快，且使用的系统和事务日志资源少，原理不同，但是如果是误删，不可恢复，慎用
DQL
select
select 属性1,属性2,······from 表名
通配符：* 代表所有，可使用 * 代表查询表中所有列
查询student表中所有信息


限定查询
where字句



比较运算符
= 、> 、 >= 、 < 、 <= 、 <>/!=



between ···and···在两值之见（包含）
注意：包含两值，等效于 id>=18 and id<=19


in(list)匹配列出的值
注：in括号内的值是一个满足条件的集合，不是一个范围，如此例中并没有2006619，等效于or


like 匹配一个字符模式



is null 匹配空值
通配符：% 代表若干个字符; _代表一个字符
逻辑运算符 ：and(&&) or(||) not(!)
！在使用时后面() 不能省略
在where字句中日期格式20160301 不用引，日期格式"2016-03-01" 要用引号
字符串不区分大小写
distinct 去重复





as 设置别名，可以省略



order by 排序
向student表中插入grade 属性和数据，然后按分数排序






可指定多个属性
desc 降序 descend
asc 升序 ascend
group by 分组
对查询结果按指定属性分组进行统计
聚合函数
count
avg
sum
max
min
having 在分组后进行进一步限定，常和group by 一起使用
增加stu_group属性，并以此分组求各组平均分，并取整







limit 参数 ：限定查询返回的记录数



如果有一个参数，该参数表示从表开始位置查询对应的记录数量
如果有两个参数，那么第一个参数表示起始位置与第一行的偏移量，第二个参数表示查询的数量
特别注意：有两个参数时，参数不可括起来
函数
char_length() 返回x字符数
concat(str1,str2)将字符串str1，str2连接起来
curdate(）：返回当前日期
curtime() ：返回当前时间
now() ：返回当前日期和时间
last_day(x) ：返回日期x所在月份最后一天
date_add(x,interval n f) ：返回日期x加上间隔时间n 的结果，f可以是day，month，year
round(x): 四舍五入返回x的整数部分
round(x,y):对x精确到小数点后y位的结果
if(expr,x,y) :expr 成立，返回x,否则返回y
select 表1.属性，表2.属性 from 表1，表2 where 表1.属性=表2.属性 and 限定子句



select 表1.属性，表2.属性 from 表1 inner join 表2 on 表1.属性=表2.属性 where 限定子句



select 表1.属性，表2.属性 from 表1 left join 表2 on 表1.属性=表2.属性 where 限定子句
返回左表全部记录和右表符合匹配条件记录
select 表1.属性，表2.属性 from 表1right join 表2 on 表1.属性=表2.属性 where 限定子句
返回右表全部记录和右表符合匹配条件记录
子查询



将一个SELECT语句A使用(嵌套)于另外一个SELECT语句B中，则A即为B中的子查询
子查询一定要使用括号括起来
多行子查询用 in
(select语句)union(select语句) 合并结果
默认去除重复
不去重复用union all
intersect 取交集
minus 取差集（oracle）
index
create index 索引名 on 表名(参数)
show index from 表名



注：在指定primary key 后，会自动给主键创建索引，所以可以查看到两个索引
drop index 索引名 on 表名



view
create view 视图名 as select语句



show table status where comment=“view”
alter view 视图名 as select语句
create or replace view 视图名称 as select语句
drop view 视图名
删除视图


procedure
create procedure 存储过程名(参数列表) 程序体



参数列表每一个参数由输入输出类型、参数名称、数据类型构成
输入 in 输出 out 输入输出 inout
程序体以 begin 开始，end 结束
call 存储过程名称(参数列表) ： 调用存储过程
into 传递数据给输出参数
declare
declare 变量名 变量类型 default 默认值
变量的作用范围是begin end 程序体中
变量赋值方法：set 变量名=值；select 属性 into 变量名 from 表名
show procedure status
查看所有的存储过程


show create procedure 存储过程名
查看某个具体存储过程


drop procedure 存储过程名



if 语句
if 条件1 then ··· elseif 条件2 then··· else··· end if
注意：if 和 end if成对出现
case 语句
case 变量 when 值1 then ··· when 值2 then··· else··· end case
注意：case 和 end case成对出现
while 语句
while 条件 do ··· end while
使用while 循环向student表插入大量数据