MySQL数据库 -- 终端 -- 操作命令1.0

mysql服务器：(Mac可以通过homebrew来安装)
sudo apt-get install mysql-server
sudo server mysql start
sudo server mysql stop
sudo server mysql restart
ps ajx | grep mysql    或 ps aux | grep mysql

mysql客户端：
sudo apt-get install mysql-client
mysql -uroot -p
ctrl+d 或 quit 或 exit

-- mysql数据库简单操作：
mysql -uroot -p

select version();
select now();
prompt python>

数据库操作：
create database python_test charset=utf8;
show databases;
show create database python_test;
drop database python_test;
select database();
use python_test;

数据表操作：
show tables;
create table person(
    id int unsigned primary key auto_increment not null,
    name varchar(20) not null default 0,
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);
show tables;
desc person;
show create table person;
drop table person;
    修改表结构:alter table person xxx;
    add:
    alter table person add birthday datetime;
    change:
    alter table person change birthday birth datetime;
    alter table person change birth birth date not null;
    modify:
    alter table person modify birth date nut null;
    drop:
    alter table person drop birth;

数据表中数据操作：
-增-insert:
方式1:
insert into person values
    (0,'小明',18,180.00,2,1,0),
    (0,'小月月',18,180.00,2,2,1),
    (0,'彭于晏',29,185.00,1,1,0),
    (0,'刘德华',59,175.00,1,2,1),
    (0,'黄蓉',38,160.00,2,1,0),
    (0,'凤姐',28,150.00,4,2,1),
    (0,'王祖贤',18,172.00,2,1,1),
    (0,'周杰伦',36,NULL,1,1,0),
    (0,'程坤',27,181.00,1,2,0),
    (0,'刘亦菲',25,166.00,2,2,0),
    (0,'金星',33,162.00,3,3,1),
    (0,'静香',12,180.00,2,4,0),
    (0,'郭靖',12,170.00,1,4,0),
    (0,'周杰',34,176.00,2,5,0);

方式2:
--准备数据 areas.sql 创建一个表 areas 结构搭建好后
    -- 通过 source 指令导入一个sql文件，注意 .sql 后缀不可省略
    source ~/Desktop/areas.sql

方式3:
insert into person (name, height) values ('王菲', 170),('谢霆锋', 170),('成龙', 173);

方式4:多表间操作-子查询插入数据-insert ... select  # 将查询的结果插入到某一个表中
    -以 jing_dong 数据库为例：
    insert into goods_cates (name) select cate_name from goods group by cate_name;

-删-delete:
delete from person where id = 7;

-改-update:
方式1: 
update person set name='大月月',age=20,height=184.00 where id = 2;

方式2:连接更新-表内数据
update goods as g join goods_cates as c on g.cate_name = c.name set g.cate_name = c.id;
    -注意：如果 表结构 约束改变，需要修改约束

-查-select:
select * from person;
select age,name,height from person;
select age as '芳名', height as '身高', age as '芳龄' from person;

-加鸡腿
-- 创建表 并且插入数据 一步到位  
    - create ... select   # 创建表的时候，就插入数据
    -- brand_name 品牌名称
    - 如果查找的数据的字段和表中的字段名不一样 此时会在表中创建一个新的名字一样的字段,将查询到的数据一一对应的自动创建的字段上面
    -- 给brand_name 起别名, 或者将goods_brands表中的name 修改为brand_name
    create table if not exists goods_brands(
        id int unsigned primary key auto_increment,
        name varchar(40) not null
    ) select brand_name as name from goods group by brand_name;

mysql数据库复杂操作：
-以 python_test_1 数据库为例；

-基础查询
select * from students;
select name,gender from students;
select age as '芳名', gender as '性别' from students;

select students.* from students;
select students.name, students.gender from students;
select python_test_1.students.* from students;

select s.name, s.age, s.gender from students as s;

-去重行
select distinct gender from students;

-条件查询
select * from students where age > 18;
select * from students where age = 18;
select * from students where age != 18;

