MySQL-note(自用)

基于狂神说视频

初识MySQL

数据库分类

关系型数据库：SQL

• 通过外键关联来建立表与表之间的关系
• 通过表与表之间、行与列之间的关系进行数据的存储
• MySQL

非关系型数据库：NO SQL (No Only SQL)

• 对象存储，通过对象自身的属性来决定
• Redis

安装MySQL详细说明

mysql重启

net stop mysql
net start mysql

常用命令

mysql -uroot -p123456 --登录

update user set password=password('123456')where user='root'; --修改密码

flush privileges; --刷新数据库

show databases; --显示所有数据库

use dbname； --打开某个数据库

show tables; --显示数据库mysql中所有的表

describe user; --显示表mysql数据库中user表的列信息

create database name; --创建数据库

ctrl + c --强行终止

exit --退出Mysql

操作数据库

操作数据库

create database [if not exists] 数据库名; --创建数据库

drop database [if exists] 数据库名; --删除数据库

show databases; --查看数据库

USER `school` --使用数据库，如果表名或字段名是特殊字符，需要加``

数据库的字段属性

-- 每一个表都必须存在以下字段
id         主键
version    乐观锁
is_delete  伪删除
gmt_create 创建时间
gmt_update 修改时间

创建数据库表

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; 


DESC student -- 显示表的结构

INNODB与MYISAM的区别

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持，可以多表多用户操作
全文索引	支持	不支持
表空间的大小	较小	较大

修改和删除表

ALTER TABLE student RENAME AS student1 --修改表名
ALTER TABLE 表名 ADD 字段名 列属性 --添加字段
ALTER TABLE 表名 MODIFY 字段名 列类型 --修改约束
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性 --字段重命名
ALTER TABLE 表名 DROP 字段名 --删除字段

DROP TABLE [IF EXISTS] 表名 --删除表

MySQL数据管理

外键

CREATE TABLE `grade` (
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 方式一：很麻烦
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 '出生日期', 
    `gradeid` INT(10) NOT NULL COMMENT '学生年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址', 
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱', 
    PRIMARY KEY(`id`),
    KEY `FK_gradeid` (`gradeid`), -- 定义外键key
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) -- 添加约束
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

上述都是物理外键，数据库级别的外键，不建议使用！

因为每次删除或更新的时候都必须考虑外键约束，开发很痛苦，测试不方便！

insert

DML = 数据操作语言，增删改

INSERT INTO grade(gradename) VALUES ('大一'); -- 插入一个数据

INSERT INTO grade(gradename) VALUES ('大一'), ('大二'); -- 插入多个数据

INSERT INTO grade VALUES (1,'大一'); -- 省略字段名，默认是一一匹配的，所以需要加上gradeid 1

update

-- 没有条件语句的话会把表中所有数据的column_name设置为value
UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];
-- 表名 和 column_name 最好都带上``

delete

DELETE FROM 表名 [WHERE condition]; -- 不加条件会删除整张表

TRUNCATE [TABLE] table_name; -- 清空表的数据

delete 与 truncate 的区别

• 都能删除数据 , 不删除表结构
• 使用TRUNCATE TABLE 重新设置AUTO_INCREMENT计数器
• 使用TRUNCATE TABLE不会对事务有影响

DQL查询数据

指定查询字段

 -- 查询所有学生信息
SELECT * FROM student;

-- 查询指定列(学号 , 姓名)
SELECT studentno,studentname FROM student;

-- 这里是为列取别名(当然as关键词可以省略)
SELECT studentno AS 学号,studentname AS 姓名 FROM student;

-- 使用as也可以为表取别名
SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;

-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

-- 查看哪些同学参加了考试
SELECT DISTINCT studentno FROM result;

