再捡SQL

创建表

字段

字段名 字段类型 是不是null(必须) 自增 默认值

字段类型

主键和外键

主键

根据设计原则，每张表都要有主键 

主键必须满足：唯一        不能更改        无业务含义

外键

用于产生表关系的列 

外键列会连接到另一张表(或自己)的主键

表关系

一对一

概念：一个A对应一个B,一个B对应一个A

实现：把任意一张表的主键同时设置为外键

一对多

概念：一个A对应多个B，一个B对应一个A，A和B是一对多，B和A是多对一

实现：在多一端的表上设置外键，对应到另一张表的主键

多对多

概念：一个A对应多个B，一个B对应多个A

实现：需要新建一张关系表，关系表至少包含两个外键，分别对应到两张表

三大设计范式

1. 要求数据库表的每一列都是不可分割的原子数据项
2. 非主键列必须依赖于主键列
3. 非主键列必须直接依赖主键列

表记录的增删改

// 增
INSERT INTO `student` (id, stuno, `name`, birthday, sex, phone, classId)
VALUES(3, '500', '刘亦菲', '2007-8-8', DEFAULT, '15288888888', 1)

// 改
UPDATE student SET `name`='迪丽热巴'
WHERE id=1;

// 删
DELETE FROM student
WHERE id=1;

查

select from

SELECT loginId, loginPwd, 'b' as 'extra' FROM `user`;

SELECT loginId '账号' FROM `user`;

SELECT * FROM `user`;

SELECT *, 'a' as 'extra' FROM `user`;


-- SELECT id, `name`,
-- case ismale
-- when 1 then '女'
-- else '男'
-- end sex,
-- salary
-- FROM employee;

where

// where =
SELECT id, `name`,
case
when ismale = 1 then '女'
else '男'
end sex,
salary
FROM employee;

// where in
SELECT * FROM department
WHERE companyId in (1, 2);

// where is
SELECT * FROM employee
WHERE location is null;

SELECT * FROM employee
WHERE location is not null;

// where > < >= <=
SELECT * FROM employee
WHERE salary>=200;

// where between
SELECT * FROM employee
WHERE salary BETWEEN 200 and 300;

// where like模糊查询
SELECT * from employee
WHERE `name` like '%a%'; //_匹配单个字符

// where and 多个条件并列
SELECT * FROM employee
WHERE `name` like 'a%' and ismale=0 and salary>=100;

// where or
SELECT * FROM employee
WHERE `name` like 'a%' and ismale=0 and salary>=100
OR
birthday>='2000-1-1';

order by

// asc 升序
SELECT * FROM employee
WHERE `name` like 'a%' and ismale=0 and salary>=100
OR
birthday>='2000-1-1'
ORDER BY salary ASC;

//desc 降序
SELECT * FROM employee
WHERE `name` like 'a%' and ismale=0 and salary>=100
OR
birthday>='2000-1-1'
ORDER BY salary DESC, birthday ASC;   //组合，salary值相同时按出生日期升序排序

limit

// 限制，从第1行之后取，取3行数据
SELECT * FROM employee
LIMIT 1,3;