mysql语法使用总结_家庭住址mysql-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_46480020/article/details/127063347

文章目录

一、Other
二、DQL
三、常用函数

一、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表，会报错

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的区别

在这里插入图片描述

-- 联表查询
/* 思路
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');