数据库

第八章 数据库

数据库的作用

#解决了操作文件的效率和便捷问题
#解决了多个服务同时使用数据的一致性问题
#解决了安全的问题
#解决了并发问题

数据库的分类

#关系型数据库:mysql,oracle,sqlserver,sqllite,accesse
#非关系型数据库(存储数据快):redis,mongodb,memcache(内存级别)

MYSQL数据库

MYSQL操作语言分类

1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

Mysql存储引擎(了解)

  • 存储引擎的概念

    1:存储引擎的概念是Mysql才里才有的,不是所有的关系型数据库都有存储引擎这个概念
    2:存储引擎就是在如何存储数据、提取数据、更新数据等技术方法的实现上，底层的实现方式不同，那么就会呈现出不同存储引擎有着一些自己独有的特点和功能，对应着不同的存取机制。
    3:其实MySQL支持多种存储引擎，每种引擎有着一些自己独特的功能，用户在使用的时候，可以根据自己的业务场景来使用不同的存储引擎，其中MySQL最常用的存储引擎为：MyISAM和InnoDB。

  • Mysql工作流程

     

  • InnoDB引擎

    InnoDB引擎
            介绍：InnoDB引擎是MySQL数据库的另一个重要的存储引擎，正称为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。和其他的存储引擎相比，InnoDB引擎的优点是支持兼容ACID的事务(类似于PostGreSQL)，以及参数完整性(即对外键的支持)。Oracle公司与2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行，也允许其他想将InnoDB结合到商业软件的团体获得授权。
    ​
    InnoDB引擎特点：
            1.支持事务：支持4个事务隔离界别，支持多版本读。
                四大特点:原子性,隔离性,持久性,一致性*****
            2.行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
            3.读写阻塞与事务隔离级别相关(有多个级别，这就不介绍啦~)。
            4.具体非常高效的缓存特性：能缓存索引，也能缓存数据。
            5.整个表和主键与Cluster方式存储，组成一颗平衡树。(了解)
            6.所有SecondaryIndex都会保存主键信息。(了解)
            7.支持分区，表空间，类似oracle数据库。
            8.支持外键约束，不支持全文索引(5.5之前)，以后的都支持了。
            9.和MyISAM引擎比较，InnoDB对硬件资源要求还是比较高的。
            
            小结：三个重要功能：Supports transactions，row-level locking，and foreign keys

  • MyISam引擎

    MyISAM引擎调优精要
            1.设置合适的索引(缓存机制)(where、join后面的列建立索引，重复值比较少的建索引等)
            2.调整读写优先级，根据实际需求确保重要操作更优先执行，读写的时候可以通过参数设置优先级。
            3.启用延迟插入改善大批量写入性能(降低写入频率，尽可能多条数据一次性写入)。
            4.尽量顺序操作让insert数据都写入到尾部，较少阻塞。
            5.分解大的操作，降低单个操作的阻塞时间，就像操作系统控制cpu分片一样。
            6.降低并发数(减少对MySQL访问)，某些高并发场景通过应用进行排队队列机制Q队列。
            7.对于相对静态(更改不频繁)的数据库数据，充分利用Query Cache(可以通过配置文件配置)或memcached缓存服务可以极大的提高访问频率。
            8.MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问。
            9.可以把主从同步的主库使用innodb，从库使用MyISAM引擎。主库写，从库读可以(不推荐，有些麻烦的地方，市场上有人这么用)

  • 区别和特点

     

MYSQL数据库安装配置文件

#在数据库安装目录下创建一个my.ini文件,写入下面的配置,然后重启服务端(注意保存,复制pycharm上修改文件路径即可)**********
​
[client]
#设置mysql所有客户端默认字符集
default-character-set=utf8
​
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
user = 'root'
password = 'root'
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:mysoftwaremysql-5.6.45-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:mysoftwaremysql-5.6.45-winx64data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#设置严格模式
sql_mode=STRICT_TRANS_TABLES

sql_mode设置

  • sql_mode的解释

    sql_mode是个很容易忽视的变量,默认值是空值(5.6版本,5.7版本是严格模式),在这种模式下允许一些非法操作,比如一些非法数据的插入,在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题.

  • sql_mode常用来解决这几种问题

    (1) 通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。
    (2) 通过设置sql model 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql 进行较大的修改。
    (3) 在不同数据库之间进行数据迁移之前，通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中

  • sql_mode的查询和修改方式

    #方式一:
    执行sql语句:    select @@sql_mode可以查看当前会话框的sql_mode模式,
    执行sql语句:    set session sql_mode='STRICT_TRANS_TABLES',就改为严格模式
        此方法在次回话关闭之后,就会失效
    #方式二:
    执行sql语句:    select @@global.sql_mode 查看当前全局的sql_mode设置
    执行sql语句:    set global.sql_mode = 'STRICT_TRANS_TABLES',就回修改尾严格模式
        此方法在mysql服务关闭重启之后失效,只在此次服务中有效
    #方法三:
    再mysql安装目录下,找到配置文件,在mysqld的配置文件下添加:
    sql_mode = 'STRICT_TRANS_TABLES',就能设置成严格模式,此方法需要重启mysql服务,  
        从方法永久生效.当然公司的生产环境上事禁止重启mysql服务的

     

