数据库开发技术A--代码题详解

1.
net start mysql
net stop mysql
mysql -uroot -h127.0.0.1 -p
mysql -remove
2.
select * from db_test.content into outfile 'C:/backup/backupcontent.txt'

create index index_subject on content(subject(3)ASC);

1)
CREATE DATABASE `student`;
 
CREATE TABLE `student`.`student_web` (
  `sid` INT NOT NULL COMMENT '学生id',
  `s_name` VARCHAR (12) COMMENT '学生姓名',
  `s_fenshu` INT COMMENT '考试成绩',
  `hometown` VARCHAR (50) COMMENT '学生籍贯',
  `s_tuition` INT COMMENT '学生学费'
) ;
2)
  INSERT INTO `student`.`student_web` (`sid`, `s_name`, `s_fenshu`, `hometown`, `s_tuition`) VALUES ('1', 'Jack Tomas', '89', '郑州金水', '2800'); 
  INSERT INTO `student`.`student_web` (`sid`, `s_name`, `s_fenshu`, `hometown`, `s_tuition`) VALUES ('2', 'Tom Joe', '88', '洛阳涧西', '3000'); 
  INSERT INTO `student`.`student_web` (`sid`, `s_name`, `s_fenshu`, `hometown`, `s_tuition`) VALUES ('3', 'Smiths', '87', '郑州中原', '2700'); 
3)
SELECT * FROM `student_web`;
4)
SELECT * FROM `student_web` WHERE s_name LIKE 'J%';
5)
SELECT * FROM `student_web` WHERE hometown LIKE '郑州%' ;
6)
select * from student_web where s_tuition<(select avg(s_tuition) from student_web );

1.
CREATE TABLE `hpmcb` (
	`Hpid` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '货品id',
	`hpmc` VARCHAR ( 50 ) DEFAULT NULL COMMENT '货品名称',
	`jldw` VARCHAR ( 10 ) DEFAULT NULL COMMENT '计量单位',
	`hptm` VARCHAR ( 20 ) DEFAULT NULL COMMENT '货品条码',
	`lsj` DECIMAL ( 9, 2 ) DEFAULT NULL COMMENT '零售价',
	`hpzt` TINYINT ( 4 ) DEFAULT 0 COMMENT '货品状态(0 正常，1 暂停进货，2 不再进货)',
PRIMARY KEY ( `Hpid` ) 
);
2.
CREATE TABLE `hpkc` (
	`kcid` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '库存id',
	`hpid` INT ( 11 ) DEFAULT NULL COMMENT '货品id',
	`kcsl` DECIMAL ( 18, 3 ) DEFAULT 0.000 COMMENT '库存数量',
PRIMARY KEY ( `kcid` ) 
);
3.
CREATE VIEW `V_hpkcb` AS SELECT
	hpmcb.Hpid, 
	hpmcb.hpmc, 
	hpmcb.jldw, 
	hpkc.kcsl
FROM
	hpkc,
	hpmcb
4.
DELIMITER $$
CREATE
    TRIGGER `after_hpmcb` AFTER INSERT
    ON hpmcb
    FOR EACH ROW BEGIN
    INSERT INTO hpkcb (hpid) VALUES (new.Hpid);
    END$$
DELIMITER ;

select * from 会员 where name!='小七';

1)
select * from score where 姓名='张三';
2)
select * from score where 学科='英语' and 分数>90;
3)
select 学号 from score group by 学号 having SUM(分数)>180

1.
1)
select * from dept where EXISTS(select did from employee where age>21);
2)
select p1.* from employee p1 JOIN employee p2 ON p1.did=p2.did where p2.name='王红';
2.
select * from send where DATEDIFF (DD,sendtime,GETDATE())=0;

 

create view view_sales as select first_haft+latter_half from sales;