010 mysql

/*
时间：2021/12/18
功能：MySql
目录: 
    一: mysql
    二: 库操作
    三: 表
    四: 字段/列
    五: 记录/行 
    六: 查询
        1 条件
        2 关键字
        3 排序
        4 分页
        5 聚合函数
        6 分组
        7 连接
    七: 外键
    八: 多表 
    九: 索引
    十: 其他
    十二: 思考
    十三: 问题
*/

一: mysql

-- mysql
    -- 登录 
        mysql -uroot -p
            -u: 表示MySQL服务端的用户名
            -p: 表示MySQL服务端的密码

    -- 退出 
        quit
        exit
    -- 清屏
        system clear;
    -- 显示当前时间 
        select now();

二: 库操作

-- 库操作
    -- 创建: 数据库 
        create database database_name charset=utf8;
        alter database database_name character set utf8; -- 修改编码
    -- 删除: 数据库 
        drop database database_name;
    -- 修改: 使用数据库 
        use database_name;
    -- 查询: 所有数据库 
        show databases;
    -- 查询: 当前使用数据库
        select database();

三: 表

-- 表
    -- 创建表 
        create table name(
        字段名称, 数据类型, 约束条件
        );
        create table students(
            id int unsigned primary key auto_increment not null,
            name varchar(20) not null,
            age tinyint unsigned default 0,
            height decimal(5,2),
            gender enum('男','女')
        );
        CREATE TABLE st(
            id INT UNSIGNED PRIMARY KEY auto_increment NOT NULL,
            name VARCHAR(20) NOT NULL,
            age TINYINT UNSIGNED DEFAULT 0,
            height DECIMAL(5, 2),
            gender enum('男','女')
        );
        
    -- 删除表
        drop table table_name;
        drop table st;        
    -- 清空表
        -- delete : 可恢复
            DELETE FROM table_name;
            DELETE FROM areas;
        -- truncate : 不可恢复
            truncate FROM table_name;
            truncate FROM areas; 
                   
    -- 查询: 创表语句
        show create table table_name;
        show create table st;   
    -- 查询: 所有字段 
        desc table_name;
        desc st;
    -- 查看: 所有表
        show tables;

四: 字段/列

-- 字段/列
    -- 添加: add
        alter table table_name add column_name 数据类型 约束; 
        alter table st add birthday datetime; 
        
    -- 修改: 属性
        -- 1 修改类型: modify
            alter table table_name modify column_name 数据类型 约束; 
            alter table st modify birthday date not null;
        -- 2 修改名称和类型: change
            alter table table_name change column_name new_name 数据类型 约束; 
            alter table st change birthday birth datetime not null;    
    -- 修改: 数值 
        UPDATE <表名> 
            SET <列名> = <表达式>           
        UPDATE st 
            set age=18, height=1.78   
            
    -- 删除: drop  
        alter table table_name drop column_name;
        alter table st drop birth;

五: 记录/行

-- 记录/行 
    -- 添加
        -- 1 全部字段
            INSERT INTO table_name values(字段...);
            INSERT INTO st VALUES(0, '小明', 12, 1.7, '男')
        -- 2 部分字段
            INSERT INTO table_name(column_name) VALUES(column_value);
            INSERT INTO st(id, name) VALUES(98, '小钱');
        -- 3 默认字段
            INSERT INTO table_name(column_name) VALUES(DEFAULT);
            INSERT INTO st(id, name) VALUES(DEFAULT, '小孙');
        -- 4 多行插入
            -- (1) 全部字段
            INSERT INTO st VALUES(DEFAULT, '小周', DEFAULT, 1.69, DEFAULT),
            (DEFAULT, '小吴', DEFAULT, 1.69, DEFAULT);
            -- (1) 部分字段
            INSERT INTO st(id, name) VALUES(DEFAULT, '诸葛'),(DEFAULT, '司马');
        -- 5 复制表 
            --(1) 全部复制
            insert into table1 
                select  * from table2
            --(1) 部分复制
            INSERT INTO table2(id, name)
                SELECT id, name FROM table1;
            
    -- 修改 
        UPDATE <表名> 
            SET <列名> = <表达式>
        WHERE <条件>;
            
        UPDATE st 
            set age=18, height=1.78
        WHERE id = 5;
        
        
    -- 删除
        -- 1 逻辑删除
            ALTER TABLE st ADD is_delete bit DEFAULT 0;
            
            UPDATE st 
                set is_delete=1
            WHERE name = '小明';

        -- 2 物理删除 
            DELETE FROM <表名> 
                WHERE <条件>; 
        
            DELETE FROM st
                WHERE id = 5;

六: 查询
　　1 条件

