数据库简单使用

操作数据库

DDL

操作数据库：CRUD
1.C（Create）:创建
* 创建数据库：create database 数据库名称;
* 创建数据库，判断不存在，再创建：create database if not exists 数据库名称；
* 创建数据库，并指定字符集：create database 数据库名称 chararter set 字符集名；
* 创建db4数据库，判断是否存在，并制定字符集为gbk: create database if not exists db4 character set gbk;
2.R（Retrieve）:查询
* 查询所有数据库的名称： show databases;
* 查询某个数据库的字符集：查询某个数据库的创建语句: show create database 数据库名称;
3.U(Updata):修改
* 修改数据库的字符集：alter database 数据库名称 character set 字符集名称;
4.D（Delete）:删除
* 删除数据库 ： drop database 数据库名称；
* 判断数据库存在，存在再删除：drop database if exists 数据库名称;
5.使用数据库
* 查询当前正在使用的数据库名称：select database();
* 使用数据库：use 数据库名称;
2.操作表
1.C（Create）:创建
- 语法:
-

 create table 表名(
			 			列名1	数据类型1，
			 			列名2	数据类型2，
			 			......
			 			列名n	数据类型n
			 		); 
			 		-- 注意：最后一列，不需要加逗号（,）

• 数据库类型：
  1. int： 整数类型 age int,
  2. double:小数类型 score double(5, 2)
  3. date:日期，只包含年月日，yyyy-MM-dd
  4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
  5. timestamp：时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
  如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
  6. varchar：字符串

   	* name varchar(20):姓名最大20个字符
      * zhangsan  8个字符    张三  2个字符
  

  • 创建表

create table student(
			id int,
			name varchar(20),
			age int,
			score double(4,1),
			birthday date,
			insert_time timestamp
		);	

2.R（Retrieve）:查询
* 查询某个数据库中所有的表名称：show tables;
* 查询表结构：desc 表名;
3.U(Update)：修改
（1）修改表名

alter table 表名 rename to 新的表名;

（2）修改表的字符集

alter table 表名 character set utf8;

（3）添加一列

alter  table 表名 add 列名 数据类型;

（4）修改列名称 类型

alter table 表名 change 列名 新列名 新数据类型;
alter table 表名 modify 列名 varchar 新数据类型;

（5）删除列

alter table 表名 drop 列名；

4.D（Delete)：删除

drop table 表名;
drop table if exists 表名；

DML:增删改表中的数据

1. 添加数据

语法：insert into 表名（列名1，列名2，..., 列名n） values(值1，值2，...，值n);
注意：
（1） 列名和值要一一对应。
（2）如果表名后，不定义列名，则默认给所有列添加值。
insert into 表名 values(值1，值2，…，值n);
（3）除了数字，其他类型用引号引起来

2. 删除数据：

语法: delete from 表名 [where 条件];
注意：
（1）如果不加条件，则删除表中所有记录。
（2）如果要删除所有记录
* delete from 表名; – 不推荐使用，有多少条记录就会执行多少次删除操作
* TRUNCATE table 表名; --推荐使用，效率更高。先删除表，然后再创建一张一样的表。

3. 修改数据：

语法：update 表名 set 列名1 = 值1，列名2 = 值2，...[where 条件]；
如果不加任何条件，则会将表中所有记录修改。

DQL：查询表中的记录

select * from 表名;

1. 语法：

2. 基础查询

1. 多个字段的查询

select 字段名1，字段名2... from 表名;

2. 去除重复

distinct

3. 计算列
   四则运算可以用。

if null(表达式1，表达式2);

4. 起别名

 as 或者空格

3. 条件查询

1. where 子句后跟条件

2. 运算符

   <、>、 <=、 >=、 =、 <>
   BETWEEN…AND
   IN(集合)
   LIKE
   IS NULL
   and 或 &&
   or 或 ||
   not 或 ！

3. 排序查询

   排序查询 order by 排序方式：ASC 升序, DESC 降序

4. 聚合函数：将一列数据作为一个整体，进行纵向的计算。
   1. count:计算个数
   2. MAX:计算最大值
   3. min:
   4. sum:
   5. avg:

