[打卡]赵梓焓——自增长

#自增长列 AUTO_INCREMENT

#自增约束的列的数据类型必须是整数类型

create table employee
( eid int auto_increment, ename varchar(20) );
#错误
#没有主键

CREATE DATABASE dbtest;

USE dbtest;

SELECT DATABASE();#查看当前使用的数据库

#在CREATE TABLE时添加

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

DESC test1;

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



#结论：但我们向主键（含有增长列）的字段上增加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');

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;
#1
SELECT last_name,salary
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
);
#2
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);