读书笔记---第四天

                                                                                                                                                                                                注:9.1-9.5写完博客忘记保存,以后补上
表9.1 用户权限表
SELECT 表,列
INSERT 表,列
UPDATE 表,列
DELETE 表    //允许用户删除现在表的行
INDEX  表    //允许用户创建和删除特定表索引
ALTER  表    //允许用户改变现在瑶结构,添加列\重命名列或表\修改列的数据类型
CREATE 数据库,表
DROP   数据库,表
表9.2 管理员权限
CREATE TEMPORARY　ＴＡＢＬＥＳ　允许管理员在ＣＲＥＡＴＥ　ＴＡＢＬＥ语句中使用ＴＥＭＰＯＲＡＲＹ
ＦＩＬＥ　允许将数据从文件读入表，或从表读入文件
ＬＯＣＫ　ＴＡＢＬＥＳ　允许使用ＬＯＣＫ　ＴＡＢＬＥＳ语句
ＰＲＯＣＥＳＳ 允许管理员查看属于所有用户的服务器进程
RELOAD 允许管理员重新载入授权表、清空授权、主机、日志和表格
REPLICＡTION CLIENT 允许在复制主机和从机上使用SHOＷ　ＳＴＡＴＵＳ
ＲＥＰＬＩＣＡＴＯＮ　ＳＬＡＶＥ　允许复制从服务器连接到主服务器
ＳＨＯＷ　ＤＡＴＡＢＡＳＥＳ　
ＳＨＵＴＤＯＷＮ
ＳＵＰＥＲ　允许管理关闭属于任何用户的线程
表９．３特别权限
ＡＬＬ　上面两表所胡权限ＡＬＬ PRIVILEGES
USAGE 只创建用户,不给任何权限
REVOKE命令:与GRANT相反,收回权限
REVOK　ALL　PRIVILEGES,GRANT FROM user_name
授权和收回的例子:
grant all on * to fred identified by '123' with grant option;
revoke all privileges,grant from fred;
9.6 创建一个WEB用户
mysql>grant select,insert,delete,update on bookm.* to bookormn identified by '123';
9.7 使用数据库
mysql> use dbname;
mysql -D dbname -u username -p
9.8 创建数据库表
CREATE TABLE tablename(colmns)
//用逗号分开的列名称后面紧跟其数据类型
导入sql文件
> mysql -h host -u username -D tablename -p INT -231-231-1或者0-232-1INTEGER INT同意词
BIGINT -263-263-1或者0-264-1
表9.6浮点数据类型
FLOAT 取决于精度
FLOAT[(M,D)] +\-1.175494351E-38 单精度浮点等同于FLOAT(4),但是指定显示宽度和小数位数
DOUBLE同上 +\-1.7976931348623157E+308 双精度,等同FLOAT(8),同上
DOUBLE PRECISION 同上
REAL 同上
DECIMAL 浮点数,以CHAR存储
DEC 同上
FIXED 同上
9.7 日期和时间类型
DATE 1000-01-01...9999-12-31 YYYY-MM-DD
TIME -838:59:59...838:59:59 HH:MM:SS
DATETIME 1000-01-0100:00:00...9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS
TIMESTAMP 1970-01-01 00:00:00...2037年的某个时间表,取决于UNIX的限制
YEAR[(2|]) 70-69(1970-2069)...1901-2155
9.8TIMESTAMP所有不同的可显示类型
TIMESTAMP YYYYMMDDHHMMSS
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
...
TIMESTAMP(2) YY
字符串:普通字符串(char,varchar),TEXT或者BLOB(分别适用于长文本或二进制数据),SET和ENUM(SET类型用来指定列中的值必须来自一个特定集合中的指定值,列值可以包含来自该集合的多个值;在指定的集合中最大可以有64个元素.ENUM就是枚举类型,与SET相似;可以只有一个指定集合中的值或者NULL,最多可有65535)
表9.9普通字符
[NATIONAL]
CHAR(M)
[BINARY|ASCII|UNICODE] 0~255
CHAR 同上
[NATIONAL] VARCHAR 1~255 除可变长同上
[BINARY]
表9.10TEXT和BLOB类型
TINYBLOB 28-1(255)
TINYTEXT 28-1(255)
BLOB 216-1(65535)
TEXT 同上
MEDIUMBLOG 224-1(16777215)
MEDIUMTEXT 同上
LONGBLOB 232-1(4294967295)
LONGTEXT 同上
9.11SET和ENUM
ENUM('value1','value2',...) 65535  该类型列只可以容纳所列值之一或者NULL
SET('value1','value2',...) 64 可容纳一组值,或者NULL
第10章 使用MYSQL数据库
10.1SQL是什么
　　Structured Query Language 结构化查询语言
１０．２在数据库中插入数据
INSERT [INTO] table [(colmn1,colmn2,colmn3,...)] VALUES
(vlaue1,value2,value3,...);
//值中字符串应该包含在单＼双引号中，数字和日期不用
给出的域不全时，必须指定域名以和值对应．也可：
insert into customers set name='Michael Archer',address='12 Adderley Avenue',city='Leeton';
在给AUTO_INCREMENT字段ＮＵＬＬ或者不指定时，值自动增加并赋．
也可以一次将多行插入，每行应该出现在自己的括号里，每个括号用逗号分开．
ＬＯＷ＿ＰＲＩＯＲＩＴＹ　如果数据不是从表格读出时，系统必须等待并且稍后再插入
ＤＥＬＡＹＥＤ　数据被缓存，服务器空闲时再插入
ＩＧＮＯＲＥ　任何可能导致重复惟一键的记录行将被忽略
句尾指定ＯＮ　ＤＵＭＰＬＩＣＡＴＥ KEY UPDATE expression 这可以使用一个常规的UPDATE语句修改重复的值
／／
10.3 从数据库中获取数据
SELECT [options] items
[INTO file_details]
FROM tables
[WHERE conditions]
[GROUP BY group_type]
[HAVING where_definition]
[ORDER BY order_type]
[LIMIT limit_criteria]
[PROcedure proc_name(arguments)]
[lock_options]
;
详细介绍子句:
where筛选子句
select *
from orders
where customerid = 5;
表10-1WHERE子句的实用比较运算符
=
>
=
IS NOT NULL
IS NULL
BETWEEN (0,60)测试一个值在范围之内
IN ('YES','NO')测试一个值是不是在一个集合中
LIKE 模式匹配(左%some,右some%,中间%some%;%匹配任意多,_匹配一个字符)
NOT LIKE
REGEXP 正则表达式匹配 RLIKE
从多个表中获取数据
1 简单的双表关联
select orders.orderid, orders.amount, orders.date
from customers,orders
where customers.name = 'Julie Smith' and customers.customerid = orders.customerid;
//以上表之间的","相当输入INNER JOIN 或CROSS JOIN;这种关联称为完全关联full join 或表的笛卡儿乘积.
等价关联:在WHERE子句中使用关联条件(JOIN CONDITION)
       customers.customerid = orders.customerid
