准备表和数据
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;