《PHP和MySQL Web开发》MySQL 基础篇

本文是 《PHP和MySQL Web开发》 第8、9、10章阅读笔记，主要介绍 MySQL 入门基础知识！ 一、数据库基础 1.1、数据库概念 表：关系数据库由关系组成，关系通常称为表。 列：表中的每一列都有一个名称和数据类型，列也叫“域”或“属性”。 行：关系类型每一行具有相同的属性，行也称“记录”。 健：表中标志列称为健或主键，可用来做表格间的引用。 外键：主键出现在另一个表时，该主键称为外键。 1.2、数据库表间关系 一对一：主键-主键 一对多：主键–外键 多对多：主键1–外键1外键2–主键2 1.3、登录连接到 MySQL 1 2 语法：mysql -h主机地址 -u用户名 －p用户密码 实例：mysql -h127.0.0.1 -uroot -p123456 ■-h主机地址，可以有空格，若省略则使用localhost。 ■-u用户名，可以有空格，若省略则使用登录操作系统的用户名。 ■-p用户密码，必须相连不能有空格。 ■MySQL 每个命令用分好隔开。 ■SQL 语句不区分大小写，数据库和表名称则区分大小写。 二、MySQL 用户与权限 2.1、MySQL 权限介绍 MySQL 权限分四个级别，分别是全局、数据库、表、列。每个级别都有三种类型权限：1、用户权限，2、管理员权限，3、特殊权限。 用户权限：1）select（查询），2）insert（插入），3）update（修改），4）delete（删除表），5）index（索引），6）alter（变更表结构），7）create（创建数据库），8）drop（删除库或表） 管理员权限：1）create temporary tables，2）file，3）lock tables，4）process，5）reload，6）replication client，7）replication slave，8）show databases，9）shutdown，10）super 特殊权限：1） all（用户+管理权限），2）usage（不授予权限） 2.2、创建MySQL用户（GRANT命令） 1 2 3 4 5 GRANT 权限 [列] ON 数据库.表 TO 用户名@主机 [IDENTIFIED BY ‘password’] [require ssl_option] [WITH [GRANT OPTION | limit_options] ] 1) *.* 指所有数据库和所有表。 2) * 指选中的数据库和所有表。 3) dbname.* 指选定数据库中的所有表。 4) “%” 是默认值，指所有主机。 5) with grant option 表示允许用户向被人授予自己拥有的权限。 limit_options 可用选项有： 1 2 3 max_queries_per_hour n max_updates_per_hour n max_connections_per_hour n 2.3、撤销权限REVOKE命令 1 2 3 REVOKE 权限 [,GRANT OPTION] ON 数据库.表 FROM 用户名@主机 注：撤销grant option权限，可不指定数据库和表名。 三、MySQL 数据库和表 创建数据库：create database 数据库名; 使用数据库：use 数据库名; 删除数据库：drop database 数据库名称; 创建数据库表：create table 数据表名称 (列名称); 删除数据库表：drop table 表名称； 1 2 3 4 5 6 CREATE TABLE customers (customersid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(10) NOT NULL, Amount FLOAT(6,2) NOT NULL, DATE DATE NOT NULL ); 整形：1) smallint、2) int、3)bigint 浮点型：1) float(长度,小数位数)、2) double(长度,小数位数) 时间日期：1) date (YYYY-MM-DD)、2) time (HH:MM:SS)、3) datetime (YYYY-MM-DD HH:MM:SS)、3) year[(2|4)] (11或2011) 字符串：常用的有char（固定字符数）和text(长文本） unsigned：表示只能为0或正数 not null：表示必须有一个值，但可以为空(null) auto_increment：表示自动插入一个唯一标识值 primary key：主键 四、INSERT 插入数据 方法一 1 2 INSERT 表 [(列1,列2)] VALUES (‘值1′,’值2’); INSERT 表 [(列1,列2)] VALUES (‘值1′,’值2’)，(‘值1′,’值2′); （插入多行记录） 方法二 1 INSERT 表 SET 列1=’值1′,列2=’值2′,列3=’值3′; 注：数字和日期不需要引号 五、SELECT 查询语句 5.1 单表查询 1 SELECT 列1,列2　FROM 表 注：* 指所有列 5.2 满足特定条件查询 1 SELECT * FROM orders WHERE customerid=1; 比较运算符：=,>,=, 50; 给出每个顾客的平均值，而不是全部顾客平均值。 having类似于where,但having只适用于合计与分组。 5.9 选择要返回的行 1 2 3 SELECT name FROM customers LIMIT 0,3; limit 带两个参数,起始行号和返回行数。 行号是已0开始索引的 六、使用子查询 6.1 基本子查询 实例：查找金额最大的订单 1 2 3 SELECT customerid,amount FROM orders WHERE amount=(SELECT MAX(amount) FROM orders); 过程:先查最大金额是多少,然后查该金额的订单信息。 “=”是比较操作符，”=”右边可以插入子查询操作符。 1 2 3 FROM orders ORDER BY amount DESC LIMIT 1; 6.2 子查询操作符 ANY (SOME) 查询t1表c1列，但该列必须大于t2表c1列中任意一个值。 1 2 SELECT c1 FROM t1 WHERE c1 > any (SELECT c1 FROM t2); IN (=any) 查询t1表c1列，但该列必须等于t2表c1列中任意一个值。 1 2 SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2); All 查询t1表c1列，但该列必须大于t2表c1列中所有值。 1 2 SELECT c1 FROM t1 WHERE c1 > ALL (SELECT c1 FROM t2); 6.3 关联子查询 1 2 books(ISBN,author,title,price) order_items(orderid,isbn,quantily) 1 2 3 4 SELECT isbn ,title FROM books WHERE NOT EXISTS (SELECT * FROM order_items WHERE order_items.isbn=books.isbn) 首先查询books表中的isbn和title（所有书） 然后将查询结果传入子查询（括号内语句） 接着执行子查询（已被订购的书） 最后将子查询的结果返回主查询（没有被订购的书） exists 是子查询炒作符，只能用在关联子查询中。 6.4 行子查询 实例：在一个表中查找存在于另一个表的行 1 2 3 SELECT c1,c2,c3 FROM t1 WHERE (c1,c2,c3) IN (SELECT c1,c2,c3 FROM t2); 6.5 使用子查询作为临时表 将子查询放在FROM子句中，必须为子查询的结果定义一个别名。 1 2 SELECT * FROM (SELECT customerid,name FROM customers WHERE city=’Box Hill’) AS box_hill_customers; 七、更新数据库记录 1 2 3 4 5 UPDATE 表名 SET 列1=表达式1,列2=表达式2,… WHERE 条件 ORDER BY 条件 LIMIT x,x 实例:修改所有行记录 1 2 UPDATE books SET price=price*1.1; 实例2:修改某一行记录 1 2 3 UPDATE customers SET address=’xxx’ WHERE customerid=4; 八、修改表结构 实例1：修改列数据类型 1 2 ALTER TABLE customers MODIFY name CHAR(70) NOT NULL; 实例2：增加一列 1 2 ALTER TABLE orders ADD tax FLOAT(6,2) after amount; 实例3：删除一列 1 2 ALTER TABLE orders DROP tax; 九、删除数据库记录 1 2 3 4 DELETE FROM 表名 WHERE 条件 ORDER BY 条件 LIMIT x,x 实例1：删除所有行 1 DELETE FROM customers; 实例2：删除某一行 1 2