MySQ基本操作

MySQL概述

1、什么是数据库
    数据库是一个存储数据的仓库
2、哪些公司在用数据库
    金融机构、购物网站、游戏网站、论坛网站... ...
3、提供数据库服务的软件
    1、软件分类
        MySQL、SQL_Server、Oracle、DB2、Mariadb、MongoDB ..
    2、在生产环境中，如何选择使用哪个数据库软件
        1、是否开源
            1、开源软件
                MySQL、Mariadb、MongoDB
            2、商业软件
                Oracle、DB2、SQL_Server
        2、是否跨平台
            1、不跨平台 ：SQL_Server
            2、跨平台
                MySQL、Oracle、DB2、Mariadb、MongoDB
        3、公司类型
            1、商业软件：政府部门、金融机构
            2、开源软件：游戏网站、购物网站、论坛网站... 
4、MySQL特点
    1、关系型数据库
        1、关系型数据库特点
            1、数据是以行和列的形式存储的
            2、这一系列的行和列成为表
            3、表中的每一行叫一条记录
            4、表中的每一列叫一个字段
            5、表和表之间的逻辑关联叫关系
            6、关系型数据库的核心内容是 关系 即 二维表

            2、非关系型数据库存储
                {姓名:"张三丰",年龄:25,班级:"1712",班主任:"侯"}
                {姓名:"张三丰",年龄:25,班级:"1712",班主任:"侯"}
    2、跨平台
        可以在Unix、Linux、Windows上运行MySQL服务
    3、支持多种编程语言
        Python、java、php、... ...

启动和连接Mysql服务

1、服务端启动
    1、查看Mysql服务的状态
        sudo /etc/init.d/mysql status
    2、启动Mysql服务
        sudo /etc/init.d/mysql start
    3、停止Mysql服务
        sudo /etc/init.d/mysql stop
    4、重启Mysql服务
        sudo /etc/init.d/mysql restart
2、客户端连接
    1、命令格式
        mysql -h主机名 -u用户名 -p密码
        mysql -hlocalhost -uroot -p123456
    2、本地连接可以省略 -h 选项
        mysql -uroot -p123456
    3、断开与服务器的连接
        exit   |   quit  |   \q

基本SQL命令

1、SQL命令的使用规则
    1、每条命令必须以 ; 结尾
    2、SQL命令不区分字母大小写
    3、使用 \c 终止命令的执行
2、库的管理
    1、库的基本操作
        1、查看已有的库
            show databases;
        2、创建库(指定字符集)
            create database 库名 default charset=utf8;
        3、查看创建库的语句
            show create database 库名;
        4、查看当前所在库
            select database();
        5、切换库
            use 库名;
        6、查看库中已有表
            show tables;
        7、删除库
            drop database 库名;
    2、库的命名规则
        1、可以使用数字、字母、_,但是不能是纯数字
        2、库名区分字母大小写
        3、库名具有唯一性
        4、不能使用特殊字符和mysql关键字

3、表的管理
    1、表的基本操作
        1、创建表
            create table 表名(
            字段名 数据类型,
            字段名 数据类型,
            ...
            );
        2、查看创建表的语句(字符集)
            show create table 表名;
        3、查看表结构
            desc 表名;
        4、删除表
            drop table 表名;
    2、注意
        1、所有的数据都是以文件的形式存储在数据库目录下
        2、数据库目录：/var/lib/mysql

    1、在表中插入记录
        1、insert into 表名 values(值1),(值2),....;
    2、查看表记录
        1、select * from 表名;
        2、select 字段名1,字段名2,... from 表名;

如何更改默认字符集

1、方法
    通过更改Mysql的配置文件实现
2、步骤
    1、获取root权限
        sudo -i
    2、修改mysql配置文件
        vi /etc/mysql/mysql.conf.d/mysqld.cnf
        [mysqld]
        character_set_server = utf8
    3、重启mysql服务
        sudo /etc/init.d/mysql restart      

客户端把数据存储到数据库服务器上的过程

1、连接到数据库服务器 : mysql -uroot -p
2、选择库 : use 库名;
3、创建/修改表
4、断开与数据库的连接 ：exit | quit | \q

数据类型

