sql

//创建一个库

CREATE DATABASE zy1

use zy1

// 创建一个班级表(classes)，包含字段id,name

CREATE TABLE classs(

id INT PRIMARY KEY auto_increment,

cname VARCHAR(20)

)

//创建一个学生表(students),包含字段id,name,birthday,gender,score,class_id

CREATE TABLE students(

id INT PRIMARY KEY auto_increment,

sname VARCHAR(10),

birthday date,

gender VARCHAR(20),

score INT,

class_id INT

)

//在班级表中添加三条记录 id:1 name:一年级，id:2 name:二年级，id:3 name:三年级

INSERT into classs(cname)

VALUES("一年级"),

("二年级"),

("三年级")

//4 在员工表里添加六条记录，

INSERT into students(sname,birthday,gender,score,class_id)

VALUES('张三','2010-11-10','男','100','1'),

('李四','2012-11-10','女','75','1'),

('王五','2014-11-10','男','80','2'),

('小明','2013-11-10','女','60','2'),

('小李','2015-11-10','女','30','3'),

('小刘','2008-11-10','男','90','3')

//5 使用pymysql模块来查询每个班级的平均分

select AVG(s.score),c.cname

FROM students s ,class c

WHERE s.class_id=c.id

GROUP BY s.class_id

//修改张三的名字为张五

UPDATE students SET sname="张五" WHERE sname="张三"

//删除班级名字为”三年级”的所有学生

delete FROM students WHERE class_id="3"

//查询一年级分数最高的人

SELECT s.sname,MAX(s.score) as "最高成绩"

FROM students s

WHERE s.class_id=1

//查询所有学生的年级信息（用子查询实现）

select c.cname,s.sname

from class c,students s

WHERE s.class_id=c.id

//查询以‘小’开头的名字为一个字学生

SELECT * FROM students where sname like '小_'

//查询出生日期在2008-11-10到2012-11-10的学生

SELECT s.sname,s.birthday

from students s

where s.birthday>'2008-11-10' and s.birthday<'2012-11-10'

//创建一个分类表(cate)，包含字段id,name

CREATE TABLE cate(

id INT PRIMARY KEY auto_increment,

cname VARCHAR(20)

)

//创建一个库

CREATE DATABASE zy2

use zy2

//创建一个商品表(goods),包含字段id,name,number,create_time,price,cate_id

CREATE TABLE goods(

id INT PRIMARY KEY auto_increment,

gname VARCHAR(10),

number INT,

create_time date,

price DOUBLE,

cate_id INT

)

//在分类表中添加三条记录 id:1 name:服装，id:2 name:玩具，id:3 name:家电

INSERT into cate(cname)

VALUES("服装"),

("玩具"),

("家电")

//在商品表里添加六条记录

INSERT into goods(gname,create_time,number,price,cate_id)

VALUES('羽绒服','2010-11-10','10','1.00','1'),

('轻便服','2012-11-10','30','2.00','1'),

('汽车','2014-11-10','40','2.00','2'),

('公主','2013-11-10','50','6.00','2'),

('橡皮泥','2015-11-10','23','3.50','2'),

('电饭煲','2008-11-10','3','3.00','3')

//使用pymysql模块来查询所有的分类及商品

select * from goods g LEFT JOIN cate c ON g.cate_id=c.id

//把名字为‘羽绒服’修改成‘棉衣’

UPDATE goods SET gname="棉衣" WHERE gname="羽绒服"

//删除名字为‘电饭煲’的记录

delete FROM goods WHERE gname="电饭煲"

//查询‘服装’分类下面库存最多的商品信息

SELECT g.gname,MAX(g.number) as "库存最多"

FROM goods g,cate c

WHERE c.cname="服装"

//查询分类下有商品分类信息

select c.cname,g.gname

from cate c,goods g

WHERE c.id=g.cate_id

//查询以‘服’结束的商品信息

SELECT * FROM goods where gname like '%服'

//查询创建日期在2008-11-10到2012-11-10的之间的商品

SELECT g.gname,g.create_time

from goods g

where g.create_time>'2008-11-10' and g.create_time<'2012-11-10'

//创建一个数据库 cart

CREATE DATABASE cart;

//使用新创建的数据库,在数据库下建立两张表