5. 分组查询

   • group by 分组字段；
     注意：where 和 having 的区别：
     1. where 在分组之前进行限定，如果不满足条件，则不参与分组。having 在分组后进行限定，如果不满足结果，则不会被查询出来。
     2. where后不可以跟聚合函数，having可以进行聚合函数的判断。

6. 分页查询
   1. limit 开始的索引，每页查询的条数；
   2. 公式：开始的索引 = （当前的页码 - 1）* 每页显示的条数
   3. limit 是一个MySQL的“方言”

约束

• 概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。
• 分类：
  1. 主键约束：primary key
  2. 非空约束：not null
  3. 唯一约束：unique
  4. 外键约束：foreign key
     非空约束: not null

--创建表时添加约束   
 CREATE TABLE stu(
			id INT,
			NAME VARCHAR(20) NOT NULL   --name为非空		
			);
			
--创建表完后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT	NULL;

--删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20); 

唯一约束：unique，值不能重复

-- 1. 创建表时，添加唯一约束
CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE   -- 添加唯一约束
);
-- 注意mysql中，唯一约束限定的列的值可以有多个null

-- 2. 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;

-- 3.  在创建表后，添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

主键约束：primary key
注意：
1. 含义：非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识

-- 2. 在创建表时，添加主键约束
	create table stu(
		id int primary key,	-- 给id添加主键约束
		name varchar(20)
	);
-- 3. 删除主键
	错误ALTER TABLE stu modify id int;
	alter table stu drop primary key;
-- 4. 创建完表后，添加主键
	alter table stu modify id int primary key;

5. 自动增长：
   1. 概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长
   2. 在创建表时，添加主键约束，并且完成主键自增长

	create table stu(
		id int primary key auto_increment,
		name varchar(20)
	);
	-- 3. 删除自动增长
	ALTER TABLE stu MODIFY id INT;
	-- 4. 添加自动增长
		ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束:foreign key，让表与表产生关系，从而保证数据的正确性。
1. 在创建表时，可以添加外键
* 语法：

create table 表名(
		...
		外键列
		constraint 外键名称 foreign key （外键列名称） references 主表名称（主表列名称）
		);	

2. 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3. 在创建表之后添加外键

ALTER TABLE 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称);

级联操作

