Mysql学习笔记

数据库分类

操作数据库

创建数据库

create database if not exists hello

删除数据库

drop database if exists hello

查看数据库

show database ---查看所有数据库

使用数据库 

use `hello`

 数据库的列类型

 

数据库的字段属性 （重点）

 

创建数据库表

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 '性别',
PRIMARY KEY(`id`)
)engine=INNODB DEFAULT charset=utf8

数据库引擎 

• InnoDB 支持事务；MyISAM 不支持事务
• InnoDB 支持行级锁；MyISAM 支持表级锁
• InnoDB 支持 MVCC(多版本并发控制)；MyISAM 不支持
• InnoDB 支持外键，MyISAM 不支持
• MySQL 5.6 以前的版本，InnoDB 不支持全文索引，MyISAM 支持；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
• InnoDB 不保存表的总行数，执行 select count(*) from table 时
  需要全表扫描；MyISAM 用一个变量保存表的总行数，查总行数速度很快
• InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，
  通过主键索引效率很高。辅助索引需要两次查询，先查询到主键，再通过主键查询到数据。主键太大，其他索引也会很大；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的

总结：

• InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引
• MyISAM 不支持事务、也不支持外键，优势是访问的速度快。对事务的完整性没有要求、以 SELECT 和 INSERT 为主的应用可以使用这个存储引擎

修改删除表

修改

删除

Mysql数据管理 

外键

不建议使用外键

DML语言（重点）

插入

create table  if not EXISTS `grade`(
`gradeid` int(4) not null Auto_INCREMENT ,
`gradename` varchar(20) not null default '匿名',
primary key(`gradeid`)

)engine=INNODB default charset=utf8
--报错
insert into `grade`(`gradename`) values('大三')--报错，字段不匹配
--插入多条数据，每一个数据都有单独的括号
insert into `grade`(`gradename`)  values ('大一'),('大二'),('大四')

语法：insert into table (字段一，字段二，字段三) values(' a' ,'b','c'),(' e' ,'f','g')

修改 

删除 

语法：delete form tablename where 字段值=x;

TRUNCATE命令

语法 truncate tablename

DQL查询语言（重点）

 去重

where条件

模糊查询

联表查询

左连接返回左表所有的值，即使右表没有匹配。

右连接。grade所有的值都被返回了，即使左表中没有对应的学生。

自连接

分页和排序

MySql函数

常用函数

聚合函数 

 

分组和过滤

 

 MD5加密

事务

事务具有四大特性（ACID）：

• 原子性(Atomic)：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样
• 一致性(Consistency)：在事务开始之前和事务结束以后， 数据库的完整性没有被破坏
• 隔离性(Isolation)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
• 持久性(Durability)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

事务控制：

• BEGIN 或 START TRANSACTION 显式地开启一个事务
• COMMIT 会提交事务，使已对数据库进行的所有修改成为永久性的
• ROLLBACK 回滚，会结束用户的事务，并撤销正在进行的所有未提交的修改
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，没有指定的保存点执行该语句会抛出一个异常
• ROLLBACK TO identifier 把事务回滚到标记点
• SET TRANSACTION 用来设置事务的隔离级别

MYSQL 事务处理的方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

• BEGIN 开始事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2、直接用 SET 来改变提交模式:

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

注意：

• MySQL 的事务控制是表引擎上处理，有些引擎是不支持事务的
• 不支持事务的表上执行事务操作，MySQL不会发出提醒，也不会报错

索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

使用索引目的是加快检索表中数据

使用场景：

• 中到大数据量表适合使用索引
• 小数据量表，大部分情况全表扫描效率更高
• 特大数据量表，建立和使用索引的代价会随之增大，适合使用分区或分库

创建和删除索引

• 创建单个字段索引的语法：CREATE INDEX 索引名 on 表名(字段名)
• 创建联合索引的语法：CREATE INDEX 索引名 on 表名(字段名1，字段名2)
• 索引命名格式一般可以这样：idx_表名_字段名。注意有长度限制
• 删除索引：DROP INDEX 索引名 ON 表名

如：

给 id 创建索引：CREATE INDEX idx_t1_id on t1(id);

给 username 和 password 创建联合索引：CREATE index idx_t1_username_password ON t1(username,password)

index 替换成 unique 或 primary key，分别代表唯一索引和主键索引

索引原则