九. 交叉查询

　　交叉查询可以对数据进行总和,平均,计数或其他总和计算法的计算,这些数据通过

　　两种信息进行分组:一个显示在表的左部,另一个显示在表的顶部.

　　Microsoft Jet SQL 用TRANSFROM语句创建交叉表查询语法:

　　TRANSFORM aggfunction

　　Select 语句

　　GROUP BY 子句

　　PIVOT pivotfield[IN(value1 [,value2[,…]]) ]

　　Aggfounction指SQL聚积函数,

　　Select语句选择作为标题的的字段,

　　GROUP BY 分组

　　说明：

　　Pivotfield 在查询结果集中创建列标题时用的字段或表达式,用可选的IN子句限制

　　它的取值.

　　value代表创建列标题的固定值.

　　例:显示在1996年里每一季度每一位员工所接的定单的数目:

　　TRANSFORM Count(OrderID)

　　Select FirstName&''&LastName AS FullName

　　FROM Employees INNER JOIN orders

　　ON Employees.EmployeeID = orders.EmployeeID

　　Where DatePart("yyyy",OrderDate)= '1996'

　　GROUP BY FirstName&''&LastName

　　orDER BY FirstName&''&LastName

　　POVOT DatePart("q",OrderDate)&'季度'

　　十 .子查询

　　子查询可以理解为 套查询.子查询是一个Select语句.

　　1 表达式的值与子查询返回的单一值做比较

　　语法:

　　表达式 comparision [ANY|ALL|SOME](子查询)

　　说明：

　　ANY 和SOME谓词是同义词,与比较运算符(=,<,>,$amp;

　　值True或 False.ANY的意思是,表达式与子查询返回的一系列的值逐一比较,只要其

　　中的一次比较产生True结果,ANY测试的返回 True值(既Where子句的结果),对应于

　　该表达式的当前记录将进入主查询的结果中.ALL测试则要求表达式与子查询返回的

　　一系列的值的比较都产生 True结果,才回返回True值.

　　例:主查询返回单价比任何一个折扣大于等于25%的产品的单价要高的所有产品

　　Select * FROM Products

　　Where UnitPrice>ANY

　　(Select UnitPrice FROM[Order Details] Where Discount>0.25)

　　2 检查表达式的值是否匹配子查询返回的一组值的某个值

　　语法:

　　[NOT]IN(子查询)

　　例:返回库存价值大于等于1000的产品.

　　Select ProductName FROM Products

　　Where ProductID IN

　　(Select PrdoctID FROM [Order DEtails]

　　Where UnitPrice*Quantity>= 1000)

　　3检测子查询是否返回任何记录

　　语法:

　　[NOT]EXISTS (子查询)

　　例:用EXISTS检索英国的客户

　　Select ComPanyName,ContactName

　　FROM orders

　　Where EXISTS

　　(Select *

　　FROM Customers

　　Where Country = 'UK' AND

　　Customers.CustomerID= orders.CustomerID)

　　1:使用SHOW语句找出在服务器上当前存在什么数据库：

　　mysql> SHOW DATABASES;

　　2:2、创建一个数据库MYSQLDATA

　　mysql> Create DATABASE MYSQLDATA;

　　3:选择你所创建的数据库

　　mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!)

　　4:查看现在的数据库中存在什么表

　　mysql> SHOW TABLES;

　　5:创建一个数据库表

　　mysql> Create TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

　　6:显示表的结构：

　　mysql> DESCRIBE MYTABLE;

　　7:往表中加入记录

　　mysql> insert into MYTABLE values ("hyq","M");

　　8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)

　　mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MYTABLE;

　　9:导入.sql文件命令(例如D:/mysql.sql)

　　mysql>use database;

　　mysql>source d:/mysql.sql;

　　10:删除表

　　mysql>drop TABLE MYTABLE;

　　11:清空表

　　mysql>delete from MYTABLE;

　　12:更新表中数据

　　mysql>update MYTABLE set sex="f" where name='hyq';

　　以下是无意中在网络看到的使用MySql的管理心得,

　　摘自:

　　在windows中MySql以服务形式存在，在使用前应确保此服务已经启动，未启动可用net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start"命令，注意启动者应具有管理员权限。

　　刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户，这是很大的安全隐患，对于一些重要的应用我们应将安全性尽可能提高，在这里应把匿名帐户删除、 root帐户设置密码，可用如下命令进行：

　　use mysql;

　　delete from User where User="";

　　update User set Password=PASSWORD('newpassword') where User='root';

　　如果要对用户所用的登录终端进行限制，可以更新User表中相应用户的Host字段，在进行了以上更改后应重新启动数据库服务，此时登录时可用如下类似命令：

　　mysql -uroot -p;

　　mysql -uroot -pnewpassword;

　　mysql mydb -uroot -p;

