复习建库建表分组分页查询，索引的建立和删除

痛定思痛。 2022-06-08 10:23 79阅读 0赞

CREATE  DATABASE IF NOT EXISTS LDD DEFAULT CHARACTER SET UTF8;  
USE  LDD;  
  
  
CREATE TABLE LDD(  
DDid INT UNSIGNED NOT NULL  AUTO\_INCREMENT PRIMARY KEY,  
DDNAME  VARCHAR(20) NOT NULL,  
DDAGE TINYINT UNSIGNED NOT NULL,  
DDGENDER ENUM('男','女','保密'),  
DDHIGH decimal(5,2),  
DDWEIGHT decimal(4,2),  
DDBIR DATETIME,  
DDADDRESS VARCHAR(100),  
DDSCHOOL VARCHAR(50),  
DDPROFESSION VARCHAR(20)  
)  
DROP TABLE LDD;  
  
  
SELECT \* from LDD;  
  
  
INSERT INTO LDD VALUES(NULL,'刘丹丹',20,'女',160,65,'1996-12-21','铁西区','辽宁农业职业技术学院','会计')  
  
  
INSERT INTO LDD(DDNAME,DDAGE,DDGENDER,DDHIGH,DDWEIGHT,DDBIR,DDADDRESS,DDSCHOOL,DDPROFESSION)  
VALUES('张三',22,'男',180,67,'1994-02-11','大东区','辽宁农业职业技术学院','环艺'),  
('张三',22,'男',185,55,'1994-02-11','大东区','辽宁农业职业技术学院','环艺'),  
('李四',32,'男',170,80,'1944-08-22','铁西区','辽宁建筑','计算机'),  
('王五',23,'女',161,67,'1994-02-11','大东区','辽宁建筑','计算机'),  
('赵六',52,'男',178,67,'1994-02-11','沈河区','辽宁农业职业技术学院','计算机'),  
('张三',22,'男',180,67,'1994-02-11','和平区','辽宁农业职业技术学院','环艺');  
  
  
UPDATE LDD SET DDADDRESS='大东区' WHERE DDID = 2;  
  
  
UPDATE  LDD  SET DDHIGH = 177 WHERE  DDID=3;  
UPDATE  LDD SET  DDHIGH =160,DDNAME='张一百' WHERE DDID = 8;  
  
  
\-- 查询姓张的人数  
SELECT COUNT(\*) FROM LDD WHERE DDNAME LIKE '张\_';  
  
  
\-- 查询一共有多少人  
SELECT COUNT(\*) FROM LDD;  
  
  
\-- 统计大东区姓李的人数  效率应该比较低  
SELECT DDADDRESS,COUNT(\*) FROM LDD   
WHERE DDNAME LIKE '张\_'  
GROUP BY DDADDRESS  
HAVING DDADDRESS LIKE '大东区'  
ORDER BY DDNAME ASC;  
  
  
\-- 查询出身高高于170的男生  
SELECT DDID,DDNAME,DDGENDER,DDHIGH,MAX(DDHIGH) FROM LDD  
WHERE DDGENDER='男'  
GROUP BY DDHIGH  
HAVING  MAX(DDHIGH)>=170  
ORDER BY DDHIGH DESC LIMIT 0,2;  
  
  
\-- 查询出身高最低的人  
SELECT MIN(DDHIGH),DDNAME FROM LDD ORDER BY DDID DESC;  
  
  
SELECT  DDNAME,COUNT(\*)  FROM LDD WHERE DDHIGH=160 GROUP BY DDNAME;  
  
  
\-- 查询出身高在170-180之间的男的  
SELECT DDNAME,DDGENDER,DDHIGH FROM LDD   
WHERE DDGENDER='男'  
GROUP BY DDHIGH  
HAVING DDHIGH BETWEEN 175 AND 180   
ORDER BY DDHIGH DESC   
LIMIT 0,2;  
  
  
\-- 在LDD表DDSCHOOL上建立名字为 AA索引  
  
  
CREATE  INDEX  AA ON LDD(DDSCHOOL);  
  
  
ALTER TABLE LDD DROP INDEX AA;  
  
  
CREATE INDEX BB ON LDD(DDSCHOOL);   
  
  
CREATE INDEX CC ON LDD(DDNAME);