MYSQL数据库笔记

二．MYSQL数据库

1. 数据库系统组成：数据库、数据库管理系统（DBMS）、应用系统和数据库管理员

（18.3.10）

2. 数据库的基本知识

DML（data manipulationlanguage）： 它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

DCL（Data Control Language）：是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

3.MySQL是关系型数据库：表与表之间存在关系

4.show databases;  ：在cmd中查询服务器有几个数据库

5.create database 数据库名：创建数据库

6.drop database （table） 数据库名（表名）; ：删除数据库（表）

7.use 数据库名;      ：使用数据库，显示如下结果

然后再使用   show tables;   ：显示数据库下有多少表

（18.3.11）

8.所有SQL语句必须加分号（MySQL语句有些可以不加）

9. VARCHAR(M)是一种比CHAR更加灵活的数据类型，同样用于表示字符数据，但是VARCHAR可以保存可变长度的字符串。

10. 通过新建查询建立表：

  

   写法顺序

   注：DEFAULT：设置默认值

      COMMENT：设置注释

      PRIMARY KEY：设置主键（主键的作用：表示数据的唯一性，便于搜索）

11. CREATE TABLE 表名1 like 表名2;   将已有的表2结构复制到表1

复制一个表，包括表结构以及表数据（可以设置复制部分数据，如下图第二种方法）

（18.3.12）

12. ALTER TABLE  原表名  RENAME  TO  新表名;   ：更换表名

   DROP  TABLE  表名;          ：删除表

   TRUNCATE  表名;           ：清空列表数据

13. 下图是表中字段的添加方法

 

字段的删除方法：ALTER  TABLE  表名  DROP  所删除字段名;

修改列定义

14.数据库三大范式

   1）一个字段必须具有单一属性，不可再分（比如name字段，如果有英文名字和中文名字，则分开写入两个字段）

   2）表必须具有唯一主键列

   3）表中不能出现其他表中非主键字段（如下图，表1中不能存在表2非主键字段）

15.mysql中索引：

相当于快捷方式，重新开辟一个空间，存放索引，往空间中存放某些常用字段的索引，使搜索起来更为方便

1）查询索引：     SHOW  INDEX FROM  表名;（默认查询表中主键）

2）添加索引：     CREATE INDEX  索引名  ON 表名(字段名)

   3）删除索引：     DROP INDEX  索引名  ON  表名;

16.插入操作

   1）普通插入操作

2）将一个表中的数据插入到另一个表中

17.表数据增删

1）表的更新 

3）表中数据的删除

（18.3.13）

18.去除重复数据

SELECT DISTINCT  字段名  FROM  表名;     （distinct不显示重复的）

19.列表排序 

 ASC：表示升序，默认不写也是表示升序   DESC：表示降序

书写格式如下

20.分页查询  

 LIMIT x,y：x代表起始位置，y表示显示多少行，图中第三列表示先排序，再查询

21. WHERE的基本用法（相当于if的用法）

22.模糊查询：

使用LIKE语句，%代表任意元素，无论个数多少     ‘_’也是代表任意元素，但是只能代表一位数

   如上图，第一行代表搜索名字开头为五的数据（该数据可能含有两个及两个以上的字），第二行代表搜索名字为五的数据，但是只能搜索数据为两个字的数据（一个下横杠代表一个字）

23.别名命名法

作用：可以用于显示搜索字段，使阅读更为方便

（18.3.14）

24.并列显示在一起 

 使用CONCAT(字段一,字段二……)

25.求和，最大值，最小值，平均值

26.计数函数

当count后面是‘*’或者‘1’时，默认查询表中最长列

27.内容加密

MD5()实现加密，无法进行解密，目前测试无法对int数据进行加密（可能是方法不对）

（18.3.15）

28.分组查询（查看数据出现的次数）

使用GROUP BY可以实现输出表中指定各个元素次数，该语句只能出现在WHERE语句之后，ORDER BY语句之前

HAVING语句与GROUP BY语句配套使用，目的是为了先过滤出有用的信息，然后再按要求进行分组

29.WHERE语句是行级过滤，会把不满足的剔除，剔除之后再分组

  与之对应的，HAVING语句是组级过滤，先分组，再过滤

30.交叉方式（组合两个表），内连接（组合两个表）

   显式交叉方式：SELECT* FROM表1 S CROSS JOIN 表2 AWHERE S.STUDENT_CLASS = A.STUDENT_CLASS;

   隐式交叉方式：SELECT* FROM 表1 S,表2 A WHERES.STUDENT_CLASS = A.STUDENT_CLASS;

两种交叉方式效果一样，都是组合两张表

   内连接：效果与交叉方式相同

31. 外连接（与前两种连接的区别：前两种连接中，如果前表中有后表无法匹配的数值，前表中的数据则不会显示在结果中）

   

———————————————————————————————————————

以上为交叉式连接显示结果

 

以上为外连接结果

外连接包括左外连接（LEFTJOIN）和右外连接（RIGHT JOIN），表示结果以左面无NULL和右面无NULL

32.合并结果集

UNION：不显示重复数据

 

 

UNION ALL：可以显示重复数据

 

 

（18.3.16）

33.子查询：

31、32中的交叉连接和内连接所出现的的结果集是一张虚拟表，不能当成一般的表使用，所以不能使用虚拟表进行相关查询，这时候就可以使用子查询进行相关查询

查询17级中的女生

 

S.*,A.STUDENT_GRADE    因为表一中STUDENT_CLASS与表二中STUDENT_CLASS发生冲突，所以定义的时候一定不能两个表同时出现相同的字段

                      查询比平均分高的同学

 

这也是用的子查询，形式上有所不同而已

34.视图：

（以表举例）将虚拟的表另存储为一张新表，当再次使用子查询查询时，就不用每次再次构建虚拟表，直接使用新表进行查询，但是实质是每次使用的时候，软件都要帮我们自动查询，我们所看到的视图中的表，其实是不存在的

 

VIEW代表视图的意思

35.搜索引擎：

MySQL用的是InnoDB储存引擎（在MySQL命令界面中show engines;查询）

36.事务：

有四个属性原子性、一致性、隔离性、持久性

事务主要用于某些过程，例如银行转账，或者某些数据的交接等等

 

 

 

事务：

      第一种方法，如果不关闭事务，继续修改年龄的值，也会受事务的影响

SET AUTOCOMMIT = FALSE;     事务的开启

COMMIT;                   提交

SET AUTOCOMMIT = TRUE;     事务的关闭

第二种方法，每次事务只会作用一次，一次过后则自动关闭

START TRANSACTION;或BEGIN;

COMMIT;

同时也还有几种方法可以关闭

ROLLBACK回滚

异常结束

用户回话正常结束

37.事务隔离四个等级

   1）Read uncommitted读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

   2）Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

 

   3）Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

   4）Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

38.悲观锁：就是比较悲观，人为别人访问数据都会修改数据，使用悲观锁就锁住了数据，不让别人看到，它是MySQL数据库的锁，它要与事务一起使用。

主要是控制mysql命令中的数据，如果点击commit，则不会出现下表

 

（18.3.17）

 

JDBC

1.DriverManager：表示加载驱动

 

1.  JDBC的事务，connection支持提供事务的属性和方法，JDBC的连接在关闭的时候是写入数据库的，所以我们必须给出回滚。

（18.3.18）