MySQL学习必备SQL_DDL_DML+DQL

MySQL创建数据库需要自行创建，数据库名称可以为【schoolDb】，字符集【utf8】，【utf8_general_ci】

1.学生表

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

 2.插入学生信息

INSERT into student VALUES(0,'2024-02-25 10:00:00','武大郎','123','13065479999',15,'女','想cc金莲。');
INSERT into student VALUES(0,'2024-02-25 10:00:00','矮脚虎王英','123','13065479999',25,'女','想cc黑旋风。');
INSERT into student VALUES(0,'2024-02-25 10:00:00','宋江','123','13065479999',35,'男','想cc王婆。');
INSERT into student VALUES(0,'2024-02-25 10:00:00','鲁智深','123','13065479999',45,'男','想cc林冲豹子头。');
INSERT into student VALUES(0,'2024-02-25 10:00:00','武大郎','123','13065479999',55,'女','想cc金莲。');
INSERT into student  (userName,age,introduce) VALUES('赤发鬼美女',30,'就爱cc。')

 实际效果：

3.基础查询测试DQL 

SELECT * FROM student;
SELECT userName as 姓名,age'年龄',sex'性别',introduce'简介'FROM student
WHERE pwd is not null;
SELECT * FROM student LIMIT 3;2;
SELECT * FROM student ORDER BY age desc limit 3;
SELECT DISTINCT sex '性别类型' FROM student;