MySQL学习笔记_1

MySQL学习笔记_1MySQL 基础命令 DML 操作表命令 一 插入 1 格式 insert into 表名 字段 1 字段 2 values 值 1 值 2 2 插入语句需注意点 若指定列 则值得个数一定要与字段数相同 若未明确制定列 则值是整个表的列的个数 无值的补空

大家好,我是讯享网,很高兴认识大家。

MySQL基础命令

DML操作表命令

一.插入
1.格式 :insert into 表名 (字段1,字段2,…)values (值1,值2,…);
2.插入语句需注意点
若指定列,则值得个数一定要与字段数相同
若未明确制定列,则值是整个表的列的个数,无值的补空。

CREATE TABLE `student` ( `sid` int(11) NOT NULL COMMENT '学生ID', `sname` varchar(30) COLLATE utf8_bin DEFAULT NULL COMMENT '学生姓名', `score` decimal(4,1) DEFAULT NULL COMMENT '分数', UNIQUE KEY `sid` (`sid`) ); -- 插入数据 INSERT into student VALUES(1 , ‘小明’ , 100); -- 多数据一行插入 INSERT into student VALUES(2 , '小鹏' , 98) , (3 , '小强' ,96); -- 指定插入字段内容 INSERT into student (sid , sname) VALUES (4 , '小刚'); -- 按指定输入顺序 INSERT into student (sname , sid) VALUES ('小王' , 5); 

讯享网

二.修改数据

讯享网-- 修改分数,所有分数置为零,全表修改 UPDATE student set score = 0 -- 根据指定条件修改 UPDATE student set score = 90 WHERE sname = '小明' or sname = '小鹏' UPDATE student set score = 80 WHERE sname = '小强' or sname = '小刚' UPDATE student set score = 75 WHERE sname = '小王' --小王改名,分数提高20分 UPDATE student set sname = '学霸' , score = score + 20 WHERE sid = 5 

三.删除数据
格式:
delete from 表名 where 字段 = 值

-- 删除 DELETE (全表删除,需谨慎) DELETE FROM student -- 按条件删除 DELETE FROM student WHERE sid = 5 

DQL操作表命令

一.DQL结果集获取

讯享网-- 结果集,是查询出来的多行的结果,虚拟的,查询一次产生一次,存放在内存中 -- 查询表数据,select语句查询不会生成新的表,与不会改变原来的表 -- 星号 * 是返回所有的列 SELECT * FROM student -- 返回指定的列,列明中间用逗号 , 分开 SELECT sname , score from student -- 条件筛选,where 左边是对表的操作,右边是条件的判断并返回 -- TRUE SELECT * FROM student WHERE 1 = 1 -- 等于 = SELECT * FROM student WHERE sid = 1 -- 大于 > SELECT * FROM student WHERE sid > 1 -- 小于 < SELECT * FROM student WHERE sid < 2 -- 大于等于 >=  SELECT * FROM student WHERE sid >= 1 -- 小于等于 <= SELECT * FROM student WHERE sid <= 1 -- 不等于 != 或 <>(不等于) SELECT * FROM student WHERE sid != 1 -- 按姓名查询 SELECT * FROM student WHERE sname = '小明' 
SELECT * FROM student -- 加入新的字段 ALTER TABLE student add COLUMN sex CHAR(1) ALTER TABLE student add COLUMN age CHAR(3) -- 1.查询性别为女,年龄为9的学生 SELECT * FROM student WHERE sex = '女' AND age = 9 -- 2.查询学好为3或者姓名是小刚的学生记录 SELECT * FROM student WHERE sid = 3 or sname = '小刚' -- 3.查询学号为1,2,3的记录 -- 第一种写法 SELECT * FROM student WHERE sid = 1 or sid = 2 or sid = 3 -- 第二种写法 in(set) 集合 SELECT * FROM student WHERE sid in (1,2,3) -- 4.查询年龄为空的学生记录 为空语句 is null SELECT * FROM student WHERE age IS NULL -- 5.查询年龄为空的学生记录 不为空语句 is not null SELECT * FROM student WHERE age IS NOT NULL -- 疑难点 null 与 空字符的区别 SELECT * FROM student WHERE sname is NULL; -- 这个是针对null记录的查询 SELECT * FROM student WHERE sname = ''; -- 针对空字符 -- 6.查询年龄在5单7岁之间的学生记录 -- 第一种写法 SELECT * FROM student WHERE age > 5 AND age < 7 -- 第二种写法 BETWEEN...AND 在某个范围之间 SELECT * FROM student WHERE age BETWEEN 5 AND 7; -- 相当于大于等于5小于等于7 -- 7.查询非男的学生记录 -- 第一种方法 SELECT * FROM student WHERE sex != '男' -- 第二种方法 SELECT * FROM student WHERE sex <> '男' -- 8.查询姓名不为null的学生记录,null和空字符有区别 SELECT * FROM student WHERE sname is not NULL -- 9.查询姓名不为空的学生记录,null和空字符有区别 SELECT * FROM student WHERE sname != '' 

