mysql数据库基础知识题简单整理02

一.创建表(没有主外键关联的)，简单查插删改语句
(一).商品基本信息表(shop_jb)
商品编号（jb_id）
商品名称（jb_name）
规格（jb_spec）
库存量（jb_stock）
进货价（jb_price）
进货日期（jb_date）；
create table shop_jb(
jb_id int,
jb_name varchar(20),
jb_spec varchar(20),
jb_stock int,
jb_price float(4,2),
jb_date date
);
insert into shop_jb values
(1,“五粮液”,“500ml”,0,0,“2018-10-01”),
(2,“可口可乐”,“500ml”,1,2.22,“2017-10-02”),
(3,“雪碧”,“500ml”,2,2.33,“2015-10-03”),
(4,“大红袍”,“500ml”,3,33.33,“1997-10-04”),
(5,“女儿红”,“500ml”,5,55.55,“1997-10-05”);

(二).商品销售表(shop_xs)
商品销售表id（xs_id）
商品编号(xs_jb_id)
销售单价（xs_sale）
销售量（xs_quantity）
销售日期（xs_date）、
业务员编号（xs_ywid）
create table shop_xs(
xs_id int,
xs_jb_id int,
xs_sale int,
xs_quantity int,
xs_date date,
xs_ywid int
);
insert into shop_xs values
(1,1,1,1,“2018-10-01”,1),
(2,2,2,2,“2017-10-02”,2),
(3,3,3,3,“2015-10-03”,3),
(4,4,4,4,“1997-10-04”,4),
(5,5,5,5,“1997-10-05”,5);

(三).业务员表(shop_yw)
业务员编号（yw_id）
姓名（yw_name）
性别（yw_sex）
年龄（yw_age）
电话（yw_tel）
住址（yw_address）
薪水（yw_salary）
create table shop_yw(
yw_id int,
yw_name varchar(20),
yw_sex char(1),
yw_age int,
yw_tel char(11),
yw_address varchar(20),
yw_salary int
);
insert into shop_yw values
(1,“张三”,“男”,23,“15883376931”,“四川成都”,1500),
(2,“李四”,“女”,19,“15883376932”,“四川成都”,6700),
(3,“王五”,“男”,17,“15883376936”,“四川乐山”,2500),
(4,“叶七”,“男”,23,“15883376937”,“四川成都”,7700),
(5,“赵六”,“男”,23,“13999090980”,“四川成都”,1500);

(四).简单操作
1.删除业务员表中姓名为‘张三’的数据
delete from shop_yw where yw_name=‘张三’;
2.将业务员表中年龄18-20的员工薪水更改为1500
update shop_yw set yw_salary=1500 where yw_age>18 and yw_age<20;
3.删除商品销售表中销售日期大于2014-1-9的记录
delete from shop_xs where xs_date>‘2014-1-9’;
4.将业务员表中姓名为‘赵六’开头并且电话为13999090980 的薪水改为5000，性别改为女
update shop_yw set yw_salary=5000 , yw_sex=‘女’ where yw_name=‘赵六’ and yw_tel=‘13999090980’;
5.修改业务员表中小于18岁的数据，年龄都增加5岁
update shop_yw set yw_age=yw_age+5 where yw_age<18;
6.修改商品信息表中库存量为0并且进货价为0的数据 分别改为100 和15.22
update shop_jb set jb_stock=100,jb_price=15.22 where jb_stock=0 and jb_price=0;
7.修改商品信息表中 商品名称为‘五粮液’开头 的进货日期改2014-1-1
update shop_jb set jb_date=‘2014-1-1’ where jb_name like ‘五粮液%’;
8.删除商品销售表中商品编号为3，并且业务员编号为2的所有数据
delete from shop_xs where xs_id=3 and xs_ywid=2;
9.用delete删除商品销售表中所有信息
delete from shop_xs;

二.创建表(有主外键关联的)，简单查插删改语句
(一).创建3张表
1.员工表：emp
属性如下：
员工id 主键 自增长
员工姓名 不能为空
员工性别 默认‘男’
员工地址
员工电话 不能为空，并且唯一
员工入职时间 不能为空
员工所属部门id 不能为空 外键约束
员工职位id 不能为空 外键约束

2.部门表：dep
部门id 主键 自增长
部门名称 不能为空
部门成立时间 不能为空

