数据库中的范式说明，以前在文豆培训的时候，宋老师说了什么第一第二第三范式，当时也不是太懂，现在又回头看了一下，看了网上几个简单的例子，觉得不是太难，贴上来，现在在公司经常会涉及到设计数据库，兵家打仗，粮草先行，做网站，数据库必须先设计好。
第一范式：（1NF）无重复的列
表：编号 学生姓名 学生身高 班级名称 班级大小
第二范式：（2NF）属性完全依赖于主键
表：编号 学生姓名 学生身高 班级名称 班级大小
改为
表1： 编号 学生姓名 学生身高
表2： 编号 班级名称 班级大小
第三范式：（3NF）属性不依赖于其它非主属性
表1： 编号 学生姓名 学生身高 班级编号(此时这里不应该再有 班级名称、班级大小这些字段信息)
表2： 编号 班级名称 班级大小
<?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";
