mysql基础知识点总结

目录

Ⅰ .Data Definition Language
一.数据库的管理
二.表的管理
三.约束
四.标识列
五.常见数据类型
Ⅱ. Data Query Language
六.查询关键字
七.函数
八.sql其他语句
Ⅲ Data Management Language
九.插入操作
十.删除操作
十一.修改操作
Ⅳ Transaction Control Language
十二.事务
十三.视图
十四.存储过程
十五.变量
十六.函数
十七.流程控制结构

Ⅰ. Data Definition Language

一. 数据库的管理

1. 库的创建

create database if not exists 库名; : 如果不存在数据库,就创建

2. 库的修改

①数据库基本不做修改,否则很容易导致数据丢失
②alter database 库名 character set gbk; : 修改数据库所用数据集

3. 库的删除

drop database if exists 库名; : 如果存在数据库,就删除

4. 使用数据库

use 库名; : 选中books数据库进行表管理

5. 库中表的查看

show tables; : 显示库中存在的所有表

—返回目录

二. 表的管理

点击这里先学习’约束’章节
点击这里学习’类型’章节

1. 表的创建

create table 表名(
     列名 列的类型(长度) 约束…
     …
);

2. 表的修改

①alter table 表名 add column 列名 列类型(长度) 约束…; : 添加列
②alter table 表名 drop column 列名; : 删除列
③alter table 表名 change column 旧列名 新列名; : 修改列名
④alter table 表名 modify column 列名 新类型(长度) 新约束; : 修改列类型和约束
⑤alter table 旧表名 rename to 新表名; : 修改表名

3. 表的删除

①drop table if exists 表名; : 删除表

4. 表的复制

①create table 表名2 like 表名1; : 仅仅复制表结构
②create table 表名2 select * from 表名1; : 复制表结构和其中所有数据
③create table 表名2 select 列名1,列名2,… from 表名1 where 条件1 and 条件2 and …; : 复制表中的部分数据;

5. 查看表结构

desc 表名; : 显示表结构

—返回目录

三.约束

1. 常用约束

①not null : 非空
②defalut 默认值 : 保证字段插入时有默认值
③primary key : 主键, 默认唯一,非空,自+1
④unique : 保证字段唯一,可以为空,但空字段也只能有一个
⑤foreign key : 外键,用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值。

2. 约束的分类

①列级约束 : 外键约束没有效果,其他约束都可用
②表级约束 : 除了非空,默认,其他都支持

3. 外键约束的规则

①主表是指那些’部门’等需要被引用的表,从表类似"员工表",即’员工表’中需要有’部门’信息
②要求在从表设置外键关系
③从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
④主表的关联列必须是一个key（一般是主键或唯一）
⑤插入数据时，先主表，后从表。 — 删除数据时，先从表，后主表。

4. 表级约束的操作

①constraint 约束名 约束类型(字段名) : 创建表时添加表级约束
②constraint 外键约束名 foreign key(从表字段名) references 主表名(字段名); : 创建表添加表级外键约束(通用写法:将外键定义为表级约束)

③show index from 表名; : 查看表中的已有约束

④alter table 表名 add 新约束(字段名) : 修改表时添加表级约束
⑤alter table 表名 add constraint 约束名 foreign key(从表字段名) references 主表名(字段名); : 修改表时添加表级外键约束

⑥alter table 表名 drop primary key; : 修改表时删除表级约束
⑦alter table 表名 drop index seat; : 修改表时删除唯一约束
⑧alter table 表名 drop foreign key 外键约束名; : 修改表时删除外键约束

—返回目录

四.标识列

1. auto_increment : 自增 ,标识列，自增长列，写在约束后边
2. show variable like %auto_increment% : 查看自增长间隔值
3. set auto_increment=3 :设置自增长间隔值为３
4. 特点

①标识列不一定非要和主键搭配，也可以和unique搭配，即要求是一个key
②一个表至多有一个标识列
③标识列必须是数值型

5. 对标识列的修改和删除 和 表的列约束修改删除 一样,因为标识列可以主观上当作是自增约束

—返回目录

五.常见数据类型

1. 数值型

1.1.1 整型:

tinyint	smallint	mediumint	int/integer	bigint
1字节	2字节	3字节	4字节	8字节

1.1.2 特点:

①如果不设置有无符号，默认是有符号。如果需要设置无符号，需要添加unsigned关键字
②如果插入的数值超出整型的范围，会报out of range异常，并且插入临界值。
③如果不设置长度，会有默认的长度
④长度代表显示的最大宽度，不影响存入，搭配zerofill会在左边填充不够宽度的个数的0; zerofill默认是无符号。

1.1.3 如何设置无符号和有符号:

