MySQL学习必备SQL_DDL_DML_DQL

MySQL创建数据库需要自行创建，

数据库名称可以为【schoolDB】，字符集【utf-8】，排序规则【utf8_general_ci】。

1、学习表DDL

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createDate` datetime DEFAULT NULL,
  `userName` varchar(20) DEFAULT NULL,
  `pwd` varchar(11) DEFAULT NULL,
  `phone` varchar(36) DEFAULT NULL,
  `age` int(5) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `introduce` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、插入学生信息DML

INSERT INTO student VALUES(0,'2023-8-26 10:45:7','纳西妲','521','19868866036','500','神','智慧之神');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','胡桃','521','19868866036','45','神','往生堂堂主');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','钟离','521','19868866036','1000','神','岩王帝君');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','芙宁娜','521','19868866036','500','神','神的替身');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','可莉','521','19868866036','120','萝','火花骑士');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','雷电将军','521','19868866036','800','神','永恒');
INSERT INTO student VALUES(0,'2023-8-23 12:56:45','班尼特','521','19868866036','54','少','六命火神');

实际效果：

3、基础查询测试DQL

#基础查询
SELECT * FROM student;
#筛选列（匿名）与筛选数据行
SELECT userName '性别',age '年龄',sex '性别',introduce '简介'
FROM student
WHERE pwd IS NOT NULL;
#分页 limit.有两个重载函数常用，
#1、 一个参数为显示条数
#2、 一个参数为从那条开始查询（下标从0开始计算），零一参数为显示条数
SELECT * FROM student LIMIT 3,2;#从4（0-3）开始查询查2条。
#排序，order by根据数值类型进行排序会比较准确。
SELECT * FROM student ORDER BY age DESC LIMIT 3;
#去重语句distinct
SELECT DISTINCT sex '性别类型' FROM student;