第十章 数据库

 

第十章 数据库

10.1 数据库

• 常见函数

  database(),use(),concat(),now(),passward()

10.1.1数据库的引入

• 自己处理一些数据备份,容错的措施

• 很多功能如果只是通过操作文件来改变数据是非常繁琐的

  • 程序员需要做很多事情

• 多于多个进程或多台机器操作同一份数据时

  • 程序员自己解决并发和安全问题比较麻烦

• c/s架构的操作数据文件的一个管理工具

  • 1.帮助我们解决并发问题

  • 2.帮我们用更简单更快捷的方式完成数据的增删改查

  • 3.给我们提供一些容错,高可用机制

  • 4.权限的认证

10.1.2数据库管理系统(DBMS)

• 专门用来管理数据,帮助用户更简洁的操作数据的软件

• 文件夹 --------数据库(database简写db)

• 数据库管理员---------DBA(database administrator)

• 数据库(管理系统)

  • 关系型数据库

    • mysql 开源

      • 小公司,互联网公司

    • oracle 收费,比较严谨,安全性比较高

      • 用于国企,事业单位银行,金融行业

    • sql server

  • 非关系数据库

    • redis

    • mongodb

10.2初识mysql

10.2.1 mysql的安装

10.2.2 mysql的cs架构

• mysqld install 安装数据库服务

• net start mysql 启动数据库的server端

• net stop mysql 停止server端

• 客户端可以是python代码,也可以是一个程序

  • mysql.exe 是一个客户端

  • mysql -u用户名 -p密码

10.2.3mysql的用户和权限

• 在安装一个数据库后,有一个最高权限的用户root

• mysql -h 连接ip地址,如果不写默认连接本地

• 我们的mysql客户端不仅可以连接本地的数据库,也可以连接网络上某个数据库的server端

• mysql>select user()

  • 查看当前用户是谁

• mysql>set password = password('密码')

  • 设置密码

• mysql>create user 's21'@'192.168.12.%' identified by '123';

  • 创建用户

• mysql>show databases;

  • 查看文件夹

• mysql>creat database day37;

  • 创建文件夹

• mysql>grant all on day37. * to 's21'@'192.168.12.%'; #赋予day37中所有表的所有权限

  • 授权并创建用户

  • flush privileges 让权限立即生效

  • mysql>grant all on day37. * to 'alex'@'%' identified by '123';

• mysql>show grants for 's21'@'192.168.12.%';

  • 查看当前用户有哪些权限

10.2.4库,表.数据

• 创建库,创建表 DDL(数据库定义语言)

• 存数据,删数据,修改语言,查看 DML(数据库操纵语言)

• grant revoke DCL(控制权限)

10.2.3.1库操作

• mysql>creat database 数据库名;

  • 创建库

• mysql>show databases;

  • 查看当前有多少数据库

• mysql>select database();

  • 查看当前使用的数据库是什么

• mysql>use 数据库名;

  • 切换到这个数据库(文件夹)下

• mysql>drop datatbase

  • 删库(不要轻易使用,除非逼不得已)

10.2.3.2表操作

• mysql>show tables;

  • 查看当前文件夹中有多少张表

• mysql>create table student(id int,name char(4));

  • 创建表

• mysql>drop table student;

  • 删除表格

• mysql>desc 表名

  • 查看表字段的基础信息

• mysql>show create table 表名

  • 能够看到和这张表相关的所有信息

• mysql>describe 表名

10.2.3.3操作表中的数据

• mysql>insert into student values(1,'alex')

  • 数据的增加

• mysql>select * from student;

  • 数据的查看

• mysql>update 表 set 字段名 = 值

  • 修改数据

  • 例 : mysql>update student set name = 'yuan';

    • 会修改全部

  • 例 : mysql>update student set name = 'yuan' where id = 2 ;

    • 会修改要修改内容