create table 表名(
     t1 int ,#有符号
     i2 int ①unsigned #无符号 #②zerofill
);

①unsigned无符号: 字段添加此项可以令字段只能保存正数，并且可以增大数据类型的可用范围。
②zerofill的作用是填充0，在字段中数据类型规定的范围中，若是插入的数据不满足范围，则会使用空格作为填充，使其符合要求，而zerofill则会将空格改为0。

2. 小数

2.1.1 浮点型:

float(M,D)
double(M,D)

2.2.1 定点型

dec(M,D)
decimal(M,D)

2.3.1 特点

①M和D的含义:
M:整数长度+小数长度
D:小数长度
如果超出范围,默认插入临界值

②M和D都省略:
如果是decimal,默认是(10,0)
如果是float,double会根据插入精度决定精度

③定点型的精确度较高,如果要求插入数值的精度较高,如货币运算等则考虑使用

2.4.1 原则 : 所选择的类型越简单越好,能保存数值的类型越小越好…

3. 字符型

3.1.1 较短的文本：char，varchar
3.2.1 较长的文本：text，blob(较大的二进制)
3.3.1 其他的：binary和varbinary，enum，set
3.4.1 char和varchar比较

	char	varchar
写法:	char(M)	varchar(M)
M的意思：	最大的字符数,可以省略,默认为1	最大的字符数,不可以省略
特点：	固定长度的字符	可变长度的字符
空间耗费：	比较耗费	比较节省
效率：	高	低

3.5.1 enum: 表明此字段只能插入abc三种字符的一个。

create table 表名(
     id enum(‘a’,‘b’,‘c’)

);

insert into 表名 value(‘a’) : 插入一个
insert into 表名 values(‘a,b,c’) : 插入多个

4. 日期型

①date只保存日期
②time只保存时间
③year只保存年份
④datetime和timestamp都可以保存日期+时间

二者比较

	字节	范围	时区等影响
datetime	8	1000-9999	不受
timestamp(用的多)	4	1970-2038	受

—返回目录

Ⅱ. Data Query Language

六.查询关键字

1. select查询

①效果如同:system.out.print();
②select *(字段名) from 表名: 查询表中(部分)全部数据
③select 100; 查询常量值
④select 100%98 : 查询表达式
⑤select version() : 查询函数

2. as起别名

select 字段名 as 别名 from 表名
注意 : as可以省略

3. distinct去重

select distinct 字段名 from 表名 : 去除字段名相同的数据

4. where条件查询

语法：

select 查询列表
from 表名
where 筛选条件;

分类：

①按条件表达式筛选
简单条件运算符：> < = != <> >= <=

②按逻辑表达式筛选
逻辑运算符：&& || ! --> and or not

③模糊查询

• like: 包含某个字符,通常和通配符一起使用

• 案例:

• select * from 表名 where name like “_ b\ _ e%% escape ‘e’”;
• 解释: 查询为"(一个字符) + b_% + (多个字符)“类型的名字, 比如"ab_%cdefg”

• 通配符: “%” 表示0到多个字符…"_" 表示一个字符
• 转义字符:

• "\ " 表示转义字符,比如当你要查询字段数据中是否有"_"符号时, 用 " \ _ " 就能实现搜索, 也可以自定义,用 “escape 自定义的转义字符”

• between 小的数 and 大的数: 在小的数和大的数之间

• 注意 ① 包含临界值 ② 两个临界值不可改变顺序

• in (一堆数): 表示是一堆数之中的一个

• 注意 ① in列表值得类型必须统一或兼容② 不支持通配符

• 某个东西 is null:判断这个东西是不是null

• 注意 =和<>不能判断null值,如果想用,可以用<=>安全等于

5. order by 排序

语法：

• select 查询列表 from 表 【where 筛选条件】
  order by 排序列表 【asc|desc】
  

特点：

• asc是升序 desc是降序
• 不写默认为升序
  

总结：

• order by 字句可以是单个字段，多个字段，表达式，函数，别名
• order by一般放在查询语句最后边，limit字句除外

6. group by分组 和 having分组后的条件查询

语法

select 分组函数,列（要求出现在group by的后面）
from 表
[where 筛选条件]
group by 分组的列表
[order by 子句]

注意：查询列表必须特殊，要求是分组函数和group by后出现的字段。

特点：

• 分组查询中的筛选条件分为两类

	数据源	位置	关键字
分组前筛选	原始表	group by子句的前边	where
分组后筛选	分组后的结果集	group by子句的后边	having

①分组函数做条件肯定是放在having中
②能在分组前筛选的，就优先考虑使用分组前筛选。

• group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数分组也支持（用的少）

• 也可以添加排序（排序放在整个分组查询最后）