-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT VERSION(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式

-- 学员考试成绩集体提分一分查看
SELECT studentno,StudentResult+1 AS '提分后' FROM result;

where条件子句

-- 查询考试成绩在95-100之间的
SELECT Studentno,StudentResult FROM result
WHERE StudentResult>=95 AND StudentResult<=100;

模糊查询

-- 查询姓刘的同学的学号及姓名
-- 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 '%嘉%';

-- 查询学号为1000,1001,1002的学生姓名
SELECT studentno,studentname FROM student
WHERE studentno IN (1000,1001,1002);

-- 查询地址在北京,南京,河南洛阳的学生
SELECT studentno,studentname,address FROM student
WHERE address IN ('北京','南京','河南洛阳');

-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT studentname FROM student
WHERE BornDate IS NULL;

-- 查询没有写家庭住址的同学(空字符串不等于null)
SELECT studentname FROM student
WHERE Address='' OR Address IS NULL;

联表查询

SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno -- on/where都可以

-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
RIGHT JOIN result r -- 重点是查分数，以右表为准
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

自连接

-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

分页&排序

排序

-- ORDER BY 语句默认按照ASC升序对记录进行排序
-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC

分页

-- 第N页 : limit (pageNo-1)*pageSzie,pageSzie
-- 每页显示5条数据，显示第一页
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5

-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年'
ORDER BY StudentResult DESC
LIMIT 0,10

子查询

-- 查询 数据库结构-1 的所有考试结果(学号,科目编号,成绩),并且成绩降序排列
-- 方法一:使用连接查询
SELECT studentno,r.subjectno,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC;

-- 方法二:使用子查询(执行顺序:由里及外)
SELECT studentno,subjectno,StudentResult
FROM result
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '数据库结构-1'
)O
RDER BY studentresult DESC;

-- 分数不小于80分的学生的学号和姓名
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
-- 在上面SQL基础上,添加需求:课程为 高等数学-2
SELECT r.studentno,studentname FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-2'
)

-- 分步写简单sql语句,然后将其嵌套起来
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)

MySQL函数

常用函数

SELECT ABS(-8); /*绝对值*/
SELECT CEILING(9.4); /*向上取整*/
SELECT FLOOR(9.4); /*向下取整*/
SELECT RAND(); /*随机数,返回一个0-1之间的随机数*/
SELECT SIGN(0); /*符号函数: 负数返回-1,正数返回1,0返回0*/

SELECT CHAR_LENGTH('狂神说坚持就能成功'); /*返回字符串包含的字符数*/
SELECT CONCAT('我','爱','程序'); /*合并字符串,参数可以有多个*/
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱'); /*替换字符串,从某个位置开始替换某个长度*/
SELECT LOWER('KuangShen'); /*小写*/
SELECT UPPER('KuangShen'); /*大写*/
SELECT LEFT('hello,world',5); /*从左边截取*/
SELECT RIGHT('hello,world',5); /*从右边截取*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转 */
-- 查询姓周的同学,改成邹
SELECT REPLACE(studentname,'周','邹') AS 新名字
FROM student WHERE studentname LIKE '周%';

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 VERSION(); /*版本*/
SELECT USER(); /*用户*/

聚合函数

-- 聚合函数
/*COUNT:非空的*/
SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student; /*推荐*/
-- 从含义上讲， count(1) 与 count(*) 都表示对全部数据行的查询。
-- count(字段) 会统计该字段在表中出现的次数， 忽略字段为null 的情况。 即不统计字段为null 的记录。
-- count(*) 包括了所有的列， 相当于行数， 在统计结果的时候， 包含字段为null 的记录；
-- count(1) 用1代表代码行， 在统计结果的时候， 包含字段为null 的记录
/*
1) 在表没有主键时， count(1)比count(*)快
2) 有主键时， 主键作为计算条件， count(主键)效率最高；
3) 若表格只有一个字段， 则count(*)效率较高
*/

SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;

SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高
分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno -- 分组，求出各科的信息
HAVING 平均分>80; -- 分组后再筛选

MD5加密

update testmd5 set pwd = md5(pwd);

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}];
-- 指定查询的记录从哪条至哪条

事务

CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)。

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交

索引

作用

• 提高查询速度
• 确保数据的唯一性
• 加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化

分类

• 主键索引 (Primary Key)
  • 某一个属性组能唯一标识一条记录
• 唯一索引 (Unique)
  • 避免同一个表中某数据列中的值重复
  • 主键索引只能有一个，唯一索引可能有多个
• 常规索引 (Index / Key)
  • 快速定位特定数据
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作
• 全文索引 (FullText)
  • 快速定位特定数据
  • 适合大型数据集

# 方法一： 创建表时
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]) ;

# 删除索引： DROP INDEX 索引名 ON 表名字;

# 删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;

# 显示索引信息: SHOW INDEX FROM student;