MySQL学习笔记

MySQL

一、通用语法

1. SQL语句可以单行或者多行书写，以分号结尾
2. MySQL数据库的SQL语句不区分大小写，关键字建议使用大小写
3. 注释

1、单行注释：--注释内容 或 #注释内容（MySQL特有）
2、多行注释：/*注释*/

二、SQL分类

1. DDL（）数据定义语言，用来定义数据库对象：数据库，库，列等
   *创建（Create）
   *查询（Retrieve）
   *修改（Upadate）
   *删除（Delete）
2. DML（）数据操作语言，用来对数据库中表的数据进行增删改
   *添加（insert）
   *修改（update）
   *删除（delete）
3. DQL（）数据查询语言，用来查询数据库中表的记录（数据）
   *基础查询
   *条件查询（where）
   *分组查询（group by）
   *排序查询（order by）
   *分页查询（limit）
4. DCL（）数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户

三、各种语言的具体操作

1.1 DDL----操作数据库

1.查询

show databass;

2.创建
创建数据库

create databass 数据库名称;

创建数据库（判断，如果不存在则创建）

create databass if not exists 数据库名称;

3.删除
删除数据库

drop databass 数据库名称;

删除数据库（判断，如果存在则删除）

drop databass if exists 数据库名称;

4.使用数据库
查看当前使用的数据库

select database();

使用数据库

use 数据库名称;

1.2 DDL----操作表

–查询表
1.查询当前数据库（先使用数据库）下所有表的名称

show tables;

2.查询表结构

desc 表名称;

–创建表
1.语法

create tables 表名(
		字段名1 数据类型1,
		字段名2 数据类型2,
		...
		字段名n 数据类型n
);

注意：最后一行末尾不能加逗号

2.数据库中的数据类型（不背，了解常用）
数值类型（int、float、double）
日期和时间类型（date、time、year）
字符串类型（char、varchar）

–删除表
1.删除表

drop table 表名;

2.删除表时判断表是否存在

drop table if exists 表名;

–修改表
1.修改表名

alter table 表名 rename to 新表名;

2.添加一列

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

3.修改数据类型

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

4.修改列名和数据类型

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

5.删除列

alter table 表名 drop 列名;

2 DML

–添加数据
1.给指定列添加数据

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

2.给全部列添加数据

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

3.批量添加数据

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

–修改数据
1.修改表数据

update 表名 set 列名1=值1,列名2=值2,...[where 条件];

注意：如果不添加条件，则将所有数据都修改！

–删除数据
1.删除数据

delete from 表名 [where 条件];

注意：如果不添加条件，则将所有数据都删除！

3 DQL

**查询语法

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组后条件
order by
	排序字段
limit
	分页限定

–基础查询
1.查询多个字段

select 字段列表 from 表名;
select *from 表名;--查询所有数据

2.去除重复记录

select distinct 字段列表 from 表名;

3.起别名

as--as也可以省略

–条件查询
1.条件查询语法

select 字段列表 from 表名 where 条件列表;

2.条件（引入图片）

–排序查询
1.排序查询语法

select 字段列表 from 表名 order by 排序字段名1[排序方式],排序字段名1[排序方式]...;

排序方式：
*ASC：升序排列（默认）
*DESC：降序排列
注意：当有多个排列条件，当前面的条件值一样时，才会根据第二条件进行排序

–分组查询
1.聚合函数
-定义：将一列数据做为一个整体，进行纵向计算
-分类

count(列名)--统计数量（一般不选择Nell的列）
max(列名)--最大值
min(列名)--最小值
sum(列名)--求和
avg(列名)--求平均

-语法

select 聚合函数名(列名) from 表;

注意：nell值不参与所有聚合函数运算
2.分组查询

select 字段列表 from 表名 [where 分组前条件限定] group by 分组字段名 [having 分组后条件过滤]

注意：分组后查询的字段为聚合函数和分组字段，查询其他字段无任何意义

**where和having的区别：
1.执行时机不一样：where是分组前进行限定，不满住where条件，则不参与分组，而having是分组之后对结果进行过滤
2.可判断的条件不一样：where不能对聚合函数进行判断，having可以
3.执行顺序：where>聚合函数>having

