author: UpDown published: True created: 2022年5月11日14点56分 tags: Done version: Outer

参考：https://blog.updown.world/articles/python/pythonbook7/index.html

介绍

SQL语句

SQL是结构化查询语言，是一种用来操作RDBMS的数据库语言，当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库

  • SQL语句主要分为：
    • DQL：数据查询语言，用于对数据进行查询，如select
    • DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete
    • TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
    • DCL：数据控制语言，进行授权与权限回收，如grant、revoke
    • DDL：数据定义语言，进行数据库、表的管理等，如create、drop
    • CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor
  • 对于web程序员来讲，重点是数据的crud（增删改查），必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其它语言如TPL、DCL、CCL了解即可
  • SQL 是一门特殊的语言,专门用来操作关系数据库
  • 不区分大小写

    安装

    安装服务端

    默认ubuntu20已安装mysql客户端和服务端

    环境：ubuntu 安装命令apt

  1. sudo apt install mysql-server
  2. sudo service mysql start启动服务
  3. ps aux|grep mysql
  4. sudo service mysql stop
  5. sudo service mysql restart

    ps aux、ps -aux、ps -ef之间的区别 mysql 和 mysqld 的区别

配置文件

  • /etc/mysql/mysql.cnf
    • !includedir /etc/mysql/conf.d/
    • !includedir /etc/mysql/mysql.conf.d/mysql.cnf真正配置项 ```java bind-address表示服务器绑定的ip，默认为127.0.0.1

port表示端口，默认为3306

datadir表示数据库目录，默认为/var/lib/mysql

general_log_file表示普通日志，默认为/var/log/mysql/mysql.log

log_error表示错误日志，默认为/var/log/mysql/error.log

  1. <a name="qfOYQ"></a>
  2. #### 安装客户端
  4. - `sudo apt install mysql-client`
  5. <a name="cpdGa"></a>
  6. ### 数据库可视化工具
  7. Navicat
  8. <a name="YNsHG"></a>
  9. ### 数据类型
  11. - 使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间
  12. - 常用数据类型如下：
  13.    - 整数：int，bit
  14.    - 小数：decimal
  15.    - 字符串：varchar,char
  16.    - 日期时间: date, time, datetime
  17.    - 枚举类型(enum)
  18. - 特别说明的类型如下：
  19.    - decimal表示浮点数，如decimal(5,2)表示共存5位数，小数占2位
  20.    - char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '
  21.    - varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'
  22.    - 字符串text表示存储大文本，当字符大于4000时推荐使用
  23.    - 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
  24. > 更全的数据类型可以参考[http://blog.csdn.net/anxpp/article/details/51284106](http://blog.csdn.net/anxpp/article/details/51284106)
  26. <a name="lbnvu"></a>
  27. #### 数值类型(常用)
  28. | **类型** | **字节大小** | **有符号范围(Signed)** | **无符号范围(Unsigned)** |
  29. | --- | --- | --- | --- |
  30. | TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
  31. | SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
  32. | MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
  33. | INT/INTEGER | 4 | -2147483648 ~2147483647 | 0 ~ 4294967295 |
  34. | BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
  36. <a name="oLrED"></a>
  37. #### 字符串
  38. | **类型** | **字节大小** | **示例** |
  39. | --- | --- | --- |
  40. | CHAR | 0-255 | 类型:char(3) 输入 'ab', 实际存储为'ab ', 输入'abcd' 实际存储为 'abc' |
  41. | VARCHAR | 0-255 | 类型:varchar(3) 输 'ab',实际存储为'ab', 输入'abcd',实际存储为'abc' |
  42. | TEXT | 0-65535 | 大文本 |
  44. <a name="ufhdk"></a>
  45. #### 日期时间类型
  46. | **类型** | **字节大小** | **示例** |
  47. | --- | --- | --- |
  48. | DATE | 4 | '2020-01-01' |
  49. | TIME | 3 | '12:29:59' |
  50. | DATETIME | 8 | '2020-01-01 12:29:59' |
  51. | YEAR | 1 | '2017' |
  52. | TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC |
  54. <a name="rPCvE"></a>
  55. ### 约束
  57. - 主键primary key：物理上存储的顺序
  58. - 非空not null：此字段不允许填写空值
  59. - 惟一unique：此字段的值不允许重复
  60. - 默认default：当不填写此值时会使用默认值，如果填写时以填写为准
  61. - 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
  62. - 说明：虽然外键约束可以保证数据的有效性，但是在进行数据的crud（增加、修改、删除、查询）时，都会降低数据库的性能，所以不推荐使用，那么数据的有效性怎么保证呢？答：可以在逻辑层进行控制
  63. - 在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率
  64. <a name="Vohk3"></a>
  65. ### 命令行使用
  67. - `mysql --help`查看帮助文档
  68. - `mysql -u root -pmysql`连接
  69. - `quit 和 exit 或 ctrl+d`退出
  71. - `select version();`查看版本
  72. - `select now();`显示当前时间
  74. <a name="NHdFk"></a>
  75. #### 数据库操作
  77. - `show databases;`
  78. - `use 数据库名;`
  79. - `select database();`查看当前使用的数据库
  80. - `create database 数据库名 charset=utf8;`创建数据库
  81. - `drop database 数据库名;`删除数据库
  82. <a name="k6tjE"></a>
  83. #### 数据表操作
  85. - `show tables;`查看当前数据库中所有表
  86. - `desc 表名;`查看表结构
  87. ```sql
  88. CREATE TABLE table_name(
  89.     column1 datatype contrai,
  90.     column2 datatype,
  91.     column3 datatype,
  92.     .....
  93.     columnN datatype,
  94.     PRIMARY KEY(one or more columns)
  95. );
  97. --例子
  98. create table classes(
  99.     id int unsigned primary key auto_increment not null,
  100.     name varchar(20) default '',
  101.     age tinyint unsigned default 0,
  102.     height decimal(5,2),
  103.     gender enum('男','女','人妖','保密'),
  104.     cls_id int unsigned default 0
  105. );
  106. --
  • alter table 表名 add 列名 类型;表添加字段
  • alter table 表名 change 原名 新名 类型及约束;表重命名或修改字段
  • alter table 表名 modify 列名 类型及约束;表修改字段

  • alter table 表名 drop 列名;表删除字段

  • drop table 表名;删除表

  • show create table 表名;查看表的创建语句

    表数据操作（增删改查）

    select

  • select * from 表名;查询所有列数据

  • select 列1,列2,... from 表名;

    • 查询指定列数据
    • 可以使用as为列或表指定别名
      insert into

  • insert into 表名 values(...)添加

    • insert into students values(0,’郭靖‘,1,'蒙古','2016-1-2');
  • insert into 表名(列1,...) values(值1,...)
    • insert into students(name,hometown,birthday) values('黄蓉','桃花岛','2016-3-2');
  • insert into 表名 values(...),(...)...;
    • insert into classes values(0,'python1'),(0,'python2');

  • insert into 表名(列1,...) values(值1,...),(值1,...)...;

    • insert into students(name) values('杨康'),('杨过'),('小龙女');
      update

  • update 表名 set 列1=值1,列2=值2... where 条件

    • update students set gender=0,hometown='北京' where id=5;
    • update students set isdelete=1 where id=1;逻辑删除，本质就是修改操作
      delete from

  • delete from 表名 where 条件

    • delete from students where id=5;

      备份

  • mysqldump –uroot –p 数据库名 > python.sql;备份数据库

  • mysql -uroot –p 新数据库名 < python.sql导入数据库

    mysql注释

    mysql中的四种注释