//用户表(users): id 姓名身高

//购物车表(user_cart):id 商品名称购买数量总价是否删除用户id

//创建一个分类表(cate)，包含字段id,name

CREATE TABLE users(

id INT PRIMARY KEY auto_increment,

uname VARCHAR(20),

height DOUBLE

)

CREATE TABLE user_cart(

id INT PRIMARY KEY auto_increment,

cname VARCHAR(20),

number INT,

price DOUBLE,

deleteu int,

uid INT

)

INSERT into users(uname,height)

VALUES("张三","1.80"),

("李四","1.75"),

("小明","1.34")

//在购物车表中加入以下记录（1代表未删除，0代表删除）

INSERT into user_cart(cname,number,price,deleteu,uid)

VALUES("商品一",10,100,1,1),

("商品二",3,89,0,1),

("商品三",2,30,1,1),

("商品四",10,50,1,2),

("商品五",3,10,1,2),

("商品六",1,76,1,2)

//7.更新李四的购物车中商品五的名字为商品5

UPDATE user_cart SET cname="商品5" WHERE cname="商品五"

//8.查询身高大于1米并且购物车没删除的用户和购物车信息

select * from users u right JOIN user_cart c ON c.uid=u.id WHERE u.height>1 AND c.deleteu=1

//9.查询购物车中删除和没删除的总价和为多少

SELECT sum(price) FROM user_cart

//10.查询每个用户没删除的购物车的数量大于1的记录，并按降序排列，取三条记录

SELECT * FROM user_cart c

WHERE c.deleteu=1 AND number>1

ORDER BY number DESC LIMIT 0,3

//11.按是否删除分组，查询平均总价格大于10的用户和购物车信息

SELECT * FROM user_cart c

GROUP BY deleteu

HAVING sum(c.price)/count(*)>10

//12.查询有购物车的用户和购物车信息

select * from users u right JOIN user_cart c ON c.uid=u.id WHERE c.deleteu=1

//13.删除张三购物车中已删除的购物车信息

delete FROM user_cart c, users u WHERE c.deleteu=0 AND u.uname="张三"

1.创建test数据库

create database test (A)

use test

2.在test数据库中创建student表id设置为主键自增长

create table student(

id int primary key auto_increment,

name varchar(20),

score int,

address varchar(50),

usermail varchar(20)

)

3.向student表中添加记录

