DQL过滤排序:
SELECT * FROM student ORDER BY age DESC; --按年龄降序
SELECT * FROM student ORDER BY age DESC,math ASC;--按年龄降序同样年龄按math升序排
SELECT COUNT(id) AS 总人数 FROM student;
SELECT COUNT(*) AS 总人数 FROM student;
SELECT IFNULL(id,0) FROM student;--id如果为null就显示为0
SELECT COUNT(*) FROM student WHERE age>66;
SELECT SUM(math) 总分 FROM student;
SELECT AVG(math) 平均分 FROM student;
SELECT MAX(math) 最高分 FROM student;
SELECT MIN(math) 最低分 FROM student;
SELECT sex,AVG(math) FROM student GROUP BY sex;
SELECT sex,COUNT(*) FROM student GROUP BY sex;
SELECT sex,COUNT(*) FROM student WHERE age>44 GROUP BY sex;
--分组前的过滤用where,分组后的过滤用having:
SELECT sex,COUNT(*) FROM student WHERE age>44 GROUP BY sex HAVING COUNT(*)>2;
SELECT * FROM student LIMIT 2,6;--跳过前两条,显示6条
查询格式:
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT
约束:
--非空约束:
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL
);--创建时加约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;--创建后加约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);--删除约束
DROP TABLE stu;
--唯一约束:
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE
);
ALTER TABLE stu DROP INDEX phone_number;--删除唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
/*
主键约束:非空且唯一,一个表只有一个主键
*/
CREATE TABLE stu(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
ALTER TABLE stu DROP PRIMARY KEY;
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,/*设置主键并且自增*/
NAME VARCHAR(20)
);
ALTER TABLE stu MODIFY id INT;
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
SELECT * FROM stu;
/*外键:让表与表产生关系*/
CREATE TABLE dep(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);
SELECT * FROM dep;
INSERT INTO dep VALUE(1,"开发");
INSERT INTO dep VALUE(2,"运维");
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
dep_id INT,
/*添加外键*/
CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES dep(id)
);
INSERT INTO employee VALUE(NULL,"a",1);
INSERT INTO employee VALUE(NULL,"b",2);
INSERT INTO employee VALUE(NULL,"c",1);
INSERT INTO employee VALUE(NULL,"d",3);--添加不存在dep(id)中的dep_id就会报错
ALTER TABLE employee DROP FOREIGN KEY emp_depid_fk; --删除外键
/*添加外键*/
ALTER TABLE employee ADD CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES dep(id);
SELECT * FROM employee;
SELECT * FROM dep;
UPDATE dep SET id=3 WHERE id=1;--有外键连接的情况不能直接更新
DELETE FROM dep WHERE id=1;--有外键连接的情况不能直接删除
/*删除和更新部门表需要有级联更新和级联删除*/
ALTER TABLE employee ADD
CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES dep(id) ON UPDATE CASCADE ON DELETE CASCADE;
/*删除部门的同时,员工表的数据也被删除*/
DELETE FROM dep WHERE id=1;
/*更新部门的同时,员工表的数据也被更新*/
UPDATE dep SET id=3 WHERE id=1;
表的关系:
备份与还原数据库:
备份:DOS下输入:
mysqldump -uroot -proot 数据库名 >文件路径.sql
还原:
use 数据库;
source 文件路径.sql
数据库设计范式:
第一范式:每一行每一列不可再拆分,称为原子性。
第二范式:一张表只描述一件事,表中每一列完全依赖于主键。
第三范式:不产生传递依赖,不通过其他列间接依赖主键。