MySQL学习必备SQL_DDL_DML_DQL

1.MySQL数据库创建（学生表）

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdate` datetime DEFAULT NULL,
  `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `pwd` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `phone` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `introduce` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

编码格式为utf8

2.插入学生数据

INSERT into student VALUES(0,'2024-02-25','香菱','123','15614333260',18,'女','万民堂大厨');
INSERT into student VALUES(0,'2024-02-25','凝光','123','15614333260',26,'女','璃月七星天权星');
INSERT into student VALUES(0,'2024-02-25','刻晴','123','15614333260',23,'女','璃月七星玉衡星');
INSERT into student VALUES(0,'2024-02-25','北斗','123','15614333260',26,'女','大船长');
INSERT into student VALUES(0,'2024-02-25','胡桃','123','15614333260',18,'女','往生堂第77代堂主');
SELECT * FROM student;

实际效果

3.基础查询设置DQL

SELECT * FROM student;
#筛选列与筛选数据行
select username as 姓名,age '年龄',sex'性别',introduce'简介'
from student 
where pwd is not null;
#分页limit,有两个重载函数常用
#1.一个参数为显示函数
#2.一个参数为从那条开始查询（下标从0开始计算），0一参数为显示条数
SELECT * from student LIMIT 3,2;# 从4（0-3）开始查询两条
#排序，order by 根据数值类型进行排序会比较准确
SELECT * FROM student order by age desc limit 3;
#去重语句distinct
SELECT distinct sex '性别类型'from student;