当前位置: 首页 > news >正文

做网站绿色和什么颜色搭配/制作网站需要什么软件

做网站绿色和什么颜色搭配,制作网站需要什么软件,电子商务网站开发的流程,网站建设安全规范这些初级题应该算是入了sql查询的门了吧?但是很多性能问题还是不清楚,太多知识有欠缺,一点一点来吧。第一题create table student(Id int(10) not null auto_increment primary key,Name varchar(20) not null ,Sex varchar(4),Birth year,De…

这些初级题应该算是入了sql查询的门了吧?但是很多性能问题还是不清楚,太多知识有欠缺,一点一点来吧。

第一题

create table student(

Id int(10) not null auto_increment primary key,

Name varchar(20) not null ,

Sex varchar(4),

Birth year,

Department varchar(20) not null,

Address varchar(50)

)engine = InnoDB default charset = utf8

create table score(

Id int(10) not null auto_increment primary key,

Stu_id int(10) not null,

C_name varchar(20),

Grade int(10)

)engine = InnoDB default charset = utf8

INSERT INTO student VALUES( 901,’张老大’, ‘男’,1985,’计算机系’, ‘北京市海淀区’);

INSERT INTO student VALUES( 902,’张老二’, ‘男’,1986,’中文系’, ‘北京市昌平区’);

INSERT INTO student VALUES( 903,’张三’, ‘女’,1990,’中文系’, ‘湖南省永州市’);

INSERT INTO student VALUES( 904,’李四’, ‘男’,1990,’英语系’, ‘辽宁省阜新市’);

INSERT INTO student VALUES( 905,’王五’, ‘女’,1991,’英语系’, ‘福建省厦门市’);

INSERT INTO student VALUES( 906,’王六’, ‘男’,1988,’计算机系’, ‘湖南省衡阳市’);

INSERT INTO score VALUES(NULL,901, ‘计算机’,98);

INSERT INTO score VALUES(NULL,901, ‘英语’, 80);

INSERT INTO score VALUES(NULL,902, ‘计算机’,65);

INSERT INTO score VALUES(NULL,902, ‘中文’,88);

INSERT INTO score VALUES(NULL,903, ‘中文’,95);

INSERT INTO score VALUES(NULL,904, ‘计算机’,70);

INSERT INTO score VALUES(NULL,904, ‘英语’,92);

INSERT INTO score VALUES(NULL,905, ‘英语’,94);

INSERT INTO score VALUES(NULL,906, ‘计算机’,90);

INSERT INTO score VALUES(NULL,906, ‘英语’,85);

1) 查询student表的第2条到4条记录中计算机系和英语系的学生的信息

2) 从student表中查询每个院系年龄18~30岁的学生信息

3) 查询计算机成绩低于95的学生信息

4) 查询同时参加计算机和英语考试的学生的信息

#1) 查询student表的第2条到4条记录中计算机系和英语系的学生的信息

select * from (select * from student limit 1,3) as temTable where temTable.Department in (“计算机系”,”英语系”);

#2) 从student表中查询每个院系年龄18~30岁的学生信息

select * from student where (year(now()) – Birth) between 18 and 22;

#3) 查询计算机成绩低于95的学生信息

select * from student where Id in (select Stu_id from score where Grade < 95);

#4) 查询同时参加计算机和英语考试的学生的信息

SELECT * FROM student WHERE id in

(SELECT stu_id FROM score WHERE stu_id IN

(SELECT stu_id FROM score WHERE c_name= ‘计算机’)

AND c_name= ‘英语’ );#学着用any和把同一个表查两次!!!

第二题

create table customer(

c_id char(6) not null primary key,

name varchar(30) not null,

location varchar(30),

salary decimal(8,2)

)engine = InnoDB default charset = utf8;

create table bank(

b_id char(5) not null primary key,

bank_name char(30) not null

)engine = InnoDB default charset = utf8;

create table deposite(

d_id int not null auto_increment primary key,

c_id char(6),

b_id char(5),

dep_date date,

dep_type char(1),

amount decimal(8,2),

foreign key(b_id) references bank(b_id),

foreign key(c_id) references customer(c_id)

)engine = InnoDB default charset = utf8;

insert into customer values (101001,”孙杨”,”广州”,1234);

insert into customer values (101002,”郭海”,”南京”,3526);