3.职位表：pos
职位id 主键 自增长
职位名称 不能为空

方法一：直接在创建表的时候，就建立了约束
create table dep(
did int primary key auto_increment,
dname varchar(10) not null,
ddate date not null
);
create table pos(
pid int primary key auto_increment,
pname varchar(10) not null
);
create table emp(
eid int primary key auto_increment,
ename varchar(10) not null,
esex char(1) default ‘男’,
eaddress varchar(50),
etel varchar(11) unique not null,
edate date not null,
e_d_id int not null,
fk_e_d foreign key(e_d_id) references dep(did) ,
e_p_id int not null,
fk_e_p foreign key(e_p_id) references pos(pid)
);

方法二：在创建表之后，建立主外键约束
create table dep(
did int primary key auto_increment,
dname varchar(10) not null,
ddate date not null
);
create table pos(
pid int primary key auto_increment,
pname varchar(10) not null
);
create table emp(
eid int primary key auto_increment,
ename varchar(10) not null,
esex char(1) default ‘男’,
eaddress varchar(50),
etel varchar(11) unique not null,
edate date not null,
e_d_id int not null,
e_p_id int not null
);
alter table emp add constraint fk_e_d_id foreign key(e_d_id) references dep(did);
alter table emp add constraint fk_e_p_id foreign key(e_p_id) references pos(pid);

三.查询(条件查询，逻辑复合查询)
使用前面创建的学生表student:stuId,stuName,age,address,email,birthday,sex,phone

–查询是四川人的所有学生信息
select * from student where address like ‘%四川%’;

–查询不是四川人的所有学生的名字和地址
select stuName,address from student where address not like ‘%四川%’;

–查询只要地址中有北京的学生信息
select * from student where address like ‘%北京%’；;

–将结果显示的列名变为 学生姓名 和 地址
select stuName as ‘学生姓名’,address as ‘地址’ from student;

–将男生的学号按降序排列
select * from student order by stuId desc;

–查询来自北京的男同学
select * from student where address=‘北京’ and sex=‘男’；

–查询电话号码是“139”开头的学号前6名的学生信息
select * from student where phone like ‘139%’ limit 0,6;

–查询来自四川或者北京的学生
select * from student where address=‘四川’ or address=‘北京’;
select * from student where address in(‘四川’,‘北京’);

–查询学号是001–010之间的学生信息
select * from student where stuId between 001 and 010;

–查询学号是001,002,003的学生信息（两种方式）
select * from student where stuId=001 or stuId=002 or stuId=003;
select * from student where stuId in(001,002,003);

–查询学生姓名含有李的信息
select * from student where stuName like ‘%李%’;

–查询学生姓名是两个字的学生信息
select * from student where stuName like ‘____’;
select * from student where char_length(stuName)=2;

–查询学生的年龄只由0-2组成的学生信息
select * from student where age like ‘%0%’ or age like ‘%2%’ or age like ‘%1%’;
select * from student where age in(1,2,10,11,12,20,21,22,100);

–查询学生的年龄为10几岁和25岁以下的学生信息
select * from student where age between 10 and 25;

–请查询姓名是三字的学生
select * from student where stuName like ‘___’;

–请查询姓“李”，姓“张”，姓“黎”的学生信息
select * from student where stuName like ‘李%’ or stuName like ‘张%’ or stuName like ‘黎%’;

–请为学生表插入一条数据，‘s011’,‘刘晓’,‘20’,‘女’
insert into student(stuId,stuName,age,sex) values(‘s011’,‘刘晓’,‘20’,‘女’);

–修改姓’刘’的学生的年龄为23岁
update student set age=23 where stuName like ‘刘%’;

–修改学号为’s003’,‘s005’,'s008’的学生年龄为25岁
update student set age=25 where stuId in(‘s003’,‘s005’,‘s008’);

–查询年龄在21~23之间的学生
select * from student where age in(21,22,23);

–删除学生姓名为’刘晓’的信息
delete from student where stuName=‘刘晓’;

–查询学号为’s003’,‘s005’,'s008’的学生，且按照姓名升序排序、年龄降序排序
select * from student where stuId in(‘s003’,‘s005’,‘s008’) order by stuName asc,age desc;

–查询前3条记录信息，且学生姓名中包含’李’
select * from student where stuName like ‘%李%’ limit 0,3;

