自增

#自增长列AUTO_INCREMENT

CREATE DATABASE dbtest;

USE dbtest;
SELECT DATABASE();

#在CREATE TABLE表时添加

CREATE table test1(

id int PRIMARY KEY AUTO_INCREMENT,

last_naem VARCHAR(15)

);
desc test1;

insert into test1
(last_name)
VALUES('sum');

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

#在 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删除

CREATE table test4(
id int PRIMARY key,
last_name VARCHAR(10)
);

ALTER table test4
MODIFY id int;

desc test4;

#____复习题_______

use atguigudb;

SELECT last_name,salray
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
);

之前留的

SELECT DATABASE();

CREATE DATABASE dbtest;

use dbtest;

#唯一性约束

#UNIQUE的复核约束
CREATE TABLE user(
id int,
`name`VARCHAR(15),
`PASSWORD` VARCHAR(25),
#表级约束
CONSTRAINT uk_user_name_pad UNIQUE(`name`,`PASSWORD`)
);

SELECT * FROM user;

INSERT into user
VALUES(1,'Tom','abc');

INSERT into user
VALUES(1,'Tom1','abc');

desc user;
SELECT * FROM information_schema.TABLE_CONSTRAINTS;#查所有的约束
#单独查
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_name='user';
#删除唯一性约束

ALTER table user
drop index uk_user_name_pwd;

#PRIMARY key 主键约束
CREATE table test1(
id int PRIMARY key,
last_name VARCHAR(15)/*删PRIMARY key*/,
salary int,
email VARCHAR(25)
);#运行不成功，一个表中只能有一个主键约束
#主键约束的特征，非空且唯一，用于唯一的标识表中的一条记录
CREATE table test2(
id int,
last_name VARCHAR(15),
salary int,
email VARCHAR(25)
#表级约束
CONSTRAINT pm_test2_id PRIMARY key(id)#没有必要取名字，不管怎么取都不以自命名为约束的名字
);
desc test2;

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_name='test2';

INSERT into test2
VALUES(1,'Tom',3000,'tom@qq.com');

SELECT * FROM test2;
VALUES(1,'Tom2',30000,'tom@qq.com');

SELECT * FROM test2;
VALUES(null,'Tom8',80000,'tom@qq.com');#id不能为空

CREATE table test8(
id int,
last_name VARCHAR(15),
salary int,
email VARCHAR(25)
#表级约束
PRIMARY key(last_name,salary)
);
desc test8;
INSERT into test8
VALUES(1,'Tom',3000,'tom@qq.com');
SELECT * from test8;
INSERT into test8
VALUES(1,'Tom1',3000,'tom@qq.com');
INSERT into test8
VALUES(1,'Tom1',null,'tom@qq.com');
#在alter table
CREATE table test10(
id int,
last_name VARCHAR(15),
salary int,
email VARCHAR(25)
);
desc test10;

ALTER table test10
add PRIMARY key(id);
#如何删除主键约束(在实际开发中，不会去删除表中的主键约束)
ALTER table test10
drop PRIMARY key;

desc test10;