学习总是需要记忆遗忘再记忆反反复复才能印入脑海。IT技术尤其如此。最好解决遗忘的方式,就是多多反复,然而捧起一本大部头从新看起显然效率低下。对于数据库入门级别的知识,看过一两遍,也就能理解主要机理了,欠缺在于代码练习。因此最近在重刷MySQL入门的同时,总结起来练习所使用的代码。用于以后的温习。某一天发现自己生疏了,花一点时间敲上一通,记忆就回来了。
1 大部分的语句都非常简单,进行了少量的注释;
2 代码稍有一些冗余,是为了熟练操作;
3 有意设置了一些错误,注意数据库的返回的信息;
4 所有测试在MariaDB上进行,MariaDB和MySQL几乎就是一样,但如果有错误,系手误或理解错误,望担待;
5 本文共100句代码,覆盖从数据库创建删除,表创建删除修改,键创建删除,索引创建删除的内容,后续会继续增加;
延续我之前的MySQL入门练习100句,增加到200句,这部分主要涉及SQL语言的增删改查。比较烦琐的是查询,MySQL支持非常灵活多样的查询。这立业之涉足皮毛,但对于熟悉操作仍然有益。部分内容稍欠周详,在后续我自己练习的时候会再补上。
//重建数据库 create database testdatabase;use database testdatabase; create table tt1(id int, name varchar(20),age int,sex boolean); show tables;desc tt1; //插入 insert into tt1 values(1,"zhang",25,0); insert into tt1 values(2,"wang",25,1); select * from tt1; insert into tt1(id,name,age,sex) values(3,"li",28,1); insert into tt1(id,name,sex,age) values(4,"sun",0,22); insert into tt1(id,name,sex) values(5,"zhao",30,1); insert into tt1(id,age,name) values(6,"he",47,0); insert into tt1(id,age,name) values(7,"chen",22,1),(7,"zhang",22,1),(7,"xie",32,1); select * from tt1; //修改 update tt1 set id=10,name="new",age=100,sex=0 where id=1; select * from tt1; update tt1 set id=11,name="new" where id=2,age=25; select *from tt1; update tt1 set id=12,sex=1 where id=7; select * from tt1; update tt1 set sex=1 where id>3; update tt1 set sex=0 where id<4; //删除 delete from tt1 where id=1;select * from tt1; delete from tt1 where id=12;select * from tt1; //查询 alter table tt1 add address varchar(30); update tt1 set address="Beijing" where sex=1; update tt1 set address="Shanghai" where sex=0; //简单查询 select id from tt1; select id,name from tt1; select id,name,address from tt1; //条件查询 select id,name,address from tt1 where address="Beijing"; select * from tt1 where id in(2,3,4); select * from tt1 where id not in(2,3,4); select * from tt1 where id between 2 and 5; select * from tt1 where id not between 2 and 5; select * from tt1 where address like "beijing"; select * from tt1 where address like "bei"; select * from tt1 where address like "bei%"; select * from tt1 where address not like "bei%"; select * from tt1 where address is null; select * from tt1 where address is not null; select * from tt1 where age<20 and sex=1; select * from tt1 where sex=0 or age>30; //查询结果不重复 select distinct address from tt1; //查询结果排序 select * from tt1 order by age; select * from tt1 order by age asc; select * from tt1 order by age desc; //分组查询 select * from tt1 group by sex;//单独使用group by 只会选择每个分组的一条记录 //group by 与 group_concat结合使用 select group_concat(name),sex from tt1 group by sex; select group_concat(name),group_concat(age),sex from tt1 group by sex; select group_concat(name,age),sex from tt1 group by sex; //group by与集合函数结合使用 select sex,count(sex) from tt1 group by sex; select sex,count(sex) from tt1 group by sex having sex>2;//having用于对分组后的结果加限制条件 select sex,count(sex) from tt1 group by sex having count(sex)>2; //with rollup select sex,count(sex) from tt1 group by sex with rollup;//在显示记录的最后一行加一条,记录是上面所有记录的和,通常用于合计数量 //limit显示限制 select * from tt1; select * from tt1 limit 2; select * from tt1 limit 3; select * from tt1 limit 0,2; select * from tt1 limit 1,2; //使用集合函数查询 //为了更好的理解本问题,新建一个表 create table grade(id int,name varchar(10),subject varchar(10),score int,sex boolean); insert into grade values(1,"wang","math",100,1),(1,"wang","english",96,1),(1,"wang","physics",90,1); insert into grade values(2,"li","math",96,1),(2,"li","english",85,1),(2,"li","physics",99,1); insert into grade values(3,"sun","math",85,0),(3,"sun","english",98,0),(3,"sun","physics",80,0); select * from grade; select count(*) from grade; select id,name,sum(score) from grade where id=1; select id,name,sun(score) from grade group by id; select id,name,sum(score) from grade group by id order by sum(score) desc; select id,name,avg(score) from grade where id=2; select id,name,avg(score),sum(score) from grade where id =3; select id,name,avg(score) from grade group by id; select subject,avg(score) from grade group by subject;* from select subject,avg(score) from grade group by subject order by avg(score); select name,max(score) from grade where subject="math"; select name,max(score) from grade where subject="english"; //连接查询 //内连接 create table stu(id int,name varchar(10),age int,sex boolean); insert into stu values(1,"wang",25,1),(2,"li",23,1),(3,"sun",23,0),(4,"zhou",27,1),(5,"zhang",22,0); select id,name,age,sex,score from stu,grade where stu.id=grade.id; select stu.id,stu.name,stu.age,stu.sex,score from stu,grade where stu.id=grade.id; select stu.id,stu.name,stu.age,stu.sex,score from stu,grade where stu.id=grade.id and score>90; //外连接 select stu.id,stu.name,stu.age,stu.sex,score from stu left join grade on stu.id=grade.id; select stu.id,stu.name,stu.age,stu,sex,score from stu right join grade on stu.id=grade.id; insert into grade values(4,"hu","math",99,1),(5,"liu","english",97,0); select stu.id,stu.name,stu.age,stu.sex,subject,score from stu left join grade on stu.id=grade.id; select stu.id,stu.name,stu.age,stu.sex,subject,score from stu right join grade on stu.id=grade.id; //子查询 select * from stu where id in (select *from grade); select * from grade where id in (select * from stu); create table scholarship(level int,money int,score int); insert into scholarship values(1,10000,90),(2,5000,85),(3,3000,80); select id,name,score from grade where score>=(select score from scholarship where level=1); select id,name,score from grade where score>=(select min(score) from scholarship); //exists子查询 select * from stu where exists (select name from grade where id=1); select * from grade where score>any(select score from scholarship); select * from grade where score>all(select score from scholarship); //合并查询结果 select name from stu union select name from grade; select name from stu union all select name from grade; //别名 select * from scholarship s where s.level=1; select * from scholarship s where s.money=5000; select s.name from stu s,grade g where s.name=g.name; select subject as su,score as sc from grade; select * from stu where name regexp "^w"; select * from stu where name regexp "g$"; select * from stu where name regexp "^w.g$"; select * from stu where name regexp "^w..g$"; select * from stu where name regexp "[aeo]"; select * from stu where name regexp "[a-g]";