mysql数据库学习（基础篇）_数据库基础学习

mysql数据库学习（基础）

题记：将分为入门（基础、操作）、进阶（底层实现原理学习、优化）进行学习笔记总结。本文为第一阶段，基础部分。

定义：

数据库概念（DataBase，DB）

存储数据的仓库，数据是有组织的进行存储。

数据库管理系统（DBMS）

操纵和管理数据库的大型软件

linux 命令行打开数据库：

service mysql start       打开数据库服务
mysql -u root -p         登陆数据库，输入密码就可进入mysql
在数据库中输入    quit;            退出数据库
1
2
3

SQL（重点）

操作关系型数据库（ORACLE、MYSQL等）的编程语言，定义了一套操作关系型数据库统一标准。

SQL通用语法

1. SQL可以单行或多行书写，以分号结尾；

2. SQL语句可以使用空格/缩进来增强语句可读性

3. MySQL的SQL语句不区分大小写，（故关键字也要注意大小写不区分问题，一般大写）；

4. 注释：

​ 单行 ： --/#（MySql独有#）

​ 多行 ： /* */(和C++一样)

SQL分类：

DDL数据库操作：

查询所有数据库： SHOW DATABASES;

创建数据库：CREATE DATABASE[IF NOT EXIST] +数据库名字+[字符集]+[排序规则]

注意：[ ]内为可选，如果加上IF NOT EXIST，则创建时，名字重复就不会报错，也不会创建。

删除数据库:

DROP DATABASE[IF EXIST]+数据库名字；

如果加上IF EXIST,数据库不存在就不报错，也不执行删除操作。

使用数据库：

USE+数据库名；

查询当前所处数据库：

SELECT DATABASE();

表操作：

查询当前数据库所有表：
SHOW TABLES；

查询表结构：

DESC 表名

查询指定表的建表语句

SHOW CREATE TABLE 表名

创建表：

CREATE TABLE 表名

(

字段1 字段1类型[COMMENT 字段1注释],

字段2 字段2类型[COMMENT 字段2注释],

字段2 字段2类型[COMMENT 字段2注释]

)[COMMENT 表注释]

注意最后一个字段后面没有逗号。

表的数据类型

字符串类型：

char(长度)；定长字符串，性能好
例子：char（50），长度固定50;
varchar(长度)；变长字符串，性能较差
例子：varchar(50),长度不固定，最大50；
TINYTEXT 
存放最大长度为255 个字符的字符串
TEXT 
存放最大长度为65,535 个字符的字符串
1
2
3
4
5
6
7
8

数值类型

//类似于C++
TINYINT 占用1byte；小整数值
SMALLINT       2byte
MEDIUMINT   3	byte		
INT                      4	byte		大整数（相当于C++中的int）
BIGINT               8 byte       极大整数（相当于C++中的 long long）
FLOAT                 4	byte		单精度
DOUBLE             8   byte     双精度
后面加unsigned        无符号

此外还有日期类型（不展示了）
DATE//大小位3，描述日期值

1
2
3
4
5
6
7
8
9
10
11
12
13

创建一个表示例：

create  table  scores_table(
id int  comment '编号',
name varchar(16) comment '姓名',
score tinyint unsigned comment '成绩'
) comment  '成绩表';
1
2
3
4
5

表操作-修改

添加字段
ALTER TABLE 表名  ADD 字段名 类型(长度)[COMMENT 注释] [约束];
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度)
修改字段名和字段类型
ALTER TABLE 表名  CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释] [注释];
删除字段
ALTER TABLE 表名 DROP 字段名
修改表名 
ALTER TABLE 表名 RENAMR TO 新表名
1
2
3
4
5
6
7
8
9
10

表修改例子：

ALTER TABLE scores_table ADD teacher varchar(10) comment'老师'
1

删除表

//删除表
DROP TABLE[IF EXISTS]表名;
//删除表，并重新创建表
TRUNCATE TABLE 表名;
1
2
3
4

DML

主要分为：添加数据、修改数据

添加数据

给指定字段添加数据

