mysql数据库学习笔记

数据库的基本概念

1.数据库的英文单词：Database 简称:DB
2.什么是数据库？
    用于存储和管理数据的仓库。
3.数据库的特点：
    1.持久化存储数据的，其实数据库就是一个文件系统
    2.方便存储和管理数据
    3.使用统一的方式操作数据库---sql

SQL

1.SQl通用语法
    1.SQL语句都是可以单行或多行书写，以分号结尾的
    2.可以使用空格或者制表符增强语句的可读性
    3.Mysql不区分大小写，建议关键字使用大写
    4. 3 种注释
        单行注释-- （中间用空格隔开）注释内容或#注释内容
        多行注释/* 注释内容 */
2.分类
    DDL(操作数据库，表)
    DML(增删改表中的数据)
    DQL(查询表种的数据)
    DCL(授权)

3.DDL(操作数据库，表)

1.操作数据库 CRUD

        1.C create创建
            *创建数据库
                craete database 数据库名称;
                craete database if not exists 数据库名称;  //判断是否存在
                craete database 数据库名称 character set gbk;  //创建数据库并指定字符集
            *创建一个数据库 判断是否存在并指定字符集
                create database if not exists 数据库名称 character set gbk;
        2.R Retrieve 查询
            *查询所有数据库的名称
                show databases;
            *查看某个数据库的字符集：查询某个数据库的创建语句
                show create database 数据库名称；
        3.U Update 修改
            *修改数据库的字符集
                alter database 数据库名称 character set utf8;
        4.D Delete 删除
            *删除数据库
                drop database 数据库名称;
            *判断数据库是否存在并进行删除
                drop database if not exists 数据库名称;
        5.使用数据库
            *查询当前正在使用的数据库
                select database();
            *使用数据库
                use 数据库名称；

2.操作表

         1.C create创建
            1.语法
                create table 表名(
                    列名1 数据类型,
                    .....,
                    列名1 数据类型
                );
                *注意最后一列不需要加逗号
                *数据库类型：
                    1.int 类型
                        age int
                    2.double 小数类型
                        score double(5,2)
                    3.date 日期
                    4.datetime 日期，包含年月日时分秒
                    5.timestamp 时间戳类型，包含年月日时分秒
                        *如果将来不给这个字段赋值，或赋值为null,则默认使用当前的系统时间，来自动赋值
                    6.varchar  字符串
                        *name varchar(20):姓名最大长度20个字符
                *创建表
                    create table student(
                        id int,
                        name varchar(32),
                        age int,
                        score double(4,1),
                        birthday date,
                        insert_time timestamp
                    )
                *复制表名
                    create table 表名 like 被复制的表名;
         2.R Retrieve 查询
            *查询某个数据库种所有表的名称
                show tables;
            *查询表结构
                desc 表名;
         3.U Update 修改
            1.修改表的表名
                *alter table 表名 rename to 新的表名;
            2.修改表的字符集
                *alter table 表名 character set 字符集名称;
            3.添加一列
                *alter table 表名 add 列名 数据类型;
            4.修改列名称，类型
                名称和类型同时更改
                    *alter table 表名 change 列名 新列名 新的数据类型
                只修改类型
                    *alter table 表名 modify 列名 新的数据类型
            5.删除列
         4.D Delete 删除
            *删除表
                *drop table if exists 表名;
            *删除列
                *alter table 表名 drop 列名;

4.DML:增删改表中的数据

    1.添加数据：
        *语法
            * insert into 表名(列名1，列名2...) values(值1,值2...)
        *注意：
            1.列名和值一一对应
            2.如果表名后不定义列名，则默认添加所有列的值
            3.除了数字类型，其他类型需要用引号（单双都可以）引起来
    2.删除数据
        *delete from 表名 where 条件
        注意：
            1.如果不加条件，则删除表中的所有数据
            2.如果要删除所有记录
                1.delete from 表名  -- 不推荐使用  效率低
                2.TRUNCATE TABLE stu; -- 删除表，并创建一个一摸一样的空表  推荐使用效率高
    3.修改数据
        *语法
            *update 表名 set 列名 数据 where 条件

