数据定义语言 DDL
DDL is Data Definition Language statements. Some examples:数据定义语言,用于定义和管理 SQL 数据库中的所有对象的语言
create 创建 alter 修改 drop 删除 truncate 清空
#一、数据库的操作
#1.创建数据库
CREATE DATABASE [库名];
#2.删除数据库
DROP DATABASE [库名];
#3.查看当前连接下的所有数据库
SHOW DATABASES;
#二、数据表的操作
#1.创建数据表
create table 表名(
字段名 字段类型 [长度] [约束] ,
...
);
#2 修改表的结构
#2-1 添加列 ALTER TABLE 表名 ADD [COLUMN] 列名 VARCHAR(20);
#2-2 删除列 ALTER TABLE 表名 DROP [COLUMN] 列名;
#2-3 修改列(类型) ALTER TABLE 表名MODIFY [COLUMN] 列名 新类型 ;
#2-4 修改表名 ALTER TABLE 表名 RENAME TO 新表名;
#2-5 修改列名 ALTER TABLE 表名 CHANGE 旧列名 新列名 CHAR(2);
#3查看表的结构
DESC 表名;
SHOW CREATE TABLE [表名]; -- 查看建表语句
#4 删除表
DROP TABLE 表名;
清空表数据: TRUNCATE [表名]
#5 表的复制
#仅仅复制表结构
CREATE TABLE [表名] LIKE student;
#复制表的结构和数据
CREATE TABLE [表名] SELECT * FROM student 【WHERE 条件 】;
#6 数据库中的约束
NOT NULL 非空约束,规定某个字段不能为空
UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
PRIMARY KEY 主键(非空且唯一) ★
FOREIGN KEY 外键
CHECK 检查约束
DEFAULT 默认值
创
列级约束
name varchar(20) not null unique,
表级约束
create table 表名(
constraint pk primary key(..)
auto_increament, --
自增长
constraint fk foreign key (表中列) references 主表(关联列)
constraint uq unique(..)
)
删
alter table 表名 drop 约束类型 约束名 -- 主键不用别名
alter table 表名 drop index 约束名 -- 删除唯一键
非空约束直接 modify 属性即可
增
alter table 表名 add constraint 约束名
添加外键
alter table 表名 add constraint 约束名
foreign key(表中列) references 主表名(主表列) on delete set null
#1.唯一约束
#2.主键约束
要求必须不为空,一个表只能有一个
#3.外键约束
1.设置外键的表称为从表,被引用的表称为主表
2.从表中的列值必须来自于主表的关联列
3.插入数据,要先插入主表,再插入从表
删除数据,要先删除从表,再删除主表
4.添加外键时
可以选择 On Delete和On Update都有Restrict,No Action, Cascade,Set Null
on delete set null
restrict(约束):当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除。
no action:意思同restrict.即如果存在从数据,不允许删除主数据。
cascade(级联):当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则也删除外键在子表(即包含外键的表)中的记录。
set null:当在父表(即外键的来源表)中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)
ON UPDATE
restrict(约束):当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许更新。
no action:意思同restrict.
cascade(级联):当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则也更新外键在子表(即包含外键的表)中的记录。
set null:当在父表(即外键的来源表)中更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(不过这就要求该外键允许取null)。
注:NO ACTION和RESTRICT的区别:只有在及个别的情况下会导致区别,前者是在其他约束的动作之后执行,后者具有最高的优先权执行。
注:同一个字段可以同时又多个字段每个字段也可以 update和delete 的设置同时存在
5.主表和外表的关联列要求类型必须一致,名称没有要求
6.主表的类要求必须是主键
注:外键关联最好只在逻辑上有,并不需要在库中添加外键,会降低效率
数据操作语言 DML
DML is Data Manipulation Language statements. Some examples:数据操作语言,SQL中处理数据等操作统称为数据操纵语言
#增
insert into [表名](字段名,字段名...) values(值,值)
字段名不分先后,可多可少必须有主键,
#删
delete from [表名] where
#改
update 表名 set 列名=新值,列名=新值 where 条件
#查
条件查询
select 输出内容 from 表
where
1.between 数值 AND 大数值
2.in(值,值,值....)
3.like ‘%_..........’
4.is null
连接查询
. 可以连接库,表,列 明确指定,避免模糊查询
笛卡尔数值,避免重复用限定条件连接多表
-1.等值连接 e.department_id = d.department_id (特殊情况自连接)
-2.非等值连接
SELECT last_name,employee_id,salary,grade_level
FROM employees e,job_grades g
WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
内连接
FROM 表
INNER JOIN 表 ON 连接条件
外链接
FROM 表
LEFT OUTER JOIN 表 ON 连接条件 --左表是主表
右外连接: left 改 RIGHT......
其中INNER 和OUTER可以省略
查询排序 SELECT * FROM 表 ORDER BY 数据1 ,数据2......
DESC倒序 | | ASC顺序
分页查询 : limit 起始行,查询行数,起始行从零开始
分组查询
聚合函数:group by 分组的字段名,字段名... having 条件
关键字的顺序:
select [distinct] ... AS , .....
from 表
where 分组前的条件 (select...)
join 表 on 条件
group by 字段
having 分组后的条件
order by 排序的子句
子查询
单行操作符:
> < >= <= = <>
多行操作符:
in(重点)、any(任意一个)、all(所有的)
not in
分类:
单行子查询: 子查询的结果集是一行
多行子查询: 子查询的结果集是多行
注意事项:
子查询要包含在括号内。
将子查询放在比较条件的右侧。
单行操作符对应单行子查询,多行操作符对应多行子查询。
数据控制语言 DCL
DCL is Data Control Language statements. Some examples:数据控制语言,用来授予或回收访问数据库的某种特权,
并控制数据库操纵事务发生的时间及效果,对数据库实行监视等
COMMIT ROLLBACK TRANSACTION
事务
SET autocommit=0; --0和false等效
START TRANSACTION;
-- 执行语句
(commit提交 、rollback回滚)
ROLLBACK;
特点:ACID
原子性 :一个事务中的所有语句是一个整体 不可再分割
一致性:数据的状态保证完整性
隔离性:一个事务和其他事务之间是互不干扰的,相互隔离的
永久性:一个事务提交成功后,持久化到本地
分类:
隐式事务:没有明确的开始和结束标记
一条insert、update、delete
显式事务:有明确的开始和结束标记
希望多条sql语句 要么都执行,要么都不执行
-------------------------------------------------------------------------------------------------------------
别名只有表级别的别名可以直接用在函数语句中
一些简单常用的函数
show variables like '%char%';显示当前字符类型变量配置
select now(); 当前时间
select upper('字符串') select lower('...')
select concat(str,str,str,.....)连接
select substr('HelloWorld',1,3)Hel 左闭右闭区间,可以超范围
select length('')获取字节长度
select lpad('fa',10,'指定符号') select rpad(.......)
select trim('*' from '** hello world ***')
select instr('supstr','substr')
select replace('supstr','前','改成什么')
数学函数 truncate 截断 round 四舍五入 mod 取模
SELECT TRUNCATE(13.567,-1);
max求最大值 (支持 数值、字符、日期类型)
min求最小值支持 (数值、字符、日期类型)
sum 求和 (支持数值型)
avg 求平均值 (支持数值型)
count 计算非null的个数
count(*) 表中的行数
条件表达式:
case 判断的字段
when 值 then 结果
when 值 then 结果
.....
else 结果
end