1. 添加级联操作
   • 语法:`

 ALTER TABLE 表名 add constraint 外键名称 
 	foreign key (外键列名称) references 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;

2.分类
1. 级联更新：ON UPDATE CASCADE
2. 级联删除：ON DELETE CASCADE

数据库的设计

1. 多表之间的关系

• 一对一

• 一对多（多对一）

   * 如：部门和员工
   * 分析：一个部门有多个员工，一个员工只能对应一个部门
  

• 多对多：

   * 如:学生与课程
   * 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
  

2. 实现关系：

• 一对多（多对一）：

   * 如：部门和员工
   * 实现方式：在多的一方建立外键，指向一的一方的主键。
  

• 多对多

   * 如：学生和课程
   * 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
  

• 一对一（了解）：

   * 如:人和身份证
   * 实现方式：一对一方式实现，可以在任意一方添加唯一外键指向另一方的主键。
  

3. 数据库设计的范式
概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。

设计关系数据库时，遵循不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库中有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF)、和第五范式（5NF,又称完美范式）。
分类：

• 第一范式（1NF）：
  每一列都是不可分割的原子数据项

• 第二范式（2NF）：

  在1NF的基础上，非码属性必须依赖于候选码（在1NF基础上x消除非主属性对主码的部分函数依赖）
  几个概念：

  1. 函数依赖：A – > B,如果通过A属性（属性组）的值，可以确定唯一B属性的值。则B依赖于A
     例如：学号–>姓名。（学号，课程名称）–>分数
  2. 完全函数依赖：A–>B,如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
  3. 部分函数依赖：A–>B，如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中m某一些值即可。
     例如：（学号，课程名称）–> 姓名
  4. 传递函数依赖：A–>B, B–>C。如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值，可以确定唯一C属性的值，则称C传递函数依赖于A
     例如：学号–>系名，系名–>系主任
  5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性值）为该表的码。

  例如：该表中码为：（学号，课程名称）

  • 主属性：码属性组中的所有属性
  • 非主属性：除过码属性组的属性
    3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖其他非主属性（在2NF基础上消除递归依赖）

数据库的备份和还原

1. 命令行：
   • 语法:
     • 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
     • 还原：
       1. 登陆数据库
       2. 创建数据库
       3. 使用数据库
       4. 执行文件。source 文件路径
2. 图形化工具：

多表查询

• 查询语法：

select
 				列名列表
 		from
 				表名列表
 		where.....

笛卡尔积：
* 有两个集合A,B，取这两个集合的所有组成情况。
* 要消除多表查询，需要消除无用的数据
多表查询的分类：
1. 内连接查询：

• 隐式内连接：使用where条件消除无用数据
• 显示内连接：

语法:

select 字段列表 from 表名1 [inner] join 表名2 on 条件;

 内连接查询：
		1. 从哪些表中查询数据
		2. 条件是什么
		3. 查询哪些字段

2. 外连接查询：

• 左外连接：

  • 语法 ： select 字段列表 from 表1 left [outer] join 表2 on 条件;
  • 查询的是左表所有数据以及交集部分。

• 右外连接：

  • 语法 ： select 字段列表 from 表1 right [outer] join 表2 on 条件;
  • 查询的是右表所有数据以及交集部分。

• 子查询：

  • 概念：查询中嵌套查询，称嵌套查询为子查询。
    • 子查询不同情况
      1. 子查询的结果是单行单列的：
      子查询可以作为条件，使用运算符去判断。运算符：> >= < <= =。
      2. 子查询的结果是多行单列的：
      子查询作为条件，使用运算符in来判断
      3. 子查询的结果是多行多列的：
      子查询可以作为一个虚拟表参与查询。

事务

• 事务的基本介绍

• 概念
  如果一个包含多个步骤的业务操作，被事务管理，那么这些操作严么同时成功，要么同时失败。

• 操作

• 开启事务：start transaction;

• 回滚: rollback;

• 提交：commit;

• MySQL数据库中事务默认自动提交
  事务提交的两种方式:
  自动提交：

  • mysql就是自动提交的
  • 一条DML（增删改）语句会自动提交一次事务。

  手动提交：

  • Oracle 数据库默认是手动提交事务
  • 需要先开启事务，再提交
  • 修改事务的默认提交方式：select @@autocommit; – 1代表自动提交，0代表手动提交
  • 修改默认提交方式：set @@autocommit = 0;

• 事务的四大特征：

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败

• 持久性：当事务提交或回滚后，数据库就会持久化的保存数据。

• 隔离性：多个事务之间。相互独立。

• 一致性：事务操作前后，数据总量不变。
  事务的隔离级别（了解）

  • 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

  • 存在问题:
    1. 脏读：一个事务，读取到另一个事务中没有提交的数据
    2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
    3. 幻读：一个事务操作（DML）数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

  • 隔离级别
    1. read uncommitted：读未提交
    * 产生的问题：脏读、不可重复读、幻读
    2. read committed：读已提交 (Oracle)
    * 产生的问题：不可重复读、幻读
    3. repeatable read：可重复读 （MySQL）
    * 产生的问题：幻读
    4. serializable：串行化
    * 可以解决所有的问题

  • 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
    数据库查询隔离级别：

select  @@transaction_isolation;

数据库设置隔离级别：

set global transaction isolation level  级别字符串;

• 演示：

 set global transaction isolation level read uncommitted; 
    start transaction;

-- 转账操作

 update account set balance = balance -  500 where id = 1;
    update account set balance = balance + 500 where id = 2;

DCL:

• SQL分类：

  1. DDL：操作数据库和表
  2. DML：增删改表中数据
  3. DQL：查询表中数据
  4. DCL：管理用户，授权

• DBA：数据库管理员

• DCL：管理用户，授权

• 管理用户

• 添加用户
  语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

• 删除用户
  语法： DROP USER '用户名'@'主机名';

• 修改用户密码：

UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('abc');

• 查询用户：

1. 切换到mysql数据库

USE mysql;

2.查询user表

SELECT * FROM USER;

通配符：% 表示可以在任意主机使用用户登录数据库
授权