Mysql基础入门

Mysql基础：

1.操作数据库

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

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

查看数据库 : show databases;

查看正在使用的数据库：select database();

使用数据库 : use 数据库名;

2.创建表

-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库

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] `表名`(
  `字段名` 列类型 [属性] [索引] [注释],
  `字段名` 列类型 [属性] [索引] [注释],
  ...
  `字段名` 列类型 [属性] [索引] [注释]
)

常用命令

-- 查看数据库的定义
SHOW CREATE DATABASE school;
-- 查看数据表的定义
SHOW CREATE TABLE student;
-- 显示表结构
DESC student;  -- 设置严格检查模式(不能容错了)SET sql_mode='STRICT_TRANS_TABLES';

3.修改表

修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名

添加字段 : ALTER TABLE 表名 ADD字段名 列属性[属性]

修改字段 :

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]    //MODIFY不改变字段名，只改属性

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]  //CHANGE改字段名加属性

删除字段 :  ALTER TABLE 表名 DROP 字段名

修改字符集 ： ATLER TABLE 表名 character set 字符集

4.删除表

语法：DROP TABLE [IF EXISTS] 表名

IF EXISTS为可选 , 判断是否存在该数据表

如删除不存在的数据表会抛出错误

5.外键

ALTER TABLE 表名 ADD CONSTRAINT 约束名FK_XX FOREIGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)

6.Insert

INSERT INTO 表名 [字段名] VALUES (字段值)
如果不写字段名，字段值要包含所有字段，且一一对应


一次插入多个字段值
INSERT INTO 表名 [字段名] 
VALUES (字段值1),
(字段值1),
...

7.Update

UPDATE 表名 SET 要修改的列名=value 
[WHERE 条件表达式];

一次修改多个列
UPDATE 表名 SET 要修改的列名1,列名2...=value1,value2... 
[WHERE 条件表达式];

value可以是具体的值，也可以是变量
8.Delete、Truncate

DELETE FROM 表名 [WHERE 条件表达式]

TRUNCATE TABLE 表名
相当于删除表的结构，再创建一张表

-- 创建一个测试表
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('row1'),('row2'),('row3');

-- 删除表数据(不带where条件的delete)
DELETE FROM test;
结论:如不指定Where则删除该表的所有列数据,自增当前值依然从原来基础上进行,会记录日志.

-- 删除表数据(truncate)
TRUNCATE TABLE test;
结论:truncate删除数据,自增当前值会恢复到初始值重新开始;不会记录日志.

-- 同样使用DELETE清空不同引擎的数据库表数据.重启数据库服务后
-- InnoDB : 自增列从初始值重新开始 (因为是存储在内存中,断电即失)
-- MyISAM : 自增列依然从上一个自增数据基础上开始 (存在文件中,不会丢失)

DELETE FROM test;

再增加数据，主键接着增加。

TRUNCATE TABLE test;

9.简单Select
DQL语句执行顺序

select		5
	..
from			1	
	..
where			2
	..
group by		3
	..
having		4
	..
order by		6

查询所有
SELECT * FROM 表名

查询多个字段
SELECT 字段名1,字段名2,...FROM 表名

别名
SELECT 字段名1 AS 别名,字段名2 AS 别名,...FROM TABLE AS 别名;
AS可以用空格代替，但是不清晰

合并
SELECT CONCAT(a,b)AS 新名字 
FROM TABLE AS 别名;
SELECT CONCAT('姓名：',StudentName) AS 新名字 
FROM TABLE AS 别名;

去重
SELECT DISTINCT 字段名 FROM 表名

WHERE条件语句
比较运算符：>、<、<=、>=、=、<>(不等于)、!=(不等于)
逻辑运算符：and、or、not


**where 与having的区别？**
where:分组前将不符合where条件的过滤掉，where后面不能用聚合函数；
group by :当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
order by:分组；
having：分组后过滤数据，可以使用聚合函数。


模糊查询
关键字：
IN
LIKE
BETWEEN...AND
IS NULL、IS NOT NULL

通配符
%:匹配任意多个字符
_:匹配一个字符

10.连接查询

内连接 inner join
   查询两个表中的结果集中的交集
