MySQL基础部分 一MySQL基本信息1MySQL底层逻辑就是用户端我们电脑连接DBMS系统使用DBMS操作底层的数据库而底层可以有多个 数据库数据库内部又有多张表表内记录的就是我们需要的数据2SQL基本语法一 SQL通用语法1SQL语法可以编写多行 以分号未结束字符2SQL可以使用空格或者缩进 增强阅读性3SQL语句不区分大小写单关键字最好使用大写表示关键注释单行注释--注释单行内容 或 #注释 内容多行注释/*注释内容*/二SQL分类DDLDMLDQLDCL1DDL语句一DDL-数据库操作查询--查询所有数据库---SHOW DATABASES;查询当前的数据库---SELECT DATABASES创建--CREATE DATABASE[IF NOT EXISTS]数据库名字[DEFAULT CHASRSET 字符集][COLLATE 排序规则];细节[IF NOT EXISTS]是当数据库不存在时就直接创建但是假如数据库不存在不会报错[DEFAULT CHASRSET 字符集]设置字符类型比如使用utf8但是不建议使用utf8因为数据库底层有一些特殊字符是四个字节而utf8最多 只有三个字节所以使用utf8mb4就有四个字节删除--DROP DATABASE[IF EXISTS]数据库名字细节[IF EXISTS]只有数据库存在才会执行删除操作而不存在不会报错使用--USE 数据库名二DDL-表查询查询当前的数据库的所有表--show tables;前提是先使用这个数据库查询表结构--desc 表名;查询指定表的建表语句--show create table 表名;三DDL-表的创建create table表名(字段1 字段1类型[comment 字段1注释]字段2 字段2类型[comment 字段2注释]字段3 字段3类型[comment 字段3注释].........字段n 字段n类型[comment 字段n注释])[comment 表注释];四DDL的数据类型可分为三个部分数值类型字符串类型日期时间类型1数值类型tinyInt1个字节 smallInt2个字节 mediumInt3个字节 int4个字节bigInt8个字节 float4个字节 double8个字节decimal依赖设置的精度标度示例123.45 -- 精度是5标度是小数点后几位就是2可以在类型后面加一个标识UNSIGNED这样就没有负数数据的范围例如age tinyInt UNSIGNED分数表示score double(4,1) - 表示数据长度是4然后只有小数点的后一位2字符串类型char类型是定长字符串假设没有填满char类型就会以空格来填补varchar类型是可变字符串会 根据输入的数据设置长度二者相比char的性能比较好因为不需要计算用户填入的字符串长度使用示例填写用户名使用 varchar(50) 因为用户输入的长度是不统一的填写性别使用 char(1) 因为结果只有是男生 或 女生3日期类型五DDL表结构的修改1添加字段alter table 表名 add 字段名 类型(长度)[comment 注释] [约束];2修改数据类型alter table 表名 modify 字段名 新数据类型(长度);3修改字段名和数据类型alter table 表名 change 旧字段名 新字段名 类型(长度)[comment注释] [约束];4删除字段alter table 表名 drop 字段名;5修改表名alter table 表名 rename to 新表名6删除表drop table [if exists] 表名;7删除表并重新创建该表truncate table 表名; -- 目的清空原有的数据2DML语句一添加数据1给指定的字段添加对应的数据insert into 表名(字段名1字段名2....) values (值1值2.....);2给全部字段添加数据insert into 表名 values (值1值2.....);3批量添加数据insert into 表名 (字段名1字段名2.....) values (值1值2.....), (值1值2.....), (值1值2.....);insert into 表名 values (值1值2.....), (值1值2.....), (值1值2.....);注意添加的数据必须和表内的数据顺序一致如果添加的是字符串或日期时间就必须存放在引号内添加的数据范围不可超过指定的范围二修改数据修改数据update 表名 set 字段1值1, 字段2值2....[where 条件];注意修改语句的条件可以有也可以没有如果没有就直接修改整张表的数据三删除数据删除数据delete from 表名[where 条件];注意删除语句的条件可以有可以没有如果没有就会删除所有数据delete不能删除某个字段的数据假如要删除某个字段就是用updata将值赋值为null3SQL语句一基本语法select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表having 分组后的条件列表 order by 排序字段列表 limt 分页参数二基本查询1查询多个字段数据select 字段名1,字段名2... from 表名;select * from 表名;2设置别名select 字段名1 (as 别名1),字段名2 (as 别名2)...from 表名;3去除重复记录select distinct 字段列表 from 表名;三条件查询where1语法select 字段名1,字段名2... from 表名 where 条件列表;2条件四聚合查询countmaxminavgsum将数据看成总体然后进行数据的统计count统计数量 max最大值 min最小值 avg平均值 sum求和细节使用聚合查询时查询某一列的值最好是不是null所有null不进入计算比如查询总人数使用 select count(idcard) from emp; 其中idcard存在null就不会记录导致和总的人数不相同假设要查询总数据量最好使用 count (*)语法select 聚合查询(字段列表) from 表名;五分组查询group by语法select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤 条件]where和having区别1执行时机的不同where是分组前的操作不满足where的不参与分组操作having是对分组 后的数据进行过滤2判断条件不同where不能对聚合函数进行判断而having可以执行顺序where 聚合函数 having分组过后查询字段一般就是聚合函数和分组字段查询其他字段没有任何意义六排序查询order by语法select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2.....;排序方式asc升序排序默认值desc降序排序如果是多字段排序假如第一个字段是相等的那么就会按照第二个字段进行排序七分页查询limit语法select 字段列表 from 表名 limit 起始索引索引数量;注意起始索引页码-1*索引数量假设 访问第一页那索引就是0分页查询是各个数据库的方言就是不同数据库写法不同MySQL就是limit如果是访问第一页起始索引可以省略八SQL执行顺序4DCL语句作用用于控制哪一些用户可以访问数据库并赋予访问权限一用户管理操作1查询用户use mysql; ----------用户信息一般存放在mysql的数据库内select * from user; --------------用户信息一般存放在user表内2创建用户create user 用户名‘主机名’ identified by ‘密码’;3修改用户密码alter user ‘用户名’‘主机名’ identified with mysql_native_password by 新密码;4删除用户drop user ‘用户名’‘主机名’;注意主机名可以使用%通配符表示任意主机都可以访问数据库一般使用这部分特别少只有DBA数据管理员使用比较多二操作权限主要使用的权限1权限控制查询权限show grants for ‘用户名’‘主机名’;授予权限grant 权限列表 on 数据库名,表名 to ‘用户名’‘主机号’;撤销权限invoke 权限列表 on 数据库名,表名 from ‘用户名’‘主机号’;注意授予多个权限时可以使用 , 分割写数据库名和表名时可以使用 * 通配符表示所有数据库和对应的所有表内容二函数1字符串函数2数值函数3日期函数4流程控制函数细节使用case when then else end 中可以使用多个when假如条件多个时不能使用 andor比如case 工作地址 when ’北京‘ or ’上海‘ then ’一线城市‘ else ’二线城市‘ end错误不知 道为什么反正我试过这样使用所有结果都返回’一线城市‘正确写法case 工作地址 when ’北京‘ then ’一线城市‘ when ’上海‘ then ’一线城市‘ else ’二线城市‘ end;三约束1概念约束是作用于表中字段上的规则用于限制存储表中的数据2目的保证数据的准确性有效性和完整性示例一个字段可以对应多个约束1外键约束一概念外键是用于两个表之间建立联系从而保证数据的完整性和依赖性注意上述的两个表在数据库层面并未真正做到联系只是逻辑上具有联系性必须使用外键使 两个表之间建立联系才能使数据具有完整性和一致性二语法1添加外键1可以在创建时使用的语法create table 表名(字段名 数据类型;.....[constraint] [外键名称] foreign key (外键字段名) references 主表 (主表列名));2创建完表后再进行关联alter table 表名 add constraint 外键名称 foreign key (外键字段名)references 主表(主表列表)2删除外键alter table 表名 drop foreign key 外键名称;3删除/更新操作一旦删除外键对应的表的信息的操作语法alter table 表名 add constraint 外键名称 foreign key (外键字段名)references 主表(主表列表)on update cascade on delete cascade;四多表查询1关系模式三种一1对N案例员工和部门之间的关系关系一个员工只能对应一个部门一个部门可以对应多名员工实现在多的一方建立外键关联少的一方指向一方的主键二N对M案例学生和选课之间的关系关系一个学生可以选多个课程一个课程可以被多名学生选择实现需要一个第三方的表来关联这两个表而第三个表至少具有两个外键分别关联两方主键三一对一案例用户与用户详情的关系关系一对一关系用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率实现在任意一方加入外键关联另一方的主键并且设置外键为唯一的unique2多表查询的概述概述指从多张表中查询数据笛卡尔积笛卡尔积是指数学中两个集合A和集合B的所有组合情况在多表查询中需要消除 无意义的笛卡尔积示例集合a数据量为2集合b数据量为4那么查询结果就是 2*483多表查询的分类一连接查询1内连接相当于查询ab交集部分的数据隐式内连接select 字段列表 from 表1,表2 where 条件...;显示内连接select 字段列表 from 表1 (inner) join 表2 on 连接条件...;2外连接左外连接查询左表所有数据以及两张表交集部分数据a部分和ab相交的部分select 字段列表 from 表1 left [outer] join 表2 on 条件...;相当于查询表1(左表)所有数据 包含 表1 和 表2 交集部分的数据右外连接查询右表所有数据以及两张表交集部分数据b部分和ab相交的部分select 字段列表 from 表1 right [outer] join 表2 on 条件...;相当于查询表2(右表)所有数据 包含 表1 和 表2 交集部分的数据3自连接相当于表和自身的连接查询自连接必须使用表别名语法select 字段列表 from 表1 别名1 join 表2 别名2 on 条件;4联合查询将表1查询的结果和表2查询的结果整合一起语法select 字段列表 from 表A...union [all]select 字段列表 from 表B...;细节查询多张表使用union返回的结果时使用的字段列表必须保持一致union all 是直接将结果整合然后输出而单用union则会去除重复部分的数据我感觉使用or和union其实差不多二子查询1概念SQL语句中嵌套了select语句称子查询select 字段列表 from 表名 where column(select column from 表名)在子查询的外面可以使用selectinsertupdate...语句2根据查询结果不同分为2一标量子查询子查询返回单个值子查询返回结果是单个值数字字符串日期等常用符号 2二列子查询子查询返回一列数据子查询返回的结果就是一列可以是多行常用符号innot inanysomeall2三行子查询子查询返回一行数据行子查询返回结果就是一行可以是多列常用操作符, , innot in2四表子查询子查询返回多行多列数据查询返回多行多列常用符号in3根据查询位置不同分为where之后select之后from之后五事务1事务简介事务是指一些操作是不可分割的一个工作单位事务会将一系列操作视作整体然后一并提交给系统即这些操作要么一起成功要么一起失败示例假设去转钱张三转给李四那么银行会判断张三是否有这些钱然后减去张三的钱增 加 李四的钱但是假如在给李四增加钱之前发生异常就会停止程序运行这样会导致张三钱少了李四钱没变所以需要事务将转钱的所有操作视作一个事务这样只要在事务内发生异常事务会回退数据就不会发生不完整性细节默认事务是自动上交的也就是说一旦运行一条指令就会马上提交到系统就需要我们手动开启或手动关闭事务2事务操作一关闭自动提交事务查看事务select autocommit;设置事务提交方式set autocommit0(细节1表示自动提交0表示关闭自动提交)提交事务commit必须执行这一步才会将事务提交上去否则数据不会改变回滚事务rollback假设在执行事务抛出异常必须执行回退操作二手动提交事务假设不想修改autocommit就可以使用这个手动提交开启事务start transaction 或begin一旦开启事务就必须执行到commit提交事务才会将事务提交到系统否则不会提交和上述一样一旦错误就需要回滚事务rollback3事务的四大特性原子性事务时不可分割的最小操作单位要么全部成功要么全部失败类似上述的银行转钱 查看余额减少金额和增加金额就是一个事务不可分割一致性事务完成时必须使所有数据保持一致状态依然类似上述转钱无论是否执行成功 最后张三和李四的金额加起来依然不变隔离性数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境运行就是 每一个事务都是一个独立的个体不会相互影响持久性事务一旦发生或者失败他对数据库中的数据影响是永久性的因为数据是存储到本地 磁盘的不会销毁4并发事务问题概念多个并发的事务在读取操作数据发生的问题一脏读解释事务A先读取数据 id1select然后执行更新操作 id1update此时事务A还未执行完而事务B却执行 id1select就会获取到 id1更新后的数据但是事务A此时还未提交事务那么就叫做脏读二不可重复读解释事务A执行 id1(select)获取数据假设是2此时事务A还未完成提交事务事务B就执行 id1(update)假设数据改为3然后提交事务那么事务A再执行 id1(select)就会发现现在获取的数据3和一开始的2不一样那么就是不可重复读三幻读解释事务A执行 id1(select) 假设此时 id1 不存在那么就读取为null此时事务A还未执行完事务B执行 id1(insert)那么此时数据库就有 id1 数据而事务A一开始以为 id1 数据不存在就会执行 id1(insert) 操作但这样就会报错那么事务A会检查 id1 是否存在假设前面的不可重复读问题已经解决那么就算数据库存在 id1 的数据但因为第一次读取返回结果就是不存在那么再读取一次依然还是不存在那么就是幻读5事务的隔离级别细节1repeatable read是MYSQL默认的如果使用其他的可能会不一样细节2表中表示的✔就是可能会发生的问题❌就是不会发生的问题细节3serializable隔离能力最高但效率是最低的read uncommitted隔离能力最低但效率是最高的查看隔离级别select transaction_isolation;设置隔离级别select [session|global] transaction isolation level [ 隔离级别的名字 ]