Mysql-数据库

目录

​

MYSQL初识：

  • 数据库优势：

    • 程序稳定性：程序崩溃不会影响数据和服务

    • 数据一致性：所有数据存储在一起

    • 并发：数据库本身支持并发

    • 效率：使用数据库对数据进行的增删改查效率要高

      ​

  • 数据库分类：

    • 关系型数据库(表结构)：

    • 特点相对慢，数据之间关联强

    • mysql，oracle，sqlserver，sqllite，accesse

      ​

    • 非关系型数据库（key:value）：

      • 特点相对快，数据与数据的关联小

      • redis，mongodb ，memcache(淘汰-内存级别，淘汰了)

        ​

    • 区别：

      • 关系型数据库存储效率相对低，数据与数据之间关联紧密
      • 关系型数据库存储效率相对高，数据与数据之间的关系是key:value

      ​

  • Mysql语句分为三种：

    • DBMS：数据库管理系统软件

    • DDL 语句， 数据库定义语言：数据库，表，视图，索引，存储过程，例如create，drop，alter

    • DML 语句，数据库操纵语言：插入、删除、更新、查询数据，insert，delete，update，select

    • DCL 语句， 数据库控制语言：用户的访问权限，grant，revoke

      ​

    • Mysql 默认三个数据库：

      • mysql：保存mysql权限，配置参数，状态信息，主从配置

      • information_schema： 保存着mysql维护其他数据库信息，如库名，表和访问权限等

      • performance_schema：数据库存储引擎，监视数据库运行的资源消耗，资源等待等等

        ​

  • Mysql工作流程：

    1. 登录授权认证安全：
    2. 查询，解析，分析，优化，缓存
    3. 存储过程，触发器，视图
    4. 存储和提取数据
    5. 存储数据，表信息，用户操作日志

​

Mysql 安装：

  • Mysql安装：

    • 路径不能有中文

    • 路径不能有特殊字符

      ​

  • InnoDB创建数据库和表后生成的文件：

    • 数据库：文件夹，表：文件

    • db.opt 字符集

    • .frm 表结构

    • .idb 每表一个表空间（表数据和表索引）

      ​

存储引擎：

  • 数据的存储方式-->存储引擎

    • 使用不同的存储引擎，数据是已不同方法存储的

    • 查看存储引擎：show engines;

      ​

  • Innodb:

  • innodb引擎，支持事务ACID特性，支持数据的完整型，并发条件下保持数据一致性，还可以保证事务的完整提交回滚，保证数据准确性，5.6以上默认innodb

    ​

    • mysql5.6以上，默认的存储方式

    • transactions 事务：保证数据的安全，执行要么成功要么失败，使用的是支付宝支付

    • row-level locking 行级锁：

    • table-level locking 表级锁：

    • foreign keys：外键：

    • 树tree = 加速查询（树形结构（数据+树）+ 表结构）

      • 存储两个文件

    • 并发 修改 删除操作的效率比较高

      ​

  • 事务：

  • ACID（原子性，一致性，隔离性，持久性）事务必须支持这种特性：

    • 原子性：事务操作同时完成，或者同时不完成

    • 一致性：完成的数据结果要保持一致

    • 隔离性：事务之间不能相互影响

    • 持久性：当服务器宕机了，仍然要保证数据的完整

      ​

  • 事务隔离级别：隔离越高性能越低，可以配置，事务通过数据集交互

  • 事务日志：自我完成重做或撤销：

    • redo log 重做日志：写入到日志里面，服务器崩溃了起来重新操作

    • undo log 撤销日志：服务器崩溃了，还会重新执行

      ​

  • Myisam：

  • 如果引用程序是以读操作或者插入操作为主，并且对事务的完整型，并发性要求不高，选择Myisam引擎

    • mysql5.5一下，默认的存储方式

    • 持久化存储

    • table-level locking 表级锁

    • 树tree= 加速查询（树形结构（数据+树）+ 表结构）

      • 存储三个文件

        ​

  • Memory：

    • 将所有的数据保存在内存中，可以提供快速的访问，缺陷是对表大小有限制，不支持事务，异常关闭数据丢失，现在不用Memory引擎，都用非关系型数据库代替

    • 基于hash

      ​

账号创建授权：

  • 删除匿名账号（安全）：

    • drop user ''@localhost;

    • drop user 'root'@'::1';

      ​

  • 设置管理员账号：

    • 1，使用管理员登录：mysql -uroot

    • 2，修改管理员密码：set password = password("123");

    • 3，登录管理员账号：mysql -uroot -p123

      ​

  • 创建数据库账号：

    • 查看创建的账号：select user(); select host,user from mysql.user;
    • 创建账号：
      • create user "haiyang"@"192.168.13.%" identified by "123";
      • flush privileges;

    ​

  • 修改账号密码：

    • update user set password=password("1234") where user='haiyang' and host='192.168.13.%';
    • flush privileges;

    ​

  • grant 账号赋权 (增删改查)：

    • grant all 所有权限

    • 存在账号赋权：grant select on mysql.* to "hai"@"192.168.13.%"

    • 无账号创建，并赋予读取权限：

      • grant select on mysql.* to "hai"@"192.168.13.%" identified by "123";

        ​

    • 查看赋予的权限：

      • show grants for 'haiyang'@'192.168.13.%';

      ​

    • 创建指定的ip地址登录，只能指定这个ip地址才可以登录：

      • mysql -uhaiyang -p123 -h192.168.13.144

    • 账号赋予的权限可以进行回收........

      ​

    • 查看引擎： show engines;

    • 查看字符集：show session variables like '%character%';

    ​

