MySQL的增删改查功能合集

数据库的本质就是存储数据并在此基础上对数据进行增删改查的操作今天就为您详细介绍一下数据库的主体四种功能在此之前先进行一个知识点补充在Java和C中我们可以用//和/**/进行注释但在数据库中我们用--空格 作为注释标记1.新增create功能1.1.语法INSERT [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ... value_list: value, [, value] ...1.2.补充1.2.1. show VARIABLES like %character%;这条命令是MySQL一条数据库命令用来查询当前数据库服务器中所有包含character字符串的系统变量具体解释SHOW VARIABLES是 MySQL 中用于查看数据库系统变量的命令这些变量控制着数据库的各种配置和行为如字符集、连接超时、缓存大小等LIKE %character%是一个模糊查询条件%是通配符表示匹配任意长度的字符包括零个字符。这里的意思是只显示变量名中包含character的变量。1.2.2.类型转换insert into student values(100,1,男)在本行代码中values后面的值类型上不与上面完全一致此时MySQL会尽量尝试进行转换如果转换失败则报错转换成功则执行insert操作。1.3create的其他用法1.3.1.单行数据全列插入-- 插入两条记录value_list 数量必须和定义表的列的数量及顺序一致 INSERT INTO student VALUES (100, 10000, 唐三藏); INSERT INTO student VALUES (101, 10001, 孙悟空); insert into student(id,name)values(3,王五);--指定行插入 -- insertintostudent(name,age)values(勾八66) -- 注意指定行插入时必须从左到右按照顺序插入1.3.2多行数据指定列插入-- 插入两条数据value——list数量必须和指定列的数量及顺序一致 insert into student values(5,田七,20,女), (6,勾八,20,女); -- 多条插入语句这样的操作客户端和服务器之间进行了一次网络通信如果把这条语句拆分成两条 那么就进行两次网络通信流程客户端——》服务器发送一整条sql语句 服务器——》客户端返回执行结果 一次请求一次相应一次网络交互2.查询REtrieve功能:2.1.语法补充 * 这里代表通配符可以代表各种信息SELECT [DISTINCT] {* | {column [, column] ...} [FROM table_name] [WHERE ...] [ORDER BY column [ASC | DESC], ...] LIMIT ...2.2.具体案例2.2.1补充decimal用法DECIMAL(M, D)M精度Precision表示数字的总位数整数位 小数位范围通常为1 ~ 65不同数据库可能有差异。D标度Scale表示小数部分的位数范围为0 ~ M。2.2.2具体案例实现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;2.3.select的多种用法2.3.1全列查询这种用法需谨慎会把所有数据都查询出来涉及大量硬盘访问和网络访问容易卡死-- 通常情况下不建议使用 * 进行全列查询 -- 1. 查询的列越多意味着需要传输的数据量越大 -- 2. 可能会影响到索引的使用。索引待后面课程讲解 SELECT * FROM exam_result;2.3.2指定列查询注意此处查询的结果得到的是一个临时的表不能对这个临时表做出任何修改这里的别名应用的数字集是utf8/utf8mb4-- 指定列的顺序不需要按定义表的顺序来 SELECT id, name, english FROM exam_result;2.3.3.查询时带有表达式边查边计算注意这里的查询结果仍然是临时表这里对临时表的操作不会影响到数据库服务器硬盘中的原始数据-- 表达式不包含字段 SELECT id, name, 10 FROM exam_result; -- 表达式包含一个字段 SELECT id, name, english 10 FROM exam_result; -- 表达式包含多个字段 SELECT id, name, chinese math english FROM exam_result;2.3.4.查询时带有别名为查询结果中的列指定别名表示返回的结果集中以别名作为该列的名称语法SELECT column [AS] alias_name [...] FROM table_name; select 表达式 as 别名 from 表名;特别的注意这里的as可以省略但不建议这么做。2.3.5.针对查询结果去重使用distinct关键字对某列数据进行去重select distinct math, chinese from exam_result; -- 这样的情况比较特殊不单独对任意一个列去重只有当两个数据的两个列都相同时才会被去重注意如果此时指定多个列去重时要求多个列都是相同的值才能去重select distinct 列名 from 表名;2.3.6.条件查询where2.3.6.1.比较运算符注意1.和唯一区别是判断null与null时的结果为true2.SQL把null视为falseSQL那别的值和NULL进行运算时结果仍为NULL。2.3.6.2.逻辑运算符注意1. WHERE条件可以使用表达式但不能使用别名2. AND的优先级高于OR在同时使用时需要使用小括号()包裹优先执行的部分。2.3.6.3用法案例实战基本查询a.查询英语不及格同学的成绩b.查询总分在200以下的同学AND和ORa.查询英语成绩大于80分且语文成绩大于80分的同学b.查询语文成绩大于80分或英语成绩大于80分的同学c.比较AND和OR的优先级select name,chinese,english,math from exam_result where chinese80 or (math70 and english70); -- 这条语句先执行后面and的语句 再执行or因为and的优先级高于or的优先级,可以加上一个括号便于优先级的理解 -- 同理我们也可以加上括号手动修改执行顺序下面就是先执行or再执行and select name,chinese,english,math from exam_result where (chinese80 or math70) and english70;AND的优先级高于OR在同时使用时需要使用小括号()包裹优先执行的部分。where不允许定义别名这里给出两组对比这里代码的执行过程大概如下1.先遍历表2.取出表的每个行带入条件此时使用的是别名3.把符合条件的行筛选出来 4.根据select后面的表达式进行计算。范围查询a.between ............ and ..............-- 查询语文成绩在 [80, 90] 分的同学及语文成绩 SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90; -- 使用 AND 也可以实现 SELECT name, chinese FROM exam_result WHERE chinese 80 AND chinese 90;b. in-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩 SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99); -- 使用 OR 也可以实现 SELECT name, math FROM exam_result WHERE math 58 OR math 59 OR math 98 OR math 99;模糊查询LIKE%代表搭配任意个任意字符 _ 代表一个任意字符-- % 匹配任意多个包括 0 个字符 SELECT name FROM exam_result WHERE name LIKE 孙%;-- 匹配到孙悟空、孙权 -- _ 匹配严格的一个任意字符 SELECT name FROM exam_result WHERE name LIKE 孙_;-- 匹配到孙权NULL的查询-- 查询 qq_mail 已知的同学姓名 SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL; -- 查询 qq_mail 未知的同学姓名 SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;排序查询ORDER BY列名越在前优先级越高 排序查询也为临时表操作不影响服务器硬盘上存储的原始数据语法-- ASC 为升序从小到大 -- DESC 为降序从大到小 -- 默认为 ASC SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];注意a.没有order by 子句的查询返回的顺序是未定义的永远不要依赖这个顺序b.NULL的数据排序视为比任何值都小升序出现在最上面降序出现在最下面-- 查询同学姓名和 qq_mail按 qq_mail 排序显示 SELECT name, qq_mail FROM student ORDER BY qq_mail; SELECT name, qq_mail FROM student ORDER BY qq_mail DESC;c使用表达式及别名的排序-- 查询同学及总分由高到低 SELECT name, chinese english math FROM exam_result ORDER BY chinese english math DESC; SELECT name, chinese english math total FROM exam_result ORDER BY total DESC;d.可以对多个字段进行排序排序优先级随书写顺序。-- 查询同学各门成绩依次按 数学降序英语升序语文升序的方式显示 SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;2.3.7.分页查询LIMIT:语法注意这里的OFFSET指的是偏移量相当于数组下标-- 起始下标为 0 -- 从 0 开始筛选 n 条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n; -- 从 s 开始筛选 n 条结果 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n; -- 从 s 开始筛选 n 条结果比第二种用法更明确建议使用 SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;实列约定一页只有三个数据-- 第 1 页 SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 0; -- 第 2 页 SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 3; -- 第 3 页如果结果不足 3 个不会有影响 SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 6;3.修改UPDATE3.1语法这里修改的就是数据库硬盘上所存储的数据UPDATE table_name SET column expr [, column expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]3.2实例set在数据库中SET是一个多功能的关键字具体用法取决于数据库类型和使用场景主要用于设置变量、修改配置、更新数据等操作。-- 将孙悟空同学的数学成绩变更为 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;4.删除DELETE4.1.语法DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]4.2实例-- 删除孙悟空同学的考试成绩 DELETE FROM exam_result WHERE name 孙悟空; -- 删除整张表数据 -- 准备测试表 DROP TABLE IF EXISTS for_delete; CREATE TABLE for_delete ( id INT, name VARCHAR(20) ); -- 插入测试数据 INSERT INTO for_delete (name) VALUES (A), (B), (C); -- 删除整表数据 DELETE FROM for_delete;5.插入查询把查询的结果插入到另一个表中注意后面查询出来的表的列数与数据类型要和前面的表一一对应但列的名字不需要匹配-- 插入查询:把select查询出来的结果存放到前面的表中 insert into exam_result select *from exam_result2; select *from exam_result;如果类型或者数据类型不匹配就会出现下面的报错show databases; drop database java115; drop database java116; drop database java118; drop database java1182;-- drop database mysql; create database java118; create database if not exists java115;--判定是否重复 create database if not exists java115 charset utf8mb4;--指定字符集utf8mb4 drop database java115; use java118; show tables;--查看数据库中所有的表 create table student (id int,name varchar (20),age int,gender varchar (20)); desc student;--查看student表结构 create table student1 (student_id int,student_name varchar (20),student_age int,student_gender varchar (20)); desc student1; drop table student1; alter table student add class_id int after gender; alter table student modify class_id bigint;--更改数据类型 alter table student rename column class_id to classID; desc student; alter table student drop classID; drop table student; show tables; create table goods (id bigint,name varchar(50),unitprice decimal(12,2),costprice decimal(12,2), catagory varchar(20),provider varchar(50)); desc goods; create table customer(id bigint,name varchar(20),gender bool,phone_number varchar(20),email varchar(50),address varchar(255),createtime datetime); desc customer; create table purchase(order_id varchar(20),customer_id bigint,goods_id bigint,nums int,amount decimal(20,2),createtime datetime); desc purchase; create table student (id int,name varchar (20),age int,gender varchar (20)); insert into student values(1,张三,18,男); desc student; insert into student values(2,李四,20,男); insert into student(id,name)values(3,王五);--指定行插入 -- insertintostudent(name,age)values(勾八66) -- 注意指定行插入时必须从左到右按照顺序插入 insert into student values(5,田七,20,女), (6,勾八,20,女); -- 多条插入语句这样的操作客户端和服务器之间进行了一次网络通信如果把这条语句拆分成两条 那么就进行两次网络通信流程客户端——》服务器发送一整条sql语句 服务器——》客户端返回执行结果 一次请求一次相应一次网络交互 show variables like %character%; -- 查询当前数据库中包含character字符串的系统变量 -- select####################################################################################### select * from student; select name from student; select age from student; select gender from student; 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); create table exam_result2 (id int,name varchar(20),chinese decimal(3,1),math decimal(3,1) ,english decimal(3,1)); insert into exam_result2(id,name,chinese,math,english) values (11,唐三, 67, 98, 56), (22,孙悟, 87.5, 78, 77), (33,猪悟, 88, 98.5, 90), (44,曹孟, 82, 84, 67); drop table exam_result; select *from exam_result; select name,chinese10 from exam_result; select id,name,chinese10,math-5,english*1.5 from exam_result; select id,name,chinesemathenglish from exam_result; select name,chinesemathenglish as total from exam_result; -- select name,chinesemathenglish total from exam_result; as可以省略 select distinct math, chinese from exam_result; -- 这样的情况比较特殊不单独对任意一个列去重只有当两个数据的两个列都相同时才会被去重 select name,english from exam_result where english60; select name,english from exam_result where english60; select name,english from exam_result where english60; -- 成绩不等于60 select name,english,chinese from exam_result where english chinese; select name,chinesemathenglish from exam_result where chinesemathenglish 200; -- select name,chinesemathenglish as total -- from exam_result -- where total 200; -- Mysql中的子句不允许定义别名使用因为其执行顺序是先执行where进行筛选数据再执行select进行计算和起别名 select name,chinese,english from exam_result where chinese80 and english70; select name,chinese,english from exam_result where chinese80 or english70; select name,chinese,english,math from exam_result where chinese80 or (math70 and english70); -- 这条语句先执行后面and的语句 再执行or因为and的优先级高于or的优先级,可以加上一个括号便于优先级的理解 -- 同理我们也可以加上括号手动修改执行顺序下面就是先执行or再执行and select name,chinese,english,math from exam_result where (chinese80 or math70) and english70; select name,chinese from exam_result where chinese between 80 and 90; select name,math from exam_result where math in (78,79,98,99); select name,math from exam_result where math78 or math79 or math98 or math99; select *from exam_result where name like 孙%; select *from exam_result where name like 孙_; select name,chinese,math,english,chinesemathenglish as total from exam_result where math60 and chinese61 and english60 order by math,english,chinese,total; -- 排序顺序默认按列从上到下升序,先排math English Chinese total 依次进行 只能保证最后一个为升序后面无法保证 select name,chinese,math,english,chinesemathenglish as total from exam_result where math60 and chinese61 and english60 order by total asc; -- asc即ascend navicat默认升序排序 可以省略不写 select name,chinese as chinese_score from exam_result where chinese between 70 and 99 order by chinese desc; -- desc即descend 降序排序 select name,chinese,math,english,chinesemathenglish as total from exam_result where math80 order by math desc,chinese; -- 当有多个需要order by需要排序时desc永远根据紧挨着他最前面的顺序进行排序 -- 分页查询 select name,chinese from exam_result where chinese60 and chinese95 ; select name,chinese from exam_result where chinese60 and chinese95 order by chinese desc limit 3; -- limit 限制的是一次一页最多显示多少个数据 select name,chinese from exam_result where chinese60 and chinese95 order by chinese desc limit 3 offset 3; -- 此处的offset 是与limit搭配使用代表偏移量类似于数组下标表示偏移n个位置从第n1个位置开始表示 select name,chinese from exam_result where chinese60 and chinese95 order by chinese desc limit 3 offset 5; -- 查询总成绩前三的数据 select name,chinesemathenglish as total from exam_result where chinese is not null and english is not null and math is not null limit 3 ; -- update############################################################################ show databases; show tables; desc exam_result; select * from exam_result; update exam_result set math 80 ,chinese80 where name 孙悟空; update exam_result set math 60 ,chinese70 where name曹孟德; update exam_result set mathmath1 order by chinesemathchinese desc limit 3; update exam_result set chinesechinese2 order by chinese desc; delete from exam_result where chinese80; delete from exam_result; -- 插入查询:把select查询出来的结果存放到前面的表中 insert into exam_result select *from exam_result2; select *from exam_result;本章完。。。。下节介绍这些功能的进阶用法。。。。