Mysql基本语句复习

------------------------创建与管理表-------------------------------------------------

-- 创建和管理表


-- 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', '[e-mail]');


-- 联合约束
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', '[e-mail]');


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语句  :)