数据-增删改查：

库操作：

  • 创建库：create database ftp;

  • 切换库：use 库名;

  • 删除库：drop database 库名;

  • 查看当前在哪个库下：select database();

    ​

表操作：

  • create 创建表：

    • create table 表名(id int,name char(字符)) engine=myisam;

    ​

  • drop 删除表：

    • drop table 表名;

      ​

  • alter

    • 修改表名：alter table 表名 rename 新表名;

    • 增加新的字段：alter table employee add haha char(10);

    • 修改表字段大小：alter table biao modify name char(20);

    • 修改表字段名称：alter table biao change name name1 char(20)

      id age name varchar(255)
alter table 表名 rename 新表明
alter table 表名 add 新字段 类型(宽度) 约束;
            # add 新字段 类型(宽度) 约束 after id
            # add 新字段 类型(宽度) 约束 first

# alter table 表名 change 旧字段 新字段 类型(宽度) 约束;
            # change name username char(12) not null
            # change name name char(12) not null
            # change name name varchar(255) after id;
           	
# alter table 表名 modify 存在的字段 新类型(新宽度) 新约束;
            # modify name char(12) unique;
            # modify name char(12) unique after id;

      ​

  • show

    • 查看表： show tables;

    • 查看表结构： desc 表名; = describe 表名;

    • 查看表结构的详细信息：show create table userinfo;

      ​

数据操作：

  • 插入数据：insert into 表名value(1,"haiyang"); 插入一条

  • 插入两条：insert into 表名values(1,"haha"),(2,"dudu"); s 插入两条

  • 自增插入：insert into 表名("字段") values("haha"),("dudu");

    ​

  • 修改一个字段：update 表名set name="fafa" where id=2; （必须指定ID）

  • 修改两个字段：update employee set age=20,salary=8000 where id=1;

    ​

  • 查看表数据：select * from 表名;

  • 清空表：delete from 表名;

  • 删除表中ID为1的数据：delete from 表名 where id=1;

    ​

Pymysql-数据插入：

  • 插入语句示例：

    import pymysql

db = pymysql.connect("127.0.0.1", "root", "123", "day32")  #打开mysql数据库：
cursor = db.cursor()  #使用cursor方法获取操作游标：
#插入语句
sql = 'insert into book(shuming,zuozhe,chubanshe,price,date) values(%s,%s,%s,%s,%s);'

#格式化输出，这样可以避免格式错误
try:
    cursor.execute(sql,("学python从开始到放弃","alex","人民大学出版社",50,"2018-7-1")),
    cursor.execute(sql,("学mysql从开始到放弃","egon","机械工业出版社",60,"2018-6-3"))
    db.commit()

except:
   db.rollback()  # 插入语句事务不完成则回滚

db.close()        # 关闭数据库连接


import pymysql
conn = pymysql.Connection(host='127.0.0.1', user='root', password="123", database='kaoshi')
cur = conn.cursor()

with open(r'E:\Pycharm-代码\考试\数据库考试\数据库答案\data\user',mode='r',encoding='utf-8') as f:
    for line in f:
        new_line = line.strip().split(',')
        print(new_line)
        sql = 'insert into user(userid,user_name,password) values(%s,%s,%s)'
        cur.execute(sql, (new_line))
        conn.commit()
cur.close()
conn.close()

    ​

创建表-数值类型：

  • 数值类型：

    设置严格模式：
    不支持对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"

    ​

整数：

  • int： 占用四个字节，正负两亿* 这两个比较常用，都可以存负数

  • tinyint：占用1个字节 ，负128,正127 unsigned转为整数正负和在一起255！！！

    ​

  • 整数示例：

    # 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table t1(id1 int,id2 int(5));
#插入多写数据不会报错
mysql> insert into t1 values(111111,111111);
mysql> select * from t1;
+--------+--------+
| id1    | id2    |
+--------+--------+
|      1 |      1 |
| 111111 | 111111 |
+--------+--------+
# 修改id1字段 给字段添加一个unsigned表示无符号，就是没有负数，只有整数
mysql> alter table t1 modify id1 int unsigned;
mysql> desc t1;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id1   | int(10) unsigned | YES  |     | NULL    |       |
| id2   | int(5)           | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+

#只能插入十个数据，超过会报错
mysql> insert into t1 values (2147483648,2147483647);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------------+------------+
| id1        | id2        |
+------------+------------+
|          1 |          1 |
|     111111 |     111111 |
| 2147483648 | 2147483647 |
+------------+------------+

