[打卡]万物不及桐神 5.28

-- auto_increment 自增长列

-- 在create TABLE 时创建

CREATE TABLE db;
USE db;
SELECT DATABASE();

CREATE TABLE t1(
id INT PRIMARY KEY auto_increment,
last_name VARCHAR(15)

);

DESC t1;

INSERT INTO t1
(last_name)
VALUES('Sam');

-- 当我们向主键（含有自增列）的字段上添加0或者null时，实际上会自动向上添加指定字段的数值
SELECT * FROM t1;

INSERT INTO t1
(id,last_name)
VALUES(0,'Sam');

INSERT INTO t1
(id,last_name)
VALUES(NULL,'LiLi');

-- 开发中，一旦主键作用的字段（含有自增列）时，则我们在添加数据的时候，不要给对应的字段去赋值
INSERT INTO t1
(id,last_name)
VALUES(0,'xiaohong');

-- 在alter table 时添加（很少）
CREATE TABLE t2(
id INT PRIMARY KEY,
last_name VARCHAR(15)
);

DESC t2;

ALTER TABLE t2
MODIFY id INT auto_increment;

-- 在alter table 时删除
ALTER TABLE t2
MODIFY id INT;

DESC t2;


SELECT last_name,salary
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
);

SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
);