[打卡]5.28

#AUTO_INCREMENT 自增列
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');

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

SELECT * FROM test1;

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

INSERT INTO test1
(id,last_name)
VALUES

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

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

#在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;

USE atguigudb;
#1查询和Zlotkey相同部门的员工姓名和工资
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
             );