外连接 outer join
   左外连接 left join
       (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
   右外连接 right join
       (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
       
数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用left jion时，on和where条件的区别如下：
1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。  
inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

什么是外连接，和内连接有什么区别？

内连接：
	假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
	AB两张表没有主副之分，两张表是平等的。

外连接：
	假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
	的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

外连接的分类？
	左外连接（左连接）：表示左边的这张表是主表。
	右外连接（右连接）：表示右边的这张表是主表。

	左连接有右连接的写法，右连接也会有对应的左连接的写法。

11.子查询

在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字
子查询结果只要是单列，则在WHERE后面作为条件
子查询结果只要是多列，则在FROM后面作为表进行二次查询

12.聚合函数

MAX
MIN
AVG
COUNT
SUM
IFNULL(列名，默认值)：如果列名为NULL，给个默认值，这样统计的个数就不会遗漏

13.排序和分页

排序：ORDER BY 
ASC:升序
DESC:降序
分页
语法：limit 开始的索引，每页查询的条数
公式：开始的索引=（当前的页码-1）*每页显示的条数

14.约束

primary key:主键
default:默认
not null:非空
unique:唯一
foreign key:外键

15.三大范式

1NF：原子性，表中每列不可再分。比如，联系方式：邮箱加电话显然不合适，要分成两列。
2NF：在满足第一范式的情况下，表中的每一个字段都完全依赖于主键。不产生局部依赖，每个表只做一件事。比如，一张表里主键是学生证号码，而其中又有借书证名称和借书证号字段，显然借书证名称依赖于借书证，不依赖于主键。

3NF：在满足第二范式的情况下，不产生传递依赖，表中每一列都直接依赖主键，而不是通过其他列间接依赖于主键。
如学号 姓名 年龄 所在学院 学院地点
显然学号(主键)确定了，所在学院就确定了，所在学院确定了，学院地点就确定了。

16.事务ACID原则、事务的隔离级别

1.什么是事务？
实际开发过程中，一个业务操作需要多次访问数据库，执行多个SQL，把他们看作一个整体，即事务，整个事务的所有SQL执行成功，事务才算成功。事务的存在是为了保证数据的完整性，安全性。

2.事务的提交

Mysql默认开始自动提交事务，默认每一条DML（增删改）语句都是一个单独的事务，每条语句自动开启一个事务，语句执行结束自动提交事务。

DDL和DML

1、DML（Data Manipulation Language）数据操作语言-数据库的基本操作，SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有：select、update、delete、insert、merge。DML操作是可以手动控制事务的开启、提交和回滚的。开启事务，就是需要手动提交事务。

2、DDL（Data Definition Language）数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言，对数据库中的某些对象(例如，database,table)进行管理。包括的关键字有：create、alter、drop、truncate、comment、grant、revoke。DDL操作是隐性提交的，自动提交事务，不能rollback。

控制台查看是否开启自动提交事务：select @@autocommit;
@@表示全局变量，1表示开启，0表示关闭。
取消自动提交事务：`select @@autocommit=0；

手动提交事务：

start transaction;//开启事务

commit;//提交事务

rollback;//回滚事务

3.事务的原理

事务开启之后，所有的操作都会临时保存到事务日志之中，事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空日志（rollback,断开连接）。

所有的查询操作从表中查询，但会经过日志文件加工后才返回。

4.ACID原则

原子性(Atomicity):整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性(Consistency):事务必须是使数据库从一个一致性状态变到另一个一致性状态。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

**隔离性(Isolation)😗*主要是针对并发操作，一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

**持久性(Durability)😗*在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

5.事务的隔离级别

并发操作下，多个用户同时访问同一个数据库，可能引起并发访问的问题：

脏读：一个事务读取了另一个没有提交的事务的数据。

不可重复读(虚读)：在同一个事务内，读取读取表中的数据，表数据已发生改变，分不清到底用哪个数据。update引起。
我开启了一个事务，9点开启了一个查询，屏幕在那放着呢，10点别人把表改了(update)，我的查询结果也跟着变，分不清到底哪个是哪个。就是我不能重复查询9点钟那个表的状态了。

幻读：在同一个事务内，读取到了别人插入的数据，导致前后读取结果不一致。delete或者insert引起。我查询表中没有id为10的数据，想要插入，结果一插入，告诉我存在，别人插的，让我感觉到出现了幻觉。

Mysql有四种隔离级别：读未提交、读已提交、可重复读、串行化。四种隔离级别依次提高，性能随之越差，安全性越高。
read uncommitted->read committed->repeatable read(MYSQL)->serializable(串行化)

	事务隔离性存在隔离级别，理论上隔离级别包括4个：
		第一级别：读未提交（read uncommitted）
			对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
			读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
		第二级别：读已提交（read committed）
			对方事务提交之后的数据我方可以读取到。
			这种隔离级别解决了: 脏读现象没有了。
			读已提交存在的问题是：不可重复读(虚读)。
		第三级别：可重复读（repeatable read）
			这种隔离级别解决了：不可重复读问题。
			这种隔离级别存在的问题是：读取到的数据是幻象。
		第四级别：序列化读/串行化读（serializable） 
			解决了所有问题。
			效率低。需要事务排队。
		
		oracle数据库默认的隔离级别是：读已提交。
		mysql数据库默认的隔离级别是：可重复读。

17.视图

1、什么是视图？
	站在不同的角度去看到数据。（同一张表的数据，通过不同的角度去看待）。

2、怎么创建视图？怎么删除视图？
	create view myview as select empno,ename from emp;
	drop view myview;

	注意：只有DQL语句才能以视图对象的方式创建出来。

3、对视图进行增删改查，会影响到原表数据。（通过视图影响原表数据的，不是直接操作的原表）
可以对视图进行CRUD操作。

4、面向视图操作？
	mysql> select * from myview;
	+-------+--------+
	| empno | ename  |
	+-------+--------+
	|  7369 | SMITH  |
	|  7499 | ALLEN  |
	|  7521 | WARD   |
	|  7566 | JONES  |
	|  7654 | MARTIN |
	|  7698 | BLAKE  |
	|  7782 | CLARK  |
	|  7788 | SCOTT  |
	|  7839 | KING   |
	|  7844 | TURNER |
	|  7876 | ADAMS  |
	|  7900 | JAMES  |
	|  7902 | FORD   |
	|  7934 | MILLER |
	+-------+--------+

	create table emp_bak as select * from emp;
	create view myview1 as select empno,ename,sal from emp_bak;
	update myview1 set ename='hehe',sal=1 where empno = 7369; // 通过视图修改原表数据。
	delete from myview1 where empno = 7369; // 通过视图删除原表数据。

5、视图的作用？
	视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员
	只对视图对象进行CRUD。