5.DQL：查询表中的数据

    *select *from 表名
    
    1.语法：
        select
            字段列表
        from
            表名列表
        where
            条件列表
        group by 
            分组字段
        having
            分组之后的条件
        order by
            排序
        limit 
            分页限定
    2.基础查询
        1.多个字段的查询
            select 字段名1，字段名2...from表名;
            *注意：
                *如果查询多个字段，则可以使用 * 代替字段列表
        2.去除重复
            *distinct
        3.计算列
            *一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
            *ifnull(表达式1，表达式2)：null参与运算，计算结果都为null
                *表达式1：哪个字段需要判断是否为null
                *如果该字段为null后的替换值
        4.起别名
            *as:也可以省略
    3.条件查询
        1.where 子句后跟条件
        2.运算符
            * > < <= = <>
            *between   and 在什么之间
            *in (集合)
            * like  模糊查询
                占位符
                    _ 单个任意字符
                    % 多个任意字符
                    
            *i是null 
            and &&
            or ll
            not !

6,DQL查询

1.排序查询

        * 语法 order by 子句
            order by 排序字段一 排序方式1 排序字段2  排序方式2
        *排序方式：
            *ASC：升序，默认的
            *DESC: 降序
        *注意：
            *如果有多个条件的排序，则当前面的条件值一样时，才判断第二条件

2.聚合函数： 将一列数据作为一个整体，进行纵向的计算。

        1.count:计算个数
            1.一般选择非空的列：主键
            2.count(*)
        2.max：计算最大值
            
        3.min: 计算最小值
        4.sum：求和计算
        5.avg：计算平均值
        *聚合函数的计算 不包含null
            解决方案：
                1.选择不包含非空的进行计算  主键
                2.IFNULL函数

3.分组查询

        1.语法：group by 分组字段；
        2.注意：
            1.分组之后查询的字段：分组字段  
                -- 按照性别分组  分别查询男同学女同学的平均分
                SELECT sex,SUM(math),COUNT(NAME),AVG(math)
                FROM student
                GROUP BY sex
            2.where和having 的区别？
                1.where在分组之前进行限定，如果不满足条件，则不参与分组。having 在分组之后进行限定，如果不满足结果则不会被查询出来
                2.where 后不能跟聚合函数   having后可以跟集合函数

4.分页查询

        1.语法
            limit 开始的索引，每页的条数
        2.-- 公式：开始的索引 = （当前的页码-1）*每页显示的条数
            -- 每页显示3条
            SELECT *
            FROM student
            LIMIT 0,3
            
            SELECT *
            FROM student
            LIMIT 3,3
            
            SELECT *
            FROM student
            LIMIT 6,3
        3.分页操作limit是mysql方言

7.约束

    *概念：对表中的数据进行限定，确保数据的正确性，有效性和完整性
    *分类
        1.主键约束 primary key
        2.非空约束 not null
        3.唯一约束 unique
        4.外键约束 foreugn key

*非空约束

        1.在创建表时添加约束
            CREATE TABLE stus(
            	id INT,
            	NAME VARCHAR(20) NOT NULL -- name 为非空
            )
        2.创建完成后，添加非空约束
            ALTER TABLE stus MODIFY NAME VARCHAR(20) NOT NULL
        3.删除表的约束
            ALTER TABLE stus MODIFY NAME VARCHAR(20)

*唯一约束 unique ，某一列不能有重复值

        1.注意：
            *唯一约束可以有null值，但是只能有一条记录为null
        2-- 在创建表时添加约束
            CREATE TABLE stus(
            	id INT,
            	phont VARCHAR(20) UNIQUE -- 手机号
            )
        3.-- 删除唯一约束
            ALTER TABLE stus DROP INDEX phont
        4.创建完成后，添加非空约束
            ALTER TABLE stus MODIFY phont VARCHAR(20)