–分页查询
1语法

select 字段列表 from 表名 limit 起始索引,查询条目数;

*起始索引：从零开始
计算公式：起始索引=（当前页码-1）*每页显示的条目数
tips：
分页查询limit是MySQL数据库的方言
Oracle分页查询用rownumber
SQL Server分页查询用top

四、约束

1约束概念和分类

1.1概念：
*约束是作用于表中列上的规则，是用来限制加入表的数据。
*约束的存在保证了数据库中数据的正确性、有效性和完整性
1.2约束的分类

约束名称	描述	关键字
非空约束	保证列中数据不能有Nell	NOT NULL
唯一约束	保证列中数据各不相同	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时，为指定的值采用默认值	DEFAULT
外键约束	外键用来让两个表中的数据之间建立联系，保证数据的一致性和完整性

tips：MySQL中不支持检查约束
1.3约束的语法
（1）添加约束

--创建表时添加约束
create table 表名(
		列名 数据类型 关键字，
		列名 数据类型 关键字，
		...
);
--建完表后添加约束
alter table 表名 modify 字段名 数据类型 关键字;

（2）删除约束

alter table 表名 modify 字段名 数据类型;

tips：同一列可以添加多种约束
1.4外键约束
（1）添加约束

--创建表的时候添加外键
create table 表名(
		列名 数据类型,
		...
		[constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
);

--建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主表名称(主表列名称)

（2）删除约束

alter table 表名 drop foreign key 外键名称;

2数据库设计

2.1概念
*根据业务系统需求，结合选用的DBMS，为这个业务系统结构出最优的数据库储存模型。
*建立数据库中表结构和表及表之间关系的过程。
2.2表之间的关系
（1）一对多（多对一）
*例如部门对应多个员工，一个员工对应一个部门
*实现方式：在多的一方建立外键，主的一方建立主键
（2）多对多
*例如一个商品对应多个订单，一个订单包含多个商品
*实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联双方主键
（3）一对一
*例如用户和用户详情
*一对一关系多用于表拆分，将一个实体中经常使用的字段放入一张表，不经常使用的字段放入另一张表，用于提升查询性能
*实现方式：在任意一方加入外键，关联另一方的主键，并且外键设置为唯一（uniqu）
2.3多表查询
（1）概念：从多张表中查询数据
（2）多表查询的分类
——连接查询
*内连接：先当与查询A B交集数据
*外连接:
**左外链接：相当于查询A表所有数据和交集部分数据
**右外链接：相当于查询B表所有数据和交集部分数据
——子查询
（3）查询语法
—链接查询
*内连接

--隐式内连接
select 字段列表 from 表1,表2,... where 条件;
--显示内连接
select 字段列表 from 表1 [inner] join 表2 on 条件;

*外连接
表1=A 表2=B

--左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
--右外链接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

—子查询
*概念：查询中嵌套查询，称嵌套查询为子查询
*分类及其语法
单行单列：作为条件值，使用= != > < 等进行判断

select 字段列表 from 表 where 字段名 = (子查询);

多行单列：作为条件值，使用 in等关键字进行判断

select 字段列表 from 表 where 字段名 in (子查询);

多行多列：作为序列表

select 字段列表 from (子查询) where 条件;

2.4事务
（1）简介
*数据库的事务是一种机制，一个操作序列，包含了一组数据库操作命令
*事务把命令当作一个整体，这些数据要么同时成功，要么同时失败
*事务是一个不可分割的工作逻辑单元
（2）语法

--开启事物
start transaction;
or begin;
--提交事务
commit
--回滚事务
rollback

（3）事务的四大特征
*原子性（Atomicity）事务是不可分割的最小操作单元，要么同时成功，要么同时失败
*一致性（Consistency）事务完成时，必须所有数据都保持一致状态
*隔离性（Isolation）多个事务之间，操作可见性
*持久性（Durability）事务一旦提交或回滚，它对数据库中的数据改变就是永久的