三、习题

讯享网CREATE TABLE emp( eid int(11) not null comment '员工编号', ename varchar (20) comment '员工姓名', deptno int (3) comment '员工所属部门编号' ); -- 1 select * from emp where deptno = 20; alter table emp add column comm decimal (5,2) comment '奖金'; alter table emp add column sal decimal (8,2) comment '工资'; -- 2 select * from emp where comm > sal; -- 3 select * from emp where comm > (sal * 0.2); -- 4 alter table emp add column work_type varchar (30) comment '工种'; select * from emp where (deptno = 10 and work_type = 'MANAGER') or (deptno = 20 and work_type = 'CLERK'); -- 5 select * from emp where work_type != 'MANAGER' and work_type != 'CLERK' and sal >= 2000; -- 6 --  引入去重关键字***distinct*** ** select distinct(work_type) from emp where comm > 0; -- 7 select * from emp where comm is null or comm < 100; -- 8 alter table emp add column work_age int(3); select * from emp where work_age >= 10; 

四、模糊查询

-- 根据某个字,匹配姓名 -- 下划线:匹配任意一个字符,一个下划线代表一个字符 -- 百分号:代表任意个字符 select * from emp where ename like '张_' select * from emp where ename like '赵%' -- 包含某个字符的查找 select * from emp where ename like '%z%' 

五、去重、字段计算、字段取别名

讯享网-- 字段控制(去重) -- distinct 只能放在最前面 -- 如果distinct后面跟随多个字段,那么表示对这多个字段进行去重 select distinct deptno from emp ; -- 字段计算,允许字段进行计算,如果有为null的记录,则用0来表示(需要用到函数) select ifnull (sal + comm , 0) from emp; -- 字段别名,给运算后的字段取一个名字,as可以要也可以省略 select ifnull (sal + comm , 0) as comm_sal from emp; -- 表也可以取别名 元表明 as 表别名 select ifnull (sal + comm , 0) as comm_sal from emp as e; 

六、结果集排序、升序、降序、组合排序

-- 结果集排序 -- 关键字 order by -- ASC :升序 DESC :降序 -- 升序 select * from emp where 1 = 1 order by sal ASC; -- 降序 select * from emp where 1 = 1 order by sal DESC; -- 组合排序,根据多个字段进行排序 select * from emp where 1 = 1 order by sal desc,eid desc 

七、同步练习

讯享网create table emp2( eid int (11) not null comment '员工编号', ename varchar (20) comment '员工姓名', salary decimal (7,2) comment '员工薪水', wage decimal (7,2) comment '员工工资', bonus decimal (7,2) comment '员工奖金' ); alter table emp2 add column work_type varchar(20) comment '员工工种'; -- 1 select * from emp2 where ename like '_____' and salary < 3000; -- 2 select ename,wage from emp2 where ename like 'm%' order by wage desc -- 3 select ifnull (salary + wage, 0) as money from emp2 where ename like '%m%' -- 4 select distinct work_type from emp2 where bonus > 0 -- 5 select ifnull(wage + bonus, 0) from emp2 -- 6 select * from emp2 where 1 = 1 order by salary asc ,bonus desc; 

