自增长列

CREATE DATABASE dbtest;

USE dbtest;

SELECT DATABASE();

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

DESC test1;

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

SELECT * FROM test1;

#当我们向主键（含有自增列）的字段上添加0或NULL时，实际上会自动添加指定的字段的数值

INSERT INTO test1
(id,last_name)
VALUES (0,'Tom');

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

#开发中，一旦主键作用的字段（含有自增列）时，我们在添加数据时，就不要给主键对应的字段去赋值

INSERT INTO test1
(id,last_name)
VALUES (20,'Lucy');

INSERT INTO test1
(id,last_name)
VALUES (-20,'Lucy');

INSERT INTO test1
(id,last_name)
VALUES (0,'xiaohong');

CREATE TABLE test2(
id INT PRIMARY KEY,
last_name VARCHAR(15)
);

DESC test2;

ALTER TABLE test2
MODIFY id INT auto_increment;

ALTER TABLE test2
MODIFY id INT;

DESC test2;

USE atguigudb;

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
);