MySQL数据库基础DDL-数据库操作查询所有数据库 SHOW DATABASES; 查询当前数据库 SELCET

DDL-数据库操作

查询所有数据库 SHOW DATABASES; 查询当前数据库 SELCET DATABASE();

创建 CREATE DATABASE 数据库名; 删除 DROP DATABASE 数据库名;

使用 USE 数据库名; 查询当前库中所有的表 SHOW TABLES; 查询表结构 DESC 表名;

查询指定表的建表语句 SHOW CREATE TABLE 表名;

表操作-创建：

创建一个表名为tb_user的用户表
CREATE TABLE tb_user(
    id int comment “编号”,
    name varchar(50) comment “姓名”,
    age int comment “年龄”,
    gender varchar(1) comment”性别”
) comment “用户表”;

添加字段：ALTER TABLE tb_user ADD nickname VARCHAR(20) COMMENT “昵称”;

修改数据类型-MODIFY：ALTER TABLE tb_user MODIFY nickname 新数据类型(长度);

修改字段名和字段类型-CHANGE：修改nikename为username：

ALTER TABLE tb_user CHANGE nickname username VARCHAR(30) COMMENT “用户名”;

删除字段：ALTER TABLE tb_user DROP 字段名(username);

修改表名：ALTER TABLE tb_user RENAME TO bt_user;

删除表：DROP TABLE 表名;

DML数据操作语言

给指定字段添加数据：INSERT INTO tb_user (字段名,id,name) VALUES (字段值,1,”name1”);

给全部字段添加数据：INSERT INTO tb_user VALUES (值1,值2);

批量添加数据：INSERT INTO tb_user VALUES (值1,值2),(值1,值2),(值1,值2);

修改数据：UPDATE tb_user SET 字段1=值1,name=name2 WHERE id = 1; 修改id为1的字段名1和name值,如果不加where，则修改整张表所有数据。

删除数据：DELETE FROM tb_user WHERE gender = “女”; 删除表中的女性。

DQL数据查询语言

查询字段名： SELECT 字段1,字段2 FROM 表名;

查询去重加DISTINCT, 例：SELECT DISTINCT workaddress FROM 表名;

查询为两个字的员工信息：SELECT * FROM tb_user WHERE name like ‘__’;

查询身份证最后一位为X的员工信息：SELECT * FROM tb_user WHERE idcard like ‘%X’;

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

COUNT数量 MAX最大值 MIN最小值 AVG平均值 SUM求和

例：SELECT COUNT(id) FROM tb_user;

分组查询：

查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

SELECT workadd, count() from emp where age < 45 group by workadd HAVING count()>=3;

起别名：SELECT workadd, count(*) address_count from emp where age < 45 group by workadd HAVING address_count >=3;

排序查询：ASC升序（默认） DESC降序

例：根据年龄对员工进行升序排序，年龄相同则根据入职时间降序排序。

SELECT * FROM emp ORDER BY age ASC,entydate DESC;

分页查询：LIMIT

例：查询第一页员工数据，展示十条记录：SELECT * FROM emp LIMIT 0,10;

查询第二页员工数据，展示十条记录：SELECT * FROM emp LIMIT 1*10,10;

DCL数据控制语言，

管理数据库用户，控制数据库访问权限。

查询用户：USE mysql; SELECT * FROM user; 使用mysql,查询用户表。

创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 主机名为%代表任意用户。

修改用户密码：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户：DROP USER '用户名'@'主机名';

查询权限：SHOW GRANTS FOR '用户名'@'主机名';

授予权限：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

例：给xiaom用户授予ry-vue所有表的权限：GRANT all ON ry-vue.* TO 'xiaom'@'localhost';

撤销权限：REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数：

字符串函数：

CONCAT(S1,S2,S3) 字符串拼接，将S1,S2,S3拼接成一个字符串

LOWER(str) 将字符串str全部转为小写 UPPER(str) 将字符串str全部转为大写