*主键约束：primary key

        1.注意：
            1.含义：非空且唯一
            2.一张表只能有一个字段为主键
            3.主键是表中记录的唯一标识
        2.在创建时添加主键
            CREATE TABLE stus(
            	id INT PRIMARY KEY, -- 添加主键
            	NAME VARCHAR(20) 
            
            )
        3.-- 删除主键
            ALTER TABLE stus DROP PRIMARY KEY
        4.-- 创建完表之后添加主键
            ALTER TABLE stus MODIFY id INT PRIMARY KEY
        5.自动增长
            *概念：如果某一列是数值类型的，使用 auto_increment 可以完成值的自动增长
            1.创建表时添加自动增长
                CREATE TABLE stus(
                	id INT PRIMARY KEY AUTO_INCREMENT, -- 添加主键
                      	NAME VARCHAR(20) 
                )
            2.-- 添加自动增长
                ALTER TABLE stus MODIFY id INT AUTO_INCREMENT
            3.-- 删除自动增长
                ALTER TABLE stus MODIFY id INT

*外键约束 foreign key，让表与表之间产生关系，从而保证数据的正确性

        1.在创建表时添加外键
            *语法
                creat table 表名(
                    ....
                    外键列 
                    constraint 外键名称  foreign key  外键列名称 reference  主表名称（主表列名称）
                )
        2.删除外键
            ALTER TABLE employee DROP FOREIGN KEY emp_dep_fk
        3.在创建表之后添加外键
            ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id)
        4.级联操作
            -- 在表创建之后 添加外键  设置级联的更新  设置级联删除
                ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id)  ON UPDATE CASCADE --级联更新
                 ON DELETE CASCADE --级联删除

数据库的设计

8.多表之间的关系

1.一对一关系：如 人和身份证
2.一对多关系，多对一  
    如 部门和员工 
3.多对多关系
    如 学生和课程

1.实现关系

    1.一对多（多对一）
        *实现方式：在多的一方建立外键，指向一的一方的主键
    2.多对多
        *实现方式：多对多的关系实现需要借助第三张中间表，中间表至少要包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键（联合主键）
    3.一对一
        *实现方式 一对一关系实现，可以在任意一方添加外键，指向另一方的主键  并且让外键唯一Unique

9.范式

*概念：设计数据库需要遵循的一些规范，要遵循后面的范式要求，必须先遵循前面的范式
    设计数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈次规范，越高的范式数据库冗余越小
    目前关系数据库有六中范式（1NF）第二范式（2NF）第三年范式（3NF）巴斯克德范式（BCNF）第四范式（4NF）第五范式（5NF）又称完美范式
*分类
    1.第一范式（1NF）每一列都是不可分割的原子数据项
    2.第二范式(2NF):在1NF的基础上，非码属性必须完全依赖候选码
    3.第三范式：在2NF基础上，任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)

10.数据库的备份和还原

1.命令行
    *语法
        *备份： mysqldump - u  用户名 -p 密码  数据库名称> 保存路径
        *还原：
            1.登录数据库
            2.创建数据库
            3.使用数据库
            4.执行文件 source 文件路径

多表查询

*查询语法
    select 
        列名列表
    from
        表名
    where
        条件

*多表查询的分类

1.内连接查询

        1.隐式内连接
            -- 查询员工表的名称，性别，部门表的名称
            SELECT emp.`NAME`,emp.`gender`,dept.`NAME`
            FROM dept,emp
            WHERE emp.`dept_id` = dept.`id`
            
            
            SELECT 
            	t1.name,  -- 员工表的名称
            	t1.gender,-- 员工表的性别
            	t2.name	  -- 部门表的名称
            FROM
            	emp t1,dept t2
            WHERE 
            	t1.dept_id = t2.id
        2.显式内连接
            *语法：
                select 字段列表
                from 表名 inner join  表名  ON 条件
                
                SELECT *
                FROM emp INNER JOIN dept ON  emp.`dept_id` = dept.`id`
        3.内连接查询：
            1.从哪些表中查询数据
            2.条件是什么
            3.查询哪些字段
            4.查询的两个表的交集部分