–查询所有成都的用户信息，并且用中文的列名称
select stuId 学号,age 年龄,address 地址,email 邮箱,stuName 姓名,birthday 生日,sex 性别,phoneNum 电话号码 from student where address like ‘%成都%’;

–查询所有成都的用户信息，并按照年龄从小到大排序，
select * from student where address like ‘%成都%’ order by age asc;

–结果显示“***是***地方的人”
select concat(stuName,‘是’,address,‘地方的人’) info from student;

–查询出用户的名字和地址，同时应该在一个列来显示，
select concat(stuName,address) from student;

四.查询(函数------字符串函数，数字函数，组函数)
1.查询所有学生的信息,所有年龄后面加上岁
select stuId 学生学号,stuName 姓名, concat(age,‘岁’) 年龄,email 邮箱,birthday 出生日期,sex 性别,phone 电话号码 from student;

2.查询所有学生的信息,姓名都显示为 李XX 形式
select stuId 学生学号,concat(‘李’,stuName) 姓名,age 年龄,email 邮箱,birthday 出生日期,sex 性别,phone 电话号码 from student;

3.查询所有学生的信息,将名字只有两个字的学生,名字后加上一个X
select stuId 学生学号,concat(stuName,‘X’) 姓名,age 年龄,email 邮箱,birthday 出生日期,sex 性别,phone 电话号码 from student
where char_length(stuName)=2;

4.查询所有学生的信息,查询邮箱的账号 45646@qq.com 查询 45646
select stuId 学生学号,stuName 姓名,age 年龄,substr(email,1,5) 邮箱,birthday 出生日期,sex 性别,phone 电话号码 from student；

5.查询所有学生信息,将电话号码中间4位替换为****
select stuId 学生学号,stuName 姓名,age 年龄,email 邮箱,birthday 出生日期,sex 性别,insert(phone,4,4,’****’) 电话号码 from student;

6.返回从学生学号第二个字符到最后的字符的信息
select substr(stuId,2) 学生学号 from student;

7.给定一个电话号码13880289977，要求查询显示如下的结果“138*****977
select insert(13880289977,4,5,’*****’) 电话号码 from student;

8.将学号的首字母变成大写，其余是小写
select concat(upper(substr(stuId,1,1)),lower(substr(stuId,2)))学生学号 from student;

9.计算当前系统时间是这一年的第几天
select dayofyear(sysdate());

10.计算当前系统时间是这一周的第几天
select dayofweek(sysdate());

11.查询本周以内的记录(select * from table where 本周以内)
#我的select * from table where date_add(sysdate(),interval 0 week);
#老师的
#错误的,没有从0点开始 select * from table where time > date_sub(sysdate(),interval dayofweek(sysdate())-2 day);
select * from table where time > date_sub(curdate(),interval dayofweek(curdate())-2 day)

12.查询24小时以内的记录(select * from table where 24小时以内)
#我的select * from table where date_add(sysdate(),interval 24 hour);
#老师的
select * from table where time > date_sub(sysdate(),interval 24 hours);

13.查询今天以内的记录(select * from table where 今天以内)
#我的select * from table where date_add(sysdate(),interval 0 day);
#老师的
select * from table where time > curdate();

五.查询（简单查询，约束，函数等等结合起来。没有连表查询哦）
(一).练习1
商品表
create table t_shop(
s_id int primary key,
s_shopcode varchar(30), – 商品编号
s_name varchar(40), – 商品名称
s_price int , – 商品价格
s_class varchar(50) – 商品类别
);
insert into t_shop(s_id,s_shopcode,s_name,s_price,s_class)
select 1,‘n11’,‘橙子’,9,‘水果’ from dual
union select 2,‘x330’,‘血橙’,11,‘水果’ from dual
union select 3,‘yx673’,‘柚子’,7,‘水果’ from dual
union select 4,‘n12’,‘白菜’,2,‘蔬菜’ from dual
union select 5,‘a13’,‘冬瓜’,3,‘蔬菜’ from dual
union select 6,‘n14’,‘西瓜’,4,‘水果’ from dual
union select 7,‘n15’,‘丝瓜’,5,‘蔬菜’ from dual
union select 8,‘c16’,‘苦瓜’,6,‘蔬菜’ from dual
union select 9,‘m17’,‘南瓜’,5,‘蔬菜’ from dual
union select 10,‘d18’,‘茄子’,6,‘蔬菜’ from dual