— 注释内容

这种注释方法不能够实现多行注释，要注意的是 —后面是有一个空格的

#注释内容

这种注释方法也不能实现多行注释。

/*注释内容*/

这种注释能够实现多行注释。

/!注释内容 /

这种注释在mysql中叫做内联注释，当！后面所接的数据库版本号时，当实际的版本等于或是高于那个字符串，应用程序就会将注释内容解释为SQL，否则就会当做注释来处理。默认的，当没有接版本号时，是会执行里面的内容的。

—————以下为查询语法———————————-

  1. -- 如果是单表查询 可以省略表明
  2. select id, name, gender from students;
  4. -- 使用 as 给字段起别名
  5. select id as 序号, name as 名字, gender as 性别 from students;
  7. -- 表名.字段名
  8. select students.id,students.name,students.gender from students;
  10. -- 可以通过 as 给表起别名 
  11. select s.id,s.name,s.gender from students as s;

distinct

  • 在select后面列前使用distinct可以消除重复的行
  • select distinct 列1,... from 表名;

    where

    使用where子句对表中的数据筛选，结果为true的行会出现在结果集中
    1. select * from 表名 where 条件;
    2. 例：
    3. select * from students where id=1;

    比较运算符

  • 等于: =
  • 大于: >
  • 大于等于: >=
  • 小于: <
  • 小于等于: <=
  • 不等于: != 或 <>

例：select * from students where id > 3;

逻辑运算符

  • and
  • or
  • not

例：select * from students where id > 3 and gender=0;