1、数值类型(有符号signed 和 无符号unsigned)
    1、整型
        1、int 大整型(4个字节)
            取值范围：0~2**32 -1
        2、tinyint 微小整型(1个字节)
            1、有符号(signed默认) -128~127
            2、无符号(unsigned) 0~255
        3、smallint 小整型(2个字节)
            取值范围：0~65535
        4、bigint 极大整型(8个字节)
            取值范围：0~2**64 -1
    2、浮点型
        1、float(4个字节,最多显示7个有效位)
            1、用法
                字段名 float(m,n) m->总位数 n->小数位位数
                float(5,2) 取值范围：-999.99~999.99
            2、注意
                1、浮点型插入整数时会自动补全小数位数
                2、小数位如果多于指定的位数，会对下一位四舍五入
        2、double(8个字节,最多显示15个有效位)
            1、用法
                字段名 double(m,n)
        3、decimal(M+2个字节,最多显示28个有效位)
            1、用法
                decimal(M,D)
2、字符类型
    1、char(定长)
        1、宽度取值范围：1~255
        2、不给定宽度默认宽度为1
    2、varchar(变长)
        1、取值范围：1~65535
        2、注意
            1、varchar没有默认宽度,必须给定一个宽度
            2、char和varchar使用时都给定宽度，但不能超过各自的范围
        3、char 和 varchar的特点
            1、char
                浪费存储空间,性能高
            2、varchar
                节省存储空间,性能低
        4、字符类型的宽度和数值类型的宽度的区别
            1、数值类型的宽度为显示宽度,只用于select查询时使用,和占用存储空间大小无关,可用zerofill查看效果
            2、字符类型的宽度超过则无法存储

3、枚举类型
    1、定义 ：字段值只能在列举的范围内选择
    2、enum 单选(最多有65535个不同的值)
        字段名 enum(值1,值2,...)
    3、set 多选(最多有64个不同的值)
        字段名 set(值1,值2,...)
        likes set("Study","Girl","Python","MySQL")
        "Study,Gril"
4、日期时间类型
    1、year ：年 YYYY
    2、date ：日期 YYYYMMDD
    3、time ：时间 HHMMSS
    4、datetime ：日期时间 YYYYMMDDHHMMSS
    5、timestamp ：日期时间 YYYYMMDDHHMMSS
    6、注意
        1、datetime不给值默认返回NULL
        2、timestamp不给值默认返回系统当前时间

表字段的操作

1、语法 ：alter table 表名 执行动作;
    1、添加字段(add)
        1、添加到末尾
            alter table 表名 add 字段名 数据类型;
        2、添加到开始
            alter table 表名 add 字段名 数据类型 first;
        3、添加到指定位置
            alter table 表名 add 字段名 数据类型 after 字段名
    2、删除字段(drop)
        alter table 表名 drop 字段名;
    3、修改数据类型(modify)
        alter table 表名 modify 字段名 新的数据类型;
    4、修改字段名(change)
        alter table 表名 change 旧名 新名 数据类型;
    5、修改表名(rename)
        alter table 表名 rename 新表名;

名词介绍

1、DB(Database)
    DB就是数据库,存储数据的仓库
2、DBMS(Database Management System)
    数据库管理系统
    管理数据库的软件,MySQL、Oracle、...
3、DBS(Database System)
    数据库系统
    DBS=DB(存储)+DBMS(数据库软件)+数据库应用(财务管理系统,人事管理系统)+用户

where条件字句(配合查、改、删操作)

1、语法格式
    select * from 表名 where 条件;

表记录管理(续)

1、更新表记录
    1、update 表名 set 字段名=值,字段名=值,...where 条件;
    2、注意
        update语句后如果不加where子句,表中所有记录该字段的值都会更改
2、删除表记录
    1、delete from 表名 where 条件;
    2、注意
        delete语句后如果不加where条件子句,将会把表中所有的记录全部删除

运算符操作(配合查询、修改、删除操作)

1、数值比较&字符比较
    1、数值比较运算符：=、!=、>、>=、<、<=
    2、字符比较运算符：=、!=
    3、语法格式
        查询：
            select * from 表名 where 字段名 运算符 数字/字符;
        修改：
            update 表名 set 字段名=值,... where 字段名 运算符 数字/字符;
        删除：
            delete from 表名 where 字段名 运算符 数字/字符;

2、逻辑比较
    1、运算符: 
        and(多个条件同时满足)  
        or(多个条件有一个条件满足就可以)

3、范围内比较
    1、运算符 ：between and 、in 、not in
    2、语法格式
        字段名 between 值1 and 值2
        字段名 in(值1,值2,...)
        字段名 not in(值1,值2,...)

