学习韩顺平老师mysql的笔记

学习韩顺平老师MySQL的笔记:

总结:

• MySQL其实是由java程序进行控制住的,这其中是有一个DBMS管理系统执行的,管理系统底下又分为多个数据库,数据库下面有很多的表.

二、sql语句的分类

1. DDL数据定义语句 [create 表 库]
2. DML 数据操作语言 [insert update delete]
3. DQL 数据查询语言 [select]
4. DCL 数据控制语言 [ grant revoke]

三、创建数据库

create datebase [if not exists ]

1. character set 指定数据库采用的字符集,默认为utf8
2. collecte 指定数据库字符集的校对规则 常见的是utf8_b[区分大小写] utf_general_ci[不区分大小写] 注意:默认是不区分大小写的
3. 在指定数据类型时,默认为是有符号的

四、查看删除数据库

显示删除数据库

show databases

显示数据库的创建语句

show create database db_name

五、恢复备份数据库

• 备份数据库
• mysqldump -u 用户名 -p -B 数据库1 数据库2 > 文件名.sql
• 恢复数据库
• source 文件名.sql

六.修改表

alter table tab_name add field datatype

• modify 改变表的结构

七.update的使用细节

• update 可以用来修改表中的数据

八.count统计函数

1. count(*):返回所有满足条件的记录行数
2. count(clo) 统计满足条件的记录行数,但是会排除为null的情况

九.字符串相关函数

• charset 返回字串字符集
• concat 连接字符
• instr(string,substring)返回substring在string中出现的位置
• ucase 转换成大写
• lcase 转换成小写
• left(string,length)从string的左边取length个长度

十.和数学相关的函数

• ABS 绝对值
• bin 十进制转二进制
• ceiling 向上取整

十一.和日期相关的函数

• current_date() 当前日期
• current_time() 当前时间
• current_timestamp() 当前时间戳
• datediff(date1,date2) 两个日期相差多少天

十二.加密和系统函数

• MD5(str)为字符串算出一个32位的字符串

十三.流程控制函数

• if(expr1,expr2,expr3)
• ifnull(expr1,expr2) 如果不为空 返回expr1 如果为空 返回expr2

十四.分页查询

select * from table

​ order by field

​ limit 每行显示的页数 * (第几页 - 1),每行的页数

十五.数据分组的总结

• 如果select语句中同时包含有group by having,limit order by,那么他们的顺序是 group by having,order by,limit

十六.自连接

• 定义:自连接是指在同一张表的连接查询
• 注意需要给表取别名,列名 as 列的别名

十七.MySQL子查询

• 子查询是嵌套在其他的sql语句,也叫嵌套查询

十八.子查询当做临时表使用

十九.在多行子查询中使用any 和all 操作符

二十.表的复制(蠕虫复制)

• 把一张表的记录复制到另一张表里面:
  insert into tab_name(field…) select from my_table

• 自我复制

  insert into table_name select from my_table

• 复制表的结构

  create table table_name like my_table

• 如何删掉一张表的重复记录:

  1. 先创建一张新表复制原表的结构

  2. 把原表的记录通过distinct 关键字,处理后,复制到新表上

  3. 清除掉表中原有的数据(保留结构)

  4. 把新表的记录复制到原表

  5. 删除新表

二十一.合并查询

• 有时候,为了合并多个select语句的结果,可以使用集合操作符号 union unionall
• unionall:

1. 该操作符用于取得两个结果集的并集,当使用该操作符时,不会取消重复行

• union:

2. 该操作符和unionall类似,但是会自动去掉一些结果集中的重复行

二十二.表的外连接

1. 左连接:左边的位置完全显示
2. 右连接:右表的位置完全显示
3. 语法:select …from 表一 right join 表二 on 条件

二十三.mysql约束

• 内容包括:not null,unique, primary key, foreign key, check
• primary key:

1. 不能重复而且不能为null
2. 一张表只能有一张主键,但是可以有复合主键

• not null :定义了非空,插入数据时,就必须提供列数据

• unique:定义了唯一约束后,该列值不能重复

二十四.外键

• 定义:

  用于定义表和表之间的关系,外键约束必须定义在表上,主表则必须具有主键约束,或是unique约束

• 细节说明:

  1. 外键指向表的字段,要求是primary key 或是 unique
  2. 表的类型是innodb,才会支持外键
  3. 外键字段的类型要和主键的类型一致
  4. 创建外键需要在创建表的时候进行外键的指定

• 语法:

  ​ foreign key() references db_name(field)

二十五.check

