mysql添加学生网页页面代码

#1.创建数据库

CREATE DATABASE exmaple；

 

#2.删除数据库

DROP DATABASE exmaple;

#3.创建表

CREATE TABLE stu(
sname VARCHAR (4),
sscore DOUBLE ,
snumber DOUBLE
);

CREATE TABLE tea(
sname VARCHAR (4),
ssalary DOUBLE ,
snumber DOUBLE
);

#4.展示所有表

SHOW  TABLES;

 

#5.展示当前表

DESC stu;

 

#6.删除stu表

DROP TABLE stu;

 

#7.在stu中添加一列

ALTER TABLE stu ADD sex VARCHAR(1);

 

#8.把sex属性varchar修改为double

ALTER TABLE stu MODIFY sex DOUBLE ;

#9.修改sex为Resex,修改列名

ALTER TABLE stu CHANGE sex Resex VARCHAR (1);

 

#10.删除stu中的Resex,删除表中的列

ALTER TABLE stu DROP Resex;

#11.修改表的名称 stu改为student

RENAME TABLE stu TO student;

DESC student;

 

#000插入一组数据

INSERT INTO student (sname, sscore,snumber,sex) VALUES('李四',77.8,2,'男');

 

#12.一次加入多组数据

INSERT INTO student VALUES (
'张三',55.6,3,'男'),
('李慧',90.5,1,'女');

#13.不带条件的修改 所有记录都修改

UPDATE student SET sname='dddd' ;

 

#14.带条件的修改

UPDATE student SET sname='王五dd' WHERE sname="王五";

 

#15.带条件的删除 删除王五dd

DELETE FROM student WHERE sname='王五dd';

 

#16.不带条件的删除 删除整个表

DELETE FROM tea;

 

#17查询所有信息

SELECT * FROM student;

#18查询姓名和成绩

SELECT sname,sscore FROM student;

#19查询结果用别名展示

SELECT sname AS '姓名',sscore AS '成绩' FROM student;

#20去掉成绩中的重复值

SELECT DISTINCT(sscore) FROM student;

#21.将所有的成绩加20

SELECT sscore+20 FROM student;

 

#22查询王五同学

SELECT * FROM student  WHERE sname="王五"; 

 

#23查询成家大于等于90的同学

SELECT * FROM student WHERE sscore>=90;

 

#24查询名字中含有李字的同学

SELECT * FROM student WHERE sname LIKE '%李%';

#25查询编号在1，3，6中的同学信息

SELECT * FROM student WHERE snumber IN (1,3,6);

 

#26查询含有李字并且编号为3的学生

SELECT * FROM student WHERE sname LIKE "%李%" AND snumber=3;

#27查询编号为1或6的同学信息

SELECT * FROM student WHERE snumber =1 OR snumber=6;

#28将所有同学的信息按照编号升序排序

SELECT * FROM student ORDER BY snumber ASC;

#29将所有的同学的信息按照编号降序排序

SELECT *  FROM student ORDER BY snumber DESC;

 

#30获取表中学生人数

SELECT COUNT(sname) FROM student;

 

#31获取表中成绩总和

SELECT SUM(sscore) FROM student;

 

#32获取成绩的平均值

SELECT AVG(sscore) FROM student;

#33通过成绩进行分组，并统计每个成绩的学生个数

SELECT sscore ,COUNT(*) FROM student GROUP BY sscore;

#34查询通过编号分组，并且统计分组后平均成绩大于等于60的结果

SELECT snumber,AVG(sscore) FROM student GROUP BY snumber HAVING AVG(sscore)>=60;

INSERT INTO student VALUE('zha',70,1,'女');