一.MYSQL数据库
1.配置
- cmd -->services.msc 打开服务窗口
- 使用管理员打开cmd
- net start mysql
- net stop mysql
- MYSQL登录
- mysql -uroot(用户名) -proot(密码)
- mysql -h(ip) -uroot(用户名) -proot(密码)
- mysql --host=(ip) --user=root(用户名) --password=root(密码)
- MYSQL退出
- exit
- quit
2.MYSQL目录结构
1.配置文件
- mysql.ini
2.MySQL数据目录
- 数据库 -->文件夹
- 表 -->frm文件
- 数据
3.SQL
1.什么是SQL
- Structured Query Language :结构化查询语言
- 定义了所有操作关系型数据库的规则
- 每一种数据库都有一些不一样的独立语言
2.SQL通用语法
- 可以单行,多行书写,以分号结尾;
- 不区分大小写,关键字建议大写
- 3种注释
- 单行注释: -- 注释
- 单行注释: #注释
- 多行注释:/* 注释 */
3.SQL的分类
- DDL(Data Definition Language)操作数据库,表,列等
- create 创建
- drop
- alter
- DML(Data Manipulation Language)操作数据
- insert
- delete
- update
- DQL(Data Query Language)查询数据
- select
- where
- 等
- DCL(Data Control Language)控制权限和安全级别
- GRANT
- REVOKE
4.DDL语句,操作数据库表
1.操作数据库
- create :创建
- careat database 数据库名 character set 字符集 ; 指定字符集创建数据库 ;
- retrieve 查询
- show database ; 展示所有数据库
- show careat database 数据库名 ; 查看数据库字符集
- update; 更改
- alter database 数据库名 character set 字符集 ; 更改数据库字符集
- delete 删除
- drop database 数据库名; 删除数据库
- 使用数据库
- select database(); 查询当前使用的数据库;
- use 数据库名 ; 使用指定数据库.
2.操作数据表
- create :创建
- create table 表名 (字段1 , 数据类型1(长度) , 字段2 , 数据类型2(长度));
- create table 表名 like 要复制的表名;复制表.
- retrieve 查询
- show tables ;
- decs 表名 ; 查询表结构.
- 数据库数据类型类型
- int ;
- double(5 , 2) ; 表示最多5位,小数点后保留两位.
- date 日期,只包含年月日
- datetime 包含年月日时分秒
- timestamp,时间戳类型,会自动赋值
- varchar;字符串.
- update; 更改
- alter table 表名 add 列名 数据类型 ; 增加一列.
- alter table 表名 change 列名 新列名 数据类型 ; .修改一列 .
- alter table 表名 modify 列名 新数据类型; 修改一列.
- alter table 表名 drop 列名;删除一列.
- delete 删除
- drop table 表名 ;
- drop table if exists 表名 ;
5.DML语句,增删改表中的数据
1.添加数据
insert into 表名 (列名1 , 列名2 , ...) values (值1 , 值2...);
2.删除数据
delete from 表名 [where 条件] ;
delete from 表名 ; -- 删除表中所有数据
truncate table 表名 ; -- 删除表,在创建一个一模一样的表,效率更高.
3.修改数据
update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
-- 不加条件所有该字段下的数据都改.
6.DQL语句,查询表中数据
1.语法
select * from 表名 ;
-- ************************************************
select
字段列表
from
表名
where
查询条件
group by
分组字段
having
分组后条件
order by
排序
limit
分页限定
2.基础查询
- 多字段查询
select 字段名... from 表名 ;
select
-- 字段名
-- 字段名
from
;-- 表名
- 去重
distinct -- 去重
select distinct 字段名 from 表名 ;
- 计算列
ifnull(字段 , 替换值);-- null参与的计算结果都为null
- 起别名
as -- 可以省略 用空格代替
3.条件查询
1.where 后跟条件
2.运算符
between ... and...
in (集合);
like -- 模糊查询
占位符
1.% 代表多个任意字符
2._ 代表一个任意字符
is null ;
and
or
not
4.排序查询
1.语法
order by 字段1 ;
order by 字段1 , 字段2 ;
2.排序方式
ASC:-- 升序的默认
DESC:-- 降序
5.聚合函数
1.概念
- 建一列数据作为一个整体,进行纵向计算
count():-- 计算个数
sum();
max()
min()
avg()
2.注意
- 聚合函数的计算排除null值
- ifnull()排除null值
6.分组查询
1.语法
group by -- 分组字段 ;
2.注意
- 分组后查询的字段:分组字段,聚合函数
- 分组把一个组看成一个整体
select age , count(age) from stu group by age ;-- 展示分组字段或者聚合函数 ;
3.where , having 区别
- where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
- where 后不可以跟聚合函数,having可以进行聚合函数的判断。
select age from stu where English > 70 group by ID having count(ID) > 2 ;-- ID个数小于2不展示
7.分页查询
1.语法
limit 开始的索引 , 每页展示的个数 ;-- limit是MySQL方言
7.约束
1.概念
- 对表中数据的数据进行限定,保护数据的完整性,正确性,有效性.
2.分类
-
主键约束:
primary key
- 注意一个表只能有一个主键.
- 主键非空,唯一.
- 主键就是表中记录的唯一标识.
-- 在创建表时,添加主键约束
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);
-- 删除主键
-- 错误 alter table stu modify id int ;
ALTER TABLE stu DROP PRIMARY KEY;
-- 创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-- 自动增长:
1. 概念:如果某一列是数值类型的,使用 auto_increment 可以来完成值得自动增长
2. 在创建表时,添加主键约束,并且完成主键自增长
create table stu(
id int primary key auto_increment,-- 给id添加主键约束
name varchar(20)
);
-- 删除自动增长
ALTER TABLE stu MODIFY id INT;
-- 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
- 非空约束: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,值不能重复
-- 创建表时,添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);
-- 注意mysql中,唯一约束限定的列的值可以有多个null
-- 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number;
-- 在创建表后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
- 外键约束: foreign key,让表于表产生关系,从而保证数据的正确性。
-- 在创建表时,可以添加外键
* 语法:
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-- 创建表之后,添加外键
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. 添加级联操作
语法:ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
-- 2. 分类:
1. 级联更新:ON UPDATE CASCADE
2. 级联删除:ON DELETE CASCADE
8.数据库的设计
1.多表关系
- 一对一
- 一对多
- 多对多
2.实现方式
- 一对一 :一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
- 一对多: 在多的一方建立外键,指向一的一方的主键。
- 多对多 : 多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键
3.范式
1.概念
- 设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须先遵循前边的所有范式要求
- 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。
- 目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。
2.分类
- 第一范式(1NF):每一列都是不可分割的原子数据项
- 第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)
- 函数依赖:A-->B,如果通过A属性(属性组)的值,可以确定唯一B属性的值。则称B依赖于A 例如:学号-->姓名。 (学号,课程名称) --> 分数
- 完全函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定需要依赖于A属性组中所有的属性值。 例如:(学号,课程名称) --> 分数
- 部分函数依赖:A-->B, 如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。 例如:(学号,课程名称) -- > 姓名
- 传递函数依赖:A-->B, B -- >C . 如果通过A属性(属性组)的值,可以确定唯一B属性的值,在通过B属性(属性组)的值可以确定唯一C属性的值,则称 C 传递函数依赖于A 例如:学号-->系名,系名-->系主任
- 码:如果在一张表中,一个属性或属性组,被其他所有属性所完全依赖,则称这个属性(属性组)为该表的码 例如:该表中码为:(学号,课程名称) * 主属性:码属性组中的所有属性 * 非主属性:除过码属性组的属性
- 第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
9.数据库备份
-
命令行:
- 语法:
- 备份: mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
- 还原:
- 登录数据库
- 创建数据库
- 使用数据库
- 执行文件。source 文件路径
- 语法:
10.多表查询
1.笛卡尔积
- 有两个集合A,B .取这两个集合的所有组成情况。
- 要完成多表查询,需要消除无用的数据
2.多表查询的分类
1.内连接查询
1.隐式内连接,使用where条件消除无用数据
-- 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
2.显示内连接
select 表字段 from 表名1 [inner] join 表名2 on 条件 ;
-- 例如
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
3.内连接查询
1. 从哪些表中查询数据
2. 条件是什么
3. 查询哪些字段
2.外连接查询
1.左外连接 : 以左表为主表,右表为从表
-- 语法
select 字段 from 表名1 left join 表名2 on 条件 ;
-- 会展示主表的所有信息和从表匹配的信息
2.右外连接 : 以右表为主表,左表为从表
-- 语法
select 字段 from 表名1 right join 表名2 on 条件 ;
-- 会展示主表的所有信息和从表匹配的信息
3.子查询
1.概念
*查询中嵌套查询,称嵌套查询为子查询。
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp );
4.补充函数
-- 1
if(ex1 , ex2 , ex3);
select if(条件 , 真返回 , 假返回) from 表;
select name , age , if(sex = 0 , 女 , 男) from stu;
-- ----------------------------------------------------
-- 2
case 字段
when 值 then 结果
when 值 then 结果
else
end 别名
select
name '姓名' ,
case sex
when 0 then '男' -- 如果是 0 返回 男
when 1 then '女' -- 如果是 1 返回 女
else '未知'
end '性别' -- 别名
from
stu ;
-- ---------------------------------------------------------
nullif() ;
-- ---------------------------------------------------------
ifnull(字段 , 默认值);
select if(age , 18) from stu;
-- ----------------------------------------------------------
isnull(字段) -- 如果字段为null返回1,否则返回null