• 数据库


    MySQL数据库

    数据库概述

    数据存储阶段

    【1】 人工管理阶段

    缺点 : 数据无法共享,不能单独保持,数据存储量有限

    【2】 文件管理阶段 (.txt .doc .xls)

    优点 : 数据可以长期保存,可以存储大量的数据,使用简单

    缺点 : 数据一致性差,数据查找修改不方便,数据冗余度可能比较大

    【3】数据库管理阶段

    优点 : 数据组织结构化降低了冗余度,提高了增删改查的效率,容易扩展,方便程序调用,做自动化处理

    缺点 :需要使用sql 或者 其他特定的语句,相对比较复杂

    数据库应用

    融机构、游戏网站、购物网站、论坛网站 ... ...

    基础概念

    数据 : 能够输入到计算机中并被识别处理的信息集合

    数据结构 :研究一个数据集合中数据之间关系的

    数据库 : 按照数据结构,存储管理数据的仓库。数据库是在数据库管理系统管理和控制下,在一定介质上的数据集合。

    数据库管理系统 :管理数据库的软件,用于建立和维护数据库

    数据库系统 : 由数据库和数据库管理系统,开发工具等组成的集合

    数据库分类和常见数据库

    • 关系型数据库和非关系型数据库

    关系型: 采用关系模型(二维表)来组织数据结构的数据库

    非关系型: 不采用关系模型组织数据结构的数据库

    • 开源数据库和非开源数据库

    开源:MySQL、SQLite、MongoDB

    非开源:Oracle、DB2、SQL_Server

    • 常见的关系型数据库

    MySQL、Oracle、SQL_Server、DB2 SQLite

    认识关系型数据库和MySQL

    1. 数据库结构 (图库结构)

    数据元素 --> 记录 -->数据表 --> 数据库

    1. 数据库概念解析

    数据表 : 存放数据的表格

    字段: 每个列,用来表示该列数据的含义

    记录: 每个行,表示一组完整的数据

    1. MySQL特点
    • 是开源数据库,使用C和C++编写
    • 能够工作在众多不同的平台上
    • 提供了用于C、C++、Python、Java、Perl、PHP、Ruby众多语言的API
    • 存储结构优良,运行速度快
    • 功能全面丰富
    1. MySQL安装

    Ubuntu安装MySQL服务

    安装服务端: sudo apt-get install mysql-server
    安装客户端: sudo apt-get install mysql-client

    配置文件:/etc/mysql
    命令集: /usr/bin
    数据库存储目录 :/var/lib/mysql

    Windows安装MySQL

    下载MySQL安装包(windows) https://dev.mysql.com/downloads/mysql/
    mysql-installer***5.7.***.msi

    安装教程去安装

      5.启动和连接MySQL服务

    服务端启动

    查看MySQL状态: sudo /etc/init.d/mysql status
    启动服务:sudo /etc/init.d/mysql start | stop | restart

    客户端连接

    命令格式

    mysql -h主机地址 -u用户名 -p密码
    mysql -hlocalhost -uroot -p123456
    本地连接可省略 -h 选项: mysql -uroot -p123456

    关闭连接

    ctrl-D
    exit

    SQL语句

    什么是SQL

    结构化查询语言(Structured Query Language),一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。

    SQL语句使用特点

    • SQL语言基本上独立于数据库本身
    • 各种不同的数据库对SQL语言的支持与标准存在着细微的不同
    • 每条命令必须以 ; 结尾
    • SQL命令关键字不区分字母大小写

    MySQL 数据库操作

    数据库操作

    1.查看已有库

    show databases;

    2.创建库(指定字符集)

    create database 库名 [character set utf8];

    e.g. 创建stu数据库,编码为utf8
    create database stu character set utf8;
    create database stu charset=utf8;

    -- 创建数据库时,设置数据库的编码方式 
    -- CHARACTER SET:指定数据库采用的字符集,utf8不能写成utf-8
    -- COLLATE:指定数据库字符集的排序规则,utf8的默认排序规则为utf8_general_ci(通过show character set查看)
    create database dbtest CHARACTER SET utf8 COLLATE utf8_general_ci;

    3.查看创建库的语句(字符集)

    show create database 库名;

    e.g. 查看stu创建方法
    show create database stu;
    

    4.查看当前所在库

    select database();

    5.切换库

    use 库名;

    e.g. 使用stu数据库
    use stu;
    

    6.删除库

    drop database 库名;

    e.g. 删除test数据库
    drop database test;
    

    7.库名的命名规则

    • 数字、字母、下划线,但不能使用纯数字
    • 库名区分字母大小写
    • 不能使用特殊字符和mysql关键字

    数据表的管理

    1. 表结构设计初步

      【1】 分析存储内容
      【2】 确定字段构成
      【3】 设计字段类型

    2. 数据类型支持

    数字类型:

    整数类型(精确值) - INTEGER,INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT
    定点类型(精确值) - DECIMAL
    浮点类型(近似值) - FLOAT,DOUBLE
    比特值类型 - BIT

    对于精度比较高的东西,比如money,用decimal类型提高精度减少误差。列的声明语法是DECIMAL(M,D)。

    M是数字的最大位数(精度)。其范围为1~65,M 的默认值是10。
    D是小数点右侧数字的数目(标度)。其范围是0~30,但不得超过M。
    比如 DECIMAL(6,2)最多存6位数字,小数点后占2位,取值范围-9999.99到9999.99。

    比特值类型指0,1值表达2种情况,如真,假


    字符串类型:

    CHAR和VARCHAR类型
    BINARY和VARBINARY类型
    BLOB和TEXT类型
    ENUM类型和SET类型

    • char 和 varchar

    char:定长,效率高,一般用于固定长度的表单提交数据存储,默认1字符
    varchar:不定长,效率偏低

    • text 和blob

    text用来存储非二进制文本
    blob用来存储二进制字节串

    • enum 和 set

    enum用来存储给出的一个值
    set用来存储给出的值中一个或多个值


    1. 表的基本操作

    创建表(指定字符集)

    create table 表名(
    字段名 数据类型,
    字段名 数据类型,
    ...
    字段名 数据类型
    );

    • 如果你想设置数字为无符号则加上 unsigned
    • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
    • DEFAULT 表示设置一个字段的默认值
    • AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。
    • PRIMARY KEY关键字用于定义列为主键。主键的值不能重复。
    e.g.  创建班级表
    create table class_1 (id int primary key auto_increment,name varchar(32) not null,age int not null,sex enum('w','m'),score float default 0.0);
    
    e.g. 创建兴趣班表
    create table interest (id int primary key auto_increment,name varchar(32) not null,hobby set('sing','dance','draw'),course char not null,price decimal(6,2),comment text);
    

    查看数据表

    show tables;

    查看已有表的字符集

    show create table 表名;

    查看表结构

    desc 表名;

    删除表

    drop table 表名;

    数据基本操作

    插入(insert)

    insert into 表名 values(值1),(值2),...;
    insert into 表名(字段1,...) values(值1),...;
    
    e.g. 
    insert into class_1 values (2,'Baron',10,'m',91),(3,'Jame',9,'m',90);
    

    查询(select)

    select * from 表名 [where 条件];
    select 字段1,字段名2 from 表名 [where 条件];
    
    e.g. 
    select * from class_1;
    
    select name,age from class_1;
    

    where子句

    where子句在sql语句中扮演了重要角色,主要通过一定的运算条件进行数据的筛选

    MySQL 主要有以下几种运算符:

    算术运算符
    比较运算符
    逻辑运算符
    位运算符

    算数运算符

    e.g.
    select * from class_1 where age % 2 = 0;
    

    比较运算符

    e.g.
    select * from class_1 where age > 8;
    select * from class_1 where between 8 and 10;
    select * from class_1 where age in (8,9);
    

    逻辑运算符

    e.g.
    select * from class_1 where sex='m' and age>9;
    

    位运算符

    更新表记录(update)

    update 表名 set 字段1=值1,字段2=值2,... where 条件;
    
    e.g.
    update class_1 set age=11 where name='Abby';
    

    删除表记录(delete)

    delete from 表名 where 条件;
    
    注意:delete语句后如果不加where条件,所有记录全部清空
    
    e.g.
    delete from class_1 where name='Abby';
    

    表字段的操作(alter)

    语法 :alter table 表名 执行动作;
    
    * 添加字段(add)
        alter table 表名 add 字段名 数据类型;
        alter table 表名 add 字段名 数据类型 first;
        alter table 表名 add 字段名 数据类型 after 字段名;
    * 删除字段(drop)
        alter table 表名 drop 字段名;
    * 修改数据类型(modify)
        alter table 表名 modify 字段名 新数据类型;
    * 修改字段名(change)
        alter table 表名 change 旧字段名 新字段名 新数据类型;
    * 表重命名(rename)
        alter table 表名 rename 新表名;
    
    e.g. 
    alter table interest add date Date after course;
    

    时间类型数据

    时间和日期类型:

    DATE,DATETIME和TIMESTAMP类型
    TIME类型
    年份类型YEAR

    时间格式

    date :"YYYY-MM-DD"
    time :"HH:MM:SS"
    datetime :"YYYY-MM-DD HH:MM:SS"
    timestamp :"YYYY-MM-DD HH:MM:SS"
    注意
    1、datetime :不给值默认返回NULL值
    2、timestamp :不给值默认返回系统当前时间

    日期时间函数

    • now() 返回服务器当前时间
    • curdate() 返回当前日期
    • curtime() 返回当前时间
    • date(date) 返回指定时间的日期
    • time(date) 返回指定时间的时间

    时间操作

    • 查找操作
      select * from timelog where Date = "2018-07-02";
      select * from timelog where Date>="2018-07-01" and Date<="2018-07-31";
    
    • 日期时间运算

      • 语法格式

        select * from 表名 where 字段名 运算符 (时间-interval 时间间隔单位);

      • 时间间隔单位: 1 day | 2 hour | 1 minute | 2 year | 3 month

      select * from timelog where shijian > (now()-interval 1 day);
    

    高级查询语句

    模糊查询和正则查询

    LIKE用于在where子句中进行模糊查询,SQL LIKE 子句中使用百分号 %字符来表示任意字符。

    使用 LIKE 子句从数据表中读取数据的通用语法:

    SELECT field1, field2,...fieldN 
    FROM table_name
    WHERE field1 LIKE condition1
    
    e.g. 
    mysql> select * from class_1 where name like 'A%';
    

    mysql中对正则表达式的支持有限,只支持部分正则元字符

    SELECT field1, field2,...fieldN 
    FROM table_name
    WHERE field1 REGEXP condition1
    
    e.g. 
    select * from class_1 where name regexp 'B.+';
    

    排序

    ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果。

    使用 ORDER BY 子句将查询数据排序后再返回数据:

    SELECT field1, field2,...fieldN from table_name1 where field1
    ORDER BY field1 [ASC [DESC]]
    

    默认情况ASC表示升序,DESC表示降序

    select * from class_1 where sex='m' order by age;
    

    分页

    LIMIT 子句用于限制由 SELECT 语句返回的数据数量 或者 UPDATE,DELETE语句的操作数量

    带有 LIMIT 子句的 SELECT 语句的基本语法如下:

    SELECT column1, column2, columnN 
    FROM table_name
    WHERE field
    LIMIT [num]
    

    联合查询

    UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

    UNION 操作符语法格式:

    SELECT expression1, expression2, ... expression_n
    FROM tables
    [WHERE conditions]
    UNION [ALL | DISTINCT]
    SELECT expression1, expression2, ... expression_n
    FROM tables
    [WHERE conditions];
    

    expression1, expression2, ... expression_n: 要检索的列。
    tables: 要检索的数据表。
    WHERE conditions: 可选, 检索条件。
    DISTINCT: 可选,删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据,所以 DISTINCT 修饰符对结果没啥影响。
    ALL: 可选,返回所有结果集,包含重复数据。
    要求查询的字段必须相同

    select * from class_1 where sex='m' UNION ALL select * from class_1 where age > 9;
    

    多表查询

    多个表数据可以联合查询,语法格式如下

    select  字段1,字段2... from 表1,表2... [where 条件]
    
    e.g.
    select class_1.name,class_1.age,class_1.sex,interest.hobby from class_1,interest where class_1.name = interest.name;
    

    数据备份

    1. 备份命令格式

    mysqldump -u用户名 -p 源库名 > ~/***.sql

    --all-databases 备份所有库
    库名 备份单个库
    -B 库1 库2 库3 备份多个库
    库名 表1 表2 表3 备份指定库的多张表

    1. 恢复命令格式

    mysql -uroot -p 目标库名 < ***.sql
    从所有库备份中恢复某一个库(--one-database)

    mysql -uroot -p --one-database 目标库名 < all.sql

    
    回顾
    1.数据库
        存储数据的仓库
        MYSQL,Oracle,
    2.MYSQL特点
        1.关系型数据库
        2.跨平台
        3.支持多款编程语言(python,java,php,.... ....)
        4.基于磁盘存储,数据是以文件的形式保存在 /var/lib/mysql
    3.启动服务
        1. sudo /etc/init.d/mysql start|stop|restart|status
        2. sudo service mysql start|stop|restart|status
    
        客户端链接: Navicat for MySQL
            mysql -hIP地址 -u用户名 -p密码 数据库
            本地连接可省略 -h
    4.基本的SQL命令
        1.库管理
            1.创建数据库
                1.createdatabase 库名 [charset=utf8]2.createdatabase 库名 default charset utf8 collate utf8_general_ci;
            2.查看已有数据库
                show databases;
            3.查看创建库的语句
                show createdatabase4.切换库
                use 库名
            5.删除库
                dropdatabase 库名
        2.表管理
            1.创建表
                createtable 表名(
                    字段名 数据类型 字段说明,
                    字段名 数据类型 字段说明,
                )
            2.查看表结构
                desc 表名
            3.修改表结构
                altertable 表名 .... ....
            4.删除表
                droptable 表名
        3.表记录管理
            1.增加 -Create1. 向所有列中插入数据
                    insertinto 表名 values(值1,值2,...),(值1,值2,...)
                2. 向部分列中插入数据
                    insertinto 表名(字段1,字段2,...)
                    values
                    (值1,值2,...)
            2.查询 - Retrieve
                1.select 字段名 from 表名
                2.select 字段名 from 表名 where 条件
                    1. >,<,>=,<=,=,!=2. in,notin3. not , and , or4. between ... and ...
                    5. 模糊查询 % , _ ,
                3.select 字段名 from 表名
                  where 条件
                    orderby 字段名 [desc] , 字段名 [desc]4.select 字段名 from 表名
                    where 条件
                    orderby 字段
                    limit offset,num
    
                    e.g. 分页
                        条件:
                            1.当前要看第多少页
                            2.每页显示多少条数据
    
                        set@current=5,@pageSize=2select id,name,age,email
                        from users
                        limit (@current-1)*@pageSize,@pageSize5.联合查询
                    select 列1,列2,...
                    from 表名 where 条件
                    union[ALL|DISTINCT]select 列1,列2,...
                    from 表名 where 条件
                6.正则匹配
                    select*from 表名 where 字段 regexp '...'3.更新 -Updateupdate 表名 set 字段=值,字段=where 条件
            4.删除 -Deletedeletefrom 表名 
                where 条件
    5.数据类型
        1.数字
            int,bigint,tinyint,float,decimal2.字符串
            varchar,text,longtext
        3.日期和时间
            date,datetime====================================================
    练习:
        1.创建数据库:country,编码为utf8,排序校对:utf8_general_ci;
    
            createdatabase country default charset utf8 collate utf8_general_ci;
    
            use country;
    
        2.创建表 sanguo(id,name,attack,defense,gender,country)
            createtable sanguo(
                id intprimarykey auto_increment,
                name varchar(32) notnull,
                attack int,
                defense int,
                gender char(2),
                country varchar(32)
            );
        3.插入5条记录
            诸葛亮,司马懿,貂蝉,张飞,赵云 
            攻击(attack >100) , 防御(defense <100)
        4.查询所有"蜀国"人的信息
            select*from sanguo where country='蜀国';
        5.将"赵云"的攻击力设置为360,防御力设置为68
            update sanguo set attack=360,defense=68where name='赵云';
        6.将"吴国"英雄中攻击值为110的英雄的攻击值改为100,防御改为60
            update sanguo set attack=100,defense=60where country='吴国'and attack=1107.找出攻击值高于200的蜀国的英雄的名字和攻击力
            select name,attack from sanguo
            where attack>200and country='蜀国';
        8.将蜀国英雄按照攻击力从高到低排序
            select*from sanguo where country='蜀国'orderby attack desc;
        9.魏蜀两国英雄中名字为三个字的按防御值升序排序
            1.select*from sanguo where (country='魏国'or country='蜀国') and name like'___'orderby defense;
    
            2.select*from sanguo where country in ('魏国','蜀国') and name like'___'orderby defense;
        10.在蜀国英雄中,查找攻击值前三名且名字不为null的姓名,攻击值和国家
            select name,attack,country from sanguo where country='蜀国'and name isnotnullorderby attack desc limit 3;
    ====================================================1.MYSQL普通查询
        1.聚合函数 (聚合查询)
            函数名             功能
            avg(字段名)        求指定字段的平均值
            max(字段名)        求指定字段的最大值
            min(字段名)        求指定字段的最小值
            sum(字段名)        求指定字段的记录和
            count(字段名)      求指定字段的记录的个数
    
            1.聚合函数使用语法
                select 聚合函数1,聚合函数2 from 表名
    
                e.g. 1:找出sanguo表中最大的攻击力值是多少
                    selectmax(attack) from sanguo
                e.g. 2:表中共有多少个英雄
                    selectcount(*) from sanguo;
                e.g. 3:找出sanguo表中最低的防御力值是多少
                    selectmin(defense) from sanguo;
                e.g. 4:蜀国英雄中攻击值大于200的英雄的数量
                    selectcount(attack) from sanguo where country='蜀国'and attack>200;
            2.注意
                select name,max(attack) from sanguo;
    
                聚合函数在默认情况下是不能与其他列一起做查询的
            3.分组查询 + 聚合查询
                e.g. 求sanguo表中每个国家的总攻击力是多少
                分组:分组列,值相同的数据会被划分到一组
                语法:
                    select 分组列,聚合函数(列)
                    fromwhere 条件
                    groupby 分组列,...
                    orderby ... 
                    limit ...
    
                e.g. 求sanguo表中每个国家的总攻击力是多少
                select country,sum(attack) from sanguo groupby country;
    
                练习:
                    1.计算每个国家的总攻击力,平均攻击力,总防御力和平均防御力
                    select country,sum(attack),avg(attack),sum(defense),avg(defense) 
                    from sanguo 
                    groupby country
                    2.所有国家的男英雄中,英雄数量最多的前2名国家名称以及英雄数量
                    select country,count(id) 
                    from sanguo
                    where gender='M'groupby country
                    orderbycount(id) desc
                    limit 24.分组筛选 -having
                e.g 查询出平均攻击力大于105的国家名称
                作用:分组后做组内筛选,配合着group by 联用
                语法:
                    select xxxx
                    from xxx
                    where xxxx
                    groupby xxx
                    having 条件
                    orderby xxx
                    limit xxx

            代码:
            select country,count(id) as cnt         from sanguo         where gender='M'         group by country         order by cnt desc         limit 2
    e.g 查询出平均攻击力大于105的国家名称
    select country,avg(attack) as avAtt from sanguo groupby country having avAtt >1052.distinct函数 作用:去重 语法: selectdistinct(列) from 表 e.g. 查询 sanguo 表中共有多少个国家 3.查询表记录时做数学运算 运算符:+,-,*,/,% e.g. 1: 查询时显示攻击力翻倍 select attack *2from sanguo; e.g. 2: 更新蜀国所有的英雄攻击力 *2update sanguo set attack = attack *2where country ='蜀国' e.g. 3: 查询攻击力+100之后大于200的英雄的姓名和国家 select name,country from sanguo where attack +100>2002.索引 1.什么是索引 对数据库表的一列或多列的值进行排序的一种结构 2.优点 加快数据的检索速度 3.缺点 1.占用物理存储空间 2.对表中数据进行更新时,索引也会动态维护,会降低维护速度 4.索引比对手段 1.查询系统时间 2.执行查询 3.查看系统时间 在 某列 上创建索引 1.查询系统时间 2.执行查询 3.查看系统时间 5.索引的分类 1.主键索引 1.特点:增加主键之后,主键列自动会被增加索引 2.增加主键[索引]1.已有表添加主键 altertable 表名 addprimarykey(id); 2.唯一索引 1.特点 1.可以有多个 2.唯一索引所在的列的值必须唯一 2.实施手段 1.创建表的时候指定唯一性 createtable xxx( id intprimarykey auto_increment, phone varchar(20) unique, ) 2.对已有表创建索引 createuniqueindex 索引名 on 表名(字段名); 3.普通索引 1.实施手段 1.创建表同时指定普通索引 createtable 表名( id xxx xxxx, country varchar(30) , index(country), index(字段名), ) 2.对已有表增加普通索引 createindex 索引名 on 表名(字段名); 6.取消索引 dropindex 索引名称 on 表名 7.查询索引 show indexfrom 表名 CREATETABLE XXX( ID XXX, EMAIL varchar(30) unique )
    1.聚合函数
        1.聚合函数
            avg() : 
            sum() :
            max() :
            min() :
            count() :
              count(id) : 
              count(*) : 
    
            e.g : select count(id) from ...
            e.g : select name,count(id) from ... (错误)
        2.分组 + 聚合
            关键字: group by
                e.g : select country,count(id)  from sanguo group by country
        3.分组筛选 
            关键词: having
            e.g : 按国家分组,将国家人数大于2人的国家名称和人数输出
                select country,count(id) as counter
                from sanguo
                group by country
                having counter > 2
    2.去重
        distinct()
    3.索引
        1.主键索引
        2.唯一索引
            1.对已有表创建唯一索引
                create unique index 索引名 on 表名(字段)
            2.创建表时创建唯一索引
                create table xxxx(
                    字段 数据类型,
                    字段 数据类型,
                    unique(字段),
                )
        3.普通索引
            1.对已有表创建普通索引
                create index 索引名 on 表名(字段)
            2.创建表时创建唯一索引
                create table xxxx(
                    字段 数据类型,
                    字段 数据类型,
                    index(字段),
                )
        4.查看索引
            show index from 表名
        5.删除索引
            drop index 索引名 on 表名
    =======================================================
    需求:
        1.老师信息:老师姓名,老师年龄,性别,爱好
        2.课程信息:课程名称,课时
        3.学员信息:学员姓名,学员年龄,性别,毕业院校,班级,专业
        4.考试信息:某位学员的某一门课考了多少分
    
    1.表关系
        1.外键 - Foreign Key
            作用:约束当前表的某列值必须取自于另一张表的主键列值
            外键所在的列称之为"外键列"
            外键所在的表称之为"外键表"或"子表"
            被外键列所引用的表称之为"主表"或"主键表"
        2.语法
            1.创建表的同时指定外键
                create table Course(
                    id int primary key auto_increment,
                    cname varchar(20)
                )
    
                create table Teacher(
                    id int primary key auto_increment,
                    tname varchar(20),
                    course int ,
                    constraint fk_course_teacher foreign key(course_id) /*规则:外键名:fk_course_teacher,第一个外键首写,第二个主键名,第三个外键名*/
    references Course(id) ) 语法: createtable xxx( 字段 类型, .... , constraint 外键名 foreignkey(字段) references 主键表(主键列) ) 2.对已有表增加外键 altertable 表名 addconstraint 外键名 foreignkey(字段) references 主键表(主键)
           
           示例代码:
           alter table student add constraint fk_major_student        foreign key(major_id)references major(id);
    3.删除外键 altertable 表名 dropforeignkey 外键名
          alter table score drop foreign key fk_student_score
            
    4.查看外键名 show createtable 表名 3.级联操作 1.语法 altertable 表名 addconstraint 外键名 foreignkey(字段) references 主键表(主键) ondelete 级联操作 onupdate 级联操作

          代码:为score表中的stu_id增加外键,引用自student主键id,并设置级联操作  
           altertable score       addconstraint fk_student_score       foreign key(stu_id)       references student(id)       on delete cascade       on update cascade
    2.级联操作取值 1.cascade 数据级联删除、更新 2.restrict(默认) 子表中有关联数据,那么主表中就不允许做删除、更新 3.setnull 主表删除数据时子表中的相关数据会设置为null
    级联操作:主表删除,子表关联的记录全部删除。
    4.表连接查询
            1.交叉连接 - 笛卡尔积
                e.g 查询teacher和course表中所有的数据
                    select*from teacher,course;
            2.内连接
                在关联的两张表中,把满足条件的数据筛选出来
                语法:
                    select 字段,... ...
                    from 表1
                    innerjoin 表2
                    on 条件
                    innerjoin 表3
                    on 条件:一般子表的外键=主表的主键
              
             示例:查询学员的姓名,年龄,所在班级名称,专业名称,并筛选出1902的学员
              select s.name,s.age,c.classname,m.m_name           from student as s           inner join classinfo as c on s.class_id = c.id           inner join major as m on s.major_id = m.id           where c.classname='1902';
    练习:
    1.查询学员的姓名,年龄,所在班级名称,专业名称 2.查询学员的姓名,毕业院校,所在班级,考试科目,考试成绩
            
    -- 1.查询学员的姓名,年龄,所在班级名称,专业名称,并筛选出1902的学员 /*select s.name,s.age,c.classname,m.m_name from student as s inner join classinfo as c on s.class_id = c.id inner join major as m on s.major_id = m.id where c.classname='1902';*/ -- 2.查询学员的姓名,毕业院校,所在班级,考试科目,考试成绩 select s.name,s.school,c.classname,cou.cname,sc.score from student as s inner join classinfo as c on s.class_id = c.id inner join score as sc on s.id = sc.stu_id 一般子表的外键=主表的主键
    inner join course as cou on sc.course_id = cou.id;
    -- 单行注释
    /* 多行注释 */
    
    -- 创建course表:id,cname,cduration
    /*create table course(
        id int primary key auto_increment,
        cname varchar(30) not null,
        cduration int not null
    );*/
    -- 向course表中插入测试数据
    /*insert into course(cname,cduration)
    values
    ('Python基础',20),('Python高级',15),
    ('WEB基础',9),('Python Web',15),
    ('爬虫',10),('数据分析&人工智能',20);*/
    
    -- 创建teacher表:id,name,age,gender,hobby,course_id
    -- course_id是外键,引用自course表的主键id
    /*create table teacher(
        id int primary key auto_increment,
        name varchar(30) not null,
        age int not null,
        gender varchar(2) not null,
        hobby varchar(50) not null,
        course_id int ,
        -- 外键约束
        constraint fk_course_teacher foreign key(course_id)
        references course(id)
    );*/
    
    -- 向teacher表中插入测试数据
    /*insert into teacher
    values
    (null,'齐天大圣',28,'M','大保健',1),
    (null,'吕泽Maria',30,'M','拍片',2),
    (null,'赵萌萌',18,'F','看帅哥',3);*/
    
    -- 创建major表 : id,m_name
    /*create table major(
        id int primary key auto_increment,
        m_name varchar(30) not null
    );*/
    
    -- 向major表中插入数据
    /*insert into major(m_name)
    values('AID'),('UID'),('JSD'),('WEB');*/
    
    -- 创建student表:id,name,age,gender,school,class_id,major_id
    /*create table student(
        id int primary key auto_increment,
        name varchar(30) not null,
        age int not null,
        gender char(2) not null,
        school varchar(100) not null,
        class_id int not null,
        major_id int not null
    )*/
    
    -- 更新student表,增加外键关系在major_id上,引用自major表的主键id
    /*alter table student
    add constraint fk_major_student
    foreign key(major_id)
    references major(id);*/
    
    -- 创建classinfo表:id,classname,status
    /*create table classinfo(
        id int primary key auto_increment,
        classname varchar(20) not null,
        status tinyint
    );*/
    
    -- 修改student表结构,增加外键在class_id,引用自classinfo表的主键id
    /*alter table student
    add constraint fk_classinfo_student
    foreign key(class_id)
    references classinfo(id);*/
    
    -- 创建score表:id,stu_id,course_id,score
    /*create table score(
        id int primary key auto_increment,
        stu_id int not null,
        course_id int not null,
        score int not null,
        constraint fk_student_score foreign key(stu_id)
        references student(id),
        constraint fk_course_score foreign key(course_id)
        references course(id)
    )*/
    
    -- 向classinfo,student,score 表中插入测试数据
    /*insert into classinfo values
    (null,'1901',0),
    (null,'1902',1),
    (null,'1903',1),
    (null,'1904',1),
    (null,'1905',1);
    
    insert into student(name,age,gender,school,class_id,major_id) values
    ('张三',30,'M','哈佛大学',2,1),
    ('李四',25,'M','剑桥大学',3,1),
    ('王二麻子',19,'F','五道口技术学院',2,1),
    ('王五',28,'M','麻省理工学院',2,1);*/
    
    /*insert into score(stu_id,course_id,score) values
    (1,1,98),(1,2,85),(1,3,96),
    (2,1,69),(2,2,89),(2,4,65),
    (3,2,76),(3,3,88),(3,4,75);*/
    
    -- 删除score表中的fk_student_score外键
    -- alter table score drop foreign key fk_student_score
    
    -- 为score表中的stu_id增加外键,引用自student主键id,并设置级联操作
    /*alter table score
    add constraint fk_student_score
    foreign key(stu_id)
    references student(id)
    on delete cascade
    on update cascade*/
    
    -- 使用内连接查询teacher和course表中的数据(姓名,年龄,课程名称,课时)
    /*select t.name,t.age,c.cname,c.cduration
    from teacher as t
    inner join course as c
    on t.course_id = c.id*/
    
    -- 1.查询学员的姓名,年龄,所在班级名称,专业名称,并筛选出1902的学员
    /*select s.name,s.age,c.classname,m.m_name
    from student as s
    inner join classinfo as c on s.class_id = c.id
    inner join major as m on s.major_id = m.id
    where c.classname='1902';*/
    
    -- 2.查询学员的姓名,毕业院校,所在班级,考试科目,考试成绩
    select s.name,s.school,c.classname,cou.cname,sc.score
    from student as s
    inner join classinfo as c on s.class_id = c.id
    inner join score as sc on s.id = sc.stu_id
    inner join course as cou on sc.course_id = cou.id;
    View Code
    1.外键
        1.创建表设置外键
            create table xxxx(
                xxx xxxx,
                constraint 外键名 foreign key(字段) 
                references 主表(主键)
                on delete cascade|restrict|set null
                on update cascade|restrict|set null
            )
        2.修改表增加外键
            alter table xxx
            add constraint 外键名
            foreign key(字段)
            references 主表(主键)
            on delete cascade|restrict|set null
            on update cascade|restrict|set null
        3.查看外键
            show create table xxxx
        4.删除外键
            alter table xxxx
            drop foreign key 外键名
    2.连接查询
        1.交叉连接
            select * from A,B where A.bid=B.id
        2.内连接
            连接两张表,将满足条件的数据筛选出来
            select * from A
            inner join B
            on A.bid = B.id
    =======================================================
    1.连接查询
        1.外连接
            1.左外连接
                1.作用
                    1.左表中所有的数据都会查询出来(即便不满足条件)
                    2.将右表中满足关联条件的数据查询出来
                    3.关联不上的数据关联字段将以null作为填充
                2.语法
                    select 字段 
                    from A left join B
                    on 关联条件
            2.右外连接
                1.作用
                    1.右表中所有的数据都会查询出来
                    2.将左表中满足关联条件的数据查询出来
                    3.关联不上的数据关联字段将以null作为填充
                2.语法
                    select 字段
                    from A right join B
                    on 关联条件
            3.完整外连接
                1.作用
                    将两张表的数据做关联查询,关联得上的则正常显示
                    关联不上的,则以null值填充
                2.语法
                    select * from 
                    A full join B
                    on 关联条件
    2.子查询
        1.什么是子查询
            将一个查询的结果作为外侧操作的一个条件出现
        2.语法
            select .... from 表名 where 条件=(select ... )
            select .... from (查询)
        练习:
            1.查询考过"齐天大圣"老师所教课程的学员的信息
            2.查询在score表中有成绩的学员的信息
            3.查询"Python基础"课程并且分数在80分以上的
              学员的姓名和毕业院校
            4.查询和"张三"相同班级以及相同专业的同学的信息
    3.E-R模型
        1.什么E-R模型
            Entity - Relationship 模型 (实体-关系模型)
            在数据库设计阶段一定会使用到
            以图形的方式展示数据库中的表以及表关系
        2.概念
            1.实体 - Entity
                表示数据库中的一个表
                图形表示:矩形框
            2.属性
                表示某实体中的某一特性,即表的字段
                图形表示:椭圆形
           讲解链接https://blog.csdn.net/ill__world/article/details/85872034
    3.关系 - Relationship 表示实体与实体之间的关联关系 1.一对一关系(1:1) A表中的一条记录只能关联到B表中的一条记录上 B表中的一条记录只能关联到A表中的一条记录上 在数据库中的实现手段 在任意的一张表中增加: 1.外键,并引用自另一张表主键 2.唯一索引/约束 2.一对多关系(1:M) A表中的一条记录能够关联到B表中的多条记录 B表中的一条记录只能关联到A表中的一条记录 在数据库中的实现手段 在"多"表中增加: 1.外键,引用"一"表的主键 3.多对多关系(M:N) A表中的一条记录能够关联到B表中的多条记录 B表中的一条记录能够关联到A表中的多条记录 在数据库中的实现手段 靠第三张关联表,来实现多对多 1.创建第三张表 2.一个主键,俩外键 外键分别引用自关联的两张表的主键 4.SQL语句优化 1.索引:经常select,where,order by 的字段应该建立索引 2.单条查询语句最后添加 LIMIT 1 , 停止全表扫描 3.where子句中尽量不使用 != ,否则放弃索引全表扫描 4.尽量避免null值判断,否则放弃索引全表扫描 5.尽量避免 or 连接条件,否则放弃索引全表扫描 6.模糊查询尽量避免使用前置%,否则全表扫描 7.尽量避免使用in 和 not in,否则全表扫描 8.尽量避免使用 select * ,使用具体字段代替 *,不要返回用不到的任何字段 select * from A inner join B on A.bid = B.id Navicat for MySQL Power Designer - 数据库建模 Microsoft Visio - ER图
    -- 1.左外连接:左表:teacher,右表:course,关联条件:teacher.course_id=course.id
    /*select * from
    teacher left join course
    on teacher.course_id = course.id*/
    
    
    -- 2.左外连接:左表:course,右表:teacher,关联条件:teacher.course_id=course.id
    /*select * from
    course left join teacher
    on teacher.course_id = course.id;*/
    
    -- 3.右外链接,左表:course,右表:teacher,关联条件:teacher.course_id = course.id
    /*select * from
    course right join teacher
    on teacher.course_id = course.id;*/
    
    -- 4.完整外连接(有误)
    /*select * from
    course full join teacher
    on full.id = teacher.course_id*/
    
    -- 5.查看没有参加过考试的同学的信息
    /*select * from
    student left join score
    on student.id = score.stu_id
    where score.score is null;*/
    
    -- 6.子查询-查询student表中比'李四'年龄大的学员的信息
    /*select * from student
    where age > (select age from student where name='李四');*/
    -- 1.查询考过"齐天大圣"老师所教课程的学员的信息
    -- 1.1 查询"齐天大圣"老师所教授的课程的ID
    -- select course_id from teacher where name='齐天大圣';
    -- 1.2 从score表中查询出course_id的值为 "1" 的stu_id的值
    /*select stu_id from score where course_id=(
        select course_id from teacher where name='齐天大圣'
    );*/
    -- 1.3 从student表中查询出id在以上查询结果中出现过的学员的信息
    select * from student where id in (
        select stu_id from score where course_id=(
            select course_id from teacher where name='齐天大圣'
        )
    );
    -- 2.查询在score表中有成绩的学员的信息
    -- 3.查询"Python基础"课程并且分数在80分以上的学员的姓名和毕业院校
    -- 4.查询和"张三"相同班级以及相同专业的同学的信息
    View Code

    Python操作MySQL数据库

    pymysql安装

    sudo pip3 install pymysql

    pymysql使用流程

    1. 建立数据库连接(db = pymysql.connect(...))
    2. 创建游标对象(c = db.cursor())
    3. 游标方法: c.execute("insert ....")
    4. 提交到数据库 : db.commit()
    5. 关闭游标对象 :c.close()
    6. 断开数据库连接 :db.close()

    常用函数

    db = pymysql.connect(参数列表)

    host :主机地址,本地 localhost
    port :端口号,默认3306
    user :用户名
    password :密码
    database :库
    charset :编码方式,推荐使用 utf8

    数据库连接对象(db)的方法

    db.commit() 提交到数据库执行
    db.rollback() 回滚
    cur = db.cursor() 返回游标对象,用于执行具体SQL命令
    db.close() 关闭连接

    游标对象(cur)的方法

    cur.execute(sql命令,[列表]) 执行SQL命令
    cur.close() 关闭游标对象
    cur.fetchone() 获取查询结果集的第一条数据 (1,100001,"河北省")
    cur.fetchmany(n) 获取n条 ((记录1),(记录2))
    cur.fetchall() 获取所有记录

     

  • 相关阅读:
    Linus:利用二级指针删除单向链表
    mysql 主从错误以及监控
    高性能Mysql主从架构的复制原理及配置详解
    echarts的tooltip 使用formatter后,设置小圆点
    echarts定点缩放地图 && 监听缩放与拖曳事件
    echarts重新绘制图表。解决下钻地图,在平移和缩放后,下钻到下一级时生成的地图会跑到容器外面去。
    Echarts实现地图下钻+对应区域数据展示+右键返回上一级
    在生产环境清除console.log
    js编辑器--css 防止元素被切
    text-decoration 莫名其妙的覆盖子元素css
  • 原文地址:https://www.cnblogs.com/gyl10/p/11149763.html
Copyright © 2020-2023  润新知