MySql_DDL

数据库操作

创建数据库

        create database 数据库名 charset utf8；

-- 创建数据库
create database Test charset utf8；

查看数据库

        show databases；

        show create database db；

        select database();

选择数据库

        use 数据库名

删除数据库

        drop database 数据库名

修改数据库

        alter database db charset utf8;

表的操作

创建表

        对数据提前有一个字段及类型的整理

-- 创建表格
create table student(
snum int,
sname varchar(255)
gender char(1),
birthday date,
schooltime date,
major varchar(255),
department carchar(255),
createtime timestamp   
);

-- 查询语句创建表
create table mirror as select * from student;

修改表

添加一列

alter table student add email varchar(20);

删除一列

alter table student drop column email

修改行

-- 修改的前提：
--  列必须存在，
--  修改后的类型能兼容，即已在数据能进行转换
alter table student modify(email varchar(255));

修改列名

alter table student rename column birthday to birth;

修改表名

rename stduent to students

删除一张表

drop table students;

 设置默认值

-- 添加
alter table 表名 alter 列名 set default '值';

-- 删除
alter table 表名 alter 列名 drop default;

表的约束

        约束：在插入数据时，对数据的约束。

        主键约束PRIMARY KEY

                主键必须唯一标识表中的每一行，且不能为NULL，即表中不可能存在有相同主键值的两行数据。

                主键分为单字段主键和多字段联合主键

                联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则

-- 单主键
create table t_student(
id int(20) primary key,
name varchar(255),
);

-- 多主键
create table t_student(
id int(20),
num int(20),
grade float,
primary key(id,num)
);

-- 添加主键
alter table t_student add primary key(id);

-- 删除主键
alter table t_student drop primary key;

唯一性约束 UNIQUE

        列中的值可以为空但不能相同

create table tb_unique(
id int(11) primary key,
name varchar,
phone varchar(11) unique,
location varchar(50)
);

-- 添加唯一性约束
-- alter table 表名 add constraint 唯一约束名 UNIQUE(列名);
alter table tb_unique add constraint t_unique_location unique(name);

-- 删除唯一性约束
-- alter table 表名 drop index 唯一约束名;
alter table tb_unique drop index t_unique_location;

非空约束 not null

create table tb_unique(
id int(11) primary key,
name varchar unique,
location varchar(50)
);

-- 添加非空约束
-- alter table 表名 change column 字段名 字段名 数据类型 not null;
alter table tb_null change column location location varchar(20) not null;

-- 删除非空约束
-- alter table 表名 change column 字段名 字段名 数据类型 null；
alter table tb_null change column location location varchar(20) null;

外键约束

        外键的主要作用：保证记录的一致性和完整性。

        主表必须已经存在于数据库中，或是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构成为自参照完整性。

        必须为主表定义主键

        主键不能包含空值。即，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

        在主表的表名后面指定列名的组合。这个列或列的组合必须是主表的主键或候选键

        外键中列的数目必须和主表的主键中的列的数目相同

        外键中列的数据类型必须和主表主键中对应列的数据类型相同。

外键删除

                级联删除：cascade

                设置为null：set null

                阻止删除：no null / restrict

                删除主表数据的时候，要保证这个ID没有被子表所使用

-- 创建teacher表
create table teacher(
tid varchar primary key
name varchar
);

-- 创建student表
-- 创建外键
create table student(
sid varchar primary key,
name varcahr,
tid carchar,
constraint fk_teacher_student_tid foreign key(tid) references teacher(tid) on delete cascade
);

-- 修改表的外键
alter table 表名 add constraint 外键名 foreign key(列名) references 主表名 (列名);


-- 删除外键
alter table 表名 drop foreign key 约束名;

索引

        存储引擎用于快速找到记录的一种数据结构       

        索引对于搜索的良好性能非常关键，表中的数据量越来越大，索引对性能的影响就很重要。

        索引优化应该是对查询性能优化最有效的手段，创建一个真正最优的索引经常需要重写SQL查询语句。

-- 创建索引
-- create index 索引名 on 表名(列名);
create index index_student_sname on t_student(sname);

索引分类

        常规索引、主键索引、 唯一索引、 外键索引

索引是数据库中一块独立的空间，专门存储索引值的一颗B-树

索引选择

        避免使用列中含有大量重复的数据

索引优点

        大大减少服务器需要扫描的数量

        避免排序和临时表

        将随机I/O转换成顺序I/O

索引缺点

        影响数据库的增删改速度

视图

        是一种虚拟存在的表，同真实存在一样，视图也由列和行组成，但视图不存在于数据库中。

        行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

优点：

        定制用户数据，凸显特定数据

        简化数据操作

        提高数据的安全性

        共享所需数据

        更改数据格式

        重用SQL语句

创建视图

create view 视图名 as select语句

        视图名：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名

        select语句：指定创建视图的select语句，用于查询多个基础表或源视图

create view v_swordsman as
select tid,name from teacher
union all
select sid,name from student;

查询视图

select * from v_swordsman

查看视图信息

describe 视图名

查看视图细节信息

show create view 视图名;

修改视图

alter view 视图名 as select语句

删除视图

drop view if exists 视图名1，视图名2... ...

三范式

        第一范式：

        数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组

        第二范式：

        数据库中每行数据的列都必须依赖于主键(即和主键有关系），每一个行数据都要有主键

        主键是一行数据的唯一性标识

        第三范式：

        表中非主键的列要完全依赖于主键，不能出现部分属性依赖于其他属性

        当出现传递依赖的时候要将非依赖于主键的列专门创建一张表进行管理

表与表的关系

        为了维护两张表的关系，根据两张表的对应关系可分为

        1：1

                两张表中的数据1条只和1条对应

                关联方案

                        1. 让关联数据主键值相同

                        2.  在一张表中存放另一张表的主键

        1：N

                A表中的一条数据有可能对应B表的多条记录

                1老师：N学生

                        在N方的表中设计一个1方的主键列，也称为外键关联

        N：N

                A表中的一条数据有可能对应B表中的多条记录

                同时B表中的一条数据右可能对应A表中的多条记录

                1学生：N课程

                1课程：N学生

                一般创建第三张表专门管理学生与课程的关系

                      1.  sid,cid作为联合主键管理信息

                      2.  id，sid，cid以ID作为这张表的单独主键