mysql的巧用

创建一个表
CREATE TABLE teacher (
– Id int PRIMARY KEY auto_increment,
– Name Varchar(50) not NULL,
– Age Int not NULL,
– Gender Char(2) DEFAULT NULL,
– Height Decimal(6,2) DEFAULT NULL
courseId int DEFAULT NULL
);

– 6、对教师表的王三笠名字修改为欧阳修，年龄改为40；

UPDATE teacher SET Name=CONCAT(‘欧阳修’), Age=CONCAT(‘40’) WHERE Name=‘王三笠’ AND Age=‘28’

– 7、对课程表的“HTML网页设计”删除；

– INSERT INTO course VALUES(NULL,‘HTML网页设计’); 插入数据
DELETE FROM course WHERE courseName=‘HTML网页设计’;

– 8、对教师的身高从高到低排序。

SELECT * FROM teacher ORDER BY height desc;

– 9、从教师表中，找出三个年龄最小的教师出来；

SELECT Name 姓名, Age 年龄 FROM teacher WHERE Age<30;

– 10、按课程统计，统计每个课程人数；

– SELECT COUNT(*) AS 课程人数 FROM course WHERE courseId=‘1’;

– SELECT COUNT(*) AS 课程人数 FROM course WHERE courseId=‘2’;

– SELECT COUNT(*) AS 课程人数 FROM course WHERE courseId=‘3’;

SELECT courseName 课程名,COUNT(*) 课程人数 FROM course C,teacher T WHERE C.courseId=T.courseId GROUP BY courseName;

– 11、找出教师身高最高和最低的教师出来；

SELECT max(Height) 最高身高, min(Height) 最低身高 FROM teacher;

select Height, Name
from teacher
where Gender=‘男’ and Height>=188;

– 要不要性别(Gender) 都行

select Height, Name
from teacher
where Height<=150;

select Height, Name
from teacher
where Height>=188;

– SELECT CONCAT(max(Height),Name) FROM teacher;字符连接

– 12、找出姓蔡的全部教师

SELECT * FROM teacher WHERE Name LIKE’蔡%%';

– 列别名

SELECT Name AS 姓名, Age AS 年龄, Gender AS 性别, Height AS 身高 FROM teacher;