数据库学习笔记——DML
建表:
CREATE TABLE employee(
employee_ID int not null,
employee_name varchar(20) not null,
street varchar(20) not null,
city varchar(20) not null,
PRIMARY KEY(employee_ID)
);
CREATE TABLE company(
company_name varchar(30) not null,
city varchar(20) not null,
PRIMARY KEY(company_name)
);
create table manages(
employee_ID int not null,
manager_ID int,
primary key(employee_ID),
foreign key(employee_ID) references employee(employee_ID) on delete cascade,
foreign key(manager_ID) references employee(employee_ID) on delete set null
);
create table works(
employee_ID int not null,
company_name varchar(30),
salary numeric(8,2) check (salary>3000),
primary key(employee_ID),
foreign key(employee_ID) references employee(employee_ID) on delete cascade,
foreign key(company_name) references company(company_name) on delete set null
);
将数据插入employee表:
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319001,'Johnson','XihuRoad','Hangzhou');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319002,'Glenn','XihuRoad','Hangzhou');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319003,'Williams','ZhongshanRoad','Wuhan');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319004,'Chambers','Jiefang Road','Maoming');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319005,'Brooks','Secong Road','Shenzhen');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319006,'Greens','Xiyuan','GuangZhou');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319007,'Turner','FirstRoad','Shenzhen');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319008,'Wilder','FirstRoad','Shenzhen');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319009,'FrieRice','Xiyuan','GuangZhou');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319010,'Jackson','University','Tianjing');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319011,'MrSmithen','Beijing Road','Wuhan');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319012,'Majeris','First','Beijing');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319013,'McBride','Safety','Beijing');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319014,'Curry','North','Tianjing');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319015,'Edmonds','University','Tianjing');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319016,'Smith','Beijing Road','GuangZhou');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319017,'Shelby','Beijing Road','GuangZhou');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319018,'MrChen','First','Foshan');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319019,'McDong','Safety','Maoming');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319020,'MrDeng','FirstRoad','Shenzhen');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319021,'DongXin','FirstRoad','Shenzhen');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319022,'CbingQuan','Jiefang Road','Maoming');
INSERT into employee (employee_ID,employee_name,street,city) VALUES (319023,'Leach','SecondRoad','Shenzhen');
将数据插入company表:
INSERT into company (company_name,city) VALUES ('Alibaba','Hangzhou');
INSERT into company (company_name,city) VALUES ('Baidu','Beijing');
INSERT into company (company_name,city) VALUES ('Huawei','Shenzhen');
INSERT into company (company_name,city) VALUES ('Netease','GuangZhou');
INSERT into company (company_name,city) VALUES ('Tensent','Shenzhen');
INSERT into company (company_name,city) VALUES ('The People Bank','Beijing');
将数据插入manages表:
INSERT into manages (employee_ID,manager_ID) VALUES (319005,NULL);
INSERT into manages (employee_ID,manager_ID) VALUES (319001,319002);
INSERT into manages (employee_ID,manager_ID) VALUES (319003,319002);
INSERT into manages (employee_ID,manager_ID) VALUES (319002,319004);
INSERT into manages (employee_ID,manager_ID) VALUES (319004,319004);
INSERT into manages (employee_ID,manager_ID) VALUES (319006,319005);
INSERT into manages (employee_ID,manager_ID) VALUES (319007,319005);
INSERT into manages (employee_ID,manager_ID) VALUES (319008,319005);
INSERT into manages (employee_ID,manager_ID) VALUES (319010,319009);
INSERT into manages (employee_ID,manager_ID) VALUES (319016,319009);
INSERT into manages (employee_ID,manager_ID) VALUES (319012,319014);
INSERT into manages (employee_ID,manager_ID) VALUES (319013,319014);
INSERT into manages (employee_ID,manager_ID) VALUES (319014,319014);
INSERT into manages (employee_ID,manager_ID) VALUES (319009,319017);
INSERT into manages (employee_ID,manager_ID) VALUES (319017,319017);
INSERT into manages (employee_ID,manager_ID) VALUES (319018,319018);
INSERT into manages (employee_ID,manager_ID) VALUES (319019,319018);
INSERT into manages (employee_ID,manager_ID) VALUES (319020,319021);
INSERT into manages (employee_ID,manager_ID) VALUES (319021,319021);
INSERT into manages (employee_ID,manager_ID) VALUES (319022,319021);
INSERT into manages (employee_ID,manager_ID) VALUES (319023,319021);
将数据插入works表:
INSERT into works (employee_ID,company_name,salary) VALUES (319001,'Alibaba',7633.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319002,'Alibaba',12500.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319003,'Alibaba',10500.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319004,'Alibaba',17000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319005,'Tensent',15000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319006,'Tensent',10500.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319007,'Tensent',10655.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319008,'Tensent',12000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319009,'Baidu',20000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319010,'Baidu',19000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319011,'Netease',8000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319012,'The People Bank',7000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319013,'The People Bank',6335.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319014,'The People Bank',18500.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319015,'The People Bank',19000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319016,'Baidu',18000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319017,'Baidu',21000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319018,'Netease',15000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319019,'Netease',8000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319020,'Huawei',13000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319021,'Huawei',16000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319022,'Huawei',14000.00);
INSERT into works (employee_ID,company_name,salary) VALUES (319023,'Huawei',15000.00);
创建临时数据表temp1,其中字段x、y、z数据类型依次为FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1), 向表中插入数据5.12、5.15和5.123 :
CREATE TABLE temp1(
x FLOAT(5,1),
y DOUBLE(5,1),
z DECIMAL(5,1)
);
INSERT into temp1 (x,y,z) VALUES (5.12,5.15,5.123);
出现警告信息:Data truncated for column ‘z’ at row 1
在创建一个decimal(5,1)的字段时,我们指定字段的总长度为5,小数点之后的位数为1,这意味着我们可以插入一个包含4个整数和1个小数点的数字。如果我们尝试插入超过该值的数据,z 的数据被截断,MySQL就会抛出上述异常。
创建临时数据表temp2, 定义数据类型为YEAR的字段 y,向表中插入值2010、‘2010’和‘2166’,并查看表temp2的结果 :
CREATE TABLE temp2(
y YEAR
);
INSERT into temp2 VALUES (2010);
INSERT into temp2 VALUES ('2010');
INSERT into temp2 VALUES ('2166');
# Out of range value for column 'y' at row 1
YEAR类型用于表示年份,使用4位字符串或数字表示,
范围为:‘1901’—‘2155’或1901—2155 。
向temp2表中的y字段插入2位字符表示的YEAR值,分别为‘0’,‘00’,‘77’和‘10’,并查看表temp2的结果:
drop TABLE temp2;
CREATE TABLE temp2(
y YEAR
);
INSERT into temp2 VALUES ('0');
INSERT into temp2 VALUES ('00');
INSERT into temp2 VALUES ('77');
INSERT into temp2 VALUES ('10');
使用两位字符串表示,范围为 ’00’—‘99’。其中, ’00’—‘69’ 范围的值会被转换为2000—2069范围的YEAR值;’70’—‘99’范围的值会被转换为1970—1999范围的YEAR 值。
向temp2表中的y字段插入2位数字表示的YEAR值,分别为0、78和11,并查看表temp2的结果:
drop TABLE temp2;
CREATE TABLE temp2(
y YEAR
);
INSERT into temp2 VALUES (0);
INSERT into temp2 VALUES (78);
INSERT into temp2 VALUES (11);
使用两位数字表示,范围为1—99。
其中,1—69范围的值会被转换为2001—2069范围的YEAR值;
70—99范围的值会被转换为1970—1999范围的YEAR值。
注意:当使用YEAR类型时,一定要区分’0’和0。
因为字符串格式的’0’表示的YEAR值是2000而数字格式的0表示的YEAR值是0000。
创建临时表temp3,定义数据类型为TIME的字段t,向表中插入值‘10:05:05’、‘23:23’、‘2 10:10’、‘3 02’、‘10’,并查看表temp2的结果 :
CREATE TABLE temp3(
t time
);
INSERT INTO temp3 VALUES ('10:05:05');
INSERT INTO temp3 VALUES ('23:23');
INSERT INTO temp3 VALUES ('2 10:10');
INSERT INTO temp3 VALUES ('3 02');
INSERT INTO temp3 VALUES ('10');
‘D HH:MM:SS’格式的字符串:
D表示天数,取值范围是0~34。保存时,小时的值等于(D*24+HH)。
输入时可以是‘HH:MM:SS’,‘HH:MM’,‘D HH:MM’,‘D HH’,‘SS’等形式。
向temp3中插入值‘101112’、111213,0,‘107010’:
drop table temp3;
CREATE TABLE temp3(
t time
);
INSERT INTO temp3 VALUES ('101112');
INSERT INTO temp3 VALUES ('111213');
INSERT INTO temp3 VALUES ('0');
INSERT INTO temp3 VALUES ('107010');
# Incorrect time value: '107010' for column 't' at row 1
‘HHMMSS’格式的字符串或HHMMSS格式的数值表示:
例如,输入‘101112’,Time类型会转换成10:11:12;输入101112,Time类型会转换成10:11:12。
如果输入0或者‘0’,那么TIME类型会转换为00:00:00。
向temp3表中插入系统的当前时间:
INSERT INTO temp3 VALUES (CURRENT_TIME);
创建临时表temp4,定义数据类型为DATA的字段d,向表中插入‘1998-08-08’、‘19980808’和‘20201010’:
CREATE TABLE temp4(
d DATE
);
INSERT into temp4 VALUES ('1998-08-08');
INSERT into temp4 VALUES ('19980808');
INSERT into temp4 VALUES ('20201010');
修改姓名为‘Brooks’的工资为16000:
UPDATE works
set salary=16000
where employee_ID in (SELECT employee_ID from employee where employee_name='Brooks');
将‘Brooks’的公司改为‘Huawei’,工作改为‘17000’ :
UPDATE works
set company_name='Huawei',salary=17000
where employee_ID in (SELECT employee_ID from employee where employee_name='Brooks');
为所有雇员增加100元工资:
UPDATE works
set salary=salary+100;
删除姓名为“DongXin”雇员的信息:
DELETE from employee where employee_name='DongXin';