insert into customer values (101003,”卢江”,”苏州”,6892);

insert into customer values (101004,”郭慧”,”济南”,3492);

insert into customer values (101005,”zain”,”北京”,6324);

insert into bank values (“B0001″,”工商银行”);

insert into bank values (“B0002″,”建设银行”);

insert into bank values (“B0003″,”中国银行”);

insert into bank values (“B0004″,”农业银行”);

insert into deposite values (1,”101001″,”B0001″,”2011-04-05″,”3″,42526);

insert into deposite values (2,”101002″,”B0002″,”2012-07-15″,”5″,66500);

insert into deposite values (3,”101003″,”B0003″,”2010-11-24″,”1″,42366);

insert into deposite values (4,”101004″,”B0004″,”2008-03-31″,”1″,62362);

insert into deposite values (5,”101001″,”B0003″,”2002-02-07″,”3″,56346);

insert into deposite values (6,”101002″,”B0001″,”2004-09-23″,”3″,353626);

insert into deposite values (7,”101003″,”B0004″,”2003-12-14″,”5″,36236);

insert into deposite values (8,”101004″,”B0002″,”2007-04-21″,”5″,26267);

insert into deposite values (9,”101001″,”B0002″,”2011-02-11″,”1″,435456);

insert into deposite values (10,”101002″,”B0004″,”2012-05-13″,”1″,234626);

insert into deposite values (11,”101003″,”B0003″,”2001-01-24″,”5″,26243);

insert into deposite values (12,”101004″,”B0001″,”2009-08-03″,”3″,45671);

1) 对 deposite、customer、bank 进行查询,查询条件为 location 在广州、苏州、济南的客户,存款在300000 至 500000 之间的存款记录,显示客户姓名 name、银行名称 bank_name、存款金额 amount.

2) 对 deposite 表进行统计,按银行统计存款总数,显示为 b_id,total.

#对 deposite、customer、bank 进行查询,查询条件为 location

#在广州、苏州、济南的客户,存款在300000 至 500000 之间的存款记录,

#显示客户姓名 name、银行名称 bank_name、存款金额 amount.

SELECT amount,name,bank_name

FROM customer INNER JOIN deposite ON deposite.c_id = customer.c_id

INNER JOIN bank ON deposite.b_id = bank.b_id

WHERE deposite.amount BETWEEN 300000 AND 500000 AND customer.location IN (“广州”,”苏州”,”济南”)

#2先子查询再连表

select amount,name,bank_name from(

select b_id,c_id,amount from deposite where c_id in

(

select c_id from customer where location in (“广州”,”苏州”,”济南”)

) and d_id in(

select d_id from deposite where amount between 300000 and 500000

)

)as temTable

inner join customer on temTable.c_id = customer.c_id

inner join bank on temTable.b_id = bank.b_id

#对 deposite 表进行统计,按银行统计存款总数,显示为 b_id,total.

select b_id, sum(amount) as total from deposite group by b_id

第三题

create table jifei(

phone char(8),

month char(8),

expenses decimal(8,2)

)engine = InnoDB default charset = utf8;

insert into jifei values (“11111111″,”20100606”,51.2);

insert into jifei values (“11111111″,”20100706”,51.2);

insert into jifei values (“11111111″,”20100806”,51.2);

insert into jifei values (“11111111″,”20100906”,0);

insert into jifei values (“11111111″,”20101006”,0);

insert into jifei values (“22222222″,”20100606”,70.2);

insert into jifei values (“22222222″,”20100706”,70.2);

insert into jifei values (“22222222″,”20100806”,50);

insert into jifei values (“22222222″,”20100906”,0);

insert into jifei values (“22222222″,”20101006”,0);

insert into jifei values (“33333333″,”20100606”,70);

insert into jifei values (“33333333″,”20100706”,70);

insert into jifei values (“33333333″,”20100806”,70);

insert into jifei values (“33333333″,”20100906”,0);

insert into jifei values (“33333333″,”20101006”,70);

insert into jifei values (“44444444″,”20100606”,0);

insert into jifei values (“44444444″,”20100706”,0);

insert into jifei values (“44444444″,”20100806”,0);

insert into jifei values (“44444444″,”20100906”,0);

insert into jifei values (“44444444″,”20101006”,0);

