mysql

数据库介绍,sql语句以及数据库优化

数据库简介

• 用途:用于存储生活中的一切数据

• 分类:

  • 关系型数据库:MySQL,Oracle,SQLserver,sqlite…
  • 非关系型数据库:Redis,MongoDB

• 概念:数据库的服务器,(数据库,数据表,数据,字段)

• SQL:全名为structured query language ,数据化查询语言

• 命令:

  • 数据定义语言(DDL):创建(create),删除(drop),修改(alter)
  • 数据操作语言(DML):增 ,删, 改
  • 数据查询语言(DQL):查
  • 数据控制语言(DCL):授权,取消授权
  • 数据事务语言(DTL):开启事务,提交事务,回滚

数据定义语言(DDL)

• 查看库:show databases; 显示连接到数据库服务器的所有数据库

• 创建库:create database user(库名); 创建一个叫user的数据库

• 删除库:drop database user(库名); 删除user数据库

• 选择数据库:use user(库名); 选择user数据库(使用user数据库)

  • 查看当前正在使用的数据库

    select database();

  • 查看当前数据库中的所有数据表

    show tables;

• 创建数据表: create table student(name varchar(20),age int(3));

• 查看数据表结构: desc user(表名);

• 查看服务器的字符集合存储引擎

  • 存储引擎: InnoDB,MyISAM

  • 查看当前的字符集

    show variables like “character%”;(默认为utf8)

  • 查看存储引擎

    show variables like “%storage%”;

• 查看创建语言

  • 查看库: show create database user(库名);
  • 查看表: show create table student(表名);

• 删除数据表: drop table student(表名);

• 修改表结构(alter)

  • 修改字段类型: alter table student modify name varchar(15);
  • 修改字段名: alter table student change name per_name char(20) 注意:change 可修改字段名和字段类型,使用时后面必须跟字段类型,否则会报错
  • 添加字段:
    • 在末尾添加(默认):alter table student add sex int(1);
    • 在指定字段后面添加:alter table student add classno int(10) after age;
    • 在开头添加:alter table person add class_id int(5) first;
    • 注:after 和first同样适用于modify和change

• 删除指定字段: alter table student drop classno;

• 修改表名: alter table student rename stu;

在数据库的一条命令结束后末尾一定要加英文的 ";"号

数据类型

• 整形

  类型	说明(大小-字节)
  tinyint	一个字节
  smallint	两个字节
  mediumint	三个字节
  int	四个字节
  bigint	八个字节

• 浮点型

  类型	说明
  varchar	可变长度的字符串
  char	定长的字符串
  text	文本类型,一般用于大型文本文件,如博文

• time类型

  类型	说明
  date	日期 ,格式为:“yyyy-mm-dd”
  time	时间,格式为:“HH-MM-SS”
  datetime	日期时间,格式为:“YYYY-mm-dd HH-MM-SS”
  timestamp	时间戳 ,从1970-1-1号到现在的所有时间转为秒
  year	年,格式为:“yyyy”

• 字段修饰

  类型	说明
  unsigned	无符号,可限制传入的int类型不为负数
  zerofill	高位不足填充0,可以防止负数的出现
  default	默认值
  not null	不为空
  auto_increment	自增,用于ing类型的字段,常和主键一起使用

• 字符集:

  • 查看系统的字符集: show character set;

• 存储引擎:

  • 查看系统支持的引擎: show engines;

  • 常见的存储引擎有:

    存储引擎	说明
    InnoDB	适合多写的操作,支持事务
    MyISAM	适合多读操作

• 索引管理

  • 作用:

    • 1.索引可以明显的加快数据的查询速度
    • 2.要确保表中多列唯一性,可以使用添加唯一索引的方法
    • 3.当查询中使用了order by和group by时,索引的使用可以明显的减少查询时间
    • 4.在进行多表联合查询的时候,如果创建了索引列,就可以提高表与表之间的连接速度

  • 分类

    索引	说明
    普通索引	index,最基本的索引
    唯一索引	unique,修饰的字段不能重复
    主键索引	primary key,一个特殊的唯一索引,一张表只能有一个主键
    全文索引	fulltext,对全局的数据添加索引