2 关联多个表
select customers.name
from customers, orders,order_items,books
where customers.customerid = orders.customerid
and orders_orderid = order_items.orderid
and order_items.isdn = books.isdn
and books.title like '%Java%';
3 查找不匹配行
左关联:两个表之间指定的关联条件下匹配的数据行,如果右边的表没有匹配行,结果中就会增加一行,该行右边的列为NULL.
select customers.customerid, customers.name, ordres.orderid
from customers left join orders
on customers.customerid = orders.customerid;
select customers.customerid, customers.name, ordres.orderid
from customers left join orders
using (customerid)
where orders.orderid is null;
左关联支持ON和USING语句,UNSING语句并不需要指定连接属性所来自的表,所以两个表中的死必须同名.
4 使用表别名:Aliases
select c.name
from customers as c, orders as o, order_items as oi, books as b
where c.customerid = o.customerid
and o.orderid = oi.orderid
and oi.isbn = b.isbn
and b.title like '%Java%';
当要关联一个表到表本身的时候必须使用别名
select c1.name, c2.name, c1.city
from customers as c1, customers as c2
where c1.city = c2.city
and c1.name  != c2.name;
5 关联的总结
笛卡儿乘积 所有表所有行的所有关联
完全关联 同上
交叉关联 同上
等价关联 WHERE子句关联
左关联 试图匹配表的行并在不匹配的行中填入NULL
以特定的顺序获取数据:ORDER BY子句
select name, address
from customers
order by name desc;
分组与合计数据
表10-3 MYSQL中的合计函数
AVG(列) 平均
COUNT(项目) //指定列会返回非空死数,前加DISTINCT返回本列中不同值的列数,COUNT(*)返回所有行数
MIN 指定列最小值
MAX 指定列最大值
STD 指定列的标准背离值
SUN 指定列所有值的和
select customerid, avg(amount)
from orders
group by customerid;
//当通过合计函数使用GROUP BY 子句的时候,实际上改变了函数行为.
select customerid, avg(amount)
from orders
group by customerid
having avg(amount) > 50;
选择要返回的行
select name
from customers
limit 2,3;
使用子查询:一个嵌套在另一个查询内部的查询.
1 基本的子查询
  一个查询的结果作为另一个查询的比较条件