select * from students where age >= 18 and gender = '男';
select * from students where age >= 18 or gender = '女';
select * from students where not age = 18;

    -模糊查询
    like: % 任意字符可有可无  _ 任意一个字符
    select * from students where name like '小%'；
    select * from students where name like '__';

    rlike:正则表达式规则
    select * form students where name rlike '^周.*';

    -范围查询-在不在...里
    in   not in   非连续范围，列举
    select * from students where age in (18,18,34,49,45);
    select * from students where age not in (18,34);

    between ... and ...     not between ... and ...  连续范围[ ]，闭区间
    select * from students where age between 18 and 50;
    select * from students where age not between 18 and 34;

    -空判断-is判断-null  not null
    select * from students where height is null;
    select * from students where height is not null;

-聚合统计函数-目的是为了-统计
    -总数count()
    * 按行计数，即使null
    select count(*) from students;
    字段计数，如果null不计数
    select count(height) from students;

    -最大值max()-最小值min()
    select max(height) from students;

    -求和sum()
    select sum(age) from students;

    -平均值avg()
    select avg(age) from students;
    select sum(age)/count(*) from students;

    -四舍五入round(123.23, 1) 保留1位小数，如：round(23.46, 1)  -> 23.5  round(23.44, 1)  ->23.4
    select round(avg(age), 2) from students;

-加鸡腿
? 帮助文档
? functions;
? round;
? avg;

-分组
    -group by 按条件-字段中的值相同的，分组成一组一组的(行)
    select gender as g from students group by gender;
    select gender,avg(age) from students group by gender;
    select gender,count(*),max(age),avg(height) from students group by gender;

    -group_concat() 按条件分组成一组一组的(行)，将字段中值的以 “集合” 形式显示出来，如: 男 (彭于晏,刘德华,周杰伦,程坤,郭靖,胡歌)
    select group_concat(name) from students group by gender;
    select gender, group_concat(name) from students group by gender;
    select gender,group_concat(name) as '对应性别都有谁，叫什么名字，列举出来',max(age) from students group by gender;

    -having 条件筛选 表示对于已经分组的数据做进一步的筛选 和 分组技能 是一对 cp 
    注意：有having 一定 group by (having 虽然可以用 where 替代，但大家都这样用，共识，好识别)
    select gender from students group by gender having gender = '男';

    -with rollup 在最后新增一行，来记录当前列里所有记录的总和
    select gender as g, count(*) from students group by gender with rollup;
    select gender as g, group_concat(name) from students group by gender with rollup;

-排序-order by 条件
asc 升序(默认)    desc 降序
select * from students where age between 18 and 36 and gender = 1 order by age;
select * from students where age between 18 and 36 and gender = 1 order by age asc;

    多个字段，首要条件,次要条件,次次要条件...
    select * from students where age between 18 and 36 and gender = 2 order by age asc, height desc, id desc;

-分页-limit start, count 
    -- start: 表示从哪里开始查询, start 默认值为0, 可以省略, 表示跳过多少条数据 ->(pagenum - 1) * count
    -- count: 表示当页需要查询多少条
    -格式：limit (pagenum - 1) * count, count;
    第1页
    select * from students limit 4;
    select * from students limit 0,4;

    第2页
    select * from students limit 4,4;

    --例子：按照年龄从小到大排序，每页显示5个，显示第3页的信息 
    select * from students order by age asc limit 10,5;

-SQL语句格式先后排序(权重)
-- 简单小结：
    条件查询(比较、逻辑、模糊、范围、空)
    分组、分组后进一步筛选
    排序
    分页

    select distinct * ,[group_concat(name, age)],[round(avg(age), 2)],[聚合统计函数]
    from 表名
    where ....
    group by ... having ...
    order by ...
    limit start,count

-逻辑删除  标记这条数据在某些情况不能够被显示
    -使用bit 类型来实现，bit 是二进制数据，只有 0 1 
    --咨询牛哥后，一般默认表中出现 0001就是逻辑删除，表中显示 什么都没有 就是没有逻辑删除，跟最初表结构定义默认值无关
    -- 查询students中没有逻辑删除的学生信息
    select * from students where is_delete = 0;

