设计公司网站图,运营推广网站建设,电子工程网下载,注册自己的网站需要多少钱#x1f4c3;基础篇 下方链接使用科学上网速度可能会更加快一点哦#xff01; 请点击查看数据库MySQL笔记大全
通用语法及分类
DDL: 数据定义语言#xff0c;用来定义数据库对象#xff08;数据库、表、字段#xff09;DML: 数据操作语言#xff0c;用来对数据库表中的…基础篇 下方链接使用科学上网速度可能会更加快一点哦 请点击查看数据库MySQL笔记大全
通用语法及分类
DDL: 数据定义语言用来定义数据库对象数据库、表、字段DML: 数据操作语言用来对数据库表中的数据进行增删改DQL: 数据查询语言用来查询数据库中表的记录DCL: 数据控制语言用来创建数据库用户、控制数据库的控制权限 DDL数据定义语言Data Definition Language
数据定义语言 数据库操作 查询所有数据库 SHOW DATABASES; 查询当前数据库 SELECT DATABASE(); 创建数据库 CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ]; 删除数据库 DROP DATABASE [ IF EXISTS ] 数据库名; 使用数据库 USE 数据库名; 注意事项 UTF8字符集长度为3字节有些符号占4字节所以推荐用utf8mb4字符集 表操作 查询当前数据库所有表 SHOW TABLES; 查询表结构 DESC 表名; 查询指定表的建表语句 SHOW CREATE TABLE 表名;
创建表
CREATE TABLE 表名(字段1 字段1类型 [COMMENT 字段1注释],字段2 字段2类型 [COMMENT 字段2注释],字段3 字段3类型 [COMMENT 字段3注释],...字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];最后一个字段后面没有逗号
添加字段 ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束]; 例ALTER TABLE emp ADD nickname varchar(20) COMMENT 昵称;
修改数据类型 ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度); 修改字段名和字段类型 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束]; 例将emp表的nickname字段修改为username类型为varchar(30) ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT 昵称;
删除字段 ALTER TABLE 表名 DROP 字段名;
修改表名 ALTER TABLE 表名 RENAME TO 新表名
删除表 DROP TABLE [IF EXISTS] 表名; 删除表并重新创建该表 TRUNCATE TABLE 表名;
DML数据操作语言Data Manipulation Language 添加数据 指定字段 INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); 全部字段 INSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加数据 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 条件 ]; 例 UPDATE emp SET name Jack WHERE id 1;
删除数据 DELETE FROM 表名 [ WHERE 条件 ];
DQL数据查询语言Data Query Language
语法
SELECT字段列表
FROM表名字段
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后的条件列表
ORDER BY排序字段列表
LIMIT分页参数基础查询 查询多个字段 SELECT 字段1, 字段2, 字段3, ... FROM 表名; SELECT * FROM 表名;
设置别名 SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名; SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
去除重复记录 SELECT DISTINCT 字段列表 FROM 表名;
转义 SELECT * FROM 表名 WHERE name LIKE /_张三 ESCAPE / / 之后的_不作为通配符 条件查询 语法 SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件
比较运算符功能大于大于等于小于小于等于等于 或 !不等于BETWEEN … AND …在某个范围内含最小、最大值IN(…)在in之后的列表中的值多选一LIKE 占位符模糊匹配_匹配单个字符%匹配任意个字符IS NULL是NULL
逻辑运算符功能AND 或 并且多个条件同时成立OR 或 ||或者多个条件任意一个成立NOT 或 !非不是
例子
-- 年龄等于30
select * from employee where age 30;
-- 年龄小于30
select * from employee where age 30;
-- 小于等于
select * from employee where age 30;
-- 没有身份证
select * from employee where idcard is null or idcard ;
-- 有身份证
select * from employee where idcard;
select * from employee where idcard is not null;
-- 不等于
select * from employee where age ! 30;
-- 年龄在20到30之间
select * from employee where age between 20 and 30;
select * from employee where age 20 and age 30;
-- 下面语句不报错但查不到任何信息
select * from employee where age between 30 and 20;
-- 性别为女且年龄小于30
select * from employee where age 30 and gender 女;
-- 年龄等于25或30或35
select * from employee where age 25 or age 30 or age 35;
select * from employee where age in (25, 30, 35);
-- 姓名为两个字
select * from employee where name like __;
-- 身份证最后为X
select * from employee where idcard like %X;聚合查询聚合函数 常见聚合函数
函数功能count统计数量max最大值min最小值avg平均值sum求和
语法 SELECT 聚合函数(字段列表) FROM 表名; 例 SELECT count(id) from employee where workaddress 广东省; 分组查询 语法 SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
where 和 having 的区别
执行时机不同where是分组之前进行过滤不满足where条件不参与分组having是分组后对结果进行过滤。判断条件不同where不能对聚合函数进行判断而having可以。
例子
-- 根据性别分组统计男性和女性数量只显示分组数量不显示哪个是男哪个是女
select count(*) from employee group by gender;
-- 根据性别分组统计男性和女性数量
select gender, count(*) from employee group by gender;
-- 根据性别分组统计男性和女性的平均年龄
select gender, avg(age) from employee group by gender;
-- 年龄小于45并根据工作地址分组
select workaddress, count(*) from employee where age 45 group by workaddress;
-- 年龄小于45并根据工作地址分组获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from employee where age 45 group by workaddress having address_count 3;注意事项 执行顺序where 聚合函数 having分组之后查询的字段一般为聚合函数和分组字段查询其他字段无任何意义 排序查询 语法 SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式
ASC: 升序默认DESC: 降序
例子
-- 根据年龄升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 两字段排序根据年龄升序排序入职时间降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;注意事项 如果是多字段排序当第一个字段值相同时才会根据第二个字段进行排序 分页查询 语法 SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
例子
-- 查询第一页数据展示10条
SELECT * FROM employee LIMIT 0, 10;
-- 查询第二页
SELECT * FROM employee LIMIT 10, 10;注意事项 起始索引从0开始起始索引 查询页码 - 1 * 每页显示记录数分页查询是数据库的方言不同数据库有不同实现MySQL是LIMIT如果查询的是第一页数据起始索引可以省略直接简写 LIMIT 10 DQL执行顺序 FROM - WHERE - GROUP BY - SELECT - ORDER BY - LIMIT
DCL数据控制语言Data Control Language 管理用户 查询用户
USE mysql;
SELECT * FROM user;创建用户: CREATE USER 用户名主机名 IDENTIFIED BY 密码;
修改用户密码 ALTER USER 用户名主机名 IDENTIFIED WITH mysql_native_password BY 新密码;
删除用户 DROP USER 用户名主机名;
例子
-- 创建用户test只能在当前主机localhost访问
create user testlocalhost identified by 123456;
-- 创建用户test能在任意主机访问
create user test% identified by 123456;
create user test identified by 123456;
-- 修改密码
alter user testlocalhost identified with mysql_native_password by 1234;
-- 删除用户
drop user testlocalhost;注意事项 主机名可以使用 % 通配 权限控制 常用权限
权限说明ALL, ALL PRIVILEGES所有权限SELECT查询数据INSERT插入数据UPDATE修改数据DELETE删除数据ALTER修改表DROP删除数据库/表/视图CREATE创建数据库/表
更多权限请看权限一览表
查询权限 SHOW GRANTS FOR 用户名主机名;
授予权限 GRANT 权限列表 ON 数据库名.表名 TO 用户名主机名;
撤销权限 REVOKE 权限列表 ON 数据库名.表名 FROM 用户名主机名; 注意事项 多个权限用逗号分隔授权时数据库名和表名可以用 * 进行通配代表所有 函数
字符串函数数值函数日期函数流程函数 字符串函数
常用函数
函数功能CONCAT(s1, s2, …, sn)字符串拼接将s1, s2, …, sn拼接成一个字符串LOWER(str)将字符串全部转为小写UPPER(str)将字符串全部转为大写LPAD(str, n, pad)左填充用字符串pad对str的左边进行填充达到n个字符串长度RPAD(str, n, pad)右填充用字符串pad对str的右边进行填充达到n个字符串长度TRIM(str)去掉字符串头部和尾部的空格SUBSTRING(str, start, len)返回从字符串str从start位置起的len个长度的字符串REPLACE(column, source, replace)替换字符串
使用示例
-- 拼接
SELECT CONCAT(Hello, World);
-- 小写
SELECT LOWER(Hello);
-- 大写
SELECT UPPER(Hello);
-- 左填充
SELECT LPAD(01, 5, -);
-- 右填充
SELECT RPAD(01, 5, -);
-- 去除空格
SELECT TRIM( Hello World );
-- 切片起始索引为1
SELECT SUBSTRING(Hello World, 1, 5);update emp set workno lpad(workno,5,0);数值函数
常见函数
函数功能CEIL(x)向上取整FLOOR(x)向下取整MOD(x, y)返回x/y的模RAND()返回0~1内的随机数ROUND(x, y)求参数x的四舍五入值保留y位小数
-- 随机生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,0);日期函数
常用函数
函数功能CURDATE()返回当前日期CURTIME()返回当前时间NOW()返回当前日期和时间YEAR(date)获取指定date的年份MONTH(date)获取指定date的月份DAY(date)获取指定date的日期DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数
例子
-- DATE_ADD
SELECT DATE_ADD(NOW(), INTERVAL 70 YEAR);流程函数
常用函数
函数功能IF(value, t, f)如果value为true则返回t否则返回fIFNULL(value1, value2)如果value1不为空返回value1否则返回value2CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END如果val1为true返回res1… 否则返回default默认值CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END如果expr的值等于val1返回res1… 否则返回default默认值
例子
selectname,(case when age 30 then 中年 else 青年 end)
from employee;
selectname,(case workaddress when 北京市 then 一线城市 when 上海市 then 一线城市 else 二线城市 end) as 工作地址
from employee;约束 说明
分类
约束描述关键字非空约束限制该字段的数据不能为nullNOT NULL唯一约束保证该字段的所有数据都是唯一、不重复的UNIQUE主键约束主键是一行数据的唯一标识要求非空且唯一PRIMARY KEY默认约束保存数据时如果未指定该字段的值则采用默认值DEFAULT检查约束8.0.1版本后保证字段值满足某一个条件CHECK外键约束用来让两张图的数据之间建立连接保证数据的一致性和完整性FOREIGN KEY
约束是作用于表中字段上的可以再创建表/修改表的时候添加约束。
常用约束
约束条件关键字主键PRIMARY KEY自动增长AUTO_INCREMENT不为空NOT NULL唯一UNIQUE逻辑条件CHECK默认值DEFAULT
例子
create table user(id int primary key auto_increment,name varchar(10) not null unique,age int check(age 0 and age 120),status char(1) default 1,gender char(1)
);外键约束 有外键的是子表 外键所关联的表父表 添加外键
CREATE TABLE 表名(字段名 字段类型,...[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);-- 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名; 删除/更新行为 行为说明NO ACTION当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则不允许删除/更新与RESTRICT一致RESTRICT当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则不允许删除/更新与NO ACTION一致CASCADE当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则也删除/更新外键在子表中的记录SET NULL当在父表中删除/更新对应记录时首先检查该记录是否有对应外键如果有则设置子表中该外键值为null要求该外键允许为nullSET DEFAULT父表有变更时子表将外键设为一个默认值Innodb不支持
更改删除/更新行为 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为; 多表查询 多表关系
一对多多对一多对多一对一 一对多 案例部门与员工关系一个部门对应多个员工一个员工对应一个部门实现在多的一方建立外键指向一的一方的主键 多对多 案例学生与课程关系一个学生可以选多门课程一门课程也可以供多个学生选修实现建立第三张中间表中间表至少包含两个外键分别关联两方主键 一对一 案例用户与用户详情关系一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率实现在任意一方加入外键关联另外一方的主键并且设置外键为唯一的UNIQUE
查询
合并查询笛卡尔积会展示所有组合结果 select * from employee, dept; 笛卡尔积两个集合A集合和B集合的所有组合情况在多表查询时需要消除无效的笛卡尔积 消除无效笛卡尔积 select * from employee, dept where employee.dept dept.id;
内连接查询
内连接查询的是两张表交集的部分
隐式内连接 SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接 SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
显式性能比隐式高
例子
-- 查询员工姓名及关联的部门的名称
-- 隐式
select e.name, d.name from employee as e, dept as d where e.dept d.id;
-- 显式
select e.name, d.name from employee as e inner join dept as d on e.dept d.id;外连接查询 左外连接 查询左表所有数据以及两张表交集部分数据SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;相当于查询表1的所有数据包含表1和表2交集部分数据 右外连接 查询右表所有数据以及两张表交集部分数据SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
例子
-- 左
select e.*, d.name from employee as e left outer join dept as d on e.dept d.id;
select d.name, e.* from dept d left outer join emp e on e.dept d.id; -- 这条语句与下面的语句效果一样
-- 右
select d.name, e.* from employee as e right outer join dept as d on e.dept d.id;左连接可以查询到没有dept的employee右连接可以查询到没有employee的dept 自连接查询 当前表与自身的连接查询自连接必须使用表别名 语法 SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...; 自连接查询可以是内连接查询也可以是外连接查询
例子
-- 查询员工及其所属领导的名字
select a.name, b.name from employee a, employee b where a.manager b.id;
-- 没有领导的也查询出来
select a.name, b.name from employee a left join employee b on a.manager b.id;联合查询 union, union all
把多次查询的结果合并形成一个新的查询集
语法
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...注意事项 UNION ALL 会有重复结果UNION 不会联合查询比使用or效率高不会使索引失效 对于联合查询的多张表的列数必须保持一致字段类型也需要保持一致 子查询 SQL语句中嵌套SELECT语句称谓嵌套查询又称子查询。 SELECT * FROM t1 WHERE column1 ( SELECT column1 FROM t2); 子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个 根据子查询结果可以分为 标量子查询子查询结果为单个值 列子查询子查询结果为一列 行子查询子查询结果为一行 表子查询子查询结果为多行多列 根据子查询位置可分为 WHERE 之后 FROM 之后 SELECT 之后 标量子查询 子查询返回的结果是单个值数字、字符串、日期等。常用操作符-
例子
-- 查询销售部所有员工
select id from dept where name 销售部;
-- 根据销售部部门ID查询员工信息
select * from employee where dept 4;
-- 合并子查询
select * from employee where dept (select id from dept where name 销售部);-- 查询xxx入职之后的员工信息
select * from employee where entrydate (select entrydate from employee where name xxx);列子查询 返回的结果是一列可以是多行。 常用操作符
操作符描述IN在指定的集合范围内多选一NOT IN不在指定的集合范围内ANY子查询返回列表中有任意一个满足即可SOME与ANY等同使用SOME的地方都可以使用ANYALL子查询返回列表的所有值都必须满足
例子
-- 查询销售部和市场部的所有员工信息
select * from employee where dept in (select id from dept where name 销售部 or name 市场部);
-- 查询比财务部所有人工资都高的员工信息
select * from employee where salary all(select salary from employee where dept (select id from dept where name 财务部));
-- 查询比研发部任意一人工资高的员工信息
select * from employee where salary any (select salary from employee where dept (select id from dept where name 研发部));行子查询 返回的结果是一行可以是多列。常用操作符, , , IN, NOT IN
例子
-- 查询与xxx的薪资及直属领导相同的员工信息
select * from employee where (salary, manager) (12500, 1);
select * from employee where (salary, manager) (select salary, manager from employee where name xxx);表子查询 返回的结果是多行多列常用操作符IN
例子
-- 查询与xxx1xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name xxx1 or name xxx2);
-- 查询入职日期是2006-01-01之后的员工及其部门信息
select e.*, d.* from (select * from employee where entrydate 2006-01-01) as e left join dept as d on e.dept d.id;事务 说明
事务是一组操作的集合事务会把所有操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败。默认MySQL的事务是自动提交的也就是说当执行一条DML语句MySQL会以及隐式的提交事务
基本操作
-- 1. 查询张三账户余额
select * from account where name 张三;
-- 2. 将张三账户余额-1000
update account set money money - 1000 where name 张三;
-- 此语句出错后张三钱减少但是李四钱没有增加
模拟sql语句错误
-- 3. 将李四账户余额1000
update account set money money 1000 where name 李四;-- 查看事务提交方式
SELECT AUTOCOMMIT;
-- 设置事务提交方式1为自动提交0为手动提交该设置只对当前会话有效
SET AUTOCOMMIT 0;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;-- 设置手动提交后上面代码改为
select * from account where name 张三;
update account set money money - 1000 where name 张三;
update account set money money 1000 where name 李四;
commit;操作方式二
开启事务START TRANSACTION 或 BEGIN TRANSACTION;提交事务COMMIT;回滚事务ROLLBACK;
操作实例
start transaction;
select * from account where name 张三;
update account set money money - 1000 where name 张三;
update account set money money 1000 where name 李四;
commit;四大特性ACID
原子性(Atomicity)事务是不可分割的最小操作但愿要么全部成功要么全部失败一致性(Consistency)事务完成时必须使所有数据都保持一致状态隔离性(Isolation)数据库系统提供的隔离机制保证事务在不受外部并发操作影响的独立环境下运行持久性(Durability)事务一旦提交或回滚它对数据库中的数据的改变就是永久的
并发事务
问题描述脏读一个事务读到另一个事务还没提交的数据不可重复读一个事务先后读取同一条记录但两次读取的数据不同幻读一个事务按照条件查询数据时没有对应的数据行但是再插入数据时又发现这行数据已经存在 并发事务隔离级别
隔离级别脏读不可重复读幻读Read uncommitted√√√Read committed×√√Repeatable Read(默认)××√Serializable××× √表示在当前隔离级别下该问题会出现 Serializable 性能最低Read uncommitted 性能最高数据安全性最差 查看事务隔离级别 SELECT TRANSACTION_ISOLATION; 设置事务隔离级别 SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };SESSION 是会话级别表示只针对当前会话有效GLOBAL 表示对所有会话有效 SELECT TRANSACTION_ISOLATION;set session transaction isolation level read uncommitted ;set session transaction isolation level repeatable read ;
