MySQL入门

数据库学习：https://blog.csdn.net/weixin_39778570/article/details/95066091
Java(持续更新):https://blog.csdn.net/weixin_39778570/article/details/94667501

权限级别

可查看这篇文章：SQL语句，数据库配置，JDBC使用

数据库级别操作

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ -- 创建数据库
SHOW CREATE DATABASE test; -- 输入test数据库如何创建
DROP DATABASE test; -- 删除数据库
SHOW DATABASES; -- 显示所有数据库
USE test; -- 使用test数据库,之后的语句就会在该数据库下执行

表级别操作

-- 创建表
CREATE TABLE IF NOT EXISTS `tbl`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL,
   `author` VARCHAR(40) NOT NULL,
   `date` DATE,
   PRIMARY KEY ( `id` )
   -- CONSTRAINT `FK_ID` FOREIGN KEY (`author`) REFERENCES `tb_book` (`author`) -- 外键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
SHOW CREATE TABLE tbl -- 查看该表的创建语句
DROP TABLE tbl -- 删除表
-- 创建索引
CREATE INDEX title_index ON tbl(title(100));
-- 添加索引,ALTER为修改语法
ALTER TABLE tbl ADD INDEX author_index(author) 
-- 删除索引
DROP INDEX title_index ON tbl; 
-- 创建唯一索引,允许空置,但不允许重复
CREATE UNIQUE INDEX title_index ON tbl(title(100)) 
-- 改变表的方式添加唯一索引
ALTER table 表名 ADD UNIQUE [索引名] (列名(长度))
-- CREATE INDEX 和 ALTER table 的区别
-- CREATE INDEX必须提供索引名,ALTER可不提供,mysql会自动创建
-- ALTER可以添加主键,CREATE不可以
ALTER TABLE tbl MODIFY id INT NOT NULL; -- 添加一列
ALTER TABLE tbl DROP PRIMARY KEY; -- 删除主键
ALTER TABLE tbl ADD PRIMARY KEY (id); -- 添加主键
-- 外键
CREATE TABLE IF NOT EXISTS `book`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `author` VARCHAR(40) NOT NULL,
   `date` DATE,
   `r_id` INT,
   PRIMARY KEY ( `id` )
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 创建外键,不指定索引名,索引名为fk_name_id
ALTER TABLE book ADD FOREIGN KEY fk_name_id(r_id) REFERENCES tbl(id)
SHOW CREATE TABLE book --可以知道外键名
-- 创建外键的时候, 是先创建了索引,再关联两个索引成为外键的
-- 指定外键(约束)名为FK_Name_id
ALTER TABLE book ADD CONSTRAINT FK_Name_id FOREIGN KEY book(r_id) REFERENCES tbl(id);
ALTER TABLE book DROP FOREIGN KEY FK_Name_id -- 删除外键
-- 级联,当我们没有级联的时候,删除和修改关联起来的索引是会出错的
-- 级联起来,索引修改则一起修改,删除则一起删除,no action是不开起,cascade是关联
ALTER TABLE book ADD CONSTRAINT FK_Name_id FOREIGN KEY book(r_id) REFERENCES tbl(id) 
ON UPDATE CASCADE ON DELETE NO ACTION;

语句级别操作

-- 增
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN ),( value1, value2,...valueN );
-- 对于表里已有数据的处理,上面会报错,采用下面的方式
INSERT IGNORE INTO table_name (last_name, first_name)
VALUES( 'Jay', 'Thomas'); -- 忽略改操作,不插入也不报错
INSERT REPLACE INTO table_name (last_name, first_name)
VALUES( 'Jay', 'Thomas'); -- 采用替换插入的方式
-- 复制表
INSERT INTO tbl(author, count)
SELECT NAME,singin
FROM employee_tbl
-- 删
DELETE FROM table_name [WHERE id=x] -- 删除id=x的行
-- 改
UPDATE table_name SET id=3 WHERE id=2; -- 把id=2改为=3
-- 查
SELECT * FROM table_name WHERE name='张飞' -- 查询叫张飞的人
-- 子查询
SELECT name,score FROM studentscore 
WHERE score>(WHERE avg(score) FROM studentscore) ORDER BY score DESC

其他操作

连接：UNION
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。也可以省略 INNER 使用 JOIN
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

使用下菜鸟教程的数据

USE test1;
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;

内连接

-- 内连接
SELECT * 
FROM runoob_tbl a 
INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

当我们使用内连接的时候，是连接两个表的交集部分，那么他是怎么查询到交集部分的呢,是由b表向a表发起查询，所以为了提升速度，我们可以在a表建立索引(B/B+树实现),被建立索引的表查询速度非常快,而表b则是枚举遍历,速度很慢,所以我们设计语句的时候应该给a表添加索引,而a表通常是一个很小的表
以上的话可以理解为：b(驱动表)表向a表发起查询，b表小，a表大且索引
默认mysql会自动把小表作为驱动表的
左连接

SELECT * 
FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

注意：左连接是左表作为驱动表查询右表，左表要小，右表设置索引，左表是全表，查不到右表的情况下，右表设置为null
右连接

INSERT tcount_tbl VALUE ('菜鸟教程',11)
SELECT * 
FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;

注意：右连接和左连接是完全相反的，右表是驱动表
注意：这几种连接都是采用笛卡尔积的

事务
MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

CREATE TABLE runoob_transaction_test( id INT(5)) ENGINE=INNODB;
SELECT * FROM runoob_transaction_test;
BEGIN; -- 开始事务,默认是不开启的
INSERT INTO runoob_transaction_test VALUE(5);
INSERT INTO runoob_transaction_test VALUE(6);
COMMIT; -- 提交事务
SELECT * FROM runoob_transaction_test;
	id
	5
	6
BEGIN; -- 开始事务
INSERT INTO runoob_transaction_test VALUES(7);
ROLLBACK; -- 回滚事务
SELECT * FROM runoob_transaction_test;
	id
	5
	6
BEGIN; -- 开始事务
SAVEPOINT savepoint_name1; -- 设置保存点1
INSERT INTO runoob_transaction_test VALUE(9);
SAVEPOINT savepoint_name2; -- 设置保存点2
INSERT INTO runoob_transaction_test VALUE(10);
ROLLBACK TO savepoint_name2; -- 回滚到保存点1,这个时候已经提交了
SELECT * FROM runoob_transaction_test;
	id
	5
	6
	9

注：一般来说，事务是必须满足4个条件（ACID）：：
原子性（Atomicity，或称不可分割性）、一致性（Consistency）、
隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，
不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始
前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、
串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，
隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed
）、可重复读（repeatable read）和串行化（Serializable）。InnoDB默认是可重复读的

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。