INSERT INTO 表名 (字段名1，字段名2，……) VALUES (值1,值2,值3……);//插入时，字段与值类型需要一一对应
给全部字段添加数据
INSERT INTO 表名 VALUES (值1,值2,值3……);
例子
INSERT INTO scores_table (id,name,score,teacher) VALUES(1,'xiaoming',78,'Mr.wang');
INSERT INTO scores_table (id,name,score,teacher) VALUES(1,'xiaojun',78,'Mr.wang');
INSERT INTO scores_table (id,name,score,teacher) VALUES(1,'xiaohong',78,'Mr.wang');
1
2
3
4
5
6
7
8
9

修改数据

UPDATE 表名 SET 字段名1=值1 字段名2=值2，……[WHERE 条件]
不带条件，会改变整个表内容。
例子：
UPDATE scores_table set  score=96 where name='xiaojun';
UPDATE scores_table set  id=2 where name='xiaojun';
UPDATE scores_table set  id=3 where name='xiaohong';
UPDATE scores_table set  score=59 where name='xiaohong';
1
2
3
4
5
6
7

删除数据

delete from 表名 [WHERE 条件]
条件规定删除哪些部分
1
2

DQL

DQL数据查询语言：主要用来查询数据库中表的记录。

查询关键字：SELECT；

SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表//分组之后国旅
ORDER BY
排序字段列表 [排序方式：desc/asc，反序/顺序]
LIMIT
分页参数//每页展示的记录数
1
2
3
4
5
6
7
8
9
10
11
12
13
14

基本查询

查询表的多个字段

SELECT 字段1,字段2…… FROM 表名
SELECT  * FROM 表名//查询表所有字段
SELECT 字段1,字段2…… FROM 表名 WHERE 条件	…………
SELECT  * FROM 表名 WHERE 条件	 …………
1
2
3
4

例子：
SELECT  name,score from scores_table where score>=60;
1
2

DQL执行顺序：from----where----group by-----having-----select-----order by----limit

SELECT  st.name  n,st.score s from scores_table st where score>=60 order by s desc;
SELECT  st.name  n,st.score s from scores_table st where s>=60 order by s desc;//这个指令会报错，因为where先执行，select之前，没有s的定义
1
2

DCL（简略）

DCL数据控制语言，管理数据库用户以及控制数据库的访问权限。(主要是运维和数据库管理员使用)

创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
例子：
create user 'qb'@'localhost' IDENTIFIED BY '1234';
删除用户
DROP USER '用户名'@'主机名';
1
2
3
4
5
6

函数（略）：

一般指一段可以直接被另一段调用的程序和代码

字符串函数、数值函数、日期函数、流程函数

约束：

作用于表中字段的规则，用于限制存储在表中的数据，保证数据库中数据的有效、完整、正确性。

分类：

（黑马程序员视频截图）

 create table scorebiao(
    id int primary key auto_increment comment '学号',
    name varchar(10) not null unique comment '姓名',
    age int comment '年龄',
    gender char(1) default '?' comment '性别',
    score int comment '分数'
    )comment '成绩表';
    //check，用不了,版本用的是5.7的
    

1
2
3
4
5
6
7
8
9
10

插入数据：
insert into scorebiao (name,age,score) values('xiaozhang',9,88);//没有gender，默认‘？’
insert into scorebiao (name,age,gender,score) values('xiaohong',9,'0',88),('xiaoming',10,'1',93);
insert into scorebiao (name,age,score) values('',9,88);//此处会报错，因为name要求非空
1
2
3
4

编写是发现varchar不能给中文：

解决方法：
创建表的时候设置 charset = utf8;  可用DDL语句运行。
1
2

外键约束：

用来让两张表建立连接，保证数据的一致性和完整性。

特点：

1.外键可以唯空，但如果不为空，该键值必须等于另一个表中主键的某个值。

2.外键不能是本表的主键，但可以为其他表的主键。

3.主从表定义：

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

4.外键定义规则：

主表需要已存在于数据库中，或者是当前创建的表，如果是当前创建的表，则主表与从表是同一个表，这样的表叫做自参照表。

主表必须定义主键，且不能为空；//当有两个表有关联时，只删除主表就会报错，原因就是子表里还存在与主表主键关联的外键。

外键列的数量、列中的数据类型必须与主键中的一致。

