MySQL数据库常见SQL语句宝典

一 、常用操作数据库的命令

 1.查看所有的数据库 : show databases;
 
 2.创建一个数据库   : create database if not exists 数据库名;
 
 3.删除一个数据库   : drop database if exists 数据库名;
 
 4.选择一张表 (注意在建表之前必须要选择数据库) : use `表名`;
 	* --tab 键的上面，如果你的表名或字段名是一个特殊字段符，就需要带 ``  *
 
 5.在选中的数据库之中查看所有的表 :  show tables;
 
 6.查看创建数据库的语句           ：show create database 数据库名;
 7.查看student数据表的定义语句    ：show create table 表名;
 8.显示表的结构                   ：desc 表名;
 9.删除表                        :  drop table 表名;
 10.查看创建库的详细信息          ：show create database 库名;
 11.查看创建表的详细信息          : show create table 表名;

二、建一张表

– 目标:创建一个schoo1数据库
– 创建学生表(列,字段)使用SQL 创建
– 学号int 登录密码varchar(20)姓名,性别varchar(2),出生日期(datatime)，家庭住址，emai1

分析：
id INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
id ：字段名 （要加个票 Tab键下面的 ）
INT(4)：类型(长度)
NOT NULL：不为空
AUTO_INCREMENT：自增
COMMENT ‘xxx’：注释 （这里 ’ ’ 是回车键隔壁的那个 ‘ ）
DEFAULT ‘xxx’：默认值xxx

-- 注意点，使用英文()，表的名称 和 字段 尽量使用 `` (Tab键下面的) 括起来
-- AUTO_ INCREMENT 自增
-- 字符串使用单引号括起来!
-- 所有的语句后面加，(英文的)，最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键!

CREATE TABLE IF NOT EXISTS `student2`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT'密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT'性别',
    `birthday` DATETIME DEFAULT NULL COMMENT'出生日期',
    `address` VARBINARY(100) DEFAULT NULL COMMENT'家庭住址',
    `email` VARBINARY(50) DEFAULT NULL COMMENT'邮箱',
     PRIMARY KEY(`id`) --设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8  --设置编码

格式

CREATE TABLE [IF NOT EXISTS] `表名`（
	`字段名` 列类型[属性][索引][注释],
	`字段名` 列类型[属性][索引][注释],
	`字段名` 列类型[属性][索引][注释] (最后一句可以不要逗号)
）[表类型][表的字符集设置][注释]``

常用命令
SHOW CREATE DATABASE school – 查看创建数据库的语句

SHOW CREATE TABLE student – 查看student数据表的定义语句

DESC student – 显示表的结构

三、修改表的命令

3.1 修改

修改

 1.  修改表名:  alter  table  旧表名  rename  as  新表名
 		 例子：alter  table  teacher  rename as teacher1
 		
 2.  增加表的字段:  alter  table  表名  add  表字段名  字段类型
             例子：alter table teacher1 add age int(11)
 
 3.   修改表的字段的类型
       --alter table 表名 modify 字段名 字段类型[ ]
       例子：alter table teacher1 moify age varchar(11)  -- 修改约束(例如：由int修改为varchar类型)

 4.   修改指定的字段名
       --alter table 表名 change 旧名字  新名字  字段类型[ ]
	   例子：alter table teacher1 change age age1 int(1) -- 字段重命名

 5.  删除表的字段 : alter table 表名 deop 字段名
     例子：alter table teacher1 drop age1    

 6.  添加字段并指定位置  alter table 表名 add 字段 字段类型   after 字段

3.2 删除

删除

 1.   删除表（如果表存在再删除）
      例子：drop table if exists teacher1

四、DML 数据库操作语言（重要）

4.1 添加

添加 insert

语法：
insert into 表名 (`字段名1`,`字段名2`,`字段名3`,……)  values (`值1`,`值2`,`值3`, ……)

还可以同时插入多条数据，VALUES后面的值需要使用，隔开即可
语法：
insert into 表名 (`字段名1`,`字段名2`,`字段名3`,……)  values (`值1`,`值2`,`值3`, ……),(`值1`,`值2`,`值3`, ……),……

