11111

INSERT into t_manager VALUES
(1,'艾编程','男',28,14788650866,'A-0068'),
(2,'陈续源','女',27,15877931019,'A-3079'),
(3,'张三','男',34,18877295583,'X-0179'),
(4,'李四','女',25,18577241846,'X-1001'),
(5,'艾编程','男',28,14788650866,'B-1234');

INSERT into t_project VALUES
('A-0068','电商商家管理平台','2023-05-01','2023-11-25','91%','xxx传媒有限公司'),
('A-3079','汽车站大数据平台','2020-03-03','2024-12-30','65%','喜来乐综合服务有限公司'),
('B-1234','权限管理系统','2023-02-10','2024-01-01','45%','喜来乐综合服务有限公司'),
('X-0179','物资管理系统','2021-01-01','2022-12-30','100%','xxx数字科技有限公司'),
('X-1001','物业管理系统','2022-03-01','2023-12-30','73%','xxx科技有限公司');

SELECT userName,gender,tel,proj_id FROM t_manager,t_project WHERE t_manager.proj_id=t_project.pro_id and t_manager.proj_id in(SELECT t_project.pro_id FROM t_project WHERE t_project.enterprise='喜来乐综合服务有限公司'and gender='女')

CREATE VIEW v_pro_infos as
SELECT userName,tel,pro_name,start_time,enterprise FROM t_manager,t_project WHERE  t_manager.proj_id=t_project.pro_id and start_time BETWEEN '2022-1-1'and '2023-1-1'

ALTER TABLE t_manager add INDEX index_tel(tel);

delimiter $$
CREATE PROCEDURE pro_manager_pro()
BEGIN
SELECT COUNT(*)FROM t_manager WHERE t_manager.userName='艾编程';
end $$
delimiter ;
call pro_manager_pro();

INSERT INTO t_student VALUES
(1,'李鑫',18,'男','22级计应1班',1),
(2,'李靖',19,'女','22级计应1班',9),
(3,'胡龙',18,'女','22级计应1班',2),
(4,'李泽霖',18,'男','22级计应1班',1);

INSERT INTO t_score VALUES
(1,'HTML',90),
(2,'mysql',95),
(3,'java面向对现象',96),
(4,'mysql高级查询与编程',90),
(5,'javascrip交互式网页设计',92),
(6,'java高级程序设计',88),
(7,'javaweb程序设计',97),
(8,'mbatis数据持久化框架',94);

SELECT name,sex,className FROM t_student,t_score WHERE t_student.id=t_score.id in(SELECT t_score.id FROM t_score WHERE course='javaweb程序设计')

CREATE VIEW a as
SELECT name,sex,className,course,score FROM t_student,t_score WHERE  t_student.score_id=t_score.id

CREATE VIEW b as
SELECT name,sex,className,course,score FROM t_student,t_score WHERE  t_student.score_id=t_score.id and age BETWEEN 18 and 21

ALTER TABLE t_student add index index_stu_name(name);

delimiter $$
CREATE PROCEDURE proc_stu_sc()
BEGIN
SELECT COUNT(*)FROM t_student,t_score WHERE  t_student.score_id=t_score.id and course='HTML';
end $$
delimiter ;
call proc_stu_sc()

delimiter $$
CREATE TRIGGER v_insert_info AFTER INSERT on t_student for each row
BEGIN
    INSERT into t_info VALUES ('添加了一条学生信息');
end $$
delimiter ;