【MySQL】表的增删改查

新增(Create)

先创建一张学生表

DROP TABLE IF EXISTS student;					-- 若存在先删除
CREATE TABLE student (							-- 创建学生表 设置四个字段：编号、学号、姓名、qq邮箱
id INT,
sn INT comment '学号',
name VARCHAR(20) comment '姓名',
qq_mail VARCHAR(20) comment 'QQ邮箱'
);

对student表进行单行数据全列插入：

INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);		-- 插入的值列表要和表定义的顺序一致，可以插入NULL
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

对student表进行多行数据指定列插入：

INSERT INTO student (id, sn, name) VALUES		-- 指定插入编号、学号、姓名字段，未插入字段会默认设置为NULL
(102, 20001, '曹孟德'),							-- 多行数据之间用逗号间隔
(103, 20002, '孙仲谋');							-- 插入的字段列表需要和指定的字段列表顺序和数量保持一致

INSERT INTO student (id, sn, name) VALUES (104, 20003, '刘玄德'), (105, 20004, '张翼德');	--也可写在同一行

查询(Retrieve)

创建考试信息表并插入测试数据

DROP TABLE IF EXISTS exam_result;		-- 若存在先删除
CREATE TABLE exam_result (				-- 创建考试成绩表 设置编号、姓名、语文成绩、数学成绩、英语成绩字段
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);

INSERT INTO exam_result (id,name, chinese, math, english) VALUES	-- 这里直接指定所有字段，多列插入
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明',75, 65, 30);

全列查询：

SELECT * FROM exam_result;		-- 从exam_result表中查询所有列

指定列查询：

SELECT id, name, english FROM exam_result;				-- 指定查询编号、姓名和英语成绩字段列

查询字段为表达式：

SELECT id, name, 10 FROM exam_result;		-- 指定查询编号、姓名、10字段

SELECT id, name, english + 10 FROM exam_result;			-- 指定查询编号、姓名、英语成绩+10字段

SELECT id, name, chinese + math + english FROM exam_result;	-- 指定查询编号、姓名、语文成绩+数学成绩+英语成绩字段

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称：

-- 给语文成绩+数学成绩+英语成绩字段指定别名：总分
SELECT id, name, chinese + math + english 总分 FROM exam_result;   

排序：ORDER BY

SELECT name, qq_mail FROM student ORDER BY qq_mail;		-- 查询学生姓名和qq邮箱，按qq邮箱排序显示，默认升序
SELECT name, qq_mail FROM student ORDER BY qq_mail desc;-- 查询学生姓名和qq邮箱，按qq邮箱排序显示，加desc改为降序

条件查询：WHERE

SELECT name, english FROM exam_result WHERE english < 60;				-- 查询英语不及格的同学及英语成绩(<60)

SELECT name, chinese, english FROM exam_result WHERE chinese > english;	-- 查询语文成绩好于英语成绩的同学

SELECT name, chinese + math + english 总分 FROM exam_result				-- 查询总分在 200 分以下的同学
WHERE chinese + math + english < 200

修改(Update)

-- 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT
3;
-- 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

删除(Delete)

-- 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';


-- 删除整表数据(并不会删除这张表)
DELETE from 表名;

其它操作

ALTER TABLE `table_name` add unique (`field_name`);	-- 创建唯一性约束

show keys from `table_name`;						-- 查看唯一性约束(得到Key_name)

drop index `Key_name` on `table_name`;				-- 删除唯一性约束

ALTER TABLE test_table DROP COLUMN test_value;		-- 删除test_table表的 test_value字段 

ALTER TABLE `table_name` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `field_name` )	--修改主键

ALTER TABLE `table_name` ADD field_name json;				-- 添加json格式的字段

alter table table_name modify field_name1 after field_name2;	-- 调整表的字段1到字段2的后面


DESC table_name;		-- 描述表

ALTER table table_name MODIFY `field_name` json DEFAULT NULL COMMENT 'xxx'	-- 给列添加注释xxx

alter  table `table_name` change `field_name1` `field_name2` json DEFAULT NULL COMMENT 'xxx'		-- 修改表的字段1为字段2