数据库开发技术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;