【无标题】MySQL单表操作学习DDL DML DQL语句

 数据库名称可以为[schoolDB]，字符集[utf8]，排序规则[utf8 general 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(255) DEFAULT NULL COMMENT '性别',
  `introduce` varchar(255) DEFAULT NULL COMMENT '介绍',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

2. 插入数据--DML语句

insert into student values(0,'2024-3-26 10:10:10','塔姆','123456','12345678910',6,'斗士','一费福星')
insert into student values(0,'2024-3-26 10:10:10','安妮','123456','12345678910',6,'魔法师','二费福星')
insert into student values(0,'2024-3-26 10:10:10','诺手','123456','12345678910',6,'战神','三费福星')
insert into student values(0,'2024-3-26 10:10:10','卡特琳娜','123456','12345678910',6,'刺客','三废福星')
insert into student values(0,'2024-3-26 10:10:10','庄瑟妮','123456','12345678910',6,'重装战士','四费费福星')
insert into student values(0,'2024-3-26 10:10:10','娑娜','123456','12345678910',6,'新春使者','新春使者新春福星羁绊')
insert into student (userName,age,introduce) values ('盲僧',66,'一脚踢飞福正的吃鸡梦')
select*from student

 

3.基础查询--DQL 

# 基础查询
select * from student :
#非空筛选
select userName as姓 名,age 年龄,sex性别'introduce'介绍from student where pwd is not null;
#去重查询
select distinct sex'性别' from student;
#分页查询关键字limit有两个参数，只写1个代表查询条数
#第一个参数代表起始查询位置，第二个参数代表查询行数
#计算初始分页条数=(当前页-1)*显示条数
select * from student LIMIT 6,3;
#排序
select * from student ORDER BY age asc;