• mysql>delete from 表名字

  • 删除数据

   

  #总结:
  #SQL:结构化查询语言(Structured Query Language)
     #DDL((Data Definition Language) 数据库定义语言
     #是用于描述数据库中要存储的现实世界实体的语言。
        #创建用户
           # mysql>create user '用户名'@'%'   表示网络可以通讯的所有ip地址都可以使用这个用户名
           # mysql>create user '用户名'@'192.168.12.%'   表示192.168.12.0网段的用户可以使用这个用户名
           # mysql>create user '用户名'@'192.168.12.87' 表示只有一个ip地址可以使用这个用户名
     #创建库
        # mysql>creat database day38;
        #创建表
        # mysql>creat table 表名(字段名 数据类型(长度)，字段名 数据类型(长度)，)
      
    #DML(Data Manipulation Language) 数据库操纵语言
    #是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除，是开发以数据为中心的应用程序必定会使用到的指令，因此有很多开发人员都把加上SQL的SELECT语句的四大指令以“CRUD”来称呼。
   # mysql>insert into student values(1,'alex') 增
   # mysql>delete from 表名字                     删
   # mysql>update 表 set 字段名 = 值             改
   # mysql>select/show                           查
          #select
            # mysql>select user(); 查看当前用户
            # mysql>select database(); 查看当前所在的数据库
          #show
            # mysql>show databases;   查看当前的数据库有哪些
            # mysql>show tables； 查看当前的库中有哪些表
          # mysql>desc 表名; 查看表结构
          # mysql>use 库名;   切换到这个库下
          
     #DCL(Data Control Language) 数据库控制语言
   #grant select on 库名.* to '用户名'@'ip地址/段' identified by '密码'

10.3mysql中的存储引擎

mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。

#查看当前的默认存储引擎:
mysql> show variables like "%engine%";
#查询当前数据库支持的存储引擎
mysql> show engines \G;
#索引 -----------数据库的目录
​
#查看当前的默认编码
mysql>show variabls like '%chara%'

10.4表结构

10.4.1表的存储方式

• 存储方式一: MyISAM 5.5及以下默认存储方式

  • 存储文件个数:表结构表中的数据,索引,

  • 适合做读,插入数据比较频繁的,对修改和删除涉及较少,索引和数据分开存储

  • 支持表级锁

  • 不支持行级锁 不支持事务 不支持外键

• 存储方式二: innoDB 5.6及以上默认存储方式

  • 存储文件个数 : 表结构,表中的数据

  • 适合并发较高,对事物一致性要求较高,行队更适应频繁的删除和修改操作,索引和数据存在一起

  • 支持行级锁和表级锁

  • 支持事务

  • 支持外键

• 存储方式三: MEMORY内存

  • 存储文件个数:表结构

  • 优势 : 增删改查都很快(用于热点新闻)

  • 数据存在内存中,表结构存在硬盘上,查询速度快

  • 劣势 : 重启数据消失,容量有限

10.4.2创建表

表介绍

表就相当于文件，表中的一条记录就相当于文件的一行内容，不同的是，表中的一条记录有对应的标题，称为表的字段

建表语句

• mysql>create table 表名(

  字段名1 类型[(宽度) 约束条件],

  字段名2 类型[(宽度) 约束条件],

  字段名3 类型[(宽度) 约束条件],

  );

• 例一: mysql>create table t1 (id int,name char(4));

• 查看表结构 :

  • mysql>desc 表名 #查看表字段的基础信息

  • mysql>show create table 表名; #能够看到和这张表相关的所有信息

  • mysql>describe 表名;

  • mysql>show create table 表名 \G; #能显示较为整洁(加分号会报错)

  • mysql>show create table 表名 \G #能显示较为整洁

    使用分号或者\G

    证明使用memory存储退出数据库后登录会清空.

10.4.3MySQL中的数据类型

10.4.3.1数值类型

整数

#总结
1.int默认是有符号的
2.它能表示的数字范围不被宽度约束
3.他只能约束数字的显示宽度

#mysql>create table t5 (id1 int unsigned,id2 int);

小数

# create table t6 (f1 float(5,2),d1 double(5,2));   会四舍五入
# create table t7 (f1 float,d1 double);
# create table t8 (d1 decimal,d2 decimal(25,20));

非常准确,能表示小数点后30位,后面不指定位数时默认保留整数

10.4.3.2日期和时间

# 类型
    # year 年            历史事件
    # date 年月日  
    # time 时分秒       运动会
    # datetime(timestamp) 年月日时分秒   交易时间,上班打卡时间

# create table t9(
# y year,d date,
# dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
# ts timestamp);

写入当前时间

10.4.3.3字符串类型

• char 定长的单位

  • 存储时 :alex -----> alex ,