insert into jifei values (“44444444″,”20101006”,0);

1.查找6,7,8月有话费产生且话费为51到100;9,10月份没有话费产生的用户

2删除jifei表中10月份出现的相同记录的多余条数

3查询9,10月份,月均30元以上的用户(结果无重复项)

1.查找6,7,8月有话费产生且话费为51到100;9,10月份没有话费产生的用户

SELECT phone1 FROM(

SELECT phone AS phone1 FROM

(SELECT * FROM jifei

WHERE MONTH(month) IN (6,7,8) AND expenses BETWEEN 51 AND 100) AS a

GROUP BY a.phone

HAVING count(1) = 3

) AS temTableA

WHERE temTableA.phone1 IN(

SELECT phone AS phone2 FROM

(SELECT * FROM jifei

WHERE MONTH(month) IN (9,10) AND expenses = 0) AS b

GROUP BY b.phone

HAVING count(1) = 2

)

iiaUZn.jpg

587094

2删除jifei表中10月份出现的相同记录的多余条数

我觉得没有不同的id,所有内容都一样时,无法删除只保留一条,有写出答案的希望可以告诉我多谢。所以我只写了一个”查出10月份出现重复记录的用户“如下:

SELECT phone FROM

(SELECT * FROM jifei WHERE MONTH(month) = 10) AS a

GROUP BY a.phone

HAVING count(*) > 1

VfAr6b.jpg

587094

这个链接里面对处理重复数据写的还比较全面。

3查询9,10月份,月均30元以上的用户(结果无重复项)

SELECT DISTINCT phone FROM jifei

WHERE MONTH(month) IN (9,10)

GROUP BY phone HAVING AVG(expenses) > 30;

第四题

create database xuexiao;

use xuexiao;

create table sc (sno varchar(20), cno varchar(20), grade int)ENGINE = INNODB DEFAULT charset = utf8;

create table course (cno varchar(20), cname varchar(20), hours int)ENGINE = INNODB DEFAULT charset = utf8;

create table student (sno varchar(20), sname varchar(20), ssex char(10), sage int, sdept varchar(20))ENGINE = INNODB DEFAULT charset = utf8;

insert into student values

(“9512101″,”李勇”,”男”,19,”计算机系”),

(“9512102″,”刘晨”,”男”,20,”计算机系”),

(“9512103″,”王敏”,”女”,20,”计算机系”),

(“9512103″,”王敏”,”女”,20,”计算机系”),

(“9521101″,”张立”,”男”,22,”信息系”),

(“9521102″,”吴宾”,”女”,21,”信息系”),

(“9521103″,”张海”,”男”,20,”信息系”),

(“9531101″,”钱小力”,”女”,18,”数学系”),

(“9531102″,”王大力”,”男”,19,”数学系”);

insert into course values

(“c01″,”计算机文化学”,70),(“c02″,”VB”,90),

(“c03″,”计算机网络”,80),(“c04″,”数据库基础”,108),

(“c05″,”高等数学”,180),(“c06″,”数据结构”,72);

insert into sc values

(“9512101″,”c01”,90),

(“9512101″,”c02”,86),

(“9512101″,”c06”,null),

(“9512102″,”c02”,78),

(“9512102″,”c04”,66),

(“9521102″,”c01”,82),

(“9521102″,”c02”,75),

(“9521102″,”c04”,92),

(“9521102″,”c05”,50),

(“9521103″,”c02”,68),

(“9521103″,”c06”,null),

(“9531101″,”c01”,80),

(“9531101″,”c05”,95),

(“9531102″,”c05”,85);

1) 查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

2) 分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,

a)并要求将这两个查询结果合并成一个结果集,

b)并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

3) 将计算机系成绩高于 80 分的学生的修课情况插入到另一张表中,分两种情况实现:

a) 在插入数据过程中建表。

b)先建一个新表,然后再插入数据

#1) 查询哪些学生的年龄相同,要求列出年龄相同的学生的姓名和年龄。

SELECT * FROM student WHERE sno NOT IN(

SELECT sno FROM student GROUP BY sage HAVING COUNT(*) = 1)

ORDER BY sage;

#2) 分别查询信息系和计算机系的学生的姓名、性别、修课名称、修课成绩,

