Mysql建库建表基本操作

1.创建数据库

create DATABASE `db_test`;

2.选择数据库

USE `db_test`;

3.创建学生表，学号为主键，性别为男或女，默认为男，其他非空

CREATE TABLE `Student` (
    `Ssno` VARCHAR(20) PRIMARY KEY,
    `Sname` VARCHAR(10) NOT NULL,
    `Sage` INT NOT NULL,
    `Ssex` VARCHAR(2) NOT NULL DEFAULT '男' CHECK(`Ssex` IN('男','女'))
)

4.删除表

DROP TABLE `student`;

5.修改表中某一列以及该列的数据类型

ALTER TABLE `Student` CHANGE COLUMN `Ssno` `S#` INT;

6.创建其他表

创建课程表

CREATE TABLE course(
    `c#` INT PRIMARY KEY,
    `cname` VARCHAR(20),
    `t#` INT
);

创建成绩表，学号和课程号为联合主键

CREATE TABLE sc(
    `s#` INT,
    `c#` INT,
    `score` INT,
    PRIMARY KEY(`s#`,`c#`)
)

创建教师表

CREATE TABLE `teacher`(
    `t#` INT,
    `tname` VARCHAR(10)
)

7.插入数据
INSERT INTO student (`s#`,`sname`,`sage`,`ssex`) VALUES (1001,"钱二",23,"女");

8.删除一行数据

DELETE FROM `student` WHERE `s#` = 1005;

9.简单条件查询

SELECT `sname`,`sage`,`ssex` FROM `student` WHERE `ssex` = "女";

10.添加主键

ALTER TABLE `course` ADD PRIMARY KEY(`c#`);
11.添加外键

ALTER TABLE `course` ADD FOREIGN KEY (`t#`) REFERENCES `teacher`(`t#`);

问题训练

1.查询“1”课程比“2”课程成绩高的所有学生的学号；

SELECT a.`s#` FROM
(SELECT `s#`,`score` FROM `sc` WHERE `c#` = 1) AS a,
(SELECT `s#`,`score` FROM `sc` WHERE `c#` = 2) AS b
WHERE a.`score` > b.`score` AND a.`s#` = b.`s#`;
2.查询平均成绩大于60分的同学的学号和平均成绩；

SELECT `S#`,AVG(`score`)
FROM `sc`
GROUP BY `S#` HAVING AVG(`score`) >60;

3。查询所有同学的学号、姓名、选课数、总成绩；

SELECT student.`s#`,student.`Sname`,COUNT(sc.`c#`),SUM(sc.`score`)
FROM student LEFT JOIN sc ON student.`s#`=sc.`s#`
GROUP BY student.`s#`

4.查询姓“吴”的老师的个数；

SELECT COUNT(`tname`) FROM `teacher` WHERE `tname` LIKE "吴%";

5.查询没学过“吴东庆”老师课的同学的学号、姓名；

SELECT student.`s#`,student.`Sname`
FROM student WHERE student.`s#` NOT IN
(SELECT sc.`s#` FROM `sc`,`course`,`teacher`
WHERE sc.`c#`=course.`c#` AND teacher.`t#`=course.`t#` AND teacher.`tname`="吴东庆")

6.查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

使用连接操作

SELECT student.`s#`,student.`Sname` FROM student
WHERE student.`s#` IN
(SELECT sc_1.`s#` FROM sc AS sc_1 LEFT JOIN sc AS sc_2
ON sc_1.`s#` = sc_2.`s#` WHERE sc_1.`c#`=1 AND sc_2.`c#`=2)

使用exists

SELECT student.`s#`,student.`Sname` FROM student,sc AS sc_1 WHERE student.`s#` = sc_1.`s#` AND sc_1.`c#` = 1
AND EXISTS(SELECT * FROM sc AS `sc_2` WHERE `sc_2`.`s#` = student.`s#` AND `sc_2`.`c#` = 2)

7.查询学过“吴东庆”老师所教的所有课的同学的学号、姓名；

SELECT student.`s#`,student.`Sname` FROM student,course,sc
WHERE student.`s#` = sc.`s#` AND sc.`c#` = course.`c#` AND course.`c#` IN
(SELECT course.`c#` FROM course,teacher WHERE course.`t#` = teacher.`t#` AND teacher.`tname`= "吴东庆")

 

未完，参考资料

https://www.cnblogs.com/qixuejia/p/3637735.html