update更新数据
UPDATE 表名
SET column_name = value [ , column_name2 = value2, …. ] [ WHERE condition ];
1 column_name 为要更改的数据列
2 value 为修改后的数据,可以为变量、具体值、表达式或者嵌套的SELECT结果
3 condition为筛选条件,如不指定则修改该表的所有列数据
select * from grade; update grade set gradename='6年级' where gradeid=6; select * from myschool.mystu; update myschool.mystu set name='方董',gradeid=10 where id=3
删除表的数据
insert into demo1(username,age) values('李磊',39),('韩梅梅',36); select * from demo1; delete from demo1 where id =2; select * from demo1;#删除韩梅梅
删除大于38岁
delete from demo1 where age>37; select * from demo1;
将表恢复到刚创建好表的时刻
#删除数据清空 truncate table demo1; select * from demo1;
第三个ppt使用DQL命令查询数据
案例:MySchool数据库
DQL(Data Query Language,数据查询语言)
1 查询数据库数据,如SELECT语句
2 简单的单表查询或多表的复杂查询和嵌套查询
3 数据库语言中最核心、最重要的语句
4 使用频率最高的语句
SELECT语法 -----重要
SELECT [ALL | DISTINCT]
{ * | table.* | table.field1 [ as alias1] [, table.field2 [as alias2]] }
FROM table_name [ as table_ alias ]
[ left|out|inner join table_name2 ] #联合查询
[ WHERE … ] #指定结果需满足的条件
[ GROUP BY …] #指定结果按照哪几个字段来分组
[ HAVING …] #过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序
[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; #指定查询的记录从哪条至哪条
[] 括号代表可选的;
{} 括号代表必须的;
#MySQL语句中的注释符,也可以用 /该处为注释/
select语句
as加不加都行 ----可以给数据列,表或者计算和总结的结果,赋值一个名字
select demo1.*from demo1; #as加不加都行 可以给数据列,表或者计算和总结的结果,赋值一个名字 select tb1.username as uname, tb1.age as uage from demo1 as tb1; select tb1.username uname, tb1.age uage from demo1 tb1; #将demo1表起别名为tb1 select username , age from demo1 ;#不加会飘红,但仍然可执行,原因:编译器不能识别
distinct关键字的使用
去除表中指定列相同的数据
select distinct t1.username, t1.age from demo1 t1;
select version();#返回当前系统版本
select 1+1; select 3*200;
生成课程表
select * from subject; insert into subject values (1,'高等数学-1',120,1), (2,'高等数学-2',110,2), (3,'高等数学-3',100,3), (4,'高等数学-4',130,4); select subjectname,classhour +30 from subject where gradeid=2; select * from grade; select * from student; select subjectname as '课程名称', classhour as '总课时', classhour/10 as '均课时/天' from subject;
where条件语句
1 用于检索数据表中符合条件的记录
2 搜索条件可由一个或多个逻辑表达式组成,结果一般为真或假
3 搜索条件的组成
(1)逻辑操作符
(2)比较操作符
案例准备:
创建result表,往student表和result表里放值
#创建result表 reate table if not exists result( studentno int(10), subjectno int(10), examdate datetime, studentresult int(10) ); #赋值student表 select * from student; show create table student; insert into student values (1001,'1234','张三',1,1,'15852520365','安德门','1999-04-11 12:12:21','zs@qq.com','320201199904112318'); truncate table student; insert into student values (1001,'1234','张三',1,1,'18967445215','安德门','1999-04-11 12:12:21','zs@qq.com','320201199904112318'), (1002,'3245','李四',1,1,'13802293888','中华门','1998-01-11 12:12:21','ls@qq.com','320201199801112318'), (1003,'7970','王五',1,1,'13935266871','清凉门','1997-06-11 12:12:21','ww@qq.com','320201199706112318'), (1004,'2764','赵六',1,1,'13365412352','水西门','1996-09-11 12:12:21','zl@qq.com','320201199609112318'), (1005,'9802','周七',1,1,'18688942596','集庆门','1999-12-11 12:12:21','zq@qq.com','320201199912112318'); select * from student; select * from result; select * from subject; select * from grade; #赋值result表 insert into result values (1001,1,'2021-12-31 9:30:00',98), (1002,1,'2021-12-31 9:30:00',68), (1003,1,'2021-12-31 9:30:00',85), (1004,1,'2021-12-31 9:30:00',100); select * from result;
where查询
逻辑操作符
操作符名称 | 语法 | 描述 |
---|---|---|
AND**或&&** | a AND b 或 a && b | 逻辑与,同时为真,结果才为真 |
OR**或||** | a OR b 或 a||b | 逻辑或,只要一个为真,则结果为真 |
NOT**或!** | NOT a 或 !a | 逻辑非,若操作数为假,结果则为真 |
select * from result where studentresult>=60 and studentresult<=90; select * from result where studentresult between 60 and 90; select * from result where studentresult>90 or studentresult<80 and studentresult>70;#and 的优先级比or高
比较操作符
操作符名称 | 语法 | 描述 |
---|---|---|
IS NULL | a IS NULL | 若操作符为NULL,则结果为真 |
IS NOT NULL | a IS NOT NULL | 若操作符不为NULL,则结果为真 |
BETWEEN | a BETWEEN b AND c | 若a范围在b与c之间则结果为真 |
LIKE | a LIKE b | SQL模式匹配,若a匹配b,则结果为真 |
IN | a IN (a1,a2,a3,….) | 若a等于a1,a2…中的某一个,则结果为真 |
select * from student; select * from student where address like '_凉_';#匹配一个 select * from student where studentname like '_五'; select * from student where address like '%门';#模糊查询,匹配多个 select * from student where address like '%门%'; select * from student where loginpwd like '%2%';
not null若操作符不为NULL,则结果为真
select * from demo1; alter table demo1 change age age int(10); #直接修改null值,点击DB进行上传,即成功 select t.* from demo1 t where t.age is null; select t.* from demo1 t where t.age is not null;
in在。。。里面
在WHERE子句中使用IN进行范围查询
SELECT 字段列1,字段2 ,…FROM 表名 WHERE 字段x IN ( 值1,值2,值3…)
查询的字段x的值,至少与括号中的一个值相同
多个值之间用英文逗号隔开
SELECT * FROM subject where ClassHour = 100 OR ClassHour =110 OR ClassHour = 120; #普通处理方式
SELECT * FROM subject where ClassHour IN ( 100, 110,120 );#使用IN进行查询方式,更为简洁,效率更高
select * from result where studentresult in (100,85); select * from result where studentresult not in (100,85);
BETWEEN AND(between and)范围查询
SELECT 字段列1,字段2 ,…FROM 表名 WHERE 字段x
BETWEEN 值1 AND 值2
#查询课程表中课时在110和120之间的所有记录
SELECT * FROM subject WHERE ClassHour BETWEEN 110 AND 120;
等同于: SELECT * FROM subject WHERE ClassHour >= 110 AND ClassHour <=120;
连接查询(多表查询)--重点
如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
分类包括
1 内连接 ( inner join)
等值和非等值的连接查询
自身连接查询
2 外连接 ( out join )
左连接(LEFT JOIN)
右连接 ( RIGHT JOIN)
外连接
select r.*,s.* from result r left join subject s on r.subjectno=s.subjectno;
select r.*,s.* from subject s left join result r on r.subjectno=s.subjectno;
####
JOIN对比
操作符名称 | 描述 |
---|---|
INNER JOIN ( JOIN ) | 如果表中有至少一个匹配,则返回行 |
LEFT JOIN | 不论右表是否有匹配,都会返回左表的所有行 |
RIGHT JOIN | 不论左表是否有匹配,都会返回右表的所有行 |
自连接查询
数据表与自身进行连接
从一个包含栏目ID,栏目名称和父栏目ID的表中,查询父栏目名称和其子栏目名称,
案例一 CREATE TABLE IF NOT EXISTS category( categoryId int(10) auto_increment primary key, categoryName varchar(32) not null , pid int(10) ); use myschool; select * from category; insert into category values(1,'软件开发',-1),(2,'javaWeb',1),(3,'WebUI',1),(4,'Bigdata',1); select * from category; select c1.*,c2.* from category c1 left join category c2 on c1.pid=c2.categoryID;#c1 和c2 是都是category
案例二
create table if not exists areatb( areaid int(10) auto_increment primary key , areaname varchar(32) not null, pid int(10) ); alter table areatb add tag int(10) not null ; insert into areatb(areaid,areaname,pid,tag) value (1,'中国',0), (2,'山西省',1), (3,'江苏省',1), (4,'安徽省',1), (5,'太原市',2), (6,'大同市',2), (7,'朔州市',2), (8,'习州市',3), (9,'南京市',3), (10,'苏州市',3,3), (11,'徐州市',3,3), (12,'',3,3), (13,'镇江市',3,3), (14,'滁州市',4,3), (15,'马鞍山市',3,3), (16,'蚌埠市',4,3), (17,'合肥市',4,3), (18,'雨花台区',9,4), (19,'鼓楼区',9,4), (20,'建邺区',9,4), (21,'六合区',9,4),
查询案例
select * from areatb; #找出南京市下辖区信息 select t2.* from (select * from areatb where areaname='南京市')t1 left join areatb t2 on t1.areaid=t2.pid; #找出马鞍山市属于哪个省,国家? select country.areaname ,provice.areaname provice ,city.areaname city from (select *from areatb where areaname='马鞍山市') city left join areatb provice on city.pid=provice.areaid left join areatb country on country.areaid =provice.pid; #找出大同市同省内的兄弟城市 select t2.areaname from (select * from areatb where areaname='大同市')t1 left join areatb t2 on t1.pid=t2.pid;
LIMIT 限制
LIMIT [m,]n 或 LIMIT n OFFSET m
1 限制SELECT返回结果的行数
2 m 制定第一个返回记录行的偏移量
3 n 制定返回记录行的最大数目
注:m不指定则偏移量为0,从第一条开始返回前n条记录LIMIT 常用于分页显示
SELECT * FROM `result` LIMIT 5 #返回前5条记录 SELECT * FROM `result` LIMIT 5,10 #返回6-15条记录
order by排序
select * from result order by studentno asc;#asc
分页查询
select * from result order by studentno asc;#asc select * from result order by studentno asc limit 0,2;#page=1,pageSize=2 select * from result order by studentno asc limit 2,2;#page=2,pageSize=2 select * from result order by studentno asc limit 4,2;#page=3,pageSize=2 #分页 # m:(page-1)*pagesize,n:pageSize————分页公式
select*from student; #查询学生信息表,每页显示三条学生信息,查出第二页的学生信息 select * from student limit 3,3;
子查询
在查询语句中的WHERE条件子句中,又嵌套了另外一个查询语句
案例
查询课程为《高等数学-2》且分数不小于80分的学生的学号和姓名
注: 嵌套查询可由多个子查询组成,求解的方式是由里及外子查询返回的结果一般都是集合,故而建议使用 IN 关键字
#使用子查询找出科目高等数学-1且成绩80分以上的学生学号和姓名 select * from subject; select studentno,studentname from student where studentno in (select studentno from result where subjectno = (select *from subject where subjectname='高等数学-1') and studentresult>=80 );
#参加过高等数学-1考试的学生studentno,studentname select studentno,studentname from student where studentno in ( select studentno from result where subjectno = (select subjectno from subject where subjectname = '高等数学-1')); select *from student; #未参加过高等数学-1考试的学生studentno,studentname select studentno,studentname from student where studentno not in ( select studentno from result where subjectno = (select subjectno from subject where subjectname = '高等数学-1'));
查询《高等数学-1》的前5名学生成绩信息
select st.studentno,st.studentname,t1.studentresult from ( select studentno, studentresult from result where subjectno = (select *from subject where subjectname = '高等数学-1') order by studentresult desc limit 0,3 )t1 left join student st on t1.studentno=st.studentno where st.studentname is not null order by t1.studentresult desc ;
select s2.studentno,s2.studentname,r.studentresult from subject s inner join result r on s.subjectno = r.subjectno inner join student s2 on r.studentno = s2.StudentNo where s.subjectname='高等数学-1' order by r.studentresult desc limit 3;
MySQL函数
1 数学函数
2 字符串函数
3 日期和时间函数
4 系统信息函数
select count(studentno) num,max(studentresult) maxscore,min(studentresult) minscore,avg(studentresult) avgscore, sum(studentresult) sumsocre from result where subjectno=1;
统计函数
函数名称 | 描述 |
---|---|
COUNT( ) | 返回满足SELECT条件的记录总和数,如 SELECT COUNT(*)… |
SUM( ) | 返回数字字段或表达式列作统计,返回一列的总和 |
AVG( ) | 通常为数值字段或表达列作统计,返回一列的平均值 |
MAX( ) | 可以为数值字段、字符字段或表达式列作统计,返回最大的值 |
MIN( ) | 可以为数值字段、字符字段或表达式列作统计,返回最小的值 |