MySQL学习必备SQL_DDL_DML_DQL

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

数据库名称可以为【schoolDB】,字符集【utf8】,排序规则【utf8_general_ci】,

1.学生表DDL

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.插入学生信息DML

INSERT into student values (0,'2024-02-25 10:00:00','赵灵儿','123','15612345678',16,'女','逍遥哥哥，你终于找到我了。')

INSERT into student values (0,'2024-02-25 10:00:00','王语嫣','123','15612345678',17,'女','慕容复，我跟你不共戴天。')

INSERT into student values (0,'2024-02-25 10:00:00','龙姑娘','123','15612345678',22,'女','我想过过过过过过的生活。')

INSERT into student values (0,'2024-02-25 10:00:00','杨过','123','15612345678',18,'男','一遇杨过误终身。')

INSERT into student values (0,'2024-02-25 10:00:00','杨逍','123','15612345678',27,'男','杨过跟程英的大儿子。')

INSERT into student(userName,age,introduce) values ('黄衣女子',26,'杨过与龙姑娘的大女儿。')

实际效果:

3.基础查询测试DQL

# 基础查询
select * from student
#筛选列(匿名)与筛选数据行
select userName as 姓名,age'年龄' ,sex '性别',introduce '简介'
from student
where pwd is not null;
#分页 limit,有两个重载函数常用，
#1.一个参数为显示条数
#2.一个参数为从那条开始查询(小标从0开始计算)，零一参数为显示条数
select * from  student limit 3,2;# 从4(0-3)开始查询查2条。
select * from  student order by age desc limit 3;
# 去重语句distinct
select distinct sex '性别类型' from student;