【无标题】

MySQL学习必备SQL_DDL_DML_DQL

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,'男','一遇杨过误终身。');studentstudent

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;

4. 查询语句

# Like的使用用法，where后 先写查询 列 再写like，最后写匹配字符串
SELECT * FROM student WHERE userName LIKE  '杨_';
SELECT * FROM student WHERE userName LIKE  '杨%';
SELECT * FROM student WHERE introduce LIKE  '%大%';
# 模糊查询基本上所有后台管理系统都会有此功能
SELECT * FROM student WHERE pwd is not null;
# 范围查询 between and 是包含==的，既可以查询数值范围，也可以查询时间范围，用途广泛。
SELECT * FROM student WHERE age BETWEEN 22 and 30;
SELECT * FROM student WHERE createDate  BETWEEN '2024-02-21 00:00:00' and '2024-02-25 00:00:00';
# In 关键字的使用，不建议使用这个关键字，因为它不一定走索引，在百外表的数据中会很卡。
SELECT * FROM student WHERE userName in ('赵灵儿','杨过','龙姑娘');

5.聚合函数

# 聚合函数5个， 数量count(x),最大值max(x),最小值min(x),平均数avg(x),求和sum(x)
SELECT count(*) '所有成年人数量'FROM student WHERE age>=18;
SELECT max(age) '最大年龄',min(age)'最小年龄'FROM student;
SELECT avg(age) '平均年龄' FROM student;
SELECT sum(age) '总年龄' FROM student;

SELECT avg(age) 'avg出的总年龄',sum(age)/(select count(*) from student)'计算出的'from student;