mysql并表函数_mysql 之表常用函数

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 常用的内置函数