-- 条件
    -- 比较: = > >= < <= <> !=
    -- 逻辑: and or not 
    -- 模糊: like  
    -- 范围: between...and...; in 
    -- null: is not 

    -- 比较: = > >= <= <> !=
        --: =
        SELECT * FROM st
            WHERE NAME = "小明";
        --: > >= < <=
        SELECT * FROM st
            WHERE height >= 1.7;   
        --: <> != 
        SELECT * FROM st
            WHERE NAME <> "小明";
    
    -- 逻辑: and or not 
        --: and 
        SELECT * FROM st
            WHERE id > 3 AND is_delete = 0
        --: or
        SELECT * FROM st
            WHERE id < 3 or id > 3;
        --: not 
        SELECT * FROM st
            WHERE not(id < 3 or id > 3); 
    
    -- 模糊: like
        --: %
        SELECT * FROM st
            WHERE `name` LIKE "小%";
        --: _
          SELECT * FROM st
            WHERE `name` LIKE "小_";

    -- 范围: 
        --: 1 between...and...
        SELECT * FROM st
            WHERE id BETWEEN 3 and 100;
        --: 2 in 
        SELECT * FROM st
            WHERE id in (3, 5, 100);
    
    -- null: 
        --: 1 is 
        SELECT * FROM st 
            WHERE height is NULL;
        --: 2 is not 
        SELECT * FROM st 
            WHERE height is not NULL;  

　　2 关键字

-- 关键字: as distinct
    -- as: 
        -- 字段 
        SELECT id, name as "姓名"
            FROM st;      
        -- 表
        SELECT s.id, s.name
            FROM st AS s;

    -- distinct
        SELECT DISTINCT name, age FROM st;

　　3 排序

-- 排序 
    -- order by
        SELECT * FROM st 
            ORDER BY id;
    -- order by desc
        SELECT * FROM st 
            ORDER BY id DESC;

　　4 分页

-- 分页
    -- limit 
        select * from 表名 limit start,count
            limit: 分页查询 - 关键字
            start: 开始行索引, 默认0行
            count: 查询条数
            
        SELECT * FROM st LIMIT 3;
        SELECT * FROM st LIMIT 3, 5;
        
    -- n页线索
        -- 每页m条数据，求第n页显示的数据
        -- select * from st limit (n-1)*m, m;

　　5 聚合函数

-- 聚合函数  
    -- count(): 列总行数
    -- max(): 列最大值 
    -- min(): 列最小值
    -- sum(): 列数值和 
    -- avg(): 列数值和

    -- count(): 列总行数
        -- 总行数: 不包含null
            SELECT COUNT(height) FROM st;
        -- 总行数: 包含null
            SELECT COUNT(*) FROM st;    
    -- max(): 列最大值 
        SELECT MAX(height) FROM st;
    -- min(): 列最小值
        SELECT MIN(height) FROM st;
    -- sum(): 列数值和 
        SELECT AVG(height) FROM st;
    -- avg(): 列数值和
        SELECT SUM(height) FROM st;
    -- null 
        select avg(ifnull(height, 0)) from st 
            WHERE gender='男';   

　　6 分组

-- 分组查询
    -- 单字段
        SELECT gender FROM st
            GROUP BY gender;
    -- 多字段
        SELECT name, gender FROM st
            GROUP BY name, gender;         
    -- GROUP_CONCAT : 统计集合 - 分组字段
        SELECT gender, GROUP_CONCAT(id)
            FROM st GROUP BY gender         
    -- 聚合函数
        SELECT gender, AVG(height) FROM st
            GROUP BY gender;
            
        SELECT gender, COUNT(*) FROM st
            GROUP BY gender;          
    -- having
    SELECT gender, COUNT(*)
        FROM st 
        GROUP BY gender
        HAVING COUNT(*) > 3;
    -- with rollup - 统计集合 - 所有分组
    SELECT gender,GROUP_CONCAT(height) 
        FROM st 
        GROUP BY gender 
        with rollup;    

　　7 连接

-- 连接查询
    -- 内连接
       SELECT s.id, s.`name`, s.class_id, c.`name`
            FROM st as s
            INNER JOIN class as c 
            on s.class_id = c.id
    -- 左连接 
        SELECT s.id, s.`name`, s.class_id, c.`name`
            FROM st as s
            LEFT JOIN class as c 
            on s.class_id = c.id
    -- 右连接
        SELECT s.id, s.`name`, s.class_id, c.`name`
            FROM st as s
            RIGHT JOIN class as c 
            on s.class_id = c.id       
    -- 子连接
        -- 导入数据
            create table areas(
                id varchar(30) not null primary key, 
                title varchar(30), 
                pid varchar(30)
            );

            source areas.sql; -- 执行sql文件 

        -- 自查询
            SELECT a1.id, a1.title, a2.title
                FROM areas AS a1 
                INNER JOIN areas AS a2 
                ON a1.pid = a2.id    
                WHERE a2.title = "河北省";

        -- 子查询 
            -- 查询: 省 市
            SELECT * FROM areas 
                WHERE pid = (SELECT id 
                FROM areas 
                WHERE title = "河北省");
            
            -- 平均身高
            select * FROM st 
                WHERE height > (SELECT avg(height) FROM st)
                
            -- 查询学生表中不为空的班级名称
            SELECT `name` FROM class
                WHERE class.id in (SELECT class_id 
                FROM st
                WHERE class_id IS NOT NULL);
            
            -- 年龄和身高都是最大
            SELECT * FROM st
                WHERE (age, height) = (SELECT MAX(age), MAX(height) FROM st);    