– 1 查询所有包含瓜的商品名称信息
select s_name from t_shop where s_name like’%瓜%’;
– 2 查询价格在1 到8 的所有商品信息
select * from t_shop where s_price between 1 and 8;
– 3 查询商品的最高价格的值是多少
select max(s_price) from t_shop;
– 4 查询商品价格最高的前三个商品的信息
select * from t_shop order by s_price desc limit 0,3;
– 5 查询所有商品的平均价格
select avg(s_price) 平均价格 from t_shop;
– 6 查询所有包含瓜的商品的平均价格
select avg(s_price) 平均价格 from t_shop where s_name like ‘%瓜%’;
– 7 查询最高商品的价格是最低商品的价格的倍数是多少
select max(s_price)/min(s_price) from t_shop;
– 8 查询商品名称中包含橙字的有多少个商品
select distinct count(*) from t_shop where s_name like ‘%橙%’;
– 9 修改 西瓜的价格为2块
update t_shop set s_price=2 where s_name=‘西瓜’;
– 10 删除id 为，4,9,1 的商品信息
delete from t_shop where s_id in (4,9,1);
– 11 查询蔬菜类别中最高的价格是多少？
select max(s_price) from t_shop where s_class=‘蔬菜’;

(二).练习2

姓名 年龄 性别 学号 笔试成绩 机试成绩
Jacky 20 男 xh1001 90 70
Simth 30 男 xh1002 75 80
Jay 18 男 xh1003 80 92
Helen 19 女 xh1004 75 85
lily 22 女 xh1005 90 95
Green 23 男 xh1006 85 85
RedChar 18 xh1007 60 55
Kevin 17 xh1008 45 50
create table student(
stu_name varchar(10),
stu_age smallint,
stu_sex enum(‘男’,‘女’),
stu_sno char(6) primary key,
stu_wgrade smallint,
stu_cgrade smallint
);
insert into student values
(‘Jacky’,20,‘男’,‘xh1001’,90,70),
(‘Simth’,30,‘男’,‘xh1002’,75,80),
(‘Jay’,18,‘男’,‘xh1003’,80,92),
(‘Helen’,19,‘女’,‘xh1004’,75,85),
(‘Lily’,22,‘女’,‘xh1005’,90,95),
(‘Green’,23,‘男’,‘xh1006’,85,85),
(‘RedChar’,18,null,‘xh1007’,60,55),
(‘Kevin’,17,null,‘xh1008’,45,50);

1.统计学员的数量
select count(姓名)
from 学生表
#select count(stu_sno) 学员数量 from student;

2.查询笔试最高分，机试最高分，笔试平均分，机试平均分，笔试总分，机试总分，分别用别名显示出来
select max(笔试成绩),max(机试成绩),avg(笔试成绩),avg(机试成绩),sum(笔试成绩),sum(机试成绩)
from 学生表
#select max(stu_wgrade) 笔试最高分,max(stu_cgrade) 机试最高分,avg(stu_wgrade) 笔试平均分, avg(stu_cgrade) 机试平均分, sum(stu_wgrade) 笔试总分, sum(stu_cgrade)机试总分
from student;

3.统计笔试和机试 都不及格的人数
select 人数
from 学生表
where 笔试不及格 机试不及格

select count(姓名) from 学生表 where 笔试成绩<60 and 机试成绩<60
#select count(stu_sno) from student
where stu_wgrade <60 and stu_cgrade<60;

4.统计笔试和机试都上80分的男生人数
#select count(stu_sno) from student where stu_wgrade > 80 and stu_cgrade > 80 and stu_sex=‘男’;
select count(姓名) from 学生表where 笔试成绩>80 and 机试成绩>80 and sex=‘男’;

5.按笔试成绩从高到低 排序 查出前5名的数据
#select * from student order by stu_wgrade desc limit 0,5;

6.按笔试成绩和机试成绩 从高到低，排序 查出所有数据
#select * from student order by stu_wgrade desc,stu_cgrade desc;

7.查询名字为“J”开头的学生信息
#select * from student where stu_name like ‘J%’;

8.查询性别为空的数据is null
#select * from student where stu_sex is null;

9.查询名字中有“e”字符的学生信息
#select * from student where stu_name like ‘%e%’;

