视图：实际是一张虚表，是存储下来的select语句。
创建视图：
CREATE VIEW view_name AS SELECT 条件
mysql> CREATE VIEW myview AS SELECT ID,Uname,Uage,CourseID FROM user WHERE CourseID IS NOT NULL;
只要基表改变，视图就会改变。
例如：
mysql> select * from myview;
+----+-----------+------+----------+
| ID | Uname | Uage | CourseID |
+----+-----------+------+----------+
| 1 | Yang Guo | 25 | 2 |
| 2 | xiao feng | 35 | 4 |
| 3 | Duan Yu | 20 | 9 |
| 4 | Xu Zhu | 22 | 10 |
+----+-----------+------+----------+
4 rows in set (0.02 sec)
如果你不想使用视图了，想要删除视图，使用下面的命令：
DROP VIEW view_name
------------------------------------------------------
子查询： 换言之就是嵌套查询，把查询的结果当作条件。
例如：查询年龄大于平均年龄的人的姓名和年龄
SELECT Uname，Uage FROM user WHERE Uage > (SELECT AVG(Uage) FROM user WHERE Uage IS NOT NULL);
平均年龄：
mysql> SELECT AVG(Uage) FROM user WHERE Uage IS NOT NULL;
+-----------+
| AVG(Uage) |
+-----------+
| 24.5714 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT Uname,Uage FROM user WHERE Uage > (SELECT AVG(Uage) FROM user WHERE Uage IS NOT NULL);
+-------------+------+
| Uname | Uage |
+-------------+------+
| Yang Guo | 25 |
| xiao feng | 35 |
| Hong xigong | 70 |
+-------------+------+
3 rows in set (0.00 sec)
---------------------------------------
事务：Transact
事务：要么同时都做了，要么同时都没做。这么说你可能不好理解，我就哪一个例子来说明吧。你拿着10元钱去买练习本，练习本的价格是2元，你给老板10元，老板给你一个本，并给你8元。整个事件就是一个事务。
应用到数据库中就是说所做的查询语句要么都执行，要么都不执行。
引擎：MyISAM 不支持事务
InnoDB 支持事务
判断一个引擎是否支持事务：
判断标准：ACID
A：Atomicity 原子性
C：Consistency 一致性（结构的一致）
I：Isolation 隔离性（多个事务之间彼此互相影响的关系）
D：Durability 持久性 完成后的结果要永久保存下来
隔离性：MySQL的隔离级别跟存储引擎相关
MySQL的隔离级别：
读未提交：READ UNCOMMITED，最低级别
读提交：READ COMMITED
可重读的:REPEATABLE READ（MySQL默认级别）
串行化：SERIABLIZABLE
级别越高，事物的安全性越好，并发性越差。
事务在提交之前可以回退，使用rollback；
查看默认级别：
mysql> SHOW VARIABLES LIKE 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
--------------------
例如有两个事务A和B
读未提交：A和B同时开始，例如A插入一条数据，并未提交，此时B使用查询会看到A插入的结构。
读提交：A和B同事开始，例如A插入一条数据，并未提交，此时B使用查询看不到A所插入的数据，只有当A执行COMMIT命令之后，B使用查询才能看到，插入的数据。
可重读：A和B同事开始，例如A插入一条数据，并未提交，此时B使用查询看不到A所插入的数据，即使当A执行COMMIT命令之后，B使用查询任然看不到A所插入的数据，直到B也执行COMMIT命令之后，再用查询命令才能看到插入的数据。
串行化：只有当A的整个事务完成以后，B再开始执行
MVCC：多版本并发控制（A和B的操作互不影响）
-----------------------------------------
查看表的存储引擎：
SHOW TABLE status\G;
开始事务：
START TRANSACTION;
回退：
ROLLBACK；
提交：
COMMIT
-----------------------------------------
mysql> START TRANSACTION
mysql> SELECT * FROM user；
mysql> INSERT INTO user SET Uname='shizhongyu';
回退：
mysql> ROLLBACK;
明确显示提交：
mysql> COMMIT；
提交过之后，回退就不起作用了
上面有提到过查看默认事务级别，也可以使用下面命令：
SELECT @@SESSION.tx_isolation;
修改事务级别：
修改为读未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED；
设置为默认级别：
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ；
---------------------------------------------------------
开启两台虚拟机，分别启动mysqld服务
第一个：
START TRANSACTION
DELETE FROM user WHERE UID=10;
COMMIT;
第二个：只有自己提交之后，才会更新
SATRT TRANSACTION;
COMMIT;
----------------------------------
锁：对同一个表进行操作时，只要一个人的操作开始了，另一个人的操作会被限制。
读锁：又称为共享锁，因为不同用户可以同时使用SELECT查询
写锁:又称为独占锁，只有当一个用户操作完毕，释放了锁，另一个用户才能执行操作。
写锁的优先级比较高
那就会有人会问，我们什么时候才能用到所能？对数据库的数据进行备份，此时就需要加锁。
锁级别：
表锁：对表进行加锁，别的用户无法对此表作任何操作。
行锁：并发级别高，可控制性复杂，其他行任然可以查询。
页锁
page,页锁，通常指内存空间
手动加锁：
LOCK TABLES table_name 锁类型
加读锁：
LOCK TABLES user READ；
释放锁：
UNLOCK TABLES；
------------------------
存储引擎： 锁级别
MyISAM/Merge ： Table
InnoDB Row
MEMORY ，保存在内存中。提供类似内存数据库 ，通常默认是16M Table
Maria Row
Falcom Row
PBXT Row
FEDERATED 仅仅是联合两个表的
NDB Row
Archive 归档存储引擎，做数据挖掘 Row
Blackhole 作为中继日志的转发节点
CSV 存储成文本文件 Table
------------------------------------------------
MyISAM:
每一个表都对应3个数据文件。
.frm 表格的保存文件
.MYD 数据的保存文件
.MYI 索引的保存文件
MyISAM的特性：
非事务
不支持外键
全文索引
无数据缓存
对索引进行缓存
支持HASH和BTREE索引
表级别的锁
读性能非常好（数据仓库）
可以压缩存放
温备
最大支持64个索引。
服务器变量：（调整MyISAM性能）
key_buffer_size,调整索引缓存大小，无论你是什么引擎，该项必须得有
SHOW VARIABLES LIKE 'key_buffer_size';
concurent_insert ,并发写入
delay_key_write ,延迟键的写入操作，不是马上就修改索引值。
查看该值：
SELECT @@GLOABL.delay_key_write
max_write_lock_count
preload_buffer_size ,用于指定key 缓存
MyISAM的管理工具：
myisamchk 分析，修复，优化
myisampack 压缩
myisam_ftdump 显示全文索引
----------------------
InnoDB
特性： 基于MVCC，支持事务
行级别锁
支持外键
聚簇索引，索引和数据在一块放。
索引缓存和数据缓存，
热备（依靠备份工具）
使用表空间，把表都放在同一个空间。默认表空间名ibdata1 ，表空间文件可以有多个。 innodb_data_home_dir 数据文件所在目录
默认存放在你定义的datadir中
innodb_data_file_path 文件路径
innodb_buffer_pool_size ,能够同时缓存数据和索引
innodb_log_file_size 日志文件，128-256M
innodb_flush_log_at_trx_commit 多长时间同步到磁盘上。默认是1 。
查看INNODB存储引擎信息：
SHOW ENGINE INNODB STATUS\G;
注意：如果你是二进制编译的话，直接对/etc/my.cnf进行修改。
如果你是使用rpm包装的话，你可以使用rpm -ql mysql-server来查看一下。其中有一行为/usr/share/doc/mysql-server-5.0.77/my-medium.cnf，修改完之后，复制到/etc/my.cnf。
练习3：
1、为mytb表创建视图records，要求只保留显示ID、zone、host、type、Mx_priority和data字段；
mysql> CREATE VIEW records AS SELECT ID,zone,host,type,Mx_priority,data from www;
练习4：
1、改变mytb的存储引擎为innodb；
mysql> ALTER TABLE www ENGINE=InnoDB;
2、设置mysql的事务隔离级别为READ COMMITED；同时显式启动两个事务A和B，以A事务向mytb中删除一行，查看B事务是否觉察到此改变；而后A事务提交后再使用B事务查看是否能看到相应的改变，而后提交B事务；
mysql> SHOW VARIABLES LIKE 'tx%';
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
A：mysql> DELETE FROM www WHERE ID=1663822;
B：SELECT * FROM www LIMIT 10;
没有变化；
A：COMMIT;
B: SELECT * FROM www LIMIT 10;
变化；
B: COMMIT;
3、启动两个mysql客户端M和N；在M上对mytb启动显式读锁，并执行一个SELECT语句；接着在N上往mytb中插入一个新行；而后在M客户端释放施加的读锁；
M:
mysql> LOCK TABLES www READ;
mysql> select * from www LIMIT 10;
N:
mysql> INSERT INTO www (host) VALUES ('www');
会插入不进去
当在M上执行了UNLOCK TABLES；之后，就可以插入成功了。
练习5：
如果你是二进制编译的话，直接对/etc/my.cnf进行修改。
如果你是使用rpm包装的话，你可以使用rpm -ql mysql-server来查看一下。
其中有一行为/usr/share/doc/mysql-server-5.0.77/my-medium.cnf
在配置文件中修改
1、设定mysql的innodb的数据目录为/innodata，并设定其文件为ibdata01和ibdata02，其中ibdata01初始大小为1024M，不能自动增长；ibdata02初始大小为50M,可以自动增长；
innodb_data_home_dir = /innodata
innodb_data_file_path = ibdata01:1024M;ibdata02:50M:autoextend
2、设定mysql的innodb的日志文件组目录为/innolog（提示：通过innodb_log_group_home_dir设定），日志组中共有4个日志文件（innodb_log_files_in_group），每个日志文件大小为50M；
innodb_log_group_home_dir = /innolog
innodb_log_files_in_group = 4
innodb_log_file_size = 50M
3、设置innodb日志和数据缓冲的大小为128M；
innodb_log_buffer_size = 128M
-------------------------------------------------------------
用户管理：
mysql用户的授权信息表：
user,db,host,tables_priv,columns_priv,procs_priv
这六张表主要指定权限委派的。
用户账号：有两段组成
'username'@'HOST'
password中的值是加密存储的。
-----------------------------------
授权格式：
GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type]
{
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
object_type =
TABLE
| FUNCTION
| PROCEDURE
with_option =
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
------------------------------------
tables_priv:对表的操作权限
columns_priv:更为细微的权限定义，对字段的操作权限
procs_priv:定义用户是否有存储过程权限的。
有哪些权限可以进行授权呢？
DML：SELECT，INSERT，UPDATE，DELETE
DDL：CREATE，DROP，
INDEX：
ALTER：定义用户修改表结构的权限
SHOW DATABASE；
SUPER：管理命令的权限
LOCK TABLES；
CREATE VIEW，SHOW VIEW：
CREATE USER（包含删除用户）
REPLICATION SLAVE（此用户是否具有从主服务器上复制二进制日志内容的权限）
REPLICATION CLIENT（表示请求主从环境当中用于复制的相关信息的权限，是否具有在主从复制架构当中，获得主服务器和从服务器架构当中信息的权限）
GRANT OPTION (此用户是否可以给别的用户授予权限)
----------------------
创建用户账号：
CREATE USER user_name@'主机' IDENTIFIED BY 'password'
此时用户没有任何权限（USAGE权限仅能连接到数据库和SHOW DATABASES；）。
>CREATE USER tom@'%' IDENTIFIED BY 'redhat';
>FLUSH PRIVILEGES;
% 匹配任意长度的任意字符
_ 匹配任意单个字符
192.168.0.%
192.168.0.0/255.255.255.0
对象类型：
TABLE
FUNCTION
PROCEDURE
想要使tom有创建数据库和表的权限
GRANT CREATE ON hello.* TO 'tom'@'%';
--------------------------------
查看用户所拥有的权限
SHOW GRANTS FOR 'root'@'localhost';
GRANT DROP ON hello.* TO 'tom' @'%';
添加的权限会在原有权限上累加
GRANT SELECT ON hello.* TO 'tom' @'%';
撤销授权：
格式：
REVOKE
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type]
{
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
FROM user [, user] ...
REVOKE SELECT ON hellodb.* FROM 'tom'@'%';
FLUSH PRIVILEGES;
----------------------------------------
总结一下：
创建用户
CREATE USER username@host [IDENTIFIED BY 'password'];
删除用户
DROP USER username@host;
授权
GRANT priv_list ON db.table TO username@host [IDENTIFIED BY 'password'];
撤销权限
REVOKE priv_list ON db.table FROM username@host;
WITH GRANT OPTION
(把自己的权限给别人)
限定其所使用的资源；
WITH MAX_QUERIES_PER_HOUR n;只能发起有限次查询；
WITH MAX_UPDATEs_PER_HOUR n;每个小时只能修改几次
WITH MAX_COMMENTIONS_PER_HOUR n;每个小时只能连接几次
WITH MAX_USER_CONNECTIONS n;同一个用户在同一时间能够连进来几次
FLUSH USER_RESOURCES; 重读限定用户资源的定义
---------------------------------------------
字段级别的权限：
限定用户在某个特定字段具有某种权限:
GRANT SELECT(name) ON db.table TO username@host;
执行存储过程和存储函数的权限：
GRANT EXECUTE ON [FUNCTION|PROCEDURE] TO username@host;
如果mysql的root用户密码忘记了：
恢复管理员密码
1.停止服务
（1）如果你是使用rpm包安装的mysql，则
ls /usr/bin/mysql* (其中有mysqld_safe文件)
#/usr/bin/mysqld_safe --skip-grant-tables --skip-networking &
（2）如果你是使用绿色安装（二进制安装），则
ls /usr/local/mysql/bin
#/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &
2.设定密码：
#mysql
>use mysql;
>UPDATE user SET password=PASSWORD('redhat') WHERE User='root' AND HOST='localhost';
3.退出，并重新正常启动mysql。
练习6：
按要求创建如下用户：
1、用户rpel，只能通过172.16.0.0/24内的主机访问，密码同用户名；而后授予其在数据库上执行REPLICATION CLIENT和REPLICATION SLAVE的权限；
mysql> show grants for rpel@'172.16.0.%';
mysql> GRANT REPLICATION CLIENT ON *.* TO rpel@'172.16.0.%';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpel@'172.16.0.%';
2、用户vikram，具有在testdb数据库上执行创建和删除tb_test表的权限；
mysql> CREATE USER vikram@'%' IDENTIFIED BY 'redhat';
mysql> GRANT CREATE ON testdb.* TO vikram@'%';
mysql> GRANT DROP ON testdb.* TO vikram@'%';
mysql> FLUSH PRIVILEGES;
在另一台主机上：mysql -uvikram -p -h192.168.0.75
mysql> CREATE TABLE tb_test(
-> tbuser VARCHAR(20),
-> tb*** ENUM ('F','M') NOT NULL DEFAULT 'M',
-> tbage int(5) NOT NULL);
mysql> DROP TABLE tb_test;
mysql> REVOKE CREATE,DROP ON testdb.* FROM vikram@'%';
3、用户gentoo，具有在mytb表中查询Host字段中数据的权限；此用户账号只能同时连入服务器1次；
CREATE USER gentoo@'%' IDENTIFIED BY 'redhat';
GRANT SELECT(Host) ON mydb.www TO gentoo@'%' WITH MAX_CONNENTIONS_PER_HOUR 1 ;
FLUSH USER_RESOURCES;
#service mysqld restart
在另一台主机上：
mysql -ugentoo -p -h192.168.0.75
4、用户centos，具有在mytb表中查询、插入和修改数据的权限；此用户每小时只能发起50次修改操作；
mysql> CREATE USER centos@'%' IDENTIFIED BY 'redhat';
mysql> GRANT SELECT,INSERT,UPDATE ON mydb.www TO centos@'%' WITH MAX_UPDATES_PER_HOUR 50;
FLUSH USER_RESOURCES;
阅读(1865) | 评论(0) | 转发(0) |