4、匹配空、非空
    1、空 ：is null
    2、非空 ：is not null

5、模糊比较
    1、语法格式
        字段名 like 表达式
    2、表达式
        1、_ : 匹配单个字符
        2、% : 匹配0到多个字符

SQL查询

1、总结(执行顺序)
    3、 select ... 聚合函数 from ...
    1、 where ...
    2、 group by ...
    4、 having ...
    5、 order by ...
    6、 limit ...;
2、order by
    1、作用：对查询的结果进行排序
    2、语法格式：order by 字段名 排序方式;
    3、排序方式
        1、ASC(默认) ： 升序
        2、DESC   ：降序

3、limit(永远放在SQL语句的最后写)
    1、作用：限制显示查询记录的条数
    2、用法
        1、limit n -->显示几条记录
        2、limit m,n
            m --> 从第几条记录开始显示,n表示显示几条
            ## m的值是从0开始计数的,3从第四条记录开始
            limit 1,3 --> 显示 2、3、4 三条记录

4、聚合函数
    1、分类
        1、avg(字段名) : 求字段的平均值
        2、sum(字段名) : 求字段的和
        3、max(字段名) : 求字段的最大值
        4、min(字段名) : 求字段的最小值
        5、count(字段名):统计该字段记录的个数

5、group by
    作用 ：给查询的结果进行分组


6、having
    1、作用：对查询的结果进行进一步筛选

    2、注意
        1、having语句通常与group by语句联合使用,用来过滤由group by语句返回的记录集
        2、having语句的存在弥补了where关键字不能与聚合函数联合使用的不足,having操作的是聚合函数生成的显示列
7、distinct
    1、作用：不显示字段的重复值

    2、注意
        1、distinct处理的是distinct和from之间的所有字段,所有字段值必须全部相同才能去重
        2、distinct不能对任何字段做聚合处理
8、查询表记录时做数学运算
    1、运算符 ：+ - * / %

约束

1、作用
    为了保证数据的完整性、一致性、有效性
2、约束分类
    1、默认约束(default)
        1、作用
            在插入记录时,如果不给该字段赋值,则使用默认值
        2、格式
            字段名 数据类型 default 值,
    2、非空约束(not null)
        1、作用 ：不允许将该字段设置为NULL
        2、格式 ：字段名 数据类型 not null

索引

1、定义
    对数据库中表的一列或者多列的值进行排序的一种结构(MySQL中用Btree方式)
2、优点
    加快数据的检索速度
3、缺点
    1、当对表中的数据进行增加、删除或修改的时候,索引也到动态维护,降低了数据的维护速度
    2、索引需要占用物理空间
4、索引分类
    1、普通索引(index)
        1、使用规则
            1、一个表中可以有多个index字段
            2、字段的值可以有重复,且可以为NULL值
            3、经常把做查询条件的字段设置为index字段
            4、index字段的key标志是MUL
        2、创建index
            1、创建表时创建
                index(字段名1),index(字段名2)
            2、在已有表中创建index
                1、语法
                    create index 索引名 on 表名(字段名);
                2、注意
                    索引名一般和字段名一样
        3、查看普通索引
            1、desc 表名; -->查看key标志
            2、show index from 表名;
        4、删除索引
            drop index 索引名 on 表名;
    2、唯一索引(unique key)
        1、使用规则
            1、一个表中可以有多个unique字段
            2、unique字段的值不允许重复,但可以为NULL
            3、unique的key标志是UNI
        2、创建唯一索引unique
            1、创建表时创建
                1、unique(字段名1),unique(字段名2)
                2、字段名 数据类型 unique,
            2、在已有表中创建
                create unique index 索引名 on 表名;
    3、删除唯一索引
        drop index 索引名 on 表名;
        注意：index、unique在删除时只能一个一个删


3、主键索引(PRI)
    1、使用规则
        1、一个表中只能有一个主键(primary)字段
        2、对应字段的值不允许重复,且不能为空
        3、主键字段的key标志PRI
        4、把表中能够唯一标识一条记录的字段设置为主键,通常把表中记录编号的字段设置为主键
    2、创建主键(primary key)
        1、创建表时创建
            1、字段名 数据类型 primary key,
            2、primary key(字段名)
        2、在已有表中创建
            alter table 表名 add primary key(字段名);
    3、删除
        alter table 表名 drop primary key;
    4、自增长属性(auto_increment)
        1、作用：通常和主键字段一起配合使用
        2、创建
            1、创建表时创建
            字段名 数据类型 primary key auto_increment
            2、在已有表中添加自增长属性(modify)
                alter table 表名 modify 字段名 数据类型 primary key auto_increment
    5、删除主键及自增长属性
        1、alter table 表名 modify 字段名 数据类型;
        2、alter table 表名 drop primay key;