DQL查询增强

一、常用聚合函数

select * from emp -- count 返回不为null的数据的记录数 select count(*) from emp -- count 最常见的场景,数据分页 当前是第几页,总共用多少条数据 select count(*) from emp where comm is not null; select count(comm) from emp -- 薪资大于50的人数 select count(*) from emp where sal > 50 -- 薪资加奖金大于100的人数 select count(*) from emp where (sal + comm) > 100 -- count(1) 与count(*) 用法相同 -- sum 对字段进行求和 -- 1.查询所有奖金总和、月薪总和 -- 2.查询所有奖金加月薪的总和 -- SUM(expr) -- null 和一个数相加后都会变成0 select sum(sal),sum(comm) from emp select sum(sal) + sum(comm) from emp -- 所以需要ifnull来转化 select sum(ifnull(sal,0) + ifnull(comm,0)) from emp -- avg 求平均数 -- 查询奖金的平均值及月薪的平均值 select avg(comm) from emp -- 聚合函数自作用于非null 的数据 select avg(ifnull(comm,0)) from emp select avg(ifnull(sal,0)) from emp -- max求最大值 与 min求最小值 select max(comm) , min(sal) from emp -- 忽略null,他们认为null数据不参与运算 

二、DQL分组查询Group By

讯享网-- 求出每个部门的总工资 -- group by 将查询到的结果按照1个或者多个字段进行分组,字段值相同的为一组 -- 注意当group by 单独使用时,只显示分组的第一天记录,所以一般不会单独使用 -- 这里的单独使用是没有与聚合函数结合使用 select * from emp group by deptno -- 在使用分组时,分组后的字段,一般作为前面的返回字段 select deptno from emp group by deptno -- group_concay(字段),平时不怎么使用 select deptno ,group_concat(ename) from emp group by deptno -- 加入性别字段 alter table emp add column sex char(1) -- 统计男女性别对应的所有姓名 select sex,group_concat(ename) from emp group by sex --统计部门下的人的性别 select deptno, group_concat(sex) from emp group by deptno -- 小练习,分组与聚合函数一起使用 -- 1.查询每个部门的名称哈薪资总和 select deptno , sum(sal) from emp group by deptno -- 2.查询男性和女性的平均工龄,空数据未参与计算 select sex , avg(work_age) from emp group by sex -- 3.查询每个部门的部门名称以及每个部门工资大于800的人数 select deptno , count(*) from emp where sal > 800 group by deptno 

三、DQL分组加Having的使用

-- where是分组前的再次筛选 -- Having 用于分组后的再次筛选,只能用于group by,先用group by处理数据之后在进行筛选 -- 1.查询薪资总和大于1000的部门名称 select deptno , sum(sal) from emp group by deptno having sum(sal) > 1000 -- 2.查询员工工资大于90,工资总和大于1000的部门名称以及工资总和,按工资总和降序排序 -- 复杂的题目进行解剖 -- 1.查询员工工资大于90的 select * from emp where sal > 90 -- 2.工资总和大于1000的部门名称 select deptno , sum(sal) from emp where sal > 90 group by deptno having sum(sal) > 1000 --  select deptno , sum(sal) as total from emp where sal > 90 group by deptno having sum(sal) > 1000 order by total desc 

四、having的相关练习

