MySQL的CRUD、约束、视图

创建和管理数据库表

-- 创建数据库 test01，指明字符集为 utf8
CREATE DATABASE IF NOT EXISTS test01 CHARACTER SET 'utf8';

-- 使用数据库 test01
USE test01;

-- 创建表 students
CREATE TABLE students (
id INT(7),
NAME VARCHAR(25)
);

-- 创建表 teachers
CREATE TABLE teachers (
id INT(7),
NAME VARCHAR(25),
age INT(3)
);

-- 显示数据库 test01 的表
SHOW TABLES FROM test01;

-- 显示表 departments 的结构
DESC students;

-- 将列 NAME 的长度增加到50
ALTER TABLE students MODIFY NAME VARCHAR(50);

-- 删除表 students
DROP TABLE IF EXISTS students;

-- 将表 students 重命名为 students01
ALTER TABLE students RENAME TO students01;
RENAME TABLE students TO students01;

-- 在表 teachers 中添加新列 test_add_column
ALTER TABLE teachers ADD test_add_column VARCHAR(10);

-- 删除表 teachers 中的列 department_id
ALTER TABLE teachers DROP COLUMN test_add_column;

-- 将 NAME 字段插入到 age 字段后面，并且修改 NAME 数据类型为 VARCHAR(50)
ALTER TABLE teachers MODIFY NAME VARCHAR(50) AFTER age;

-- 将age字段数据类型改为 varchar(70)
ALTER TABLE teachers MODIFY age VARCHAR(70);

-- 将age字段改名为 AGE，数据类型为VARCHAR(50)
ALTER TABLE teachers CHANGE age AGE111 VARCHAR(50);

表数据的查询SELECT

-- 显示系统时间
SELECT NOW() FROM DUAL;

-- 查询去除重复的数据，使用DISTINCT
SELECT DISTINCT job_id FROM employees;

-- 选择的条件where，可以使用and、or、not、between...and...、in、is null、is not null、like
SELECT last_name, salary FROM employees 
WHERE salary NOT BETWEEN 5000 AND 12000;

-- 排序：order by ... ASC/DESC；
-- 分页：limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录
SELECT last_name,salary FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

-- 聚合函数count、sum、avg、max、min、count，聚合函数不能使用where子句进行过滤，用having
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees GROUP BY job_id;
SELECT AVG(salary) FROM employees;
-- 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id, MIN(salary) FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id 
HAVING MIN(salary) > 6000;

表数据的增删改

-- 增加、单条插入
INSERT INTO employee(id,`name`,sex,tel,addr,salary) VALUES
(10000,'张一一','男','13456789000','山东青岛',1001.58);

-- 增加、批量插入
INSERT INTO employee(id,`name`,sex,tel,addr,salary) VALUES
(10001,'张二二','男','13456789000','山东青岛',1001.58),
(10002,'刘小红','女','13454319000','河北保定',1201.21),
(10003,'李四','男','0751-1234567','广东佛山',1004.11),
(10004,'刘小强','男','0755-5555555','广东深圳',1501.23),
(10005,'王艳','男','020-1232133','广东广州',1405.16);

-- 删除
DELETE FROM pet WHERE death IS NOT NULL and species = 'Dog';

-- 修改
UPDATE employee SET addr='广东韶关' WHERE `name`='李四';

数据库表添加约束

数据库的五大约束：主键约束、外键约束、检查约束（数据的范围、格式限制）、默认约束（默认值）、唯一约束。

-- 添加主键约束
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE emp2 ADD PRIMARY KEY(id);

-- 添加列，并添加外键约束
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_deptid FOREIGN KEY(dept_id) REFERENCES
dept2(id);

-- 增加自增约束
ALTER TABLE books MODIFY id INT AUTO_INCREMENT;

-- 增加非空约束
ALTER TABLE books name VARCHAR(50) NOT NULL;

-- 删除字段note
ALTER TABLE employees DROP COLUMN note;

-- 创建表并添加约束
CREATE TABLE employee(
empid INT PRIMARY KEY,
`name` VARCHAR(20) NOT NULL,
sex CHAR NOT NULL DEFAULT '男',
title VARCHAR(20) NOT NULL,
birthday DATE,
depid INT,
FOREIGN KEY(depid) REFERENCES department(depid) ON UPDATE CASCADE ON DELETE SET
NULL
);

视图

1、视图是从一个或多个基本表中导出的虚拟表。

2、数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。
所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。

3、视图的创建和删除，不影响对应的基本表。但是对视图中的数据进行增、删、改操作，会影响基本表的数据，反之亦然。

-- 创建视图，AS关键字
CREATE OR REPLACE VIEW emps
AS
SELECT last_name,employee_id,department_id
FROM employees
WHERE department_id = 80;

-- 描述视图
DESC emps;
-- 不能往视图中插入数据
INSERT INTO emp(last_name,salary,email,phone_number)
VALUES('Tom',2300,'tom@126.com','1322321312'); #实测不可以

-- 修改视图emp中员工的工资，每人涨薪1000
-- 特别注意：会影响原表的数据
UPDATE emp SET salary = salary + 1000;

-- 删除emp中姓名为Olsen的员工
-- 特别注意：会影响原表的数据
DELETE FROM emp WHERE last_name = 'Olsen';

-- 删除视图
DROP VIEW IF EXISTS emp,emp_v1;