Mysql表及其增删改查
关注我，每天学习一点点，每天进步一点点！
一、MySQL创建数据表（CREATE TABLE语句）
在创建数据库之后，接下来就要在数据库中创建数据表。所谓创建数据表，指的是在已经创建的数据库中建立新表。
创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。
接下来我们介绍一下创建数据表的语法形式。
在 MySQL 中，可以使用 CREATE TABLE 语句创建表。其语法格式为：
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
CREATETABLE命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。
USE test_db;Database changedCREATE 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 语句和运行结果如下所示。
ALTER TABLE tb_emp1ADD COLUMN col1 INT FIRST;Query OK, 0 rows affected (0.94 sec)
在MySQL数据库中，对于不再需要的数据表，我们可以将其从数据库中删除。在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。
使用 DROP TABLE 语句可以删除一个或多个数据表。删除数据表 tb_emp1，输入的 SQL 语句和运行结果如下所示。
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 语句和执行结果如下所示。
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 语句和执行结果如下所示。
UPDATE tb_courses_newSET 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 语句和执行结果如下所示。
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其实不难，接下来要多练习，熟能生巧。
多唠叨一句，对于计算机来说，SQL已经被公认为最具有语义化的高级编程语言了。
回想计算机的发展史，从二进制的01代码开始、经历了纸带打孔、汇编语言、C语言面向过程，C++和JAVA等面向对象的高级编程语言，只有SQL最语义化，写起来跟人类的自然语言较接近。
声明:【原创文章，若要转载，请联系作者，谢谢！】