七: 外键

-- 外键约束
    数据类型(无符合)
    -- 添加约束
        ALTER TABLE table_name1 add FOREIGN KEY(column_id) REFERENCES table_name2(id);
        ALTER TABLE st add FOREIGN KEY(class_id) REFERENCES class(id);
    
    -- 创造添加 
        CREATE TABLE school(
            id int not null PRIMARY KEY auto_increment,
            name VARCHAR(10)
        );

        CREATE TABLE teacher(
            id int not null PRIMARY key auto_increment,
            name VARCHAR(10),
            s_id int not null,
            FOREIGN key(s_id) REFERENCES school(id)
        );
    
    -- 删除约束 
        想·SHOW CREATE TABLE st;   --获取名称 : 外键  
        ALTER TABLE st DROP FOREIGN KEY st_ibfk_1;  --删除外键  

八: 多表

-- 多表操作 
    -- 复制表 
        --(1) 全部复制
        insert into table1 
            select  * from table2
        --(2) 部分复制
        INSERT INTO table2(id, name)
            SELECT id, name FROM table1;
        --(3) 创建复制 
        CREATE TABLE good_brands(
            id int UNSIGNED PRIMARY KEY auto_increment,
            NAME VARCHAR(40) NOT NULL)
            SELECT brand_name as name FROM goods GROUP BY brand_name; 
            
    -- 更新表值 
        UPDATE goods g INNER JOIN good_cates gc 
            on g.cate_name = gc.`name`
        SET g.cate_name = gc.id
    
    -- 更改名称: 字段  
        ALTER TABLE goods change brand_name brand_id int not null 
        CHANGE brand_name brand_id int NOT NULL;

九: 索引

-- 索引
    -- 查看索引
        show index from 表名;
        SHOW INDEX FROM test_index;       
    -- 添加索引 
        alter table 表名 add index 索引名(列名, ..)
        alter table classes add index my_name (name);       
    -- 删除索引 
        alter table 表名 drop index 索引名
        alter table classes drop index my_name;
    -- 联合索引: 最左原则;

-- 开启运行时间监测：
    set profiling=1;
-- 查找第1万条数据ha-99999
    select * from test_index where title='ha-99999';
-- 查看执行的时间：
    show profiles;
-- 给title字段创建索引：
    alter table test_index add index (title);
-- 再次执行查询语句
    select * from test_index where title='ha-99999';
-- 再次查看执行的时间
    show profiles;

+----------+------------+--------------------------------------------------+
| Query_ID | Duration   | Query                                            |
+----------+------------+--------------------------------------------------+
|        1 | 0.02104125 | select * from test_index where title = 'h_99999' |
|        2 | 0.09241275 | alter table test_index add index(title)          |
|        3 | 0.00020575 | select * from test_index where title = 'h_99999' |
+----------+------------+--------------------------------------------------+

十: 其他

-- 其他
    -- 保留小数 - 两位
        SELECT ROUND(avg(price),2)
            FROM goods;
    -- 行数 
        select count(*) from st;

-- 行列转换(纵横表):
    select * from scores;

    SELECT [姓名],
        SUM(CASE [课程]
        WHEN '语文' THEN [分数]
        ELSE 0
        END) AS '语文',
        SUM(CASE [课程]
        WHEN '数学' THEN [分数]  
        ELSE 0
        END) AS '数学',
        SUM(CASE [课程]
        WHEN '物理' THEN [分数]
        ELSE 0
        END) AS '物理'
        FROM scores
        GROUP BY [姓名]

 

十二: 思考

-- 组成: 行/列/表/库/系统
        单表操作: 增删改查
        多表操作: 增删改查
        
-- sql:
    表的修改: 在列和行。
    列行查询: 在表
    列操作: alter; 
        
-- show/select
    show: 查询所有库/表; 创表语句; 索引
    
-- delete/drop:
    delete： 清空表; 清空行;
    drop: 删除库; 删除表; 删除列; 删除索引;

十三: 问题

-- 数据库系统三部曲(战德臣); 《SQL基础教程》; 《SQL进阶教程》; 《MySQL基础教程》; 《SQL必知必会》; 老男孩DBA