• 用于强制列数据必须满足的条件,假定在某个列上定义

二十六.自增长类型

• auto_increment

二十七.索引(用空间换时间,极大地提高了数据库的查询效率)

• 语法:

  ​ create index index_name on db_name(field)

  ​ alter table tab_name add index (field)

• 索引类型:

  1. 主键索引:主键自动就为主索引
     2. 唯一索引:unique
     3. 普通索引:index
     4. 全文索引:fullindex

• 删除索引:

  ​ drop index index_name on db_name(field)

• 删除主键索引:

  ​ alter table tab_name drop primary key

• 查询索引:

  ​ show index(index_name) from table_name

  ​ show keys from table_name

二十八.事务和锁

当执行事务操作(DML) MySQL会在表上加锁,防止其他用户改表的数据

几个重要的操作:

1. start transaction 开始一个事务
2. save point 保存点名 ——设置保存点
3. rollback to 保存点名 —— 回退事务
4. rollback——回退全部的事务
5. commit ——提交事务，所有的操作生效，不能回退

细节点

1. 如果不开始事务，默认情况下，dml是自动提交的，不能够进行回退
2. 如果开始一个事务，你没有创建保存点，你可以执行rollback，默认回退到事务开始的起点
3. 你也可以在这个事物中，创建多个保存点
4. MySQL的事务机制需要innodb存储引擎才可以使用，myisam不好使

二十九.查看事务的隔离级别

• **脏读(dirty read)😗*一个事务读取到另一个事务尚未提交的改变

• **不可重复读(nonrepeated read)😗*同一个查询再同一个事务中多次进行,由于其他事务所做出的的改变或删除每次都会返回不同的结果,此时发生不可重复读

• **幻读:**同一查询再同一个事务中多次进行,由于其他事务所做的插入操作每次返回的结果集,发生了幻读

​

mysql隔离级别	脏读	不可重复读	幻读	加锁读
读未提交(Read uncommitted)
读已提交(Read committed)
可重复读(Repeatable read)
可串行化(Seriallzable)

• 查看当前会话的隔离级别:

  ​ select @@ tx_isolation

• 查看系统当前的隔离级别:

  ​ select @@ global,tx_isolation

• 设置当前会话的隔离级别:

  ​ set session transaction isolation leavel repeatabel read

• 设置系统当前的隔离级别:

  ​ set global transaction isolation leaval repeatable read

三十.存储引擎

1. myisam不支持事务,也不支持外键,但其访问速度快,对事务的完整性没有要求/
2. innodb 存储引擎提供了具有提交,回滚和崩溃恢复能力的事务安全,相比于myisam存储引擎,innodb写的处理差一些,并且会占用更多的磁盘空间已保留数据和索引.
3. memory存储引擎使用在内存中的内容创建表,访问速度非常快,因为他的数据时放在内存中的,并使用hash索引,一旦mysql服务关闭,表中的数据就会丢失掉,但是表的结构还在.

三十一.视图

• **视图:**视图是一张虚拟的表,其内容由查询定义同真实的表一样,视图包含列,其数据来自对应的真实表
• 视图的基本使用

1. create view view_name as select语句
2. alter view 视图名 as select语句 -----更新成表的视图
3. show create view
4. drop view

• 视图细节:

1. 创建视图后,到data目录下面去找.可以找到对应的视图文件(形式:文件名.sql)
2. 视图的数据变化会影响到基表,基表的数据变化也会影响视图

三十二 MySQL用户管理

• MySQL的用户,都储存在数据库的mysql的user的表中

• 创建用户:

  ​ create user 用户名@IP地址identied by 用户密码

• 删除用户:

  drop user 用户名@IP地址indentied by 用户密码

• 给用户授权:

  ​ grant 权限列表 on 库.对象名 to 用户名@登录位置

• 特别说明:

  ***.****是代表数据库的所有对象

  库.*表示某数据库的所有对象

• 回收用户授权:

  ​ revoke 权限列表 on 库.对象名 from 用户名@ 登录位置

储存在数据库的mysql的user的表中

• 创建用户:

  ​ create user 用户名@IP地址identied by 用户密码

• 删除用户:

  drop user 用户名@IP地址indentied by 用户密码

• 给用户授权:

  ​ grant 权限列表 on 库.对象名 to 用户名@登录位置

• 特别说明:

  ***.****是代表数据库的所有对象

  库.*表示某数据库的所有对象

• 回收用户授权:

  ​ revoke 权限列表 on 库.对象名 from 用户名@ 登录位置