mysql语法使用总结

待我称王封你为后i 2023-09-29 17:17 52阅读 0赞

#### 文章目录 ####

*  一、Other
 *   *  1.1 简单的创表语句
     *  1.2 innoDB和MyIsam引擎的比较
     *  1.3 添加物理外键约束
     *  1.4 DML
 *  二、DQL
 *   *  2.1 简单的查询
     *  2.2 联表查询
     *  2.3 自连接
     *  2.4 分页和排序
     *  2.5 子查询和嵌套查询
 *  三、常用函数
 *   *  3.1 数学运算
     *  3.2 字符串
     *  3.3 时间和日期函数
     *  3.4 查看系统和用户
     *  3.5 聚合和分组
     *  3.6 md5加密

## 一、Other ##

### 1.1 简单的创表语句 ###

**创建一个student表。**

> 创建一个school数据库  
> 创建学生表，使用SQL创建  
> 学号int 非空 登录密码varchar(20)默认123456 姓名，性别，出生日期(datetime)，家庭住址，email

CREATE DATABASE IF NOT EXISTS school;
    USE school;
    CREATE TABLE IF NOT EXISTS `student`(
    	`id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '学号',
    	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    	`name` VARCHAR(10) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    	`address` VARCHAR(50) DEFAULT NULL COMMENT '家庭住址',
    	`email` VARCHAR(30) DEFAULT NULL COMMENT '邮箱',
    	PRIMARY KEY(`id`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;

### 1.2 innoDB和MyIsam引擎的比较 ###

*  innoDB支持事务，支持外键，支持行级锁定
 *  MyIsam占用空间小，支持全文检索

### 1.3 添加物理外键约束 ###

USE school;
    CREATE TABLE IF NOT EXISTS `student1`(
    	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级',
    	`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    	`gradeId` INT(10) NOT NULL COMMENT '学生年级',
    	PRIMARY KEY(`id`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    
    CREATE TABLE IF NOT EXISTS `grade`(
    	`gradeId` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级ID',
    	`gradeName` VARCHAR(20) NOT NULL COMMENT '年级名称',
    	PRIMARY KEY(`gradeId`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    
    -- 创建了两个表，student1和grade，现在需要用student1来创建一个外键约束引用到grade表
    -- 注意这里创建的是物理外键，正常情况下使用不多。因为要删除被引用的表就需要先删除引用他的表
    -- 这里就不能直接删除grade表，必须删除student1表后才能删除grade表。
    -- 原则：数据库就是存储数据，单纯的行列。需要这种外键操作时可以通过程序来实现。
    ALTER TABLE `student1`
    ADD CONSTRAINT `FK_gradeId` FOREIGN KEY(`gradeId`) REFERENCES `grade`(`gradeId`);

下面是直接删除grade表，会报错  
![039873e4ca4840e4b8701252b9ff2675.png][]

### 1.4 DML ###

数据库管理语言：包括 insert   delete from   update

-- DML
    DESC student;
    -- 一次插两条记录
    INSERT INTO `student`(`id`,`name`,`birthday`)
    VALUES(1,'弱智','2000-01-01 12:00:01'),(3,'王华子','2000-08-13 12:00:13');
    
    -- 不写字段，直接插入值，但是值要和字段一一对应
    INSERT INTO `student`
    VALUES(4,'abcdefg','王凯子','1999-09-20','湖南槟榔村','shuaige@qq.com');
    
    -- 修改数据
    UPDATE `student` SET `name`='亿羊干洗',`birthday`=CURRENT_TIME WHERE `id`=1;	/*可使用变量 current_time */
    -- where条件判断 还有 betweent...and.. AND, OR这些
    
    -- 删除数据
    DELETE FROM `student` WHERE `name`='王华子';
    -- truncate和delete都可以清空表数据，但是有一些区别：
    /* 1、truncate清空表数据会重置自增点；2、truncate不影响 事务 */

## 二、DQL ##

**首先准备四张表。**

-- DQL
    -- 首先准备四张表。
    CREATE DATABASE IF NOT EXISTS test;
    USE test;
    CREATE TABLE IF NOT EXISTS `grade`(
    	`gradeId` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
    	`gradeName` VARCHAR(20) NOT NULL COMMENT '年级名称',
    	PRIMARY KEY(`gradeId`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    /* insert data into table `grade` */
    INSERT INTO `grade`(`gradeName`)
    VALUES('大一'),('大二'),('大三'),('大四'),('预科班');
    
    CREATE TABLE IF NOT EXISTS `result`(
    	`studentNu` INT(4) NOT NULL COMMENT '学号',
    	`subjectNu` INT(4) NOT NULL COMMENT '课程编号',
    	`examDate` DATETIME NOT NULL COMMENT '考试日期',
    	`score` INT(4) NOT NULL COMMENT '考试成绩',
    	KEY `FR_subjectNu`(`subjectNu`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    /* insert data into table `result` */
    INSERT INTO `result`(`studentNu`,`subjectNu`,`examDate`,`score`) VALUES(1000,1,'2022-06-13 14:00:00',88),(1000,2,'2022-06-14 08:30:00',78),(1001,3,'2022-06-15 14:00:00',68);
    
    CREATE TABLE IF NOT EXISTS `student`(
    	`studentNu` INT(4) NOT NULL COMMENT '学号',
    	`loginPwd` VARCHAR(20) DEFAULT NULL,
    	`studentName` VARCHAR(20) NOT NULL COMMENT '学生姓名',
    	`sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    	`gradeId` INT(11) DEFAULT NULL COMMENT '年级编号',
    	`phone` VARCHAR(11) NOT NULL COMMENT '联系电话',
    	`address` VARCHAR(50) COMMENT '地址',
    	`borndate` DATETIME DEFAULT NULL COMMENT '出生日期',
    	`email` VARCHAR(20) NOT NULL COMMENT '邮箱',
    	`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    	PRIMARY KEY(`studentNu`),
    	UNIQUE KEY `idCard`(`idCard`),
    	KEY `email`(`email`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    INSERT INTO `student`
    VALUES(1000,'12345678','乌鸦哥',1,4,'13813457896','铜锣湾','1980-11-20 02:00:00','53252279@qq.com','411523198011200832'),(1001,'12345678','乌蝇哥',1,3,'15913457896','香港','1970-11-20 12:00:00','53752279@qq.com','不详');
    
    CREATE TABLE IF NOT EXISTS `subject`(
    	`subjectNu` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    	`subjectName` VARCHAR(20) DEFAULT NULL COMMENT '课程名称',
    	`classHour` INT(4) DEFAULT NULL COMMENT '课时',
    	`gradeId` INT(4) DEFAULT NULL COMMENT '年级编号',
    	PRIMARY KEY(`subjectNu`)
    )ENGINE=INNODB DEFAULT CHARSET=utf8;
    INSERT INTO `subject`(`subjectNu`,`subjectName`,`classHour`,`gradeId`)
    VALUES(1,'高等数学',100,1),(2,'大学英语',90,1),(3,'C语言',48,1),(4,'计算机网络',36,2);

### 2.1 简单的查询 ###

-- DQL
    SELECT * FROM `result`;
    SELECT * FROM `student`;
    SELECT `studentName` AS `姓名`, `loginPwd` AS `密码` FROM `student`;
    SELECT CONCAT('密码：', `loginPwd`) AS `New name` FROM `student`;

select version();		//查询版本
    select @@auto_increment_increment;		//查询自增
    select 100*3+1 as comp_result;			//用作计算
    select distinct `studentNu` from `result`;		//distinct去重
    
    -- =============模糊查询=============
    is NULL
    is not NULL
    between AND
    LIKE
    IN
    
    -- 查询姓刘的同学
    select `studentName` from `student` where studentName like '刘%';		// %匹配任意个字符
    
    -- 查询两个字的姓刘的同学
    select `studentName` from `student` where studentName like '刘_';	// _匹配一个字符
    
    -- 查询地址在安徽，洛阳，江苏的学生
    select `studentName`, `studentNu` from `student` where address in ('安徽','洛阳','江苏');

### 2.2 联表查询 ###

left join, right join, inner join的区别

![在这里插入图片描述][240d3c6477504bc8811f8913a420e327.png]

-- 联表查询
    /* 思路
    1. 分析需求，分析查询的字段来自哪些表
    2. 确定使用那种连接查询？确定交叉点
    */
    
    ALTER TABLE `result`
    MODIFY COLUMN `score` INT(4);
    
    -- 查询学生的成绩
    SELECT s.studentNu,studentName,subjectNu,score
    FROM student AS s
    INNER JOIN result AS r
    ON s.studentNu = r.studentNu;
    
    SELECT s.studentNu,studentName,subjectNu,score
    FROM student AS s
    LEFT JOIN result AS r
    ON s.studentNu = r.studentNu;
    
    SELECT s.studentNu,studentName,subjectNu,score
    FROM student AS s
    RIGHT JOIN result AS r
    ON s.studentNu = r.studentNu;
    
    -- 查询缺考的同学
    -- 即成绩为NULL的学生
    SELECT s.studentNu,studentName,subjectNu,score
    FROM student s 
    LEFT JOIN result r
    ON s.studentNu = r.studentNu
    WHERE score IS NULL;
    
    -- 三张表查询
    /*  查询从参加考试的学生的信息：学号，姓名，科目，分数
    	查询三张表：student, result, subject
    */
    -- 先连接两张表，然后再连接第三张表
    SELECT s.studentNu,studentName,subjectName,score
    FROM student s
    RIGHT JOIN result r
    ON r.studentNu = s.studentNu
    INNER JOIN SUBJECT sub
    ON r.subjectNu = sub.subjectNu;

### 2.3 自连接 ###

*  连接的是同一张表

-- 自连接
    -- 连接的是同一张表
    CREATE TABLE `category`(
    	`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题ID',
    	`pid` INT(10) NOT NULL COMMENT '父ID',
    	`categoryName` VARCHAR(20) NOT NULL COMMENT '主题名',
    	PRIMARY KEY (`categoryid`)
    )ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
    
    INSERT INTO `category`(`categoryid`,`pid`,`categoryName`)
    VALUES(2,1,'信息技术'),
    	(3,1,'软件开发'),
    	(4,3,'数据库'),
    	(5,1,'美术设计'),
    	(6,3,'Web开发'),
    	(7,5,'PS技术'),
    	(8,2,'办公信息');
    
    SELECT a.categoryName AS 父栏目, b.categoryname AS 子栏目
    FROM `category` AS a
    INNER JOIN `category` AS b
    ON a.categoryid = b.pid;

### 2.4 分页和排序 ###

*  **limit, order by, desc, asc** 几个关键字

-- 分页和排序	limit, order by
    -- asc:升序 desc:降序
    select s.`studentNu`,`studentName`,`score` from student s
    inner join `result` r
    on s.studentNu = r.studentNu
    order by score DESC
    limit 1,2;

### 2.5 子查询和嵌套查询 ###

`这里举个查询的例子来演示`

*  查询C语言成绩大于60分的学生学号和姓名，

-- 查询C语言成绩大于60分的学生学号和姓名，有三种方式都可以实现：联表查询，子查询，嵌套查询
    
    -- 1、普通的联表查询
    select s.`studentNu`,`studentName`
    from `student` s
    inner join `result` r
    on s.studentNu = r.studentNu
    inner join `subject` sub
    on r.subjectNu = sub.subjectNu
    where subjectName = 'C语言' and score >= 60;
    
    -- 2、子查询
    select s.`studentNu`,`studentName`
    from `student` s
    inner join `result` r 
    on s.studentNu = r.studentNu
    where score >= 60 and `subjectNu` = (
    	select subjectNu from subject
    	where subjectName = 'C语言'
    );
    
    -- 3、嵌套查询
    select `studentNu`,`studentName`
    from `student`
    where studentNu in (
    	select studentNu from result
    	where score >= 60 And subjectNu = (
    		select subjectNu from subject
    		where subjectName = 'C语言'
    	)
    );

## 三、常用函数 ##

### 3.1 数学运算 ###

*  包含求绝对值(**abs**)，取整(**ceiling** || **floor**)，随机数(**rand**) 这些常用的

-- 数学运算
    select abs(-8)	-- 绝对值
    select ceiling(9.4)	-- 向上取整
    select floor(9.4)	-- 向下取整
    select rand()	-- 生成0~1之间的随机数
    select sign(10)	-- 判断是正数(1)还是负数(-1)，或者0（0）

### 3.2 字符串 ###

*  **char\_length, concat, lower, upper, instr, substr, reverse** 等常用函数

-- 字符串
    select char_length('你个老逼登') -- 字符串长度5
    select concat('你','是','鲨臂')	-- 拼接
    select lower('WoDiaoNiMaDe')	-- 小写
    select upper('WoDiaoNiMaDe')	-- 大写
    select instr('WoDiaoNiMaDe', 'Ma')	-- 返回第一次出现的子串索引
    select replace('你个老逼登','逼登','可爱')	-- 替换
    select substr('他们说坚持就能成功',4,6)	-- 返回指定长度的字符串
    select reverse('我叼你妈的')	-- 反转

*  示例：查询姓肖的同学，并将肖改为萧

select replace(studentName,'肖','萧') from student
    where studentName like '肖%';

### 3.3 时间和日期函数 ###

*  返回当前时间(**now**)，时间戳(**unix\_timestamp**)，**年月日**等

-- 时间和日期函数
    select current_date()
    select curdate()
    
    select unix_timestamp(now())	-- 返回时间戳
    select from_unixtime(1666425897)	-- 时间戳转换为时间
    select now()
    select localtime()
    select sysdate()
    
    select year(now())
    select month(now())
    select day(now())
    select hour(now())
    select minute(now())
    select second(now())

### 3.4 查看系统和用户 ###

-- 系统
    select system_user()
    select user()
    select version()

### 3.5 聚合和分组 ###

常用的聚合函数包含这几种：

*  **count, sum, avg, min, max**

-- count, sum, avg, min, max
    select count(`bornDate`) from student;	-- 会忽略null值
    select count(*) from student;	-- 不会忽略null值，返回行数
    select count(1) from student;	-- 不会忽略null值，返回行数
    
    select sum(score) as 总分 from result;
    select avg(score) as 平均分 from result;
    select max(score) as 最高分 from result;
    select min(score) as 最低分 from result;

*  分组

示例：`查询不同课程的平均分，最高分，最低分，要求显示平均分大于80分的`

select sub.subjectName,avg(score) as 平均分,max(score) as 最高分,min(score) as 最低分 from result r
    inner join subject sub
    on r.subjectNu = sub.subjectNu
    group by r.subjectNu
    having 平均分 > 80;

### 3.6 md5加密 ###

> 首先创建一个testmd5表，

create table `testmd5`(
      `id` int(4) not null,
      `name` varchar(12) not null,
      `pwd` varchar(20) not null,
      primary key(`id`)
    )engine=innodb default charset=utf8;
    desc testmd5;
    alter table `testmd5` modify `pwd` varchar(50) not null;

> 插入密码和校验密码

-- 明文密码
    insert into testmd5 values(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456');
    
    -- MD5加密
    update testmd5 set pwd=md5(pwd);
    select char_length(pwd) from testmd5 where id = 1;
    
    -- 插入数据库时加密
    insert into testmd5 values(4,'xiaoming',md5('123456'));
    
    -- 校验密码  一般 是在程序中进行校验
    select * from testmd5 where `name`='xiaoming' and pwd=md5('123456');

[039873e4ca4840e4b8701252b9ff2675.png]: https://img-blog.csdnimg.cn/039873e4ca4840e4b8701252b9ff2675.png
[240d3c6477504bc8811f8913a420e327.png]: https://img-blog.csdnimg.cn/240d3c6477504bc8811f8913a420e327.png#