#数据小时候使用，字节为：
mysql> create table t2(id tinyint);   #默认创建最大插入127最大数量

#添加unsigned最大可以插入为255，因为变成整数，负数转为整数为255
mysql> alter table t2 modify id tinyint unsigned;

    ​

小数：

  • float：float(255.30) 255整数多少位，5,小数点保留位

  • double：double(255.30) 精度更高，精确位四舍五入

  • decimal：decimal(60,30)

    ​

  • 小数示例：

    # 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位
mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2));

#插入数据被截断，四舍五入
mysql> insert into t2 values (1.234,1.234,1.234);
mysql> insert into t2 values (1.235,1.235,1.235);
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
						
#建立新表，去掉参数约束：
#float小数点默认五位，double无限制，decimal取整
insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555);
mysql> select * from t3;
+---------+--------------------+------+
| id1     | id2                | id3  |
+---------+--------------------+------+
| 1.23556 | 1.2355555555555555 |    1 |
+---------+--------------------+------+

    ​

时间:

  • 日期时间类型：

    • 内置函数now() user() database()

    • datatime : 年月日时间 日志/打卡时间/博客/评论/页面超时时间 *，表示的范围大

    • data: 年月日 员工生日/入职日期/离职时间/开班时间 *

    • time: 8.30 上班，上课时间，竞赛数据 *

    • year: 年份

    • timestamp：由于表示范围问题，用的少了,约束不能为空，默认值是当前时间

      ​

  • 类型示例：

    #创建三个类型：date，time，datatime
mysql> create table t4 (d date,t time,dt datetime);

#插入内置函数now()年月日时间，根据格式写入各自的类型中：
mysql> insert into t4 values(now(),now(),now());
mysql> insert into t4 values(null,null,now());

mysql> select * from t4; #默认可以插入空
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-07-30 | 17:45:34 | 2019-07-30 17:45:34 |
| NULL       | NULL     | NULL                |
| NULL       | NULL     | 2019-07-30 17:48:53 |
+------------+----------+---------------------+

  • year:

    mysql> create table t7(y year);
  mysql> insert into t7 values(2016);
  mysql> select * from t7;
  +------+
  | y    |
  +------+
  | 2016 |
  +------+

  • datatime：

    #datatime 插入的几种方式
mysql> create table t8 (dt datetime);
mysql> insert into t8 values ('2018-9-26 12:20:10');
mysql> insert into t8 values ('2018/9/26 12+20+10');
mysql> insert into t8 values ('20180926122010');
mysql> insert into t8 values (20180926122010);

mysql> select * from t8;
+---------------------+
| dt                  |
+---------------------+
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
+---------------------+

    ​

字符串:

  • char长度范围(0-255)，varchar长度范围(0-65535)

  • char：

    • 定长存储，存储速度更快，读取速度也快，占用空间 *
    • char(12)
    • haiy ---> "haiy........"12的长度，剩下的字节会自动补全

  • varchar：

    • 变长存储，存储速度慢，占用空间小 *

    • varchar(12)

    • haiy ---> "haiy........"5的长度

      ​

  • 时间换空间 list，因为空间大需要一定时间计算

  • 空间换时间 dict 占用空间，直接拿key取值，速度快

  • 使用场景：

    • 手机号码、身份证号码：char
    • 用户名/密码：有一定范围性 char
    • 评论：varchar

  • char varchar示例：按照字符算长度

    #创建varchar char字段
mysql> create table t9 (v varchar(4),c char(4));

#插入数据，查看数据char类型会去掉空格
mysql> insert into t9 values ('da  ','da  ');
mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| da   | da   |
+------+------+
#查询结果计算真实长度
mysql> select length(v),length(c) from t9;
+-----------+-----------+
| length(v) | length(c) |
+-----------+-----------+
|         4 |         2 |
+-----------+-----------+

#超过字符数量进行截断：
mysql> insert into t9 values ('huhaiyang','huhaiyang');
mysql> select * from t9;
+------+------+
| v    | c    |
+------+------+
| da   | da   |
| huha | huha |
+------+------+

    ​

  • enum（枚举类型）:

    • gender 单选题，只能从有限的条件中选择：

      #enum，只能选择单个:
mysql> create table t10 (name char(20),gender enum('haiyang','junli'));

#只能选择一个插入:
mysql> insert into t10 values("baobao","haiyang");
mysql> select * from t10;
+--------+---------+
| name   | gender  |
+--------+---------+
| baobao | haiyang |
+--------+---------+

#多插入报错:
mysql> insert into t10 values("baobao","haiyang","junli");
ERROR 1136 (21S01): Column count doesn't match value count at row 1

  • set 多选题，从有限的条件中选，去重：

    #多选去重
mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车'));