• varchar 变长的单位

  • 存储时:alex ------> alex4

• 哪一种存储方式好?

  • vachar :节省空间,存储效率相对低

  • char :浪费空间,存储效率相对高,长度变化小的

  #手机号码,身份证号,用户名,密码   char  *****
  #评论,微博,说说,微信状态        varchar
  
  create table t11 (name1 char(5),name2 varchar(5));
  

10.4.3.4ENUM和SET类型

#ENUM  枚举

create table t12(
name char(12),
gender ENUM('male','female'),
hobby set('抽烟','喝酒','烫头','洗脚')    #自动去重
);

10.4.4约束

unsigned 设置某一个数字无符号

not null 某一个字段不能为空

defalut 给某个字段设置默认值

• 我们约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入，这样会给我们的操作带来新的负担，于是就出现了默认值的概念。

  只向id1字段添加值，会发现id2字段会使用默认值填充

  单独向id2中添加数值时,id1会默认添加0

  向id1，id2中分别填充数据，id2的填充数据会覆盖默认值

  #not null不生效：
      不支持对not null字段插入null值
      不支持对自增长字段插入”值
      不支持text字段有默认值
  
  #直接在mysql中生效(重启失效):
  mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
  
  #配置文件添加(永久失效)：
  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  设置严格模式：
      不支持对not null字段插入null值
      不支持对自增长字段插入”值
      不支持text字段有默认值
  

unique 设置某一个字段不能重复

• 法一:

• 法二:

  注意上面姓名设置的为unique,所以不能再创建

• 联合唯一

  create table service(
  id int primary key auto_increment,
  name varchar(20),
  host varchar(15) not null,
  port int not null,
  unique(host,port) #联合唯一
  );
  
  mysql> insert into service values
      -> (1,'nginx','192.168.0.10',80),
      -> (2,'haproxy','192.168.0.20',80),
      -> (3,'mysql','192.168.0.30',3306)
      -> ;
  Query OK, 3 rows affected (0.01 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
  ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
  

auto_increment 设置某一个int类型的字段,自动增加

前提 :自增字段必须是数字且必须唯一,自带非空属性

primary key 设置某一字段不能为空且不能重复

• 主键 :约束这个字段 非空(not null)且唯一(unique),的字段,你指定的第一个非空且唯一自动定义诶主键

• 一张表只能设置一个主键且最好设置一个主键

• 联合主键(不常用)

  create table t7(
      id int primary key,     # 你指定的第一个非空且唯一的字段会被定义成主键
      name char(12) not null unique
  )
  

foreign key 外键,涉及到两张表

• 员工表

  id age gender salary hire_date postname postid post_comment post_phone

  外键 foreign key 涉及到两张表

  #员工表
  create table staff(
  id  int primary key auto_increment,
  age int,
  gender  enum('male','female'),
  salary  float(8,2),
  hire_date date,
  post_id int,
  #foreign key(post_id) references post(pid)   外键关联的那张表的字段必须为unique
  )       
  
  #部门表
  create table post(
      pid  int  primary key,
      postname  char(10) not null unique,
      comment   varchar(255),
      phone_num  char(11)
  )
  

• reference

• 级联删除和级联更新

  • cascade方式 :在父表上update/delete记录时，同步update/delete掉子表的匹配记录

  • set null方式 :在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null

  • set null方式 :在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null

  • Set default方式 :父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

  create table staff2(
  id  int primary key auto_increment,
  age int,
  gender  enum('male','female'),
  salary  float(8,2),
  hire_date date,
  post_id int,
  #foreign key(post_id) references post(pid) on update cascade on delete set null
  );
  

10.4.5修改表结构

• 修改表名

  • alter table 表名 rename 新表名;

• 增加表名

  • alter table 表名 add 字段名 数据类型 [完整性约束条件];

• 删除字段

  • alter table 表名 drop 字段名;

• 修改字段

  • alter table 表名 modify 字段名 数据类型 [完整性的约束条件];

    修改已经存在的字段的类型,宽度和约束

  • alter table 表名 change CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];

    修改已经存在的字段的类型,宽度,约束 及字段名字

• 修改字段排列顺序/在增加的时候指定字段位置

  • alter table 表名 modify age int not null after id

  • alter table 表名 modify age int not null first

10.4.6 表与表之间关系

