[打卡]郭佳豪——自增长

# 自增长列 AUTO_INCREMENT

CREATE DATABASE dbtest;

USE dbtest;

SELECT DATABASE();

# 在CREATE TABLE 时添加

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(30,'meiyangyang');

INSERT INTO test1
(id,last_name)
VALUES(-30,'meiyangyang');

SELECT * FROM test1;


# 在ALTER TABLE时添加（很少）

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

DESC test2;

ALTER TABLE test2
MODIFY id INT AUTO_INCREMENT;

# 在ALTER TABLE时删除

ALTER TABLE test2
MODIFY id INT;

DESC test2;

USE atguigudb;

SELECT DATABASE();

# 查询和Zlotket相同部门的员工姓名和工资

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