归档文章 (2011-2017)

date
2011/08/12
本文是 《PHP和MySQL Web开发》 第8、9、10章阅读笔记，主要介绍 MySQL 入门基础知识！

一、数据库基础

1.1、数据库概念

表：关系数据库由关系组成，关系通常称为表。 列：表中的每一列都有一个名称和数据类型，列也叫“域”或“属性”。 行：关系类型每一行具有相同的属性，行也称“记录”。 健：表中标志列称为健或主键，可用来做表格间的引用。 外键：主键出现在另一个表时，该主键称为外键。

1.2、数据库表间关系

一对一：主键-主键 一对多：主键–外键 多对多：主键1–外键1外键2–主键2

1.3、登录连接到 MySQL

语法：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、特殊权限。
用户权限： - select（查询） - insert（插入） - update（修改） - delete（删除表） - index（索引） - alter（变更表结构） - create（创建数据库） - drop（删除库或表）
管理员权限： - create temporary tables - file - lock tables - process - reload - replication client - replication slave - show databases - shutdown - super
特殊权限： - all（用户+管理权限） - usage（不授予权限）

2.2、创建MySQL用户（GRANT命令）

grant 权限 [列]
on 数据库.表
to 用户名@主机 [identified by 'password']
[require ssl_option]
[with [grant option | limit_options] ]
  • .* 指所有数据库和所有表。
  • 指选中的数据库和所有表。
  • dbname.* 指选定数据库中的所有表。
  • "%" 是默认值，指所有主机。
  • with grant option 表示允许用户向被人授予自己拥有的权限。
limit_options 可用选项有：
max_queries_per_hour n
max_updates_per_hour n
max_connections_per_hour n

2.3、撤销权限REVOKE命令

revoke 权限 [,grant option]
on 数据库.表
from 用户名@主机
注：撤销grant option权限，可不指定数据库和表名。

三、MySQL 数据库和表

创建数据库：create database 数据库名; 使用数据库：use 数据库名; 删除数据库：drop database 数据库名称; 创建数据库表：create table 数据表名称 (列名称); 删除数据库表：drop table 表名称；
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 插入数据

方法一
insert 表 [(列1,列2)] values ('值1','值2');
insert 表 [(列1,列2)] values ('值1','值2')，('值1','值2'); （插入多行记录）
方法二
insert 表 set 列1='值1',列2='值2',列3='值3';
注：数字和日期不需要引号

五、SELECT 查询语句

5.1 单表查询

select 列1,列2　from 表
注：* 指所有列

5.2 满足特定条件查询

select * from orders where customerid=1;
比较运算符：=,>,<,>=,<=,!=,is not null,is null,between,in,not in,like,not like,regexp
like是模式匹配，由文本加表示任意数量字符的”%“和表示单个字符的”_“组成。 如：name like (”fred %“)

5.3 双表关联查询

customers(customerid,name,address,city)
orders(orderid,customerid,amount,date)
books(ISBN,author,title,price)
order_items(orderid,isbn,quantily)
实例：查询某个顾客所有订单
SELECT orders . *
FROM orders, customers
WHERE customers.name = 'Julie Smith'AND customers.customerid = orders.customerid;
注意：切记不要遗漏最后一行，否则虽查询到该用户的所有记录（例如有4条记录），但这些记录对应的订单记录可能不是该用户的，因为MySQL只是按顺序列出前4条查询结果。
表名称间逗号表示完全关联，它将所有表所有记录完全列出，故双表查询一定要指定关联条件。
关联条件可以是等价关联，如客户表中的客户ID=订单表的客户ID。

5.4 多表关联查询

实例：哪些顾客订购了关于Java的书
select customers.name,orders.orderid,books.title
from customers,orders,order_items,books
//至少有一个订单的客户（结果可能有多个客户）
where customers.customerid=orders.customerid
//订单所包含的项目（每个客户每张订单可能有多个项目）
and orders.orderid=order_items.orderid
//项目的详细信息
and order_items.isbn=books.isbn
//包含'java'一词的书
and books.title LIKE '%java%'
注：关联条件的总数量要比关联的表少一个。