-数据库备份和恢复(mysqldump, mysql)
    -备份：mysqldump 命令
    --python_test_bf.sql 如果不存在会自动创建
    mysqldump -uroot -p python_test > ~/Desktop/python_test_bf.sql

    -- mysqldump -udianfubao -h192.168.212.101 -p noah > ~/Desktop/noah.sql

    -恢复 mysql 命令
    --如果没有demo 这个数据库，必须先连接mysql，创建一个数据库demo ，然后退出连接到终端：
    --如果有demo 这个数据库，里边表相同的会先删除在恢复，表不同的保留不会遗失：
    mysql -uroot -p demo < ~/Desktop/python_test_bf.sql

    -- mysql -uroot -p cmdb < ~/Desktop/noah.sql

    -升级版 如：从mac 备份整个完整的全部数据库(包括系统自带数据库) 到mac 桌面，并恢复到ubuntu 中，这样ubuntu 数据库会和mac 数据库完全一样
    -完整数据库备份/恢复-这种操作，与上边是不同的
        -备份
        --all-databases ：导出所有数据库
        --lock-all-tables ：执行操作时锁住所有表，防止操作时有数据修改
        mysqldump -uroot -p --all-databases --lock-all-tables > ~/Desktop/master_db.sql
        
        -拷贝转移 mac 桌面 -> ubuntu 桌面
        scp ~/Desktop/master_db.sql python@192.168.106.127:/home/python/Desktop/

        -恢复
        mysql -uroot -p < ~/Desktop/master_db.sql   这个指令需要手动敲入

mysql数据库-表1-表2-表3...-间的操作处理-属于条件查询处理范畴-参考上边SQL语句格式先后顺序
-连续查询
    -内连接查询
    ... join ... on ...
    select * from students join classes on students.cls_id = classes.id;
    select s.name,c.name from students as s join classes as c on s.cls_id = c.id;

    -外连接查询
    --左外连接：左边的表作为主表，主表的数据都会显示(满足连接条件+不满足连接条件)，右边的表作为副表，不满足连接条件的数据会以NULL填充 
    ... left join ... on ...
    select * from students left join classes on students.cls_id = classes.id;

-自关联  一种特殊的内连续查询 --> 自己关联自己 a join a   -->应用场景：无限分级(上下级)
    -- 将一个表，想象成两个表，将两个表中的数据按照设置的连接条件进行筛选, 符合连接条件的数据才能够被筛选出来
    -以 python_test_0 中表 areas 为例：
    --省级联动 url:http://demo.lanrenzhijia.com/2014/city0605/
    --省/直辖市 -> 市/区 ->区、县/街道 --> (上下级，却在一个表中)
    -- 创建一个数据表areas
    create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
    );

    向表中导入数据：source ~/Desktop/areas.sql
    -- 查询所有省份/直辖市
    select * from areas where pid is null;
    -- 查询出广州市有哪些区县
    select p.atitle, c.atitle from areas as p join areas as c on c.pid = p.aid where p.atitle = '河北省';
    select p.atitle, c.atitle from areas as p join areas as c on p.aid = c.pid where p.atitle = '石家庄市';

-子查询
-- 定义：在一个 select 语句中,嵌入了另外一个 select 语句, 那么 被嵌入的 select 语句称之  子查询语句
    --子查询中特定关键字使用
        in 范围
        格式1: 主查询 where 条件 in (列子查询(属性))
        格式2: 主查询 where (字段1，字段2...) = (子查询为行级子查询 - 单行 n 列(记录))
        格式3: 主查询 from (子查询语句 - 多行多列(表)) as 别名        -- 这种 必须得起个别名 这很重要 因为此时子查询结果是一个表

    格式1:
    select * from students where age in (select age from students where age in (18,18,25,36,38,40)); 

    格式2:
    select * from students where (height) > (select avg(height) from students);
    select * from classes where id not in (select cls_id from students);

    格式3: -- 直接当作 表 来使用，必须起 别名
    select * from (select * from students) as tmp;