#查看多选内容：
mysql> desc t11;
+-------+------------------------------------------+
| Field | Type                                     |
+-------+------------------------------------------+
| name  | char(20)                                 |
| hobby | set('抽烟','喝酒','烫头','翻车')       |
+-------+------------------------------------------+
#插入数据，从set集合中可以多选择，选择的如果重复可以去重，没有set中选项为空
mysql> insert into t11 values("junli","抽烟,喝酒");

    ​

表约束：

  • unsigned： 无符号的：数字

  • not null： 非空约束，指定某列不能为空；

  • default： 默认值，不是非空也可以设置默认值

  • unique： 唯一约束，id user phone mail，约束不能重复，约束角度：not null + uniqe = primary key

    ​

  • auto_increment： 自动 针对int，自带not null ，前提需要设置unique

  • primary key： 主键约束 ，相当于非空加唯一，一张表只有一个

  • foreign key： 外键约束 前提需要设置主键或unique

    • on delete cascaed 级联删除：
    • on update cascaed 级联更新：

    ​

not null非空：

  • 非空约束，指定某列不能为空；

    mysql> create table t12 (id int not null);  #指定为空
mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

#插入空报错
mysql> insert into t12 values (null);
ERROR 1048 (23000): Column 'id' cannot be null

    ​

  • null+default：

    #创建表字段，插入数据为空时，默认666
mysql> create table t13(id1 int not null,id2 int not null default 666);
mysql> desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
| id2   | int(11) | NO   |     | 666     |       |
+-------+---------+------+-----+---------+-------+

#插入数据，t12字段默认666
mysql> insert into t13 (id1) values (111);
#如有id2有新的数据，覆盖默认数据666
mysql> insert into t13 (id1,id2) values (112,223);

mysql> select * from t13;
+-----+-----+
| id1 | id2 |
+-----+-----+
| 111 | 666 |
| 112 | 223 |

    ​

unique （唯一约束）：

  • 约束不能重复

  • 对与mysql来说，数据与数据之间相等就是重复，null 不能用=判断，对与nuique说，可以插入多个空值

    #方法一：
mysql> create table department1(id int unique,name varchar(20) unique,comment varchar(100));

#方法二：
mysql> create table department2(id int,name varchar(20),comment varchar(100),unique(name));

#测试唯一约束：
mysql> insert into department1 values(1,"IT","python");

mysql> insert into department1 values(1,"IT","python");
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

    ​

  • not null 和unique唯一约束：

    • 如果一张表中没有设置primary key主键，那么第一个设置非空+唯一的字段会被设置成主键

    • 特点：一张表中只能有一个主键

    • 不能为空，不能重复：

      #约束ID不能为空，不能重复，PRI=primary
mysql> create table t14(id int not null unique);
mysql> desc t14;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

      ​

  • unique 联合唯一：

    • 多个字段一起设置唯一约束

      #设置host，port联合唯一后，在插入和这两个字段相同的数据报错
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(4,"dudu","192.168.3.3",88);
mysql> insert into service values(8,"dudu","192.168.3.3",88);
ERROR 1062 (23000): Duplicate entry '192.168.3.3-88' for key 'host'

      ​

auto_increment 自增：

  • 第一 只能操作数据

  • 第二 自带非空属性

  • 第三 只能对unique使用

  • 第四：不受删除影响，内部会记录自增数量，使用truncate table 表名，自增清零

    #设置自增必须设置联合唯一：
mysql> create table t15(id int unique auto_increment,name varchar(5));

#插入数据查看：
mysql> insert into t15(name) values("hai");
mysql> insert into t15(name) values("yang");

#数据自动增长
mysql> select * from t15;
+----+------+
| id | name |
+----+------+
|  1 | hai  |
|  2 | yang |
+----+------+

#也可以自己添加数据：
mysql> insert into t15 values(10,"yang");

#AUTO_INCREMENT=12 每次都会记录：
mysql> show create table t15;
| t15   | CREATE TABLE `t15` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |

    ​

primary key：

  • 主键

  • 约束的角度上：not null + unqiue

  • 一张表只能有一个主键

    #创建一个主键
mysql> create table t16(id int primary key auto_increment,name char(14));

mysql> insert into t16(name) values("haiyang");
mysql> select * from t16;
+----+---------+
| id | name    |
+----+---------+
|  1 | haiyang |
|  2 | haiyang |
+----+---------+

  • 联合主键：

    mysql> create table t17(family_name char(4),name char(12),primary key(family_name,name));

    ​

foreign key 外键：

  • 创建表的类型必须是innodb存储引擎：

  • 外键关联，被关联表必须要设置主键或者至少是unique唯一约束：

    #部门表，员工表相互关联
#先创建部门表被关联表必须要设置主键，在创建员工表关联
mysql> create table departments (dep_id int(4) primary key,dep_name varchar(11));

#在创建员工表进行关联：
mysql> create table staff_info (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references departments(dep_id));

foreign key 关联字段，references departments(dep_id))被关联字段（部门表主键）

    ​

  • 外键操作：

    #创建部门表被关联表
mysql> create table department(id int primary key,name varchar(10) not null);