添加外键：

语法：
1.可以直接在建立表的时候设置外键
create table(
……
 CONSTRAINT 外部键名称 FOREIGN KEY（外部键字段名称） REFERENCES 主表（主列表名),
……
)……
2.在创建表之后，用来添加外键;
ALTER TABLE 表名 ADD CONSTRAINT 外部键名称 FOREIGN KEY（外部键字段名称） REFERENCES 主表（主列表名)
1
2
3
4
5
6
7
8
9

例子：

给之前的scorebiao增加外部键，做好准备
ALTER TABLE scorebiao ADD classid int comment'班级号';
UPDATE scorebiao set  classid=3 where name='xiaozhang';
UPDATE scorebiao set  classid=2 where name='xiaohong';
UPDATE scorebiao set  classid=1 where name='xiaoming';
classes 作为主表。
create table classes(
 id int primary key auto_increment comment '班级号',
  name varchar(15) not null comment '老师名字'
) character set = utf8 comment '班级管理表';
insert into classes (id,name) VALUES (1,'王老师'),(2,'李老师'),(3,'马老师');

添加外键
ALTER TABLE scorebiao ADD CONSTRAINT waibujian FOREIGN KEY (classid) REFERENCES classes(id);//这里不演示了
1
2
3
4
5
6
7
8
9
10
11
12
13
14

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

外键：设置删除/更新行为，（在定义外键时使用）

ALTER TABLE 表名 ADD CONSTRAINT 外部键名称 FOREIGN KEY（外部键字段名称） REFERENCES 主表（主列表名) on update (更新行为) on delete (删除行为)
1

多表查询

多表关系

关系：

一对多：使用外键可以实现，多的一方设置外键，一的一方设置主键。

多对多：通过建立中间表来实现，中间表至少包含两个外键，与两个表的主键相连。

一对一(常用于单表拆分，基础字段放一张表中，其他详细字段放入另一张表中)：在任意一方，加入一个外键，关联另一方的主键（主键设置为unique）

多表查询：（笛卡尔积）

多表查询，需要消除无效的笛卡尔积

笛卡尔积：令A和B是任意两个集合，若序偶的第一个成员是A的元素，第二个成员是B的元素，所有这样的序偶集合，称为集合A和B的笛卡尔乘积或直积，记做A X B。

序偶：带顺序的集合

例子：A={a1,a2,a3,……an};B={b1,b2,b3……bn}

AXB={(a1,b1),(a2,b2),…………(an,bn)}

select * from 表名;//单表查询
多表查询
select * from 表1名称,表2名称where  条件;//条件去消除无效的笛卡尔积
1
2
3

例子

select * from scorebiao,classes;//没有消除无效的笛卡尔积
1

select * from scorebiao,classes where scorebiao.classid=classes.id;//消除无效的笛卡尔积
1

多表查询-连接查询

内连接，外连接区别：

内连接就是两个表的交集 ,左外连接就是左边表+两表交集 ,右外连接就是右边表+两表交集。

下面链接中的图更形象一些。

http://t.csdn.cn/F1mmt（详细参考）

**内连接：**用左边表的记录去匹配右边表的记录

   隐式：SELECT 字段列表 FROM 表1,表2 WHERE 条件……;
	显式：SELECT  字段列表  from 表1 [INNER] JOIN    表2 ON  连接条件；
	显式例子：
	SELECT   * from scorebiao  JOIN classes on scorebiao.classid=classes.id;
1
2
3
4

结果其实与隐式（上个代码例子结果）一样。

内连接查询步骤：

1. 确定查询哪些表
2. 确定表连接的条件
3. 确定查询的条件
4. 确定查询的字段

外连接：

左外连接：用左边表的记录去匹配右边表的记录，如果符合条件的则显示（保存左边表+交集）

SELECT  字段列表  from 表1  LEFT [OUTER] JOIN 表2 ON 条件
1

右外连接：用右边表的记录去匹配左边表的记录，如果符合条件的则显示(保存右边表+交集)

SELECT  字段列表  from 表1  RIGHT [OUTER] JOIN 表2 ON 条件
1

自连接：

自连接查询就是当前表与自身的连接查询