-\G 将查询的数据的所有 列单独成行 显示，即显示一条记录，一条一条显示出来，对于大大量数据可以更好的观看：
    select * from areas where pid is null\G;

-SQL练习
-以 jing_dong_test 数据库为例：
-感受下，具体想看，去看看原来资料吧
-- 查询每种类型中最贵的商品信息
方法1: select g.* from goods as g join (select cate_name,max(price) as max_price from goods group by cate_name) as tmp on g.cate_name = tmp.cate_name and g.price = tmp.max_price;
方法2: select * from goods where (cate_name, price) in (select cate_name,max(price) from goods group by cate_name);

-@以上都是SQL语句的经常使用几乎所有方法，下边是在代码中，经常用到的
mysql数据库，在web 服务器开发中，一般除了直接执行SQL语句外，经常配合 “视图、事务、索引(面试)” 来使用，此外，和“外键、关键字”(较少用)等 最详情可去看 -> 00-mysql_第四天笔记.sql

- 视图-view (对于一个SQL查询语句的封装)
    -- 问题：
        对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为某些原因发生了改变，如数据表名字改变了等，
        为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦
    -- 定义：
        通俗的讲，视图就是  一条SELECT语句执行后返回的结果集 作为一张虚拟的表 是对基本表的引用
        注意：一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变)

    -- 创建视图   ->建议以 v_ 开头命名
    create view 视图名称 as select语句;
        create view v_students as select * from students;

    -- 查看/使用视图 -> 都当作一个表来使用即可 ->"中介公司"

    -- 删掉视图
    drop view 视图名称;

    - 作用：
    -- 提高了重用性，就像一个函数
    -- 对数据库重构，则却不影响程序的运行(即:若数据表名字变了,但如果开发使用的是视图,只需改视图里的这个表名就可以了,维护很方便)
    -- 提高了安全性能，可以对不同的用户
    -- 让数据更加清晰，减少复杂的SQL语句，增强可读性
    重重重点:
    如: Django框架,内部封装一些很常用到的SQL查询的结果集,可以封装成一个视图,作为开发时查询使用,可以减少一些SQL语句编写;

-事务 (begin, commit, rollback)
    -- 应用场景
        -- 事务广泛的运用于订单系统、银行系统等多种场景

    -- 定义：
        所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位

        把一个多步骤任务打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤，回到事务创建前的状态
    -- 引擎
        表的引擎类型必须是 innodb 类型才可以使用事务，这是mysql表的默认引擎
 
    -- 开启事务     ->执行命令的变更，会维护到本地缓存中，而不维护在物理表中
        begin;   或者   start transaction;

    -- 进行一系列操作
        update students set name='大月月',age=20,height=184 where id = 2;
        select * from students;

    -- 提交事务     ->将缓存中的数据变更维护到物理表中
        commit;

    -- 回滚事务     ->放弃缓存中变更的数据，回到事务开启前的状态
        rollback;

    -- 作用：
        1、平时在mysql 中修改 表中数据 的命令其实是会自动的触发事务(自动开启，自动提交)，包括 insert、update、delete
        2、而在SQL语句中使用 手动开启事务 的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

    -- 注意：
        事务 只对数据表中的数据有效，针对数据行加锁，所操作数据行无法被另一用户操作，对于 数据库的操作和表结构 的更改无效

    -- 总结：
        事务的三大指令:
            开启事务: begin;
            提交事务: commit;
            回滚事务: rollback;

        事务的四大特征(简称 ACID )  -> 经常面试会被提问 ! ! !
            1. 原子性： 一个事务必须被视为一个不可分割的最小工作单元，要么成功要么失败
            2. 一致性：状态切换，只要没有commit 即使系统崩溃等情况，事务所做修改无效，回到开启前状态
            3. 隔离性：一个事务中所做的一切修改，只要还没有commit ，对其他事务都是不可见的(看不出修改)
                这里,格外说下,Django框架默认也是这种隔离级别,在项目中,对于乐观锁处理数据库,需要其他事务可以看见,要修改级别;
            4. 持久性：只要事务commit ，所有的修改都会永久保存到数据库，即使之后系统崩溃，也不会丢失