讯享网-- 1.查询20号部门的所有员工人数 select count(*) from emp where deptno = 20; -- 2.查询每个部门中奖金最高的奖金 select deptno , max(comm) from emp group by deptno -- 3.查询每个部门的平均薪资和奖金汇总 select deptno , avg(sal) , sum(comm) from emp group by deptno -- 4.查询10号部门的平均工资 select ifnull(avg(sal),0) from emp where deptno = 10 -- 5.查询所有工种不是MANAGER和CLERK,部门工资总和大于1000的部门名称以及薪资总和,按照倒叙显示 select deptno , sum(sal) from emp where work_type not in ('CLERK' , 'MANAGER') group by deptno having sum(sal) > 1000 order by sum(sal) desc -- having 中能够使用and或or等关键字进行多重条件判断 
-- DQL语句书写顺序 -- select + 列名 + from + 表名 + where + 条件 + group by + 分组 + having + 分组后计算 + order by + 升降序 + limit + 分页 -- DQL语句执行顺序 -- from(将表查出) where(筛选条件) group by(控制分组) having(分组后计算0) select(确认需要输出的列名) order by(按照条件进行排序) limit  

五、limit分页

讯享网-- limit分页思路 -- int curPage = 1; 当前页 -- int pageSize = 3; 每页多少条数据 -- 当前页为1,第一页从0开始 (1-1)*3 = 0 -- 当前页为2,第一页从3开始 (2-1)*3 = 3 -- 当前页为3,第一页从6开始 (3-1)*3 = 6 -- 当前页为4,第一页从9开始 (4-1)*3 = 9 -- 公式 == select * from emp limit (当前页-1)*每页多少条数据 , 每页多少条数据 

数据完整性

实体完整性

1.什么是实体
数据库的一行就是一个实体
实体完整性的作用:标志每一行不重复,是行级约束

2.约束类型
主键约束(primary key)
唯一约束(unique)
自动增长列(auto_increment)

3.主键约束
每个表中需要有一个主键
主键约束该列数据不能重复,且不能为null
声明方式
在字段上声明:列名 类型 primary key
在最后一列后声明:primary key (字段…)
先创建表,然后使用 alter 语句修改 alter table 表名 add constraint primary key (id);

-- 在字段上声明 create table student2( sid int comment '学生ID' primary key , sname varchar(30) comment '学生姓名' ); insert into student2 values(1, '王五'); -- 会报错,因为主键不能相同 insert into student2 values(3, '王五'); -- 改为3,运行成功 -- 在最后一行声明 create table student2( sid int comment '学生ID' , sname varchar(30) comment '学生姓名', primary key(sid) ); -- alter 添加主键约束 alter table student2 add constraint primary key (sid) 

联合主键

讯享网-- 联合主键 -- 多个字段作为一个主键(两个字段加在一起不能是一样的,且两个字段都不能为空) alter table student2 add constraint primary key (sid , sname) 
-- 唯一约束 create table student2( sid int comment '学生ID' unique , -- 学生ID不可以重复 sname varchar(30) comment '学生姓名', ); 

5.自动增长列
指定列的数据自动增长,即使删除数据,还是后从删除列表往下增长
声明方式:字段或主键后面加上 auto_increment