SELECT sname,sage,cname,grade FROM student LEFT JOIN sc ON sc.sno = student.sno

LEFT JOIN course ON course.cno = sc.cno WHERE sdept = “信息系”;

SELECT sname,sage,cname,grade FROM student LEFT JOIN sc ON sc.sno = student.sno

LEFT JOIN course ON course.cno = sc.cno WHERE sdept = “计算机系”;

#a)并要求将这两个查询结果合并成一个结果集,

SELECT sname,sage,cname,grade FROM student LEFT JOIN sc ON sc.sno = student.sno

LEFT JOIN course ON course.cno = sc.cno WHERE sdept IN (“信息系”,”计算机系”);

#b)并以系名、姓名、性别、修课名称、修课成绩的顺序显示各列。

SELECT sdept,sname,sage,cname,grade FROM student LEFT JOIN sc ON sc.sno = student.sno

LEFT JOIN course ON course.cno = sc.cno WHERE sdept IN (“信息系”,”计算机系”) ORDER BY sdept;

#3) 将计算机系成绩高于 80 分的学生的修课情况插入到另一张表中,分两种情况实现:

INSERT INTO newStudent SELECT * FROM student;

#a) 在插入数据过程中建表。

CREATE TABLE studentInfo1 AS SELECT sname,sage,cname,grade FROM student INNER JOIN sc ON student.sno = sc.sno

INNER JOIN course ON sc.cno = course.cno WHERE sdept = “计算机系” AND grade > 80;

#b) 先建一个新表,然后再插入数据

CREATE TABLE studentInfo (sname VARCHAR(20), sage INT, cname VARCHAR(20),grade INT)ENGINE = INNODB DEFAULT charset = utf8;

INSERT INTO studentInfo

SELECT sname,sage,cname,grade FROM student INNER JOIN sc ON student.sno = sc.sno

INNER JOIN course ON sc.cno = course.cno WHERE sdept = “计算机系” AND grade > 80;

能力有限,出现错误希望可以指出

完结

http://www.lbrq.cn/news/1623241.html

相关文章:

  • 武汉最好的网站建设前十/一键免费生成网页的网站
  • 网站开发技术构架/企业网站营销的优缺点
  • 公司网站维护一年多少钱/网站权重一般有几个等级
  • wordpress插件安装教程视频/惠州seo整站优化
  • 吉林省干部网络培训学院官网/新乡seo网络推广费用
  • 微信营销案例ppt/搜索引擎优化的含义
  • 国内域名服务商/北京优化推广
  • 优化网站的公司/深圳网站建设推广方案
  • 永久免费云服务器无需注册/seo推广公司教程
  • 永兴县人民政府门户网站/品牌推广策略分析
  • 沈阳网站制作公司和服务器/关键词优化骗局
  • 给客户做网站 客户不付尾款/软文广告300字范文
  • 南岗哈尔滨网站建设/合肥seo推广公司
  • 营销优化型网站怎么做/seo整站优化系统
  • wordpress大前端增强版/厦门百度推广排名优化
  • 网站建设整改实施方案/app营销
  • 湖北网络建设公司网站/推广产品的方法和步骤
  • 郑州网站开发公司/lol关键词查询
  • 今日头条入口/西安关键词seo
  • wordpress文章页面模板/宁波谷歌seo推广公司
  • 淘宝关键词搜索工具/张北网站seo
  • 可以做片头的网站/做网页用什么软件好
  • 贵阳网站建设蜜蜂/百度手机助手app下载官网
  • 哪些网站可以接点私活做的/爱站数据
  • 广州网站 服务器建设/百度seo排名优化是什么
  • 怎样做网站优化排名/网站推广的方式有哪些?
  • 曹县网站建设/电商平台有哪些
  • b2b网站建设公司/网站统计
  • 做调查问卷网挣钱的网站/seo编辑的工作内容
  • qq音乐怎么做mp3下载网站/四川游戏seo整站优化
  • CentOS安装ffmpeg并转码视频为mp4
  • easyexcel填充方式导出-合并单元格并设置边框
  • Android:Reverse 实战 part 2 番外 IDA python
  • AIC 2025 热点解读:如何构建 AI 时代的“视频神经中枢”?
  • React入门学习——指北指南(第三节)
  • 【科研绘图系列】R语言绘制误差连线散点图