创建student表
CREATE TABLE student (
id INT PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
gender VARCHAR(10) DEFAULT ‘男’
);
创建score表
CREATE TABLE score(
id INT ,
score INT,
sid INT,
CONSTRAINT fk_score_student FOREIGN KEY (sid) REFERENCES student(id)
)
mysql语句
查询性别为男,并且年龄56的记录
查询学号为2,或者姓名为hualili的记录
查询学号为1,2,3的记录
查询学号不是1,2,3的记录
查询年龄为null的记录
SELECT * FROM stu1 WHERE age IS NULL;
查询年龄在20到40之间的学生记录
SELECT * FROM stu1 WHERE age>20 AND age<40;
查询性别非男的学生记录
SELECT * FROM stu1 WHERE gender!=“男”;
查询姓名不为null的学生记录
SELECT * FROM stu1 WHERE sname IS NOT NULL;
连接两个表,查询id=1的学生成绩
SELECT student.NAME,score.score
FROM student LEFT JOIN score
ON student.id =score.sid
WHERE sid=1
SELECT c.score, s.name
FROM student s ,score c WHERE
s.id=c.sid AND sid=1(第二种方法)
结果如下
#左连接,将student中id的值加入score表中
SELECT *
FROM student LEFT JOIN score
ON student.id=score.sid;
#右连接,将score中id的值加入student表中
SELECT *
FROM score RIGHT JOIN student
ON student.id=score.sid;
模糊查询
排序
聚合函数
分页