讯享网-- 自动增长 (auto_increment) -- 需要在配合主键(约束)的情况下使用 create table student2( sid int auto_increment comment '学生ID' , sname varchar(30) comment '学生姓名', primary key(sid) ); insert into student2(sname) values('王五') 
域完整性(列完整性)
1.以单元格为单位进行约束,针对当前单元格的数据,也称列完整性,不去对照其他数据,域完整性主要保证当前单元格的不会被输入无效数据。 2.域完整性约束 数据类型约束 非空约束 默认值约束 
讯享网-- 数据类型约束  create table student2( sid int auto_increment comment '学生ID' ,-- 只可以输入int类型的数据,数据类型约束 sname varchar(30) comment '学生姓名', primary key(sid) ); 
-- 默认值约束 -- default 'xxx' 默认值 create table student2( sid int auto_increment comment '学生ID' , sname varchar(30) comment '学生姓名', sex varchar(10) default '男', -- 默认值为男 primary key(sid) ); 
参照完整性
讯享网1.值表与表之间的对应关系 通常可以建立两表之间的主键、外键关系,域编写两表的触发器来实现 操作有引用关系的数据表格过程中,对其一张表格做插入、更新、删除等操作时,系统都会将被修改表格与另外一张表格进行对照,从而阻止一些不正确的数据操作。 2.要求 至少两张表(主表,从表) 主表:用来约束其他表的表 从表:受约束的表 数据库的主键和外键类型要一致 设置参照完整性后,外键中的值,必须是另外一张表中的主表的内容 
--建立一个从表 create table student_class( sid int(11) primary key auto_increment comment '学生ID' , sname varchar(20) unique comment '学生姓名' ); -- 建立一个主表 create table class( cid int primary key auto_increment comment '班级ID', cname varchar(20) unique comment '班级名称', yname varchar(20) ); -- 学生表与班级表之间,没有办法描述他们之间的关系 -- 为学生表添加一个cid字段 alter table student_class add column cid int ; -- class是主表,约束从表中的数据,student_class 是从表 -- 外键 : foreign key  -- references : 指向哪个表的哪一个字段 alter table student_class add constraint foreign key s_c_id(cid) references class(cid) -- 添加方式 -- alter添加:alter table tab add constraintsc_tc_fk foreign key(sid) references stu(sid) -- 删除 -- 必须要先把从表中的数据移除后,才能删除主表中的数据 
表与表的关系
  1. 一对一关系
  2. 一对多关系
  3. 多对多关系
    三个表:学生表、老师表、中间表
讯享网-- 多对多需引入第三个表来对两表进行联系  create table stu_tea( sid int primary key auto_increment, sname varchar(20) unique not null ); create table tea_stu( tid int primary key auto_increment, tname varchar(20) unique not null ); create table stu_tea_ref( sid int , tid int ); -- 建立两个外键 alter table stu_tea_ref add constraint stu_sid foreign key (sid) references stu_tea(sid) -- 从表 自定义外键名称 外键字段 主表中对应的字段 alter table stu_tea_ref add constraint stu_tid foreign key (tid) references tea_stu(tid) 

数据库设计基础

第一范式

  1. 概述

每一列属性都是不可再分的属性值,确保每一列的原子性。


讯享网

两列的属性相近或相似或一样,尽量合并成属性一样的列,确保不产生冗余数据。

第二范式

第二范式实在第一范式的基础上建立起来的,即满足第二范式必须先满足第一范式。

第二范式要求数据库表中的每一个实例或行必须可以被唯一的区分。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识。

比如有两个主键,不能存在这样的属性,他只依赖于其中一个主键,这就不符合第二范式。

通俗解释:任意一个字段都只依赖表中的同一个字段,比如每个帮派只能有一个老大。(也就是说只能依赖一个主键)

第三范式

在满足第二范式的基础上,数据不能存在传递关系,非主键字段不能相互依赖。

像:a–>b–>c 属性之间含有这样的关系,是不符合第三范式的。(就是说非主键之间不能有上下级的关系,只能是平级关系)

合并结果集

  1. 合并结果集就是讲两个select语句的查询结果合并在一起
  2. 合并结果集有两种方式
    UNION:合并时去除重复记录
    UNION ALL:合并时不去除重复记录
  3. 语法格式
    select * from 表1 union select * from 表2
    select * from 表1 union all select * from 表2
  4. 注意事项
    被合并的结果:列的数量及类型必须完全一样,否则报错
    字段名可以不一样,但类型必须一样

5.案例演示

-- 建立两个表 create table msg( mid int , title varchar(20), content varchar(100) ); create table msg_history( mid int , title varchar(20), content varchar(100) ); -- 应用场景 : 业务表与业务历史表,如消息与消息历史,订单与订单历史 -- 给表msg插入三条数据 insert into msg values (1 , '购物提醒1' , 'xxxxxxxxxxx'); insert into msg values (2 , '购物提醒2' , 'xxxxxxxxxxx'); insert into msg values (3 , '购物提醒3' , 'xxxxxxxxxxx'); -- 给表msg——history插入三条数据 insert into msg_history values (1 , '购物提醒历史1' , 'xxxxxxxxxxx'); insert into msg_history values (2 , '购物提醒历史2' , 'xxxxxxxxxxx'); insert into msg_history values (3 , '购物提醒历史3' , 'xxxxxxxxxxx'); -- 合并查询结果集,相同去重 select * from msg union select * from msg_history; -- 合并查询结果集,相同的不去重 select * from msg union all select * from msg_history; 

