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一个name

Create 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)

begin

declare i int default 0;

while i

insert 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;