5.5 左关联查询（查找不匹配行）

实例：查询没有任何订单的客户。
select customers.name,orders.orderid
from customers left join orders
//该行可以用"using (customerid)"代替。
//使用using语句，两个表中的列名称必须相同。
on customers.customerid=orders.customerid
where orders.orderid is null
左关联：检索左表（客户表）中的每条记录，是否在右表（订单表）有匹配记录（按关联条件指定的匹配规则），如果没有则增加一行，该行右边的列为NULL

5.6 使用表的别名 aliases

select c.name
from customers as c,orders as o
where c.customerid=o.customerid
注：当要关联一个表到表本身的时候就必须使用表的别名
实例：查找同城顾客
select c1.name,c1.city,c2.name,c2.city
form customers as c1,customers as c2
where c1.city=c2.city and c1.name != c2.name;

5.7 列排序 order by

select name,address
from customers
order by name desc;
注：默认是升序（asc)，也可以使用降序（desc）

5.8 分组与合计

常用合计函数
select avg(amount)
from orders;
avg(列)：求平均值 sun(列)：求和 count(列)：非空值的行数 count(distinct 列)：本列中不同值的行数 count(*)：包含空值在内的行数
分组 group by
select customerid,avg(amount)
from orders
group by customerid
having avg(amount) > 50;
给出每个顾客的平均值，而不是全部顾客平均值。 having类似于where,但having只适用于合计与分组。

5.9 选择要返回的行

select name
from customers
limit 0,3;
limit 带两个参数,起始行号和返回行数。 行号是已0开始索引的

六、使用子查询

6.1 基本子查询

实例：查找金额最大的订单
select customerid,amount
from orders
where amount=(select max(amount) from orders);
过程:先查最大金额是多少,然后查该金额的订单信息。
“=”是比较操作符，“=”右边可以插入子查询操作符。
from orders
order by amount desclimit 1;

6.2 子查询操作符

ANY (SOME)
查询t1表c1列，但该列必须大于t2表c1列中任意一个值。
select c1 from t1
where c1 > any (select c1 from t2);
IN (=any)
查询t1表c1列，但该列必须等于t2表c1列中任意一个值。
select c1 from t1
where c1 in (select c1 from t2);
All
查询t1表c1列，但该列必须大于t2表c1列中所有值。
select c1 from t1
where c1 > all (select c1 from t2);

6.3 关联子查询

books(ISBN,author,title,price)
order_items(orderid,isbn,quantily)
select isbn ,title
from books
where not exists(select * from order_items where order_items.isbn=books.isbn)
  1. 首先查询books表中的isbn和title（所有书）
  1. 然后将查询结果传入子查询（括号内语句）
  1. 接着执行子查询（已被订购的书）
  1. 最后将子查询的结果返回主查询（没有被订购的书）
exists 是子查询炒作符，只能用在关联子查询中。

6.4 行子查询

实例：在一个表中查找存在于另一个表的行
select c1,c2,c3
from t1
where (c1,c2,c3) in (select c1,c2,c3 from t2);

6.5 使用子查询作为临时表

将子查询放在FROM子句中，必须为子查询的结果定义一个别名。
select * from(select customerid,name from customers where city='Box Hill') as box_hill_customers;

七、更新数据库记录

update 表名
set 列1=表达式1,列2=表达式2,...
where 条件
order by 条件
limit x,x
实例:修改所有行记录
update books
set price=price*1.1;
实例2:修改某一行记录
update customers
set address='xxx'where customerid=4;

八、修改表结构

实例1：修改列数据类型
alter table customers
modify name char(70) not null;
实例2：增加一列
alter table orders
add tax float(6,2) after amount;
实例3：删除一列
alter table orders
drop tax;

九、删除数据库记录

delete from 表名
where 条件
order by 条件
limit x,x
实例1：删除所有行
delete from customers;
实例2：删除某一行
delete from customers
where customerid=5;
If you have any questions, please contact me.