- 索引-(大大重点)
    -- 定义
    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针

    -- 目的
    - 提高查询的速度
    - 只需要对合适的字段创建索引(重查询)
    - 索引会额外的占用磁盘空间, 会降低表的更新速度

    01 查看索引
    show index from 表名;

    02 创建索引
        - 注意：
            如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
            字段类型如果不是字符串，可以不填写长度部分
    create index 索引名称 on 表名(字段名称(长度))

    03 删除索引
    drop index 索引名称 on 表名;

    - 索引实例

    1. 准备大量的数据 100w
    -- 创建数据库test0
    create database test0 charset=utf8;

    -- 创建测试表test_index
    create table if not exists test_index (
        id int unsigned primary key auto_increment not null,
        title varchar(10)
    );

    在python中通过for 循环来实现
    -> day15_h - 01-表_索引_准备数据.py
    在ubuntu 运行，导入数据

    2. 通过主键字段查询最后一条数据, 并且记录查询的时间
    需要开启时间检测
    set profiling=1;
    select * from test_index where id = 1000000;

    3. 通过非主键(没有索引) 来查询最后一条数据, 并且记录查询的时间
    select * from test_index where title = "ha-999999";

    查看每个sql指令执行消耗的时间
    show profiles;

    4. 给非索引字典添加索引 ->名称：title_index, 查询最后一条数据, 获取是时间
        -- 如果给字符串类型的字段添加索引的时候, 需要设置字段的长度, 最好和创建的时候设置的长度相等, 非字符串的字段就不需要写长度
    create index title_index on test_index(title(10));

    5. 查看下，目前表有几个索引
    show index from test_index;

    6. 给有索引的字段再做测试，对比时间
    需要开启时间检测
    set profiling=1;
    - 两者查询速度已经到了一个量级 ! !
        select * from test_index where id = 1000000;
        select * from test_index where title = 'ha-999999';

    查看每个sql指令执行消耗的时间
    show profiles;

    - 索引原理:    有无索引的区别
    select * from test_index where title = "ha-999999";
        -- 将数据表的数据从硬盘读取到内存, 在内存中进行数据逐条的比较
        -- 不要重复多次的进行磁盘的IO, 效率很低, 内存(二级缓冲)

    select * from test_index where id = 1000000;
        -- 主键字段有索引
        -- 将主键字段的 索引读取内存
        -- 索引是按照  一定顺序排列(BTREE 平衡树), 有序, 就决定查询速度非常快,查询索引非常快,可以通过索引直接定位到数据,
        -- 42亿: 无序, 最悲观(最后一个)--> 42亿
        -- 42亿: 有序, 最悲观(二分式：一半一半...)--> (42亿/2/2/2...)32次, BTREE比32次还要低，即BTREE会更快

    时间复杂度: 都是最悲观的那种情况.

    - 树结构 - 数据结构(重重点 ! ! ! 如: 冒泡排序, 快速排序) - 资料：
    binary tree -> 二叉树
    balance tree -> 平衡树
    对比：
    1. 平衡树比二叉树更快
    2. 平衡树(矮且胖)、二叉树(高且瘦)

    - 总结：
    1. 非索引字段, 将数据从硬盘读取到内存,并且一条一条的比对 这需要消耗性能, 导致查询很慢
    2. 创建了索引之后, 索引会按照  树结构(BTREE)排序(有序), 通过有索引字段查询数据的时候
    3. 读取索引根节点, 磁盘IO的数量也会小很多, 将数据读取到内存进行数据的比较, 快速找到对应的数据索引位置, 再通过索引定位到数据
    4. 数据库的索引查找的复杂度 比logn还要低

下一节
索引  账户管理  mysql数据库服务器主从 等