MySQL基础知识
这几天一直在学数据库(只是初步了解一下),同时也整理了一下笔记。如下:
MySQL简单介绍
MySQL是一种关系型数据库。关系型数据库的特点:
- 数据以表格的形式出现
- 每行为各种记录名称
- 每列为记录名称所对应的数据域
- 许多的行和列组成一张表单
- 若干的表单组成database
SQL基础语法(增删改查)
关于数据库(database)的基本操作:
有关数据库的基础语法 | 描述 |
---|---|
use 数据库名; | 选择数据库 |
create database 数据库名 (charset 编码格式); | 创建数据库(增) |
drop database 数据库名; | 删除数据库(删) |
alter database 数据库名 (charset 修改后的编码格式); | 修改数据库(改) |
show create database 数据库名; | 查看当前新创建的数据库(查) |
show databases; | 查看所有数据库(查) |
#db1为数据库名(举例声明)
use db1; #选择db1的数据库进行操作
create database db1 charset utf8; #创建一个名为db1的数据库(编码格式为utf8,不写则为默认值)
drop db1; #删除名为db1的数据库
alter database db1 charset gbk; #将db1数据库的编码格式改成gbk
show create database db1; #查看当前新创建的db1数据库
show databases; #查看所有数据库
关于数据表(table)的基本操作:
有关数据表的基础语法 | 描述 |
---|---|
create table 数据表名(数据名,数据类型); | 创建数据表(增) |
drop table 数据表名; | 删除数据表(删) |
alter table 数据表名 modify 数据名 修改后的数据类型; | 修改数据表(改) |
show create table 数据表名; | 查看当前新创建的数据表(查) |
show tables; | 查看所有数据表(查) |
describe(desc) 数据表名; | 查看数据表的结构(查) |
#t1为数据表名 id,name为变量名 int,char(20)为数据类型(举例声明)
create table t1(id int,name char(20)); #创建一个名为t1的数据表
drop table t1; #删除名为t1的数据表
alter table t1 modify id tinyint; #将t1数据表中的id数据的数据类型改成tinyint
show create table t1; #查看当前新创建的数据表t1
show tables; #查看当前数据库中的所有数据表
describe t1; #查看数据表的结构
关于数据记录的基本操作:
有关数据记录的基础语法 | 描述 |
---|---|
insert into 数据表名 (数据名) value (数据值) ; | 创建数据记录(增) |
delete from 数据表名 where 条件; | 删除数据记录(删) |
update 数据表名 set 数据名=新数据值 where 条件; | 修改数据记录(改) |
select 数据名 from 数据表名; | 查看数据表中的数据记录(查) |
#t1为数据表名 id,name为变量名 int,char(20)为数据类型(举例声明)
insert into t1 (id,name) values (1,'wula'); #往数据表t1中添加数据记录
delete from t1 where id = 1; #删除数据表t1中id为1的数据记录
update t1 set id = 2 where id = 1; #将数据表t1中id为1的数据值改成2
select id from t1; #查看数据表t1中id的数据记录
select * from t1; #查看数据表t1中的所有数据记录
MySQL的数据类型
MySQL的数据类型大致可以分为三类:数值类型、日期时间类型和字符串类型。
类型名 | 大小 | 描述 |
---|---|---|
数值类型 | ||
TINYINT | 1 byte | 小整数值 |
SMALLINT | 2 bytes | 大整数值 |
MEDIUMINT | 3 bytes | 大整数值 |
INT | 4 bytes | 大整数值 |
BIGINT | 8 bytes | 极大整数值 |
FLOAT | 4 bytes | 单精度浮点值 |
DOUBLE | 8 bytes | 双精度浮点值 |
DECIMAL | * | 小数值 |
日期时间类型 | ||
DATE | 3 bytes | 日期 |
TIME | 3 bytes | 时间 |
YEAR | 1 byte | 年份 |
DATETIME | 8 bytes | 日期加时间 |
TIMESTAMP | 4 bytes | 时间戳 |
字符串类型 | ||
CHAR | 0~255 bytess | 定长字符串 |
VARCHAR | 0~65535 bytes | 变长字符串 |
TINYBLOB | 0~255 bytes | 二进制字符 |
TINYTEXT | 0~255 bytes | 短文本字符串 |
BLOB | 0~65535 bytes | 二进制形式的长文本数据 |
TEXT | 0~65535 bytes | 长文本数据 |
MEDIUMBLOB | 0~16777215 bytes | 二进制形式的中长文本数据 |
MEDIUMTEXT | 0~16777215 bytes | 中长文本数据 |
LONGBLOB | * | 二进制形式的极大文本数据 |
LONGTEXT | * | 极大文本数据 |
其他类型 | ||
enum | * | 枚举(单选) |
set | * | 集合(多选) |
简单使用一下枚举与集合:
#枚举的使用
create table t1(gender enum('female','male','no')); #数据值只能在female,male,no三个中选一个,单选
create table t1(hobby set('football','swim','run')); #数据值只能在footall,swim,run三个里面选,可多选
约束
非空约束(not null):字段可以重复但不能为空
默认值约束(default):字段为空时会有默认值
唯一约束(unique):字段不能重复单但可以为空
主键约束(primary key):字段不能重复且不能为空
外键约束(foreign key):将两个表进行联系。其中被关联表叫做主表,关联表叫做从表
#t1,t2为数据表名 id,name为变量名 int,char(20)为数据类型(举例声明)
#以创建数据表为例子
#非空约束(not null)
create table t1(id int no null,name char(20)); #对id变量加上非空约束
#默认值约束(default)
create table t1(id int default 1,name char(20)); #对id变量加上默认值为1约束
#唯一约束(unique)
create table t1 (id int unique,name char(20)); #对id变量加上唯一性约束
create table t1 (id int,name char(20),unique(id)); #对id变量加上唯一性约束
#主键约束(primary key) 效果:primary key=not null + unique
create table t1 (id int primary key,name char(20)); #建表前对id变量加上主键约束
create table t1 (id int,name char(20),primary key(id)); #建表前对id变量加上主键约束
alter table t1 add primary key (id); #建表后对id变量加上主键约束
#外键约束(foreign key)
#(方法1)将主表与从表的id2与id1之间建立联系。主表中的数据记录被从表引用时,主表中的该记录不可被删除
create table t1 (id1 int,name1 char(20)); #主表
create table t2 (id2 int,name2 char(20),constraint link foreign key(id2) references t1(id1)); #从表
#(方法2)将主表与从表的id2与id1之间建立联系。主表中的数据记录被从表引用时,删除主表中的数据记录时,会相应删除从表中的数据记录
create table t1 (id1 int,name1 char(20)); #主表
create table t2 (id2 int,name2 char(20), #从表
constraint link foreign key(id2) references t1(id1)
on delete cascade on update cascade #同步删除
);
tip:多字段添加约束与联合添加约束的不同
#以唯一约束(unique)为例子
#多字段添加唯一约束:代表数据记录中不能存在id或name相同的数据。(id不同和name不同的数据才能添加)
create table t1 (id int unique,
name char(20) unique
);
#联合添加唯一约束:代表数据记录中不能存在id与name相同的数据。(id不同或name不同的数据也能添加)
create table t1 (id int ,
name char(20) ,
unique(id,name)
);
三大范式(略)
第一范式: 所有字段值都是不可拆分的原子值
第二范式: 主键以外的其他列都必须依赖于主键的全部
第三范式: 出来主键以外的其他列之间不能有传递历来关系
表与表的关系
表与表的关系可分成三种:一对多/多对一,多对多,一对一
先把两张表看做表1与表2,再进行比较判断
一对多/多对一:表1中的一条记录对应表2中的多条记录或表2中的多条记录对应表1中的多条记录
例:游戏里的一个英雄对应多个皮肤
多对多:表1中的一条记录对应表2中的多条记录与表2中的多条记录对应表1中的多条记录
例:一名学生能够选多个课程,一个课程也可以被多个学生选
一对一:表1中的一条记录唯一对应表2中的一条记录与表2中的一条记录唯一对应表1中的一条记录
例:一个人对应一个身份证号,一个身份证号对应一个人
单表查询
关键字的优先级顺序:from→where→group by→having→distinct→order by→limit
1.from(寻表)
select * from t1;
2.where(约束)
–比较运算符:>,<,>=,<=,<>(不等于),!=
–between a and b 值在a与b之间
–in(a,b,c) 值是a或b或c
–like’ ’ %代表任意多个字符,_代表一个字符
–逻辑运算符and or not
select * from t1 where age > 18; #查找t1表中年龄大于18的数据
select * from t1 where age between 18 and 20; #查找t1表中年龄在18至20之间的数据
select * from t1 where age in(18,20); #查找t1表中年龄为18,20的数据
select * from t1 where name like '黄%'; #查找t1表中姓黄的数据
select * from t1 where age > 18 and name like '黄%'; #查找t1表中年龄大于18且姓黄的数据
3.group by(分组)
聚合函数:
- max() 最大值
- min() 最小值
- sum() 求和
- count() 求总个数
- avg() 求平均值
select gender avg(age) from t1 group by gender; #按照性别分组求各分组的平均年龄
4.having(过滤)
select count(id) from t1 group by gender having count(id) > 10; #按照性别分组求出总人数大于10的性别
5.distinct(去重)
select distinct age from t1; #在t1表中求出不重复的年龄(去重)
6.order by(排序)
–asc(升序)
–desc(降序)
select * from t1 order by age asc; #在t1表中按照年龄age升序排序
select * from t1 order by age desc; #在t1表中按照年龄age降序排序
select * from t1 order by age asc,id desc; #在t1表中先按照年龄age升序排序,再按照id降序排序
7.limit(限制)
select * from t1 limit 5; #从头开始获取5个数据(id1~id5)
select * from t1 limit 5,5; #从第5个开始获取5个数据(id6~id10)
多表查询
多表查询分成三种:多表连接查询,符合条件查询,子查询
多表连接查询
多表连接查询分成内连接,左连接,右连接与外连接。
#数据库t1中有id1数据记录,数据库t2中有id2数据记录(举例声明)
#内连接:(只获取匹配的记录)
select * from t1 inner join t2 on t1.id1 = t2.id2;
#左连接:(只获取左表的所有记录)
select * from t1 left join t2 on t1.id1 = t2.id2;
#右连接:(只获取右表的所有记录)
select * from t1 right join t2 on t1.id1 = t2.id2;
#外连接:(获取所有记录)
select * from t1 left join t2 on t1.id1 = t2.id2 union select * from t1 right join t2 on t1.id1 = t2.id2;
符合条件查询
就是在多表连接查询的条件下加上其他约束
select * from t1 inner join t2 on t1.id1 = t2.id2 where age > 18 ;
子查询
–子查询是将一个查询语句嵌套在另一个查询语句中
–内层查询语句的查询结果,可以为外层查询语句提供查询条件
–子查询中可以包含:IN,NOT IN,ANY,ALL,EXISTS,NOTEXISTS等关键字
–子查询中还可以包含比较运算符:>,<,!=等
select name from t1 where id1 in(select id2 from t2 where age > 18);
select * from t1 where age >(select avg(age) from t1);
索引
在拥有庞大数据量时,很必要建立索引。有索引的查询速度比无索引快。
常见的索引类型:普通索引,唯一索引,主键索引,联合索引
普通索引
特点:快速查找
#普通索引举例
#在建表创建时添加普通索引
create table t1 (id int auto_increment ,
name char(20) not null,
index sy_name(name) #给name添加普通索引
);
#在建表后添加普通索引
create index sy_name on t1(name); #给name添加普通索引
alter table t1 add index sy_name(name) #给name添加普通索引
#删除普通索引(其他的索引也一样)
drop index sy_name on t1; #删除name的普通索引
#查看索引(其他的索引也一样)
show index from t1; #查看t1数据表所添加的索引
唯一索引
特点:快速查找+唯一性
#唯一索引举例
#在建表创建时添加唯一索引
create table t1 (id int auto_increment ,
name char(20) not null,
unique index usy_name(name) #给name添加唯一索引
);
#在建表后添加唯一索引
create unique index usy_name on t1(name); #给name添加唯一索引
alter table t1 add unique index usy_name(name) #给name添加唯一索引
主键索引
特点:加快查找+唯一性+不为空
#主键索引举例(用法和添加主键一样)
#在建表创建时添加主键索引
create table t1 (id int auto_increment primary key, #给id添加主键索引
name char(20) not null,
);
#在建表后添加主键索引
alter table t1 add primary key (id); #给id添加主键索引
联合索引
特点:合并查找速度更快(联合索引的查询速度比索引合并的查询速度快)
#在建表创建时添加联合索引
create table t2 (id int auto_increment ,
name char(20) not null,
index sy_name(id) #给id和name添加联合索引
);
#在建表后添加联合索引
create index sy_name on t1(id,name); #给id和name添加联合索引
对于联合索引(以上述为例),在进行查找时,查找id会使用索引,查找id+name会使用索引,查找name则不会使用索引。
执行计划(略)
通过explain来查看查询的速度。
explain select * from t1; #得到的查询速度是ALL
查询速度比较:(慢)all<index<range<index_range<ref_or_null<ref<eq_ref<system<const(快)
MySQL事务(略)
在默认情况下,MySQL会自动管理事务,一条SQL语句独占一个事务。
事务的特点:
- 原子性:是指事务是一个不可分割的整体,事务的操作要么成功,要么失败
- 一致性:事务处理前后的数据的完整性必须保持一致
- 隔离性:是指多个用户访问同一个数据库时,一个用户的事务不会受到干扰,多个并发的事务相互隔离
- 持久性:是指一个事务一旦被提交,其对数据库中的数据是永久改变的