select customerid, amount
from orders
where amount = (select max(amount) from orders);
2 子查询和操作符
10-4 子查询操作符
ANY select c1 From t1 where c1 > any(select c1 from t2);
IN select c1 from t1 where c1 in (select c1 from t2);
SOME select c1 from t1 where c1 > some(select c1 from t2);
ALl select c1 from t1 where c1 > ALL(select c1 from t2);
3 关联子查询
select isbn, title
from books
where not exists
(select * from order_items where order_items.isbn=books.isbn);
4 行子查询
select c1, c2, c3
from t1
where (c1, c2, c3) in (select c1, c2, c3 from t2);
5 使用子查询作为临时表
select * from
(select customerid, name from customers where city='Box Hill')
as box_hill_customers;
10.4 更新数据库记录
UPDATE [LOW_PRIORITY] [IGNORE] tablename
SET column1=expression2,colmn2=expression2,...
[where condition]
[ORER BY order_criteria]
[LIMIT number]
//ORDER BY只在LIMIT中有用
update books
set price=price*1.1;
10.5 创建后修改表
ALTER TABLE [IGNORE] tablename alteration [, alteration...]

表10-5用ALTER TABLE语句可能完成的修改
ADD[COLUMN]column_description [FIRST|AFTER colmn]
ADD[COLMN](column_description,column_description,...)增加多行
ADD INDEX[index](column,...) 在指定的一列或几列添加一个表的索引
ADD[CONSTRAINT[symbol]]PRIMARY KEY(column,...) 指定一列或几列为表主键
ADD UNIQUE[CONSTRAINT[symbol]][index](column,...) 在指定的一列或几列添加一个惟一的表索引
....
...
..
.
(太多了,打字太累了,用得时候再查吧)
常见用法
alter table customers
modify name char(70) not null;
alter table orders
add tax float(6,2) after amount;
alter table orders
drop tax;
10.6 删除数据库中的记录
DELETE [LOW_PRIORITY][QUICK][IGNORE] FROM table
[WHERE condition]
[ORDER BY order_cols]
[LIMIT number]
delete from customers
where customerid = 5;
10.7 表的删除
DROP TABLE table;
10.8 删除整个数据库
DROP DATABASE database;
第11章 使用PHP从WEB访问MYSQL数据库
11.1 WEB数据库架构的工作原理
  Book-O-Rama Search ResultsBook-O-Rama Search Results  // create short variable names  $searchtype=$_POST['searchtype'];  $searchterm=$_POST['searchterm'];  $searchterm= trim($searchterm);  if (!$searchtype || !$searchterm)  {     echo 'You have not entered search details.  Please go back and try again.';     exit;  }    if (!get_magic_quotes_gpc())  {    $searchtype = addslashes($searchtype);    $searchterm = addslashes($searchterm);  }  @ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books');  if (mysqli_connect_errno())   {     echo 'Error: Could not connect to database.  Please try again later.';     exit;  }  $query = "select * from books where ".$searchtype." like '%".$searchterm."%'";  $result = $db->query($query);  $num_results = $result->num_rows;  echo 'Number of books found: '.$num_results.'';  for ($i=0; $i   {     $row = $result->fetch_assoc();     echo ''.($i+1).'. Title: ';     echo htmlspecialchars(stripslashes($row['title']));     echo '
Author: ';     echo stripslashes($row['author']);     echo '
ISBN: ';     echo stripslashes($row['isbn']);     echo '
Price: ';     echo stripslashes($row['price']);     echo '';  }    $result->free();  $db->close();?>
11.2 从WEB查询数据库的基本步骤:
1 检查并过滤来自用户的数据
2 建立一个适当数据库的连接
3 查询数据库
4 获取查询结果
5 将结果显示给用户
11.3 检查与过滤用户输入数据
$searchterm = trim($searchterm);
//去空格
if (!$searchtype || !$searchterm)
  {
     echo 'You have not entered search details.  Please go back and try again.';
     exit;
  }
//查看是不是用户没有输入信息
if (!get_magic_quotes_gpc())
  {
    $searchtype = addslashes($searchtype);
    $searchterm = addslashes($searchterm);
  }
//是否自动完成了引号,如果不则手动加
11.4 建立一个连接
PHP5为连接MYSQL提供了新函数库mysqli(i表示改进)
@ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books');
//这是以面向对象的方式连接数据库,可以调用对象的方法来访问数据库
@ $db = mysqli_connect('localhost','bookorama','bookorama123','books');
//这是面向过程的
if (mysqli_connect_errno())
  {
     echo 'Error: Could not connect to database.  Please try again later.';
     exit;
  }