查看mysql所有的编码格式

#查看字符集编码的指令:
    show variables like '%char%';

MYSQL客户端链接服务端的完整指令

#完整版指令
mysql -h 127.0.0.1 -P 3306 -uroot -p(密码)
#平时使用的版本
mysql -uroot -p(密码)

mysql登陆密码忘记后的修改方式

#1:停掉Mysql服务端
net stop mysql
#2:在cmd中路径切换到Mysql安装目录下的bin目录,然后手动启动mysql服务端:
mysqld.exe --skip-grant-tables
#3:服务端启动成功后,重新启动一个新的cmd窗口,链接mysql服务端
#4:修改mysql这个库中的user表中的root用户记录的密码
update user set password = password('123') where user='root';
#5:关掉mysqld服务端:
tasklist | findstr mysqld#找到mysqld的服务
taskkill /F /PID 进程号#关闭mysqld的服务
#6:重新启动mysql服务端(正常启动)

登陆Mysql后修改密码的三种方式

#方法1： 用SET PASSWORD命令 
　　　　首先登录MySQL，使用mysql自带的那个客户端连接上mysql。 
　　　　格式：mysql> set password for 用户名@localhost = password('新密码'); 
　　　　例子：mysql> set password for root@localhost = password('123');
#方法2：用mysqladmin  （因为我们将bin已经添加到环境变量了，这个mysqladmin也在bin目录下，     所以可以直接使用这个mysqladmin功能，使用它来修改密码）
　　　　关于mysqladmin的介绍：是一个执行管理操作的客户端程序。它可以用来检查服务器的配置和      当前状态、创建和删除数据库、修改用户密码等等的功能，虽然mysqladmin的很多功能通过使      用MySQL自带的mysql客户端可以搞定，但是有时候使用mysqladmin操作会比较简单。
　　　　格式：mysqladmin -u用户名 -p旧密码 password 新密码 
　　　　例子：mysqladmin -uroot -p123456 password 123
#方法3：用UPDATE直接编辑那个自动的mysql库中的user表 
　　　　1:首先登录MySQL，连接上mysql服务端。 
　　　　2:mysql> use mysql;   use mysql的意思是切换到mysql这个库，这个库是所有的用户表       和权限相关的表都在这个库里面，我们进入到这个库才能修改这个库里面的表。
　　　　3:mysql> update user set password=password('123') where user='root' and         host='localhost';   #其中password=password('123') 前面的password是变量，后面       的password是mysql提供的给密码加密用的，我们最好不要明文的存密码，对吧，其中user是       一个表，存着所有的mysql用户的信息。
​
　　　　4:mysql> flush privileges;  #刷新权限，让其生效，否则不生效，修改不成功

MYSQL账号操作(了解)

mysql> select user();  #查看当前用户
mysql> exit     # 也可以用q quit退出
​
# 默认用户登陆之后并没有实际操作的权限
# 需要使用管理员root用户登陆
$ mysql -uroot -p   # mysql5.6默认是没有密码的
#遇到password直接按回车键
mysql> set password = password('root'); # 给当前账户设置密码
​
# 创建账号(IDENTIFIED BY '123',指定密码)
create user 'eva'@'192.168.10.%' IDENTIFIED BY '123';# 指示该网段可以使用eva登陆
create user '田彩'@'192.168.10.5' IDENTIFIED BY '123';   # 指示ip和用户名连接
create user 'eva'@'%' IDENTIFIED BY '123';  #指示所有机器都可以通过eva账户连接  
drop user 'eva'@'192.168.1.1'     #删除用户
rename user '用户名'%'IP地址' to '新用户名'%'IP地址';#修改用户名
set password for '用户名'%'IP地址'=password('新密码');#修改用户密码
 
# 远程登陆(使用创建的账号,在别人的电脑)
mysql -uroot -p123 -h 192.168.10.3;
mysql -h 192.168.10.3 -P 3306 -uroot -p123;
​
# 给账号授权
grant all privileges on *.* to 'eva'@'%'; #给eva用户所有的权限(privileges可以不写)
grant select,insert on 数据库.表名 to '田彩'@'%';#给用户某个库中某个表的查询,增加权限
grant all privileges  on db1.* to "chao"@'%';#chao用户对db1数据库中的文件执行任何操作
grant all privileges  on *.*  to "chao"@'%';#chao用户对所有数据库中文件有任何操作
show grants for 'eva'@'192.168.10.5';#查看某个用户的权限
flush privileges;    # 刷新使授权立即生效
​
#取消权限
revoke all on db.t1 from 'cai'@'%';#取消用户cai对t1表的所有操作
revoke all on db.* from 'cai'@'%';#取消用户彩对db库的所有权限
revoke all on *.* from 'cai'@'%'#取消用户彩对所有数据库所有表的权限
​
# 创建账号并授权(*代表所有)
mysql> grant all on 数据库.表名 to 'eva'@'%' identified by '123'
mysql> grant select on *.* to 'eva'@'%' identified by '123'