例子
-- 一般写插入语句，我们一定要数据和字段一一对应。
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一');

--单独只插入一个字段
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaaa','男')

--可以同时插入多条数据，VALUES后面的值需要使用，隔开即可
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('李四','aaaaa','男'),('王五','23232','女')

4.2 修改

修改：

---

update 修改谁（条件） set 原来的值=新值

语法：
update 表名 set `字段名1` = '值1' ,`字段名2` = '值2' … where [条件]

-- 修改学员名字

UPDATE `student` SET `name`='囷' WHERE id =1;
-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='233'

-- 语法；
-- UPDATE 表名 set column_name,[] = value where 条件

条件：where 子句 运算符 id 等于 某个值，大于某个值，在某个区间内修改

操作符返回布尔值

注意：

• column_name 是数据库的列，带上
• 条件，是筛选的条件，如果没有指定，则会修改所有的列
• value 是一个具体的值，也可以是一个变量
• 多个设置的属性之间，使用英文逗号隔开

UPDATE `student` SET `birthday`=CURRENT_TIME where `name`='李四' AND SEX = '男'

4.3 删除

delete 命令

语法  delete from 表名 [where 条件]

-- 删除数据 (避免这样写,会删除所有的数据)
DELETE FROM `student`

-- 删除指定
DELETE FROM `student` where id= 1

TRUNCATE 命令

作用：完全清空一个数据库，表的结构和索引不会变

delete 和 truncate 区别

• 相同点： 都能删除数据，都不会删除表结构
• 不同：
  - TRUNCATE 重新设置自增列 计数器会归零
  - TRUNCATE 不会影响事务

测试代码：

-- 测试delete 和 truncate 区别

CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')

DELETE FROM `test` -- 不会影响自增

TRUNCATE TABLE `test` -- 自增会归零

了解即可：delete删除的问题 重启数据库，现象

• innoDB 自增列会从1开始（存在内存当中，断电即失）
• MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

五、DQL查询数据（最重点）

5.1 DQL

(Data Query Language) :数据查询语言

• 所有的查询操作都用它 Select（select 选择）
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语言

5.2 指定查询字段

 1. 查询全部的学生：select *(通配符) from 表名
 		例子：select * from student
 	
 2.查询指定字段：select  `字段1`,（英文逗号隔开）`字段2`,…   from 表
   		例子：select `studentNo` , `studentName` from student
   
 3.别名，给查询结果表头起一个名字  as  可以给字段起别名，也可以给表起别名
 		例子：selsct `studentNo` as 学号 , `studentName` as 学生名字 from student as s

 4.函数 Concat（a,b）给查出来的表添加字体
 		例子：select concat('姓名：' ,StudentName) as 新名字 from student

去重(将查询出来重复的数据去除掉)

  语法：在select后面加上一个distinct
        select distinct `字段名` from 表名

数据库的列 (表达式)

select 表达式 from 表

5.3where 条件子句

作用：检索数据中符合条件的值

逻辑运算符

尽量使用英文

题目：查询考试成绩在95分到100分之间
方式一：
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult<=100

方式二：
-- 模糊查询（区间）
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

方式三：
-- 除了100分学生之外的同学成绩
SELECT `StduentNo`,`StudentResult` FROM result
WHERE  StudentResult != 100

方式四： !=  not
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentResult = 100

模糊查询：比较运算符

==================like （% 和 _ 只能用在like中）===========================
--  查询姓刘的同学
-- like结合 %（代表0到任意字符）  _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%';

-- 查询姓刘的同学，名字后只有一个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_';

-- 查询姓刘的同学，名字后只有两个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__';

-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';



===================IN(具体的一个或者多个值)===========================
-- 查询1001 1002 1003 学员信息
之前的方法：
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003

用in来操作的方法：
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);

-- 查询在北京的学生（注意in查询的是一个具体的值才能查询出来）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('安徽','河南洛阳');


===================NULL NOT NULL===================================
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=''OR address IS NULL

-- 查询有出生日期的同学  不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;

5.4 联表查询

JOIN 对比

两表查询

语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1 as(as可以省略) 别名1
(inner\left\right) join 表2 as 别名2
(where\on) 别名1.共同字段 = 别名2.共同字段 

