美文网首页
mysql 基础学习笔记

mysql 基础学习笔记

作者: 小生不才_ | 来源:发表于2020-07-09 16:53 被阅读0次

    安装

    MacOS

    Windows 10

    Centos 7

    SQL规范

    1. 不区分大小写,但是建议大写关键词,小写表名列名
    2. 每条SQL建议分号结尾
    3. 每条SQL根据需要进行换行缩进
    4. 注释:
      单行:# --
      多行:/* */

    类型

    数值:
        整型:
        小数:
            定点数
            浮点数
    字符:
        短字符: char varcahr 
        长文本: text blob
    日期:
        date        2020-02-03
        datetime    2020-02-02 02:02:02
        timesiamp   1594279093389
        time        02:02:02
        year        2020
    

    常用SQL

    use test; -- 选中 数据库
    show tables; -- 现实当前选中的库的所有表
    
    show tables from mysql; # 查询mysql下的tables
    
    SHOW INDEX FROM stuinfo; # 显示当前的索引
    
    select database(); # 查看当前库
    
    /* create table table1(
        id int,
        name varchar(24)
    ); */
    
    desc table1; -- 查看表结构
    
    select * from table1;
    
    insert into table1 (id,name) values(1,'测试'); -- 插入
    
    update table1 set name='我靠' where name='ces'; -- 修改
    
    update table1 set id=0 where name='我靠'; -- 修改
    
    delete from table1 where name='我靠'; -- 删除
    
    
    

    常见函数

    单行函数

    处理

    字符函数

    SELECT LENGTH('我是谁'); -- 根据当前字符集 得到当前字节长度
    
    SELECT CONCAT('我','是','谁呀'); -- 拼接字符串
    
    SELECT UPPER('Abc'); -- 转换成大写字符
    
    SELECT LOWER('Abc'); -- 转换成小写
    
    SELECT SUBSTR('abc123一二三',4,3); -- 从4开始截取3个 包含4 索引从1开始
    
    SELECT SUBSTRING('abc123一二三',4,3); -- 从4开始截取3个 包含4 索引从1开始
    
    SELECT INSTR('01234556','234'); -- 查找字符串出现的位置 没找到就是0
    
    SELECT TRIM(' A B C D '); -- 去除前后空格
    
    SELECT TRIM('a' FROM 'aaaaA B  CaaaDaaaa' ); -- 去除前后的a
    
    SELECT LPAD('abc123一二三',20,'*'); -- 左填充/保留左边的
    SELECT RPAD('abc123一二三',20,'*'); -- 右填充/保留左边的
    
    

    数学函数

    SELECT ROUND(0.4); -- 四舍五入
    SELECT ROUND(0.5);  -- 四舍五入
    SELECT ROUND(-0.4);  -- 四舍五入
    SELECT ROUND(-0.5);  -- 四舍五入
    
    SELECT CEIL(0.2); -- 向上取整
    SELECT FLOOR(0.9); -- 向下取整
    
    SELECT RAND(); -- 随机数
    
    SELECT TRUNCATE(0.2345,3); -- 保留多少位小数 不进行处理
    
    SELECT MOD(10,3); -- 取余
    
    

    日期函数

    SELECT NOW(); -- 返回当前的日期时间
    
    SELECT CURDATE(); -- 返回当前的日期
    
    SELECT CURTIME(); -- 返回当前时间
    
    SELECT YEAR(NOW()) as `year`, MONTH(NOW()) as `month`, DAY(NOW()) as date as `day`; -- 年/月/日
    
    SELECT STR_TO_DATE('2020-03-23 22:32:12','%Y-%m-%d %H:%i:%s'); -- 将字符串解析成时间
    
    SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'); -- 格式化时间
    

    其他函数

    SELECT VERSION(); -- 查看版本号
    
    SELECT DATABASE(); -- 查看当前的库
     
    SELECT USER(); -- 当前用户
    

    流程控制函数

    SELECT IF(10<5,'大','小'); -- if
    
    SELECT `last_name`, IF(`commission_pct` IS NULL,TRUE,FALSE) AS isPct from `employees` ORDER BY `isPct` DESC; -- if 例子
    
    # case 
    SELECT `salary`,`department_id`,
    CASE department_id
        WHEN 80 THEN
            salary * 1.2
            WHEN 40 THEN
            salary * 1.9
        ELSE
            salary * 0
    END AS newMoney 
    FROM `employees`
    ORDER BY department_id DESC;
    
    

    统计函数

    统计

    SELECT COUNT(*) FROM `employees`; -- 数量统计
    SELECT SUM(`salary`) FROM `employees`; -- 相加和
    SELECT AVG(`salary`) FROM `employees`; -- 平均值
    SELECT MAX(`salary`) FROM `employees`; -- 最大值
    SELECT MIN(`salary`) FROM `employees`; -- 最小值
    
    SELECT COUNT(*) AS `count`, SUM(`salary`) AS `sum`, AVG(`salary`) AS `avg`, MAX(`salary`) as `max`, MIN(`salary`) as `min`
    FROM `employees`;
    
    # 注意
    /*
    
    */
    

    常见约束

    一种限制,用于限制表中的数据,用来保证表中数据的准确和可靠性

    
    分类:
        六大约束:
            NOT NULL: 非空,用于保证该字段的值不能为空
            DEFAULT: 默认值
            PRIMARY KEY: 主键,用于保证该字段具有唯一性(非空)
            UNIQUE: 唯一(可空)
            CHECK: 检查 (mysql 不支持)
            FOREIGN KEY: 外键,用于限制两个表的关系,用于保证该字段必须来自关联表的主键
        添加约束的时机:
            1. 创建表
            2. 修改表
        约束的添加分类:
            列级约束:
                六大约束语法上都支持,外键约束无效
            表级约束:
                除了非空和默认其他都支持
                
    主键和唯一的区别:
        主键:唯一、非空、只能一个
        唯一:唯一、可空、多个
    
    外键:
        1. 从表设置外键关系
        2. 主从表类型一致/兼容
        3. 主表关联键一般为主键或唯一
        4. 必须对应主表数据,删除先删除从表再删除主表
    

    DQL 数据查询语言

    常量、表达式、函数

    SELECT 1; -- 常量值
    SELECT 10*20; -- 表达式
    SELECT VERSION(); -- 函数
    

    别名

    SELECT 1+2 as number;
    

    去重

    SELECT DISTINCT 
        `name`
    FROM 
        `table`
    

    +号

    SELECT 1+2; -- 数字相加
    SELECT 1+'123'; -- 字符串会强转成数字非数字转为0
    SELECT 1 + Null; -- 与Null返回Null
    

    字符串连接 concat

    SELECT CONCAT('a','b','c'); -- 字符串拼接
    SELECT CONCAT(`first_name`,`last_name`) as `name` FROM `employees`; -- 拼接字段
    

    条件查询

    条件表达式

    < > >= <= != <> <=>

    # 等于
    SELECT 
        CONCAT(`first_name`,`last_name`) as `name` 
    FROM `employees` 
    WHERE 
        `first_name`='Bruce'; 
    
    # 安全等于 可查 Null
    
    SELECT 
        CONCAT(`first_name`,`last_name`) as `name` 
    FROM `employees` 
    WHERE 
        `first_name`<=>'Bruce'; 
    
    # 大于
    SELECT 
        *
    FROM `employees` 
    WHERE 
        `department_id` > 60;
    
    # 小于
    SELECT 
        *
    FROM `employees` 
    WHERE 
        `department_id` <= 60;
        
    # 不等于 
    # != 不建议
    SELECT 
        *
    FROM `employees` 
    WHERE 
        `department_id` <> 60;
    
    逻辑表达式

    && || !
    AND OR NOT

    # 且查询
    # 不建议 &&
    SELECT 
    CONCAT(`first_name`,`last_name`) as `name`
    FROM `employees` 
    WHERE 
        `first_name`='Bruce' 
        AND 
        `last_name`='Ernst';
    
    # 或
    SELECT 
        CONCAT(`first_name`,`last_name`) as `name` 
    FROM `employees` 
    WHERE 
        `first_name`='Bruce' 
        OR 
        `last_name`='K_ing';
        
    # 非
    SELECT 
        CONCAT(`first_name`,`last_name`) as `name` 
    FROM `employees` 
    WHERE 
        NOT
        `first_name`='Bruce' 
    
    模糊查询
    like 模糊查询

    %:任意多个字符
    _: 任意单个字符
    \: 转义

    # 包含 a
    SELECT * FROM `employees` 
    WHERE 
        `first_name` like '%a%'; 
    
    # 定义转义字符
    SELECT * FROM `employees` 
    WHERE 
        `last_name` like '%$_%' ESCAPE '$'; 
    
    
    between and 范围

    在什么到什么之间,包含前后

    # 查找100 到200 之间的数据
    SELECT * FROM `employees` 
    WHERE 
        `employee_id` BETWEEN 100 AND 200;
    
    in 属于

    查询是否属于某些列表中的某一个

    # 查询是否属于某些列表中的某一个
    SELECT * FROM `employees` 
    WHERE 
        `job_id` IN ('SH_CLERK','AD_ASST','AD_VP');
    
    is null or is not null 是否为Null
    # 查询字段为空的
    SELECT * FROM `employees`
    WHERE
        `commission_pct` IS NULL;
    
    # 查询不为空的
    SELECT * FROM `employees`
    WHERE
        NOT `commission_pct` IS NULL;
    
    SELECT * FROM `employees`
    WHERE
        `commission_pct` IS NOT NULL;
    
    <=> 安全等于

    既可以判断NULL 又可以判断数值

    SELECT * FROM `employees`
    WHERE
        `commission_pct` <=> NULL;
    
    order by 排序

    ASC升序 DESC降序 默认 ASC

    SELECT * FROM `employees` ORDER BY `salary` ASC; -- 升序
    SELECT * FROM `employees` ORDER BY `salary` DESC; -- 降序
    
    SELECT `salary` * IFNULL(`commission_pct`,0) + IFNULL(manager_id,0) as money,`salary`  FROM `employees` ORDER BY `money`; -- 表达式别名降序
    
    SELECT LENGTH(`last_name`) as len FROM `employees` ORDER BY len; -- 按函数
    
    SELECT * FROM `employees` ORDER BY `salary` DESC, `employee_id` ASC; -- 多个排序条件
    

    分组查询

    
    # 每个工种的总工资
    SELECT SUM(`salary`) AS `money`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `money`;
    
    # 每个工种的最高工资
    SELECT MAX(`salary`) as `max`,`job_id` FROM `employees` GROUP BY `job_id` ORDER BY `max`;
    
    # 查询邮箱包含a的工种的最高工资
    SELECT MAX(`salary`) as `max`, `job_id`
    FROM `employees`
    WHERE email LIKE '%a%' 
    GROUP BY `job_id` 
    ORDER BY `max`;
    
    # 查询邮箱包含a的工种的最高工资大于1万的   HAVING
    SELECT MAX(`salary`) as `max`, `job_id` 
    FROM `employees`
    WHERE email LIKE '%a%' 
    GROUP BY `job_id`
    HAVING `max` > 10000
    ORDER BY `max`;
    
    # 查询名称长度一样的大于5个人的数据
    SELECT LENGTH(`first_name`) AS `name`, COUNT(1) AS `count`
    FROM `employees`
    GROUP BY `name`
    HAVING `count` > 5;
    
    # 多字段分组
    SELECT AVG(`salary`) AS `avg`,`department_id`,`job_id`
    FROM `employees`
    GROUP BY `department_id`,`job_id`
    ORDER BY `department_id`,`job_id`
    

    连接查询

    分类:
        按年代划分:
            sql92标准:只支持内连接
            sql99标准【推荐】:支持内连接+外来连接(左外、右外)+交叉连接
        按功能划分:
            内连接:
                等值连接
                非等值连接
                自连接
            外连接:
                左外连接
                右外连接
                全外连接
            交叉连接
    

    sql92标准

    # 等值连接
    SELECT e.`first_name`,j.`job_title`,`j`.`job_id`
    FROM `employees` as e,`jobs` as j
    WHERE `e`.`job_id` = `j`.`job_id`;
    
    # 非等值连接
    
    # 自连接
    SELECT e.last_name,m.employee_id,e.manager_id,m.last_name 
    FROM `employees` AS e, `employees` AS m
    WHERE `e`.employee_id = `m`.manager_id
    

    sql99标准

    # 等值连接
    SELECT `last_name`,`department_name`
    FROM employees
    INNER JOIN departments
    ON employees.`department_id` = departments.`department_id`
    
    # 复杂的等值连接
    SELECT department_name,COUNT('*') AS count,MAX(`salary`) AS max,min(`salary`) AS min
    FROM employees
    INNER JOIN departments
    ON employees.department_id = departments.department_id
    WHERE last_name LIKE '%o%'
    GROUP BY department_name
    HAVING `count` BETWEEN 2 AND 10
    ORDER BY `count`
    
    # 多表等值连接
    SELECT last_name,department_name,job_title
    FROM employees
    INNER JOIN departments
    ON departments.department_id =employees.department_id
    INNER JOIN jobs
    ON employees.job_id = jobs.job_id;
    
    

    子查询

    分类:
        按出现位置:
            select 后面:
                只支持标量子查询
            form 后面
                表子查询
            where 和having后面 *
                标量子查询  *
                列子查询    *
                行子查询
            exists后面
                表子查询
            
        按结果集的行列数不同:
            标量子查询(结果集只有一行一列)
            列子查询(结果集只有一列多行)
            行子查询(结果集只有多列一行)
            表子查询(结果集一般多行多列)
    

    where 和having后面

    特点: 1. 放在小括号内 2. 放在条件右侧 3. 标量子查询:配合单行操作符。列子查询:配合多行操作符。

    
    # 查询工资比Abel高的人
    SELECT * FROM employees 
    WHERE salary > (
        SELECT salary FROM employees 
        WHERE last_name = 'Abel'
    );
    
    # job_id与141号员工一样且工资大于143号员工的员工
    SELECT last_name,salary,employee_id FROM employees
    WHERE job_id = (
        SELECT job_id FROM employees WHERE employee_id = 141
    ) AND salary > (
        SELECT salary FROM employees WHERE employee_id = 143
    )
    
    # 查询比50号部门最低工资高的部门的最低工资
    SELECT department_id, COUNT(*),MIN(salary) AS minsalary FROM employees 
    GROUP BY department_id
    HAVING minsalary > (SELECT MIN(salary) AS minsalary FROM employees WHERE department_id = 50)
    ORDER BY minsalary DESC;
    

    分页查询

    SELECT *
    FROM `employees` 
    LIMIT (page-1)*size,size;
    
    SELECT *
    FROM `employees` 
    LIMIT 10,10;
    

    联合查询

    # 无
    

    DML 数据操作语言

    插入

    INSERT INTO jobs(job_id,job_title,min_salary,max_salary) VALUES('p_a','捡垃圾1',200,6000);
    
    INSERT INTO jobs 
    VALUES('p_a1','捡垃圾1',200,6000),
    ('p_a2','捡垃圾2',200,6000);
    
    INSERT INTO jobs SET job_id = 'ces',job_title="123"
    
    INSERT INTO jobs SELECT 'ces1','444',200,6000
    

    修改

    # 简单修改
    UPDATE jobs
    SET job_title = '2222'
    WHERE job_id = 'ces1'
    
    # 修改多表
    UPDATE jobs
    INNER JOIN employees
    ON employees.job_id = jobs.job_id
    SET jobs.job_title = CONCAT(jobs.job_title,'22222'),employees.job_id = 'ces1'
    WHERE jobs.job_title = 'Public Accountant12322222'
    

    删除

    # 单表删除
    DELETE FROM jobs
    WHERE job_id = 'ces'
    
    # 清空整表
    TRUNCATE TABLE ttt;
    
    # 多表删除
    DELETE employees
    FROM employees
    INNER JOIN jobs
    ON jobs.job_id = employees.job_id
    WHERE jobs.job_id = 'SA_MAN'
    

    DDL 数据定义语言

    1. 库的管理
        创建\修改\删除
    2. 表的管理
        创建\修改\删除
    
    创建: create
    修改: alter
    删除: drop
    

    库管理

    # 创建 IF NOT EXISTS 进行容错 CHARACTER SET  字符集
    CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;
    
    # 修改 不能该了
    RENAME DATABASE books TO newbook;
    
    # 更改字符集
    ALTER DATABASE books CHARACTER SET gbk;
    
    # 库的删除
    DROP DATABASE IF EXISTS books;
    
    

    表的管理

    创建表

    USE books;
    # 表的创建
    /*
    CREATE TABLE 表名(
        列名 列类型([长度]) [约束],
        列名 列类型([长度]) [约束],
        ...
    )
    */
    CREATE TABLE book (
        id INT,
        b_name VARCHAR(20),
        price DOUBLE,
        author_id INT,
        publish_date DATETIME
    );
    
    DESC book;
    
    CREATE TABLE author(
        id INT,
        au_name VARCHAR(20),
        nation VARCHAR(10)
    );
    
    DESC author;
    

    表的修改

    /*
    修改列:
    ALTER TABLE 表名 [CHANGE|MODIFY|ADD|DROP] COLUMN 列名 类型|约束;
    修改表名:
    ALTER TABLE 表名 RENAME TO 新表名;
    */
    # 修改列名
    ALTER TABLE book CHANGE COLUMN publish_date publishDate DATETIME;
    # 修改列类型约束
    ALTER TABLE book MODIFY COLUMN publishDate TIMESTAMP;
    # 添加新列
    ALTER TABLE author ADD COLUMN annual DOUBLE;
    # 删除
    ALTER TABLE author DROP COLUMN annual;
    # 修改表名
    ALTER TABLE author RENAME TO authers;
    

    表的删除

    DROP TABLE IF EXISTS authers;
    

    表的复制

    # 复制表结构
    CREATE TABLE copy LIKE author;
    
    # 复制整个表
    CREATE TABLE copy2 
    SELECT * FROM author;
    

    约束

    添加表时添加约束

    # 列级约束
    CREATE TABLE stuinfo(
        id INT PRIMARY KEY, # 主键
        stu_name VARCHAR(20) NOT NULL, # 非空
        gender CHAR(1) DEFAULT '男', # 默认
        seat INT UNIQUE # 唯一
    );
    
    # 表级约束
    CREATE TABLE stuinfo(
        id INT, # 主键
        stu_name VARCHAR(20), # 非空
        gender CHAR(1) , # 默认
        seat INT, # 唯一
        majorid INT,
        
        CONSTRAINT pk PRIMARY KEY(id),# 主键
        CONSTRAINT uq UNIQUE(seat),# 唯一
        CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
    );
    
    # 通用建议约束
    DROP TABLE  IF EXISTS stuinfo;
    CREATE TABLE stuinfo(
        id INT PRIMARY KEY, # 主键
        stu_name VARCHAR(20) NOT NULL, # 非空
        gender CHAR(1) DEFAULT '男', # 默认
        seat INT UNIQUE, # 唯一
        majorid INT,
        -- 表级约束
        CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
    );
    
    
    

    修改表时的约束

    # 修改列约束
    ALTER TABLE stuinfo MODIFY COLUMN stu_name VARCHAR(20) NOT NULL; 
    # 表级约束
    ALTER TABLE stuinfo ADD PRIMARY KEY(id);
    ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
    # 删除外键
    # ALTER TABLE stuinfo DROP PRIMARY KEY;
    

    标识列(自增长列)

    AUTO_INCREMENT

    CREATE TABLE stuinfo(
        id INT PRIMARY KEY AUTO_INCREMENT, # 主键
        stu_name VARCHAR(20) NOT NULL, # 非空
        gender CHAR(1) DEFAULT '男', # 默认
        seat INT UNIQUE, # 唯一
        majorid INT,
        -- 表级约束
        CONSTRAINT fk_stuiinfo_major FOREIGN KEY(majorid) REFERENCES major(id)# 外键
    );
    

    TCL 事务控制语言

    一个或一组sql语句组成一个执行单位,要么全部执行,要么全部失败。

    ACID: A: 原子性 C: 一致性 I: 隔离性 D: 持久性

    # 提交表示完成,回滚代表异常
    set autocommit = 0; -- 关闭自动事务
    START TRANSACTION; -- 开始事务
    UPDATE stuinfo SET stu_name = '12232' WHERE id = 3;
    SAVEPOINT a; -- 保存节点
    UPDATE stuinfo SET stu_name = '12332' WHERE id = 5;
    ROLLBACK; -- 回滚事务
    ROLLBACK TO a; -- 回滚事务到指定节点
    COMMIT; -- 提交事务 
    

    相关文章

      网友评论

          本文标题:mysql 基础学习笔记

          本文链接:https://www.haomeiwen.com/subject/kwyscktx.html