MySQL入门
菜鸟编程读书笔记
介绍
MySQL是当下最流行的关系型数据库管理系统（RDBMS），数据库是按照数据结构组织，存储，管理数据的仓库。
数据库和java一样有自己的API，实现增删改查。
用io流把数据存到文件里，读写效率太低，就出现了数据库。
RDBMS的特点
数据 -> 表，行 -> 记录名称，列 -> 数据域 , 行 + 列 = 表，表 × n = 数据库（db）
陌生的术语
冗余：存储两倍数据，性能低，更安全
主键：唯一，用于查数据
复合键（组合键）：将多个列作为一个索引键，用于复合索引
索引：快速访问数据库表中的特定信息，类似目录
参照完整性：参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足 的完整性约束条件，目的是保证数据的一致性
表格五要素
表头（header）列（col）行（row）值（value）键（key）
键的值在当前列中有唯一性
MySQL数据库是瑞士开发的数据库。
安装+配置
注意要下载压缩包版本的
地址：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
删库跑路：sc delete mysql，如果配置错了重开的命令
配环境变量：Path，安装目录\bin
ini文件：
安装目录下建一个my.ini文件
[client]
port=3306
default-character-set=utf8
[mysqld]
basedir=安装目录\
datadir=安装目录\data\
port=3306
character_set_server=utf8
skip-grant-tables
管理员模式cmd，切换到bin目录下 cd /d 目录
mysqld -install
mysqld --initialize-insecure --user=mysql
net start mysql
mysql -u root -p
use mysql;
update user set authentication_string=password('fgj') where user='root' and Host='localhost';
flush privileges;
ini文件最后一行删了
net stop mysql
net start mysql
到这里，安装就完成了！
Navicat（可破解）
提供构建、管理和维护数据库的新方法，提高可用性和可访问性，
你能够以前所未有的速度完成复杂的工作，方便管理多种数据库，
支持管理某些云数据库，Navicat 的用户界面 (GUI) 设计良好，让你以安全且简单的方法创建、组织、访问和共享信息。
基本命令：
-- 创建数据库
CREATE DATABASE runoob;
-- 删除数据库
DROP DATABASE runoob;
-- 选择数据库
USE runoob;
数据类型
常用：INT DOUBLE DECIMAL DATETIME TIMESTAMP
VARCHAR TEXT
基本命令
-- 创建数据库
CREATE DATABASE runoob;
-- 删除数据库
DROP DATABASE runoob;
-- 选择数据库
USE runoob;
-- 创建数据表
CREATE TABLE IF NOT EXISTS `runoob_tb1`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY (`runoob_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `runoob_tb2`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY (`runoob_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 删除数据表
DROP TABLE `runoob_tb2`;
-- 插入数据
INSERT INTO `runoob_tb1`(
`runoob_title`,`runoob_author`,`submission_date`
)VALUES(
"学习java","菜鸟教程",NOW()
);
INSERT INTO `runoob_tb1`(
`runoob_title`,`runoob_author`,`submission_date`
)VALUES(
"学习PHP","菜鸟教程",NOW()
);
INSERT INTO `runoob_tb1`(
`runoob_title`,`runoob_author`,`submission_date`
)VALUES(
"java教程","RUNOOB.COM","2022-01-01"
);
-- ------------------------------
INSERT INTO `runoob_tb2`(
`runoob_title`,`runoob_author`,`submission_date`
)VALUES(
"学习c","菜鸟教程",NOW()
);
INSERT INTO `runoob_tb2`(
`runoob_title`,`runoob_author`,`submission_date`
)VALUES(
"学习c++","菜鸟教程",NOW()
);
INSERT INTO `runoob_tb2`(
`runoob_title`,`runoob_author`,`submission_date`
)VALUES(
"c++教程","RUNOOB.COM","2022-01-01"
);
-- 读取数据表
select * from runoob_tb1;
select * from runoob_tb2;
-- 查询数据
select runoob_tb1.runoob_author,runoob_tb2.runoob_title FROM runoob_tb1,runoob_tb2 LIMIT 2;
select runoob_author,runoob_title,submission_date from runoob_tb1 where runoob_id > 1 and runoob_id < 3;
-- 更新数据
update runoob_tb1 set runoob_title="这条数据已更改" where runoob_id = 2;
select * from runoob_tb1 where runoob_id = 2;
-- 删除数据
delete from runoob_tb1 where runoob_id = 2;
-- like模糊查找
select * from runoob_tb2 where runoob_author like '%COM';
常用命令2
use runoob;
-- union (去重)
select country from websites union select country from apps order by country
-- union all (不去重)
select country from websites union all select country from apps order by country
select country,name from websites where country="CN" union all select country,app_name from apps where country="CN" order by country
-- UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
-- UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
-- order by 排序： desc:降序 asc:升序 默认升序
select * from `runoob_tb1`
select * from `runoob_tb1` order by `submission_date` desc
-- group by 分组，可以用函数
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
`id` int(11) NOT NULL,
`name` char(10) NOT NULL DEFAULT '',
`date` datetime NOT NULL,
`signin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
select * from employee_tbl
-- 统计name记录条数
select `name`,count(*) from employee_tbl group by `name`;
-- with rollup 再分组
select `name`,sum(signin) as signin_count from employee_tbl group by `name` with rollup
-- coalesce
select coalesce(`name`,"总数"),sum(signin) as signin_count from employee_tbl group by `name` with rollup
连接
inner join：内连接，获取两个表中字段匹配关系的记录
left join：左连接，获取左表所有记录，即使右表没有对应匹配的记录
right join：右连接，获取右表所有记录，即使左表没有对应匹配的记录
内连接示意图 ->
左连接示意图 -> 
右连接示意图 ->
create database db1;
use db1;
/*
Navicat MySQL Data Transfer
Source Server : 127.0.0.1
Source Server Version : 50621
Source Host : localhost
Source Database : RUNOOB
Target Server Version : 50621
File Encoding : utf-8
Date: 04/13/2017 14:25:12 PM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `runoob_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `runoob_tbl`;
CREATE TABLE `runoob_tbl` (
`runoob_id` int(11) NOT NULL AUTO_INCREMENT,
`runoob_title` varchar(100) NOT NULL,
`runoob_author` varchar(40) NOT NULL,
`submission_date` date DEFAULT NULL,
PRIMARY KEY (`runoob_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `runoob_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `runoob_tbl` VALUES ('1', '学习 PHP', '菜鸟教程', '2017-04-12'), ('2', '学习 MySQL', '菜鸟教程', '2017-04-12'), ('3', '学习 Java', 'RUNOOB.COM', '2015-05-01'), ('4', '学习 Python', 'RUNOOB.COM', '2016-03-06'), ('5', '学习 C', 'FK', '2017-04-05');
COMMIT;
-- ----------------------------
-- Table structure for `tcount_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `tcount_tbl`;
CREATE TABLE `tcount_tbl` (
`runoob_author` varchar(255) NOT NULL DEFAULT '',
`runoob_count` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `tcount_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `tcount_tbl` VALUES ('菜鸟教程', '10'), ('RUNOOB.COM ', '20'), ('Google', '22');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
use db1;
select * from runoob_tbl;
select * from tcount_tbl;
-- 连接表
-- 1. 内连接
select a.runoob_id,a.runoob_author,b.runoob_count from runoob_tbl a inner join tcount_tbl b on a.runoob_author = b.runoob_author;
-- 上述代码等价于 => where 子句
select a.runoob_id,a.runoob_author,b.runoob_count from runoob_tbl a , tcount_tbl b where a.runoob_author = b.runoob_author;
-- 2. 左连接
select a.runoob_id,a.runoob_author,b.runoob_count from runoob_tbl a left join
tcount_tbl b on a.runoob_author = b.runoob_author;
-- 不存在对应的 where 子句
-- 3. 右连接
select a.runoob_id,a.runoob_author,b.runoob_count from runoob_tbl a right join
tcount_tbl b on a.runoob_author = b.runoob_author;
-- 不存在对应的 where 子句
NULL
MySQL中查询条件字段为NULL，= 、!= 都失效了，对应：
IS NULL: 当列的值是 NULL,此运算符返回 true
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。
正则表达式
-- 正则表达式
create table if not exists `regexptb` (`id` int
unsigned auto_increment ,`lan` varchar(100) not null,
primary key (`id`))engine=innodb default charset=utf8;
select * from `regexptb`
insert into `regexptb` (`lan`) values("一只敏捷的棕色狐狸跳过一只懒惰的狗"),
("hello world "),
("我他妈莱纳"),
("剑光如我，斩尽牛杂");
update `regexptb` set `lan`="hello world" where id = 2;
select * from `regexptb` where `lan` regexp "^我"
select * from `regexptb` where `lan` regexp "d$"
select * from `regexptb` where `lan` regexp "我"
select * from `regexptb` where `lan` regexp "我|狗"
事务
-
begin 开始事务
rollback 回滚事务
commit 确认事务
-
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
use db1;
create table `tb1`(`id` int )engine=innodb;
select * from `tb1`
begin;
insert into tb1 values(2);
insert into tb1 values(333);
insert into tb1 values(44);
insert into tb1 values(5);
insert into tb1 values(65);
rollback;
commit;
select * from tb1;
delete from tb1;
一些解释：
假如有这样一款游戏，游戏分为一个个关卡，单个关卡内需要打败很多Boss才能闯关成功，每个管卡闯关成功游戏会自动进行记录游戏进度，并将游戏进度上传远程服务器，除非重置游戏，否则游戏进度不可回退。
原子性：最小操作逻辑，不可再进行分割；就像游戏的一个关卡一样，关卡内不管闯到什么地方，只要失败了就会回到闯关卡前。
事务，将原本是一系列的数据库操作包裹成一个关卡（一个原子操作），要么闯过关卡，要么回到关卡前。
持久性：当事务执行完毕（事务提交），该操作将永久的改变了数据库中的数据。即关卡闯关完成，游戏进度永久保存在了服务器中。
事务，能保证AID，即原子性，隔离性，持久性。但是一致性无法通过事务来保证，一致性依赖于应用层，开发者。
ROLLBACK 回滚的用法可以设置保留点 SAVEPOINT，执行多条操作时，回滚到想要的那条语句之前。
alter
-- 删除，增加，修改字段名字
ALTER TABLE `tb1` ADD `add` INT;
UPDATE `tb1`
SET `add` = 3 ALTER TABLE `tb1` DROP `add`;-- 指定位置
ALTER TABLE tb1 ADD fir INT FIRST;
ALTER TABLE tb1 ADD `end` INT AFTER id;-- 查看列状态
SHOW COLUMNS
FROM
`tb1` ALTER TABLE `tb1` ADD `charch` VARCHAR ( 10 );
alter table `tb1` modify charch varchar(12);
alter table `tb1` change charch1 `int1` int ;
SHOW COLUMNS FROM `tb1`
-- 指定 null
alter table tb1 modify `id` int not null default 100;
select * from tb1;
-- 修改字段默认值
alter table tb1 alter id set default 1000;
-- 删除默认值
alter table tb1 alter id drop default;
-- 修改表名tcount_tbl
alter table tb1 rename to tb2;
索引
如果没有设计和使用索引的MySQL就是一个人力三轮车合理的的话，那么设计且使用索引的MySQL是一辆兰博基尼。
常用命令：
-- 1. 创建索引
create index `index1` on `tb1`(`id`);
-- --------------------
alter table `tb1` add index `index1`(`id`);
-- 2. 删除索引
drop index `index1` on `tb1` ;
-- 3. 修改主键
alter table `tb1` add primary key(`id`);
-- 4. 删除主键
alter table `tb1` drop `id`;
-- 5. 显示索引信息
show index from `tb1`;
临时表
临时表用于保存一些临时数据，只在当前连接可见，一旦关闭连接就自动删除。
命令：
table -> temporary table
-- 用查询直接创建临时表：
create temporary table `tb1` as (select * from `tb` limit 0,1000);
表复制
-- 第一、只复制表结构到新表
create table `clone_tb` select * from `tb2` where 1=2;
-- 第二、复制表结构及数据到新表
create table `clone_tb` select * from `tb2`;
元数据
MySQL的信息一般包括以下三种：
-
查询结果信息：
select update delete语句影响的记录数
-
数据库和数据表的信息：
数据库和数据表的结构信息
-
MySQL服务器信息：
数据库服务器的当前状态，版本号等
常用命令
-- 查看服务器版本信息
select version();
-- 当前数据库名
select database();
-- 当前用户名
select user();
-- 服务器状态
show status;
-- 服务器配置变量
show variables;
序列
让其他字段也像主键一样自增
auto_incriment
处理重复数据
-- 1. 双主键
create table `tb1` (
`id` int
,`name` varchar(10)
,primary key (id)
);
drop table `tb2`
drop table `tb1`
-- 2. 忽略重复插入,前提是没设置唯一性
insert ignore into `tb1` values(01,"hello")
,(01,"hello");
insert into `tb1` values(1,"hello")
,(02,"world")
,(03,"hi")
,(04,"mysql");
insert into `tb1` values (02,"world");
select * from `tb1`;
select count(*) as repetitions,`id`,`name` from `tb1` group by `id`,`name` having repetitions > 1;
-- 过滤重复数据 -> distinct
select distinct id,`name` from `tb1`
-- 或 -> group by
select `name`,sum(id) as summ from `tb1` group by `name` with rollup
常用函数（现用现查）
运算符（现用现查）
随便做的一些练习：
drop database runoob;
create database runoob;
use runoob;
-- 创建一个角色表
create table if not exists `role_tb` (
`id` int unsigned auto_increment,
`name` varchar(100) not null,
`age` int ,
`hobby` varchar(100) not null,
`eat` char(10) not null,
`sleep` char(10) not null,
`play` char(10) not null,
primary key (`id`)
)engine=innodb default charset=utf8;
select * from `role_tb`
insert into `role_tb`(
`name`,age,hobby,eat,sleep,play
)values
("胡桃",12,"摆烂","对","错","对"),
("刻晴",13,"吃饭","对","对","对"),
("芭芭拉",18,"睡觉","对","对","错"),
("琴",22,"摆烂","对","对","错"),
("雷电将军",23,"摆烂","对","错","错"),
("甘雨",14,"吃饭","对","对","对"),
("重云",13,"摆烂","对","对","错"),
("凝光",21,"睡觉","对","错","对"),
("旅行者",6,"摆烂","对","对","错");
-- 把旅行者改成钟离
update role_tb set `name`="钟离" where `id`=9;
update `role_tb` set age=24 where `id`=9;
-- 谁在摆烂
select `name` from role_tb where hobby like "%烂"
-- 按照年龄排序
select `name` as "名字" from role_tb order by age;
-- 事务
begin;
insert into role_tb (`name`,age,hobby,eat,sleep,play) values
("班尼特", 12,"睡觉","对","对","对");
rollback;
commit;
delete from `role_tb` where id>9 and id<14;
select * from role_tb
drop table `role_tb`
create table `people_tb` (
id int unsigned auto_increment,
`name` varchar(10) not null,
`age` int,
`hobby` varchar(10) not null,
primary key(`id`)
);
insert into `people_tb`(`name`,`age`,`hobby`) values
("小明",12,"钓鱼"),
("小红",14,"摆烂"),
("小刚",13,"健身"),
("小绿",16,"写作"),
("小黑",12,"电竞");
select * from `people_tb`;
-- 连接
-- 查找年龄相同的名字
-- 内连接
select a.name as "角色名",b.name as "人物名"
from role_tb a inner join people_tb b on a.age = b.age;
-- 左连接
select a.name as "角色名",b.name as "人物名"
from role_tb a left join people_tb b on a.age = b.age;
-- 右连接
select a.name as "角色名",b.name as "人物名"
from role_tb a right join people_tb b on a.age = b.age;
-- 正则表达式
create table `regex_tb`(lan text(100))engine=innodb default charset=utf8;
insert into `regex_tb` values
("不觉得换气声很像那种无助的抽泣声吗...."),
("年轻的时候什么都奢侈，只有爱情最廉价，等到了曾经的奢侈品你觉得廉价的时候，爱情却成了最奢侈的奢望……"),
("给我520个赞，我就能和她在一起了"),
("「你就是我的晴天[爱心]」"),
("马丁一点都没有进步，唉，每年都是老样子，这孩子一点长进都没有，都连续三年第一了");
select * from `regex_tb` where lan regexp "^不";
select * from `regex_tb` where lan regexp "……$";
select * from `regex_tb` where lan regexp "了";
select * from `regex_tb` where lan regexp "一起|抽泣";
-- 索引
create index `index_id` on `role_tb`(`id`);
