0


软件工程20数据库期末复习简答题

1.启动,连接,停止,卸载mysql的语句:

net start mysql;

mysql-uroot;

net stop mysql;

mysql-remove;

2.请用create index语句在数据库db_test的表中创建一个升序索引

mysql>USER db_test;

Database changed mysql>CREATE INDEX index_subject->n content(subject(3)ASC);

3.请使用select into outfile 语句,备份数据到backupcontent.txt的文件中

SELECT * FROM db_test.content INTO OUTFILE 'C:/backup/backupcontent.txt';

4.郑州金水

(1)create database student;

use student;

create table student_web(s_id is not null,s_name varchar(12),s_fenshu int.s_hometown varchar(50),s_tuition int);

(2)insert into student_web(s_id,s_name,s_fenshu,s_hometown,s_tuition)values(1,'Jack Tomas',89,'郑州金水',2800)values(2,'Tom Joe',88,'洛阳涧西',3000)values(3,'Smiths',87,'郑州中原',2700)

(3)select*from student_web;

(4)select*from student where s_name like'%J%';

(5)select*from student_web where s_hometown=‘郑州%’;

(6)select*from student_web where s_tuition<(select avg(s_tuition) from student_web);

5.score表

select*from score where 姓名=‘张三’;

select*from score where 学科='英语'AND分数>90;

select 学号 FROM SCORE group by 学号HAVING SUM(分数)>180;

6.一张user表,有id,name,删除id为null的;讲null改为匿名

delete from user where id is null;

update user set name='匿名' where name is null;

7.查询表中class=3的所有信息

​select * from student where class=3;

8.现有一个student表,表结构如下所示:

根据表结构编写建表语句

Creat table student(

Id int(4) primary key.

Name varchar(20) not null,

Grade float);

Insert into student values(5,'lily',100);

9.有部门表dept和员工表employee 查询存在年龄大于21的员工所对应的部门信息,采用自连接查询方式查询与王红在同一部门的员工

select*from dept where exsits(select did from employee where age >21)

select p1.* from employee p1 join employee p2 on p1.did =p2.did where p2.name =‘王红’

10.在sales 表上创建一个视图

create view view_sales as select first _half+latter_half from sale

11.请查询send表中当天的所有记录的sql语句,其中字段sendtime为datetime型

select * from send where datediff(dd,endtime,getdate())=0;

12.请按照以下要求设计sal 语句。假定student 表中有:id,name, grade,gender 四个字

段 要求如下:查询name 字段值以字符 “h”开始 或者gender 字段值为 “女” 或者 grade

宇段值为 100 的记录。上面三个条件只要一个条件成立就符合要求,写出一个sa1 语句实

现上述需求。

SELECT id, name, grade, gender FROM student WHERE name LIKE 'h%' or gender ='*'

or grade=100:

13.请查询出send 表中日期 (sendtime 字段)为当天的所有记录的sa1 语句,其中字段

sendtime 为 datetime 型。

SELECT * FROM send WHERE DATEDIFF (DD, sendtime. GETDATE O) =0:

14.己知数据库中有一张会员表,表中有字段会员编号、姓名、性别、入会时间,请查询出表中除了会员“小七

SELECT * from 会员where姓名!=小七’;

  1. CREATE TABLE hpmcb(
  2. Hpid INT (11) NOT NULL COMMENT'货品id' AUTO_INCREMENT,
  3. hpmc VARCHAR(50) DEFAULT NULL COMMENT '货品名称',
  4. jldw VARCHAR(10) DEFAULT NULL COMMENT '计量单位',
  5. hptm VARCHAR(20) DEFAULT NULL COMMENT '货品条码',
  6. lsj VARCHAR(50) DEFAULT NULL COMMENT '零售价',
  7. hpzt TINYINT(4) DEFAULT 0 COMMENT '货品状态(0 正常,1 暂停进货,2 不再进货,)',
  8. PRIMARY KEY (Hpid)
  9. )
  10. CREATE TABLE hpkcb(
  11. kcid INT(11) NOT NULL AUTO_INCREMENT COMMENT '库存id',
  12. hpid INT(11) DEFAULT NULL COMMENT '货品id',
  13. kscl DECIMAL(18,3) DEFAULT '0.000' COMMENT '库存数量',
  14. PRIMARY KEY (kcid)
  15. )
  16. CREATE VIEW V_hpkcb AS SELECT hpmcb.Hpid,hpmc,jldw,kscl FROM hpmcb,hpkcb
  17. INSERT INTO hpmcb(hpmc,jldw,hptm,lsj) VALUE('可乐','元','123','3')
  18. DELIMITER $$
  19. USE `mypos`$$
  20. DROP TRIGGER /*!50032 IF EXISTS */ `after`$$
  21. CREATE
  22. TRIGGER `after` AFTER INSERT ON `hpmcb`
  23. FOR EACH ROW
  24. BEGIN
  25. INSERT INTO hpkcb(hpid) VALUE (new.hpid);
  26. END;
  27. $$
  1. DELIMITER
标签: 数据库开发

本文转载自: https://blog.csdn.net/weixin_51621288/article/details/124466374
版权归原作者 长歌当哭-vo 所有, 如有侵权,请联系我们删除。

“软件工程20数据库期末复习简答题”的评论:

还没有评论