数据库名称可以为【schoolIBD】,字符集【utf8】,排序规则【utf8_general_ci】。
1、创建数据表——DDL语句
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`creadate` 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 NULL COMMENT '性别',
`introduce` varchar(255) DEFAULT NULL COMMENT '介绍',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
2、插入数据——DML语句
INSERT INTO student VALUES(0,'2023-3-26','大耳朵图图','123456','12345678',5,'男','快乐的小孩')
INSERT INTO student VALUES(0,'2023-3-26','蜡笔小新','23456','6789',5,'男','一个五岁的小孩')
INSERT INTO student VALUES(0,'2023-3-26','叶罗丽精灵梦','12345','345678',5,'女','魔法世界')
INSERT INTO student VALUES(0,'2023-3-26','甜心格格','12345','456',16,'女','流落在外的格格')
INSERT INTO student VALUES(0,'2023-3-26','小马宝莉','23456','2356',18,'女','关于友谊的故事')
INSERT INTO student VALUES(0,'2023-3-26','水果特工','23456','34567',20,'男','水果世界的故事')
3、基础语句——DDL语句
SELECT username as '用户名',age年龄,sex'性 别',introduce '简介' from student
where pwd is not null;
#去重查询 dis tin ct
select distinct sex '性别类型' from student;
#排序order by
select*from student order by username desc;
#分页查询-limit.是一个重载的函数,当只有1个参数的时候代表显示行数
#两个参数limit分页:参数1、从N行开始查询=(当前页-1)*每页条数
SELECT*FROM student limit 6,3;