2.外连接查询

        1.左外连接
            * 语法：
                select 字段列表 from 表1 left join 表2 ON 条件；
            *查询的是左表的所有数据及其交集
            
        2.右外连接
        * 语法：
                select 字段列表 from 表1 right join 表2 ON 条件；
            *查询的是右表的所有数据及其交集

3.子查询

*概念：
    查询中嵌套查询，称子查询
    
        --  查询工资最高的员工信息
        
        -- 查询最高的工资
        SELECT MAX(salary)
        FROM emp
        
        -- 查询员工信息
        SELECT * 
        FROM emp
        WHERE emp.`salary` = 9000
        
        -- 一条语句查询
        SELECT *
        FROM emp
        WHERE emp.`salary` = (SELECT MAX(salary)FROM emp)
*子查询的不同情况
    1.子查询的结果是单行单列的
        *子查询可以作为条件，使用运算符去判断 运算符>< >= <=
            -- 查询员工工资小于平均工资的人
            SELECT * 
            FROM emp  WHERE emp.`salary`<(
            SELECT AVG(salary)
            FROM emp
            )
    2.子查询的结果是多行单列的
        -- 查询财务部和开发部所有的员工信息
            SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '开发部' 
            SELECT *
            FROM emp 
            WHERE emp.`dept_id` IN (2,3)
            -- 子查询
            SELECT * 
            FROM emp 
            WHERE emp.`dept_id` IN (
                SELECT id
                FROM dept
                WHERE NAME = '财务部' OR NAME = '开发部' 
            )

        
        
    3.子查询的结果是多行多列的
        -- 查询员工的入职日期是2011-11-11日之后的员工信息和部门信息
            SELECT * FROM dept t1,(SELECT * FROM emp WHERE emp.`join_date`>'2011-11-11') t2
            WHERE t1.id = t2.dept_id

事务

1.事务的基本结束
    1.一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
    2.操作：
        1.开启事务:start transaction
        2.回滚：rollback
        3.提交：commit
        4.数据库中事务默认自动提交
            *一条DML(增删改)语句会自动提交一次事务
            *事务提交的两种方式：
                *自动提交
                    mysql 就是自动提交的
                *手动提交
                    需要先开启事务，再提交
            *修改事务的默认提交方式：
                select @autocommit; --1代表自动提交 0 代表手动提交
                set @@autocommut  = 0; --更改默认提交方式
    
2.事务的四大特征
    1.原子性：是不可分割的最小单位，要么同时成功，要么同时失败
    2.持久性：当事务提交或回滚后数据库会持久化的保存数据
    3.隔离性：多个事务之间，相互独立
    4.一直性：数据操作前后数据的总量不变
3.事务的隔离级别（了解 ）
    *概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔开级别就可以解决问题
    *存在问题
        1.脏读：一个事务，读到了另一个事务中没有提交的数据
        2.不可重复读：在同一个事务中，两次读取到的数据不一样
        3.幻读： 一个事务操作（DML）数据表中所有的记录，另外一个事务添加了一条数据，则第一个事务查询不到自己的修改
        
    *隔离级别
        read uncommited ：读未提交
            *产生1，2，3问题
        read conmmited:读已提交
            产生2，3问题
        repatable read: 可重复读 （ mysql中默认）
            产生3问题
        serializable:串行化
            可以解决所有的问题
        *注意：隔离级别从大到小越来越高  但是效率越来越低
        数据库查询隔离级别∶
            *select @@tx_isolation; 
        *数据库设置隔离级别:
            *set global transaction isolation level级别字符串;