有关MySQL的知识点总结
1.相关概念
DB
数据库(database):存储数据的"仓库",它保存了一系列有组织的数据。
DBMS
数据库管理系统(Database Management System).数据库是通过DBMS创建和操作的容器。
SQL
结构化查询语言(Structure Query Language):专门用来与数据库通信的语言。
常见的数据库管理系统:Mysql Oracle DB2 sqlServer等。
Mysql是关系型数据库(c/s),是一种开放资源代码的关系型数据管理系统,适用于多种平台。
关系型数据库的特点:
1.实现数据共享,减少数据冗余
2.采用特定的数据类型
3.具有较高的数据独立性
4.具有统一的数据控制功能
sql语句:
DDL:数据库定义语言,用于库和表的创建(create)、修改、删除 。
DQL:数据库查询语句 select(查询)
DML:数据操纵语句,用于添 加、删除、修改、查询数据库记录,并检查数据完整性 ,insert(插入/添加) update(修改),delete(删除)
DCL:数据控制语句,用于定义用 户的访问权限和安全级别。用于对数据库对象操作的权限。如grant revoke commit rollback等语句
相关知识、操作介绍:
-- 连接mysql
mysql -uroot -p密码;
-- 退出
exit; 或 /q(Queit)
/**数据库操作 **/
-- 查看当前数据库
select database();
-- 查看已有的库
show databases;
show databases like '匹配模式';(模糊查询)
-- 创建库
create database [ if not exists] 数据库名 数据库选项
-- 删除库
drop database [ if exists ] 数据库名
同时删除该数据库箱管的目录及目录的内容
//表的操作
-- 创建表
create table [ if not exists] [库名.]表名 (表的结构定义)[表选项]
每个字段必须有数据类型,最后一个字段后不能有逗号
对于字段的定义:
字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
如:
create table people(
id int not null primary key auto_increment,指定为主键且自增
name varchar(10),
sex char(1),
age smallint,
tid int(10),
foreign key (tid) reference 父表(主键名tid) //tid的类型,显示宽度要一致
);
//表选项
-- 字符集
CHARSET = charset_name 如果表没有设定,则使用数据库字符集
-- 存储引擎
ENGINE = engine_name
表在管理数据的时候采用不同的数据结构,结构不同会导致处理方式,提供的特性操作等不同,
常见的引擎:InnoDB MyISAM Memory/Heap CSV Mrg_Myisam Blackhole(黑洞引擎)
-- 显示存储引擎的状态信息
show engines
-- 显示存储引擎的日志或状态信息
show engines 引擎名 {logs | status}
-- 表注释
comment = 'string'
-- 查看所有表
show tables [ like 'pattern' ]
show tables from 表名
-- 查看表结构
show create table 表名
desc 表名 / show columns from 表名; / describe 表名
-- 修改表
-- 修改表本身的选项
alter table 表名 表的选项
alter table 表名 engine=MyISAM;
-- 修改表名:rename
alter table 旧表名 rename 新表名;
-- 修改字段数据类型:modify
alter table 表名 modify 字段名 新数据类型 [约束];
-- 设置非空约束:
alter table 表名 modify 字段名 数据类型 not null;
alter table 表名 modify age int(10) not null;
注意:设置非空约束时,已经存在数据的字段是null值的情况下是无法设置成功的。
-- 新增外键约束:
alter table 表名 add foreign key (外键列) reference 父表(父表主键列);
-- 新增唯一约束:
alter table 表名 add unique(字段名);
-- 新增主键:
alter table 表名 add primary key(字段名);
主键设有自增无法删除,先把自增设为0,再删。
alter table 表名 auto_increment=0;在进行删除
-- 删除主键约束:
alter table 表名 drop primary key;
-- 删除外键约束:
alter table 表名 drop foreign key(约束名);
外键名在外键设置时constraint关键字之后的参数,如果没有设置外键名,系统会有默认的外键名,
通过建表语句(show create table 表名;) 查询。
-- 删除唯一约束:
alter table 表名 drop index 字段名;
-- 设置默认约束:
alter table 表名 alter 字段 set default 默认值;
-- 删除默认值:
alter table 表名 alter 字段 drop default;
-- 修改字段排序位置:
alter table 表名 modify 字段一 数据类型 first;
alter table 表名 modify 字段一 数据类型 after 字段二;
-- 修改字段名和数据类型:change
alter table 表名 change 旧字段名 新字段名 数据类型;
alter table 表名 change phone newPhone int(11);
-- 添加字段:add
alter table 表名 add 新字段名 数据类型[first/after];
alter table 表名 add address varchar(50) first;可以指定添加的位置。
first:将新的字段设置顺序为第一个
after:将新的字段添加到指定字段之后
alter table 表名 add weight float(5,2) after sex;
-- 删除字段:drop
alter table 表名 drop 字段名;
alter table 表名 drop weight;
-- 更改表的储存引擎:
使用show engines;查看系统支持的存储引擎
alter table 表名 engine=更改后的存储引擎名;
-- 清空表数据
truncate [table] 表名;
-- 复制表结构
create table 表名 like 要复制的表名
-- 复制表结构和数据
create table 表名 [as] select * from 要复制的表名
//数据的操作
-- 增
insert into 表名 [(字段列表)] values(值列表)
-- 当插入的值列表包含所有字段并顺序一致,则可以省略字段列表,可以插入多条数据记录。
-- 删
delete from 表名 [删除条件子句]
truncate table 表名 //清空表数据
****** truncate delete 的区别 *******
#1.truncate不能加where条件,而delete可以加where条件
#2.truncate 是删除表再创建,delete 是逐条删除(truncate的效率高一丢丢 )
#3.truncate 删除带自增长的列的表后,如果再插入数据,数据从1开始
#delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始(truncate 重置auto_increment的值。而delete不会)
#4.truncate删除不能回滚,delete删除可以回滚
#5.truncate 不知道删除了几条,而delete知道
#6.当被用于带分区的表时,truncate 会保留分区
-- 改
update 表名 set 字段 = 值 [, 字段名 = 值] [更新条件]
--查
select 字段列表 from 表名 [其他子句]
// 字符集编码
-- MySQL、数据库、表、字段均可设置编码
-- 数据编码与客户端编码不需一致,在mysql中,是utf8
show variables like 'character_set_%' -- 查看所有字符集编码项
character_set_client 客户端向服务器发送数据时使用的编码
character_set_results 服务器端将结果返回给客户端所使用的编码
character_set_connection 连接层编码
set 变量名 = 变量值
set character_set_client = gbk;
set character_set_results = gbk;
set character_set_connection = gbk;
set names gbk; -- 相当于完成以上三个设置
-- 校对集
校对集用以排序
show character set [like 'pattern']/show charset [like 'pattern'] 查看所有字符集
show collation [like 'pattern'] 查看所有校对集
charset 字符集编码 设置字符集编码
collate 校对集编码 设置校对集编码
//数据类型
1.数值类型
--整型
类型 字节 范围(有符号位)
tinyint 1字节 -128 ~ 127 无符号位:0 ~ 255
smallint 2字节 -32768 ~ 32767
mediumint 3字节 -8388608 ~ 8388607
int 4字节
bigint 8字节
int(M) M表示总位数
- 默认存在符号位,unsigned 属性修改
- 显示宽度,如果某个数不够定义字段时设置的位数,则前面以0补填,zerofill 属性修改
例:int(6) 插入一个数'123',补填后为'000123'
- 在满足要求的情况下,越小越好。
- 1表示bool值真,0表示bool值假。MySQL没有布尔类型,通过整型0和1表示。常用tinyint(1)表示布尔型。
-- 浮点型
类型 字节
float(单精度) 4字节
double(双精度) 8字节
浮点型既支持符号位 unsigned 属性,也支持显示宽度 zerofill 属性。
不同于整型,前后均会补填0.
定义浮点型时,需指定总位数和小数位数。
float(M, N) double(M, N)
M表示总位数,N表示小数位数。
M和N的大小会决定浮点数的范围。不同于整型的固定范围。
M既表示总位数(不包括小数点和正负号),也表示显示宽度(所有显示符号均包括)。
支持科学计数法表示。
浮点数表示近似值。
-- 定点数
decimal -- 可变长度
decimal(M, N) M也表示总位数,N表示小数位数。
保存一个精确的数值,不会发生数据的改变,不同于浮点数的四舍五入。
2. 字符串类型
-- char, varchar
char 定长字符串,速度快,但浪费空间
varchar 变长字符串,速度慢,但节省空间
M表示能存储的最大长度,此长度是字符数,非字节数。
不同的编码,所占用的空间不同。
char,最多255个字符,与编码无关。
varchar,最多65535字符,与编码有关。
一条有效记录最大不能超过65535个字节。
utf8 最大为21844个字符,gbk 最大为32766个字符,latin1 最大为65532个字符
varchar 是变长的,需要利用存储空间保存 varchar 的长度,如果数据小于255个字节,则采用一个字节来保存长度,反之需要两个字节来保存。
varchar 的最大有效长度由最大行大小和使用的字符集确定。
最大有效长度是65532字节,因为在varchar存字符串时,第一个字节是空的,不存在任何数据,然后还需两个字节来存放字符串的长度,所以有效长度是64432-1-2=65532字节。
例:若一个表定义为create table tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少? 答:(65535-1-2-4-30*3)/3
-- blob, text
blob 二进制字符串(字节字符串)
tinyblob, blob, mediumblob, longblob
text 非二进制字符串(字符字符串)
tinytext, text, mediumtext, longtext
text 在定义时,不需要定义长度,也不会计算总长度。
text 类型在定义时,不可给default值
-- binary, varbinary
类似于char和varchar,用于保存二进制字符串,也就是保存字节字符串而非字符字符串。
char, varchar, text 对应 binary, varbinary, blob.
3. 日期时间类型
一般用整型保存时间戳,因为PHP可以很方便的将时间戳进行格式化。
datetime 8字节 日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59
date 3字节 日期 1000-01-01 到 9999-12-31
timestamp 4字节 时间戳 19700101000000 到 2038-01-19 03:14:07
time 3字节 时间 -838:59:59 到 838:59:59
year 1字节 年份 1901 - 2155
4. 枚举和集合
-- 枚举(enum)
enum(val1, val2, val3...)
在已知的值中进行单选。最大数量为65535.
枚举值在保存时,以2个字节的整型(smallint)保存。每个枚举值,按保存的位置顺序,从1开始逐一递增。
表现为字符串类型,存储却是整型。
null值的索引是null。
空字符串错误值的索引值是0。
//列属性
-- primary key 主键
-- 唯一标识(唯一,非空)
create table tb ( id int, stu varchar(10), primary key (id));
create table tb ( id int, stu varchar(10), age int, primary key (stu, age));
-- unique 唯一索引(唯一约束)
使得某字段的值也不能重复。
--null 约束
null不是数据类型,是列的一个属性。
表示当前列是否可以为null,表示什么都没有。
null, 默认,允许为空。
not null, 不允许为空。
insert into tb values (null, 'val');
-- 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null
-- default 默认值属性
当前字段的默认值。
insert into tb values (default, 'val'); -- 此时表示强制使用默认值。
create table tb ( add_time timestamp default current_timestamp );
-- 表示将当前时间的时间戳设为默认值。
current_date, current_time
-- auto_increment 自动增长约束
自动增长必须为索引(主键或unique)
只能存在一个字段为自动增长。
默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置,或 alter table tbl auto_increment = x;
-- comment 注释
例:create table tab ( id int ) comment '注释内容';
-- foreign key 外键约束
用于限制主表与从表数据完整性。
alter table t1 add constraint `t1_t2_fk` foreign key (t1_id) references t2(id);
-- 将表t1的t1_id外键关联到表t2的id字段。
-- 每个外键都有一个名字,可以通过 constraint 指定
存在外键的表,称之为从表(子表),外键指向的表,称之为主表(父表)。
作用:保持数据一致性,完整性,主要目的是控制存储在外键表(从表)中的数据。
MySQL中,可以对InnoDB引擎使用外键约束:
语法:
foreign key (外键字段) references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下,可以设置为null.前提是该外键列,没有not null。
可以不指定主表记录更改或更新时的动作,那么此时主表的操作被拒绝。
如果指定了 on update 或 on delete:在删除或更新时,有如下几个操作可以选择:
1. cascade,级联操作。主表数据被更新(主键值更新),从表也被更新(外键值更新)。主表记录被删除,从表相关记录也被删除。
2. set null,设置为null。主表数据被更新(主键值更新),从表的外键被设置为null。主表记录被删除,从表相关记录外键被设置成null。但注意,要求该外键列,没有not null属性约束。
3. restrict,拒绝父表删除和更新。
注意,外键只被InnoDB存储引擎所支持。其他引擎是不支持的。
-- 常见约束
not null default unique check primary key foreign key
-- 条件查询
语法:
select
要查询的字段|表达式|常量值|函数
from
表
where
条件 ;
分类:
1.条件表达式
示例:salary>10000
条件运算符:
> < >= <= = != <>
2.逻辑表达式
示例:salary>10000 && salary<20000
逻辑运算符:
and(&&):两个条件如果同时成立,结果为true,否则为false
or(||):两个条件只要有一个成立,结果为true,否则为false
not(!):如果条件成立,则not后为false,否则为true
3.模糊查询:name like 'a%'
-:下划线通配符,一个下划线只能匹配单个字符
%:百分号通配符,表示任何字符出现任何次数(包括0次)。
%值:左模糊查询 (以值结尾的)
值%:右模糊查询(以值开始的)
%值%:全模糊查询(包含值的)
-- 排序查询
语法:
select
要查询的东西
from
表
where
条件
order by 排序的字段|表达式|函数|别名 【asc|desc】
-- 常见函数
一、单行函数
1、字符函数
concat拼接
substr截取子串
upper转换成大写
lower转换成小写
trim去前后指定的空格和字符
ltrim去左边空格
rtrim去右边空格
replace替换
lpad左填充
rpad右填充
instr返回子串第一次出现的索引
length 获取字节个数
2、数学函数
round 四舍五入
rand 随机数
floor向下取整
ceil向上取整
mod取余
truncate截断
3、日期函数
now当前系统日期+时间
curdate当前系统日期
curtime当前系统时间
str_to_date 将字符转换成日期
date_format将日期转换成字符
4、流程控制函数
if 处理双分支
case语句 处理多分支
情况1:处理等值判断
情况2:处理条件判断
5、其他函数
version版本
database当前库
user当前连接用户
-- 分组函数
sum 求和
max 最大值
min 最小值
avg 平均值
count 计数
特点:
1、以上五个分组函数都忽略null值,除了count(*)
2、sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
3、都可以搭配distinct使用,用于统计去重后的结果
4、count的参数可以支持:
字段、*、常量值,一般放1
建议使用 count(*)
-- 分组查询
语法:
select 查询的字段,分组函数
from 表
group by 分组的字段
特点:
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
针对的表 位置 关键字
分组前筛选: 原始表 group by的前面 where
分组后筛选: 分组后的结果集 group by的后面 having
4、可以按多个字段分组,字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名
-- 多表连接查询
笛卡尔乘积:如果连接条件省略或无效则会出现
解决办法:添加上连接条件
-- 1.传统模式下的连接 :等值连接——非等值连接
1.等值连接的结果 = 多个表的交集
2.n表连接,至少需要n-1个连接条件
3.多个表不分主次,没有顺序要求
4.一般为表起别名,提高阅读性和性能
-- 2.sql99语法:通过join关键字实现连接
含义:1999年推出的sql语法
支持:等值连接、非等值连接 (内连接)外连接 交叉连接
语法:
select 字段,...
from 表1
【inner|left outer|right outer|cross】join 表2 on 连接条件
【inner|left outer|right outer|cross】join 表3 on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】
好处:语句上,连接条件和筛选条件实现了分离,简洁明了!
-- 子查询
含义:
一条查询语句中又嵌套了另一条完整的select语句,其中被嵌套的select语句,称为子查询或内查询
在外面的查询语句,称为主查询或外查询
特点:
1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面,但一般放在条件的右侧
3、子查询优先于主查询执行,主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类:
① 单行子查询
结果集只有一行
一般搭配单行操作符使用:> < = <> >= <=
非法使用子查询的情况:
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集有多行
一般搭配多行操作符使用:any、all、in、not in
in: 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替
-- 分页查询
语法:
select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引,】条目数;
特点:
1.起始条目索引从0开始
2.limit子句放在查询语句的最后
3.公式:select * from 表 limit (page-1)*sizePerPage,sizePerPage
假如:
每页显示条目数sizePerPage
要显示的页数 page
-- 联合查询
引入:
union 联合、合并
语法:
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
select 字段|常量|表达式|函数 【from 表】 【where 条件】 union 【all】
.....
select 字段|常量|表达式|函数 【from 表】 【where 条件】
特点:
1、多条查询语句的查询的列数必须是一致的
2、多条查询语句的查询的列的类型几乎相同
3、union代表去重,union all代表不去重
-- 修改单表语法:
update 表名 set 字段=新值,字段=新值【where 条件】
-- 修改多表语法:
update 表1 别名1,表2 别名2
set 字段=新值,字段=新值
where 连接条件
and 筛选条件
-- 多表的删除:
delete 别名1,别名2
from 表1 别名1,表2 别名2
where 连接条件
and 筛选条件;
-- 悲观锁:先获取锁,再进行操作。(即“悲观”的认为获取锁是非常有可能失败的)
'一锁二查三更新' 在数据库上的悲观锁需要数据库本身提供支持,
通常用select ... for update操作来实现悲观锁。
begin;开启事务
select * from tb where ... for update; 设置悲观锁,其它事务中不能对tb进行修改
悲观的认为有操作就有修改
-- 乐观锁:先进行业务操作,不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的。
乐观锁在数据库上的实现完全是逻辑的,不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号,
或者时间戳,然后按照如下方式实现:
添加版本号version ,每更新一次version加1;如果查询的version与设置的不一致则代表数据被别人修改过。
mysql的基础知识总结至此,如有不足望不吝赐教。