Tag: 语句

<?php
// ========================================================
// 下面这段代码展示了如何创建主数据库，并创建主从用户名、密码，并赋予主数据库所有权限，从数据库只读权限
$dbName		=	'test_db';
$userName	=	'test_db_user';
$userPass	=	'test_db_pass';
 
$readDbName		=	'readtest_db';
$readUserName	=	'readtest_db_user';
$readUserPass	=	'readtest_db_pass';
 
$sql[0]=	"CREATE DATABASE  `$dbName` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
$sql[1]=	"CREATE USER '$userName'@'%' IDENTIFIED BY  '$userPass';";
$sql[2]=	"GRANT USAGE ON * . * TO  '$userName'@'%' IDENTIFIED BY  '$userPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
$sql[3]=	"GRANT ALL PRIVILEGES ON  `$dbName` . * TO  '$userName'@'%' WITH GRANT OPTION ;";
$sql[4]=	"CREATE USER '$userName'@'localhost' IDENTIFIED BY  '$userPass';";
$sql[5]=	"GRANT USAGE ON * . * TO  '$userName'@'localhost' IDENTIFIED BY  '$userPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
$sql[6]=	"GRANT ALL PRIVILEGES ON  `$dbName` . * TO  '$userName'@'localhost' WITH GRANT OPTION ;";
 
$sql[7]=	"CREATE USER '$readUserName'@'%' IDENTIFIED BY  '$readUserPass';";
$sql[8]=	"GRANT USAGE ON * . * TO  '$readUserName'@'%' IDENTIFIED BY  '$readUserPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
$sql[9]=	"GRANT SELECT ON  `$readDbName` . * TO  '$readUserName'@'%';";
$sql[10]=	"CREATE USER '$readUserName'@'localhost' IDENTIFIED BY  '$readUserPass';";
$sql[11]=	"GRANT USAGE ON * . * TO  '$readUserName'@'localhost' IDENTIFIED BY  '$readUserPass' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;";
$sql[12]=	"GRANT SELECT ON  `$readDbName` . * TO  '$readUserName'@'%';";
 
mysql_connect('localhost','root','') or die('die db');
 
foreach ($sql as $v)
{
	$re	=	mysql_query($v)==true ? 'true' : 'false'; 	
	echo $re.'<br />';
}
 
// ========================================================
// 下面这些SQL片段是摘自w3school的学习笔记，大家可以参考
 
//显示数据库	
$sql	=	"SHOW DATABASES;";
//创建数据库	
$sql	=	"CREATE DATABASE  `testaa` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
//删除数据库	
$sql	=	"DROP DATABASE  `testaa`;";
//创建表	
$sql	=	"CREATE TABLE  `testaa`.`test` (`id` INT UNSIGNED NULL AUTO_INCREMENT PRIMARY KEY) ENGINE = MYISAM ;";
//删除表	
$sql	=	"DROP TABLE `test`;";
//显示表	
$sql	=	"SHOW TABLES;";
//描述表	
$sql	=	"DESCRIBE `test`;";
//插入表	
$sql	=	"INSERT INTO `testaa`.`test` (`id`) VALUES ('2'), ('3');";
 
// ========================================================
// SQL用法
 
//ORDER BY用法
$sql	=	"SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber;";
$sql	=	"SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC;";
 
//TOP用法
$sql	=	"SELECT TOP 2 * FROM Persons;";
$sql	=	"SELECT TOP 50 PERCENT * FROM Persons;";
 
//LIKE和NOT LIKE用法 
$sql	=	"SELECT * FROM Persons WHERE City LIKE '%g';";
$sql	=	"SELECT * FROM Persons WHERE City NOT LIKE '%lon%';";
 
//_通配符，匹配单个字符,(可以匹配Carter)
$sql	=	"SELECT * FROM Persons WHERE LastName LIKE 'C_r_er';";  
 
//[]通配符
//现在，我们希望从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人：
$sql	=	"SELECT * FROM Persons WHERE City LIKE '[ALN]%';";
//现在，我们希望从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人：
$sql	=	"SELECT * FROM Persons WHERE City LIKE '[!ALN]%';";
 
// IN用法
$sql	=	"SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');";
 
//BETWEEN  AND和NOT BETWEEN  AND用法
$sql	=	"SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter';";
 
//AS用法
$sql	=	"SELECT LastName AS Family, FirstName AS Name FROM Persons;";
 
// 多表查询
$sql	=	"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P;";
 
// JOIN用法
$sql	=	"SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName;";
 
/*
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
*/
 
//UNION和UNION ALL用法，UNION会去掉重复的值，UNION ALL不会去除重复
$sql	=	"SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA;";
$sql	=	"SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA;";
 
//SELECT INTO用法(可以用来制作备份文件)
$sql	=	"SELECT * INTO Persons_backup FROM Persons;";
$sql	=	"SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P;";
 
// SQL UNIQUE约束，CONSTRAINT是用来重命名的
$sql	=	"CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
);";
 
// 添加UNQIUE约束
$sql	=	"ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);";
// 删除UNQIUE约束
$sql	=	"ALTER TABLE Persons DROP INDEX uc_PersonID;";
 
