1.创建一个企业员工管理的数据库,数据库名称为“userdb”。要求判断建库库时是否已存在。数据库字符集要求设置为支持中文字符的字符集“gbk”,字符集校验规则设置为gbi_chinese_ci
CREATE DATABASE userdb
CHARACTER SET gbk
COLLATE gbk_chinese_ci;
`character
`collate
2.在userdb数据库中按照要求创建以下数据表,注意不要遗漏实体完整性和域完整性约束条件:
部门表 Departments
create table if not exists departments(
d_id int unsigned primary key auto_increment comment '部门编号',
d_name varchar(50) not null COMMENT '系部名称',
note text COMMENT '部门介绍',
unique(d_name)
);
`if not exists
`int unsigned-->非负整数
`primary key-->主键
`auto_increment-->自增长
`unique
雇员表 Employees
create table if not exists employees(
e_id int unsigned primary key auto_increment comment '雇员编号',
d_id int unsigned comment '部门编号',
`name` varchar(20) not null comment '姓名',
age int unsigned not null comment '年龄',
sex char(10) not null default '男' comment '性别',
edate datetime comment '入职日期'
);
`not null
`default
雇员薪水表 Salary
create table if not exists salary(
e_id int unsigned primary key not null comment '雇员编号',
income float comment '收入',
outcome float comment '各项扣款'
);
3.为雇员表创建外键fk_dm参照完整性约束,被参照的父表是部门表。定义参照约束操作策略为:删除操作置空值,更新操作级联
# 创建外键模板
ALTER TABLE child_table # 为what创建外键,“ALTER what”
ADD CONSTRAINT fk_name # 创建外键
FOREIGN KEY (column_name) # 两表的关联
REFERENCES parent_table (column_name); # 被参照的父表
ALTER TABLE employees
ADD CONSTRAINT `fk_dm`
FOREIGN KEY (d_id)
REFERENCES departments (d_id)
ON DELETE SET NULL
ON UPDATE SET CASCADE;
`alter
`constraint
`foreign key
`references-->参照
`on
`cascade-->级联
4.为雇员薪水表增加一列实际薪资Realincome,类型为整型,可以为空
ALTER TABLE table_name
ADD COLUMN new_column_name datatype;
ALTER TABLE employees
ADD COLUMN realincome int;
5.向部门表 Departments中插入记录
INSERT INTO departments(d_id,d_name) VALUES
(1,'销售部'),
(2,'采购部'),
(3,'生产部');
6.员工王林岗位发生调动,由原本的销售部调入采购部。请修改数据以匹配他的新岗位
INSERT INTO employees(e_id,d_id,name,age) VALUES
(1,1,'王林',40);
先写入王林的数据
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE employees
SET d_id = 2
WHERE name = '王林';
7.查询各部门员工的总收入,列出部门名和总收入
SELECT d_name '部门名称',SUM(e.realincome) '总收入'
FROM departments d
JOIN employees e
on e.d_id=d.d_id
GROUP BY d.d_name
`join...on...
`group by