– 表中增加一列 alter table student add column info text; alter table student add column info text after age; alter table student add column ingo text first; – 删除表中一列 alter table student drop column info text; – 修改表名 rename table student to students; alter table student rename to studtents; – 随机查询两条数据，对大型数据库不可以，没有缓存，而且效率低 select * from student order by rand() limit 2; select * from student limit 1; – 利用limit关键字将表分为3页，每页2条数据 ,表分为m页，每页n条数据 select * from student limit 0,2; select * from student limit 2,2; select * from student limit 4,2; select * from student limit m*n-n,n; – 跨库查询 select * from mysql.user;
select * from student where sname=‘李一’; select * from student where age>9 && age <13; select * from student where age between 9 and 13; alter table student add column address varchar(200) not null after favorite; update student set address=‘北京市’ where sid = 1; update student set address=‘合肥市’ where sid = 2; update student set address=‘郑州市’ where sid = 3; update student set address=‘北京市’ where sid = 4; update student set address=‘合肥市’ where sid = 5; update student set address=‘郑州市’ where sid = 6;
select * from student where address is not null; select * from student where address is null;
alter table student add column dept varchar(20) after age; update student set dept=‘英语’ where sid = 1; update student set dept=‘计算机’ where sid = 2; update student set dept=‘英语’ where sid = 3; update student set dept=‘计算机’ where sid = 4; update student set dept=‘英语’ where sid = 5; update student set dept=‘语文’ where sid = 6;
select * from student where dept in(‘英语’);
select * from student where sid in(2,4,6); select * from student where sid not in(2,4,6);
– 模糊查询 like 其中% 代表零个或多个任意字符 - 代表一个任意字符 select * from student where sname like ‘_二’; select * from student where sname like ‘%三%’; select * from student where sname like ‘__’; – 正则表达式查询 . 代表任意一个字符 select * from student where sname regexp ‘^…$’;
alter table student add column score tinyint; update student set score = 97 where sid = 1; update student set score = 87 where sid = 2; update student set score = 77 where sid = 3; update student set score = 67 where sid = 4; update student set score = 57 where sid = 5; update student set score = null where sid = 6;
select sid 学号,sname 姓名,score 分数 from student; select max(score) from student; select max(score) 最高分,min(score) 最低分,round(avg(score)) 平均分,sum(score) 总分 from student; select sid 学号,sname 姓名,score 分数 from student where score is null; select concat( max(score),‘分’) 最高分 from student; – 子查询 select * from student where score = (select max(score) from student); select sid 学号,sname 姓名,ifnull(score,‘缺考’) 分数 from student; select count(*) from student; – 查询结果中消除重复 select distinct dept from student; select count(distinct dept) from student;
– 关键字union 联合函数 两个集合交集 union all 两个集合并集 select sex 性别,max(score) 分数 from student where sex = ‘男’ union select sex ,max(score) from student where sex = ‘女’;
update student set sex = null where sid = 2; select * from student; select sex 性别, count() 人数 from student where sex=‘男’ union select sex, count() from student where sex=‘女’ union select ‘保密’, count(*) from student where sex is null;
– group by having order by select dept 专业,count() 人数, concat(count()*2000,‘元’) 奖金 from student group by dept;
– case (when then) end select sid 学号, sname 姓名, ifnull(score,0) 成绩, case when score>90 then ‘优秀’ when score>80 then ‘良好’ when score>70 then ‘中等’ when score>60 then ‘及格’ else ‘补考’ end 等级 from student; – 统计各个分数段人数 select level 等级,count(*) 人数 from ( select if(score>90 ,‘优秀’, if(score>80 ,‘良好’, if(score>70 ,‘中等’, if(score>60 ,‘及格’,‘补考’ ) ) ) ) level from student )as tt group by level;
select 等级, count(*) 人数 from ( select sid 学号, sname 姓名, ifnull(score,0) 成绩, case when score>90 then ‘优秀’ when score>80 then ‘良好’ when score>70 then ‘中等’ when score>60 then ‘及格’ else ‘补考’ end 等级 from student) as tt group by 等级;
– 多表等值查询 只能查询不为空的数据 select * from teacher; select * from student; select sid, sname , tname from teacher t,student s where s.stid = t.tid; update student set stid = null where sid = 5; – 多表查询 join 关键字 可重复join select sid , sname , tname from student s join teacher t; select sid 学号, sname 姓名, ifnull(tname,‘暂无老师’) 老师 from student s left join teacher t on s.stid = t.tid;
– 窗口函数 rank()会跳号 dense_rank()不会跳号 select sid 学号, sname 姓名, score 分数, concat (‘第’,rank() over(order by score desc),‘名’) 名次 from student; – row_number() 排序 select row_number() over(), sname,dept from student order by dept; select row_number() over(), dept, 人数 from (select distinct dept,count(*) 人数 from student group by dept) as tt order by (dept);
– 查看mysql信息 show variables
create table book( id tinyint unsigned auto_increment, name varchar(20), author varchar(30), piece decimal(4,1), pdate date, primary key (id) )engine= innodb charset=utf8 ; drop table book; – 修改表的字段类型，及名称 – change 可改 名称 类型 位置 alter table book change name nameb varchar(40) after id; – modify 可改 类型 位置 alter table book modify name varchar(50) first;
insert into book values(null,’<数据库开发>’,‘jack’,90,‘2010-4-1’); insert book values(null,’<java高级语言>’,‘bill’,120,‘2020-5-19’), (null,’<c#开发>’,‘rose’,70,‘2012-8-26’), (null,’<java开发>’,‘tony’,150,‘2008-7-9’), (null,’<python开发>’,‘ton’,85,‘2019-12-23’), (null,’<java入门到精通>’,‘sam’,110,‘1998-2-15’); select * from book; – 删除表中所有数据 delete from book where 1; truncate book; – 替换表中数据 没有自行加一行 replace book values(1,’<java框架开发>’,‘bob’,99,‘2005-7-15’);
– 建立一个表的备份 供练习 create table bookbak like book; insert bookbak select * from book; – 此方法创建表没有表结构只有表数据 create table booknew as select * from book;
– length() char_length() char() round() ceil() floor() pow() rand() – to_days() 日期转化为天数 select to_days(sysdate()) - to_days(‘2019-4-5’);
– 天数向前向后 date_add select date_add(curdate(),interval -7 day),date_add(current_date(),interval 7 day),curdate(); – 两个日期相差天数 datediff 小时差 timestampdiff(hour, ‘’ ,’’) select datediff(‘2020-06-29’,‘2020-06-25’); select timestampdiff(hour,‘2020-6-30 18:20:30’ ,now())
– last_day 月的最后一天 select last_day(curdate()), last_day(‘1998-5-1’); – 生成月的第一天 select date_add(curdate(),interval -dayofmonth(curdate())+1 day); – 造日期 makedate() 字符串转日期 str_to_date() select makedate(2020,180),str_to_date(‘2020-6-20’,’%Y-%m-%d’); – 日期格式化 date_format( , ‘%Y年%m月%d日 %H时%i分%s秒’) select now(),date_format(now(),’%Y年%m月%d日’),date_format(sysdate(),’%m月%d日’);
– 时间戳日期转换 1593522134(549) from_unixtime(时间戳，[格式]) 可加格式 – unix_timestamp(日期) 获取时间戳 select from_unixtime(1593522134) select unix_timestamp(‘2020-6-30 21:10:10’),unix_timestamp();
– concat group_concat select group_concat(distinct dept) from student; select group_concat(distinct dept separator ‘-’ ) from student; select group_concat(distinct score order by score desc separator ‘>’) from student;
– 重复 repeat() 空格符 space() select repeat(’*’,10),space(10);
– 大写 upper 小写 lower 左截取 right(str,个数) 逆序 reverse(str) – 右截取 left(str，个数 ) 中间截取mid(str，位置，个数 ) – replace(str, str1,str2) str中str1部分替换成str2
– mysql8 加密函数 md5() sha() sha1() sha2(str ,哈希长度hash_length ) select md5(‘admin’),sha(‘admin’),sha1(‘admin’),sha2(‘admin’,224);
– 随机字符串 uuid() 不会重复 uuid_short() select uuid(),uuid(),uuid_short();
– ip与数相互转化 inet_aton() inet_ntoa() select inet_aton(‘192.168.0.102’); select inet_ntoa(3232235622);
