MySQL试题-测试、复习、查看

设计三张表创建student表（学生表）Create table student(Sid tinyint primary key auto_increment,Sname char(10),Sex char(5),Sage tinyint,Sdept char(20));insert into student(Sid,Sname,Sex,Sage,Sdept) values(1,'张三','女',20,'IT ');insert into student(Sid,Sname,Sex,Sage,Sdept) values(2,'李四','女',25,'info ');insert into student(Sid,Sname,Sex,Sage,Sdept) values(3,'王五','男',22,'en ');insert into student(Sid,Sname,Sex,Sage,Sdept) values(4,'马云','女',28,'ch');insert into student(Sid,Sname,Sex,Sage,Sdept) values(5,'赵子龙','女',18,'info ');insert into student(Sid,Sname,Sex,Sage,Sdept) values(6,'王小','女',21,'info ');insert into student(Sid,Sname,Sex,Sage,Sdept) values(7,'赵龙龙','男',24,'en ');创建course表（课程表）Create table course(Cid tinyint primary key auto_increment,Cname char(20),Ccredit tinyint);insert into course values(1,'数据结构',5);insert into course values(2,'数据库',10);insert into course values(3,'英语',3);insert into course values(4,'数学',6);创建sc表（选课表）create table sc(Sid tinyint,Cid int,Grade int);insert into sc values(1,1,65);insert into sc values(1,2,60);insert into sc values(1,4,90);insert into sc values(1,3,50);insert into sc values(2,2,100);insert into sc values(3,1,90);insert into sc values(6,2,70);insert into sc values(4,4,90);insert into sc values(5,1,95);insert into sc values(6,1,80);insert into sc values(7,3,80);创建admin表create table admin(id int(10),qq int(20),uid int(5));创建province表create table province(id int(10) primary key auto_increment,pname varchar(30));insert into province(id,pname) values(2,'上海');insert into province(id,pname) values(3,'广州');insert into province(id,pname) values(4,'成都');创建student2表create table student2(id int(10) primary key auto_increment,name varchar(30),pid int(10),foreign key(pid) references province(id) on delete cascade);正则查询建表创建表user两个字段一个id一个nameCreate table user (id int(10),name varchar(20));Insert into user (name) values('bat'),('baet'),('baaset'),('atath'),('astst');创建一个表，两个字段，商品名称，商品价格Create table goods(id int(10) primary key,goodsName varchar(20),price int(20));Insert into goods Values(1,'HuaWei',1000);Insert into goods Values(2,'Vivo',1500);Insert into goods Values(3,'iphone',3000);Insert into goods Values(4,'honor',600);Insert into goods Values(5,'HuaWei2',2000);Create table student1(Id int(10) primary key not null auto_increment,Username varchar(30) unique,Password varchar(30),Sex int(10) default 20);创建表indexd表Create table indexd(id int primary key auto_increment,name varchar(20));创建存储过程\d //create procedure p10(num int)begindeclare i int default 0;while i<num doinsert into indexd(name) values(concat('user',i));set i=i+1;end while;end//call p10(1000);创建一个user1表create table user1(id tinyint(4) primary key auto_increment,name char(10),price int(10));1、查询student表全体学生学号和姓名select Sid,Sname from student;2、查询sc表选修了课程的学生学号select Sid from sc group by Sid;3、查询考试成绩不及格的学生select distinct (student.Sname) from student,sc where sc.Grade < 60 and sc.Sid=student.Sid;4、查询不在20-30岁年龄的学生select * from student where Sage not between 20 and 30;5、查询info、en系的学生姓名和年龄select Sname,Sage,Sdept from student where Sdept in('info','en');6、查询所有姓王的学生信息Select * from student where Sname like '王%';7、查询所有不姓王的学生信息Select * from student where Sname not like '王%';8、查询计算机系(info)年龄在20岁一下的学生姓名select Sname from student where Sdept = 'info' and Sage < 20;9、查询选修了3号课程的学生学号及其成绩，查询结果按分数排序Select Sid, Grade from sc where Cid=3 order by Grade;10、查询了选修1号课程学生的平均成绩select avg(Grade) from sc where Cid=1;11、查询选修3门以上课程的学生学号Select Sid from sc group by Sid having count(Cid)>3;12、查询选修2号课程且成绩在90分以上的学生select * from student ,sc where sc.Cid=2 and sc.Grade>=90 and sc.Sid=student.Sid;13、查询选修了所有课程的同学select * from student where not exists(select * from course where not exists (select * from sc where sc.Sid=student.Sid and sc.Cid=course.Cid));14、计算pi()/4的角度select degrees(pi()/4);15、绝对值函数：求-3的绝对值Select abs(-3);17、求3.45向上取整Select Ceil(3.45);18、求90°正弦的值Select Sin(90*(pi()/180));19、求e^2自然常量Select Exp(2)20、求以10为底，100的对数的值;Select log10(100);21、截取小数点2.5935471358后5位数Select Truncate(2.5935471358,5);22、随机生成一个三位数的随机数select floor(rand()*900+100);23、计算8的三次方Select pow(8,3);24、截取字符串‘hello world’,输出world或者helloselect left('hello world',5);select right('hello world',5);select substring('hello world',1,5);//1：表示从哪个位置开始5：表示截取的长度select substring('hello world',7,5);25、替换字符串my name is han,把han替换成自己的名字Select replace('my name is han','han','huqianwei');select insert('my name is han',12,3,'wangyuanyuan');26、填充字符串，在字符串hello后面添加自己的名字：如hello lucyselect rpad('hello ',8,'hu');27、在数据表admin结构中增加一个字段dep条件约束唯一varchar(30)类型Alter table admin add dep varchar(30) unique ;28、在数据表admin结构中把dep字段改为sexAlter table admin change dep sex varchar(30);29、修改表名称将数据表名称admin修改为SadminAlter table admin rename Sadmin;alter table admin rename to Sadmin;30、修改数据类型将admin表qq数据类型int，修改为varchar(20);alter table admin modify qq varchar(20);31、在admin表中添加一个salary字段类型为varchar(10)Alter table admin add salary varchar(10);32、在admin表中修改字段salary 唯一性约束uniquealter table admin modify salary varchar(10) unique;33、在admin表中在id字段后面添加一个depid字段int(10)alter table admin add depid int(10) after id;34、在admin表中删除uid字段alter table admin drop uid;35、连接字符串my,name,is,mysqlSelect concat('my ','name ','is ','mysql ');36、使用分隔符-连接字符串my、name、is、mysqlSelect concat_ws(‘-’, ‘my’,’name’,’is’,’mysql’);37、查询3是否在4、5、6中Select 3 in (4,5,6);38、在2、5、6、5中哪个数最小select least(2,5,6,5);39、查询3是否在5和9之间Select 3 between 5 and 9;40、求字符串my的长度select length('my');41、左边字符串abcd截取3Select left('abcd',3);->abc42、字符串abcd反转Select Reverse('abcd');->dcba43、小写abcd转大写select upper('abcd');值：ABCD44、字符串比较：比较aa 与bb 大小select strcmp('aa','bb');45、向字符串hello左边填充字符串abc，填充后的长度为9select lpad(' hello',9,'abc');46、向字符串hello右边填充字符串abc，填充后的长度为9select rpad('hello',9,'abc');47、返回当前日期+时间select now();48、返回当前日期select current_date();49、返回系统时间Select sysdate();50、返回当前时间的年Select year(now());值：当前的年51、返回2017-4-1第几周Select week('2017-4-1');值：1352、返回当前时间是星期几select dayofweek(now());//1表示星期天，2表示星期一···53、返回2017-4-6年当中的第几天Select dayofyear('2017-4-6');值：96;54、返回2017-4-6和2017-4-1两个日期相隔天数Select datediff('2017-4-6','2017-4-1');55、查看学生表的所有信息Select * from student;56、查询user 表中b开头的字段数据Select * from user where name regexp '^b';57、查询user 表中b与t之间有单个字符的数据Select * from user where name regexp 'b.t';//…….58、查询user 表中有a或者b字符的数据Select * from user where name regexp '[a,b]';59、查询选修课学生及格的同学Select * from sc group by Sid having Grade>=60;60、查询所有学生选修的几门课Select Sid,count(*) from sc group by Sid;61、查询所有学生选修课的平均成绩Select Sid,avg(Grade) from sc group by Sid;62、平均成绩大于60的学号和平均成绩的学生Select Sid,avg(Grade) from sc group by Sid having avg(Grade)>=60;63、新建一个以自己名字为名称的数据库Create database huqianwei;64、创建学号（id）、姓名（Name）的student1表Create table student1(Id int,Name varchar(30));65、创建存储过程p1，查询student表的所有信息\d //Create procedure p1()beginselect * from student;end//Call p1()//66、删除存储过程p5Drop procedure p5//不能加括号67、查询学号最大的学生姓名及年龄Select Sname,Sage from student order by Sid desc limit 1;68、查看student表结构desc student;69、逻辑运算：逻辑1与0Select 1&&0;Select 1and0;70、逻辑运算：逻辑1或0Select 1||0;Select 1or0;71、逻辑运算：逻辑非0Select !0;72、存储过程p3求c=a+b加法运算求3=1+2create procedure p3(a int,b int)begindeclare c int default 0;set c=a+b;select c as sum;end//call p3(1,2)//73、在indexd索引表里添加索引indexNameAlter table indexd add index indexName(name);74、在indexd索引表里删除索引indexNameAlter table indexd drop index indexName;Drop index indexName on indexd;75、查询goods表中price价格在500到2000的商品Select * from goods where price>=500 and price<2000;76、查询goods表中商品名称goodsName以H开头Select * from goods where goodsName like 'H%';77、查询goods表中当前价格price是否在（3000，500，800，400，200）select * from goods where price in (3000,500,800,400,200);78、求余：5/2的余Select Mod(5,2);79、返回2017-4-1时间的季度Select quarter('2017-4-1');值：280、时间10:20:30转化为秒Select time_to_sec('10:20:30'); 值：3723081、在indexd表中100<id<200里创建视图vindexdCreate view vindexd as select * from indexd where id>100 and id<200;82、删除indexd表的视图vindexddrop view vindexd;83、创建触发器t1,在indexd表中的每行插入indexd2Create trigger t1 before insert on indexd for each rowBeginInsert into indexd2(name) values();End//84、向province表pname字段插入数据:’重庆’insert into province(pname) values("重庆");85、向student2表插入学生信息数据name=自己的名字、pid=2（注意student2有外键）insert into student2(name,pid) values("自己的名字",2);86、删除province表id为2的数据delete from province where id=2;87、删除student2中的外键alter table student2 drop foreign key student2_ibfk_1;88、修改admin表引擎为myisamalter table admin engine=myisam;89、显示数据库引擎show engines;90、向user1表中name,price插入入多条数据insert into user1(name,price) values('rr',30),('uu',60),('ii',70);。