------------------------创建与管理表-------------------------------------------------
-- 创建和管理表
-- 1.创建数据库
CREATE DATABASE atguigu;
-- 选择数据库
USE atguigu;
-- 2. 创建和管理表
-- DDL : 数据定义语言
-- 1). 新建表 CREATE TABLE
-- 方式一:
CREATE TABLE emp(
id INT,
NAME VARCHAR(30),
hire_date DATE,
salary DOUBLE(10, 2)
);
DESC emp;
CREATE TABLE emp1(
id INT AUTO_INCREMENT,
NAME VARCHAR(30),
hire_date DATE,
salary DOUBLE(10,2),
CONSTRAINT PRIMARY KEY(id)
);
SELECT * FROM emp1;
-- 方式二:基于现有表创建新表
-- 相当于表的复制
CREATE TABLE emp2
AS
SELECT *
FROM employees;
SELECT * FROM emp2;
DESC employees;
DESC emp2;
CREATE TABLE emp3
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 90;
SELECT * FROM emp3;
-- 基于现有表创建新表,但是不导入数据
CREATE TABLE emp4
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE 1 = 2;
SELECT * FROM emp4;
-- 2). 修改表 ALTER TABLE
-- ①添加列
ALTER TABLE emp3
ADD age INT;
ALTER TABLE emp3
ADD gender VARCHAR(5) DEFAULT '男';
-- ②修改列
ALTER TABLE emp3
MODIFY gender VARCHAR(2);
DESC emp3;
-- ③重命名列
ALTER TABLE emp3
CHANGE gender gender22 VARCHAR(5);
-- ④删除列
ALTER TABLE emp3
DROP COLUMN gender22;
-- 3). 重命名表
ALTER TABLE emp3
RENAME employees3;
SELECT * FROM employees3;
-- 4). 删除表
DROP TABLE emp3;
-- 5). 清空表
SELECT * FROM emp3;
TRUNCATE TABLE emp3;
-- DML : 数据操纵语言
DELETE FROM emp3;
-- DCL : 数据控制语言
SET autocommit = FALSE;
COMMIT; -- 提交
ROLLBACK; -- 回滚,默认回滚到上一次 commit;
-- 注意:DDL 不可以回滚的,自动 commit;
--------------------------------数据的增删改------------------------------------
-- 数据处理之增删改
-- DDL : 数据定义语言
CREATE TABLE emp5(
id INT(7),
NAME VARCHAR(30),
hire_date DATE,
salary DOUBLE(10, 2)
);
-- DML : 数据操纵语言
-- 添加数据
INSERT INTO emp5(id, NAME, hire_date, salary)
VALUES(101, '张三', '1999-9-9', 9999.99);
INSERT INTO emp5
VALUES(102, '李四', '1990-10-10', 10000.00);
INSERT INTO emp5(id, NAME)
VALUES(103, '王五');
-- 基于现有表导入数据
INSERT INTO emp5(id, NAME, salary)
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 90;
-- 删除数据
DELETE FROM emp5
WHERE id = 100;
-- 修改数据
UPDATE emp5
SET NAME = 'HelloKitty'
WHERE id = 101;
-- 同时修改多列
UPDATE emp5
SET hire_date = '1980-11-11', salary = 20000.00, NAME = 'King'
WHERE id = 103;
-- 查询数据
SELECT * FROM emp5;
-- DCL : 数据控制语言
SET autocommit = FALSE;
COMMIT; -- 提交
ROLLBACK; -- 回滚
----------------------------------------约束与分页------------------------------------------------
-- 约束与分页
-- NOT NULL 非空约束,规定某个字段不能为空
CREATE TABLE emp1(
id INT(7) NOT NULL, -- 列级约束
NAME VARCHAR(30)
);
DESC emp1;
SELECT * FROM emp1;
INSERT INTO emp1(id, NAME)
VALUES(102, NULL);
INSERT INTO emp1(NAME)
VALUES('张三');
-- 添加非空约束
ALTER TABLE emp1
MODIFY NAME VARCHAR(30) NOT NULL;
-- 取消非空约束
ALTER TABLE emp1
MODIFY NAME VARCHAR(30) NULL;
-- UNIQUE 唯一约束,规定某个字段在整个表中是唯一的
CREATE TABLE emp2(
id INT(7),
NAME VARCHAR(30),
phone VARCHAR(30),
email VARCHAR(35),
CONSTRAINT emp2_name_un UNIQUE(NAME) -- 表级约束
);
SELECT * FROM emp2;
INSERT INTO emp2
VALUES(101, '张三11', '2436477', 'abc@abc.com');
-- 联合约束
CREATE TABLE emp3(
id INT(7),
NAME VARCHAR(30),
phone VARCHAR(30),
email VARCHAR(35),
CONSTRAINT emp3_phone_email_un UNIQUE(phone, email)
);
SELECT * FROM emp3;
INSERT INTO emp3
VALUES(101, '张三', '2436477877', 'abc@abcb.com');
TRUNCATE TABLE emp3;
-- 添加唯一约束
ALTER TABLE emp3
ADD CONSTRAINT emp3_name_un UNIQUE(NAME);
-- 删除唯一约束
ALTER TABLE emp3
DROP INDEX emp3_name_un;
-- PRIMARY KEY 主键(非空且唯一) 通常用于确定唯一一条数据
CREATE TABLE emp4(
id INT(7),
NAME VARCHAR(30),
CONSTRAINT emp4_id_pk PRIMARY KEY(id)
);
SELECT * FROM emp4;
INSERT INTO emp4(id, NAME)
VALUES(NULL, '张三');
-- 添加主键约束
CREATE TABLE emp5(
id INT(7),
NAME VARCHAR(30)
);
ALTER TABLE emp5
ADD CONSTRAINT emp5_id_pk PRIMARY KEY(id);
SELECT * FROM emp5;
INSERT INTO emp5(id, NAME)
VALUES(NULL, '张三');
DESC emp5;
-- 删除主键约束
ALTER TABLE emp5
DROP PRIMARY KEY;
ALTER TABLE emp5
MODIFY id INT(7) NULL;
DESC emp5;
-- FOREIGN KEY 外键约束:关联另一个主键,出现在外键表中的数据一定出现在主键表中
CREATE TABLE dept(
dept_id INT(7) PRIMARY KEY,
dept_name VARCHAR(25)
);
CREATE TABLE emp(
id INT(7) PRIMARY KEY,
NAME VARCHAR(30),
depart_id INT(7),
CONSTRAINT emp_depart_id_fk FOREIGN KEY(depart_id) REFERENCES dept(dept_id)
ON DELETE SET NULL
-- ON DELETE CASCADE(级联删除): 当父表中的列被删除时,子表中相对应的列也被删除
-- ON DELETE SET NULL(级联置空): 子表中相应的列置空
);
SELECT * FROM dept;
SELECT * FROM emp;
INSERT INTO dept
VALUES(10, 'IT');
INSERT INTO emp
VALUES(101, '张三', 10);
DELETE FROM emp
WHERE depart_id = 10;
DELETE FROM dept
WHERE dept_id = 10;
-- 添加外键约束
CREATE TABLE emp6(
id INT,
NAME VARCHAR(20),
dept_id INT(7)
);
ALTER TABLE emp6
ADD CONSTRAINT emp6_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id);
INSERT INTO emp6
VALUES(101, '张三', 10);
-- 删除外键约束
ALTER TABLE emp6
DROP FOREIGN KEY emp6_dept_id_fk;
-- CHECK 检查约束
CREATE TABLE emp7(
id INT(7),
NAME VARCHAR(30),
salary DOUBLE(10, 2),
CONSTRAINT emp7_salary_ck CHECK(salary > 3000)
);
SELECT * FROM emp7;
INSERT INTO emp7
VALUES(101, '张三', 800);
-- MySql 分页
-- (当前页数-1)*每页条数,每页条数
SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 20, 10;
-----------------------------------------简单的SQL语句 :)