数据库中的范式说明,以前在文豆培训的时候,宋老师说了什么第一第二第三范式,当时也不是太懂,现在又回头看了一下,看了网上几个简单的例子,觉得不是太难,贴上来,现在在公司经常会涉及到设计数据库,兵家打仗,粮草先行,做网站,数据库必须先设计好。
第一范式:(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"; // 获取字段的类型/名称/长度 <?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(); ?> |