mysql怎么创建成绩小于100_sql练习

标签：

1.写出创建上面这两张表的sql

CREATE TABLE score(

id int(10) NOT NULL AUTO_INCREMENT,

stuId int(10) NOT NULL,

grade int(10) DEFAULT NULL,

c_name varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

CREATE TABLE students (

id int(10) NOT NULL AUTO_INCREMENT,

name varchar(20) NOT NULL,

age int(10) DEFAULT NULL,

sex varchar(4) DEFAULT NULL,

class VARCHAR(20) NOT NULL,

address varchar(50) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

2. 使用while循环和repeat循环写各两个存储过程，传入一个行数，控制插入多少条数据，往第一题创建的两个表中各插入500条数据

delimiter $$;

CREATE PROCEDURE myproc()

BEGIN

Declare i int;

Set i =1;

While i<=500 do

insert into students(name,sex,age,class,address) values(‘lgqest‘, ‘男‘,‘21‘, ‘路飞‘, ‘广东省‘);

set i=i+1;

end while;

End

$$;

delimiter;

Call myproc();

createprocedurescorepc()

begin

declarei int;

seti = 1;

repeat

insert into score(stuid,c_name,grade) values(i,‘计算机‘,‘98‘);

seti = i + 1;

until i >500

endrepeat;

end;

call scorepc()

3.写insert语句插入两个表的数据

(1)

insert into students(id,name,sex,age,class,address) values(801,‘张三‘, ‘男‘