4、外键索引(foreign key)
    1、定义
        让当前表的字段值在另一个表的范围内选择
    2、语法格式
        foreign key(参考字段名)
        references 被参考表名(被参考字段名)
        on delete 级联动作
        on update 级联动作
    3、案例
        表1：缴费信息表(财务)
            学号  姓名     班级     缴费金额
                1   唐伯虎  AID1712     28000
                2   点秋香  AID1712     20000
        表2：学生信息表(班主任)
            学号  姓名    缴费金额
                1   唐伯虎    28000
    4、删除外键
        1、语法格式
            alter table 表名 drop foreign key 外键名;
        2、注意
            1、外键名的查看方式
                show create table 表名;
    5、在已有表中添加外键
        1、语法格式
            alter table 表名 add 
            foreign key(参考字段名) references
            被参考表名(被参考字段名)
            on delete 级联动作
            on update 级联动作
        2、注意
            在已有表中添加外键时,会受到表中原有数据的限制
    6、级联动作
        1、cascade ：数据级联更新
            1、当主表删除记录时,如果从表有相关联记录则级联删除
            2、当主表更新被参考字段的值时,从表级联更新参考字段的值
        2、restrict(默认)
            1、当主表删除记录时,如果从表中有相关联记录则不允许主表删除
            2、update同 1 
        3、set null
            1、当主表删除记录时,从表中相关联记录外键字段值变为null
            2、update 同 1
        4、no action
            同 restrict,都是立即检查外键限制
    7、使用规则
        1、两张表被参考字段和参考字段的数据类型要一致
        2、被参考字段必须是KEY的一种,通常是primary key

数据导入

1、作用：将文件系统的内容导入到数据库中
2、语法格式
    load data infile "文件名"
    into table 表名
    fields terminated by "分隔符"
    lines terminated by "分隔符"

    tarena:x:1000:1000:tarena,,,:/home/tarena:/bin/bash
    用户名:密码:UID:GID:描述:家目录:登录权限
3、把/etc/passwd 导入到mysql数据库中
4、操作步骤
    1、在数据库中创建对应的表
    2、查看数据库的默认搜索路径
        show variables like "secure_file_priv";
    3、将系统文件拷贝到数据库的默认搜索路径中
        sudo cp /etc/passwd /var/lib/mysql-files
        sudo -i
        cd /var/lib/mysql-files
        ls
        exit

数据导出

1、作用
    将数据库中表的记录保存到系统文件里
2、语法格式
    select ... from 表名
    into outfile "文件名"
    fields terminated by "分隔符"
    lines terminated by "分隔符"

3、注意
    1、导出的内容由SQL查询语句决定
    2、执行导出命令时路径必须指定在对应的数据库目录下
    3、show variables like "secure_file_priv";
         show variables like "%secure%";

表的复制

1、表的复制
    语法格式
        create table 表名 select 查询命令;

2、只复制表结构
    1、语法格式
        create table 表名 select ... where false;
3、注意
    1、复制表的时候不会把原有表的 键 属性复制过来

嵌套查询

1、定义
    把内层的查询结果作为外层查询的条件
2、语法格式
    select查询语句 where 条件(select查询语句);

多表查询

两种方式
    1、select 字段名列表 from 表名列表; #笛卡尔积
        select * from tt1,tt2;
    2、select 字段名列表 from 表名列表 where 条件;

连接查询

1、内连接
    1、定义
        从表中删除与其他被连接表中没有匹配到的行
    2、语法格式
        select 字段名列表 from 表1 
        inner join 表2 on 条件;

2、外连接
    1、左连接
        1、定义
            以左表为主显示查询结果
        2、语法
            slect 字段名列表 from 表1 left join 表2 on 条件;

    2、右连接
        1、定义
            以左表为主显示查询结果
        2、语法
            slect 字段名列表 from 表1 right join 表2 on 条件;

数据备份(在Linux终端操作)

