MYSQL学习笔记（一）

MYSQL学习笔记（一）

sql语言的分类

DDL

数据定义语言 Data Definition Language。DDL用于数据库、表、视图等的建立、删除。DDL包括CREATE、 ALTER、 DROP等。

DML

数据操纵语言 Data Manipulation Language。DML用于添加、删除和修改数据表中的记录。DML包括INSERT、 DELETE和UPDATE。

DQL

数据控制语言 Data Control Language。DCL包括数据库对象的权限管理和事务管理。DCL包括COMMIT、 ROLLBACK、 GRANT等 。

DCL

数据查询语言 Data Query Language。查询是数据库的基本功能。DQL中使用SELECT查询数据表中的记录 。

MySQL支持的存储引擎

存储引擎就是如何存储数据、如何更新数据、如何查询数据、如何为存储的数据建立索引等一系列技术的实现方法。

存储引擎	描述
myisam	拥有较快的插入、查询速度，但不支持事务
innodb	支持ACID事务，支持行级锁，支持外键；MySQL5.5版本之后的默认存储引擎
MRG_MYISAM	将一组结构相同的MyISAM表聚合成一个整体，在进行增删改查操作
memory	所有数据存储在内存中，响应快；MySQL重启时数据全部丢失
archive	归档，且有压缩机制，适用于历史数据归档
CSV	逻辑上由逗号分隔数据，会为每张表创建一个.csv文件

MySQL数据类型

在创建表时，准确的定义字段的数据类型是非常重要的。 MySQL的数据类型大致可以分为3类：数值、日期/时间和字符串类型。

数值类型

类型	所占字节数	说明
tinyint	1	小整数值，如状态
smallint	2	大整数值
mediumint	3	大整数值
int	4	大整数值
bigint	8	极大整数值
float	4	单精度浮点数值
double	8	双精度浮点数值
decimal	Max(D+, M+)	含小数值，例如金额

日期和时间类型

类型	所占字节数	说明
date	3	YYYY-MM-DD
time	3	HH:MM:SS
year	1	YYYY
datetime	8	YYYY-MM-DD HH:MM:SS
timestamp	8	YYYYMMDDHHMMSS

字符串类型

类型	所占字节数	说明
char	0~255	定长字段串
varchar	0~65535	变长字符串
text	0~65535	长文本数据
blob		二进制形式的文本数据

数据库查询语言

having

having的作用和where差不多，区别在于：where关键字无法与聚合函数一起使用，而having可以对分组后的各组数据进行筛选。

表的连接

• 内连接：join 或inner join

• 外连接：

  left join：左外连接

  right join：右外连接

  full join：全外连接

• 自连接：同一张表的连接

MySQL权限体系

概念

MySQL的权限体系大致分为5个层级，全局层级、数据库层级、表层级、列层级和子程序层级。

层级	描述
全局层级	适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。
数据库层级	适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。
表层级	适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。
列层级	适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。
子程序层级	CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

权限信息存储

MySQL的权限信息主要存储在一下几张表中：

表名	描述
user	用户权限表，记录账号、密码及全局性权限信息
db	记录数据库相关权限
table_priv	用户对某个表拥有的权限
column_priv	用户对某表的某个列所拥有的权限
procs_priv	用户对存储过程及存储函数的操作权限

权限列表

授权

格式：grant 权限类型 on databaseName.tableName to 用户名@主机名;

e.g.

mysql> grant select on test.user to zhangsan@localhost;
Query OK, 0 rows affected (0.00 sec)

撤销授权

格式：revoke 权限类型 on databaseName.tableName from 用户名@主机名;

e.g.

mysql> revoke select on test.user from zhangsan@localhost;
Query OK, 0 rows affected (0.00 sec)

刷新权限

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看权限

格式：show grants for 用户名@主机名;

e.g.

mysql> show grants for zhangsan@localhost;
+-----------------------------------------------+
| Grants for zhangsan@localhost                 |
+-----------------------------------------------+
| GRANT SELECT ON *.* TO 'zhangsan'@'localhost' |
+-----------------------------------------------+
1 row in set (0.00 sec)

禁止远程登录

默认情况下Mysql安装完之后root用户是禁止远程登录的。

是否能远程登录只要修改mysql.user表的host字段即可，修改为localhost或127.0.0.1即表示禁止远程登录。

mysql> use mysql;                  
Database changed                   
mysql> select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| zhangsan      | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

mysql> update user set host = 'localhost' where user = 'zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user, host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| zhangsan      | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)