#创建员工表进行关联，联级更新：on update cascade 联级删除：on delete cascade
create table employ(id int primary key,name varchar(10) not null,dpt_id int,foreign key(dpt_id) references department(id) on update cascade on delete cascade);

#部门表-父表插入数据
mysql> insert into department values(1,"技术部"),(2,"销售部"),(3,"人力资源部");

#员工表-插入数据
insert into employ values
(1,'yuan',1),
(2,'nezha',2),
(3,'egon',2),
(4,'alex',2),
(5,'wusir',3),
(6,'李沁洋',3),
(7,'皮卡丘',3),
(8,'程咬金',3),
(9,'程咬银',3);

#删除附表中的数据，子表也会跟着删除
mysql> delete from department where id=2;
mysql> select * from department;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | 技术部          |
|  3 | 人力资源部      |
+----+-----------------+
mysql> select * from employ;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      3 |
|  6 | 李沁洋    |      3 |
|  7 | 皮卡丘    |      3 |
|  8 | 程咬金    |      3 |
|  9 | 程咬银    |      3 |
+----+-----------+--------+

#更新父表department，子表employee中对应的记录跟着改：
mysql> update department set id=2 where id =3;
mysql> select * from employ;
+----+-----------+--------+
| id | name      | dpt_id |
+----+-----------+--------+
|  1 | yuan      |      1 |
|  5 | wusir     |      2 |
|  6 | 李沁洋    |      2 |
|  7 | 皮卡丘    |      2 |
|  8 | 程咬金    |      2 |
|  9 | 程咬银    |      2 |
+----+-----------+--------+

​

表之间关系：

  • 表之间关系三种模式：

    分析步骤：
#1、先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）

#2、再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）

#3、总结：
#多对一：
如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表

#多对多
如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

    ​

一对一关系：

  • 两张表：学生表和客户表：一个学生是一个客户

  • 关联方式：foreign key+unique

    #创建客户表
create table customer(
    id int primary key auto_increment,
    name varchar(20) not null,
    qq varchar(10) not null,
    phone char(16) not null);

#创建学生表：
create table student(
    id int primary key auto_increment,
    class_name varchar(20) not null,
    customer_id int unique,		#该字段必须要唯一
    foreign key(customer_id) references customer(id) #外键字段要保证unique
    on delete cascade on update cascade);
    
#客户表写入数据：
insert into customer(name,qq,phone) values
('海洋','31811231',13811341220),
('俊丽','31811231',13811341220),
('hhh','31811231',13811341220);

mysql> select * from customer;
+----+--------+----------+-------------+
| id | name   | qq       | phone       |
+----+--------+----------+-------------+
|  1 | 海洋   | 31811231 | 13811341220 |
|  2 | 俊丽   | 31811231 | 13811341220 |
|  3 | hhh    | 31811231 | 13811341220 |
+----+--------+----------+-------------+

#一对一增加学生，customer_id设置了唯一约束，不可重复：
mysql> insert into student(class_name,customer_id) values("一班",1),("二班",2),("二班",3);

    ​

一对多：

  • 建立三张表：出版社，作者信息，书

  • 一对多或者一对多：一个出版社可以出版多本书 关联方式：foreign key

    #创建出版社表：设置id为主键
mysql> create table press(id int primary key auto_increment,name varchar(20));

#创建书籍:将press_id关联到出版社表ID，联表删除和更新，如删除一个出版社，出版社对应的数据全部清空
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,  				#一对多字段不约束设置为null这样可以插入多条数据
foreign key(press_id) references press(id)
on delete cascade
on update cascade);

#创建三个出版社：
mysql> insert into press(name) values('北京工业地雷出版社'),('人民音乐不好听出版社'),('知识产权没有用出版社');
mysql> select * from press;
+----+--------------------------------+
| id | name                           |
+----+--------------------------------+
|  1 | 北京工业地雷出版社             |
|  2 | 人民音乐不好听出版社           |
|  3 | 知识产权没有用出版社           |
+----+--------------------------------+


#查看到出版社表每个ID对应一个出版社，这时我们可以根据ID进行插入数据，可以多个数据对应一个出版社ID
mysql> insert into book(name,press_id) values('九阳神功',1),('九阴真经',2),('九阴白骨爪',2),('独孤九剑',3),('降龙十巴掌',2),('葵花宝典',3);

mysql> select * from book;
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功        |        1 |
|  2 | 九阴真经        |        2 |
|  3 | 九阴白骨爪      |        2 |
|  4 | 独孤九剑        |        3 |
|  5 | 降龙十巴掌      |        2 |
|  6 | 葵花宝典        |        3 |
+----+-----------------+----------+

    ​

多对多：

  • 三张表：出版社，作者信息，书

  • 关联方式：foreign key+一张新的表

  • 多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多

    #创建作者表：
mysql> create table author(id int primary key auto_increment,name varchar(20));

#创建一张新表存放作者表和书表关联关系，如要查询数据，直接查新建的这个表：
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);

#插入四个作者：
mysql> insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');