insert into student(id,name,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')

4.使用sql语句查询出表中的所有内容

select * from student

5.使用sql语句查询出表中所有同学的id,name,score

select id,name,score from student

6.更改useremail字段的数据类型为varchar(50)

alter table student modify usermail varchar(50)

7.向表中添加一个字段，字段名称为“pingjia”,字段类型为varchar(20)

alter table student add pingjia varchar(20)

8.更改姓名是张三的同学的分数为88

update student set score='88' where name=‘张三’

9.如果80分为及格线，查询出所有及格的同学的详细信息

select * from student where score>=80

10.把姓名是“小红”的同学的分数在原来的基础上+40

update student set score=score+40 where name='小红'

11.使用关键字in,查询id值是1或5或7的同学的基本信息

select * from student where id in(1,5,7)

12.查询id值在5至8的所有同学的基本信息

select * from student id where id>=5 and id<=8

13.查询姓名是小红并且分数大于60的同学的基本信息

select * from student where name='小红' and score>60

14.查询姓名是小红或者分数大于90的同学的基本信息

select * from student where name='小红' or score>90

15.查询score字段值是NULL的同学的基本信息

select * from student where score='null'

16.查询score字段值不是NULL的同学的id和name

select id,name from student where score is not null

----------------------------------------------------------------------------------------------

1创建一个学校数据库（jiyun） (B)

create database jiyun

2创建课程（lesson）id（整形主键）name（课程名称）

create table lesson(

id int primary key auto_increment,

name varchar(50)

)

3创建一个班级(cls)，包含字段id（整形主键）name(班级名称)number(班级人数)lesson_id（课程名称id）

CREATE TABLE cls (

id int primary key auto_increment,

name varchar(50),

number varchar(50),

lesson_id varchar(50)

)

4在lesson表中插入两数据

insert into lesson(name)VALUES

('python高级'),

('数据库vue')

5在班级表中插入6条记录

insert into cls(name,number,lesson_id)values

('1905A','22','1'),

('1906A','44','1'),

('1904A','33','1'),

('1901A','18','2'),

('1902A','48','2'),

('1903A','56','2')

6.查询类型为 'python高级' 的所有班级、人员数量

select * from cls where lesson_id in(select id from lesson where name='python高级')

7.查询每个班级中人数最多、最少

select max(number) as '人数最多',min(number) as'人数最少' from cls

8.查询所有人员数量大于平均人数的班级，并且按班级人数降序排序

select * from cls where number < (select avg(number) from cls) order by number desc

9.修改cls表中'1902A'班级数量为44

update cls set number='44' where name='1902A'

----------------------------------------------------------------------------------------

1.创建数据库 (C)

create database db_test

use db_test

2.在test数据库中创建yuangong表

create table yuangong(

sid int primary key auto_increment,

sname varchar(20) not null,

sex varchar(4) deeault '男',

job varchar(20)not null,

birthday date,

salary decimal,

comm decimal,

widthhold int

)

4.表中添加如下记录

insert into yuangong (sid,sname,sex,job,birthday,salary,comm,widthhold)values

(1001,'张三','男','高级程师','1975-1-1',2200,1100,200),

(1002,'李四','女','助工','1985-1-1',1200,200,100),

(1003,'王五','男','工程师','1978-11-11',1900,700,200),

(1004,'赵六','男','工程师','1979-1-1',1960,700,150)

4.修改表名为emp

alter table yuangong RENAME emp

5.向表中添加字段Hobby,设置类型为varchar(50),设置唯一约束

alter table emp add hobby varchar(50) unique

6.使用desc语句查看表结构

show create table emp

7.向表中添加记录，字段对应值分别为（1005，林青霞，女，架构师，1969-12-12，8000，NULL，100，阅读）

insert into emp values(1005,'林青霞','女','架构师','1969-12-12',8000,null,100,'阅读')

8.修改sname字段的类型为varchar(20)

alter table emp modify sname VARCHAR(20)

9.查询表中sid字段的值从是1002或1003或1005员工的所有记录

select * from emp where sid = 1002 OR sid = 1003 OR sid = 1005

10.修改表中job值是高级工程师员工的job为“架构师”

update emp set job='架构师' where job='高级程师'

11.删除表中sid是1003并且job是王五的员工的记录

delete from emp where sid = 1003 and job = '王五'

12.修改表中姓名是1004员工的salary在原来的基础上-300

update emp set salary = salary - 300 where sid = 1004

----------------------------------------------------------------------------------------------

1.创建库 (D)

create database databook

2.自定义类Book，表名为book

use databook

create table book(

id int primary key auto_increment,

bookname varchar(10),

price int,

author varchar(20),

publish varchar(20)

)

2.向表里添加10条数据

insert into book(bookname,price,author,publish) values

('北平无故事',25,'刘和平','作家出版社'),

('人间失格',16,'太宰治著','作家出版社'),

('高兴',14,'贾平凹','人民出版社'),

('源氏物语',57,'刘和平','人民出版社'),

('卡夫卡文集',9,'卡夫卡','邮电出版社'),

('大家',12,'王蒙','邮电出版社'),

('拉片子',37,'杨健','清华出版社'),

('古代散文',5,'归有光','安徽出版社'),

('百花散文',6,'孙虹选','百花文艺出版社'),

('方令孺散文集',5,'方令孺','安徽文艺')

3.查询所有图书的信息，并按价格降序显示

select * from book where price order by price asc

4.查询所有作家出版社的图书信息，并按价格降序显示

select author from book where price order by price desc

5.查询出所有刘和平的图书信息，并输出

select * from book where author = '刘和平'

6.删除ID是2的记录，如果没有相关记录则提示

delete from book where id = 2 select * from book

7.将所有价格不足10元的图书调到10元，并查看信息

update book set price = 10 where price <10

8.查看所有图书的价格情况，并升序显示

select price from book where price order by price asc

9.查看所有价格低于20元的图收信息

select publish from book where price <=20

10.所有图书的价格上调20%，并查看信息

select * from book

update book set price = price*1.2

推荐阅读更多精彩内容