mysql数据库库的操作

'''库操作'''
show databases;     #查询所有的数据库
create database student;    #创建一个student的库
create database db1 charset utf8,   #创建一个db1的库',并指定编码
use student;    #进去student这个库,(然后才能操作表)
drop database student;  #删除student这个库(慎用)

mysql数据库的表操作

1:#创建一个叫t1的表,申明引擎为innoDB
    create table t1(id int,name char(16))engine=innodb; 
2:#查看库下面的所有表
    show tables;    
3:#查看t1的表结构(desc全称describe)
    desc t1;    
4:#查看创建表的详细详细(创建语句,编码等)
    show create table t1;   
5:#删除t1这个表
    drop table t1;  
6:#修改表的名字
    alter table t1 rename student;
7:#修改表的数据类型
    alter table t1 modify name char(16);
8:#修改表的字段名和数据类型
    alter table t1 change name name1 char(15);
    alter table t1 change name name1 旧数据类型;
    '''
    change比modify多了一个修改字段名的功能,如果change后面要修改的数据类型使用的还是旧的     数据类型,那么就只修改了字段名,如果写的新字段名和就得字段名一样,就和modify一样只是修改    了数据类型
    '''
9:#给t1这个表增加一个字段
    alter table t1 add name char(10) first;
    alter table t1 add age int unsigned ,add sex enum('男','女')default '男';
    alter table t1 add salary float(7,2) not null after name;
    '''
    添加字段时,可以使用逗号分割一次添加多个字段,first是表示将新添加的字段放到第一个字段的    位置去(类似于在表第一个字段前面插入一个字段),after表示的是将新添加的字段放到指定的字    段后面,上面的例子就是把salary这个字段加到name的后面,有了这两个字段就可以将新增加的字    段放到任意一个位置
    '''
10:#给t1这个表中的字段bname添加一个约束条件
    alter table t1 add unique(bname);
11:#把t1这个表中的主键取消
    alter table t1 drop primary key;
12:#把t1这个表中bname字段的unique这个约束条件取消
    alter table t1 drop index 'bname';
13:#删除表中的一个字段
    alter table t1 drop name;
14:#复制表(了解就好)
    create table student select * from service;
    create table student select * from server where 1=2;只复制表结构
    '''
    这句话的意思是从service这个表里查询到的数据直接给了student这个要创建的表,表结构和数    据是一样的,但是不能复制主键,外键和索引,可以使用alter 给他加回来
    '''
15:#删除外键关系
    alter table book drop foreign key book_ibfk_1(外键名称);
16:#创建表完成之后,后添加外键关系
    alter table book add foreign key(pid) references publish(id);
17:#创建外键时指定外键名称
    #创建表时:
    create table t1(id int,pid int,constraint fk_t1_publish foreign key(pid)        references publish(id);)
    #创建表完成之后,后添加外键关系
    alter table book add constraint fk_t1_publish foreign key(pid) references       publish(id);

MYSQL数据简单操作

'''数据操作,简单操作,详细的看下面的数据库操作'''
1:#查看student这个表中的所有数据(*代表所有)一般少用*,因为查询效率低
    select * from student;
2:#往student表中添加两条数据,还可以指定字段添加数据,可以插入一条和多条,用逗号分割
    insert into student values(1,'田彩'),(2,'李杰');
    insert into student(name) values('田彩'),('李杰');
    insert into student(id,name,age)select (id,name,age)from t1;
    '''
    insert 可以同时插入多条数据,也可以指定数据插入,还可以讲查询到数据插入到一张表中,上面
    最后一个示例就是把t1表中查询到的三个字段的数据,插入到student表中对应的字段中,但是这种
    插入数据的方式需要字段和数据类型等意义对应好
    '''
3:#将id为1的name改成田少岗
    update student set name='田少岗' where id=1;
4:#将id为1的name改成田少岗,id改成2可以指定修改多个字段的值,用逗号分割就好
    update student set name='田少岗' id=2 where id=1 
5:#把student这个表的数据清空(慎用),删除后插入数据还是从以前最后一个id的位置开始,
    delete from student;
6:#把student这个表中id为1的数据删掉(如果有自增id，新增的数据，仍然是以删除前的最后一样作	为起始。)
    delete from student where id = 1;
