• 数据库学习笔记——DML

    数据库学习笔记——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									
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    将数据插入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');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45

    将数据插入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');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    将数据插入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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    将数据插入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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45

    创建临时数据表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);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    出现警告信息：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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    使用两位字符串表示，范围为 ’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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 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');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    ‘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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

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

    • 1

    创建临时表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');

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    修改姓名为‘Brooks’的工资为16000：

    UPDATE works 
set salary=16000 
where employee_ID in (SELECT employee_ID from employee where employee_name='Brooks');

    • 1
    • 2
    • 3

    将‘Brooks’的公司改为‘Huawei’，工作改为‘17000’ :

    UPDATE works
set company_name='Huawei',salary=17000
where employee_ID in (SELECT employee_ID from employee where employee_name='Brooks'); 

    • 1
    • 2
    • 3

    为所有雇员增加100元工资：

    UPDATE works
set salary=salary+100;

    • 1
    • 2

    删除姓名为“DongXin”雇员的信息：

    DELETE from employee where employee_name='DongXin';

    • 1
  • 相关阅读:
    8.字符串转换整数（atoi）
    [RK3568 Android11]火焰图详解
    随想录一刷Day48——动态规划
    Java的synchronized关键字用法
    Web大学生网页作业成品——美食餐饮网站设计与实现(HTML+CSS+JavaScript)
    【深入浅出 Yarn 架构与实现】3-3 Yarn Application Master 编写
    java基本微信小程序的心理服务平台 uniapp 小程序
    22年11月-外包-面试题
    大学网课查题公众号零基础注册使用（内含详细教程和接口）
    如何让Python2与Python3共存
  • 原文地址：https://blog.csdn.net/m0_62122789/article/details/133658082