• 添加(普通)索引: alter table student add index(email);

• 查看索引:show index from student 表示查询表名称为student的表中的所有索引

• 删除索引:alter table student drop index email;

数据操作语言(DML)

• 增 ,删 ,改

• 创建一张表

  create table star(
   id int auto_increment,
   name varchar(20) not null,
   money float not null,
   province varchar(20) default null,
   age tinyint unsigned not null,
   sex tinyint not null,
   primary key(id)
   )engine=innodb default charset=utf8
  

• 插入数据

  • 不指定字段插入,添加数据的时候需要给定所有的字段值

    insert into star values(1,'胡歌',165465156,'上海',41,0);
    

  • 指定字段插入,只需要传递指定的字段值

    insert into star(name, money, age, sex, province)
    values("周杰伦", 78278278, 42, 0, "台湾"),
    ("杨幂", 72636633,39,1,"北京"),
    ("林俊杰",16465554,35,0,"上海");
    

    注意:一次性插入多条数据的时候,每条数据必须使用()括起来,数据之间使用","隔开;插入数据字段的顺序,与指定字段的顺序要一致,与数据库里面字段的顺序无关

  • 以下字段插入的时候不需要传递值:

    • 自增的字段
    • 有默认值的字段
    • 可以为空的字段

• 修改数据:

  update star set money=16456156154 where id=1;
  

  注意:修改的时候要加条件,否则会修改所有数据的指定值

• 删除数据:

  delete from star where name='胡歌';
  

  注意:删除的时候也要加条件,但在真实项目中,一般使用逻辑删除

  ​

数据查询语言(DQL)

• 基本查询:select * from star;

• 指定字段查询: select name,money from star;

• 过滤重复记录: select distinct province from star;

  • 说明:使用distinct指定的字段不能重复,可以指定多个字段

• 条件查询:

  条件	说明
  >	大于
  <	小于
  >=	大于等于
  <=	小于等于
  =	等于
  != 或<>	不等于
  and	并且
  or
  [not] between m and n	[不]在[m,n]的区间
  [not] in()	[不]在指定的集合中
  [not] like 条件	模糊匹配,%表示任意字符
  is [not] null	是否为空

  select * from star where id > 2;
  select * from star where id >5 and age >30;
  select * from star where age between 30 and 40;
  select * from star where id in (2,4,6);
  select * from star where province like "湖%";
  select * from star where province is null;
  

• 结果集排序(order by)

  select name, money from star order by money desc;
  select name, money, age from star order by age asc,money desc;
  

  • 说明:
    • 默认为升序ASC,降序使用desc
    • 多个字段进行排序的时候,先按照第一个进行排序,若有相同的数据再按照第二个排序

• 限制结果集(limit)

  limit m,n ----从下标为m的开始,取n条数据

   select * from star limit 3;#提取3条数据
   select * from star limit 3 offset 2;#跳过两条提取3条数据
   select * from star limit 2,3;#同上
  

• 聚合函数

  函数	说明
  count	统计个数
  sum	求和
  max	最大值
  min	最小值
  avg	平均值

  select count(*)  as c from star;    #可以给查询的字段起别名
  select max(money) as max_money from star;
  

• 分组和过滤(group by 和having)

  select sex from star group by sex;    #以性别进行分组
  select count(*) as c, sex from star group by sex;#分组以后并统计
  select count(*) as c, province from star group by province having c=1;#分组统计以后过滤
  

多表联合查询

create table user (
id int(11) not null auto_increment,
username varchar(20) default null,
gid int(11) ,
primary key(id));


create table goods (
id int(11) not null auto_increment,
name varchar(40) default null,
price float default null,
category varchar(20) default null,
primary key(id));

• 隐式内连接

  • 查询哪个用户购买了什么商品

    select username ,name from user,goods where user.gid=goods.id;

    [外链图片转存失败(img-HxA6K7XF-1566556390052)(D:\feiqiudownload\two_phase\SQL语句执行结果图集\批注 2019-08-17 151334.png)]

