注册    登录
今天看啥  ›  专栏  ›  光之家_大头领

Mysql总结

光之家_大头领  · CSDN  ·  · 2021-02-25 10:17

mysql数据类型

1、整数类型（单位：字节）

整数类型 byte
tinyint 1
smallint 2
int 4
bigint 8

2、小数类型

浮点型 byte
float 4
double 8

定点型：decimal(m,d)，m是数字长度（整数位+小数位），d是小数位的长度
3、日期的时间类型

类型 byte 表示示例
year 1 年：2021
time 3 时间：13:38:20
date 3 日期：2021-02-07
datetime 8 日期+时间：2021-02-07 13:38:20
timestamp 4 时间戳：1970-01-01 00：00：00 到现在的毫秒数

4、字符串类型

类型 byte
char(x) x
varchar(x) x+1
text(x) x+2

5、二进制类型

类型 byte
bit(x) x
binary(x) x
varbinary(x) x
tinyblob 2^16-1
mediumblob 2^24-1
longblob 2^32-1

管理数据库结构 

#1、创建数据库
# create database people default character set utf8 collate utf8_general_ci;
#2、删除数据库
# drop database people;
#3、删除表
# drop table user;
#4、查看所有数据库
# show databases;
#5、选择数据库
# use people;
#6、创建表
# use people;
# create table user(
#     id int,
#     name varchar(11),
#     score float
# )
#7、删除表
# drop table people.user;
#8、查看所有的数据表
# use people;
# show tables ;
#9、查看表结构
# desc people.user;
#10、修改表结构
#11、修改表结构
# （1）新增一个字段
# alter table user add title varchar(11);
# (2)修改一个字段
# alter table user change title age int;
# (3)删除一个字段
# alter table user drop age;
# desc user;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

常用SQL函数 

# 一、日期函数
#1、获取当前日期
# select current_date;
#2、获取当前时间
# select current_time;
#3、获取当前日期+时间
# select now();
#4、计算d1与d2之间相隔的天数(老日期在前，返回天数为负；新日期在前，返回天数为正)
# select datediff('2021:02:08','2020:03:08');
#5、获取d日期100天之后的新日期
# select adddate('2021:02:08',100);
#6、获取d日期100之前的旧日期
# select subdate('2021:02:08',9200);

# 二、聚合函数
#1、根据某个字段统计总记录数，若字段值设置为null不计算
# select count(score) from user;
# 2、计算某个字段的总和
# select sum(score) from user;
# 3、计算某个字段值的平均值
# select avg(score) from user;
# 4、最大值
# select max(score) from user;
# 5、最小值
# select min(score) FROM user;

# 三、分组查询
# 1、根据名称、平均分分组
# select name,avg(score) from user group by name;
# 2、根据名称、平均分分组，平均分降序排序（desc降序、asc升序）
# select name,avg(score) from user group by name  order by avg(score) desc ;
# 3、根据名称、平均分分组，平均分降序排序，且平均大于70
# select name,avg(score) from user group by name having avg(score)> 70 order by avg(score) desc ;

#四、模糊查询
# 1、查询name中包含“大”字的数据
# %大%：包含所有大  %大：以大结尾  大%：以大开头
# select * from user where name like '%大%'
# 2、"_":限定一个长度去查询
# __:两个字查询 __大：长度为3，以大结尾
# select * from user where name like '__大';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41

主外键

1、主键
用来唯一标识某条数据，便于查询和管理，建议int类型，保证主键值不冲突
语法： 

