前言
数据库名称可以为【schoolDB】,字符集【utf8】,排序规则【utf8-gengeral-ci】。
1、建表语句——DDL
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`createDate` datetime DEFAULT NULL COMMENT '创建时间',
`userName` varchar(20) DEFAULT NULL COMMENT '姓名',
`pwd` varchar(36) DEFAULT NULL COMMENT '密码',
`phone` varchar(11) DEFAULT NULL COMMENT '手机号',
`age` tinyint(3) DEFAULT NULL COMMENT '年龄',
`sex` char(2) DEFAULT '男' COMMENT '性别',
`introduce` varchar(255) DEFAULT NULL COMMENT '个人简介',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
2、插入语句——DML
INSERT INTO student VALUES(0, '2024-10-09 10:00:00','周星驰','123456',
'15666666666',62,'男','星爷');
INSERT INTO student VALUES(0,'2022-09-20 09:00:00','成龙','134567',
'13266666666',25,'男','龙叔');
INSERT INTO student VALUES(0,'2023-08-06 07:00:00','洪金宝','123457',
'13299999999',34,'男','武打大哥');
INSERT INTO student VALUES(0,'2023-09-06 06:00:00','刘德华','123857',
'13299990099',49,'男','给我一杯我一杯忘情水');
INSERT INTO student VALUES(0,'2023-03-06 01:00:00','郭富城','323457',
'13288888888',47,'男','摇头天王');
INSERT INTO student VALUES(0,'2021-08-06 07:00:00','黎明','923457',
'13266666666',49,'男','帅气天王');
INSERT INTO student VALUES(0,'2024-08-06 08:00:00','张学友','103457',
'13299999019',47,'男','歌神');
3、查询语句
#1、基础查询
SELECT * FROM student;
#2、分列匿名以及筛选数据查询
SELECT userName as '姓名' ,age 年龄,sex '性别',introduce'个人简介'
FROM student;
WHERE pwd is not null;
#3、去重查询
SELECT DISTINCT sex'性别类型' FROM student;
#4、排序查询
SELECT userName as '姓名',age 年龄,sex '性别',introduce '简介' FROM student ORDER BY age desc;
SELECT*FROM student LIMIT 2;
SELECT*FROM student LIMIT 4,2