select
id 学号,
name 姓名,
ifnull(score,0) 成绩,
case
when score>=90 then '优秀'
when score>=80 then '良好'
when score >=70 then '一般'
when score >=60 then '及格'
else '补考'
end 等级 from stu;
select dj 等级,count(*) 人数 from (select if(score>=90,'优秀',if(score>=75,'良好',if(score>=60,'及格','补考'))) dj from student) as tt group by dj;
having 分组查询的条件
-- 查询统计 优秀多少人 良好多少人 及格 多少人 补考多少人。
select 等级,count(*) from (
select
id 学号,
name 姓名,
ifnull(score,0) 成绩,
case
when score>=90 then '优秀'
when score>=80 then '良好'
when score >=70 then '一般'
when score >=60 then '及格'
else '补考'
end 等级 from stu) as ss group by 等级 having count(*)>=3 order by count(*) desc;
select * from 表名 where group by having order by limit
ifnull(null,'yes')
if()
连接查询 join (多表查询)
UML
E-R
show.do?id=3
mysql 下载安装 mysql5.6 vc++运行库
数据库操作 建立 删除
表的数据类型
int
bigint
tinyint
decimal(9,2) #######.##
char()
varchar()
text
longtext
enum('Y','N')
set('a','b','c','d') 'a' 'c,b' 'a,c,d'
date
time
datetime
longblob
json
表的操作
建立表 create table create table t(t int);
pk
fk
uk
not null
default
修改表 alter table
删除表 drop table
crud 操作
select 查询
where
group by having
order by
limit
表的crud insert插入添加 delete删除 update 修改
-- 数据插入
-- insert into book values(0,'《mysql8.0入门与精通》');
insert into book values(null,'《java 入门》',90,'jack','2020-5-1');
insert book values(null,'《数据库入门》',30,'jack','2020-5-1'),(null,'《java 高级》',120,'jack','2020-6-1');
insert book(name,author) values('《c#程序入门》','bill');
insert book(name,price,author,pdate) select name,price,author,pdate from book;
replace book values(0,'《java框架开发入门》',30,'jack','2020-6-30');
select * from book;
删除
delete
drop database db;
drop table book;
删除的数据表的记录
delete from book;
delete from book where 1;
delete from book where 1=1;
delete from book where true;
create table aa like xxx;
insert aa select * from xxx;
create table aa as select * form xxx;
-- 数据的删除 一定要控制好条件
delete from booknew;
delete from booknew where 1; -- 0 true false 1=1
delete from booknew where id>10;
delete from booknew where name like '%c#%';
-- 删除表的数据 表(结构 + 数据)
truncate booknew;
truncate table booknew;
select * from booknew;
修改
update
-- 修改记录 控制好条件
update book set price = price - 5;
select id,name,price 原价,price - price * .1 现价 from book;
select * from book;
update book set name='《c#程序入门经典》',price=68,pdate='2019-10-20' where id = 4;
mysql 常用的内置函数