//SQL PRIMARY KEY 约束 (类似UNIQUE)
 
//SQL FOREIGN KEY 外键约束
$sql	=	"CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
);";
 
// 添加FOREIGN KEY 外键约束
$sql	=	"ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P);";
// 删除FOREIGN KEY 外键约束
$sql	=	"ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;";
 
// SQL CHECK 约束
// CHECK 约束用于限制列中的值的范围。
$sql	=	"CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
);";
 
// 添加SQL CHECK 约束
$sql	=	"ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes');";
// 删除SQL CHECK 约束
$sql	=	"ALTER TABLE Persons DROP CONSTRAINT chk_Person;";
 
//Default默认值
$sql	=	"CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
);";
 
// 索引
 
// 创建索引
$sql	=	"CREATE INDEX index_name ON table_name (column_name);";  
// 创建唯一索引
$sql	=	"CREATE UNIQUE INDEX index_name ON table_name (column_name);"; 
// 创建索引（这个有点看不懂）
$sql	=	"CREATE INDEX PersonIndex ON Person (LastName, FirstName);"; 
// 删除索引
$sql	=	"ALTER TABLE table_name DROP INDEX index_name;"; 
 
//清空表中的数据
//表名称
$sql	=	"TRUNCATE TABLE;";  
 
// ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
// 添加字段：
$sql	=	"ALTER TABLE Persons ADD Birthday date;";
// 修改字段：
$sql	=	"ALTER TABLE Persons ALTER COLUMN Birthday year;";
// 删除字段：
$sql	=	"ALTER TABLE Persons DROP COLUMN Birthday;";
 
//AUTO_INCREMENT用法
$sql	=	"CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);";
 
 
//判断字段值是否是NULL，使用IS NULL或者IS NOT NULL
$sql	=	"SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL;";
 
// mysql中的IFNULL和COALESCE用法
$sql	=	"SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0)) FROM Products;";
 
/*
SQL数据类型
TEXT类型中的BLOB\ENUM\SET类型比较少用到，简单介绍
BLOB:用于存储二进制大对象
ENUM:类似于单选下拉框值
SET:类似于多选选下拉框值,多个值用逗号分割
*/
 
// SQL 函数 以下内容参考W3SCHOOL.COM.CN
// SELECT function(列) FROM 表
 
//平均值
$sql	=	"SELECT AVG(OrderPrice) AS OrderAverage FROM Orders;";
 
//高于平均值
$sql	=	"SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)";
 
// COUNT统计
$sql	=	"SELECT COUNT(DISTINCT column_name) FROM table_name;";
 
// FIRST
$sql	=	"SELECT FIRST(column_name) FROM table_name;";
 
// LAST
$sql	=	"SELECT LAST(column_name) FROM table_name;";
 
//MAX
$sql	=	"SELECT MAX(column_name) FROM table_name;";
 
//MIN
$sql	=	"SELECT MIN(column_name) FROM table_name;";
 
//SUM
$sql	=	"SELECT SUM(column_name) FROM table_name;";
 
/*
 * GROUP BY 语句
 * GROUP BY 多个列可能会在统计中常用到，可以参考示例
 */
$sql	=	"SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer;";
 
//HAVING 子句
$sql	=	"SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000;";
 
// UCASE 函数把字段的值转换为大写
$sql	=	"SELECT UCASE(column_name) FROM table_name;";
 
// LCASE 函数把字段的值转换为小写
$sql	=	"SELECT LCASE(column_name) FROM table_name;";
 
// MID 函数用于从文本字段中提取字符,会取出City字段的三位字符
$sql	=	"SELECT MID(City,1,3) as SmallCity FROM Persons;";
 
// LEN 函数返回文本字段中值的长度
$sql	=	"SELECT LEN(City) as LengthOfCity FROM Persons;";
 
// ROUND 函数用于把数值字段舍入为指定的小数位数
$sql	=	"SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products;";
 
// NOW 函数返回当前的日期和时间
$sql	=	"SELECT ProductName, UnitPrice, Now() as PerDate FROM Products;";
 
// FORMAT 函数用于对字段的显示进行格式化
$sql	=	"SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products;";
 
//LENGTH 用法,查找字段长度为9的所有字段值
$sql	=	"SELECT * FROM  `abc` WHERE LENGTH(  `abcd` ) =9";
 
// 获取字段的类型/名称/长度
<?php
    mysql_connect("localhost", "mysql_username", "mysql_password");
    mysql_select_db("mysql");
    $result = mysql_query("SELECT * FROM func");
    $fields = mysql_num_fields($result);
    $rows   = mysql_num_rows($result);
    $table = mysql_field_table($result, 0);
    echo "Your '".$table."' table has ".$fields." fields and ".$rows." record(s)\n";
    echo "The table has the following fields:\n";
    for ($i=0; $i < $fields; $i++) {
        $type  = mysql_field_type($result, $i);
        $name  = mysql_field_name($result, $i);
        $len   = mysql_field_len($result, $i);
        $flags = mysql_field_flags($result, $i);
        echo $type." ".$name." ".$len." ".$flags."\n";
    }
    mysql_free_result($result);
    mysql_close();
?>