mysql数据库之数据CRUD

准备表和数据 

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` varchar(5) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `math` int(11) DEFAULT NULL,
  `english` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '马云', '55', '男', '杭州', '66', '78');
INSERT INTO `student` VALUES ('2', '马化腾', '45', '女', '深圳', '98', '87');
INSERT INTO `student` VALUES ('3', '马景涛', '55', '男', '香港', '56', '77');
INSERT INTO `student` VALUES ('4', '柳岩', '20', '女', '湖南', '76', '65');
INSERT INTO `student` VALUES ('5', '柳青', '20', '男', '湖南', '86', null);
INSERT INTO `student` VALUES ('6', '刘德华', '57', '男', '香港', '99', '99');
INSERT INTO `student` VALUES ('7', '马德', '22', '女', '香港', '99', '99');
INSERT INTO `student` VALUES ('8', '德玛西亚', '18', '男', '南京', '56', '65');
INSERT INTO `student` VALUES ('9', '唐僧', '25', '男', '长安', '87', '78');
INSERT INTO `student` VALUES ('10', '孙悟空', '18', '男', '花果山', '100', '66');
INSERT INTO `student` VALUES ('11', '猪八戒', '22', '男', '高老庄', '58', '78');
INSERT INTO `student` VALUES ('12', '沙僧', '50', '男', '流沙河', '77', '88');
INSERT INTO `student` VALUES ('13', '白骨精', '22', '女', '白虎岭', '66', '66');
INSERT INTO `student` VALUES ('14', '蜘蛛精', '23', '女', '盘丝洞', '88', '88');

-- ----------------------------
-- Table structure for userinfo
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `sitename` varchar(64) DEFAULT NULL COMMENT '网站名称',
  `username` varchar(64) NOT NULL DEFAULT '' COMMENT '账号',
  `password` varchar(64) NOT NULL DEFAULT '' COMMENT '账号密码',
  `siteadress` varchar(255) DEFAULT '' COMMENT '网站地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES ('1', 'GitHub', 'GitHub', '123456', 'https://github.com');
INSERT INTO `userinfo` VALUES ('2', 'Coding', 'Coding', '234567', 'https://coding.net');
INSERT INTO `userinfo` VALUES ('3', 'CSDN', 'CSDN', '345678', 'https://www.csdn.net');

一、DML操作

1、添加数据

语法：INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT},...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

实例：insert into userinfo(id,sitename,username,password,siteadress) values(4,'glot','glot','123456','https://glot.io/');

2、修改数据

语法：UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

实例：update userinfo set sitename='Oschina',username='Oschina',password='123456',siteadress='https://www.oschina.net' where id=4;

3、删除数据

语法：DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]

实例：delete from userinfo where id = 4;

二、DQL操作

语法：

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [STRAIGHT_JOIN]

      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr, ...

    [INTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name']

    [FROM table_references

    [WHERE where_definition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_definition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC] , ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [FOR UPDATE | LOCK IN SHARE MODE]]

1、简单查询

• 查询所有行和列数据：select * from student;
• 查询指定列数据：select name,age from student;
• 使用别名：select name as 姓名,age as 年龄 from student;
• 去掉重复的记录：select distinct address from student;

2、条件查询

• 使用运算符：select * from student where math>80;（查询math分数大于80分的学生）
• 使用逻辑运算符：select * from student where age>35 and sex='男';（查询age大于35且性别为男的学生）
• in关键字：select * from student3 where id not in(1,3,5);（查询id不是1或3或5的学生）
• 范围查询：select * from student where english between 75 and 90;（查询english成绩大于等于75，且小于等于90的学生）
• 模糊查询：select * from student where name like '%德_';（查询姓名中包含'德'字且德字后面只有一个字符的学生）

3、排序查询

• 查询所有数据,使用年龄降序排序：select * from student order by age desc, math asc;

4、分组查询

• 查询年龄大于25岁的人，按性别分组，统计每组的人数，并只显示性别人数大于2的数据：

         select sex, count(*) from student3 where age > 25 group by sex having count(*) >2;

having与where的区别：

• 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
• where后面不可以使用聚合函数；
• having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
• having后面可以使用聚合函数。

5、分页查询

• 实例：select * from student3 limit 2,6;