一、对数据库的操作
1、对库的操作
1.1 创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名;
1.2 删除数据库
DROP DATABASE [if EXISTS] 数据库名;
1.3 使用数据库
use 数据库名;
1.3 查看数据库
SHOW DATABASES;
2.数据库的基本数据类型
2.1数值
2.2 字符串
2.3 时间日期
2.4 null
(1) 无符号的
(2) 声明了该列不能为负数
拓展：每一个表，都必须存在以下五个字段
二、对表的操作
1 创建数据库表
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) 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` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
格式：
CREATE TABLE IF NOT EXISTS `student`(
'字段名' 列类型 [属性] [索引] [注释],
'字段名' 列类型 [属性] [索引] [注释],
......
'字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]
2、修改表
(1)修改表名 :
ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;
(2)添加字段 :
ALTER TABLE 表名 ADD字段名 列属性[属性]
ALTER TABLE teachers ADD age INT(11);
(3)修改字段 :
ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
(4)删除字段 :
ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;
二、对表数据的操作（DML）
1. 添加 insert
语法：
INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];
-- 普通用法
INSERT INTO `student`(`name`) VALUES ('zsr');
-- 插入多条数据
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');
-- 省略字段
INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1);
注意：
（1）字段和字段之间使用英文逗号隔开
（2）字段是可以省略的，但是值必须完整且一一对应
（3）可以同时插入多条数据，VALUES后面的值需要使用逗号隔开
2.修改 update
语法：
UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];
-- 修改学员名字,指定条件
UPDATE `student` SET `name`='zsr204' WHERE id=1;
-- 不指定条件的情况,会改动所有表
UPDATE `student` SET `name`='zsr204';
-- 修改多个属性
UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;
-- 通过多个条件定位数据
UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';
3.删除 delete
语法：
DELETE FROM 表名 [WHERE 条件]
-- 删除数据(避免这样写,会全部删除)
DELETE FROM `student`;
-- 删除指定数据
DELETE FROM `student` WHERE id=1;
拓展：
TRUNCATE ：完全清空一个数据库表，表的结构和索引约束不会变！
DELETE和TRUNCATE 的区别：
(1)DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表
(2)TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增
(3)DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。
4.查询 select
语法：
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
4.1 基本查询
SELECT 查询列表 FROM 表名;
4.2 连接查询
https://blog.csdn.net/qq_27184497/article/details/119672665
4.3 条件查询
语法：
select 查询列表 from 表名 where 筛选条件;
-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;
-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;
-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';
-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';
-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);
4.4 分组查询
语法：
select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
区别：
-- 查询不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根据不同的课程进行分组
SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90;
4.5 排序和分页
select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc
limit offset,pagesize;
排序：
asc ：升序，如果不写默认升序 desc：降序
分页：
offset代表的是起始的条目索引，默认从0开始
size代表的是显示的条目数
offset=(n-1)*pagesize
-- 第一页 limit 0 5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页 limit (n-1)*pagesize,pagesize
-- pagesize:当前页面大小
-- (n-1)*pagesize:起始值
-- n:当前页面
-- 数据总数/页面大小=总页面数
-- limit n 表示从0到n的页面
4.6 子查询
-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列
-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.StudentNo=s.SubjectNo
WHERE SubjectName='课程设计'
ORDER BY StudentResult DESC;
-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
SELECT SubjectNo FROM `subject`
WHERE SubjectName='课程设计'
)
三、MySQL函数
1.常用函数
```dart
-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1
-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串
-- 时间日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT now(); -- 获取当前时间
SELECT LOCALTIME(); -- 本地时间
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();
- 聚合函数
// 计算个数
SELECT COUNT(StudentName) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
SELECT SUM(`StudentResult`) FROM result; //和
SELECT AVG(`StudentResult`) FROM result; //平均值
SELECT MAX(`StudentResult`) FROM result; //最大值
SELECT MIN(`StudentResult`) FROM result; //最小值
四、索引
1、 索引（Index）是一种数据结构，可以帮助我们快速的进行数据的查找。。
作用：
(1) 提高查询速度
(2) 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
(3) 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
(4) 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
(5) 全文检索字段进行搜索优化
索引的数据结构和具体存储引擎的实现有关，在 MySQL 中使用较多的索引有 Hash 索引，B+ 树索引等，而我们经常使用的 InnoDB 存储引擎的默认索引实现为：B+ 树索引
2、索引的分类
主键索引（PRIMARY KEY），普通索引（KEY / INDEX），普通索引（KEY / INDEX），全文索引（FULLText）
2.1 主键索引（PRIMARY KEY）
唯一的标识，主键不可重复，只有一个列作为主键
(1) 最常见的索引类型，不允许为空值
(2) 确保数据记录的唯一性
(3) 确定特定数据记录在数据库中的位置
-- 创建表的时候指定主键索引
CREATE TABLE tableName(
......
PRIMARY INDEX (columeName)
)
-- 修改表结构添加主键索引
ALTER TABLE tableName ADD PRIMARY INDEX (columnName)
2.2 普通索引（KEY / INDEX）
默认的，快速定位特定数据
(1) index 和 key 关键字都可以设置常规索引
(2) 应加在查询找条件的字段
(3) 不宜添加太多常规索引,影响数据的插入,删除和修改操作
-- 直接创建普通索引
CREATE INDEX indexName ON tableName (columnName)
-- 创建表的时候指定普通索引
CREATE TABLE tableName(
......
INDEX [indexName] (columeName)
)
-- 修改表结构添加普通索引
ALTER TABLE tableName ADD INDEX indexName(columnName)
2.3 通索引（KEY / INDEX）
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值
与主键索引的区别：主键索引只能有一个、唯一索引可以有多个
-- 直接创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)
-- 创建表的时候指定唯一索引
CREATE TABLE tableName(
......
UNIQUE INDEX [indexName] (columeName)
);
-- 修改表结构添加唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)
2.4 全文索引（FULLText）
快速定位特定数据（百度搜索就是全文索引）
(1) 在特定的数据库引擎下才有：MyISAM
(2) 只能用于CHAR , VARCHAR , TEXT数据列类型
(3) 适合大型数据集
-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引
3.索引的使用
3.1 索引的创建
在创建表的时候给字段增加索引：
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名] (字段名[(长度)] [ASC |DESC])
);
创建完毕后，增加索引：
-- 方法一：CREATE在已存在的表上创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
-- 方法二：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL]
INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
3.2 索引的删除
-- 删除索引
DROP INDEX 索引名 ON 表名;
-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;
3.3 显示索引信息
SHOW INDEX FROM 表名;
3.4 索引原则
(1) 索引不是越多越好，小数据量的表不需要加索引
(2) 不要对经常变动的数据增加索引
(3) 索引一般加在经常要查询的列上
3.5 explain分析sql执行的情况
-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);
-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
