概述
数据库的增删查、使用
DDL查询表
查询当前数据库所有表的名字
show tables;
查询表结构
desc 表名字
DDL创建表
creat table 表名(
字段1 数据类型
字段2 数据类型
....
);
注意:
- 字段名是列的名字
- 最后一行末尾,不能加逗号
create table tb_user (
id int,
username varchar(20), #sql语句中字符串是char和varchar类型
password varchar(32)
);
三种数据类型,数值、日期、字符串
数值
tinyint:小整数型,占一个字节
int :大整数类型,占四个字节
double:浮点类型
使用格式:字段名 double(总长度,小数点后保留的位数)
日期
date :日期值要带引号,只包含年月日
time:时间值或持续时间
year:年份值
datetime:混合日期和时间,年月日时分秒
字符串
char:定长字符串
优点:存储性能好
缺点:浪费空间
varchar:变长字符串
优点:节约空间
缺点:存储性能低
DDL删除表
删除表
drop table 表名;
删除表时判断表是否存在
drop table if exists 表名;
DDL修改表
关键字:rename、add、modify、change、drop
- 修改表名
alter table 表名 rename to 新表名;例如alter table tb_dept rename to tb_dept1;
- 添加一列
alter table 表名 add 列名 数据类型;
-
修改列名和数据类型
alter table tb_dept1 change 列名 新列名 新数据类型;例:alter table stu change address addr varchar(50);
- 删除列
ALTER TABLE 表名 DROP 列名;
-- 将stu表中的addr字段 删除
alter table stu drop addr;
数据操作语言DML
DML添加数据
- 给指定列添加数据
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,…)…;
注意:注意添加字符串时候要加引号
-- 给指定列添加数据
INSERT INTO stu (id, NAME) VALUES (1, '张三');
-- 给所有列添加数据,列名的列表可以省略的
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
-- 批量添加数据
INSERT INTO stu VALUES
(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),
(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);
修改数据
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
注意:
修改语句中如果不加条件,则将所有记录都修改!
像上面的语句中的中括号,表示在写sql语句中可以省略这部分
update stu set sex = '女' where name = '张三';
删除数据
DELETE FROM 表名 [WHERE 条件] ;
-- 删除张三记录
delete from stu where name = '张三';
-- 删除stu表中所有的数据
delete from stu;
注意:
-
和上面一样,删除语句中如果不加条件,所有记录都将被删除,慎重!
-
中括号,表示在写sql语句中可以省略的部分
DQL数据查询语言
查询完整语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定
查询多个字段
SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据SELECT DISTINCT 字段列表 FROM 表名; --查询字段并去除重复记录
条件查询
select 字段列表 from 表名 where 条件列表
SELECT DISTINCT name AS '名字',age AS '年龄' FROM stu WHERE age>20 && age<=40;
模糊查询
SELECT * FROM stu WHERE name LIKE '_斯%';
模糊查询替换符:
下划线是必须一个字符,百分号替换0-多个字符
注意:
- null不能和等号运算,要 IS NULL或 IS NOT NULL,而不是=null
- SQL语句没有==,相等是=,没有赋值的概念。
排序查询
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;
查询学生信息,按照数学成绩降序排列,如果数学成绩一样,再按照英语成绩升序排列
select * from stu order by math desc , english asc ;
ASC:升序排列(默认)
DESC:降序排列
注意:多个排列条件,前边的条件值一样时,才会根据第二条进行排序
聚合函数
SELECT 聚合函数名(列名) FROM 表;
select count(id) from stu; #统计id字段非null的记录数量
select count(*) from stu;
# 统计“存在非null字段”的记录数量,* 表示所有字段数据,只要某行有一个非空数据,就会被统计在内
聚合函数:
函数名 | 功能 |
---|---|
count(列名) | 统计数量(选用不为null的列) |
max(列名) | 最大值 |
min(列名) | 最小值 |
sum(列名) | 求和 |
avg(列名) | 平均值 |
注意:null 值不参与所有聚合函数运算
注意:
- 使用count带条件统计数量必须or null,否则是统计总数量(条件是distinct除外)
- 使用sum带条件统计数量不用or null
示例:使用count带条件统计数量如果不加or null,就会统计这个字段总数量、
SELECT count(name='abcd') FROM student;
分组查询--group by
常常和聚合函数一起用
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意:分组之后,查询的字段为聚合函数和分组字段,查询其他字段无任何意义
查询男同学和女同学各自的数学平均分:
#根据性别分组,每组统计平均值
select sex, avg(math) from stu group by sex;
注意:在分组的情况下,查询字段为聚合函数时,这个聚合函数统计的将是每组的信息
where和having的区别:
- 执行的时机不同:where是分组之前进行限定,不满足where条件,则不参与分组,而having是分组之后对结果进行过滤。
- 可执行的条件不一样:where不能对聚合函数进行判断,having可以。执行顺序where>聚合函数>having,不可能判断后面执行的条件
分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;
练习:
起始索引 = (当前页码 - 1) * 每页显示的条数
-
从0开始查询,查询3条数据
select * from stu limit 0 , 3;
-
每页显示3条数据,查询第1页数据
select * from stu limit 0 , 3;
约束
概念
- 约束是作用于表中列上的规则,用于限制加入表的数据,例如:我们可以给id列加约束,让其值不能重复,不能为null值。
- 添加约束可以在添加数据的时候就限制不正确的数据,例如把年龄是3000,数学成绩是-5分这样无效的数据限制掉,继而保障数据的完整性。
常用约束
增删约束
外键约束(待补充)
-- 创建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [外键取名名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
-- 创建表时添加外键约束,constraint译作限制,束缚;references译作关联,参考,提及
create table 表名(
列名 数据类型,
…
[constraint] [外键取名名称] foreign key(外键列名) references 主表(主表列名)
);
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);
-
删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
数据库设计
概念
数据库的设计概念
- 设计方向:有哪些表?表里有那些字段?表和表之间有什么关系?
- 数据库设计就是根据业务系统的具体需求,结合我们所选用的DBMS,为这个业务系统构造出最优的数据储存模型
- 建立数据库中的表结构以及表与表之间的关联关系
数据库设计的步骤
-
需求分析(数据是什么? 数据具有哪些属性? 数据与属性的特点是什么)
-
逻辑分析(通过ER图对数据库进行逻辑建模,不需要考虑我们所选用的数据库管理系统)
-
物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)
-
维护设计(1.对新的需求进行建表;2.表优化)
表设计
1.一对多
例如:部门和员工
一个部门对应多个员工,一个员工对应一个部门
实现方式:
在多的一方建立外键,在‘一’的一方建立主键
-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
-- 部门表
CREATE TABLE tb_dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE tb_emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
);
2.多对多
例如:商品和订单
一个商品对应多个订单,一个订单包含多个商品
实现方式:
建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;
-- 订单表
CREATE TABLE tb_order(
id int primary key auto_increment,
payment double(10,2),
payment_type TINYINT,
status TINYINT
);
-- 商品表
CREATE TABLE tb_goods(
id int primary key auto_increment,
title varchar(100),
price double(10,2)
);
-- 订单商品中间表
CREATE TABLE tb_order_goods(
id int primary key auto_increment,
order_id int,
goods_id int,
count int
);
-- 建完表后,添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);
表结构模型:
3.一对一
如:用户 和 用户详情
一对一关系多用于表拆分,将一个实体中经常使用的字段放一张表,不经常使用的字段放另一张表,用于提升查询性能。
实现方式:
在任意一方加入外键,关联另一方主键,并且设置外键为唯一(UNIQUE)
而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用,此时就可以将这张表查分成两张表:
create table tb_user_desc (
id int primary key auto_increment,
city varchar(20),
edu varchar(10),
income int,
status char(2),
des varchar(100)
);
create table tb_user (
id int primary key auto_increment,
photo varchar(100),
nickname varchar(50),
age int,
gender char(1),
desc_id int unique,
-- 添加外键
CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);
多表查询
创建练习的表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
# 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键,关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);
连接查询
概念
内连接查询:相当于查询AB交集的数据
外连接查询:
- 左外连接查询:相当于查询A表所有数据和交集部门数据
- 右外连接查询:相当于查询 B表所有数据和交集部分数据
关联查询结果行数:假设a表x行,b表y行;
- a左连接b:x行~x*y行
- a右连接b:y行~y*x行
- 内连接:0行~min(x,y)行
内连接查询
相当于查询AB交集数据。
-- 隐式内连接。没有JOIN关键字,条件使用WHERE指定。书写简单,多表时效率低
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
-- 显示内连接。使用INNER JOIN ... ON语句, 可以省略INNER。书写复杂,多表时效率高
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
#显式内连接
select * from emp inner join dept on emp.dep_id = dept.did;
自连接
自连接时一种特殊的内连接,他是指相互连接的表在物理上为同一张表,但是在逻辑上分为两张表。
注意:自连接查询的列名必须是 表名* , 而不是直接写 *
案例:
要求检索出学号为20210的学生的同班同学的信息
SELECT stu.* #一定注意是stu.*,不是*
FROM stu JOIN stu AS stu1 ON stu.grade= stu1.grade
WHERE stu1.id='20210'
递归查询
with 语法
WITH [RECURSIVE]
cte_name [(col_name [, col_name] ...)] AS (subquery)
[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
recurslve译为递归。
with:在mysql中被称为公共表达式,可以作为一个临时表然后在其他结构中调用.如果是自身调用那么就是后面讲的递归.
cte_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询
col_name :公共表达式包含的列名,可以写也可以不写
with RECURSIVE t1 AS #这里t1函数名,也是临时表的表名
(
SELECT 1 as n #n是列的别名,1是初始记录
UNION ALL #把递归结果(2,3,4,5)合并到t1表中
SELECT n + 1 FROM t1 WHERE n < 5 #n+1是参数,t1是函数名,n<5是遍历终止条件
)
SELECT * FROM t1; #正常查询t1这个临时表,相当于调用这个函数。
说明:
t1 相当于一个表名
select 1 相当于这个表的初始值,这里使用UNION ALL 不断将每次递归得到的数据加入到表中。
n<5为递归执行的条件,当n>=5时结束递归调用。
with recursive t1 as ( #t1是函数名、临时表名
select * from course_category where id= '1' #初始记录,也就是根节点
union all #把递归结果合并到t1表中
select t2.* from course_category as t2 inner join t1 on t1.id = t2.parentid #递归,用分类表t和临时表t1内连接查询
)
select * from t1 order by t1.id, t1.orderby #查t1表,相当于调用这个函数。
mysql递归特点,对比Java递归的优势
mysql递归次数限制:
mysql为了避免无限递归默认递归次数为1000,可以通过设置cte_max_recursion_depth参数增加递归深度,还可以通过max_execution_time限制执行时间,超过此时间也会终止递归操作。
对比Java递归的优势:
mysql递归相当于在存储过程中执行若干次sql语句,java程序仅与数据库建立一次链接执行递归操作。相比之下,Java递归性能就很差,每次递归都会建立一次数据库连接。
外连接查询
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
一般都用左外连接,因为右外连接可用左外连接实现,可读性更好
示例
查询emp表所有数据和对应的部门信息(左外连接)
select * from emp left join dept on emp.dep_id = dept.did;
select * from emp right join dept on emp.dep_id = dept.did;
子查询
查询中嵌套查询,称嵌套查询为子查询
注意:子语句没有分号
子查询根据查询结果不同作用不同可分为:
子查询语句的结果是单行单列,子查询语句作为条件值,使用 = != > < 等进行条件判断
示例:
查询比猪八戒薪水高的员工:
SELECT * FROM emp WHERE salary >(SELECT salary FROM emp WHERE name='猪八戒');
- 子查询语句结果是多行单列,子查询语句作为条件值,使用 in 等关键字进行条件判断
示例:
查询 '财务部' 和 '市场部' 所有的员工信息:
SELECT * FROM emp WHERE dep_id in (SELECT did FROM dept WHERE dname IN ('财务部','市场部'));
- 子查询语句结果是多行多列,子查询语句作为虚拟表
示例:
查询入职日期是 '2011-11-11' 之后的员工信息和部门信息:
select * from (select * from emp where join_date > '2011-11-11' ) AS t1, dept where t1.dep_id = dept.did;
事务
概念
数据库的事务(Transaction)是一种机制、一个操作序列,包含了一组数据库操作命令。
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败。
事务是一个不可分割的工作逻辑单元。
在转账前开启事务,如果出现了异常回滚事务,三步正常执行就提交事务,这样就可以完美解决问题。
语法:
开启事务:
START TRANSACTION; --transaction译为事务,业务,交易
或者
BEGIN;
提交事务:
commit;
示例;
-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500
-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';
出现异常了... -- 此处不是注释,在整体执行时会出问题,后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
事务的四大特征
原子性(Atomicity): 事务是不可分割的最小操作单位,要么同时成功,要么同时失败
一致性(Consistency) :事务完成时,必须使所有的数据都保持一致状态
隔离性(Isolation) :多个事务之间,操作的可见性
持久性(Durability) :事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
说明:
mysql中事务是自动提交的。
也就是说我们不添加事务执行sql语句,语句执行完毕会自动的提交事务。
可以通过下面语句查询默认提交方式:
SELECT @@autocommit;
查询到的结果是1 则表示自动提交,结果是0表示手动提交。当然也可以通过下面语句修改提交方式set @@autocommit = 0;
函数--【精选】齐全且实用的MySQL函数使用大全-CSDN博客
(一)单行函数
①字符串函数
主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等。
②数学函数
主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获得随机数的函数等。
③日期函数
主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。
④流程控制函数
主要用于在 SQL 语句中控制条件选择。其中包括 IF 语句、CASE 语句和 WHERE 语句
⑤系统信息函数
主要用于获取 MySQL 数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数等。
⑥其他函数
主要包括格式化函数和锁函数等。
(二)聚合函数
AVG(平均值)函数:返回指定组的平均值,空值会被忽略。
COUNT(统计)函数:返回指定组中项目的总数量。
MAX(最大值)函数:返回指定数据的最大值。
MIN(最小值)函数:返回指定数据的最小值。
SUM(求和)函数:返回指定数据的和,只能用于数字列,空值会被忽略。