扫码加入数据分析学习群

3.DML

-- 指定字段名添加

insert into dept(depid,depname,peoplecount)values('p10’，’人力部’，,15)，(‘p20’,’研发部’,50);

-- 不指定字段名添加

insert into dept values

('p10’,’人力部’,15)，(‘p20',’研发部’,50);

-- 查看默认安全路径

show variables like ‘%secure%'

-- 为 Monthly Indicator 表导入外部 txt 文件

load data infile ‘c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/xx.txt'

into table Monthly Indicator

fields terminated by ‘\ t’

[ignore l lines];

-- 检查导入内容 Monthly Indicator

select * from Monthly Indicator;

-- 检查导入数据总行数 Monthly Indicator

select count(x) from Monthly Indicator;

-- 检查表结构

desc Monthly Indicator;

--将部门 ID为 p01的信息单独保存到 department 表中

create table department as

select depid,depname,peoplecount

from dept

where depid='p01';

-- 将 dept 表中编号为 p11 的部门名称修改为后勤部

update dept set depname='后勤部 where depid='p11';

-- 删除 p20 部门的员工记录

delete from dept where depid=p20;

-- 删除所有的员工记录

delete from dept;

truncate dept;