MySQL基础篇：SQL语法、约束、多表查询、事务...

目录前言一、SQL语法1、DDL语句2、DML语句3、DQL查询语句4、DCL语句二、函数三、约束外键约束四、多表查询1多表关系2多表查询分类1、连接查询2、子查询嵌套查询1标量子查询2列子查询3行子查询4表子查询五、事务1事务操作的两种方式2事务四大特性3并发事务问题4事务的隔离级别前言SQL 是一门操作数据库的「语言」MySQL 是用这门语言的「数据库软件」SQL是一种结构化查询语言是用来增删改查数据的语法规则全球通用标准所有数据库都支持所有主流数据库MySQL、Oracle、PostgreSQL全都使用 SQL 语言Mysql一款数据库管理软件是用来存储、管理数据的容器。MySQL免费、开源最常用其他主流数据库还有Oracle收费大型企业用、PostgreSQL功能强大、SQL Server微软出品等。学会 SQL换任何数据库都能直接用。一、SQL语法通用语法1、可单行或多行书写以分号结尾2、可用空格/缩进3、MySQL数据库的sql语句不区分关键字用大写4、注释单行用 #或 - - 多行/* */SQL分类DDL定义用来定义数据库、表、字段DML操作用来对数据库表中的数据进行增删改DQL查询用来查询数据库中表的记录DCL控制用来创建数据库用户、控制数据库的访问权限1、DDL语句1对数据库database的操作查询、创建、删除、使用查询所有数据库 show databses; 查询当前数据库 select database(); 创建数据库 create database [if not exsists] 数据库名 [default charset 字符集] [collate 排序规则]; 删除数据库 drop database [if exists] 数据库名 使用某个数据库 use 数据库名;2对表table的操作查询当前数据库中所有的表 show tables; 查询表结构 desc 表名; 查询指定表的建表语句 show create table 表名;数据类型创建表示例create table emp( id int comment 编号, workno varchar(10) comment 工号, name varchar(10) comment 姓名, gender char(1) comment 性别, age tinyint unsigned comment年龄, idcard char(18) comment身份证号, entrydate date comment入职日期 )comment 员工表;修改表1、添加字段 ALTER TABLE 表名 ADD 字段名 类型长度COMMENT []; 例如为emp表增加一个新字段为nickname ALTER TABLE emp ADD nickname varchar(20) 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 表名;2、DML语句对数据库中表的数据进行增删改 1添加数据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,...); 注意字符串和日期型数据应该包含在引号里2修改数据UPDATE 表名 SET 字段名1值1,字段名2值2,... [WHERE 条件]; 注意修改条件可有也可没有如果没有则会修改整张表的所有数据3删除数据DELETE FROM 表名 [WHERE 条件]; 注意删除条件可有也可没有如果没有则会删除整张表的所有数据3、DQL查询语句1基本查询1、查询多个字段 SELECT 字段1,字段2,... FROM 表名; 2、给字段设置别名 SELECT 字段1 AS 别名1,字段2 AS 别名2,.... FROM 表名 3、去除重复记录 SELECT DISTINCT 字段列表 FROM 表名2条件查询SELECT 字段列表 FROM 表名 WHERE 条件列表;3聚合函数将一列数据作为整体进行纵向计算SELECT 聚合函数(字段列表) FROM 表名; count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 注意null值不参与所有聚合函数运算4分组查询 group byselect 字段列表 from 表名 where 条件 group by 分组字段 having 分组后过滤条件--分组查询 1、根据性别分组统计男性员工和女性员工的数量 select gender,count(*) from emp group by gender; 2、查询年龄小于45的员工并根据工作地址分组获取员工数量大于等于3的工作地址 select workaddress,count(*) from emp where age45 group by workaddress having count(*)3;where和having的区别执行时机不同where是在分组之前进行过滤不参与分组而having是在分组之后对结果进行过滤。判断条件不同where不能对聚合函数进行判断而having可以。注意执行顺序where → 聚合函数 → having分组之后查询字段一般为聚合函数和分组字段查询其他字段毫无意义。5排序查询 order byselect 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式2; 排序方式ASC:升序默认值; DESC:降序 如果是多字段排序当第一个字段值相同时才会根据第二个字段进行排序6分页查询 limitselect 字段列表 from 表名 limit 起始索引,查询记录数; 注意 1、起始索引从0开始起始索引(查询页码-1)*每页显示记录数 2、如果是查询第一页数据起始索引可以省略直接写查询记录数变相考察分页查询前5个就是第一页且查询5条数据 例如查询性别为男且年龄在20-40岁的前5个员工信息对查询的结果按年龄升序排序年龄相同按入职时间升序排序。 select * from emp where gender男 and age between 20 and 40 order by age asc,entrydate asc limit 5;4、DCL语句1用户管理CREATE USER 用户名主机名 IDENTIFIED BY 密码; ALTER USER 用户名主机名 IDENTIFIED WITH mysql_native_password BY 密码; DROP USER 用户名主机名;2权限管理1、授权 GRANT 权限列表(INSERT/DELETE/UPDATE) ON 数据库名.表名 TO 用户名主机名; 2、收回权限 REVOKE 权限列表(INSERT/DELETE/UPDATE) ON 数据库名.表名 FROM 用户名主机名;二、函数1、字符串函数MySQL内置了很多字符串函数例如把员工的工号统一调整为5位数把不足5位数的全部在前面补0 update emp set workidlpad(workid,5,0);2、数值函数例如生成一个六位数的随机验证码 因为随机数只能生成0~1的小数所以扩大1000000倍最后四舍五入去掉小数部分 但是有可能是0.0几最后前面的0省略了导致不足六位数因此可以用lpad或rpad补零 select lpad(round(rand()*1000000,0),6,0);3、日期函数例如查询所有员工的入职天数并根据入职天数倒序排序 select name,datediff(curdate(),entrydate) as entrydays from emp order by entrydays desc;4、流程函数相当于是一种判断函数然后转换成想要的信息1、查询emp表的员工姓名和工作地址北京/上海---一线城市其他--二线城市 --展示两列数据第一列是员工姓名第二列是对应的一线城市或二线城市 select name,(case workaddress when 北京 then 一线城市 when 上海 then 一线城市 else 二线城市 end)as 工作地址 from emp; 2、已知学生的各项成绩现在想要直接展示对应的等级 --直接展示分数 select id,name,math,english,chinese from score; --展示等级 select id, name, case when math85 then优秀 when math60 then及格 else 不及格 end)数学, case when english85 then优秀 when english60 then及格 else 不及格 end)英语, case when chinese85 then优秀 when chinese60 then及格 else 不及格 end)语文 from score;三、约束定义约束是作用于表上字段上的规则用于限制存储在表中的数据保证数据库中数据的正确性、有效性和完整性。外键约束外键用来让两张表的数据之间建立连接并保证数据的一致性和完整性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;foreign key实质是一种物理外键关联另一张表缺点影响增删改的效率需要检查外键关系仅用于单节点数据库不适用于分布式、集群场景容易引发数据库的死锁问题消耗性能解决使用逻辑外键解决外键关联问题四、多表查询1多表关系包括一对多(多对一)、多对多、一对一1、一对多多对一案例部门和员工的关系关系一个部门对应多个员工一个员工对应一个部门实现在多的一方建立外键指向一的一方的主键2、多对多案例学生与课程选课关系关系一个学生可以选多门课程一个课程可以被多个学生选择实现建立第三张中间表中间表至少包含两个外键分别关联两方主键3、一对一案例用户与用户详情的关系关系单表拆分将一张表的基础字段防一张表中其他详情字段放另一张表中以提升操作效率实现在任意一方加入外键关联另一方的主键并且设置外键为唯一的UNIQUE2多表查询分类1、连接查询1内连接包括隐式内连接和显示内连接1、隐式内连接 select 字段列表 from 表1表2 where 连接条件; 2、显式内连接 select 字段列表 from 表1 inner join 表2 on 连接条件;例如查询一个员工的姓名及其关联的部门的名称 select emp.name,dept.name from emp,dept where emp.dept_iddept.id; --给表起别名简化sql语句 select e.name,d.name from emp e,dept d where e.dept_idd.id; --显示内连接 select e.name,d.name from emp e inner join dept d on e.dept_idd.id;2外连接包括左外连接和右外连接1、左外连接 (包括左表中的所有数据右表能匹配上就显示数据匹配不上就显示NULL) select 字段列表 from 表1 left join 表2 on 连接条件; 2、右外连接 (包括右表中的所有数据左表匹配不上就显示NULL) select 字段列表 from 表1 right join 表2 on 连接条件;3自连接自连接就是自己连接自己但是要给自己取两个别名以作区分select 字段列表 from 表A 别名A join 表A 别名B on 连接条件;什么时候用自连接就是目前所有信息全部杂糅在一张表上但现在需要明确的一一列出来就用自连接1、查询所有员工emp及其领导的名字(也在emp表中) select a.name,b.name from emp a,emp b where a.manager_idb.id; 2、现要求如果该员工没有领导也要查询出来--用到外连接 select a.name,b.name from emp a left join emp b on a.manager_idb.id;扩展联合查询2、子查询嵌套查询子查询也叫嵌套查询即SQL语句中嵌套SELECT语句。子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一种。根据子查询的结果不同可以分为标量子查询子查询的结果为单个值列子查询子查询结果为一列行子查询子查询结果为一行表子查询子查询结果为多行多列根据子查询位置可以是where之后from之后select之后1标量子查询是指子查询只返回单个值数字、字符串、日期等常用 、、 、 、 1、查询“销售部”的所有员工信息 首先先得知道销售部的id -select id from dept where name销售部; select * from emp where dept_id(select id from dept where name销售部); 2、查询在“方东白”入职日期之后的员工信息 首先查出“方东白”入职日期 -select entrydate from emp where name方东白; select * from emp where entrydate (select entrydate from emp where name方东白);2列子查询子查询返回结果为一列常用IN、NOT IN、ANY、SOME、ALL1、查询“销售部”和“市场部”的所有员工信息 SELECT * FROM emp WHERE dept_id in(SELECT id FROM dept WHERE name销售部 or name市场部); 2、查询比财务部所有人薪资都高的员工信息 SLECT * FROM emp WHERE salary all( SELECT salary FROM emp WHERE dept_id(SELECT id FROM dept WHERE name财务部) );3行子查询子查询返回结果为一行多列常用操作符、、IN 、NOT IN查询和“张三”的薪资和直属领导相同的员工信息 SELECT * FROM emp WHERE (salary,managerid)(SELECT salary,managerid from emp where name张三);4表子查询子查询返回的是多行多列常用 in1、查询与“张三”“李四”的职位和薪资相同的员工信息 SELECT * FROM emp WHERE (job,salary) IN ( SELECT job,salary from emp WHERE name张三 OR name李四 ); 2、查询入职日期在“2006-01-01”之后的员工信息及其部门信息 select e.*,d.* from (select * from emp where entrydate 2006-01-01) e left join dept d on e.dept_idd.id; //先把入职日期在“2006-01-01”之后的员工信息筛选出来再去连接部门表查对应部门信息五、事务定义事务是一组操作的集合它会把所有操作作为一个整体一起向系统提交或撤销操作这些操作要么都成功要么都失败。mysql中的事务默认自动提交如果我们要对多个事务进行管理需要手动开启事务1事务操作的两种方式1、把自动提交事务修改为手动提交事务 set autocommit0只要没有手动commit事务就不会提交各种操作也不会生效2、开启事务start transaction一旦出现异常就回滚事务rollback没有出现异常就提交事务commit2事务四大特性原子性要么都成功要么都失败一致性事务完成时必须所有数据保持一致隔离性数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行持久性事务一旦提交或回滚它对数据库的数据的改变是永久的3并发事务问题A事务和B事务同时在操作同一个数据库或数据表时引发的问题脏读一个事务读到另一个事务还没有提交的数据。因为一个事务必须要等全部操作完成后才能提交事务但是在执行这些操作的过程确实是对数据库造成了改变导致另一个事务会读到改变后的数据.不可重复读一个事务先后读取同一条记录但两i读取到数据不同。就是事务A在两次查询操作之间有事务B提交了事务使得数据库中的数据发生了改变。幻读一个事务按条件查询数据时没有对应的数据行但在插入数据时又发现这行数据已经存在。4事务的隔离级别用于解决并发事务问题×是不会出现的√是可能发生的