mysql> select * from author;  #查看作者id
+----+---------+
| id | name    |
+----+---------+
|  1 | egon    |
|  2 | alex    |
|  3 | yuanhao |
|  4 | wpq     |
+----+---------+
	

mysql> select * from book;   #查看书籍id
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功        |        1 |
|  2 | 九阴真经        |        2 |
|  3 | 九阴白骨爪      |        2 |
|  4 | 独孤九剑        |        3 |
|  5 | 降龙十巴掌      |        2 |
|  6 | 葵花宝典        |        3 |
+----+-----------------+----------+

#根据两者关系，插入数据：
mysql> insert into author2book(author_id,book_id) values(1,1),(1,2),(3,6),(4,1),(4,5);

    ​

单表查询：

  • 简单查询：

    #根据字段查看数据
mysql> select emp_name,salary from employee;

  • distinct去重：联合去重

    mysql> select distinct post from employee;

  • 通过四则运算查询：

    mysql> select emp_name, salary*12 from employee;
mysql> select emp_name, salary*12 as Annaul from employee; #计算的字段修改成Annual

    ​

  • 定义显示格式：case when 筛选列，条件判断：

    #concat  函数用于拼接字符串：
mysql> select concat('姓名: ',emp_name,'  年薪: ', salary*12)  as Annual_salary from employee;
mysql> select concat("<姓名：",emp_name,">","<薪资：",salary,">") from employee;

#concat_ws  ":"第一个参数为分隔符：
mysql> select concat_WS(': ',emp_name,salary*12)  as Annual_salary from employee;

#case when 从头到尾，条件判断，成功匹配结果concat拼接：
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条件约束：

  • 范围查询：

    • <> >= = != between and or not in

      #单条件查询 = ：
mysql> select emp_name from employee where post="sale";
mysql> select emp_name,age from employee where post="teacher";#查看所有老师年龄

#多条件查询 = and：
mysql> select emp_name from employee where post="sale" and salary > 1000;
#查看所有老师年龄并且大于30的：
mysql> select emp_name,age from employee where post="teacher" and age > 30;


#between and 关键字查询 一万到两万范围条件查询：
mysql> select emp_name from employee where salary between 10000 and 20000;
mysql> select emp_name from employee where salary not between 10000 and 20000;


#关键字is 判断某个字段是否为空，不能用等号，这种判断使用is
mysql> select emp_name,post_comment from employee where post_comment is null;


#关键字in：判断元祖里面数据是否在salary中
mysql> select emp_name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000;  #这种方法比较麻烦

mysql> select emp_name,salary from employee where salary in(3000,3500,9000);
mysql> select emp_name,salary from employee where salary not in(3000,3500,9000);

      ​

  • like 模糊查询：

    • like：

      • 通配符% ：一个百分号代表任意长度的任意字符
        • “a%” 开头
        • "%ing" 结尾
        • "%a%" 包含
      • _
        • "a_" 一个下划线代表一个任意字符

    • regexp：正则匹配

      • “^a”

      • "\d+"

        ​

    • 模糊查询示例：

      #匹配以什么开头：
mysql> select * from employee where emp_name like "eg%";

#匹配中间字符：
mysql> select * from employee where emp_name like "%咬%";

#一个下划线代表一个字符：
mysql> select * from employee where emp_name like "__金";

#匹配老师字段并且以jin开头
select emp_name,age from employee where post="teacher" and emp_name like"jin%";

分组：

  • group by

  • 根据某个重复率比较高的字段进行的，这个字段有多少种可能就分成多少个组

    • 根据性别分组:男的一组，女的一组
    • 根据部门分区:销售一组，教学一组
    • 去重，一旦分组就不能对具体某一条数据进行操作了
    • group_concat：了解，只用来做最终的显示，不能作为中间结果操作其他数据

  • 分组示例：

    #将post分组，在使用count查看，组内有多少成员
mysql> select post,count(id) from employee group by post;
mysql> select post,count(id) from employee group by post;
+-----------------------------------------+-----------+
| post                                    | count(id) |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使                  |         1 |
+-----------------------------------------+-----------+

    ​

聚合函数：

  • count：计数，每个组对应几条数据

  • max min：求最大最小值

  • avg： 求平均值

  • sum：就和值

  • 聚合函数示例：

    #查看表有多少列
mysql> select count(*) from employee;

#查看某个字段=1有多少个
mysql> SELECT COUNT(*) FROM employee WHERE depart_id=1;

#max 取表中最大工资，mix取小
mysql> select max(salary) from employee;

#平均值
mysql> select avg(salary) from employee;
#ID等于三的平均值
mysql> select avg(salary) from employee where depart_id=3;

    ​

  • 分组+聚合：

    #对男女分组，在查看男女分组的数量
mysql> select sex,count(id) from employee group by sex;

#查看post分组成员平均工资
mysql> select post,avg(salary) from employee group by post;

#查看男女分组平均工资
mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex    | avg(salary)   |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |
+--------+---------------+

    ​