7:#数据量大，删除速度比上一条快，且id直接从零开始,
    truncate table t1;

Mysql支持的数据类型

  • 数值

    1:tinyint(1个字节)小整数型(0,255)(-128,127)tinyint unsigned(指定使用正数,不能有符	号)#*********
2:int(4个字节)	大整数型(0,4294967295)int unsigned(指定使用正数,不能有符号)#******
3:float 单精度浮点数值 (4个字节)float(10,5)10代表这个数据一共几位,5表示小数点后面保留	几位,最大(255,30)一般情况下不指定范围,小数点后面只保留5位,不是很准确,double也是,	  在能保留的最后一位小数上会四舍五入#***********
4:double 双精度 浮点数 (8个字节)
5:decimal(65,30)可以精确到小数点后面30位,不写参数,默认是10位的整数

  • 时间

    1:datetime 年月日时分秒 ******(可以把timestamp的属性写到后面.抄过来,show)
2:date	   年月日   ******
3:time	   时分秒   ***
4:year	
5:timestamp(约束不能为空,默认当前时间,在修改表数据时同时更新时间)
#now()获取当前时间
存储时间时,可以写字符串('2019-8-29 12:01:20').也可以写数字类型(201908291026)但是不能少位数

  • 字符串

    1:char (0-255个字节) 定长存储 设置几个就占几个位置,char(10)输入一个'田彩',也占10位,
    存储速度快,占用空间(默认为一)
2:varchar (0-65535个字节) 变长存储 只存数输入的长度,比如输入'田彩',站的位数是2+1,这个	1是数据的长度,存储速度慢,占用空间小(必须指定长度 )
   # 记住这两个就可以了

  • set和enum

    1:enum enum('男','nv'),单选,只能选择括号里的选项
2:set  set则是多选但是也必须是括号里的内容,

约束

  • 约束的介绍和作用

    1:约束条件和数据类型一样,都是可选参数
2:约束条件的作用是用于数据的完整性,和数据的一致性

  • 主键与常见约束(****)

    1:unsigned 无符号的,只对数字有效
2:not null 非空约束  (是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值)
    #create table t1(name char(10) not null);
3:unique 唯一约束(标识该字段的值是唯一的)
    #create table t1(name char(10) unique);
4:anto_increment 自动增加(标识该字段的值自动增长（整数类型，而且为主键）)
    #create table t1(id int primary key auto_increment); 前面必须是个主键key
5:primary key 主键约束,第一个使用not null和unique的字段就会变成主键
    #create table t1(id int primary key);
6:default 默认值(字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值)
    #create table t1(name char(10) not null default 'xx');

  • 外键 foreign key references

    • foreign key外键介绍

      1:外键就是标明表与表之间的关系
2:表与表之间的关系有三种:一对一.一对多.多对多
3:级联关系
    #严格模式(默认的),外键有强制约束效果,被关联字段不能随意删除和修改
    #外键有强制约束效果,被关联字段删除或者修改,关联他的那么字段数据会随之删除或者	修改
    on update cascade , on delete cascade 级联更新和删除
    #set null模式: 被关联字段删除时,关联他的字段数据会置成null
#级联set null的示例
mysql> create table tt2(id int primary key auto_increment,name char(10));

mysql> create table tt3(id int,pid int,foreign key(pid) references tt2(id) on delete set null);
Query OK, 0 rows affected (1.06 sec)

