首页 小组 文章 相册 留言本 用户 搜索 我的社区 在线学堂 商城 购物车 支付钱包

[打卡]谷加伟——增长列

2024-05-28 15:00:57
0
81

# 自增列 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或UNLL时,实际上会自动往上添加数字
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(20,'LiLi');

SELECT * FROM test1;

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

INSERT INTO test1(id,last_name)
VALUES(-1,'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;

1 查询和Zlotkey相同部门的员工和工资

USE atguigudb;

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

评论
意见反馈