having 筛选：

  • where：筛选行

  • having：筛选组

    执行优先级从高到低：where > group by > having >order by > limit
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使       用聚合函数

    ​

  • 过滤示例：

    #查询大于十万的员工
mysql> select * from employee where salary > 100000;

#分组数量大于三的：
mysql> select post,count(id) from employee group by post having count(id) > 3;
+-----------+-----------+
| post      | count(id) |
+-----------+-----------+
| operation |         5 |
| sale      |         5 |
| teacher   |         7 |
+-----------+-----------+

mysql> select post,group_concat(emp_name),count(id) from employee group by post having count(id) < 2;

#查出各岗位平均工资大约1000切小于20000的岗位名和平均工资
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
+-----------+--------------+
| post      | avg(salary)  |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+

    ​

order by 排序：

  • 按单列排序：

    #默认正序排，从小到大：
mysql> select * from employee order by salary;
mysql> select * from employee order by salary ASC;  #两条命令相同
+------------+
| salary     |
+------------+
|    1000.37 |
|    2000.35 |
|    2100.00 |
|    3000.13 |

#降序从大到小排：
mysql> select * from employee order by salary desc;
+------------+
| salary     |
+------------+
| 1000000.31 |
|   30000.00 |
|   20000.00 |

    ​

  • 按多列排序：

    #队列排序逗号分开：
mysql> select * from employee order by age asc,hire_date desc;

#查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列：
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;

    ​

limit 限制查询的记录数：

  • 示例：

    #排序查询，结果只显示六个
mysql> select * from employee order by salary desc limit 6;

#从五开始，第一条数据为第六条
mysql> select * from employee order by salary desc limit 5,6;

多表查询：

  • 交叉查询（了解）：

    • 先计算两张表的笛卡尔积，在根据用户给出的条件进行筛选

    • select * from department,employee where dep_id = department.id;

      ​

内外连接：

  • inner join 内连接：

    • 内连接只显示两张表中相互匹配的项，其他不匹配的不显示：

      #表的先后顺序，跟查询显示顺序相同
mysql> select * from employee e inner join department d on e.dep_id=d.id;

mysql> select * from employee e inner join department d on e.dep_id = d.id order by e.age;

    ​

  • 外连接：

    • left 左外连接：

    • 不管左表中是否匹配上都会显示所有内容：

      mysql> select * from employee e left join department d on e.dep_id = d.id;

#右连接其实只要把表替换一下位置就可以
mysql> select * from department d left join  employee e on d.id = e.dep_id;

      ​

    • right 右外连接：

    • 不管右表中是否匹配上都会显示所有内容：

      mysql> select * from department d right join  employee e on d.id = e.dep_id;

      ​

  • 全外连接：

    • union 上下联表

    • 左右表中不匹配的都会显示：

      mysql> select * from employee e left join department d on e.dep_id = d.id union select * from department d left join  employee e on d.id = e.dep_id;

      ​

子查询：

  • 子查询是将一个查询语句嵌套在另一个查询语句中（几乎哪里都可以放）

  • 内层查询语句的查询结果，可以为外层查询语句提供查询条件

  • 连表的效率比子查询的效率要高

    ​

  • 语句中出现两个select语句就是子查询：

    #查询大于所有人平均年龄的员工名与年龄，一个表查询两次
mysql> select name from employee where age > (select avg(age) from employee);

#查询平均年龄在25岁以上的部门名
mysql> select name from department where id in(select dep_id from employee group by dep_id having avg(age) > 25);

#查询技术部员工姓名：
mysql> select name from employee where dep_id in(select id from department where name = "技术");

#查看不足1人的部门名(子查询得到的是有人的部门id)
mysql> select name from department where id not in (select distinct dep_id from employee);

#查询大于部门内平均年龄的员工名、年龄 一个表内查询内连接，后在条件判断
mysql> select * from employee e inner join (select dep_id,avg(age) avg_age from employee group by dep_id) t on e.dep_id = t.dep_id where e.age > t.avg_age;

#查询每个部门最新入职的那位员工
#t2表里面取的是分组名称和最早入职时间，在内连接，post匹配相同，在where筛选相同时间
mysql> select * from emp t1 inner join (select post,max(hire_date) max_date from emp group by post) as t2 on t1.post=t2.post where t1.hire_date = t2.max_date;

#简单方法：
mysql> select e.name,e.hire_date from (select * from emp order by hire_date desc) as e group by e.post;

    ​

