- 工信部备案号 滇ICP备05000110号-1
- 滇公安备案 滇53010302000111
- 增值电信业务经营许可证 B1.B2-20181647、滇B1.B2-20190004
- 云南互联网协会理事单位
- 安全联盟认证网站身份V标记
- 域名注册服务机构许可：滇D3-20230001
- 代理域名注册服务机构：新网数码
相关文章
国信办再关闭31家违规网站 云南公布第二批免费向社会提供信息技术服务企业名单 云南省2019年国家网络安全周在丽江启动，蓝队云获颁“最佳技术支持奖” 英特尔计划在中国打造物联网国际品牌 中国互联网企业赴美上市规模预计今年或减半
My SQL表及其增删改查
2025-01-07 17:48:39
170
如今数据库管理成为了众多领域不可或缺的关键技能。MySQL 作为一款广受欢迎的开源数据库管理系统，其操作的熟练掌握尤为重要。今天蓝队云将深入探讨 MySQL 中关于数据表及数据处理的核心操作。
一、MySQL创建数据表（CREATE TABLE语句）
在创建数据库之后，接下来就要在数据库中创建数据表。所谓创建数据表，指的是在已经创建的数据库中建立新表。
创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。
接下来我们介绍一下创建数据表的语法形式。
在 MySQL 中，可以使用 CREATE TABLE 语句创建表。其语法格式为：
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
CREATETABLE命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。
mysql> USE test_db;
Database changed
mysql> CREATE TABLE tb_emp1
-> ( -> id INT(11),
-> name VARCHAR(25),
-> deptId INT(11),
-> salary FLOAT
-> );
Query OK, 0 rows affected (0.37 sec)
语句执行后，便创建了一个名称为 tb_emp1 的数据表，使用 SHOW TABLES；语句查看数据表是否创建成功，如下所示。
mysql> SHOW TABLES;
+--------------------+
| Tables_in_test_db |
+--------------------+
| tb_emp1 |
+--------------------+
1 rows in set (0.00 sec)
MySQL 使用 ALTER TABLE 语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。
使用 ALTER TABLE 修改表 tb_emp1 的结构，在表的第一列添加一个 int 类型的字段 col1，输入的 SQL 语句和运行结果如下所示。
mysql> ALTER TABLE tb_emp1
-> ADD COLUMN col1 INT FIRST;
Query OK, 0 rows affected (0.94 sec)
在MySQL数据库中，对于不再需要的数据表，我们可以将其从数据库中删除。在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。
使用 DROP TABLE 语句可以删除一个或多个数据表。删除数据表 tb_emp1，输入的 SQL 语句和运行结果如下所示。
mysql> DROP TABLE tb_emp1;
Query OK, 0 rows affected (0.22 sec)
二、MySQL INSERT：插入数据（添加数据）
数据库与表创建成功以后，需要向数据库的表中插入数据。在 MySQL 中可以使用 INSERT 语句向数据库已有的表中插入一行或者多行元组数据。
INSERT 语句有两种语法形式，分别是 INSERT…VALUES 语句和 INSERT…SET 语句。
1) INSERT…VALUES语句
-- INSERT VALUES 的语法格式为：INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];
语法说明如下。
<表名>：指定被操作的表名。
<列名>：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可以省略，直接采用 INSERT<表名>VALUES(…) 即可。
VALUES 或 VALUE 子句：该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。
2) INSERT…SET语句
-- 语法格式为：INSERT INTO <表名>
SET <列名1> = <值1>,<列名2> = <值2>
在 tb_courses 表中插入一条新记录，course_id 值为 1，course_name 值为“Network”，course_grade 值为 3，info 值为“Computer Network”。
mysql> INSERT INTO tb_courses
-> (course_id,course_name,course_grade,course_info)
-> VALUES(1,'Network',3,'Computer Network');
Query OK, 1 rows affected (0.08 sec)
三、MySQL DELETE：删除数据
在 MySQL 中，可以使用 DELETE 语句来删除表的一行或者多行数据。
使用 DELETE 语句从单个表中删除数据，语法格式为：
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
语法说明如下：
<表名>：指定要删除数据的表名。
ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
注意：在不使用 WHERE 条件的时候，将删除所有数据。
在 tb_courses_new 表中，删除 course_id 为 4 的记录，输入的 SQL 语句和执行结果如下所示。
mysql> DELETE FROM tb_courses
-> WHERE course_id=4;
Query OK, 1 row affected (0.00 sec)
四、MySQL UPDATE：修改数据（更新数据）
在 MySQL 中，可以使用 UPDATE 语句来修改、更新一个或多个表的数据。
使用 UPDATE 语句修改单个表，语法格式为：
UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]
语法说明如下：
<表名>：用于指定要更新的表名称。
SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
LIMIT 子句：可选项。用于限定被修改的行数。
注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。
在 tb_courses_new 表中，更新所有行的 course_grade 字段值为 4，输入的 SQL 语句和执行结果如下所示。
mysql> UPDATE tb_courses_new
-> SET course_grade=4;
Query OK, 3 rows affected (0.11 sec)
Rows matched: 4 Changed: 3 Warnings: 0
五、MySQL SELECT：数据表查询语句
MySQL表单查询是指从一张表的数据中查询所需的数据，主要有查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果进行排序等。
从数据表中查询数据的基本语句为 SELECT 语句，基本格式如下：
SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]
查询 tb_students_info 表中的所有数据，输入的 SQL 语句和执行结果如下所示。
mysql> SELECT id,name,dept_id,age,sex,height,login_date
-> FROM tb_students_info;
以上，就是关于Mysql对于表创建和其增删改查的基本操作，但是在实际工作中，其实大多数的操作都是在进行查询，比如大家常听的SQL boy、SQL gril等，主要工作就是写SQL查询语句进行数据提数和分析。
下面，我们重点对查询，结合关键字和实际用途做一个更详细的总结。
数据库和表的查询
--显示所有数据库：
SHOW DATABASES;
--显示所有的表：
SHOW TABLES;
--查看表结构
DESC 表名;
-- 查看表的创建语句
SHOW CREATE TABLE 表名;
查询表
--查询表中的姓名和年龄SELECT 姓名,年龄 FROM 表名 ;
--查询表中id乘50和姓名的结果
SELECT id*50,姓名 FROM 表名;
--给乘50的列名加个注释：as "字符串"，可以把as去掉也能显示注释名
SELECT id*50 as "乘50",姓名 FROM 表名;
查询去重 DISTINCT
distinct：作用范围是后面所有字段的组合（可以有多个字段）
-- 查询时去除重复项（表名表中列名，内容去重）
SELECT DISTINCT 列名，列名 FROM 表名;
查询排序 ORDER BY
SELECT DISTINCT 年龄,姓名 FROM 表名 ORDER BY 年龄;
--按年龄排序，展示年龄姓名两列数据
SELECT DISTINCT 年龄,姓名 FROM 表名 ORDER BY 年龄,姓名;
--展示年龄和姓名的前提下，按年龄理相同，姓名不同的排序（默认从低到高）
SELECT DISTINCT 年龄,姓名 FROM 表名 ORDER BY 年龄,姓名 DESC;
--展示年龄和姓名的前提下，按年龄理相同，姓名不同的排序（DESC是倒叙）
SELECT DISTINCT 年龄,姓名 FROM 表名 ORDER BY 年龄,姓名 ASC;
--排列顺序从低到高（默认）`（ASC是正序，默认就是正序，一般不用）`
查询限定 WHERE（<= , >=） / LIKE
SELECT 年龄,姓名 FROM 表名 WHERE 年龄>20;
-- 查出所有年龄超过20岁的人
SELECT 年龄,姓名 FROM 表名 WHERE 年龄>20 AND 姓名="张三";
--查出某个国家所有年龄超过20且叫张三的人（注意：后边是汉字的要加双引号）
SELECT 年龄,姓名 FROM 表名 WHERE 年龄=21 OR 年龄=67;
--查出年龄为21或者67的人
模糊查询 like：
--查询表中叫王某的人有谁SELECT 姓名 FROM 表名 WHERE 姓名 LIKE "王%";
-- WHERE 子句中可以使用等号 = 来设定获取数据的条件， country=中国’但是有时候我们需要获取含有某个字符的所有记录，这时我们就需要在 WHERE 子句中使用 LIKE 子句
SELECT 年龄,姓名 FROM 表名 WHERE age>20 AND 姓名 LIKE ‘王%';
SELECT 年龄,姓名 FROM 表名 WHERE age>20 AND 姓名 LIKE ‘%四';
-- SQL LIKE 子句中使用百分号 %字符来表示任意字符，如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的
分页查询
在查询时可以只检索前几条或者中间某几行数据（数据量很大时）
SELECT * FROM t_user LIMIT 0,3;
--（从第一行开始，查询3条数据）
limit 后面的第一个数字设置从哪里开始检索（从0开始）
limit 后面的第二个数字是设置每页显示多少条(3,4代表从第四行查询三条数据)
聚合函数
统计总数 count()
为了快速得到统计数据（多条数据的统计结果），提供了5个聚合函数
count()：查询表中某项数据一共包含多少条（统计总数）
--查询表中共包含多少条数据（会检索所有列）
SELECT COUNT(*) FROM 表名;
--查询表中一共有多少个人且年龄大于20的（只会检索一列）
SELECT COUNT(年龄) FROM renshi WHERE 年龄>20;
推荐使用第二种写法，可以提高效率。
统计一般计算的值
--`max(列)`：求此列的最大值
SELECT MAX(age) FROM 表名 ;
--`min(列)`：求此列的最小值
SELECT MIN(age) FROM 表名 ;
--`sum(列)`：求此列的和
SELECT SUM(age) FROM 表名 ;
--`avg(列)`：求此列的平均值
SELECT AVG(age) FROM 表名 ;
--查出总人数、最大年龄、最小年龄、年龄的总和、平均年龄
SELECT COUNT(姓名),MAX(年龄),MIN(年龄),SUM(年龄),AVG(年龄) FROM 表名 ;
分组：group by
将表中数据分成若干小组，例如分为男人和女人，不同国籍等等。
-- 查看每个国家有多少人
SELECT COUNT(*) FROM 表名 GROUP BY 国家;
--(先根据group by查询国家，再查询有多少人)
--查看每个国家的平均年龄
SELECT AVG(年龄) FROM 表名 GROUP BY 国家;
--查看每个国家的总人数，年龄总和，平均年龄，最高年龄，最低年龄
SELECT country,COUNT(uname),SUM(age),AVG(age),MAX(age),MIN(age) FROM t_user GROUP BY country;--查看每个国家的总人数，年龄总和，平均年龄，最高年龄，最低年龄，但是排除某个国家
SELECT 国家,COUNT(姓名),SUM(age),AVG(age),MAX(age),MIN(age) FROM表名 WHERE country!='吴国' GROUP BY country;--列出每个国家小于20岁的人
SELECT 国家,名字 FROM 表名 WHERE age<20 GROUP BY 国家;
过滤：having
SELECT 列名 FROM 表名 WHERE 过滤条件。使用where，可以用来过滤单行，如果想要过滤分组之后的数据，要加having。
-- 显示每个国家的平均年龄，但是仅显示那些总年龄超过100的国家，having在聚合后对组记录进行筛选
SELECT country,AVG(age) FROM 表名 GROUP BY country HAVING SUM(age)>100;
--注意顺序：having放在分组之后，因为作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组
分组查询和过滤：
having 和 where：
WHERE 在分组和聚合计算之前进行选取（它控制哪些行进入聚合计算），
因此，WHERE 子句不能包含聚合函数。
HAVING 在分组和聚合之后选取分组的行，而且HAVING 子句总是包含聚集函数。（严格来讲，你可以写不使用聚集的 HAVING 子句， 但同样的条件用WHERE更有效）
综上所述：
having要跟在group by（分组）之后，对分组查询的结果进行过滤（过滤分组），where要出现在group by（分组）之前，执行表中所有数据来进行过滤（过滤行），另外，having可以用聚合函数，并支持所有where子句操作符。
where子句操作符：
<,>,=,!=,>=,<=,and,not,or,like
其中 <,>,=,!=,>=,<=属于比较运算符，and，not，or属于逻辑运算符。
--查询年龄大于十岁小于三十的人名
SELECT 姓名 FROM 表名 WHERE 年龄>10 AND 年龄<30;
空值检测：IS NULL
--返回id不为空的行，`IS NULL` 为空值判断
SELECT * FROM 表名 WHERE id IS NOT NULL
子查询
最后再说一个平时用的比较多的查询语句，大家可以当作练习。
先建表：
CREATE TABLE stu( --先建立一个stu的表
stuID INT, --添加id为int类型
sname VARCHAR(32), --名字是varchar类型
score INT, --分数是int类型
PRIMARY KEY(stuID) --把id设置为主键
);
添加几条数据：
INSERT INTO stu VALUES (1,‘xiaoming’,60), (2,‘xiaoli’,70);
如何查询所有比小明成绩高的学生名字，先查询名字是xiaoming这个人的成绩，然后，再根据stu表的其他人的分数去做对比：
SELECT sname FROM stu WHERE score > (SELECT score FROM stu WHERE sname = 'xiaoming');
查询成绩高于平均成绩的学生姓名和成绩：
SELECT sname,score FROM stu WHERE score > (SELECT AVG(score) FROM stu);
好了，以上就是本文的所以内容，SQL其实不难，接下来要多练习，熟能生巧。
想了解更多内容欢迎上蓝队云官网查询，也可以直接咨询专业客服。
- 上一篇：短信群发平台哪里有
- 下一篇：Linux教程：深入理解Awk命令