• 两张表中数据的关系

  • 多对一 foreign key 永远是在多的那张表中设置外键

    • 多个学生都是一个班级的

    • 学生表 关联 班级表

    • 学生是多 ,班级是一

       

  • 一对一 foreign key +unique 后出现的的那张表中的数据作为外键(并且约束这个外键唯一)

    • 客户关系表 :手机号码,招生老师,上次联系时间,备注信息

    • 学生表 :姓名,入学日期,缴费日期,结业时间

  • 多对多 产生第三张表,把两个关联关系的字段作为第三张表的外键

    • 书

    • 作者

10.4.7数据的操作

增加 insert

• 方式一: insert into 表名 values (值...);

  所有在这个表中的字段都需要按照顺序被填写在这里

• 方式二: insert into 表名 (字段名1,字段名2.....) values (值......);

  所有在字段位置填写了名字的字段和后面的值必须是一一对应

• 方式三: insert into 表名 (字段名1,字段名2.....) values (值...),(值...),(值...)

  所有在字段位置填写了名字的字段和后面的值必须是一一对应

注 :value单数 一次性写入一行数据

values复数 一次性写入多行数据

# 第一个角度
    # 写入一行内容还是写入多行
    # insert into 表名 values (值....)
    # insert into 表名 values (值....)，(值....)，(值....)
    
# 第二个角度
    # 是把这一行所有的内容都写入
    # insert into 表名 values (值....)
    # 指定字段写入
    # insert into 表名(字段1，字段2) values (值1，值2)

删除 delete

delete from 表 where 条件;

修改 update

update 表 set 字段=新的值 where 条件；

查询 select *****