//检查连接是否正确
11.5 选择使用的数据库
$db->select_db(dbname)
或
mysqli_select_db(db_resource,dbname)
11.6 查询数据库
$query = "select * from books where $searchtype like '%$searchterm%'";
$result = $db->query($query);或 $result = mysqli_query($db, $query);
11.7 检索查询结果
我们可以使用不同的函数以不同的方式将查询结果从结果对象或标识符中取出来.结果对象或标识符是访问查询返回行的关键.
  面向对象方法中,返回的行数保存在结果对象的num_rows成员变量中:$num_results = $result->num_rows;
  过程方法中,函数mysqli_num_rows()给出了查询返回的行数:$num_results = mysqli_num_rows($result);
for ($i=0; $i fetch_assoc()(或mysqli_fetch_assoc());如果没有返回行,该循环将停止执行.该函数接受结果集合中每一行并以一个相关数组返回该行,每个关键词为一个属性名,每个值为数组中相应的值:$row = $result->fetch_assoc(); 或者过程方法$row = mysqli_fetch_assoc($result);
给定相关数组$row,我们可以遍历每个字段并适当地显示它们
echo '
ISBN:';
echo stripslashes($row['isbn']);
从结果标识符中获得查询结果有几种不同的方法:
$row = $result->fetch_row($result);
或者
$row = mysqli_fetch_row($result);
//属性值将在每个数组值$row[0] $row[1]...里面列出
也可以使用mysqli_fetch_object()函数将一行取回到一个对象中:
$row = $result->fetch_object();
或者
$row = mysqli_fetch_object($result);
//然后通过$row->title $row->author等访问每个属性
11.8 从数据库断开连接
$result->free();
或者
mysqli_free_result($result);
然后可以使用
$db->close();
或者
mysqli_close($db);
关闭一个数据库连接.
11.9 将信息插入到数据库
  Book-O-Rama Book Entry ResultsBook-O-Rama Book Entry Results  // create short variable names  $isbn=$_POST['isbn'];  $author=$_POST['author'];  $title=$_POST['title'];  $price=$_POST['price'];  if (!$isbn || !$author || !$title || !$price)  {     echo 'You have not entered all the required details.
'          .'Please go back and try again.';     exit;  }  if (!get_magic_quotes_gpc())  {    $isbn = addslashes($isbn);    $author = addslashes($author);    $title = addslashes($title);    $price = doubleval($price);  }  @ $db = new mysqli('localhost', 'bookorama', 'bookorama123', 'books');  if (mysqli_connect_errno())   {     echo 'Error: Could not connect to database.  Please try again later.';     exit;  }  $query = "insert into books values             ('".$isbn."', '".$author."', '".$title."', '".$price."')";   $result = $db->query($query);  if ($result)      echo  $db->affected_rows.' book inserted into database.';   $db->close();?>
通过实例化  mysqli对象调用方法完成sql insert
$query = "insert into books values
            ('".$isbn."', '".$author."', '".$title."', '".$price."')";
  $result = $db->query($query);
//面向过程是mysqli_query();
使用INSERT和SELECT的一个显著不同之处在于对mysqli_affected_rows()的的使用,这是一个过程式版本的函数或者面向对象版本中的一个类成员变量.
echo $db->affected_rows.'book(s) inserted into database.';
//使用mysqli_num_rows(0确定SELECT操作可以返回多少行记录
//使用mysqli_affected_rows()确定INSERT DELETE和UPDATE影响的行数
11.10 使用prepared语句
mysqli函数支持PREPARED语句的使用,它们对于在执行大量具有不同数据的相同查询时可以提高执行速度,也可以免受SQL注射风格的攻击.
$query ="insert into books values(?,?,?,?)";
$stmt = $db->prepare($query);
$stmt->bind_param("sssd",$isbn, $author, $title, $price);
$stmt->execute();
echo $stmt->affected_rows.'book inserted into database.';
$stmt->close();
//1 设置问号
//2 构建一个语句对象或需要用来完成实际处理的资源;bind_param()方法用途是告诉PHP哪些变量应该被问号所替换,第一个参数是一个格式化字符串(s字符串i整数b blob d双精度)
必须列出与语句中的问号数量相同的变量,它们将依次被替换.
11.11 使用PHP与数据库交互的其他接口
  PHP可以连接oracle mssql postgresql同时还可以用ODBC
11.12 使用常规的数据库接口PEAR DB
  Book-O-Rama Search ResultsBook-O-Rama Search Results  // create short variable names  $searchtype=$_POST['searchtype'];  $searchterm=$_POST['searchterm'];  $searchterm= trim($searchterm);  if (!$searchtype || !$searchterm)  {     echo 'You have not entered search details.  Please go back and try again.';     exit;  }    if (!get_magic_quotes_gpc())  {    $searchtype = addslashes($searchtype);    $searchterm = addslashes($searchterm);  }  // set up for using PEAR DB  require_once('DB.php');  $user = 'bookorama';  $pass = 'bookorama123';  $host = 'localhost';  $db_name = 'books';  // set up universal connection string or DSN  $dsn = "mysqli://$user:$pass@$host/$db_name";  // connect to database  $db = &DB::connect($dsn);  // check if connection worked  if (DB::isError($db))   {        echo $db->getMessage();    exit;  }  // perform query  $query = "select * from books where ".$searchtype." like '%".$searchterm."%'";  $result = $db->query($query);  // check that result was ok  if (DB::isError($result))   {     echo $db->getMessage();    exit;  }  // get number of returned rows  $num_results = $result->numRows();  // display each returned row  for ($i=0; $i   {     $row = $result->fetchRow(DB_FETCHMODE_ASSOC);     echo ''.($i+1).'. Title: ';     echo htmlspecialchars(stripslashes($row['title']));     echo '
Author: ';     echo stripslashes($row['author']);     echo '
ISBN: ';     echo stripslashes($row['isbn']);     echo '
Price: ';     echo stripslashes($row['price']);     echo '';  }  // disconnect from database  $db->disconnect();?>
第十二章 MYSQL高级管理
12.1 深入理解权限系统
权限信息保存在msyql的五个表中:user host db tabes_priv column_priv
user表
  该表包含全局用户权限的详细信息,可以确定一个用户是否有权连接MYSQL数据库,是否具有全局级别的权限,是否有使用系统中每个数据库的权限.
db表和host表
  普通用户的权限大多数都保存在这两个表中;DB可以确定哪些用户可以访问哪些主机和哪些数据库,此表中给出的权限用于在一个特定行中指定的任意数据库;HOST表是DB表的补充,如果一个用户从多个主机连接到数据库,在DB表中将不会列出该用户的主机名.
tables_priv表和columns_priv表
  这两个表分别用于存储表级别和列级别的权限.
MYSQL使用GRANT表
1 连接验证 2 请求验证
更新权限的生效
使用GRANT和REVOKE,服务器会自动读取GRANT表.
如果手动修改必须告知服务器:
1 命令提示符 flush privileges;
2 操作系统中 mysqladmin flush-privileges或mysqladmin reload
12.2 提高MYSQL安全性
操作系统:可禁止以3306连接
密码安全
用户权限:权限最小化
WEB问题:以特定用户访问
12.3 获取更多关于数据库信息
show获取信息
show tables;
show databases;
show tables from books;
show columns from books.orders;
show grants for bookorama;
DESCRIBE获取关于列的信息
DESCRIBE table [column];
将给出所有列的信息,如果指定column将给出列的信息,也可用通配符
用EXPLAIN理解查询操作的工作过程
EXPLAIN table;
在SELECT语句前加EXPLAIN可以查看语句执行的全过程
12.4 使用索引提高查询速度

ALTER TABLE table ADD INDEX (column);
12.5 优化数据库
设计优化:最小化冗余,使用小的列数,少用NULL,主键要短.
权限:权限要少
表的优化:OPTIMIZE TABLE tablename;或myisamchk -r table,也可myisamchk --sort-index --sort-records=1 pathtomysqldatadirectory/*/*.MYT
使用索引:使用正确的索引
使用默认值:尽可能的地方使用殊默认值
12.6 备份MYSQL数据库
1 复制数据文件时使用LOCK TABLES命令锁定表
LOCK TABLES table lock_type [,table lock_type...]
备份前必须执行FLUSH TABLES(确保对索引所做的任何修改写入到磁盘)
2 推荐:mysqldump --opt -all-databases > all.sql
3 使用mysqlhotcopy脚本
  mysqlhotcopy database /path/for/backup
12.7 恢复MySQL数据库
如果出现了一个破坏了的表,可以运行-r 选项的myisamchk命令修复
用备份一,可以将数据文件重新复制到安装MYSQL的相同位置
用备份二,必须先在导出文件中运行查询,这可以将数据库重新构建至导出该文件时的状态,接着还应该将数据库更新至保存在二进制