• 显示内连接

  • 说明:功能和隐式内连接相同,使用关键字join ,注意使用on来进行连接

    select username,name from user join goods on user.gid=goods.id;

    [外链图片转存失败(img-G97x44nE-1566556390054)(D:\feiqiudownload\two_phase\SQL语句执行结果图集\批注 2019-08-17 151715.png)]

• 左外连接

  • 说明:以左边的表为主,主要显示左边的表,右边的标有对应的数据则显示,没有显示null

    select username,name from user left join goods on user.gid=goods.id;

    [外链图片转存失败(img-DnC1kQru-1566556390058)(D:\feiqiudownload\two_phase\SQL语句执行结果图集\批注 2019-08-17 152345.png)]

• 右外连接

  • 说明:会显示右边表的所有数据,左表有对应的的数据则显示,没有则显示null

    select username,name from user right join goods on user.gid=goods.id;

    [外链图片转存失败(img-7zuKD7nS-1566556390059)(D:\feiqiudownload\two_phase\SQL语句执行结果图集\批注 2019-08-17 152741.png)]

• 记录联合

  • 格式 select 语句1 union select 语句2

  • 实例:select username,name from user right join goods on user.gid=goods.id union select username,name from user left join goods on user.gid=goods.id;

    [外链图片转存失败(img-g8el8r1e-1566556390062)(D:\feiqiudownload\two_phase\SQL语句执行结果图集\批注 2019-08-17 153124.png)]

  • union :去重后进行拼接

  • union all:将两边的查询结果直接拼接到一起

    select username,name from user right join goods on user.gid=goods.id union all select username,name from user left join goods on user.gid=goods.id;

    [外链图片转存失败(img-Ief5CnuE-1566556390063)(D:\feiqiudownload\two_phase\SQL语句执行结果图集\批注 2019-08-17 153351.png)]

• 联合更新

  • 实例: update user u, goods g set u.gid=3,g.price=g.price+1000 where u.gid=g.id and u.id=2;

• 子(嵌套)查询

  • select * from user where gid in(select id from goods);

    [外链图片转存失败(img-SGQJiuIv-1566556390065)(D:\feiqiudownload\two_phase\SQL语句执行结果图集\批注 2019-08-17 154225.png)]

数据事务语言

• 说明:用于测试表的存储引擎必须是innodb

• 开启事务:禁止自动提交

  select @@autocommit #查看是否为自动提交,为1则表示自动提交,默认为1
  set autocommit =0;	#设为禁止自动提交
  

• 提交事务:事务过程没有出现问题的情况下

  commit;
  

• 事务回滚:事务出现了问题,进行回滚,返回事物开始前的状态

  rollback;
  

• 代码

  #开启事务
  try:
  #执行相关的操作
  except  Exception as e:
  #操作回滚
  else:
  #提交事务
  

数据控制语言

• 查看授权

  • 格式:show grants [for ‘user’@‘localhost’]
  • 说明:查看指定用户的权限,若不指定,则查看当前用户的权限

• 创建用户

  • 格式: create user’user’@‘host’ identified by ‘password’
  • 实例: create user’new_user’@‘localhost’ identified by ‘123456’;

• 用户授权

  • 格式: grant 权限 privileges on 库名.表名 to ‘user’@‘host’ identified by ‘password’;

  • 实例: grant all privileges on school.student to ‘user’@‘localhost’ identified by ‘123456’;

  • 权限 :insert ,delete,update,select,all表示所有权限

    注意:当给用户授予制定的单项权限时应使用如下表示方法:

    • grant insert on school.student to ‘user’@‘localhost’ identified by ‘123456’;

• 刷新权限 :flash privileges;

• 取消权限:

  • 格式: revoke 权限 privileges on 库名.表名 from ‘user’@‘host’;

    revoke all privileges on school.student from ‘user’@‘localhost’;

• 删除用户

  • 格式 :drop user ‘user’@‘host’;

    drop user’user’@‘localhost’;

