目录
三、DDL与DML
一、数据定义语言DDL
1、数据库的创建
(1)创建数据库
①查看数据库
show databases;
②创建数据库
create database <数据库名>;
例:
create database db_1; -- 创建一个数据库,数据库名为db_1
③如果数据库名不存在就创建数据库:
create database [if not exists] <数据库名>; -- 如果数据库名不存在则创建数据库,否则不创建
(2) 删除数据库
drop database <数据库名>; -- 删除名称为<数据库名>的数据库
例:
drop database DB_1; -- 删除数据库DB_1
(3) 使用数据库(进入数据库)
use 数据库名; -- 进入数据库,只有进入了数据库才能在数据库里创建数据表
例:
use DB_1; -- 使用数据库DB_1
2、数据表的创建
(1) 创建数据表
create table [if not exists] <表格名称> (
属性名1 属性类型 [约束...],
属性名2 属性类型 [约束...],
属性名n 属性类型 [约束 ] )[engine innodb] [charset=utf8];
# 创建表并设置它的属性名、数据类型、存储引擎和字符集
如:
create table students( -- 创建一个学生数据表
s_id int, -- 学号
s_name varchar(10), -- 姓名
s_sex varchar(4), -- 性别
s_age int, -- 年龄
s_cid int -- 所在班级
);
(2) 查看表结构
desc 表名; -- 查看数据表的属性、数据类型、约束等
或:
show columns from 表名;
如:
desc students;
show columns from students;
(3) 修改表结构
①增加表中列
alter table <表名> add <属性名> <数据类型>; -- 向数据表中添加一个属性列
如:
alter table students add s_score float; -- 向数据表students中添加一个属性列s_score
②删除表中列
alter table <表名> drop column <属性名>; -- 删除数据表中某个属性列
如:
alter table students drop column s_cid; -- 删除数据表students中s_cid属性
③修改表中列
alter table <表名> modify column <属性名> <数据类型>; -- 修改数据表中某个属性列的数据类型或约束
如:
alter table students modify column s_name varchar(30); -- 将属性s_name的数据类型修改为varchar(30)
alter table students modify column s_score double; -- 将属性s_score的数据类型修改为double
④修改列名称
alter table <表名> change column <旧列名> <新列名> <数据类型>; -- 修改数据表中某个属性的名称
如:
alter table students change column s_id s_num int; -- 将属性名s_id修改为s_num
(4) 删除数据表
drop table <表名>; -- 删除数据表
如:
drop table students; -- 删除数据表students
(5) 查看数据表
show tables; -- 查看正在使用的数据库中存在的数据表
如:
(6)修改表名称
rename table <原表名> to <新表名>; -- 修改某个数据表的名称
二 、DML数据操纵语言
1、数据的插入
①插入所有属性值
insert into <表名> [value | values](属性值1,属性值2,……,属性值n);
如:
insert into students_tb value(1,'小明','男',16,2); -- 向students_tb表中插入一条数据
②插入部分属性值
insert into <表名>(属性名1,属性名2,……,属性名n) [value | values](属性值1,属性值2,……,属性值n);
如:
insert into students_tb(s_id,s_name) value(2,'小花');
insert into students_tb(s_name,s_id,s_age,s_sex,s_cid) value('小王',3,18,'男',1);
③插入多条数据
insert into students_tb values(4,'小胖','男',17,3),(5,'Bob','男',18,2),(6,'C','女',15,1);-- 多条数据之间用逗号','分隔开来
2、修改数据(更新数据)
①修改所有数据
update <表名> set <属性名> = <属性值>; -- 修改数据表中某个属性的取值
如:
update students_tb set s_sex = '女'; -- 修改students_tb表中s_sex属性的取值为'女'
②条件修改
update <表名> set <属性名> = <属性值> [where <条件表达式>]; -- 修改数据表中满足条件的某个属性值
如:
update students_tb set s_sex = '男' where s_name = '小明'; -- 修改'小明'的性别为'男'
③多条件修改
update students_tb set s_sex = '男' where s_name = '小胖' || s_name = '小王' or s_name = 'bob';-- 多个条件满足任意一个用'||'、'or'、'OR'(逻辑或)
update students_tb set s_age = 16,s_cid = 1 where s_name = '小花'&& s_id = 2;
-- 多个条件同时满足用'&&'、'and'、'AND'(逻辑与)多条件同时满足用与(&&,and,AND),
满足其中任意一个用或(||,or,OR) where之后的条件语句可以使用比较运算符:=,>,<,>=,<=,!=(<>)
3、删除数据
①删除全部数据
delete from <表名>; -- 删除数据表中所有数据
如:
delete from students_tb;
②条件删除
delete from <表名> where <条件表达式>; -- 删除数据表中满足条件的数据
如:
delete from students_tb where s_name = '小花';
③多条件删除
delete from students_tb where s_name = '小胖' || s_id = 5;
-- 注意:同样都为删除,delete和drop的区别
delete是删除数据表数据时使用的,是相对于数据而言,对数据的删除。
drop是删除数据库对象时使用的,是相对于数据库而言,对数据库对象(如:数据库、数据表、函数、视图等)进行删除。
# alter和update的区别与delete和drop的区别类似
四、数据库完整性
一、约束相关性概念
1、什么是数据完整性
数据完整性是指存储在数据库中的数据要能正确反映实际情况,规定输入的数据不能是无效值、错误值或者乱码等。
2、数据完整性的类型
①实体完整性:标识符或者主键的完整性,使其值唯一。
②域完整性:限制类型、格式和取值范围等。
③引用完整性:保持原表和引用表数据的一致性。
④自定义完整性:用户自定义的业务规则
3、数据完整性的实现方式
①实体完整性:主键约束、唯一值约束
②域完整性:检查约束、默认值约束
③引用完整性:外键约束
④用户自定义完整性:以上约束的自由组合
4、约束
数据完整性约束也就是创建表( create table )时的约束条件,是用来限制属性或者表中数据的,也可以通过修改语句( alter table )修改约束条件。
4.1、约束的分类:
约束又分为行级约束(约束产生作用的范围只针对行)和表级约束(影响表与表之间的约束),行级约束和表级约束本质上是一样的,只是约束的效力和范围不同。
非空约束、默认值约束、主键约束一般为行级约束;
外键约束(联合主键)是表级约束;
检查约束(check) 既可以是行级约束也可以是表级约束
4.2、各种约束及其描述如下所示:
二、完整性约束
1、非空约束
确保当前约束的属性不为空值(null),非空约束只能出现在表对象的列中,是行级约束。表示该属性的取值不能为空,如果插入的数据为空,会被系统阻止插入。(数据库中如果不给属性值,属性的取值默认为空)
非空约束关键字:not null
创建非空约束的语法格式:
create table <table_name>(
no int not null,
name varchar(20) not null
);
#创建一个表,使编号和名称不能为空
2、表约束的修改
# 修改表中行级属性约束的一般格式:
例:
alter table <表名> modify column <属性名> <数据类型> [约束];
例:
alter table teachar modify column t_name varchar(20) not null;
# 修改表中的属性约束为非空约束:
#通过修改表去掉非空属性约束:
alter table teachar modify column t_no varchar(20);
注意:修改表属性的时候,会对原表中的数据进行一个检查,如果出现取值冲突,则无法修改表约束。
3、唯一约束
指定某列或者某几列数据的取值不能重复。
唯一约束关键字:unique
① 创建表时添加唯一约束:
create table teacher_tb(
t_no int not null unique,
t_name varchar(20)
);
② 指定列添加唯一约束:
alter table <表名> add 约束类型(属性名);
#示例:可以通过constraint给约束取别名
alter table <表名> add constraint <约束名> unique(属性名);
③ 删除唯一约束:
alter table <表名> drop index <属性名/约束名>;
3.1、复合唯一约束
创建复合唯一约束(表级约束),可以对多个列进行唯一约束,约束两个以上的字段(属性)不能同时一致。
① 在创建表时添加复合唯一约束
constraint <约束名> unique(<属性1>[,属性2][,…属性n])
② 在创建表后添加复合唯一约束
alter table <表名> add constraint <约束名> unique(<属性1>[,属性2][,…属性n]);
③ 复合唯一约束得到删除(根据约束名删除)
alter table <表名> drop index <约束名>
# 这里也就是约束属性1、属性2到属性n中可以允许其中存在取相同值的属性,但不能同时相同,至少要有一个不同。
4、主键约束
# 数据库中如果一个数据表没有主键约束,默认第一个非空且唯一的属性为主属性(主键)
1、主键约束就是非空约束+唯一约束,用于规定实体完整性。
2、且每个表只能有一个主键约束(表级),在多列联合的主键约束时,联合主键的值不能同时重复。
主键约束关键字:primaty key。
① 在创建表时设置单列主键
create table <表名>( 属性名 数据类型 primary key, # 设置主键约束 属性名 数据类型 not null )
② 设置多个属性为联合主键
alter table <表名> add [constraint <约束名>] primary key(属性1[,属性2,…,属性n]);
-- 如果表中添加主键的字段已经存在空值或重复值,则不允许添加主键。
③ 删除主键约束
alter table <表名> drop primary key; # 直接丢掉主键
5、自增列
自增列就是在插入数据时,如果不规定属性的值,其值就会根据上一个属性的值自动加1。
自增列关键字:auto_increment(行级约束)
① 创建表时添加自增列
create table <表名>( id int primary key auto_increment, # 将id设置为自增列 );
② 添加自增列
alter table <表名> modify column <属性名> <数据类型> auto_increment = 100;
# 可以设置自增列的初始值(默认值为1)
③ 删除自增列
alter table <表名> modify column <属性名> <数据类型>;
6、默认值约束
给带有默认值约束的属性添加数据时,如果没有给定属性值,会自动添加一个默认值。
默认值约束关键字:default
① 在创建表时设置默认值约束
create table <表名>( name varchar(20) not null,sex varchar(2) default '男'); # 性别默认为'男'
② 添加/删除默认值约束
alter table <表名> modify column <属性名> <属性类型> [default <默认值>];
7、检查约束
检查约束就是检查列中属性的取值范围,判断字段的值是否为指定的值,如果不是就不允许数据的插入或修改。
检查约束关键字:check
如:
age int check(age>=0 and age=<150), # 约束年龄的取值范围为0~150之间
sex varchar(2)check(sex='男' or sex='女') # 约束性别的取值范围为'男'或'女'
# 注意:MySQL不支持检查约束,需要在外部进行判断
8、外键约束
外键约束是指一个表中的属性值参考另一个表中的主属性的值,也就是外键值参考主键值,由于外键约束是表级约束,所以在创建参考表之前要先创建被参考表。
① 创建表时建立外键约束
constraint <约束名> foreign key(<属性名>) references 被参考表(<被参考属性名>)
② 添加外键约束
alter table <表名> add constraint <约束名> foreign key(<属性名>) references 被参考表(<被参考属性名>);
③ 删除外键约束
alter table <表名> drop constraint <约束名>;
# 根据约束名删除约束
9、级联更新/删除
新/删除是指父表中的元组更新或删除等操作对子表的处理方法,其有三种处理方法:
# 在更新/删除中:(on update/on delete)
-- ① cascade:父表中的元组更新或删除,在子表中跟着更新或者删除
# 在外键约束后加上:on update cascade/on delete cascade
-- ② no action:无动作,若子表中有匹配的记录,就不允许更新/删除父表的数据
# 在外键后加上:on update no action/on delete no action
-- ③ set null:设置为空,若子表中有匹配的记录,更新/删除父表中的数据,子表中对应数据设置为空
# 在外键约束后加上:on update set null/on delete set null
可以同时设置更新和删除所对应的操作
-- 在外键约束后加上:on update cascade on delete no action
# 父表在更新数据时,若子表中有匹配的记录,子表同步更新匹配的数据,并且若子表中有匹配的记录无法删除父表数据
示例:
create table student1(
s_id int primary key,
s_name varchar(20) not null,
s_cid int not null,
s_age int,s_sex varchar(2),
constraint for_scid foreign key(s_cid) references class(class_id) on update cascade on delete set null );
# 学生表中的班级编号s_cid参考班级表中的班级编号class_id