比如将一个表中有员工和老板，查询老板与员工从属关系：

代码模板：

SELECT  字段列表  from 表1   别名a join  表1   别名b ON 条件（比如a.id=b.sid）
//注意一定要别名,因为就一个表（自己）
1
2

**联合查询（**略）

定义：将多次查询(多条select语句), 在记录上进行拼接(字段不会增加)

子查询:（又称为查询语句）（略）

定义： 查询是在某个查询结果之上进行的

http://t.csdn.cn/DJ9CL（详细见该链接）

主要包含：标量子查询、列子查询、行子查询、表子查询：

标量子查询：子查询返回的结果是一个数据（一行一列）

列子查询：返回的结果是一列（一列多行）

行子查询：返回的结果是一行（一行多列）

表子查询：返回的结果是多行多列（多行多列

事务

事务是一组操作的集合，是一个不可分割的工作单位，事务会把所有的操作作为一个整体向系统提交或撤销操作请求。

特点：

​ 1.事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL组成。

2. 事务是访问并更新数据库中各种数据项的一个程序执行单元。

3. 事务中的操作，要么全部成功，要么全都不成功。

   4.事务必须同时满足四个特性：原子性、一致性、隔离性、持久性：

**原子性：**指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个SQL语句执行失败，已经执行成功的SQL必须撤销，数据库状态应该退回到事务前的状态。

**一致性：**指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

事务是一致性的状态，如果事务中某个动作失败了，系统可以自动撤销事务 — 返回初始化的状态

**事务隔离性：**要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，通常使用锁来实现。

**持久性：**事务一旦提交，其结果就是永久性的。即使发生宕机等故障也能恢复。

个人理解事务作用：当有一系列操作代码提交时，若中途出现错误，错误后的操作不能执行，但前面的却执行完毕，这往往会导致很多的麻烦。将这一系列操作集合为一个事务，将作为一个整体，要么全部执行成功，要么全部失败。

//查看/设置事务的提交方式
SELECT @@autocommit;
SET @@autocommit=0;//设置关闭自动提交，之后就是手动操作开启。
//开启事务
START TRANSACTION 
//提交事务
commit;
//回滚事务
rollback;
1
2
3
4
5
6
7
8
9

并发事务问题

1.脏读(dirty read)：如果第二个事务查询到第一个事务还未提交的更新数据，形成脏读。
2.虚读(phantom read)/幻读：一个事务执行两次查询，第二次查询比第一次多出或少一些数据，造成两次结果不一致。只是另一个事务在这两次查询中间插入或者删除了数据造成的。
3.不可重复读(unrepeated read)：一个事务两次读取同一行数据，结果得到不同状态结果，如中间正好另一个事务更新了该数据，两次结果相异，不可信任

针对以上问题，通过设置事务隔离级别来解决。

以MYSQL数据库为例，事务隔离级别：

y:可以解决；n：不能解决

隔离级别查看：

SELECT @@tx_isolation;
1

第一种隔离级别：Read uncommitted(读未提交)
如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。

特点：一写多读，写的时候，其他事务也能读

却可能出现脏读，也就是说事务B读取到了事务A未提交的数据，出现不可重复读问题，B同时读取了A提交前后某一行数据，数据不一致，也避免不了幻读问题，在此就不举例了。

第二种隔离级别：Read committed(读提交)
如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

特点：一个事务写这个表时，其它事务不可读，只能读写后的。

只解决了脏读问题

第三种隔离级别：Repeatable read(可重复读取)
可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读（即一个事务在进行读某一行数据时，另一个事务对这行数据所在表进行了数据条数的增减，导致查询结果不一致）。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。

特点：一个事务执行结束前，同一行数据只能在一个事务内读写，其他事务不能操作。

解决了脏读、不可重复读，但是还会出现幻读

第四种隔离级别：Serializable(可序化)
提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行。

特点：只能一个事务来读写这个表，其他事务没有读写权限。

不仅可以避免脏读、不可重复读，还避免了幻读

补充：以上四种级别，在实际效率方面，由高到低，也就是说安全级别高，也会导致效率的下降。

事务底层原理，在进阶部分学习。

至此，基础篇完结。