模糊查询

  • like
  • %表示任意多个任意字符
  • _表示一个任意字符

例：

  • select * from students where name like '黄_';
  • select * from students where name like '黄%';

  • select * from students where name like '黄%' or name like '%靖';

    范围查询

  • in表示在一个非连续的范围内

  • between … and …表示在一个连续的范围内

例：

  • select * from students where id in(1,3,8);
  • select * from students where id between 3 and 8;

  • select * from students where (id between 3 and 8) and gender=1;

    空判断

  • 注意：null与’’是不同的

  • 判空is null
  • 判非空is not null

例：

  • select * from students where height is null;

  • select * from students where height is not null and gender=1;

    优先级

  • 优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符

  • and比or先运算，如果同时出现并希望先算or，需要结合()使用

    order by

    select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
    说明

  • 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推

  • 默认按照列值从小到大排列（asc）
  • asc从小到大排列，即升序
  • desc从大到小排序，即降序

例：

  • select * from students where gender=1 and is_delete=0 order by id desc;
  • select * from students where is_delete=0 order by name;

  • select * from students order by age desc,height desc;

    聚合函数

    count

  • count(*)表示计算总行数，括号中写星与列名，结果是相同的

  • select count(*) from students;

    max

  • max(列)表示求此列的最大值

  • select max(id) from students where gender=2;

    min

  • min(列)表示求此列的最小值

  • select min(id) from students where is_delete=0;

    sum

  • sum(列)表示求此列的和

  • select sum(age) from students where gender=1;

  • select sum(age)/count(*) from students where gender=1;

    avg

  • avg(列)表示求此列的平均值

  • select avg(id) from students where is_delete=0 and gender=2;

    group by

  1. group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组

  2. group by可用于单个字段分组，也可用于多个字段分组

    group_concat()

  3. group_concat(字段名)可以作为一个输出字段来使用，

  4. 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合

    1. select gender,group_concat(name) from students group by gender;
    2. +--------+-----------------------------------------------------------+
    3. | gender | group_concat(name)                                        |
    4. +--------+-----------------------------------------------------------+
    5. | 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                               |
    6. | 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                      |
    7. | 中性   | 金星                                                       |
    8. | 保密   | 凤姐                                                       |
    9. +--------+-----------------------------------------------------------+

    group by + 集合函数

    1. -- 分别统计性别为男/女的人的个数
    2. select gender,count(*) from students group by gender;
    3. +--------+----------+
    4. | gender | count(*) |
    5. +--------+----------+
    6. | 男     |        5 |
    7. | 女     |        7 |
    8. | 中性   |        1 |
    9. | 保密   |        1 |
    10. +--------+----------+

    having

  5. having 条件表达式：用来分组查询后指定一些条件来输出查询结果

  6. having作用和where一样，但having只能用于group by
    1. select gender,count(*) from students group by gender having count(*)>2;
    2. +--------+----------+
    3. | gender | count(*) |
    4. +--------+----------+
    5. | 男     |        5 |
    6. | 女     |        7 |
    7. +--------+----------+

    with rollup

    ```sql select gender,count() from students group by gender with rollup; +————+—————+ | gender | count() | +————+—————+ | 男 | 5 | | 女 | 7 | | 中性 | 1 | | 保密 | 1 | | NULL | 14 | +————+—————+

select gender,group_concat(age) from students group by gender with rollup; +————+—————————————————————-+ | gender | group_concat(age) | +————+—————————————————————-+ | 男 | 29,59,36,27,12 | | 女 | 18,18,38,18,25,12,34 | | 中性 | 33 | | 保密 | 28 | | NULL | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 | +————+—————————————————————-+ ```

limit

  • 从start开始，获取count条数据

select * from 表名 limit start,count
例：select * from students where gender=1 limit 0,3;查询前3行男生信息

join查询（连接查询）

image.png
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列
例：

  • select * from students inner join classes on students.cls_id = classes.id;内连接
  • select * from students as s left join classes as c on s.cls_id = c.id;左连接
  • select * from students as s right join classes as c on s.cls_id = c.id;右连接

  • select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';自关联

    子查询

    在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句

  • 标量子查询: 子查询返回的结果是一个数据(一行一列)

  • 列子查询: 返回的结果是一列(一列多行)

  • 行子查询: 返回的结果是一行(一行多列)

    标量子查询

    select * from students where age > (select avg(age) from students);

    列级子查询

    select name from classes where id in (select cls_id from students);

    行级子查询

    select * from students where (height,age) = (select max(height),max(age) from students);

    in

    in 范围

  • 格式: 主查询 where 条件 in (列子查询)

    —————以上为查询语法————————-

    数据库设计

    https://blog.updown.world/articles/python/pythonbook7/03-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1%E4%B8%8EPython%E4%BA%A4%E4%BA%92/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1-1.html

