数据库练习题

Day01

/建库/
CREATE DATABASE test;
/进入库/
USE test;
乱码
ALTER DATABASE test CHARACTER SET =“utf8”
/建表/
CREATE TABLE student(
id INT(11),
sname VARCHAR(20),
score INT(11),
address VARCHAR(50),
usermail VARCHAR(20)
);
/插入数据/
INSERT INTO student(id,sname,score,address,usermail)
VALUES(1,‘张三’,98,‘北京’,‘111111111@qq.com’),
(2,‘李四’,88,‘上海’,‘111111112@qq.com’),
(3,‘王五’,78,‘广州’,‘111111113@qq.com’),
(4,‘赵六’,68,‘深圳’,‘111111114@qq.com’),
(5,‘孙七’,58,‘杭州’,‘111111115@qq.com’),
(6,‘小红’,48,‘北京’,‘111111116@qq.com’),
(7,‘小黑’,99,‘上海’,‘111111117@qq.com’),
(8,‘小绿’,100,‘杭州’,‘111111118@qq.com’),
(9,‘小粉’,60,‘杭州’,‘111111119@qq.com’),
(10,‘小紫’,70,‘黑龙江’,‘111111110@qq.com’);
/查看表中所有内容/
SELECT * FROM student
/查看所有同学编号-姓名-成绩/
SELECT id,sname,score FROM student
/修改类型/
ALTER TABLE student MODIFY usermail VARCHAR(50)
/增加字段/
ALTER TABLE student ADD pingjia VARCHAR(20)
/更改分数/
UPDATE student SET score=‘88’ WHERE sname=‘张三’
/查询及格人数所有信息/
SELECT * FROM student WHERE score>=80
/给指定人物加分/
UPDATE student SET score=score+40 WHERE sname=‘小红’
/使用关键字in查询1或5或7的同学的基本信息/
SELECT * FROM student WHERE id IN(1,5,7);
/查询ID值在5和8之间/
SELECT * FROM student id WHERE id>=5 AND id<=8
/查询姓名是小红并且分数大于60的同学的基本信息/
SELECT * FROM student WHERE sname=‘小红’ AND score>60
/查询姓名是小红或者分数大于90的同学的基本信息/
SELECT * FROM student WHERE sname=‘小红’ OR score>90
/查询score字段值是NULL的同学的基本信息/
SELECT * FROM student WHERE score=‘NULL’
/查询score字段值不是NULL的同学的id和sname/
SELECT id,sname FROM student WHERE score IS NOT NULL
Day02
– 1.建库
CREATE DATABASE test1;
– 进库
USE test1;
– 乱码
ALTER DATABASE test1 CHARACTER SET =“utf8”

– 2.建交
CREATE TABLE if not exists student(
id INT(11),
sname VARCHAR(20),
score INT(11),
address VARCHAR(50),
useremail VARCHAR(20)
);
– 3.插入
/插入数据/
INSERT INTO student(id,sname,score,address,useremail)
VALUES(1,‘张三’,98,‘北京’,‘111111111@qq.com’),
(2,‘李四’,88,‘上海’,‘111111112@qq.com’),
(3,‘王五’,78,‘广州’,‘111111113@qq.com’),
(4,‘赵六’,68,‘深圳’,‘111111114@qq.com’),
(5,‘孙七’,58,‘杭州’,‘111111115@qq.com’),
(6,‘小红’,48,‘北京’,‘111111116@qq.com’),
(7,‘小黑’,99,‘上海’,‘111111117@qq.com’),
(8,‘小绿’,100,‘杭州’,‘111111118@qq.com’),
(9,‘小粉’,60,‘杭州’,‘111111119@qq.com’),

(10,‘小紫’,70,‘黑龙江’,‘111111110@qq.com’);
/4.查询表中id sname 和 address 字段的所有内容/
SELECT id,sname,score FROM student
/5.查询表中所有同学的id sname score所有内容/
SELECT id,sname,score FROM student
/7.向表中添加字段，字段名称为 pingjia 字段类型为 varchar(20)/
INSERT INTO student VALUES(11,‘小明’,98,‘北京’,‘111111112@qq.com’);
/8.更改姓名是张三的同学分数为92/
UPDATE student SET score=92 WHERE sname=‘张三’
/9.80分为及格线，查询出所有不及格人数/
SELECT * FROM student WHERE score<80
– 10. 把姓名是“小红”的同学的分数在原来的基础上+20
UPDATE student SET score=score+20 WHERE sname=‘小红’
/11.使用关键字in查询id值是1或5或7的同学基本信息/
SELECT * FROM student WHERE id IN(1,5,7)
/12.查询id值在4至9的所有同学的基本信息/
SELECT * FROM student WHERE id>=4 AND id<=9
/13.查询姓名是小红并且分数大于60的同学的基本信息/
SELECT * FROM student WHERE sname=‘小红’ AND score>60
/14.查询姓名是小红或者分数大于90的同学的基本信息/
SELECT * FROM student WHERE sname=‘小红’ OR score>90
/15.查询score字段值是NULL的同学的基本信息/
SELECT * FROM student WHERE score=‘NULL’
/16.查询sname不是张三的同学的id sname 和score/
SELECT id,sname,score FROM student WHERE sname!=‘张三’
Day03
/建库/
CREATE DATABASE test2;
/进入库/
USE test2;
/建表/
CREATE TABLE student(
id INT(11)PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
score INT(11),
address VARCHAR(50),
useremail VARCHAR(20)
);
/插入数据/
INSERT INTO student VALUES(1,‘张三’,98,‘北京’,‘111111111@qq.com’);
INSERT INTO student VALUES(2,‘李四’,88,‘上海’,‘111111112@qq.com’);
INSERT INTO student VALUES(3,‘王五’,78,‘广州’,‘111111113@qq.com’);