MySql之增删改查(单表操作)

　　本文主要详细介绍一下MySql的增删改查,以下都是MySql必须要掌握的语法,所有操作都建立在单表上,未涉及到多表操作，多表操作请点击连接查看。
　　在进行”增删改查”的操作之前,先建立一个包含数据表student的数据库


建表

CREATE TABLE `student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `grade` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.增 – 添加数据

(1)语法:INSERT INTO 表名（字段名1，字段名2，…）VALUES（值1，值2，…）；
INSERT INTO student (id,name,grade) VALUES (1,'张三',90);
INSERT INTO student (name,grade) VALUES ('李四',90);                   //主键id是自增的列，可以不用写
INSERT INTO student (id,name,grade) VALUES (NULL,'王五',90);           //主键id是自增的列，可以写为NULL

(2)语法:INSERT INTO 表名 VALUES(值1，值2，…）；                          //若不指定字段名，则添加的值的顺序应和字段在表中的顺序完全一致。
INSERT INTO student VALUES (7,'王五',90);                              //主键id是自增的列，也不可以省略
INSERT INTO student VALUES (NULL,'王五',90);                           //主键id是自增的列，可以写为NULL

(3)语法:INSERT INTO 表名（字段名1，字段名2，…）VALUES（值1，值2，…）；      //为指定字段添加数据，即只向部分字段添加值，而其他字段的值为表定义时的默认值。
INSERT INTO student (id,name) VALUES (NULL,'王五');
INSERT INTO student (name) VALUES ('王五');

(4)语法:INSERT INTO 表名 SET 字段名1=值1[,字段名2=值2，…]；
INSERT INTO student SET id=16, name='王五', grade=72;
INSERT INTO student SET name='王五', grade=72;
INSERT INTO student SET id=NULL, name='王五', grade=72;

(5)语法:INSERT INTO 表名[(字段名1，字段名2，…)] VALUES （值1，值2，…），（值1，值2，…），…（值1，值2，…）；
INSERT INTO student VALUES (19,'张三',90),(20,'李四',90),(21,'王五',90);         //主键id是自增的列，也不可以省略
INSERT INTO student VALUES (NULL,'张三',90),(NULL,'李四',90),(NULL,'王五',90);   //主键id是自增的列，也不可以省略
INSERT INTO student (name,grade) VALUES ('张三',90),('李四',90),('王五',90);     //主键id是自增的列，前面加字段后面可以省略
INSERT INTO student (name) VALUES ('张三'),('李四'),('王五');

最常用：INSERT INTO student (id,name,grade) VALUES (NULL,'张三',90),(NULL,'李四',90),(NULL,'王五',90);

2.删 – 删除数据

(1)语法:DELETE FROM 表名 WHERE 条件表达式                                //删除部分数据
DELETE FROM student WHERE id =12;

(2)语法:DELETE FROM 表名                                                //删除全部数据
DELETE FROM student;

(3)语法:TRUNCTE [TABLE ] 表名                                           //删除全部数据
TRUNCATE TABLE student;

注意事项：
1.DELETE后面可以跟WHERE子句指定删除部分记录，TRUNCATE只能删除整个表的所有记录
2.使用TRUNCATE语句删除记录后，新添加的记录时，自动增长字段（如本文中student表中的 id 字段）会默认从1开始，
而使用DELETE删除记录后，新添加记录时，自动增长字段会从删除时该字段的的最大值加1开始计算（即原来的id最大为5，则会从6开始计算）。
所以如果是想彻底删除一个表的记录而且不会影响到重新添加记录，最好使用TRUNCATE来删除整个表的记录。

3.改 – 更新数据

(1)语法:UPDATE 表名 SET 字段名1=值1，[ ，字段名2=值2，…] [ WHERE 条件表达式 ];       //更新指定数据
UPDATE student SET `name`='三三',grade=100 WHERE id=1;

(2)语法:UPDATE 表名 SET 字段名1=值1，[ ，字段名2=值2，…] ;                          //更新全部数据
UPDATE student SET grade=100;

4.查 – 查询数据

4.1准备工作

1.建表

CREATE TABLE `student2` (
`id`  int(10) NOT NULL AUTO_INCREMENT ,
`name`  varchar(10) CHARACTER SET utf8mb4 NULL ,
`grade`  int(10) NULL ,
`gender`  varchar(10) CHARACTER SET utf8mb4 NULL ,
PRIMARY KEY (`id`)
)
;

2.导入数据

INSERT INTO student2(name,grade,gender) VALUES ('songjiang',40,'男'),('wuyong',100,'男'),
('qinming',90,'男'),('husanniang',88,'女'),('sunerniang',66,'女'),('wusong',86,'男'),('linchong',92,'男'),('yanqing',90,NULL);

4.2简单查询

(1)语法:SELECT 字段名1，字段名2，… FROM 表名;
SELECT id,name,grade,gender FROM student2;                  //根据字段进行查询

(2)语法:SELECT * FROM 表名；
SELECT * FROM student2;                                     //在SELECT语句中使用('*')通配符代替所有字段

在WHERE子句中可以使用如下关系运算符:

(3)语法:SELECT * | 字段名1，字段名2，… FROM 表名 WHERE 条件表达式;
SELECT id,name,grade,gender FROM student2 WHERE id<=4;      //带关系运算符的查询

(4)语法:SELECT * | 字段名1，字段名2，… FROM 表名 WHERE 字段名 [ NOT ]  IN （元素1，元素2，…）;
SELECT * FROM student2 WHERE id IN (1,2,3);
SELECT * FROM student2 WHERE id NOT IN (1,2,3);
注意：NOT IN 与 IN 相反，查询的是不在指定范围内的记录。

(5)语法:SELECT * | { 字段名1，字段名2，… } FROM  表名 WHERE 字段名 [ NOT ] BETWEEN  值1  AND  值2；
SELECT id,`name` FROM student2 WHERE id BETWEEN 2 AND 5;
SELECT id,`name` FROM student2 WHERE id NOT BETWEEN 2 AND 5;
注意：NOT BETWEEN AND 表示查询指定范围外的记录。

(6)语法:SELECT * | 字段名1，字段名2，… FROM 表名 WHERE 字段名 IS [ NOT ] NULL
SELECT * FROM student2 where gender IS NULL;
SELECT * FROM student2 where gender IS NOT NULL;
注意：
1.IS NOT NULL 关键字用来查询字段不为空值的记录。
2.数据库中空字符串("")与NULL是两种值，IS NULL只用来判断字段的值是否为空值(NULL)。

(7)语法:SELECT DISTINCT 字段名 FROM 表名；
SELECT DISTINCT gender FROM student2;
SELECT DISTINCT grade,gender FROM student2;
注意：
1.DISTINCT 关键字还可作用于多个字段，则只有多个字段的值都完全相同时才会被认作是重复记录。
2.DISTINCT 语句中select显示的字段只能是distinct指定的字段，其他字段是不可能出现的。(distinct只能返回它的目标字段，而无法返回其它字段)

(8)语法:SELECT * | 字段名1，字段名2，… FROM 表名 WHERE 字段名 [ NOT ] LIKE "匹配字符串";
1.百分号(%)通配符
说明:匹配任意长度的字符串,包括空字符串。例如,字符串"c%"匹配以字符c开始,任意长度的字符串,如"ct","cut","current"等；
字符串"c%g"表示以字符c开始，以g结尾的字符串；字符串"%y%"表示包含字符"y"的字符串,无论"y"在字符串的什么位置。
SELECT id,name FROM student2 WHERE name LIKE 's%';
SELECT id,name FROM student2 WHERE name LIKE 'w%g';
SELECT * FROM `student2` WHERE `name` NOT LIKE '%y%';
SELECT * FROM student2 WHERE gender LIKE '%\%%';
2.下划线(_)通配符
说明:下划线通配符只匹配单个字符,若要匹配多个字符,需要使用多个下划线通配符。例如,字符串"cu_"匹配以字符串"cu"开始，长度为3的字符,如"cut","cup"；
字符串"c__l"匹配在"c"和"l"之间包含两个字符的字符串,如"cool"。需要注意的是,连续的"_"之间不能有空格，例如"M_ _QL"只能匹配"My SQL",不能匹配"MySQL"。
SELECT * FROM `student2` WHERE `name` LIKE 'wu_ong';
SELECT * FROM student2 WHERE gender LIKE '\_';
注意：若要查询的字段值本来就含有"%"或者"_",则要用"\"进行转义,如要查询本身含有"%"的字符串,命令应改为"%\%%"。(只要是字段内容带有'_','%'查询时就应该加上转义字符'\')

(9)语法:SELECT * | 字段名1，字段名2，… FROM 表名 WHERE 条件表达式1 AND 条件表达式2 [ … AND 条件表达式 n ];
SELECT id,name,gender FROM student2 WHERE id<5 AND gender='女';

(10)语法:SELECT * | 字段名1，字段名2，… FROM 表名 WHERE 条件表达式1 OR 条件表达式2 [ … OR 条件表达式 n ];
SELECT id,name,gender FROM student2 WHERE id<3 OR gender='女';

(11)语法:OR 和 AND 一起使用的时候,AND的优先级高于OR,因此二者一起使用时,会先运算AND两边的表达式,再运算OR两边的表达式
SELECT * FROM student2 WHERE gender='女' OR gender='男' AND grade=100;

4.3聚合函数查询

　　聚合函数:

(1)语法:SELECT COUNT(*) FROM 表名                 //统计记录的条数
SELECT COUNT(*) FROM student2;                   //返回检索行的数目，不论其是否包含NULL值
SELECT COUNT(gender) FROM student2;              //返回SELECT语句检索到的行中非NULL值的数目(空字符串可以检索到)
SELECT COUNT(DISTINCT gender) FROM student2;     //统计不包括NULL值

(2)语法:SELECT SUM(字段名) FROM 表名；             //求出表中某个字段所有值的总和
SELECT SUM(grade) FROM student2;

(3)语法:SELECT AVG(字段名) FROM 表名；             //求出表中某个字段所有值的平均值
SELECT AVG(grade) FROM student2;

(4)语法:SELECT MAX(字段名) FROM 表名；             //求出表中某个字段所有值的最大值
SELECT MAX(grade) FROM student2;

(5)语法:SELECT MIN(字段名) FROM 表名；             //求出表中某个字段所有值的最小值
SELECT MIN(grade) FROM student2;

注意

在mysql中使用聚合函数的时候比如avg(t),t是表中一个类型int型的字段，可为null，有三行数据，三行中的数据分别为：10，null，20，请问最后的结果是什么？

结果：15(聚合函数都忽略null值)
所以平时表里存需要计算的数据尽量不要写成null，而是写成0，否则例如求平均数时就会少算一个人

4.4高级查询

(1)语法:SELECT 字段名1，字段名2，… FROM 表名 ORDER BY 字段名1 [ ASC | DESC ],字段名2 [ ASC | DESC ]…
SELECT * FROM student2 ORDER BY grade;                //默认升序排序
SELECT * FROM student2 ORDER BY grade ASC;            //升序排列
SELECT * FROM student2 ORDER BY grade DESC;           //降序排列
SELECT * FROM student2 ORDER BY grade ASC,id DESC;    //先按grade升序排，值相同的按id降序排
注意：排序字段中的NULL值视为最小值

(2)语法:SELECT 字段名1，字段名2，… FROM 表名 GROUP BY 字段名1，字段名2，… [ HAVING 条件表达式 ];
1.单独使用GROUP BY进行分组                 //单独使用GROUP BY关键字，查询的是每个分组中的一条记录
SELECT gender FROM student2 GROUP BY gender;    =======   SELECT DISTINCT gender FROM student2;
SELECT gender,grade FROM student2 GROUP BY gender,grade;  //要求gender和grade都相同才能为一组

2.GROUP BY和聚合函数一起使用               //GROUP BY和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等(顺序:先分组后聚合函数)。
SELECT COUNT(*),gender FROM student2 GROUP BY gender;
SELECT AVG(grade),gender FROM student2 GROUP BY gender;
SELECT COUNT(*),AVG(grade),gender FROM student2 GROUP BY gender;

3.GROUP BY和HAVING关键字一起使用
SELECT SUM(grade) as sugrade,gender FROM student2 GROUP BY gender HAVING sugrade<300;

(3)语法:SELECT 字段名2，字段名2，… FROM 表名 LIMIT [ OFFSET ,] 记录数
SELECT * FROM student2 LIMIT 5;
SELECT * FROM student2 LIMIT 2,3;
SELECT * FROM student2 ORDER BY grade DESC LIMIT 2,3;
注意:在此语法中，LIMIT后面可以跟两个参数，第一个参数"OFFSET"表示偏移量，如果偏移量为0，则从查询结果的第一条记录开始，
偏移量为1则从查询结果中的第二条记录开始，以此类推。OFFSET为可选值，默认值为0，第二个参数“记录数”表示指定返回查询记录的条数。

注意

SELECT * FROM student2 GROUP BY gender;   //该sql语句为错误的，当gender相同的有多个数据时，sql其它字段不是唯一的，不知道该展示哪一条数据好所以报错(该结论在mysql5.7以上版本成立)
在mysql5.7以下版本如果不能唯一确定的话，mysql会随机选一个值(当group by字段和select多字段是唯一的一个分组时，这样能提高效率)

where和having的区别

where和having可以使用的场景：
	
    1.可以用where和having的情况
    select price,name from goods where price > 100
    select price,name from goods having price > 100
    说明：having可用的前提是已经筛选出了price字段，在这种情况下和where的效果是等效的，But如果没有select price 就会报错！！因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

    2.只可以用where，不可以用having的情况
    select name from goods where price> 100
    select name from goods having price> 100 //报错！！！因为select没有筛选出price 字段，having不能用,而where是对表进行检索price。100

    3.只可以用having，不可以用where情况
    查询每种id 商品价格的平均值，获取平均价格大于100元的商品信息
    select id, avg(price) as agprice from goods group by id having agprice > 100
    select id, avg(price) as agprice from goods where agprice>100 group by id //报错！！因为from goods这表里面没有agprice这个字段

    4.一条sql中where having group by的顺序是 where  group by having否则报错
    select sum(score) from student where gender='boy' group by name having sum(score)>210;
    where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，也就是在分组之前过滤数据，条件中不能包含聚和函数，使用where条件限制特定的行。
    having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用having 条件过滤特定的组，也可以使用多个分组标准进行分组。

    5.where  group by having  分组前where  分组统计  分组后having

4.5为表和字段取别名

(1)语法:SELECT * FROM 表名 [ AS ] 别名；
SELECT * FROM student2 AS s WHERE s.gender='女';

(1)语法:SELECT 字段名 [ AS ] 别名 [ ,字段名 [AS] 别名，…]  FROM 表名 ；
SELECT name AS stu_name,gender AS stu_gender FROM student2;

示例

SELECT name AS stu_name,gender AS stu_gender FROM student2 AS a WHERE name='songjiang';   //正确
SELECT name AS stu_name,gender AS stu_gender FROM student2 HAVING stu_name='songjiang';   //正确
SELECT name AS stu_name,gender AS stu_gender FROM student2 AS a WHERE stu_name='songjiang';   //报错
SELECT name AS stu_name,gender AS stu_gender FROM student2 AS a WHERE a.stu_name='songjiang';  //报错
SELECT name AS stu_name,gender AS stu_gender FROM student2 AS a HAVING a.stu_name='songjiang'; //报错
SELECT name AS stu_name,gender AS stu_gender FROM student2 WHERE stu_name='songjiang';        //报错