/*
1. 分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo 
-- JION（表） ON （判断的条件）连接查询
-- where 等值查询
SELECT studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo

--Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo

--LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo

有条件的联表查询

语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1 as(as可以省略) 别名1
(inner\left\right) join 表2 as 别名2
on 别名1.共同字段 = 别名2.共同字段 
where 条件
and 条件2 and 条件3……

查询没有考试的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL

三表或多表查询

语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1  别名1
(inner\left\right) join 表2  别名2
on 别名1.共同字段1 = 别名2.共同字段1
(inner\left\right) join 表3  别名3
on 别名1.共同字段2 = 别名3.共同字段2 

 查询了参加考试同学的信息：学号：学生姓名：科目名：分数
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo

-- 我要查询哪些数据 SELECT ....
-- 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
-- 假设存在一中多张表查询，先查询两章表，然后再慢慢增加

FROM a LEFT JOIN b   左为准
FROM a RIGHT JOIN b	右为准

5.5 分页和排序

============================分页 limit 和排序order by=================

-- 排序：  升序ASC  降序  DESC
SELECT  xx
FROM xx
JOIN xx
WHERE  xx
ORDER BY  xx
ASC   ||  DESC

排序

---

语法：order by 通过那个字段排序 怎么排
order by 字段名 asc（升序）\desc（降序）

分页

---

语法： limit 起始项，每页的数量

-- 为什么要分页
-- 缓解数据库压力，给人的体验更好


-- 分页，每页显示五条数据

-- 语法： limit 当前页，页面的大小
-- limit 0,5 1-5
-- limit 1,5 1-5
-- limit 6,5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`subjectNo`
WHERE subjectName='数据结构-1'
ORDER BY StudentResult ASC
LIMIT 0,5

-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit 5*（n-1）,5

5.6 子查询

where（这个值是计算出来的，即不确定的），之前的where后面是跟一个固定的值
本质：在where语句中嵌套一个子查询语句

where （select * from）
解析：where 后加( ) 括号里面放新的查询语句

-- ===========================where=========================

-- 1.查询 数据库结构-1的所有考试结构（学号，科目编号，成绩） 降序
-- 方式一： 连接查询
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC

-- 方式二：使用子查询(由里及外)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE StudentNo=(
	SELECT SubjectNo FROM  `subject` 
    WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC


-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80

-- 在这个基础上 增加一个科目 ，高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(
    SELECT Subject FROM `subject`
    WHERE SubjectName='高等数学-2'
)

-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='高等数学-2'
WHERE `SubjectaName`='高等数学-2' AND StudentResult >=80


-- 再改造 (由里即外)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等数学-2'
)
)

六、MySQL函数

6.1 常用函数

 数学运算

SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 返回字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('我爱编程helloword',1,2,'超级热爱') -- 从某个位置开始替换某个长度 ：1为替换的开启下标，2为替换的字字符的长度   结果为：超级热爱编程helloword
SELECT UPPER('Abc') --小写字母
SELECT LOWER('Abc')  --大写字母
SELECT INSTR('kuangshen','h')  -- 返回第一次出现的字符串的索引
SELECT REPLACE('坚持就能成功','坚持','努力')  -- 替换出现的指定字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6)  --返回指定的子字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('清晨我上马')  -- 反转

-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'

-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME()  -- 本地时间
SELECT SYSDATE()  -- 系统时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

6.2 聚合函数（常用）

分组

group by

分组后有条件判断用

naving 条件​​​​​​​

6.3 数据库级别MD5加密（拓展）

什么是MD5

主要增强算法复杂度不可逆性。

MD5不可逆，具体的MD5是一样的

MD5破解原理，背后有一个字典，MD5加密后的值，加密前的值

CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=UTF8


-- 明文密码
INSERT INTO testmd5 VALUES(1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1  -- 加密全部

-- 插入时加密

INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'红',MD5('123456'))

-- 如何校验，将用户传递过来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123456')

明码

MD5加密后

插入数据的时候就加密：​​​​​​​

     感谢大家的阅读，觉得有帮助的朋友们点点赞！