10.查询出笔试成绩在80 85 90的学生信息
#select * from student where stu_wgrade in (80,85,90);

(三).练习3
– 商品类别表 –
CREATE TABLE category(
cat_id INT PRIMARY KEY,#类别编号
cat_name VARCHAR(30) NOT NULL#类别名称
);

– 商品表 –
CREATE TABLE goods(
goods_id INT PRIMARY KEY,#商品编号
goods_name VARCHAR(30) NOT NULL,#商品名称
goods_price DOUBLE,#商品进价
shop_price DOUBLE,#商品卖价
market_price DOUBLE,#市场价
cat_id INT,#商品类别
goods_number INT,#商品数量
FOREIGN KEY(cat_id) REFERENCES category(cat_id)
);

INSERT INTO category(cat_id,cat_name) VALUES(1,‘航模’);
INSERT INTO category(cat_id,cat_name) VALUES(2,‘车模’);
INSERT INTO category(cat_id,cat_name) VALUES(3,‘船模’);
INSERT INTO category(cat_id,cat_name) VALUES(4,‘动物模型’);

INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(1,‘F16战斗机’,300,1000,900,1,120);
INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(2,‘F35战斗机’,400,1200,1000,1,210);
INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(3,‘F117隐形轰炸机’,290,800,600,1,99);
INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(4,‘牧马人’,120,600,500,2,1200);
INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(5,‘宝马Z4’,130,560,510,2,231);
INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(6,‘地中海帆船’,90,300,180,3,68);
INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(7,‘密西西比号蒸汽明轮’,100,560,520,3,114);
INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(8,‘德鲁伊号16门炮护卫舰’,1322,2322,2600,3,100);
INSERT INTO goods
(goods_id,goods_name,goods_price,shop_price,market_price,cat_id,goods_number)
VALUES(9,‘皇家理查德号 74门炮战舰’,350,800,769,3,312);

#####################有"每个",就要分组###########
1求每个类别下商品种类数
select cat_id 商品类别,count(goods_id) 商品种类数 from goods group by cat_id;
2查询本店每个商品价格比市场价低多少；
select goods_id 商品,market_price-shop_price 差价 from goods;
3查询每个类别下面积压的货款
select cat_id商品类别,goods_pricegoods_number 积压货款 from goods group by cat_id;
4查询本店商品价格比市场价低多少钱，输出低200元以上的商品
select goods_name from goods where market_price-shop_price>200;
5查询积压货款超过2万元的栏目，以及该栏目积压的货款
#栏目是啥子鬼？
#错误的select cat_id 商品类别,goods_pricegoods_number 积压货款 from goods where goods_pricegoods_number>20000;
#错误的
#select cat_id 商品类别,goods_pricegoods_number 积压货款 from goods group by cat_id having (goods_pricegoods_number)>20000;
select cat_id 商品类别,goods_pricegoods_number money from goods group by cat_id having money>20000;
6按类别号升序排列，每个类别下的商品进价降序排列
select * from goods order by cat_id asc, goods_price desc;
7取价格第1-6高的商品
select * from goods order by goods_price desc limit 0,6;
8查询每个类别下进价最高的商品
select cat_id 商品类别,max(goods_price) 进价最高商品 from goods group by cat_id;
9取出每个类别下最新的产品(goods_id唯一)
#select max(goods_id) 最新产品id from goods group by cat_id;
10.查询没有商品的商品类别
#select cat_id 商品类别 from category where cat_id not in (select cat_id from goods);
#select c.cat_id,c.cat_name,count(g.good_id) number from category c left join goods g on c.cat_id=g.cat_id group by c.cat_id having number=0;
11.查询超过最高卖价航模的商品有哪些商品？
#select goods_name 商品名称 from goods where shop_price>
(select max(shop_price) from goods where cat_id=1);
12.查询每个商品类别的商品总数超过500个的商品类别有哪些？
#select cat_id 商品类别 from goods group by cat_id having sum(goods_number)>500;
13.查询商品进价低于100的商品类别有哪些？(显示出商品类别名)
#错误的，要显示类别名，不是id select cat_id 商品类别名 from goods where goods_price < 100;
select c.cat_name,g.goods_price from goods g,category c where g.cat_id=c.cat_id and g.goods_price<100;