image.png
image.png

视图

对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦
解决办法：定义视图

  • 通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
  • 视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
  • 方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
  1. 提高了重用性，就像一个函数
  2. 对数据库重构，却不影响程序的运行
  3. 提高了安全性能，可以对不同的用户
  4. 让数据更加清晰

    定义

  • create view 视图名称 as select语句;定义建议以v_开头
  • show tables;查看表，会将所有的视图也列出来
  • select * from v_stu_score;使用，查询

  • drop view 视图名称;删除

    事务

    所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
    事务四大特性(简称ACID)

  • 原子性(Atomicity)

  • 一致性(Consistency)
  • 隔离性(Isolation)
  • 持久性(Durability)

表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎

  • begin;或start transaction;开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
  • commit;提交事务，将缓存中的数据变更维护到物理表中
  • rollback;回滚事务，放弃缓存中变更的数据

注意：

  1. 修改数据的命令会自动的触发事务，包括insert、update、delete
  2. 而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

    索引

    索引详解：https://blog.csdn.net/qq_44129924/article/details/115333658

当数据库中数据量很大时，查找数据会变得很慢
优化方案：索引

  • 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
  • 要注意的是，建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，就没有必要为一个很少使用的where字句单独建立索引了，对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。

  • 建立索引会占用磁盘空间

  • show index from 表名;查看索引

  • create index 索引名称 on 表名(字段名称(长度))
    • 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
    • 字段类型如果不是字符串，可以不填写长度部分
  • drop index 索引名称 on 表名;删除索引

然后执行正常的查询语句即可

账户管理

  • MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种
    • 服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
    • 数据库级别账号：对特定数据库执行增删改查的所有操作
    • 数据表级别账号：对特定表执行增删改查等所有操作
    • 字段级别的权限：对某些表的特定字段进行操作
    • 存储程序级别的账号：对存储程序进行增删改查的操作
  • 账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

注意：

  1. 进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限
  2. 通常都使用数据库级操作权限

    查看所有用户

    select * from mysql.user;
    desc mysql.user;
  • 所有用户及权限信息存储在mysql数据库的user表中
  • 查看user表的结构
  • 主要字段说明：
    • Host表示允许访问的主机
    • User表示用户名
    • authentication_string表示密码，为加密后的值

select host,user,authentication_string from user;

创建账户、授权

grant 权限列表 on 数据库 to '用户名'@'访问主机' identified by '密码';

  • 需要使用实例级账户登录后操作，以root为例
  • 常用权限主要包括：create、alter、drop、insert、update、delete、select
  • 如果分配所有权限，可以使用all privileges

例1：
grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';
说明

  • 可以操作python数据库的所有表，方式为:jing_dong.*
  • 访问主机通常使用 百分号% 表示此账户可以使用任何ip的主机登录访问此数据库
  • 访问主机可以设置成 localhost或具体的ip，表示只允许本机或特定主机访问

例2：
grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"

查看用户有哪些权限

show grants for laowang@localhost;

修改权限

grant 权限名称 on 数据库 to 账户@主机 with grant option;

修改密码

  • update user set authentication_string=password('新密码') where user='用户名';
  • flush privileges

使用root登录，修改mysql数据库的user表
使用password()函数进行密码加密
注意修改完成后需要刷新权限

删除用户

  1. drop user '用户名'@'主机';
  2. delete from user where user='用户名';
    1. flush privileges

推荐使用语法1删除用户, 如果使用语法1删除失败，采用语法2方式

忘记root密码

https://blog.csdn.net/lxpbs8851/article/details/10895085

远程登录

如果向在一个Ubuntu中使用msyql命令远程连接另外一台mysql服务器的话，通过以下方式即可完成，但是此方法仅仅了解就好了，不要在实际生产环境中使用
修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件
image.png
然后重启mysqlservice mysql restart

依然连不上原因

  1. 查看数据库是否配置了bind_address参数

    本地登录数据库查看my.cnf文件和数据库当前参数show variables like ‘bind_address’; 如果设置了bind_address=127.0.0.1 那么只能本地登录

  2. 查看数据库是否设置了skip_networking参数

    如果设置了该参数，那么只能本地登录mysql数据库

主从配置

参考：https://blog.updown.world/articles/python/pythonbook7/04-MySQL%E9%AB%98%E7%BA%A7/%E4%B8%BB%E4%BB%8E.html