create table user(
    # 设置主键
    id int primary key auto_increment,
    name varchar(11)
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 1
  • 2
  • 3
  • 4
  • 5

2、外键
为了构建表与表之间的关系，主外键之间的约束关系
3、标语表之间的关系
（1）一对一
例如人类作为一张表，身份证id作为一张表
（2）一对多
学生作为一张表，班级多为一张表
例如： 

#班级表，主表
create table class(
    id int primary key auto_increment,
    name varchar(11)
);
#学生表，从表（有外键的表）
create table student(
    id int primary key auto_increment,
    name varchar(11),
    cid int,
#     设置外键连接
    foreign key (cid) references class(id)
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

（3）多对多
大学选课，学生作为一张表，课程作为一张表
例如： 

#用户表
create table user(
    # 设置主键
    id int primary key auto_increment,
    name varchar(11)
);
#课程表
create table course(
    id int primary key auto_increment,
    name varchar(11)
);
# 从表，中间表
create table user_course(
    id int primary key auto_increment,
    uid int,
    cid int,
    foreign key (uid) references user(id),
    foreign key (cid) references course(id)
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

多表关联查询

1、嵌套查询
查询张三所在班级 

select * from class where id = (select cid from student where student.name = '张三');
  • 1
  • 1

2、连接查询
（1）内连接
笛卡尔积
查询一班中的所有学生 

#查询所有学生
select student.name from student;
  • 1
  • 2
  • 1
  • 2
#笛卡尔积
select student.name from student inner join class;
  • 1
  • 2
  • 1
  • 2
#筛选出一班与所有学生做笛卡尔积
select student.name from student inner join class where class.name = '一班';
  • 1
  • 2
  • 1
  • 2
#最终结果
select student.name from student inner join class WHERE class.name='一班' and class.id=student.id;
#select s.name from student s ,class c where c.name='一班' and c.id=s.id;
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

（2）外连接（左连接、右连接）

左连接：左表所有数据和右表满足条件的数据的笛卡尔积

右连接：右边所有数据和左表满足条件的数据的笛卡尔积

3、一对多查询
查询王五所在的班级 

select student.name,class.name 
from student,class 
where student.name='王五' and class.id=student.cid;
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

4、多对多查询

查询学习java的学生 

select  u.name,c.name 
from user u ,course c ,user_course uc  
where u.id=uc.uid and c.id=uc.id and c.name = 'java' ;
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

查询张三学习的课程 

select  u.name,c.name 
from user u ,course c ,user_course uc  
where u.id=uc.uid and c.id=uc.cid and u.name = '张三' ;
  • 1
  • 2
  • 3
  • 1
  • 2
  • 3

5、去重 

select  distinct u.name,c.name from user u ,course c ,user_course uc  where u.id=uc.uid and c.id=uc.cid and u.name = '张三' ;
  • 1
  • 1

6、分页 

select * from user limit 0,10;
  • 1
  • 1

数据库索引

1、索引就是指向表中数据的指针，添加索引后能够快速查询某条记录。所有字段都可以添加索引，与主外键一样。索引类似于查询内容的目录
索引的数据结构是B+tree，mysql用page来存储数据的，将不同的数据存储到不同的page中。
数据库底层对于数据的存储使用的是链表的结构，查询效率低，在此基础上通过树状的结构来达到快速检索数据的目的。
2、索引分类：
（1）普通索引
（2）唯一索引
（3）全文索引
（4）单列索引
（5）多列索引
（6）空间索引
3、选择索引
索引会消耗空间以及查询时间
（1）在where语句后面的列添加索引
（2）为了效率，索引的值最好唯一
（3）不要添加过多的索引，这样维护的成本高，降低数据库性能
4、添加索引
（法1）alter table 表名 add index 索引名称(字段);
（法2）create index 索引名称 on 表名(字段);
5、删除索引
（法1）alter table 表名 drop index 索引名称;
（法2）drop index 索引名称 on 表名;

数据库事务(transaction)

1、事物：将多条sql语句作为一个整体来执行，要么全部成功要么全部失败。
2、事物的四要素
（1）原子性：多条sql语句是个整体不可分割
（2）一致性：sql语句执行前后，数据库的值保持一资
（3）隔离性：事务之间的执行是相互独立的
（4）永久性：事务一旦提交，数据库中数据的改变是永久的
3、事务的操作
（1）开启事务：start transaction;
（2）事务回滚（恢复成执行前的结果）：rollback;
（3）提交事务：commit;
注：mysql是默认自动提交的
show variables like ‘autocommit’; ：查询是否自动提交
set autocommit 0/1 : 取消/自动

数据库视图

视图是数据库中虚拟的表，允许不同的用户以不同的方式查看同一张表的数据
（1）创建视图
create view 视图名 as select 字段 from 表名;
（2）使用视图
select * from view;
（3）删除视图
drop view 视图名称;

数据库触发器（trigger）

1、某些数据表中的数据需要进行数据同步的时候使用，触发器是预先定义一系列的操作，可以在对指定表进行插入、更新、删除时自动完成这些操作
2、优点
（1）开发快，trigger存储在数据库中，在应用程序中不用手动调用触发器，会自动执行
（2）易维护，访问目标表会自动调用触发器，实现数据同步
（3）业务全局实现，若修改业务只需要修改触发器即可，不需要修改业务代码
3、分类
（1）前触发器：在更新、插入、删除前进行的操作
（2）后触发器：在更新、插入、删除后进行的操作
4、触发器使用
创建触发器：
示例1 

create trigger afterinsert
	 after insert on tab1 
	 for each row 
	 begin 
	 insert into tab2(tab2_id) 
values(new.tab1_id); 
	end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

示例2 

create trigger afterdelete
	after delete on tab1 
	for each row 
	begin 
	delete from tab2 where tab2_id = old.tab1_id; 
end;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

删除触发器： 

drop trigger afterinsert;
  • 1
  • 1



原文地址：访问原文地址
快照地址： 访问文章快照
推荐文章
微观三农  ·  农业农村部部署当前防灾减灾夺秋粮丰收工作
9 小时前
微观三农  ·  农业农村部答复推广“三物循环”有关建议
18 小时前
微观三农  ·  实录丨国新办举行“推动高质量发展”系列主题新闻发布会（农业农村部）
2 天前
微观三农  ·  八大流域防汛抗洪 近期重点做好这些工作
4 天前
微观三农  ·  习近平对陕西商洛市柞水县境内一高速公路桥梁发生垮塌作出重要指示
6 天前
合天网安实验室  ·  Apache OFBiz CVE-2023-49070 & 最新 CVE-2023-51467 未授权分析
6 月前
正和岛  ·  王健林，打了一场翻身仗！
1 年前
格斗迷  ·  嘴炮盛赞中国蒙古族悍将，刚刚迎来UFC两连胜！
1 年前
二马由之  ·  说说抄作业的正确姿态
2 年前
土家野夫  ·  -20201028231211
3 年前
关于   移动版
今天看啥 - 微信公众号rss订阅, 微信rss, 稳定的RSS源
© 2024 ~ 沪ICP备11025650号