mysql> desc tt3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| pid   | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into tt2(name) values('xx1'),('xx2');
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into tt3 values(1,1),(2,1);
Query OK, 2 rows affected (0.12 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tt3;
+------+------+
| id   | pid  |
+------+------+
|    1 |    1 |
|    2 |    1 |
+------+------+
2 rows in set (0.00 sec)

mysql> delete from tt2 where id = 1;
Query OK, 1 row affected (0.10 sec)

mysql> select * from tt3;
+------+------+
| id   | pid  |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+
2 rows in set (0.00 sec)

    • 外键的作用

      1:使表的组织结构变得清晰
2:节省空间
3:增强扩展性
4:保证了数据的安全性和逻辑性
===========
约束作用:
    1:先要建立被关联的表,才能建立关联表
    2:再插入数据的时候,要先往被关联表中插入数据,才能往关联表中插入数据
    3:更新或者删除的时候都要考虑关联表和被关联表的关系

    • 一对多的关系

      1:create table dep(id int primary key auto_increment,dep_name char(16) not null);#创建部门表(被关联的表)
2:create table emp(id int primary key auto_increment,name char(10) not null,dep_id int, foreign key(dep_id) references dep(id))#员工表(关联表)
================================
'''
创建表时,先创建被关联的表,因为关联表中外键的字段数据事来自与被关联的表中的字段,否则就会报错,再插入数据时,也是先再被关联的表中创建数据,先在关联表中创建数据的话,外键是没有值得这样也报错,在修改删除数据时,而是先删除关联表的数据,如果先删除被关联表中的数据,就会报错,原因一样,在关联表中,这个数据被关联着,是无法删除的

有一个解决方案是可以在被关联表中添加两个方法,做到被关联表更新和删除的操作的时候,关联表中同步更新和删除的效果,在申明外键的时候,在后面加上两句话
1:on delete cascade 删除同步
2:on update cascade 更新同步
'''

    • 多对多的关系

      1:create table author(id int primary key auto_increment,name char(16) not null);#表一
2:create table blook(id int primary key auto_increment,bname char(16) not null);#表二
3:create table author_blook(id int primary key auto_increment,author_id int,book_id int ,foreign key(author_id) references author(id) on update cascade on delete cascade,foreign key(book_id) references book(id) on update cascade on delete cascade);#中间表
'''
中间表需要指定两个外键,来关联两个表
'''
多对多的关系,可以查看博客:https://www.cnblogs.com/clschao/articles/9968396.html#_label6

    • 一对一的关系

      1:create table customer(id int primary key auto_increment,name char(16) not null,phone char(11) not null unique ,qq char(15) not null unique);#表一
2:create table student(id int primary key auto_increment,name char(10) not null , class_name char(15) not null,cid int unique,foreign key(cid) references customer(id));#表二
'''
一对一的状态下,被关联的字段中的数据只能在关联表中关联一次,所有我们把关联的字段设置成unique 唯一的,这样就不会出现重复的
'''

    • 对于查看表关系的总结

      #查看表关系的步骤:
1:先从左表的角度去找(条件一)
    是否左表的多条记录可以对应右表的一条记录,则证明左表中有一个外键对应右表的一个		字段
2:先从右表的角度去找(条件二)
    是否右表的多条记录可以对应左表的一条记录,则证明右表中有一个外键对应左表的一个		字段
#关系总结:
	1:如果上面的1条件成立.就是左表多对一右表
	2;如果上面的2条件成立,就是右表多对一左表
	3:如果两个条件都成立,就是多对多,需要定义一个三方表,来存放两张表的关系
	4:如果两个条件都不成立,而是左表的一条数据对应右表的一条数据,就是一对一,那么确	 定主从关系,从级表中就回有一个外键指向主表,这个外键要用unique 设置唯一,

数据库查询(单表查询)

  • 关键字执行的优先级(重点)

    重点中的重点：关键字的执行优先级
1:from #找到表
2:where #使用where指定的条件,去表中取出一条条记录
3:group by #将取出的数据进行分组,如果没有指定,则整体作为一组
4:having #将分组的结果按照having指定的条件进行过滤
5:select #指定select查询
6:distinct #去重
7:order by #将查询的结果按照order by指定的字段进行排序
8:limit #限制结果的显示数量

  • select查询操作(筛选列)

    1:#select 查询操作
	'''
	注意所有select 查询后面右加括号的方法,比如,select user()这种带括号的都是Mysql的	 内置方法,select本身就是查询操作,from只是说明从哪查,
	'''
	select * from 表名; #查询表中所有的数据  *代表所有
	select 字段名 from 表名;#查询表中某一个字段的数据
	select 字段名,字段名,字段名 from 表名;#查询表中多个字段的数据
	select database();#查询返回当前操作的数据库
	select user();#查询当前登陆的用户
	select now;#查询返回当前的时间
===========================================================
2:#distinct 去重操作
	'''
	当使用select查询表某些字段数据的时候,这个字段中可能出现多个重复的数据,就可以使用	distinct对查询出来的数据进行去重操作,distinct也可以对多个字段的数据进行联合去重
	'''
	select dictinct 字段名 from 表名;#对查出来的字段数据进行去重
	select distinct 字段名,字段名 from 表名;#对多个字段查询的数据进行联合去重
===========================================================
3:#四则运算
	'''
	使用select 查询某个字段的数据的时候,对数值类型的字段数据,可以进行四则运算,四则运	 算包括加减乘除等操作
	'''
	select 字段名*12 from 表名;#对查询出来的数据乘以12,返回结果,>>字段名要是数值类		型
===========================================================
4:#concat字符串拼接和concat_ws指定分割符进行拼接(自定义显示的格式)
	'''
	concat内置函数可以对查询出来的字段数据进行字符串拼接
	'''
	select concat('姓名:',name) from 表名;#对查询出来的姓名字段的数据前面都加上一	  个字符串姓名,能够友好的显示查询出来的数据的意思,其实就是字符串拼接
	select concat(name,':',salary)as info from 表名;#concat还可以将两个字段的数		据拼接,,产生一个新的字段数据显示出来,as是个这个字段重命名
	'''
	concat_ws()函数也只是字符串拼接,不过concat_ws的凭借方式是类似与python join拼接	    的方式,就是以某个元素对多个字段的数据进行拼接
	'''
	
    select concat_ws('_','姓名:',name,'性别:',sex)as info from 表名;
==========================================================
5:#case end 语句 对查询出来的每一个数据进行加工和显示
	'''
	case语句的作用是,对表中数据进行查询时,对于查询出来的语句进行进一步的加工,并且显示	出来,case end语句有点像python里的if语句,不过sql的case语句需要表明开始和结		束,case代表开始,end代表结束.when代表的就是if条件,else就是其他,当有多个when的时	候,从第二个when开始就代表的类似于elif的意思,,,,,知道了解就好>>>>>>
	'''
select(
	case
    when name='田少岗' concat(name,'逗比')
    when name='田彩' concat(name,'女神')
    else 
    	concat(name,'边玩去')
   	end
)as now_name from 表名;
==========================================================
6:#select的相关总结;
    可以查一个,多个,*所有
    调用函数:now(),database(),concat(),concat_ws()
    可以四则运算
    可以去重 distinct
    可以使用case end 条件判断语句

  • where 筛选行语句

    1:#范围查询
    #< > >= <= !=  <>代表不等于和!=是一个意思 
    select age from 表名 where age <100 and age>20;(多条件查询)
    #between 1 and 10 找寻1到10之间的
    select age from 表名 where between 1 and 100; >>注意包含1和100
    #in (1,2,3,4) 多选一
    select age from 表名 where age in (10,20,30,40);
    #将age=10和age=20的都能取出来
    select age from 表名 where age=10 or age=20;
2:#模糊查询
    #like
    like的表现形式是,'%a'查询以a结尾的,'a%'查询以a开始的,'%a%'查询包含a的
    select name from 表名 where name like '田%';>>>查询出姓田的所有人
    like还有一种形式,'_a','a_','a__'一个划线代表一个字符,%代表的是任意长度
    select name from 表名 where name 'like '田_';>>>查询出以田开头的两个字的姓名
    #regexp 可以使用正则匹配(记一下正则表达式)
3:#is is not 
    is null is not null 一般用来判断是不是空,
4:#逻辑运算
    and or not

  • group by(分组)

    '''group by可以对某个字段的值进行分组,这个字段有多少种值就分多少组,group by还有一个特性就是去重,一旦使用group by对数据分组了,就不能对某一条数据进行操作,永远都是这一组数据'''
group_concat()函数(只用来做最终的显示,不做中间数据的操作)可以显示当前这一组的所有信息,拼在一起显示
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
'''
mysql> set global sql_mode='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。(了解就好)
'''

  • 聚合函数

    1:#count 计数
2:#max  求最大值
3:#avg  求平均值
4:#min  求最小值
5:#sum  求和

  • having 过滤条件

    '''
having是针对一个组做的过滤条件,是放在group by 后面执行的,他的意思和where是一样的
where group 和 having的执行顺序是,where>group by>having,所以having一般都是和having一起用的
'''
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;

  • order by 排序

    '''
对查询出来的数据进行排序,可以升序或者降序(desc),可以使用多个条件一起排序,执行顺序按照从左到右执行排序
'''
SELECT * FROM employee ORDER BY salary; #默认是升序排列
SELECT * FROM employee ORDER BY salary ASC; #升序
SELECT * FROM employee ORDER BY salary DESC; #降序

  • limit 分页

    '''
可以和order by一起使用,先排序再分页取几个,还可以指定删选的范围,limit m,n这个意思是从m+1开始取n条数据,不如limit 0,6,就是从1开始取六条数据,然是limit分页时,只适合做小数据的分页,当数据量过大时,效率就会非常慢,limit还有一种语法是:limit n offset m,这个意思也是从m+1开始,取n条,这个语法知道就行,
'''
#默认初始位置为0,从第一条开始顺序取出三条 
	SELECT * FROM employee ORDER BY salary DESC LIMIT 3;
#从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5;  
##从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5;

多表查询

  • 笛卡尔积查询

    select * from 表一,表二;
'''
上面这个语句的执行是想查出来两张表中所有的数据,但是发现执行结果多出来好多数据,当联表查询的时候,mysql不知道如何连接两孩子那个表的关系,也不知道你想要查询那些数据,mysql就会将两张表中所有可能组合起来的数据,全部组合起来返回给你,当然这样的好处是,帮你把所有可能的组合都做好,方便查询数据,但是效率慢,
'''
select * from emp,dep;#将两张表拼在一起
select * from emp,dep where dep.id = emp.dep_id; #找到两张表中对应的关系记录
select emp.name from dep,emp where dep.id = emp.dep_id and dep.name='技术';
#拿到筛选后的技术部门数据中emp.name的数据

  • inner join on 内连接

    '''
在写sql语句的时候,让我们对语句有一个清晰的区分,增强代码的可读性,联表的操作和查询的操作有个明确的区分,就用到了内连接,左链接,右链接的方式inner join on就是内连接的意思,内连接的方式就一个弊端就是,当两个表的数据中无法通过联表指定的字段对应上的时候,就无法显示这些数据,类似集合中交集的意思,
'''
第一步:连表
	select * from dep inner join emp on dep.id=emp.dep_id;
第二步: 过滤
	select * from dep inner join emp on dep.id=emp.dep_id where dep.name='技术';
第三步:找对应字段数据
	select emp.name from dep inner join emp on dep.id=emp.dep_id where dep.name='技术';

  • left join on 左连接(外连接)

    '''
左连接的意思就是在内连接的基础上增加左边有,右边没有的数据,将左边的表作为主表,右边的表作为辅表,当辅表的数据没办法对应上左表的数据时,就通过null来表示(把左表的所有数据都显示)
'''
select * from dep left join emp on dep.id=emp.dep_id;

  • right join on 右链接(外连接)

    '''
右连接于左连接的用法使一样的,不过右连接是将右表作为主表,左表作为辅表
'''
select * from dep right join emp on dep.id=emp.dep_id;

  • union 全连接

    '''
不管是内连接还是左连接,右连接,都会由一些数据使无法显示出来的,那么就可以用全连接的方式来链接,写法是写一个左连接,一个右链接,中间用union 来连接
'''
select * from dep left join emp on dep.id=emp.dep_id union select * from dep right join emp on dep.id=emp.dep_id;

  • 子查询

    '''
子查询是将一个查询的结果作为另一个查询的条件
'''
select name from emp where dep_id = (select id from dep where name = '技术');

Navicat数据库链接的安装使用

安装使用教程:https://www.cnblogs.com/clschao/articles/10022040.html
掌握：
#1. 测试+链接数据库
#2. 新建库
#3. 新建表，新增字段+类型+约束
#4. 设计表：外键
#5. 新建查询
#6. 备份库/表

#注意：
批量加注释：ctrl+？键
批量去注释：ctrl+shift+？键

PyMysql模块

  • 链接参数

    host='127.0.0.1'	#计算机的IP地址
port=3306 			#数据库的端口号
user='root'			#数据库的用户名
password='root'		#用户密码
database='lucky'	#要连接数据库名
charset='utf8'		#链接数据的编码格式

  • 代码示例

    import pymysql
1：#查询
conn = pymysql.connect(
	host='127.0.0.1', #主机
	port=3306, #端口号
	user='root',#用户名
	password='666', #密码
	database='day43', #需要连接的库
	charset='utf8'#指定编码
)
cursor = conn.cursor()#获取游标,默认获取的是元祖嵌套形式的数据
sql = "select * from dep;" #sql语句
ret = cursor.execute(sql) #ret 受影响的行数
print(cursor.fetchall())  #取出所有的
print(cursor.fetchmany(3))  #取出多条不写参数默认是取一条
print(cursor.fetchone())  #取出单条

cursor.scroll(3,'absolute')  #绝对移动,按照数据最开始位置往下移动3条
cursor.scroll(3,'relative')  #相对移动,按照当前光标位置往下移动3条
'''
cursor 获取游标的时候默认获取的是元祖套元祖的数据类型,可以指定使用字段的方式,来获取字典的数据格式
'''
#字典格式的游标
cursor = conn.cursor(pymysql.cursors.DictCursor)

  • pymysql插入数据

    #插入
conn = pymysql.connect(
	host='127.0.0.1', #主机
	port=3306, #端口号
	user='root',#用户名
	password='666', #密码
	database='day43', #需要连接的库
	charset='utf8'#指定编码
)
cursor = conn.cursor()#获取游标,默认获取的是元祖嵌套形式的数据
sql = "insert into t1 values (3,'xx3',18);" #sql语句
ret = cursor.execute(sql) #ret 受影响的行数
conn.commit()  #增删改操作时,需要进行提交

     

  • sql注入解决方案

    #sql注入:解决方案
	cursor.execute(sql,[参数1,参数2...])
 import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='666',
    database='day43',
    charset='utf8',
)
while 1:
    username = input('请输入用户名:')
    password = input('请输入密码:')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = "select * from userinfo where username=%s and password=%s;"
    #cursor.execute(sql,[uname,pword]) ，其实它本质也是帮你进行了字符串的替换，只不过它会将uname和pword里面的特殊字符给过滤掉。
    ret = cursor.execute(sql,[username,password])
    #ret我们说是得到的行数，如果这个行数不为零，说明用户输入的用户名和密码存在，如果为0说名存在
    if ret:#如果输入的用户名和密码错误，这个结果为0，如果正确，这个结果为1
        print('登录成功')
    else:
        print('账号或者密码错误,请重新输入!!!')

     

  • 获取插入的最后一条数据的自增ID

    import pymysql
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')
cursor=conn.cursor()
sql='insert into userinfo(name,password) values("xxx","123");'
rows=cursor.execute(sql)
print(cursor.lastrowid) #在插入语句后查看
conn.commit()
cursor.close()
conn.close()