1、命令格式
    mysqldump -u用户名 -p 源库名 > 路径/XXX.sql
2、源库名的表示方式
    --all-databases    备份所有库
    库名               备份单个库
    -B 库1 库2 ...     备份多个库
    库名 表1 表2 ...   备份指定库的指定表

数据恢复(在Linux终端操作)

1、命令格式
    mysql -uroot -p 目标库名 < 路径/XXX.sql
2、从所有库的备份文件中恢复某一个库(--one-database)
    mysql -uroot -p --one-database 目标库名 < 路径/all_mysql.sql

3、注意
    1、恢复库时如果恢复到原有库会将表中数据覆盖,新增的表不会删除
    2、在恢复时如果要恢复的库不存在,则先要创建空库

ER模型&ER图

1、定义
    ER模型即实体-关系模型,ER图即实体-关系图
2、三个概念
    1、实体
        1、定义：现实世界中任何可以被认知、区分的事物
        2、示例
            1、学校 ：学生、教师、课程、班主任 ...
            2、企业 ：职工、产品
    2、属性
        1、定义 ：实体所具有的特性
        2、示例
            1、学生属性：学号、姓名、年龄、性别、专业 ...
            2、产品属性：产品编号、产品名称、规格 ...
    3、关系
        1、定义 ：实体之间的关系
        2、分类
            一对一关系(1:1) 班级和班长
            一对多关系(1:n) 公司和职工、班级和学生
            多对多关系(m:n) 学生和课程、商店和顾客
    4、ER图的绘制
        1、矩形框代表实体,菱形框代表关系,椭圆形代表属性
        2、示例
            学生选课系统

workbench(图形化界面操作数据库)

事务&事务回滚

1、事务定义
    一件事从开始发生到结束的整个过程
2、属性
    1、原子性：一个事务是不可分割的工作单位,事务中的各个操作要么都做,要么就都不做
    2、一致性：事务必须从一个一致性状态到另一个一致性状态
    3、隔离性：一个事务的执行不能被其他并发事务干扰
    4、持久性：一个事务一旦提交,它对数据库的改变是永久性的
3、事务及事务回滚的应用
    1、mysql中默认sql语句会自动commit到数据库
        show variables like "autocommit";
    2、事务应用
        1、开启事务
            mysql> start transaction;
            ## 此时autocommit被禁用,SQL命令不会对数据库中数据做修改
        2、终止事务
            mysql> commit; | rollback;
    3、注意
        事务&事务回滚只针对对表记录的操作:增加、删除、修改,对创建库、创建表是无效的
    4、案例
        1、背景
            你 ：   建行卡
            你朋友：工商卡
            你在建行的自动提款机给你朋友(工商银行卡)转账
        2、过程
            表1、建行：CCB
            表2、工行：ICBC

            ###开始转账
            mysql>start transaction;
            mysql>update CCB set money=95000 where name="你";
            mysql>update ICBC set ... 断电了...;
            mysql>rollback

            ###验证
            mysql>select * from CCB;

Python数据库编程

1、python数据库接口(Python DB-API)
    1、定义：为开发人员提供的数据库应用编程接口
    2、支持的数据库服务软件
        MySQL、Oracle、SQL_Server、Sybase、Mogodb ...
    3、Python提供的操作MySQL的模块
        模块名：pymysql
    4、pymysql的使用流程
        1、建立数据库连接
        2、创建游标对象
        3、使用游标对象的方法和SQL语句操控MySQL数据库
        4、提交commit
        5、关闭游标
        6、关闭数据库连接
    5、建立数据库连接
        1、语法格式
            对象名(db) = pymysql.connect("主机地址","用户名","密码","库名",charset="utf8")
        2、connect对象(db)的方法
            1、cursor() 创建一个游标对象db.cursor()
            2、commit() 提交到数据库 db.commit()
            3、rollback() 回滚 db.rollback()
            4、close() 关闭与数据库的连接 db.close()
        3、cursor游标对象(cur)的方法
            1、execute() 执行sql命令
            2、fetchone() 取得结果集的第一条记录
            3、fetchmany(数字) 取得结果集的 几条 记录
            4、fetchall() 取得结果集的所有行
            5、close() 关闭游标对象
            属性：rowcount : 返回命令执行所影响的条数
    
    pymysql.connect --->>db ---> db.cursor() ----> 执行

转载于:https://www.cnblogs.com/-hjj/p/9935042.html