　　mysql mydb -uroot -pnewpassword;

　　上面命令参数是常用参数的一部分，详细情况可参考文档。此处的mydb是要登录的数据库的名称。

　　在进行开发和实际应用中，用户不应该只用root用户进行连接数据库，虽然使用root用户进行测试时很方便，但会给系统带来重大安全隐患，也不利于管理技术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过User表来实现的，添加新用户常用的方法有两个，一是在User表插入相应的数据行，同时设置相应的权限;二是通过GRANT命令创建具有某种权限的用户。其中GRANT的常用用法如下：

　　grant all on mydb.* to NewUserName@HostName identified by "password" ;

　　grant usage on *.* to NewUserName@HostName identified by "password";

　　grant select,insert,update on mydb.* to NewUserName@HostName identified by "password";

　　grant update,delete on mydb.TestTable to NewUserName@HostName identified by "password";

　　若要给此用户赋予他在相应对象上的权限的管理能力，可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户，Password字段应用PASSWORD 函数进行更新加密，以防不轨之人窃看密码。对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，回收权限可以通过更新User表相应字段，也可以使用REVOKE操作。

　　下面给出本人从其它资料()获得的对常用权限的解释：

　　全局管理权限：

　　FILE: 在MySQL服务器上读写文件。

　　PROCESS: 显示或杀死属于其它用户的服务线程。

　　RELOAD: 重载访问控制表，刷新日志等。

　　SHUTDOWN: 关闭MySQL服务。

　　数据库/数据表/数据列权限：

　　Alter: 修改已存在的数据表(例如增加/删除列)和索引。

　　Create: 建立新的数据库或数据表。

　　Delete: 删除表的记录。

　　Drop: 删除数据表或数据库。

　　INDEX: 建立或删除索引。

　　Insert: 增加表的记录。

　　Select: 显示/搜索表的记录。

　　Update: 修改表中已存在的记录。

　　特别的权限：

　　ALL: 允许做任何事(和root一样)。

　　USAGE: 只允许登录--其它什么也不允许做。

　　1、MySQL常用命令

　　create database name; 创建数据库

　　use databasename; 选择数据库

　　drop database name 直接删除数据库，不提醒

　　show tables; 显示表

　　describe tablename; 表的详细描述

　　select 中加上distinct去除重复字段

　　mysqladmin drop databasename 删除数据库前，有提示。

　　显示当前mysql版本和当前日期

　　select version(),current_date;

　　2、修改mysql中root的密码：

　　shell>mysql -u root -p

　　mysql> update user set password=password(”xueok654123″) where user=’root’;

　　mysql> flush privileges //刷新数据库

　　mysql>use dbname; 打开数据库：

　　mysql>show databases; 显示所有数据库

　　mysql>show tables; 显示数据库mysql中所有的表：先use mysql;然后

　　mysql>describe user; 显示表mysql数据库中user表的列信息);

　　3、grant

　　创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令something做这个

　　mysql> grant all privileges on *.* to user@localhost identified by ’something’ with

　　增加新用户

　　格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”

　　GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY ’something’ WITH GRANT OPTION;

　　GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;

　　删除授权：

　　mysql> revoke all privileges on *.* from root@”%”;

　　mysql> delete from user where user=”root” and host=”%”;

　　mysql> flush privileges;

　　创建一个用户custom在特定客户端it363.com登录，可访问特定数据库fangchandb

　　mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ' passwd’

　　重命名表:

　　mysql > alter table t1 rename t2;

　　4、mysqldump

　　备份数据库

　　shell> mysqldump -h host -u root -p dbname >dbname_backup.sql

　　恢复数据库

　　shell> mysqladmin -h myhost -u root -p create dbname

　　shell> mysqldump -h host -u root -p dbname < dbname_backup.sql

　　如果只想卸出建表指令，则命令如下：

　　shell> mysqladmin -u root -p -d databasename > a.sql

　　如果只想卸出插入数据的sql命令，而不需要建表命令，则命令如下：

　　shell> mysqladmin -u root -p -t databasename > a.sql

　　那么如果我只想要数据，而不想要什么sql命令时，应该如何操作呢

　　mysqldump -T./ phptest driver

　　其中，只有指定了-T参数才可以卸出纯文本文件，表示卸出数据的目录，./表示当前目录，即与mysqldump同一目录。如果不指定driver 表，则将卸出整个数据库的数据。每个表会生成两个文件，一个为.sql文件，包含建表执行。另一个为.txt文件，只包含数据，且没有sql指令。

　　5、可将查询存储在一个文件中并告诉mysql从文件中读取查询而不是等待键盘输入。可利用外壳程序键入重定向实用程序来完成这项工作。例如，如果在文件my_file.sql 中存放有查

　　询，可如下执行这些查询：

　　例如，如果您想将建表语句提前写在sql.txt中:

　　mysql > mysql -h myhost -u root -p database < sql.txt

　　// 启动服务

　　mysqld --console

　　// 停止服务

　　mysqladmin -u root shutdown

　　// 登录后使用数据库 mysql

　　mysql -u root -p mysql

　　mysql -u root -p -h 11.11.11.11 database

　　// 创建数据库

　　create database db_name [default character set=gbk]

　　// 设置数据库默认字符集

　　alter databse db_name default character set gbk

　　// 更换数据库 use database test after log on

　　use test

　　// 创建一个带图像字段的表 create a table mypic to store picture

　　create table mypic (picid int, picname varchar(20), content blob);

　　// 显示表的结构 describe table mypic

　　desc mypic

　　// 显示当前表的建表语句

　　show create table table_name

　　// 更改表类型

　　alter table table_name engine innodb|myisam|memory

　　// 插入一条记录 insert a record

　　insert into mypic values (1, '第二章', 0x2134545);

　　// 显示当前用户 show current user

　　select user();

　　// 显示当前用户密码 show current password

　　select password('root');

　　// 显示当前日期 show current date

　　select now();

　　// 更改用户密码 change user password

　　update user set password=password('xxx') where user='root';

　　// 分配用户权限 grant

　　grant all privileges on *.* toroot@localhost

　　grant select,insert,delete,update,alter,create,drop on lybbs.* tolybbs@"%" identified by "lybbs";

　　grant select,insert,delete,update,alter,create,drop on lybbs.* tolybbs@localhostidentified by "lybbs";

　　// 在不重启的情况下刷新用户权限 flush privileges

　　flush privileges

　　// 向表中增加一个主键 add primary key

　　alter table mypic add primary key (picid)

　　// 修改表结构增加一个新的字段 add a new column userid after picid

　　alter table mypic add column userid int after picid

　　// 更改列类型,当存储图像过大时，使用默认blob超不过100k

　　alter table userpic change image image longblob;

　　alter table userpic modify image longblob;

　　// 设置默认字符集为gb2312

　　mysqld --default-character-set=gb2312

　　// 显示详细信息，包括字符集编码

　　show full columns from userpic;

　　// 改变表的编码

　　Alter TABLE userpic CHARACTER SET gb2312;

　　// mysql jdbc连接url 使用中文

　　jdbc:mysql://localhost/test useUnicode=true&characterEncoding=gb2312

　　// 执行外部脚本

　　source

　　MySQL是最受欢迎的开源SQL数据库管理系统，由MySQL AB开发、发布和支持。MySQL AB是一家基于MySQL开发人员的商业公司，是一家使用了一种成功的商业模式来结合开源价值和方****的第二代开源公司。MySQL是MySQL AB的注册商标。

　　MySQL是一个快速的、多线程、多用户和健壮的SQL数据库服务器。MySQL服务器支持关键任务、重负载生产系统的使用，也可以将它嵌入到一个大配置(mass-deployed)的软件中去。

　　收集的几篇，有空再做下整理。Mysql常用命令

　　show databases; 显示数据库

　　create database name; 创建数据库

　　use databasename; 选择数据库

　　drop database name 直接删除数据库，不提醒

　　show tables; 显示表

　　describe tablename; 显示具体的表结构

　　select 中加上distinct去除重复字段

　　mysqladmin drop databasename 删除数据库前，有提示。

　　显示当前mysql版本和当前日期

　　select version(),current_date;

　　修改mysql中root的密码：

　　shell>mysql -h localhost -u root -p //登录

　　mysql> update user set password=password("xueok654123") where user='root';

　　mysql> flush privileges //刷新数据库

　　mysql>use dbname; 打开数据库：

　　mysql>show databases; 显示所有数据库

　　mysql>show tables; 显示数据库mysql中所有的表：先use mysql;然后

　　mysql>describe user; 显示表mysql数据库中user表的列信息);

　　grant

　　创建用户firstdb(密码firstdb)和数据库，并赋予权限于firstdb数据库

　　mysql> create database firstdb;

　　mysql> grant all on firstdb.* to firstdb identified by 'firstdb'

　　会自动创建用户firstdb

　　mysql默认的是本地主机是localhost,对应的IP地址就是127.0.0.1，所以你用你的IP地址登录会出错，如果你想用你的IP地址登录就要先进行授权用grant命令。

　　mysql>grant all on *.* to root@202.116.39.2 identified by "123456";

　　说明:grant 与on 之间是各种权限，例如:insert,select,update等

　　on 之后是数据库名和表名,第一个*表示所有的数据库，第二个*表示所有的表

　　root可以改成你的用户名，@后可以跟域名或IP地址，identified by 后面的是登录用的密码，可以省略，即缺省密码或者叫空密码。

　　drop database firstdb;

　　创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个