第二章 数据库管理操作

1.练习

1.1 sql语句创建数据库

create database goodsdb
default character set utf8mb4
default collate utf8mb4_general_ci

1.2  创建表

create table goods(
	goodsId int,#商品编号
	goodsName char(6),#商品名称
	price decimal(5,1),
	produceDate date,#生产日期
	address char(20) #产地
);

1.3 插入数据

insert into goods(goodsId,goodsName,price,produceDate,address) values (1,'冰红茶',3.5,'2018-03-22','郑州');
insert into goods(goodsId,goodsName,price,produceDate,address) values (2,'可口可乐',3.5,'2018-01-23','郑州');
insert into goods(goodsId,goodsName,price,produceDate,address) values (3,'面包',5.0,'2018-07-01','洛阳');
insert into goods(goodsId,goodsName,price,produceDate,address) values (4,'牙刷',4.5,'2018-06-01','洛阳');

２.数据库的操作 

2.1 创建数据库

CREATE　DATABASE ［IF NOT EXISTS］ <数据库名>

[ [DEFAULT]  CHARACTER SET <字符集名> ]

[ [DEFAULT] COLLATE <校对规则名> ]

说明：

①[ ] 中的内容是指可选的；

②<数据库名>：创建数据库的名称；MYSQL的数据存储区将以目录方式表示MySQL数据库，因此数据库的名称必须符合操作系统的文件夹命名规则，不要以数字开头，尽量要有实际意义；(在MySQL中不区分大小写)

③IF NOT EXISTS:在创建数据库之前进行判断，只有该数据库目前不存在时才能执行操作，用于避免数据库已经存在而重复创建的错误；

④[DEFAULT] CHARACTER SET : 指定数据库的字符集，以避免在数据库中存储数据出现乱码的情况，如果不指定字符，那么就使用系统默认字符集；

⑤[DEFAULT] COLLATE: 指定字符集的默认校对规则。

2.2 删除数据库

drop database 数据库名;

2.3 切换数据库

use 数据库名;

 2.4 导入和导出(备份)

三种方式：

①cmd窗口命令，不进入mysql数据库:

#导出

mysqldump  -uroot -p --database 数据库名[表名,...]  > 保存的路径

#还原

 mysql -uroot -p  [数据库名] < 路径

② Navicat 备份

③Navicat 导出sql语句

 

3. MySQL存储引擎简介

存储引擎：

        数据库存储引擎是数据库底层软件组织、数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据；不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能；现在许多不同的数据库管理系统都支持 多种不同的数据引擎；MySQL的核心就是存储引擎。

存储引擎的选择：

功能	MYISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持数索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

①如果要提供提交、回滚、崩溃恢复能力的事务安全(ACID兼容)能力，并要求实现并发控制，InnoDB是一个好的选择；

②如果数据表主要是用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；

③如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果；

④如果只有INSERT和SELECT操作，可以选择Archive,Archive支持高并发的插入操作，但本身并不是事务安全的；Archive非常适合存储归档数据，如记录日志信息。

        一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。

查看当前MySQL默认引擎：

show variables likes '%engine%';

查看MySQL支持的引擎：

show engines; 

修改默认存储引擎

 如果修改本次会话的默认存储引擎(重启后失效)，只对本会话有效，其他会话无效：

set default_storage_engine = innodb;

修改全局会话默认存储引擎(重启后失效),对所有会话有效：

set global default_storage_engine = innodb; 

 4. 数据的操作

4.1 SQL简介

        SQL(Structured Query Language：结构化查询语言)是用于管理关系数据库管理系统(RDBMS);SQL的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。

4.2 SQL语言分类

①数据查询语言(DQL:Data Query Language) ：select

        其语句也称为“数据检索语句”，用于从表中获得数据，确定数据怎样在应用程序给出；保留字SELECT是DQL(也是所有SQL)用的最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY 和HAVING，这些DQL保留字常与其他类型的SQL语句一起使用；

②数据操作语言(DML：Data Manipulation Language): INSERT,UPDATE和DELETE

        分别用于添加，修改和删除表中的行；也称为动作查询语言；