Mysql数据库数据备份和恢复

#使用mysqldump实现数据库的备份
mysqldump -uroot -p -B -d 库名 > f:数据库备份练习crm2stru.sql
Enter password: ***
'''
mysqldump也是mysql数据库的一个命令,-u用户名,-p密码 -B指的是在备份数据的时候,备份的数据增加建库的语句和use语句,>号后面指定路径数据备份保存的路径和文件名
'''
#利用mysql命令回复数据
'''
恢复数据首先要把数据库中原来的库删除,然后把备份的数据导入进来,<号表示得就是要导入进来,后面写上备份数据的路径,运行就可以回复数据
'''
mysql -u用户名 -p 密码 库名 < f:数据库备份练习crm2stru.sql

索引

  • 原理

    索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少io次数，加速查询。（其中primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果）

强调：一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据

索引的影响
　　　　　　1、在表中有大量数据的前提下，创建索引速度会很慢
　　　　　　2、在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

　　　　本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
'''
索引的作用主要是用来加速查询的效率,数据存储时,会根据聚集索引存储成一种数据结构,叫做B+树的数据结构(下图),数据都回存储在叶子节点上(图中最下面的一层),除了聚集索引还有普通话索引,普通索引存储的是索引字段这一列的数据和聚集索引(回表操作),存储数据的时候选择索引尽量选择字段数据较小的尾索引字段
'''

  • 索引的数据结构

     

  • 主键索引

    #添加主键索引:
		创建的时候添加:  添加索引的时候要注意,给字段里面数据大小比较小的字段添加,给字段里面的数据区分度高的字段添加(一般添加到id字段上).
		聚集索引的添加方式
		创建的时候添加
		Create table t1(
			id int primary key,
		)#方式一
		Create table t1(
			id int,
			primary key(id)
		)#方式二
		表创建完了之后添加
			Alter table 表名 add primary key(id);#当数据量大的时候,效率会很慢
		删除主键索引:
			Alter table 表名 drop primary key;#当数据量大的时候,效率会很慢

  • 普通索引

    #普通索引:
'''
当查询的数据就是普通索引这一列的时候,普通索引也叫覆盖索引
'''
		创建:
			Create table t1(
				Id int,
				Index index_name(id)
			)
			Alter table s1 add index index_name(id);
			Create index index_name on s1(id);

		删除:
			Alter table s1 drop index u_name;
			DROP INDEX 索引名 ON 表名字;

  • 联合索引

    #联合索引(联合主键联合唯一联合普通索引)	
		Create table t1(
				Id int,
				name char(10),
				Index index_name(id,name)
			)
    '''
	联合索引在申明的时候,是申明几个字段作为索引,再查找的数据的时候,如果也要使用索引来提升查询速度,就要按照索引字段的顺序来查找,比如在查找一条数据的时候,联合索引的字段为name,age,那个在指定where条件的时候,一定更要使用到name字段,否则就无法使用这个索引,还有当使用联合索引的时候,多个字段中,如果出现范围查找的字段,那么在这个范围字段后面的字段再查找的时候联合索引也无法使用		
			'''
    
#explain可以查看查找数据时,扫描了多少条数据,数字越少速度越快
explain select * from student where name='田cai';

     

  • 唯一索引

    #唯一索引:
		Create table t1(
			int unique,
		)

		Create table t1(
			id int,
			unique key uni_name (id)
		)'''unique key 后面是给这个索引取名字,这个名字可以写可以不写,不写的情况下一般以指定的索引字段名命名'''
		表创建好之后添加唯一索引:
		alter table s1 add unique key  u_name(id);
		删除:
		Alter table s1 drop index u_name;

     

事务和锁

#行锁:
	select * from 表名 where id=1 for update;#排他锁,上锁之后,其他用户对这个表的增删改查就回阻塞,等你操作完成之后,其他用户才能操作
    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;#共享锁(其他用户可以查询,不能对数据进行增删改)
#表锁:
	共享读锁：lock table tableName read;
　　 独占写锁：lock table tableName write;
	批量解锁：unlock tables;
#事务:
	begin;或者 start transaction;#开启事务
    commit;提交#提交事务
    rollback;回滚#回滚事务
    https://www.cnblogs.com/clschao/articles/10034539.html#_label4(博客链接)
'''
原子性（Atomicity）：事务是一个原子操作单元。在当时原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。
一致性（Consistent）：事务开始到结束的时间段内，数据都必须保持一致状态。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行。
持久性（Durable）：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。。'''
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/Tian-Cai-1996/p/12245830.html