MySQL学习3

NAVICAT安装与使用

数据库完整性约束：

为了保证数据库中数据的正确性和相容性，对关系模型提出的某种约束或规则 

完整性约束关键字：

NOT NULL：非空约束，修饰字段的值不能为空值

DEFAULT：为字段设置一个默认值

UNIQUE KEY：UK约束，修饰字段的值是唯一的（可为空值）

PRIMARY KEY：PK约束，修饰字段为该表的主键，可以作为该表的唯一标识

多字段主键：CONSTRAINT 约束名 PRIMARY KEY (字段1,字段2,......)

AUTO_INCREMENT:

修饰字段的值自动增加

一张表中只能有一个字段受此约束，必须是整数类型

FOREIGN KEY：FK约束，修饰字段为该表的外键

保证多个表的参照完整性

REFERENCES：

示例：

学生表（学生id，学生姓名） - id 自增长 学生姓名不能为空CREATE TABLE student(sid INT PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(10) NOT NULL);

为学生表添加年龄字段sage 默认值设为20ALTER TABLE student ADD sage INT DEFAULT 20;

为学生表添加薪水字段ssalary 默认值设为5ALTER TABLE student ADD ssalary INT DEFAULT 5;

为学生表添加个性字段personality 设定UK约束ALTER TABLE student ADD personality VARCHAR(10 )UNIQUE KEY;

课程表（课程名，任课老师） - 课程名 主键 任课老师默认是陈老师CREATE TABLE lesson(lname VARCHAR(10) PRIMARY KEY,teacher VARCHAR(10) DEFAULT '陈老师');

学生成绩表（学生id，课程名，考试分数）CREATE TABLE score(sid INT,lname VARCHAR(10),score INT,

设置主键CONSTRAINT pk_sid_lname PRIMARY KEY (sid,lname),

设置外键CONSTRAINT fk_sid FOREIGN KEY (sid) REFERENCES student(sid),CONSTRAINT fk_lname FOREIGN KEY (lname) REFERENCES lesson(lname));

数据库操作语句：

一、单表查询：

1.插入记录(INSERTINTOVALUES)

INSERT INTO student (sname) VALUES ('王五'),('赵六');

INSERT INTO student VALUES (6,'学渣');

INSERT INTO lesson (lname) VALUES ('JAVA SE'),('数据库');

INSERT INTO score VALUES (1, 'JAVA SE', 80);

INSERT INTO score VALUES (1,'数据库',70),(2,'JAVA SE',60),(3,'JAVA SE', 100)

INSERT INTO score VALUES (1, 'JAVA EE', 80);

2.删除记录(DELETE　FROMTRUNCATE)

DELETE FROM score;TRUNCATE score;

DELETE删除记录是一条一条的删除，TRUNCATE是直接删除整个表的内容，所以TRUNCATE的删除速度较快，表中数据越多效果越明显

DELETE FROM score WHERE score < 70;

在成绩表中把张三的所有记录都删除DELETE FROM score WHERE sid = 1;

3.修改记录(UPDATESET)

UPDATE score SET score = 80;

把所有人分数减去20UPDATE score SET score = score - 20;

把不及格的人改成59分UPDATE score SET score = 59 WHERE score < 60;

4.查询记录(SELECTFROM)

SELECT sname FROM student;

SELECT sname 姓名,sage 年龄 FROM student;

SELECT * FROM student;

打印所有年龄大于18岁但小于30岁的学生信息SELECT * FROM student WHERE sage BETWEEN 18 AND 30;

5.去重查询(DISTINCT)

SELECT DISTINCT sage FROM student;

6.运算查询(含有算术表达式)

SELECT sname 姓名, 2016-sage 出生年份 FROM student;

学生表增加字段，期望薪资（K/月）默认5K。打印出所有学生的期望的年薪SELECT sname, ssalary * 12 FROM student;

7.条件查询(WHERE)

打印出姓名为张三的学生的姓名和年龄SELECT sname,sage FROM student WHERE sname = '张三';

8.空值查询(IS NULL)

查询出没有性格的人SELECT * FROM student WHERE personality IS NULL;

查询出有性格的人SELECT * FROM student WHERE personality IS NOT NULL;

9.排序查询(ORDER BY   ASC升序DESC降序

)

SELECT * FROM student ORDER BY sage DESC,ssalary DESC;

查询学生表的所有信息，以薪资的降序进行排列，当薪资相同时，以年龄的升序进行排列。年龄相同以学号的降序进行排列SELECT * FROM student ORDER BY ssalary DESC, sage ASC, sid DESC

10.限制数量查询(LIMIT)

显示查询记录中的前两条

SELECT * FROM student LIMIT 2;

限制范围查询<span style="white-space:pre"></span>可通过修改第一个参数实现翻页查询SELECT * FROM student LIMIT 0,3;

11.分组查询(GROUP　BY)

SELECT * FROM student GROUP BY ssalary;

二、多表查询

1.交叉连接(CROSS JOIN)

查询 student表(6行5列)和score表(4行3列)中所有的信息 24行8列SELECT * FROM student,score;SELECT * FROM student CROSS JOIN score;

2.内连接(INNER　JOIN ON)

SELECT * FROM score INNER JOIN student ON student.sid = score.sid;

打印分数在60分以上的学生的姓名 考试科目和分数SELECT sname, lname, score FROM student INNER JOIN score ON student.sid = score.sid && score > 60;

3.外左连接(LEFT JOIN)

左表全保留的情况下，将右表连接上去

SELECT * FROM student LEFT JOIN score ON student.sid = score.sid;

4.外右连接(RIGHTJOIN ON)

右表全保留的情况下，将左表连接上去

SELECT * FROM student RIGHT JOIN score ON student.sid = score.sid;

5.子查询

返回单行单列的子查询

输出年龄比张三大的学生的信息SELECT * FROM student WHERE sage > (SELECT sage FROM student WHERE sname = '张三');

输出分数比学号是2号的学生更高的学生姓名，考试科目以及分数SELECT sname, lname, scoreFROM student,scoreWHERE student.sid = score.sid &&score > (SELECT score from score WHERE sid = 2);

打印小于80分的学生姓名思路1SELECT sname FROM student,score WHERE student.sid = score.sid && score < 80;思路2(返回多行单列的子查询)SELECT snameFROM studentWHERE sid IN (SELECT sid from score WHERE score < 80)

练习：打印出考100分的学生的任课老师SELECT teacherFROM lessonWHERE lname IN (SELECT lname FROM score WHERE score = 100)

打印学号大于分数表中任意一个学号的学生姓名SELECT sname FROM student  WHERE sid < ANY(SELECT sid FROM score);

打印学号大于分数表中所有学号的学生姓名SELECT sname FROM student  WHERE sid > ALL(SELECT sid FROM score);

打印学生表中存在，但是分数表中不存在的学生信息SELECT * FROM student  WHERE NOT EXISTS (SELECT * FROM score WHERE student.sid = score.sid)

返回多行多列的子查询

打印学生的姓名，考试分数，任课老师（后两项如果不存在就是null）SELECT sname,score,teacherFROM student LEFT JOIN(SELECT sid,teacher,score FROM score,lesson WHERE score.lname = lesson.lname)sl ON student.sid = sl.sid;