索引原理：

  • 是存储引擎用于快速找到记录的一种数据结构 （数据结构 b+树）

  • 索引的主要目的就是提高查询效率，就跟我们读书用的目录一样，通过目录在找到章，在找到页数，而数据库是将数据分成不同的树状图

    ​

  • 磁盘IO与预读：

    • 磁盘IO是非常昂贵的操作，计算机操作系统做了一些优化，当发生一次IO时，不光把当前磁盘地址的数据读取，相邻的数据也会读取到内存缓存区中，当计算机再次访问，数据会很快被访问到

      ​

  • 树：

    • 数他是一个有限节点组成，它具有层次关系的集合，一个根节点，每个根节点下有两个父节点，每个父节点下有一个子节点，树的高度为3层

      ​

  • b+树：

    • b+树的数据结构，他的作用是当你你每次查找数据时，他可以将磁盘IO数控制在一个很小的数量级，从而提高查找速度，分为辅助索引和聚集索引

    • 数据只存储在叶子节点

    • 在字典节点之间加入双向地址连接，更方便在子节点之间进行数据的读

      ​

  • innodb

    • 聚集索引：

      • 聚集索引就是把每张表的主键构建一颗B+树，同时叶子节点存放为整张表的行记录数据，也叫数据页，数据也成为索引的一部分，每个数据页通过一个双向链表来进行链接，聚合索引能询快速查询到数据
      • 聚合索引好处：
        • 1，聚合索引是双向链表，可以快速找到最后一个数据（双向链表：从头或尾节点都可遍历）
        • 2，聚合索引查找数据，先通过叶子节点上层中间节点得到范围，之后再读取数据页

      ​

    • 辅助索引：

      • 除了主键之外所有的索引都是辅助索引
      • 叶子节点不直接指向数据页，每张表可以有多个非聚集索引，但需要更多磁盘和内容
      • 多个辅助索引引影响insert和update速度

      ​

    • 回表：只查询一个索引，并不能解决查询中的问题，还需要到具体的表中去获取整行数据

    ​

  • myisam索引：

    • 辅助索引，除了主键之外，所有的索引都是辅助索引

      ​

索引的种类四个：

  • primary key 创建自带索引效果 非空 + 唯一 + 聚集索引

  • unique 唯一约束的创建也自带索引效果 唯一 + 辅助索引

  • index 普通索引 辅助索引

  • 联合索引（联合主键，联合唯一，联合普通索引）

    ​

索引-create：

  • 创建了索引之后的效率大幅度提高

  • 文件所占的硬盘资源也大幅度提高

  • 数据库查询时间：加上网络和返回时间一般为为0.3秒

  • 创建索引：

    • mysql> create index ind_name on s1(id); 创建树形结构

  • 删除索引：

    • mysql> drop index ind_name on biao;

  • 查看索引：

    • show create table s1;

    ​

  • 索引的优缺点：

    • 优点：查找速度快

    • 缺点：浪费空间，拖慢写的速度

联合索引：

  • 联合索引是指对表上多个列合起来做一个索引：

  • 创建：create index ind_mix on s1(id,email);

    • where a= xx and b=xxx

    • a/b分别创建了索引，正常情况下只能命中一个（解决：对ab创建联合索引）

    • 在多个条件相连的情况下，使用联合索引速度高于单字段索引

      ​

索引的使用规则：

  • 正确使用索引:

    • 1，所查询的列不是创建索引的列

    • 2，使用between或者>查询数据的范围不可过大，也不能有效利用索引（between and > < != not in ）

    • 3，like 如果把% 放在最前面也不能命中索引

    • 4，如果创建索引的列内容重复率高也不能有效利用索引（重复率不超过10%的列比较适合做索引）

    • 5，查询条件中不能带运算或者函数，必须是字段 = “值“，where条件后面等号前面不能为运算符

    • 6，多条件的情况：

      • and 只要有一个条件列是索引，就可以命中（区分两边一个分度高的字段进行查询）

      • or 要所有条件列都是索引才能命中索引（从左至右）

        ​

    • 7，联合索引规则：

      • 1.创建索引的顺序id email，其中一个字段查询开始出现了范围过大，索引效果失效

      • 2.联合索引在使用的时候遵循最左前缀原则 s1(abcd),联合索引带着a就可以命中

      • 3.联合索引中也是只能使用and能生效，使用or失效

        ​

mysql explain ：

  • 查看是否索引成功

  • explain select count(*) from s1;

    ​

  • 覆盖索引：从辅助索引中就可以得到查询记录，不需要查询聚集索引中的记录

  • 索引合并：两个索引临时合并一起使用，不常用

    ​

mysql慢日志：

​

mysql 导入导出：

  • 备份单表：

    #语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.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

  • 数据恢复：

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

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

    ​

七表联查速度慢怎么办：

  • 表结构的角度：

    • 尽量用固定长度的数据类型代替可变长度类型
    • 把固定长度的字段放在前面

  • 数据的角度：

    • 如果表中的数据越多，查询效率越慢：
      • 列多：垂直分表 （列长了）
      • 行多：水平分表 （数据多了）

  • 从sql的角度：

    • 尽量把条件写的细致点，where条件要多做筛选
    • 多表尽量连表代替子查询
    • 创建有效的索引，而规避无效的索引，使用联合索引

  • 从配置的角度：

    • 开启慢日志查询，确认具体有问题的sql

  • 从架构的角度：

    • 读写分离，解决读的瓶颈
posted @ 2019-07-29 19:46  海洋1994  阅读(548)  评论(0)  编辑  收藏  举报