LPAD(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度

RPAD(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度

TRIM 去掉字符串头部和尾部的空格

SUBSTRING(str,start,len) 返回字符串str从start位置起的len个长度的字符串

例：将企业员工工号统一为五位数，不足的补零，例00001

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后的时间值

例：DATE_ADD(now(),INTERVAL 70 DAY);表示当前时间往后推七十天

DATEDIFF(date1,date2) 返回起始时间date1和结束时间date2之间的天数

例子：查询所有员工的入职天数，并根据入职天数倒叙排序。

SELECT name DATEDIFF(CURDATE(),entrydate) as ‘entrydays’ from emp order by entrydays DESC;

流程函数：

IF(value,t,f) 如果value为true则返回T，如果为false则返回f,

IFNULL(value1,value2) 如果value1不为空，则返回value1,否则返回value2

CASE WHEN [val1] THEN [ral1]...ELSE [DEFAULT] END 如果val1为true则返回res1，否则返回default默认值

例：查询emp表员工的姓名和工作地址（北京，上海-->一线城市，其他-->二线城市）

SELECT name, CASE workaddress WHEN ‘北京’ THEN ‘一线城市’ ELSE ‘二线城市’ END FROM emp;

例：统计学员成绩，>=85为优秀，>=60为及格，否则为不及格

SELECT id, name, CASE WHEN math >= 85 THEN ’优秀’ WHEN math >= 60 THEN ‘及格’ ELSE ‘不及格’ END,
CASE WHEN chinese>= 85 THEN ’优秀’ WHEN chinese>= 60 THEN ‘及格’ ELSE ‘不及格’ END,
CASE WHEN english >= 85 THEN ’优秀’ WHEN english>= 60 THEN ‘及格’ ELSE ‘不及格’ END from score;

约束：

非空约束 NOT NULL 唯一约束 UNIQUE 主键约束 PRIMARY KEY 默认约束 DEFAULT

外键约束 FOREIGN KEY 检查约束 CHECK

例：create table user(

id int primary key auto_increment comment ‘主键’,

name varchar(10) not null unique comment ‘姓名’,

age int check (age > 0 && age <= 120) comment ‘年龄’,

status char(1) default ‘1’ comment ‘状态’,

gender char(1) comment ‘性别’

) comment ‘用户表’;

添加外键：

为emp表中的dept_id添加外键,表为dept,修改删除同步cascade，删除后写空set null

Alter TABLE EMP ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) references dept(id) on update cascade on delete cascade;

删除外键：Alter TABLE EMP DROP FOREIGN KEY fk_emp_dept_id;

多表关系：

一对多（多对一）：在多的一方建立外键，指向一的一方的主键。

多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

一对一：在任意一方加入外键，关联另一方的主键，并设置外键为唯一（UNIQUE）。

多表查询：

笛卡尔积：指在数学中，两个集合A集合和B集合的所有组合情况。

例：SELECT * FROM emp,dept where emp.dept_id = dept.id;

连接查询：内连接：相当于A、B交集的部分。

外连接：左外连接：查询左表所有数据，以及两张表交集部分数据。

右外连接：查询右表所有数据，以及两张表交集部分数据。

自连接：当前表与自身的连接查询，自连接必须使用表别名。

子查询：...

内连接演示：查询每一个员工的姓名，以及关联部门的名称。

表结构：emp,dept 连接条件：emp.dept_id = dept.id

隐式内连接：SELECT emp.name,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

显式内连接：SELECT e.name,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;

外连接演示：

左外连接：查询emp表所有数据和对应的部门信息

SELECT e.*,d.name FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id; outer可以省略

右外连接：查询dept表所有数据及其对应的员工信息

SELECT e.*,d.* FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;

自连接：可以是内连接也可以是外连接

例：查询员工及其所属领导的名字

SELECT a.name, b.name FROM emp a, emp b WHERE a.managerid = b.id;

例：查询所有员工及其所属领导的名字，如果没有领导也需要查询出来

SELECT a.name, b.name FROM emp a, LEFT JOIN emp b ON a.managerid = b.id;

联合查询：UNION, UNION ALL 把多次查询的结果合并起来，形成一个新的查询结果集。

例：将薪资低于5000的员工和年龄大于50岁的员工全部查询出来

SELECT * FROM emp WHERE salary < 5000

union union all 会将全部数据直接合并在一起，union会对合并后的数据去重

SELECT * FROM emp WHERE age > 50;

对于联合查询，多张表的列数必须保持一致，字段类型也需要保持一致。

子查询：又称为嵌套查询

标量子查询：子查询返回的结果是单个值（数字，字符串，日期）最简单的形式。

常用操作符：= > >= <

例：查询销售部所有员工的信息。

拆解：查询销售部门ID：SELECT id FROM dept WHERE name = ‘销售部’; 结果为4

按照销售部门ID查询员工信息：SELECT * FROM emp WHERE dept_id = 4;

合并：SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = ‘销售部’);

例：查询在东方白入职之后的员工信息。

SELECT * FROM emp WHERE entrydate > (SELECT entrydate FROM emp WHERE name = ‘东方白’);

列子查询：子查询的返回结果是一列（可以是多行）

常用的操作符： in any not in some all

例：查询销售部和市场部所有员工信息。

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = ‘销售部’ OR name = ‘市场部’);

例：查询比财务部所有人工资都高的员工信息。

SELECT * FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = ‘财务部’) );

行子查询：子查询的返回结果是一行（可以是多列）常用操作符：= in not in

例：查询与张无忌薪资及直属领导相同的员工信息。

SELECT * FROM emp WHERE (salary, managerid) = (SELECT salary, managerid FROM emp WHERE name = ‘张无忌’);

表子查询：子查询的返回结果是多行多列

例：查询与”宋”，”路”的职位和薪资相同的员工信息

SELECT * FROM emp WHERE (job, salary) in (SELECT job, salary FROM emp WHERE name = ‘宋’ OR name = ‘路’);

例：查询入职日期是”2006-01-01”之后的员工信息及其部门信息。

SELECT e.*, d.* FROM (SELECT * FROM emp WHERE entrydate > ‘2006-01-01’) e LEFT JOIN dept d ON e.dept_id = d.id;

事务：

是一组操作的集合，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。例如：银行转账。

事务操作：

方式一：查看设置事务提交方式：SELECT @@AUTOCOMMIT; 为1说明是自动提交。

SET @@AUTOCOMMIT = 0; 设置为0，手动提交。

提交事务：COMMIT; 执行之后需要提交回滚事务：ROLLBACK; 事务报异常进行回滚操作。

方式二：开启事务：START TRANSACTION; 或 BEGIN;

提交事务：COMMIT; 执行之后需要提交回滚事务：ROLLBACK; 事务报异常进行回滚操作。

事务四大特性ACID：

原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性：事务完成时，必须使所有数据都保持一致状态。

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

持久性：事务一旦提交或回滚，他对数据库中数据的改变就是永久的。

并发事务问题：

脏读：一个事务读到另外一个事务还没有提交的数据。

不可重复读：一个事务先后读取同一条数据，但两次读取的数据不同，称之为不可重复读。

幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。

事务隔离级别：

隔离级别	脏读	不可重复读	幻读
Read uncommitted	🐕	🐕	🐕
Read committed	×	🐕	🐕
Repeatable Read(默认)	×	×	🐕
Serializable	×	×	×