③事务处理语言(TPL)

        能确保被DML语句影响的表的所有行及时得以更新，TPL语句包括BEGIN，TRANSACTION，COMMIT和ROLLBACK；

④数据控制语言(DCL)

        通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问；某些RDBMS可用GRANT或者REVOKE控制对表单个列的访问；

⑤数据定义语言(DDL) :根表的结构有关，create、drop、alter(更爱表结构)

        在数据库中创建表或删除表(CREATE TABLE 或 DROP　TABLE)；为表加入索引，DDL包括许多与人数据库目录中获取数据有关的保留字，它也是动作查询的一部分；

⑥指针控制语言(CCL)：

        像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

4.3 SQL常用操作语句

4.3.1 创建表

create table 表名

(

        ...

)

create table goods(
	goodsId int,#商品编号
	goodsName char(6),#商品名称
	price decimal(5,1),
	produceDate date,#生产日期
	address char(20) #产地
);

4.3.2 查询语句

select 列1,列2,...,列n

from 表名

where 条件

group by 列

having 条件

order by 列 asc/desc

limit 位置，记录数

select goodsId,goodsName
from goods 
where price > 3.5

4.3.3 插入语句 

#按字段插入

insert into 表名(列名1,列名2...) values (值1,值2...)

#插入一条

insert into 表名 values(id,name,age,address)

#同时插入多条语句

insert into 表名

(列1,列2,列3,列4)

values

(值1,值2,值3,值4),

(值1,值2,值3,值4),

(值1,值2,值3,值4);

#同时插入多条语句

insert into

表名

(列1,列2,列3,列4)

select 值1,值2,值3,值4 union

select 值1,值2,值3,值4

insert into goods 
value 
(1,'冰红茶',3.5,'2018-03-22','郑州'),
(1,'冰红茶',3.5,'2018-03-22','郑州'),
(1,'冰红茶',3.5,'2018-03-22','郑州'),
(1,'冰红茶',3.5,'2018-03-22','郑州'),
(1,'冰红茶',3.5,'2018-03-22','郑州')

4.3.4 修改语句

update 表名

set    列名1 = '列值1',

        列名2 = '列值2',

        列名3 = '列值3'

where 条件

update goods
set goodsId = 2,
	price = 4.5
where goodsName like '冰%'

4.3.5 删除语句

delete from 表名 where 条件

delete from goods where goodsId = 2;

5. 数据完整性

5.1 数据完整性(Data integrity)

 5.2 数据完整性分类

实体完整性(Entity Integrity):保证数据记录的唯一性，

域完整性(Domain Integrity):非空，默认值，外键约束，

引用完整性(Referential Integrity):外键约束，

用户自定义完整性(User-defined Integrity)

 5.3 实体完整性约束

        要确保实体的唯一性：主键约束，自增约束，unique约束；

5.3.1 主键约束(primary key)

        特点：加了主键约束的列要唯一且非空，主键列会自动生成索引；

        主键约束是行数据的唯一标识，不能为null，不能重复，一个表中应该有主键，只能有一个主键，有主键的表，查询效率高。

主键：数据库主键是指表中一个列，其值能唯一地标识表中的每一行，这样的一列称为表的主键，通过它可强制表的实体完整性；当创建或更改表时可通过定义PRIMARY KEY约束来创建主键，一个表只能有一个PRIMARY KEY约束，而且 PRIMARY KEY 约束中的列不能接受空值，由于 PRIMARY KEY约束确保唯一数据，所以经常用来定义标识列。

create table student(
    stuId int primary key;
)

#添加主键

alter table 表名

add constraint 约束名 primary key(列)

#删除主键

alter table 表名

drop primary key

 主键的主要作用：

①保证实体的完整性；

②加快数据库的操作速度；

③在表中添加新记录时，数据库会自动检查新记录的主键值，不允许该值与其他记录的主键 值重复；

④数据库自动按主键值的顺序显示表中的记录，如果没有定义主键，则按输入记录的顺序显 示表中的记录。