备份和恢复

• 备份:把数据库中的数据保存到文件中

  mysqldump -u root -p test(要备份的数据名) >test.sql(保存的文件名)

• 恢复:从保存的sql文件中,解析执行sql语句

  mysql -uroot -p test2(解析后的文件名)<test.sql(要解析的文件名);

数据库优化

优化基本手段

• 说明:计算机性能的瓶颈通常在于磁盘的IO
• 手段:
  • 进行架构的调整
  • 进行硬件提升
  • 减少磁盘IO
  • 优化SQL语句
  • 合理的数据库设计

数据库设计

• 存储引擎选择: 根据需要选择合适的引擎

• 数据表的设计

  • 第一范式：表的所有字段都是不可再分割的，称为满足第一范式，记为1NF

  优化前：
  用户ID 用户名 手机号 地址
  优化后：
  用户ID 用户名 手机号 省份 城市 详细地址
  

  • 第二范式：表的字段之间没有部分依赖时，称为满足第二范式，记为2NF

    优化前：
    学号 姓名 年龄 课程号 课程名称 课程分数
    优化后：
    学号 姓名 年龄
    课程号 课程名称
    学号 课程号 课程分数
    

  • 第三范式:表的字段之间没有传递依赖时，称为满足第三范式，记为3NF

    优化前：
    学号 姓名 年龄 性别 所在院校 院校地址 院校电话
    优化后：
    学号 姓名 年龄 性别 院校代号
    院校代号 院校名称 院校地址 院校电话
    

  • 总结:有时我们会根据业务的需要进行反范式设计,通过适当的冗余以减少关联查询

• 使用字段索引

  • 根据需要添加合适的字段索引

优化SQL语句

• 慢查询
  • 说明:mysql自带功能,默认是关闭,开启后会记录执行较慢的SQL语句
  • 作用:定位耗时的SQL语句
• explain
  • 说明:查询执行计划,可以查看语句的执行情况
  • 示例:explain select * from student where id>3\G;
• 优化示例(思路):
  • 尽量不要使用select * 查询,需要什么字段就查询什么字段
  • 需要多少数据就去多少数据,可以使用limit来进行限制
  • 尽量避免复杂的join或子查询
  • 禁止结果集自动排序:分组后结果集会自动排序,使用order by null可以阻止自动排序

减少磁盘的IO

• 说明:简单理解就是使用缓存技术替代或者减少数据库的读写

• 原理:

  请求到来,先检查缓存中是否有相关数据,有缓存数据直接返回,若没有,则从数据库中读取,然后保存到缓存中再返回数据

进行硬件提升

• 说明:当资源使用效率足够高,但依然不能满足要求,可以选择提升硬件配置

进行架构调整

• 单台服务器架构

• 数据库读写分离
  据业务的需要进行反范式设计,通过适当的冗余以减少关联查询

• 使用字段索引

  • 根据需要添加合适的字段索引

优化SQL语句

• 慢查询
  • 说明:mysql自带功能,默认是关闭,开启后会记录执行较慢的SQL语句
  • 作用:定位耗时的SQL语句
• explain
  • 说明:查询执行计划,可以查看语句的执行情况
  • 示例:explain select * from student where id>3\G;
• 优化示例(思路):
  • 尽量不要使用select * 查询,需要什么字段就查询什么字段
  • 需要多少数据就去多少数据,可以使用limit来进行限制
  • 尽量避免复杂的join或子查询
  • 禁止结果集自动排序:分组后结果集会自动排序,使用order by null可以阻止自动排序

减少磁盘的IO

• 说明:简单理解就是使用缓存技术替代或者减少数据库的读写

• 原理:

  请求到来,先检查缓存中是否有相关数据,有缓存数据直接返回,若没有,则从数据库中读取,然后保存到缓存中再返回数据

进行硬件提升

• 说明:当资源使用效率足够高,但依然不能满足要求,可以选择提升硬件配置

进行架构调整

• 单台服务器架构
• 数据库读写分离
• 高可用负载均衡