• No.015-Python-学习之路-Day12-Mysql


    数据库与Mysql

    数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据。

    我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。
    所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理的大数据量,而所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。

    常用的关系型数据库:Oracle, Mysql, sqlServer, DB2, Postgresql, Sqlite, access;

    RDBMS即关系数据库管理系统(Relational Database Management System)的特点:
    1. 数据以表格的形式出现
    2. 每行为各种记录名称
    3. 每列为记录名称所对应的数据域
    4. 许多的行和列组成一张表单
    5. 若干的表单组成database
    RDBMS术语
    • 数据库: 数据库是一些关联表的集合。
    • 数据表: 表是数据的矩阵,在一个数据库中的表看起来像一个简单的电子表格。
    • 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
    • 行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
    • 冗余:存储两倍数据,冗余可以使系统速度更快。(表的规范化程度越高,表与表之间的关系就越多;查询时可能经常需要在多个表之间进行连接查询;而进行连接操作会降低查询速度。例如,学生的信息存储在student表中,院系信息存储在department表中。通过student表中的dept_id字段与department表建立关联关系。如果要查询一个学生所在系的名称,必须从student表中查找学生所在院系的编号(dept_id),然后根据这个编号去department查找系的名称。如果经常需要进行这个操作时,连接查询会浪费很多的时间。因此可以在student表中增加一个冗余字段dept_name,该字段用来存储学生所在院系的名称。这样就不用每次都进行连接操作了。)
    • 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
    • 外键:外键用于关联两个表。
    • 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
    • 索引:使用索引可快速访问数据库表中的特定信息,索引是对数据库表中一列或多列的值进行排序的一种结构,类似于书籍的目录。
    • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。

    Mysql数据库

    Mysql是最流行的关系型数据库管理系统,在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。由瑞典MySQL AB公司开发,目前属于Oracle公司。MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

    • Mysql是开源的,所以你不需要支付额外的费用。
    • Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
    • MySQL使用标准的SQL数据语言形式。
    • Mysql可以允许于多个系统上,并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
    • Mysql对PHP有很好的支持,PHP是目前最流行的Web开发语言。
    • MySQL支持大型数据库,支持5000万条记录的数据仓库,32位系统表文件最大可支持4GB,64位系统支持最大的表文件为8TB。
    • Mysql是可以定制的,采用了GPL协议,你可以修改源码来开发自己的Mysql系统。

    Mysql操作-简单操作

    msql –uroot –p123.com # 使用用户名密码登录;
    show databases; # 显示所有的数据库;
    use mysql; # 使用某个数据库;
    show tables; # 显示该数据库中所有的表;
    desc user; # 显示表结构;
    select * from userG; # 显示表中所有的内容,并竖着显示;
    select User from user; # 显示表中某一列的数据;
    Mysql操作-用户设置

    方式一:如果你需要添加 MySQL 用户,你只需要在 mysql 数据库中的 user 表添加新用户即可。;

    # 在mysql.user表中插入记录,所有的非空字段填满
    INSERT INTO user (host, user, `ssl_type`, `authentication_string`, `ssl_cipher`, `x509_issuer`, `x509_subject`) VALUE ('localhost', 'alvin', '', password('123'), 0, 0, 0);
    Query OK, 1 row affected (0.01 sec)
    # 修改mysql.user表赋予权限
    UPDATE user SET   Select_priv='Y', insert_priv='Y', update_priv='Y' WHERE user='alvin';
    # 刷新权限
    FLUSH privileges;

    方式二:通过SQL的 GRANT 命令 ;

    # 创建新用户
    CREATE user 'bruce' IDENTIFIED BY '123';
    # 赋权
    GRANT all privileges ON *.* TO 'bruce' @'%';
    # 刷新权限
    FLUSH PRIVILEGES:
    # 修改账户密码
    # centos 7下 mysql5.7以后版本自带默认密码,在grep 'temporary password' /var/log/mysqld.log查看
    alter user 'root'@'localhost' identified by '123456';
    set global validate_password_policy=0; # 设置密码复杂度为0
    set global validate_password_length=1; # 设置密码长度为1等,但是实际测试还是需要6位才ok,这个默认8位
    

    Mysql操作-常用管理命令

    以下列出了使用Mysql数据库过程中常用的命令:
        USE 数据库名 :选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
        SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表。
        SHOW TABLES: #显示指定数据库的所有表,使用该命令前需要使用 use命令来选择要操作的数据库。
        SHOW COLUMNS FROM 数据表: #显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
        create database testdb charset "utf8"; #创建一个叫testdb的数据库,且让其支持中文,如果不指定默认为latin1
        show create database mysql; # 可以查看这个数据创建时定义的一些信息;
        drop database testdb; #删除数据库
        SHOW INDEX FROM 数据表:显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
    MySQL数据类型

    MySQL中定义数据字段的类型对你数据库的优化是非常重要的,MySQL支持多种类型,大致可以分为三类:数值日期/时间字符串(字符)类型。

    数值类型

    image

    时间类型

    表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR,每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值,TIMESTAMP类型有专有的自动更新特性。

    image

    字符串类型

    字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET,该节描述了这些类型如何工作以及如何在查询中使用这些类型。

    image

    注:

    BINARY和VARBINARY类类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串;也就是说,它们包含字节字符串而不是字符字符串,这说明它们没有字符集,并且排序和比较基于列值字节的数值值。

    BLOB是一个二进制大对象,可以容纳可变数量的数据,有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB,它们只是可容纳值的最大长度不同。

    有4种TEXT类型:TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型,有相同的最大长度和存储需求。

    Char、varchar与text的区别

    1.它们的存储方式和数据的检索方式都不一样,数据的检索及存储效率是:char > varchar > text;

    2.char:存储定长数据很方便,必须在括号里定义长度,比如定义char(10),那么不论存储字节是否达到10个字节,都占10个字节空间(自动使用空格填充),检索的时候后面的空格会隐藏掉;

    3.varchar:存储变长数据,必须在括号里定义长度,保存数据的时候,不进行空格自动填充,检索的时候后面的空格不会隐藏,会比实际长度+1字节,用于保存实际使用了多大的长度;

    4.text:存储可变长度的非Unicode数据,最大长度为2^31-1个字符,text列不能有默认值,存储或检索过程中,不存在大小写转换,后面如果指定长度,不会报错误,但是这个长度是不起作用的;

    MySQL常用命令

    创建数据库及数据表

    语法:

    CREATE TABLE table_name (column_name column_type);
    

    实例:

    # 创建数据库fcdb
    create database fcdb charset "utf8" 
    
    # 创建一个student表
    create table student(
       stu_id INT NOT NULL AUTO_INCREMENT, # AUTO_INCREMENT每创建一条自己增加+1,y一般用于主键,NOT Null设置为空;
       name CHAR(32) NOT NULL,
       age  INT NOT NULL,
       register_date DATE,
       PRIMARY KEY ( stu_id ) # 定义主键,其实主键自动不为空,stu_id可以不用设置非空;
    );

    插入数据

    语法:

    INSERT INTO table_name ( field1, field2,...fieldN )
                           VALUES
                           ( value1, value2,...valueN );

    实例:

    mysql> insert into student(name, age, register_date) values ("Bruce Lee", 25, "2019-1-3");
    Query OK, 1 row affected (0.07 sec)
    
    mysql>
    mysql>
    mysql>
    mysql> select * from student;
    +--------+-----------+-----+---------------+
    | stu_id | name      | age | register_date |
    +--------+-----------+-----+---------------+
    |      1 | Bruce Lee |  25 | 2019-01-03    |
    +--------+-----------+-----+---------------+
    1 row in set (0.00 sec)
    

    查询数据-SELECT语句

    select 去重选项[all/distinct] 字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [having子句] [order by 子句] [limit子句];
    #where几种语法:
    方式一:基于值
    = : where 字段 = 值,查找出对应字段等于对应值的记录(相似的有<,<=,>,>=,!=),例如:where name = 'lilei';
    like:where 字段like值 ;功能与 = 相似 ,但可以使用模糊匹配来查找结果,例如:where name like 'li%';
    
    方式二:基于范围
    in: where 字段 in 范围;查找出对应字段的值在所指定范围的记录。例如:where age in (18,19,20)
    not in : where 字段 not in 范围;查找出对应字段的值不在所指定范围的记录。例如:where age not in (18,19,20)
    between x and y :where 字段 between x and y;查找出对应字段的值在闭区间[x,y]范围的记录。例如:where age between 18 and 20;
    
    方式三:条件复合
    or : where 条件1 or 条件2… ; 查找出符合条件1或符合条件2的记录。
    and:  where 条件1 and 条件2… ; 查找出符合条件1并且符合条件2的记录。
    not: where not 条件1 ;查找出不符合条件的所有记录。
    &&的功能与and相同,||与or功能类似,!与not 功能类似。
    
    注:where是从磁盘中获取数据的时候就进行筛选的,所以某些在内存是才有的东西where无法使用。(字段别名什么的是本来不是“磁盘中的数据”(是在内存这中运行时才定义的),所以where无法使用,一般都依靠having来筛选),如下:
    select name as n ,gender from student where name ="lilei";
    -- select name as n ,gender from student where n ="lilei"; --报错
    select name as n ,gender from student having n ="lilei";
    
    # Mysql排序
    SELECT field1, field2,...fieldN table_name1, table_name2...
    ORDER BY field1, [field2...] [ASC [DESC]]
    使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
    select *from student where name like binary "%Li" order by stu_id desc;
    
    # 分组统计-GROUP BY 语句
    “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组,所谓的分组就是将一个“数据集”划分成若干个“小区域”,然后针对若干个“小区域”进行数据处理。
    语法:
    SELECT column_name, function(column_name)
    FROM table_name
    WHERE column_name operator value
    GROUP BY column_name;
    注:在select指定的字段要么就要包含在Group By语句的后面,作为分组的依据;要么就要被包含在聚合函数中,<测试不正确>
    常用的聚合函数-sum(列名),max(列名),min(列名),avg(列名),count(列名),count(*)[与count(列名)有啥区别]
    mysql> select name , sum(age) as "总年龄" from student group by name order by sum(age);
    +-----------+--------+
    | name      | 总年龄 |
    +-----------+--------+
    | Min Lee   |      2 |
    | Alvin Lee |      8 |
    | Bruce Lee |     50 |
    +-----------+--------+
    3 rows in set (0.00 sec)
    
    mysql> select name, register_date, sum(age) as "总年龄" from student group by name order by sum(age);
    +-----------+---------------+--------+
    | name      | register_date | 总年龄 |
    +-----------+---------------+--------+
    | Min Lee   | 2019-03-31    |      2 |
    | Alvin Lee | 2019-01-03    |      8 |
    | Bruce Lee | 2019-01-03    |     50 |
    +-----------+---------------+--------+
    3 rows in set (0.00 sec)
    
    mysql> select name,count(age) from student group by name with rollup; # 做总的统计
    +-----------+------------+
    | name      | count(age) |
    +-----------+------------+
    | Alvin Lee |          4 |
    | Bruce Lee |          3 |
    | Min Lee   |          1 |
    | NULL      |          8 |
    +-----------+------------+
    4 rows in set (0.00 sec)
    
    mysql> select coalesce(name, "总数"),count(age) from student group by name with rollup; # 设置名称替代NULL
    +------------------------+------------+
    | coalesce(name, "总数") | count(age) |
    +------------------------+------------+
    | Alvin Lee              |          4 |
    | Bruce Lee              |          3 |
    | Min Lee                |          1 |
    | 总数                   |          8 |
    +------------------------+------------+
    4 rows in set (0.00 sec)
    
    

    修改数据-UPDATE语句

    UPDATE table_name SET field1=new-value1, field2=new-value2
    [WHERE Clause]
    update student set age=22 ,name="Bruce" where stu_id>3;

    删除行-DELETE语句

    DELETE FROM table_name [WHERE Clause]<br><br>delete from student where stu_id=5; 

    修改表名或字段-ALTER语句

    我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令;

    # 表内添加字段
    mysql> alter table student add sex enum("M","F") ;
    Query OK, 0 rows affected (0.52 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> select * from student;
    +--------+-----------+-----+---------------+------+
    | stu_id | name      | age | register_date | sex  |
    +--------+-----------+-----+---------------+------+
    |      1 | Bruce Lee |  25 | 2019-01-03    | NULL |
    |      3 | Bruce Lee |  25 | 2019-01-03    | NULL |
    |      4 | Alvin Lee |   2 | 2019-01-03    | NULL |
    |      5 | Alvin Lee |   2 | 2019-01-03    | NULL |
    |      6 | Min Lee   |   2 | 2019-03-31    | NULL |
    |      7 | Alvin Lee |   2 | 2019-01-03    | NULL |
    |      8 | Alvin Lee |   2 | 2019-01-03    | NULL |
    |      9 | Bruce Lee |  23 | 2019-03-31    | NULL |
    +--------+-----------+-----+---------------+------+
    8 rows in set (0.00 sec)
    
    # 表内删除字段-alter add/drop
    mysql> alter table student drop sex;
    Query OK, 0 rows affected (1.60 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> select * from student;
    +--------+-----------+-----+---------------+
    | stu_id | name      | age | register_date |
    +--------+-----------+-----+---------------+
    |      1 | Bruce Lee |  25 | 2019-01-03    |
    |      3 | Bruce Lee |  25 | 2019-01-03    |
    |      4 | Alvin Lee |   2 | 2019-01-03    |
    |      5 | Alvin Lee |   2 | 2019-01-03    |
    |      6 | Min Lee   |   2 | 2019-03-31    |
    |      7 | Alvin Lee |   2 | 2019-01-03    |
    |      8 | Alvin Lee |   2 | 2019-01-03    |
    |      9 | Bruce Lee |  23 | 2019-03-31    |
    +--------+-----------+-----+---------------+
    8 rows in set (0.00 sec)
    
    # 修改字段-alter modify/change
    
    mysql> alter table student modify age tinyint null DEFAULT 20; # modify修改字段的数据类型及是否为空,并设置默认值;
    Query OK, 8 rows affected (1.51 sec)
    Records: 8  Duplicates: 0  Warnings: 0
    
    mysql> alter table student change register_date register datetime not null ; # change可以修改名字及数据类型及是否为空;
    Query OK, 8 rows affected (2.25 sec)
    Records: 8  Duplicates: 0  Warnings: 0
    
    mysql> alter table student rename to students; # 修改表的名字
    Query OK, 0 rows affected (0.49 sec)
    mysql> show tables;
    +----------------+
    | Tables_in_fcdb |
    +----------------+
    | students       |
    | teacher        |
    +----------------+
    2 rows in set (0.00 sec)

    单数据源与多数据源:

    # 数据源可以为任意的二维表格,当然也可以是select生成的表;
    select * from (select stu_id, name from student) as d where name != "Bruce Lee";
    +--------+-----------+
    | stu_id | name      |
    +--------+-----------+
    |      4 | Alvin Lee |
    |      5 | Alvin Lee |
    |      6 | Min Lee   |
    |      7 | Alvin Lee |
    |      8 | Alvin Lee |
    +--------+-----------+
    
    # 数据源为多个时,结果为A*B个表项;
    mysql> select * from student, teacher;
    +--------+-----------+-----+---------------+--------+---------+-----+
    | stu_id | name      | age | register_date | tec_id | name    | age |
    +--------+-----------+-----+---------------+--------+---------+-----+
    |      1 | Bruce Lee |  25 | 2019-01-03    |      1 | Amadeus |  37 |
    |      1 | Bruce Lee |  25 | 2019-01-03    |      2 | john    |  51 |
    |      3 | Bruce Lee |  25 | 2019-01-03    |      1 | Amadeus |  37 |
    |      3 | Bruce Lee |  25 | 2019-01-03    |      2 | john    |  51 |
    |      4 | Alvin Lee |   2 | 2019-01-03    |      1 | Amadeus |  37 |
    |      4 | Alvin Lee |   2 | 2019-01-03    |      2 | john    |  51 |
    |      5 | Alvin Lee |   2 | 2019-01-03    |      1 | Amadeus |  37 |
    |      5 | Alvin Lee |   2 | 2019-01-03    |      2 | john    |  51 |
    |      6 | Min Lee   |   2 | 2019-03-31    |      1 | Amadeus |  37 |
    |      6 | Min Lee   |   2 | 2019-03-31    |      2 | john    |  51 |
    |      7 | Alvin Lee |   2 | 2019-01-03    |      1 | Amadeus |  37 |
    |      7 | Alvin Lee |   2 | 2019-01-03    |      2 | john    |  51 |
    |      8 | Alvin Lee |   2 | 2019-01-03    |      1 | Amadeus |  37 |
    |      8 | Alvin Lee |   2 | 2019-01-03    |      2 | john    |  51 |
    +--------+-----------+-----+---------------+--------+---------+-----+
    14 rows in set (0.00 sec)
    外键介绍

    外键,一个特殊的索引,用于关键2个表,只能是指定内容

    # 新建一张mytable的表,有两个字段 ID 及 status;
    create table mytable(
    id int auto_increment primary key,
    status char(20) not null
    );
      
    # 新建一张student2的表,将stu_id关联外键mytable的id
    create table student2(
    stu_id int auto_increment primary key,
    name char(20) not null,
    age int not null,
    KEY `fk_mytable_key` (`stu_id`),
    CONSTRAINT `fk_mytable_key` FOREIGN KEY (`stu_id`) REFERENCES `mytable` (`id`)
    ); 
    #  修改外键->先删除,再新建
    删除外键关联
    mysql> ALTER TABLE student2 drop FOREIGN KEY fk_class_key;
    Query OK, 0 rows affected (0.17 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    删除相关的键
    mysql> ALTER TABLE student2 drop KEY fk_class_key;
    Query OK, 0 rows affected (0.24 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    新建外键及关联
    mysql> ALTER TABLE student2 ADD FOREIGN KEY (`s_id`) REFERENCES `mytable`(`id`);
    Query OK, 0 rows affected (2.78 sec)
    Records: 0  Duplicates: 0  Warnings: 0
      
    # student2表插入数据
    如果mytable中没有这个数据,则无法插入
    mysql> INSERT INTO student2 (name, age, s_id) VALUES ("Bruce", 25, 3);
    ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`fcdb`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `mytable` (`id`))
    有就可以正常插入
    mysql> INSERT INTO student2 (name, age, s_id) VALUES ("Bruce", 25, 1);
    Query OK, 1 row affected (0.14 sec)
    mysql> INSERT INTO student2 (name, age, s_id) VALUES ("Alvin", 2, 2);
    Query OK, 1 row affected (0.12 sec)
      
    # student2与mytable相关联的数据删除
    mytable无法删除被关联的数据行
    mysql> DELETE FROM mytable WHERE `id`=1;
    ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`fcdb`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `mytable` (`id`))
    student2删除关联数据
    mysql> DELETE FROM student2 WHERE `s_id`=1;
    Query OK, 1 row affected (0.07 sec)
    mytable的id=1行,无人关联,则可以删除
    mysql> DELETE FROM mytable WHERE `id`=1;
    Query OK, 1 row affected (0.10 sec)
    Mysql中的NULL

    我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时,该命令可能就无法正常工作。
    为了处理这种情况,MySQL提供了三大运算符:
    IS NULL: 当列的值是NULL,此运算符返回true。
    IS NOT NULL: 当列的值不为NULL, 运算符返回true。
    <=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。

    Mysql 连接(left join, right join, inner join ,full join)

    MySQL 的 JOIN 在两个或多个表中查询数据,可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

    JOIN 按照功能大致分为如下三类:
        INNER JOIN(内连接,或等值连接) # 获取两个表中字段匹配关系的记录。
        LEFT JOIN(左连接) # 获取左表所有记录,即使右表没有对应匹配的记录。
        RIGHT JOIN(右连接) #  与 LEFT JOIN 相反,用于获取右表所有记录,即使左表没有对应匹配的记录。
    

    Suppose you have two tables, with a single column each, and data as follows:

    A    B
    -    -
    1    3
    2    4
    3    5
    4    6

    INNER JOIN-2个表的交集

    An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

    mysql> select * from A INNER JOIN B on A.a = B.b;
    +---+---+
    | A | B |
    +---+---+
    | 3 | 3 |
    | 4 | 4 |
    +---+---+

    LEFT/RIGHT JOIN

    A left/right join will give all rows in A/B, plus any common rows in B/A.

    mysql> select * from A LEFT JOIN B on A.a = B.b;
    +---+------+
    | A | B    |
    +---+------+
    | 3 |    3 |
    | 4 |    4 |
    | 1 | NULL |
    | 2 | NULL |
    +---+------+
    4 rows in set (0.00 sec)
    
    mysql> select * from A RIGHT JOIN B on A.a = B.b;
    +------+---+
    | A    | B |
    +------+---+
    |    3 | 3 |
    |    4 | 4 |
    | NULL | 5 |
    | NULL | 6 |
    | NULL | 7 |
    +------+---+
    5 rows in set (0.00 sec)

    FULL JOIN-2个表的并集

    A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa

    # 在mysql中并不支持full_join
    mysql> select * from A FULL JOIN B on A.a = B.b;
    ERROR 1054 (42S22): Unknown column 'A.a' in 'on clause'
    # 可以UNION一个left及一个right来实现FULL_join
    mysql> select * from A left join B on A.a = B.b UNION select * from A right join B on A.a = B.b;
    +------+------+
    | A    | B    |
    +------+------+
    |    3 |    3 |
    |    4 |    4 |
    |    1 | NULL |
    |    2 | NULL |
    | NULL |    5 |
    | NULL |    6 |
    | NULL |    7 |
    +------+------+
    7 rows in set (0.00 sec)
    数据库事务

    MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务!

    •在MySQL中只有使用了Innodb数据库引擎[5.0之后默认]的数据库或表才支持事务 
    •事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行 
    •事务用来管理insert,update,delete语句 

    事务是必须满足4个条件(ACID): Atomicity(原子性)、Consistency(稳定性)、Isolation(隔离性)、Durability(可靠性)

        1、事务的原子性:一组事务,要么成功;要么撤回;
        2、稳定性 : 有非法数据(外键约束之类),事务撤回;
        3、隔离性:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度;
        4、可靠性:软、硬件崩溃后,InnoDB数据表驱动会利用日志文件重构修改,可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里;

    在Mysql控制台使用事务来操作

    begin; # 开始一个事务
    增删改之类的操作
    rollback; # 如果中间有些操作异常,回滚,事务内操作均取消;
    commit; # 如果没啥异常的,提交即可以写入数据库;
    数据库索引

    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

    创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

    实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

    索引分单列索引和组合索引:

    单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引;

    组合索引,使用多个列来创建索引,这多个列在一起可以确定唯一性,完成对数据查询时的索引功能;

    索引对数据库造成的影响:

    索引大大提高了查询速度,同时却会降低更新表的速度;

    更新表时,MySQL不仅要保存数据,还要保存一下索引文件,建立索引会占用磁盘空间的索引文件。

    普通索引:加速查询

    # 创建方式1:CREATE INDEX indexName ON mytable(username(length)); # 注length的长度建议不超过字符的长度,一般设置等长就ok的;
    
    mysql> CREATE INDEX Myindex on students(name(32));
    Query OK, 0 rows affected (1.22 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    # 创建表时创建索引
    create table in1(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        index ix_name (name)
    )
    
    # 删除索引
    alter table student drop index myindex;
    
    # 显示索引
    show index from student;

    唯一索引:加速查询 和 唯一约束(可含null)

    # 创建方式1: create unique index 索引名 on 表名(列名) # 比普通索引多unique关键字
    
    # 创建表时创建唯一索引
    create table in1(
        nid int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        extra text,
        unique ix_name (name)
    )
    
    # 删除索引
    alter table student drop index myindex;
    

    组合索引:将n个列组合成一个索引,当频繁的同时使用n列来进行查询时使用;

    # 创建组合索引方式1
    create index mulindex on student(name, age);
    
    # 建表时创建组合索引
    student | CREATE TABLE `student` (
       `id` smallint NOT NULL AUTO_INCREMENT,
       `name` char(32) NOT NULL,
       `age` tinyint NOT NULL,
       `register_date` datetime DEFAULT NULL,
       PRIMARY KEY (`id`),
       KEY `mulindex` (`name`,`age`)
     )
    

    Python操作Mysql

    在Python中熟知的用来操作Mysql的模块分别是MySQLClient及PyMySQL两个模块,操作基本一样;

    win10上安装
    # 直接pip安装mysql-python
    pip install mysql-python
    # 毫无疑问报错,然后按照版本查找mysqlclient
    https://www.lfd.uci.edu/~gohlke/pythonlibs/#twisted
    # 下载下文件后,安装
    pip install D:UsersAdministratorDownloadsmysqlclient-1.4.6-cp37-cp37m-win_amd64.whl
    # 然后,我也没安装成功mysql-python,但是啊,从结果来看:
    我上面的操作等同于 pip install MySQLClient
    在python中可以正常的导入MySQLClient模块,来操作数据库了
    
    # 使用中报错:
    MySQLdb._exceptions.OperationalError: (2059, <NULL>)
    # 原因为mysql 8.0之后的新版本使用的加密方式变成caching_sha2_password;
    # 提供了一个解决办法,在mysql中将加密方式改为老的加密方式;
    mysql> ALTER user 'root'@'localhost' identified with mysql_native_password by 'mypassword';
    Query OK, 0 rows affected (0.25 sec)
    mysql> flush privileges;
    # 测试有效
    在mysql新增数据<逐条增与批量增>
    import MySQLdb
    
    # 建立与数据库的链接
    conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='123', db='fcdb')
    # 选定一个光标,相当于cli里的mysql>
    cur = conn.cursor()
    
    name = "Apple2"
    age =1
    
    # 两种format语句的方式
    reCount = cur.execute('insert into students (name, age) VALUES (%s, %s)', (name, age))
    reCount = cur.execute('insert into students (name, age) VALUES (%(name)s, %(age)s)'
                          , {"name":name, "age":age})
    # 批量插入的方式
    new_students = [
        ("apple10", 10),
        ("apple11", 11),
        ("apple12", 12)
    ]
    ManyCount = cur.executemany('insert into students (name, age) VALUES (%s, %s)', new_students)
    
    print(reCount) # 返回的是操作条目数
    print(ManyCount) # 返回的是操作条目数
    
    # 提交操作
    conn.commit()
    
    # 关闭光标
    cur.close()
    
    # 关闭链接
    conn.close()
    在mysql中删除数据及修改数据
    import MySQLdb
    
    conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='123.com', db='fcdb')
    cur = conn.cursor()
    
    # 删除表内容
    deCount = cur.execute('DELETE FROM students WHERE age IS NULL ')
    # 修改表内容
    chCount = cur.execute("UPDATE students SET register=%s WHERE register IS NUll", ("2020-4-1",))
    
    print(deCount, chCount) # 返回的是操作条目数
    conn.commit()
    cur.close()
    conn.close()
    
    在mysql中查找数据
    import MySQLdb
    
    conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='123.com', db='fcdb')
    #cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor) ???
    cur = conn.cursor()
    
    # 查表内容
    seCount = cur.execute('SELECT * FROM students')
    
    print(cur.fetchone()) # 一条条显示
    print(cur.fetchone())
    print(cur.fetchone())
    # 相对当前位置移动行数
    cur.scroll(-1, mode="relative")
    print(cur.fetchone())
    print(cur.fetchone())
    # 直接跳转到某行
    cur.scroll(0, mode="absolute")
    print(cur.fetchone())
    
    cur.scroll(0, mode="absolute")
    # 一次全部显示
    print("分割线".center(50, "-"))
    allLine = cur.fetchall()
    for line in allLine:
        print(line)
    cur.scroll(0, mode="absolute")
    print("分割线".center(50, "-"))
    # 一次显示多条
    SomeLine = cur.fetchmany(4)
    for line in SomeLine:
        print(line)
        
    conn.commit()
    cur.close()
    conn.close()
    






    end

    课件-https://www.cnblogs.com/alex3714/articles/5950372.html

    char varcha text-https://blog.csdn.net/mjshldcsd/article/details/78541323

    Python操作Mysql->

    python-mysqldb : http://www.cnblogs.com/wupeiqi/articles/5095821.html

    pymysql : http://www.cnblogs.com/wupeiqi/articles/5713330.html  

    Mysql的扩展知识->

    mysql练习题 http://www.cnblogs.com/wupeiqi/articles/5729934.html 

    更多mysql知识,请看http://www.cnblogs.com/wupeiqi/articles/5713323.html

    ORM框架:http://www.cnblogs.com/alex3714/articles/5978329.html

  • 相关阅读:
    springloud系列搭建注册中心
    在生产环境下禁用swagger
    consul怎么在windows下安装
    linux上传与下载
    使用git将本地代码提交到码云上去
    springboot整合activemq(三)配置文件
    springboot整合activemq(二),消费均匀分析
    Python3学习之路~3.2 递归、函数式编程、高阶函数、匿名函数、嵌套函数
    Python3学习之路~3.1 函数基本语法及特性、返回值、参数、局部与全局变量
    Python3学习之路~2.9 字符编码与转码
  • 原文地址:https://www.cnblogs.com/FcBlogPythonLinux/p/12601033.html
Copyright © 2020-2023  润新知