虽然身为一个前端工程师，数据库可能不是经常接触，但是做为息息相关的一门技术，有一个基本的理解和认识还是很有必要的，这次数据库学习，主要为了接下来egg.js做准备。
1.工具
下载地址：www.mysql.com/downloads/ ，建议安装5.5.6版本，比较稳定。
可视化工具：Navicat(推荐，可连接各类数据库)或者SQLyog
2.概念
SQL
- DDL（Data Define Language） 数据定义语言，定义数据库和表的数据结构的
- DML（Data Manipulate Language） 数据操作语言
- DCL 数据库控制语言
3.SQL_DDL语句
建表
-- 关键字大写
CREATE TABLE `student`
(
id INT(11) NOT NULL,
name VARCHAR(64) NOT NULL,
age INT(11) DEFAULT NULL,
city VARCHAR(64) DEFAULT '北京'
)
SELECT * FROM student;
DESC student;
修改，增加，删除字段
-- 如何修改和增加表里的字段
ALTER TABLE student ADD COLUMN idcard VARCHAR(64) NULL;
ALTER TABLE student MODIFY idcard VARCHAR(128) NOT NULL;
ALTER TABLE student DROP idcard;
增加约束
-- 为表增加主健
ALTER TABLE student ADD PRIMARY KEY(id)
-- 增加唯一约束
ALTER TABLE student ADD UNIQUE INDEX uq_student_idcard(idcard)
-- 增加默认约束
ALTER TABLE student MODIFY COLUMN city VARCHAR(64) DEFAULT '南京'
-- 主外键约束
ALTER TABLE score ADD CONSTRAINT fk_score_student_id FOREIGN KEY(student_id)
REFERENCES student(id)
4.SQL_DML语句
插入数据
INSERT INTO student(name,idcard,age,city)
VALUES('张三','123456',10,'北京');
INSERT INTO student(name,idcard,age,city)
VALUES('李四','111111',20,'广州');
-- insert 注意事项
更新数据
-- 更新
-- 1.可以一次更新多列，可以用逗号隔开 2.可以指定更新条件，如果有多个条件可以用 and or not
UPDATE student SET age=40,city='上海' WHERE id=12 OR name='李四';
UPDATE student SET idcard='123456' WHERE id=12;
删除数据
-- 删除
-- 删除是整行删除，所以不需要提供列表
-- 如果要删除的表是主表的，那需要先删除子表
DELETE FROM student WHERE id = 12;
清空表
-- TRUNCATE
-- TRUNCATE 重置标示种子 不写入日志，没有备份
TRUNCATE table student;
-- 会写入日志，可以恢复
DELETE FROM student;
DROP TABLE student;
查询数据
-- 查询语句
-- 查询北京 的同学信息，并按ID正序排列
SELECT id,name
FROM student
WHERE city = '北京'
ORDER BY id DESC
-- 别名
SELECT id,name,city home
FROM student
WHERE city = '北京'
ORDER BY id DESC
-- 如何查询空行
INSERT INTO student(name,idcard,age,city)
VALUES('陈七','7',7,'北京');
SELECT * FROM student WHERE level IS NULL;
-- 常量列
SELECT id,name,city,'中国' AS country
FROM student
WHERE city = '北京'
ORDER BY id DESC
-- 分页 limit第一个参数索引，第二个参数取几条
SELECT id,name,city
FROM student
LIMIT 2,2
-- 查询同学们都来自于哪些不同的城市
SELECT DISTINCT city
FROM student;
-- +SQL +只能用来加数字
SELECT 1+1;
SELECT 1+'1';
SELECT 1+'a';
SELECT 'a'+'b';
SELECT CONCAT('a','b','c');
5.常用操作
ALTER TABLE `student`
ADD COLUMN `province` varchar(50) NULL AFTER `city`,
ADD COLUMN `birthday` date NULL AFTER `province`,
ADD COLUMN `gender` int(11) NULL AFTER `birthday`,
ADD COLUMN `email` varchar(50) NULL AFTER `gender`;
INSERT INTO `student`(id,name,idcard,age,city,province,birthday,gender,email) VALUES ('1', '郭靖', '1', '1', '济南', '山东省', '1982-09-03', 1, '1@qq.com');
INSERT INTO `student`(id,name,idcard,age,city,province,birthday,gender,email) VALUES ('2', '黄蓉', '2', '2', '济南', '山东省', '1982-09-03', 0, '2@qq.com');
INSERT INTO `student`(id,name,idcard,age,city,province,birthday,gender,email) VALUES ('3', '杨过', '3', '3', '终南山', '陕西省', '1979-09-03', 1, '3@qq.com');
INSERT INTO `student`(id,name,idcard,age,city,province,birthday,gender,email) VALUES ('4', '小龙女', '4', '4', '终南山', '陕西省', '1970-09-03', 0, '4@qq.com');
INSERT INTO `student`(id,name,idcard,age,city,province,birthday,gender,email) VALUES ('5', '欧阳锋', '5', '5', '白驼山', '新疆', '1989-09-09', 1, '5@qq.com');
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');
INSERT INTO `score` VALUES ('1', '1', '100');
INSERT INTO `score` VALUES ('1', '2', '90');
INSERT INTO `score` VALUES ('1', '3', '70');
INSERT INTO `score` VALUES ('2', '1', '100');
INSERT INTO `score` VALUES ('2', '2', '90');
INSERT INTO `score` VALUES ('2', '3', '80');
INSERT INTO `score` VALUES ('3', '1', '100');
INSERT INTO `score` VALUES ('3', '2', '90');
INSERT INTO `score` VALUES ('3', '3', '80');
-- 查询山东省的全部同学
SELECT *
FROM student
WHERE province = '山东省' AND gender = 1;
-- 查询没有填写电子邮件的同学姓名和身份证号
SELECT name,idcard
FROM student
WHERE email IS NULL
-- 把学生的分数升序排列
SELECT *
FROM score
ORDER BY grade ASC
-- 课程编号升序，分数降序
SELECT *
FROM score
ORDER BY course_id ASC,grade DESC;
-- 模糊查询主要学习 like % _
SELECT * FROM student
WHERE NAME LIKE'郭_' ;
-- substring 'abcdefg' FROM 4 FOR 2
SELECT SUBSTR('abcdefg' from 4 FOR 2);
-- indexof
SELECT INSTR('abcdefg','defg');
CREATE TABLE t(name varchar(64));
INSERT INTO t(name) values('zhAng');
SELECT
CONCAT_WS('-',UPPER(SUBSTR(name,1,1)),LOWER(SUBSTR(name,2)))
FORM t;
-- 如何去空格
SELECT TRIM(' abcdefg ');
SELECT LTRIM(' abcdefg ');
-- padding 补齐位数
SELECT LPAD('abcdefg',10,'0');
SELECT RPAD('abcdefg',10,'0');
-- 替换
SELECT REPLACE('abcdefg','abc','dog');
-- FORMAT数字的格式化，保留两位小数
SELECT FORMAT(100000,0);
-- 数学函数
CEIL FLOAT DIV MOD POWER ROUND TRUNCATE
-- 时间函数
NOW CURDATE CURTIME YEAR MONTH DAY HOUR MINUTE SECOND
SELECT STR_TO_DATE('2018-09-09','%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%m-%d-%Y');
SELECT DATE_ADD(NOW(),INTERVAL 365 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATEDIFF('2019-5-1',NOW());
-- 每个客户端链接上服务器之后都会有一个链接的ID
SELECT CONNECTION_ID();
-- 当前数据库的名称
SELECT DATABASE();
SELECT VERSION();
SELECT USER();
insert into t(name) value('wangwu');
SELECT LAST_INSERT_ID();
SELECT PASSWORD('root');
SELECT MD5('root');
SELECT * FROM studb1.user;
-- if
SELECT IF(1>2,1,0)
-- case 函数
CASE 要判断的字段或表达式、
WHEN 常量1 then 要显示的值
WHEN 常量2 then 要显示的值
ELSE 要显示的值
END
SELECT * FROM score;
SELECT
CASE grade
WHEN 100 THEN '满分'
WHEN 90 THEN '优秀'
ELSE '其他'
END
FROM score;
SELECT
CASE
WHEN grade>100 THEN '优'
WHEN grade>90 THEN '良'
ELSE '其他'
END
FROM score;
-- 把学生表中的邮箱由小写改成大写
UPDATE student SET email = UPPER(email);
-- 自定义函数
CREATE FUNCTION ZNOW1(format VARCHAR(64)) RETURNS VARCHAR(128)
RETURN DATE_FORMAT(NOW(),format)
SELECT ZNOW1('%y-%m-%d %h:%i:%s');
-- 函数体可能不止一行
CREATE FUNCTION ADD_USER(name VARCHAR(64)) RETURNS INT
BEGIN
INSERT INTO t(name) VALUES(name);
return 1581313;
END
SELECT ADD_USER('chenqi');
SELECT * FROM score WHERE grade>70 AND grade<100;
SELECT * FROM score WHERE grade BETWEEN 70 AND 100;
SELECT * FROM score;
-- 全班的总分
SELECT SUM(grade) FROM score WHERE student_id = 1;
SELECT AVG(grade) FROM score WHERE student_id = 1;
SELECT COUNT(grade) FROM score WHERE student_id = 1;
SELECT MAX(grade) FROM score WHERE student_id = 1;
SELECT MIN(grade) FROM score WHERE student_id = 1;
-- 统计每位学生的平均分
SELECT student_id,AVG(grade)
FROM score
-- WHERE student_id =1
GROUP BY student_id
-- 统计每门课程的最高分，并按分数从高到低排列
SELECT course_id,MAX(grade)
FROM score
GROUP BY course_id
ORDER BY MAX(grade) ASC
-- 多列分组 统计各省男女的总人数
SELECT province,gender,COUNT(*)
FROM student
GROUP BY province,gender
-- 分组筛选
SELECT province,COUNT(*)
FROM student
GROUP BY province
HAVING COUNT(*)>1
-- 统计不及格次数大于1的同学
SELECT student_id,COUNT(*)
FROM score
WHERE grade<=60
GROUP BY student_id
HAVING COUNT(*)>1
-- 返回此列不为null的记录数
-- * 就等同于记录数
SELECT COUNT(*) FROM t2;
-- 查询年龄大于平均年龄的学生
SELECT *
FROM student
WHERE age>(SELECT AVG(age) FROM student)
-- any some all
-- 年龄大于陕西省任何一位同学
SELECT *
FROM student
WHERE age>ANY(SELECT age FROM student WHERE province='陕西省')
-- 年龄大于陕西省所有的同学
SELECT *
FROM student
WHERE age>ALL(SELECT age FROM student WHERE province='陕西省')
-- 年龄大于陕西省某些同学
SELECT *
FROM student
WHERE age>SOME(SELECT age FROM student WHERE province='陕西省')
-- 查询有考试成绩的同学
SELECT *
FROM student
WHERE id NOT IN (SELECT student_id FROM score);
-- exist not exist
SELECT * FROM student
WHERE EXISTS(SELECT * FROM score where score.student_id = student.id)
6.表连接
-- 表连接
CREATE VIEW xx AS SELECT * FROM student;
-- 查询学生姓名和学生的分数 表连接
SELECT * FROM student INNER JOIN score ON student.id = score.student_id;
SELECT * FROM student,score
WHERE student.id = score.student_id;
-- 左外连接
SELECT * FROM student LEFT JOIN score ON student.id = score.student_id;
-- 右外连接
SELECT * FROM student RIGHT JOIN score ON student.id = score.student_id;
-- 学生姓名 课程名 分数
SELECT student.name,course.name,score.grade FROM student,course,score
WHERE score.student_id = student.id AND score.course_id = course.id;
SELECT student.name,course.name,score.grade
FROM score INNER JOIN student ON score.student_id = student.id
INNER JOIN course ON score.course_id = course.id;
7.删除重复
-- 删除重复记录
INSERT INTO category(id,name,parent_id)
VALUES
(7,'iPad',1),
(8,'李宁',2),
(9,'康师傅',3);
SELECT * FROM category;
-- 查出来要删除的ID
SELECT * FROM category c1 LEFT JOIN
(SELECT id,name FROM category GROUP BY name having COUNT(*)>1) c2
ON c1.name = c2.name WHERE c1.id != c2.id
-- 用IN ，NOT IN也可以实现
SELECT * FROM category c1
WHERE c1.name IN
(SELECT name FROM category GROUP BY name having COUNT(*)>1)
AND c1.id NOT IN
(SELECT MIN(id) FROM category GROUP BY name having COUNT(*)>1)
-- 实现真正的删除
DELETE FROM category
WHERE name IN
(SELECT name FROM (SELECT name FROM category GROUP BY name having COUNT(*)>1) t1)
AND id NOT IN
(SELECT id FROM (SELECT MIN(id) id FROM category GROUP BY name having COUNT(*)>1) t2)
-- 按province分组后name会随意取一个，没有意义
SELECT province,name FROM student GROUP BY province
create table province(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(64)
)
INSERT INTO province(name) SELECT DISTINCT province FROM student;
-- 更新省份
UPDATE student INNER JOIN province ON student.province = province.name
SET student.province = province.id;
ALTER TABLE student MODIFY province INT NOT NULL;
-- 建city表
CREATE TABLE city (id INT PRIMARY KEY auto_increment,name VARCHAR(64))
-- 把学生表中的city数据插入到city表中
INSERT INTO city(name) SELECT DISTINCT city FROM student;
SELECT * FROM city;
-- 多表联合更新 把student表中的city字段从城市文本字符串改为文本在city表中的id
UPDATE student INNER JOIN city ON student.city = city.name
set student.city = city.id
-- 把city字段进行更新，改为city_id改为int类型
ALTER TABLE student
CHANGE COLUMN city city_id int(11) NOT NULL AFTER province;
8.无限分类
-- NC oracle
-- 库存量
CREATE TABLE category(
id int PRIMARY KEY AUTO_INCREMENT NOT NULL,
name VARCHAR(64),
parent_id int
)
INSERT INTO category(id,name,parent_id)
VALUES (1,'数码产品',0),(2,'服装',0),(3,'食品',0),
(4,'iPad',1),(5,'李宁',2),(6,'康师傅',3);
-- COUNT() 函数返回匹配指定条件的行数
SELECT * FROM category;
-- 查询所有的顶级分类下面的类别的数量
SELECT c1.id,COUNT(*) FROM category c1 INNER JOIN category c2 ON c1.id = c2.parent_id
WHERE c1.parent_id = 0
GROUP BY c1.id
-- 要把所有的父ID变成名称
SELECT c1.id,c1.name 名称,c2.name 父分类名称 FROM category c1 INNER JOIN category c2 ON c1.parent_id = c2.id;
category表
9.通过模型建表
- 模型同步到数据库
- 导入sql文件