连接查询

  1. 连接查询也叫跨表查询,将多个表关联获取结果,将多个列合并在一起
  2. 同时查询两张表,默认出现的就是笛卡尔积结果[交叉连接]
    如:A表有k个字段,n条记录;B表有j个字段,m条记录
    默认产生结果集记录为:m*n条,产生的字段数量为:k+j
  3. 上述产生的结果集基本不可用,需要优化
    查询时保持主键与外键一致
    主表的主键与从表的外键保持一致(引用完整性)
  4. 根据连接方式得到取出满足业务的**效果
    连接方式:内连接、外连接、交叉连接
  5. 查询时不需要建立外键,外键只是用来保证引用完整性的手段
讯享网-- 连接查询 -- 默认查出来的是笛卡尔积 3 * 3 = 9条数据量 select * from msg , msg_history; 

内连接

  1. 使用比较运算符(=、>、<、<>、>=、<=、!>、!<)进行标间的比较操作,查询与连接条件相匹配的数据,内连接只返回满足连接条件的数据行。
-- 内连接 select * from msg , msg_history where msg.mid = msg_history.mid; -- 建立实例所需要的表 create table user( user_id int not null auto_increment comment '用户ID', user_name varchar(30) comment '用户名', credit int(11) comment '经验值', primary key (user_id) ); alter table user comment '用户信息表'; create table topic( topic_id int not null auto_increment comment '帖子ID', release_user_name int comment '发帖人ID', title varchar(100) comment '标题', primary key (topic_id) ); alter table topic comment '发帖信息表'; create table reply( reply_id int not null auto_increment comment '用户ID', user_id int comment '回复用户ID', topic_id int(11) comment '帖子ID', reply_content text comment '回复内容', primary key (reply_id) ); alter table reply comment '用户评论表'; -- 插入用户数据 insert into user(user_name , credit) values ('zs' , 120) , ('ls' , 70) , ('ww' , 20); -- 插入帖子信息 insert into topic (release_user_id , title) values (1 , '帖子1') , (1 , '帖子2') , (2 , '帖子3'); -- 插入回复帖子信息 insert into reply (user_id , topic_id , reply_content) values (2 , 1 , '2号回复1号帖子') , (2 , 2 , '2号回复2号帖子') , (3 , 3 , '3号回复3号帖子'); 
讯享网-- 内连接---等值连接 select * from user u inner join topic t on u.user_id = t.release_user_id where 1 = 1; -- 去除笛卡尔积的写法(简写版) select * from `user` u , topic t where u.user_id = t.release_user_id and 1 = 1; 
-- 内连接---非等值连接 select * from `user` u , topic t where u.user_id != t.release_user_id and 1 = 1; -- 等值连接加上非等值连接 == 笛卡尔积 

注意:A表中的ID与B表中的ID一定具有主外键关系

讯享网-- 添加一个等级记表 create table grade( grade_id int not null auto_increment comment '等级ID', low_credit int(11) comment '最低值', high_credit int (11) comment '最低等级', grade int(1) comment '等级', primary key (grade_id) ); alter table user comment '积分等级表'; insert into grade(low_credit , high_credit , grade) values (0 , 50 , 1) , (50 , 100,2) -- 算出用户等级 -- 非等值连接 select u.user_name , t.title , g.grade from `user` u join topic t join grade g on u.user_id = t.release_user_id where 1 = 1 and u.credit > g.low_credit and u.credit < high_credit; select u.user_name , g.grade from `user` u , grade g where u.credit between g.low_credit and g.high_credit; 

=========================================待续。。。。。

小讯
上一篇 2025-03-20 11:42
下一篇 2025-02-08 11:23

相关推荐

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/112745.html