• 单表查询

  company.employee
      员工id      id                  int             
      姓名        emp_name            varchar
      性别        sex                 enum
      年龄        age                 int
      入职日期     hire_date           date
      岗位        post                varchar
      职位描述     post_comment        varchar
      薪水        salary              double
      办公室       office              int
      部门编号     depart_id           int
  

  #准备操作
  #创建表
  create table employee(
  id int not null unique auto_increment,
  emp_name varchar(20) not null,
  sex enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
  );
  
  
  #查看表结构
  mysql> desc employee;
  +--------------+-----------------------+------+-----+---------+----------------+
  | Field        | Type                  | Null | Key | Default | Extra          |
  +--------------+-----------------------+------+-----+---------+----------------+
  | id           | int(11)               | NO   | PRI | NULL    | auto_increment |
  | emp_name     | varchar(20)           | NO   |     | NULL    |                |
  | sex          | enum('male','female') | NO   |     | male    |                |
  | age          | int(3) unsigned       | NO   |     | 28      |                |
  | hire_date    | date                  | NO   |     | NULL    |                |
  | post         | varchar(50)           | YES  |     | NULL    |                |
  | post_comment | varchar(100)          | YES  |     | NULL    |                |
  | salary       | double(15,2)          | YES  |     | NULL    |                |
  | office       | int(11)               | YES  |     | NULL    |                |
  | depart_id    | int(11)               | YES  |     | NULL    |                |
  +--------------+-----------------------+------+-----+---------+----------------+
  
  #插入记录
  #三个部门：教学，销售，运营
  insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
  ('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
  ('alex','male',78,'20150302','teacher',1000000.31,401,1),
  ('wupeiqi','male',81,'20130305','teacher',8300,401,1),
  ('yuanhao','male',73,'20140701','teacher',3500,401,1),
  ('liwenzhou','male',28,'20121101','teacher',2100,401,1),
  ('jingliyang','female',18,'20110211','teacher',9000,401,1),
  ('jinxin','male',18,'19000301','teacher',30000,401,1),
  ('成龙','male',48,'20101111','teacher',10000,401,1),
  
  ('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
  ('丫丫','female',38,'20101101','sale',2000.35,402,2),
  ('丁丁','female',18,'20110312','sale',1000.37,402,2),
  ('星星','female',18,'20160513','sale',3000.29,402,2),
  ('格格','female',28,'20170127','sale',4000.33,402,2),
  
  ('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
  ('程咬金','male',18,'19970312','operation',20000,403,3),
  ('程咬银','female',18,'20130311','operation',19000,403,3),
  ('程咬铜','male',18,'20150411','operation',18000,403,3),
  ('程咬铁','female',18,'20140512','operation',17000,403,3)
  ;
  
  #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
  
  
  
  准备表和记录
  

  • select 语句

    • select * from 表;

    • select 字段,字段... from 表;

    • select distinct 字段,字段 from 表; #按照查出来的字段去重

    • select 字段*5 from 表 ; # 按照查出来的字段去重

    • select 字段 as 新名字,字段 as 新名字 from 表 # 按照查出来的字段去重

    • select 字段 新名字 from 表 # 按照查出来的字段去重

    #简单查询
        SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
        FROM employee;
    
        SELECT * FROM employee;
    
        SELECT emp_name,salary FROM employee;
    
    #避免重复DISTINCT
        SELECT DISTINCT post FROM employee;    
    
    #通过四则运算查询
        SELECT emp_name, salary*12 FROM employee;
        SELECT emp_name, salary*12 AS Annual_salary FROM employee;
        SELECT emp_name, salary*12 Annual_salary FROM employee;
    
    #定义显示格式
       CONCAT() 函数用于连接字符串
       SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
       FROM employee;
       
       CONCAT_WS() 第一个参数为分隔符
       SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
       FROM employee;
    
       结合CASE语句：
       SELECT
           (
               CASE
               WHEN emp_name = 'jingliyang' THEN
                   emp_name
               WHEN emp_name = 'alex' THEN
                   CONCAT(emp_name,'_BIGSB')
               ELSE
                   concat(emp_name, 'SB')
               END
           ) as new_name
       FROM
           employee;
    

    CONCAT() 函数用于连接字符串

    CONCAT() 函数用于连接字符串
    SELECT CONCAT('姓名: ',emp_name,'  年薪: ', salary*12)  AS Annual_salary 
       FROM employee;
       
     CONCAT_WS() 第一个参数为分隔符(根据':'进行拼接)
       SELECT CONCAT_WS(':',emp_name,salary*12)  AS Annual_salary 
       FROM employee; 
       
       
    结合CASE语句：
       SELECT
           (
               CASE
               WHEN emp_name = 'jingliyang' THEN
                   emp_name
               WHEN emp_name = 'alex' THEN
                   CONCAT(emp_name,'_BIGSB')
               ELSE
                   concat(emp_name, 'SB')
               END
           ) as new_name
       FROM
           employee;
    

where语句

• 比较运算符 >,<,=,>=,<=,!=,<>

• 逻辑运算 条件的拼接

  • 与 and

    select * from employee where sex ='female' and age = 18;

  • 或 or

    select * from employee where salary=1000 or salary=2000;

  • 非 not

• 范围筛选

  • 多选一 字段名 in (值1,值2,值3)

    select * from employee where salary in (2000,3000,4000);

  • 在一个模糊的范围里

    • 在一个数值区间 1w-2w之间 between

      select emp_name from employ where salary between(1000,2000);

    • 字符串的模糊查询 like

      • 通配符 % 匹配任意长度的任意内容

        select * from employee where emp_name like "程%";

      • 通配符 _ 匹配一个长度的任意内容

        select * from employee where emp_name like "程_ _";

    • 正则匹配 regexp 更加细粒度的匹配的时候

      • select * from 表 where 字段 regexp 正则表达式;

        select * from employee where eme_name regexp '^[a-z]{5}';

• 身份运算符null

  要用 is unll / is not null 来判断

  查看岗位描述不为空的员工信息

  select * from employee where post_comment is null;

group by 分组

• select * from 表 group by post

  会把在group by 后面的这个字段,也就是post字段的每一个不同得项都保留下来,并且把值是这一项的所有行归为一组(只会显示这个组中的第一项)

   

聚合

将很多行的同一个字段进行一些统计,最终得到一个结果

• count (字段) 统计这个字段有多少项

  select count(*) from employee;

• sum(字段) 统计这个字段对应的数字的和

  select sum(salary) from employee;

• avg(字段) 统计这个字段对应值的平均值

  select avg(salary) from employee

• min(字段)

• max(字段)

分组聚合

• 求各部门的人数

  select count(*) from employee group by post;

  书写错误的修改

• 求公司里男生和女生人数

  select sex,count(id) from employee group by sex;

  # 求各部门的平均薪资
  	select post,avg(salary) from employee group by post;
  # 求各部门的平均年龄
  	select post,avg(age) from employee group by post;
  # 求各部门年龄最小的
  	select post,min(age) from employee group by post;
  # 求各部门年龄最大的
  	select post,max(age) from employee group by post;
  # 求各部门薪资总和
      select post,sum(age) from employee group by post;
  # 求各部门薪资最高的
  	select name,max(salary) from employee group by post;
  # 求各部门薪资最低的
  	select name,min(salary) from employee group by post;
  #求整个公司薪资最高的
  	select name,max(salary) from employee ;
  # 求最晚入职的
  	select max(hire_date) from employee;
  # 求最早入职的
  	select min(hire_date) from employee;
  # 求各部门最晚入职的
  	select post,max(hire_date) from employee group by post;
  # 求各部门最早入职的
  	select post,min(hire_date) from employee group by post;
  #求男生和女生最早入职的
  	select sex,min(hire_date) from employee group by sex;
  

  注 :

  1.求部门的最高薪资或者求公司的最高薪资都可以通过聚合函数取到,但是要得到对应的人,就必须通过多表查询

  2.总是根据会重复的项来进行分组

  3.分组总是会和聚合函数一起用(最大,最小,平均值)

having 条件

过滤 组

#1.执行顺序 :总是先执行where,再执行group分组,所以相关先分组,之后再根据分组做某些条件筛选的时候where都用不上
#2.只能用having来实现,having中可用聚合函数
#3.having后面的条件要么是select 字段,要么是分组字段

#部门人数大于3的部门
	select post from employee group by post having count(*) > 3;
#平均薪资大于10000的部门
	select post from employee group by post having avg(salary) > 10000;
#   
    select * from employee having age>18

order by 排序

#根据薪资排序
select * from employee order by salary desc;

• order by 某一个字段 asc; 默认是升序asc 从小到大

• order by 某一个字段 desc; 指定降序排列desc 从大到小

• order by 第一个字段 asc,第二个字段 desc; 指定先根据第一个字段升序排列，在第一个字段相同的情况下，再根据第二个字段排列

limit

#取前n个  limit n   ==  limit 0,n
    考试成绩的前三名
    入职时间最晚的前三个
#分页    limit m,n   从m+1开始取n个
员工展示的网页
    18个员工
    每一页展示5个员工
#limit n offset m == limit m,n  从m+1开始取n个

顺序

10.4.8pymysql模块

#增
import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', password="123",database='day40')
cur = conn.cursor()   # 数据库操作符 游标
cur.execute('insert into employee(emp_name,sex,age,hire_date) values ("郭丰","male",40,20190808)')
conn.commit()
conn.close()

#删
import pymysql

conn = pymysql.connect(host='127.0.0.1',user='root',passward='123',database='day40')
cur = conn.cursor()
cur.execute('delete from employee where id =18')
conn.commit()
conn.close()

import pymysql

conn = pymysql.connect(host='127.0.0.1',user='root',passward='123',database='day40')
cur = conn.cursor()  #元组型
#cur = conn.cursor(pymysql.cursors.DictCursor)   #加完之后为字典型
cur.execute('select * from employee where id >10')
#ret = cur.fetchone()
#print(ret)

#ret = cur.fetchmany(5)  #可添加条数
#print(ret)

ret = cur.fetchall()
print(ret)

conn.commit()
conn.close()

#db.rollback()       # 如果发生错误则回滚

sql注入风险

10.4.9 多表查询

连表查询

把两张表连在一起查

#建表
create table department(
id int,
name varchar(20) 
);

create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

#查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+

• 两张表是如何连在一起的?

   

• 内连接 inner join

  两张表条件不匹配的项不会出现在结果中

  select * from emp inner join department on emp.dep_id = department.id;

• 外连接

  • 左外链接 left join

    永远显示全量的左表中的数据

    select * from emp left join department on emp.dep_id = department.id;

  • 右外链接 right join

    永远显示全量的右表中的数据

  • 全外连接

    select * from emp left join department on emp.dep_id = department.id

    union

    select * from emp left join department on emp.dep_id = department.id;

    # 连接的语法
    # select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段;
        # 常用
        # 内链接
        # 左外链接
    

    # 找技术部门的所有人的姓名
        # select * from emp inner join department on emp.dep_id = department.id;
    # +----+-----------+--------+------+--------+------+--------------+
    # | id | name      | sex    | age  | dep_id | id   | name         |
    # +----+-----------+--------+------+--------+------+--------------+
    # |  1 | egon      | male   |   18 |    200 |  200 | 技术         |
    # |  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
    # |  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
    # |  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
    # |  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
    # +----+-----------+--------+------+--------+------+--------------+
    

    select * from emp inner join department on emp.dep_id = department.id where department.name = '技术'
    select emp.name from emp inner join department d on emp.dep_id = d.id where d.name = '技术'
    
    # 找出年龄大于25岁的员工以及员工所在的部门名称
    select emp.name,d.name from emp inner join department as d on emp.dep_id = d.id where age>25;
    
    # 根据age的升序顺序来连表查询emp和department
    select * from emp inner join department as d on emp.dep_id = d.id order by age;
    
    # 优先使用连表查询，因为连表查询的效率高
    
    
    # 练习
    # 查询平均年龄在25岁以上的部门名
    # 部门名 department表
    select name from department where id in (
    select dep_id from emp group by dep_id having avg(age)>25
    );
    # 员工表
    select dep_id,avg(age) from emp group by dep_id;
    select dep_id from emp group by dep_id having avg(age)>25;
    
    # 查看不足1人的部门名(子查询得到的是有人的部门id)
    # 查emp表中有哪些部门id
    select dep_id from emp group by dep_id;
    # 再看department表中
    select * from department where id not in (???)
    select * from department where id not in (select dep_id from emp group by dep_id);
    
    # 查询大于所有人平均年龄的员工名与年龄
    select * from emp where age>(select avg(age) from emp);
    
    # 查询大于部门内平均年龄的员工名、年龄
    select dep_id,avg(age) from emp group by dep_id;
    select * from emp inner join (select dep_id,avg(age) avg_age from emp group by dep_id) as d
    on emp.dep_id = d.dep_id where emp.age > d.avg_age;
    

     

子查询

 

10.5 索引原理

10.5.1 基本概念

#什么是索引?
一个在存储表阶段就有的一个存储结构,能够在查询时加速

#索引的重要性
读写比例 10:1 ,读(查询)的速度至关重要

block 磁盘预读原理

• 读文件时的操作 :for line in f

• linux操作系统中一次性会读取4096个字节(一个block快)

  读硬盘的io操作的时间非常的长,比cpu执行指令的时间长很多,所以尽量的减少IO次数才是读写数据的主要解决的问题

聚集索引和辅助索引

innodb 聚集索引和辅助索引共存

• 聚集索引 ------主键

  数据存储在树结构的叶子结点

• 辅助索引 除了主键外的所有索引

  数据不直接存储在树中

mysam 只有辅助索引无聚集索引

• 辅助索引

  数据不直接存储在树中

10.5.2数据的存储方式

新的数据结构----------树

平衡树 balance tree(b树)

在b树的基础上进行了改良(b+树)

• 1.分直接点和根节点都不在存储实际数据

  • 而是将所有的实际数据都存在于叶子节点中,导致分支和根节点能存储更多的索引信息,降低了树的高度

• 2.在叶子节点之间加入双向的链式结构

  • 方便在查询中的范围条件

#注意:
#mysql当中的所有b+树索引的高度都基本控制在3层
   1.io操作的次数非常稳定
   2.有利于通过范围查询

#什么会影响索引效率     ------树的高度
  1.对哪一列创建索引,尽量选择短的列做索引
  2.对区分度高的列建索引,重复率超过了10%就不适合创建索引

10.5.3创建索引

#创建索引
create index 索引名字 on 表(字段);
#删除索引
drop index 索引名 on 表名字;

索引的种类

• primary key 主键聚集索引 约束的作用 :非空 + 唯一

  联合主键

• unique 自带索引 ,辅助索引 ,约束的作用 :唯一

  联合唯一

• index 辅助索引 ,无约束作用

  联合索引

10.6使用索引

没给id创建索引前,查找效率低

给id创建索引后,查找效率高

以email 作为条件时

• 不加索引,速度慢

  查询的字段不是索引字段,也慢

  查找第2999999条数据

  查找第1条数据

• 加了索引,速度快

id 作为条件时

• 如果不加索引,速度慢

• 加了索引,速度快.

10.6.1索引不生效的原因

# 单列索引
    # 选择一个区分度高的列建立索引，条件中的列不要参与计算，条件的范围尽量小，使用and作为条件的连接符
# 使用or来连接多个条件
    # 在满上上述条件的基础上
    # 对or相关的所有列分别创建索引

• 原因一:索引列不能再条件中参与计算

• 原因二: 要查询的数值范围大(范围越小,速度越快io操作少)

  • !=,>,<,>=,<=

  • between and

  • like

    • 结果的范围大,索引不生效

    • 如果 abc% 索引生效, %abc索引就不生效

• 原因三:一列内容的区分度不高(使用name列测试)

• 原因四 :对两列内容进行条件查询

  先去掉id列索引

  • and and条件两端的内容,优先选一个有索引的,并且树形结构更好的来进行查询

    select * from s1 where id = 1000000 and email = 'eva1000000@oldboy';

    只有两个条件都成立才能完成where条件,先生成范围小的,缩小后面条件的压力

  • or or条件的,不会进行优化,只是根据条件从错到有依次

    select * from s1 where id = 1000000 or email = 'eva1000000@oldboy';

    条件中带有or的要想命中索引列,这些条件中所有的列都是索引列

• 原因五: 联合索引

  • 在联合索引中如果使用了or条件,索引不生效

  • 最左前缀原则 :在联合索引中,条件必须含有在创建索引时的第一个索引列

  • 在整个条件中,从开始出现模糊匹配的那一刻,索引就失效了

   

  为避免其他索引干扰,删掉其他索引

  创建联合索引 create index ind_mix on s1(id,email);

#合并索引
  #对两个字段分别创建索引,由于sql

#执行计划 :explain
  #如果想在执行sql之前就知道sql语句的执行情况,可以使用执行计划
  #语法  explain select id from s1 where id = 1000000;
  #情况一:
    30000000条数据,假设查一条数据要20秒,不想用这么长时间
    使用explain sql --->并不会真正执行sql而是会给你列出一个执行计划
 #情况二:
	有20条数据,但将来可能有2000000条数据
    使用explain sql

10.6.2合并索引和覆盖索引

• 覆盖索引

  如果我们在使用索引作为条件查询,查询完毕后,不需要回表查,覆盖索引

  explain select id from s1 where id = 1000000;
   explain select count(id) from s1 where id > 1000000;
  

• 合并索引

  对两个字段分别创建索引,由于sql的条件让两个索引同时生效,这两个索引就是合并索引

10.7数据备份和事务

10.7.1数据备份

退出mysql后执行

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql   #备份所有表
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql   #备份指定表

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql 

重命名

10.7.2数据恢复

#方法一：
[root@egon backup]# mysql -uroot -p123 < /backup/all.sql

#方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   #关闭二进制日志，只对当前session生效
mysql> source /root/db1.sql

10.2.3事务

# begin;  # 开启事务
# select * from emp where id = 1 for update;  # 查询id值，for update添加行锁；
# update emp set salary=10000 where id = 1; # 完成更新
# commit; # 提交事务

10.8sql注入

# create table userinfo(
# id int primary key auto_increment,
# name char(12) unique not null,
# password char(18) not null
# )
#
# insert into userinfo(name,password) values('alex','alex3714')

# 输入用户
# 输入密码
#
# 用户名和密码到数据库里查询数据
# 如果能查到数据 说明用户名和密码正确
# 如果查不到，说明用户名和密码不对
# username = input('user >>>')
# password = input('passwd >>>')
# sql = "select * from userinfo where name = '%s' and password = '%s'"%(username,password)
# print(sql)


# -- 注释掉--之后的sql语句
# select * from userinfo where name = 'alex' ;-- and password = '792164987034';
# select * from userinfo where name = 219879 or 1=1 ;-- and password = 792164987034;
# select * from userinfo where name = '219879' or 1=1 ;-- and password = '792164987034';

import pymysql

conn = pymysql.connect(host = '127.0.0.1',user = 'root',
                       password = '123',database='day41')
cur = conn.cursor()
username = input('user >>>')
password = input('passwd >>>')
sql = "select * from userinfo where name = %s and password = %s"
cur.execute(sql,(username,password))
print(cur.fetchone())
cur.close()
conn.close()

10.9慢日志

转载于:https://www.cnblogs.com/lilinyuan5474/p/11362179.html