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) unsigned DEFAULT NULL COMMENT '年龄',
  `sex` char(2) DEFAULT '男' COMMENT '性别',
  `introduce` varchar(255) DEFAULT NULL COMMENT '介绍',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

2.插入数据———DML语句

insert into student values(
0,'2024-02-25 10:10:10','马牛','123456','15612345678',20,'女人','团长'
);
insert into student values(
0,'2024-02-25 10:10:10','丁成','123457','15612345698',19,'女人','队员'
);
insert into student values(
0,'2024-02-25 10:10:10','宋亚','123466','15612349678',20,'女人','队员'
);
insert into student values(
0,'2024-02-25 10:10:10','张员','129456','15662345678',21,'女人','队员'
);
insert into student values(
0,'2024-02-25 10:10:10','严翔','183456','15612345678',22,'女人','队员'
);
insert into student values(
0,'2024-02-25 10:10:10','贺军','123459','15612344678',24,'女人','队员'
);
insert into student values(
0,'2024-02-25 10:10:10','刘为','123456','15612345678',23,'女人','队员'
);
insert into student (userName, age ,introduce) values('悠悠球琛总',30,'职业爱坤');
select * from student;

1.创建数据表———DDL语句

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