主键的特点：

①唯一性：一个表中只能有一个主键，如果在其他字段上建立主键，则原来的主键就会取 消；(联合主键)

②非空性：主键的值不可重复，也不可为空；

③一张表要有一个无意义的主键， 主键主要是用于其他表的外键关联，以及本记录的修改与 删除。

5.3.2 自增约束(auto_increment) 

特点：系统自动生成，自动加1；要和主键约束一起使用，一个表只能有一个自增列。

create table emp(
    empId int primary key auto_increment,
    empName varchar(20),
    sex char(1),
    hireDate date,
    phone varchar(20)
)

#对于自增列：不提供值的时候，系统自动添加

#自增会在上一条记录的基础上累加

insert into emp

(empName,sex)

VALUES

('李四','女')

#添加自增

alter table 表名

 modify column id int auto_increment

#删除自增

alter table 表名

modify column id int

注：自增值在某些场景下会出现断层

①在mysql存储引擎是innodb的情况下，如果使用select  union语法做批量插入，则会导致两次插入的自增值断层，而使用 insert  values语法不会导致这个问题，将表的存储引擎改为  MyISAM后这个问题可以解决；

②在innodb存储引擎下，在插入唯一约束数据重复，外键无效数据时，此时报错，会导致自增断层，而数据长度过长导致的插入不成功不会导致自增断层；

③在有自增列的表中删除记录之后，再插入数据时，自增不会连续增长。

5.3.3 唯一约束(unique) 

特点：确保数据的唯一性，允许null值；

create table emp
(
    empId int primary key auto_increment,
    empName varchar(20),
    sex char(1),
    hireDate date,
    phone varchar(20) unique # 唯一约束
)

#添加唯一约束

alter table 表名

add constraint 唯一键约束名 UNIQUE(列)

#删除唯一索引约束

alter table 表名

drop index 唯一键约束名

5.4 引用完整性

        引用完整性用于限定多表之间的关联关系。

5.4.1 外键约束(foreign key)

        确保表与表之间的数据的一致性；

        外键约束(foreign key)：主要是为了维护两个表之间的数据的一致性关系，是建立在主键的基础之上的，A表中的某列(外键)会引用B表中的主键；数据类型一致；体现表与表之间的关联关系；必须先有主键数据，才可以录入外键数据；外键要和主键数据一致；

        如果一张表的某个列指向另一个表的主键列，这个列就称之为外键列，这个表被称为从表，而其关联的目标表被称为主表。 

#创建班级表
create table classInfo(
    classId int primary key auto_increment,#主键自增
    className varchar(20) not null
)

#创建学生表，加外键约束
create table student
(
    studentId int primary key auto_increment,
    name varchar(20) not null,
    sex char(1) not null,
    bjId int ,
    foreign key (bjId) #针对当前学生表中的bjId加外键约束
    REFERENCES classInfo(classId) #关联另外一个表的哪个列
)

 在具有引用完整性的表之间，禁止用户进行下列操作：

①当主表中没有关联的记录时，将记录添加到从表中；

②更改主表表中的被关联值并导致从表中的记录孤立；

③从主表中删除记录，但仍存在与该记录匹配的相关记录；

④主表被从表关联，此时删除主表结构，会报错，主表无法删除。

5.5 域完整性

        用于限定数据范围

5.5.1 默认值约束：default

        当没有为列提供值得时候，用默认值填充列

5.5.2 非空约束：not null

        确保某一列的值不能为空

5.5.3 外键约束：foreign key

create table emp(
    empId int primary key auto_increment,
    empName varchar(20) not null,#非空约束
    sex char(1) not null, #非空约束
    hireDate date,
    phone varchar(20) unique, # 唯一约束
    province varchar(20) default '河南' #省份
)

5.6 用户自定义完整性

        用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数 据必须满足的语义要求，例如某个属性必须取唯一值，某个非主属性也不能取空值，某个属性的取值范围在0-100之间等。

        用户定义完整性可以涵盖实体完整性、域完整性、参照完整性等完整性类型。