导航
导航
文章目录
  1. 一、数据库基础
    1. 1.1、数据库概念
    2. 1.2、数据库表间关系
    3. 1.3、登录连接到 MySQL
  2. 二、MySQL 用户与权限
    1. 2.1、MySQL 权限介绍
    2. 2.2、创建MySQL用户(GRANT命令)
    3. 2.3、撤销权限REVOKE命令
  3. 三、MySQL 数据库和表
  4. 四、INSERT 插入数据
  5. 五、SELECT 查询语句
    1. 5.1 单表查询
    2. 5.2 满足特定条件查询
    3. 5.3 双表关联查询
    4. 5.4 多表关联查询
    5. 5.5 左关联查询(查找不匹配行)
    6. 5.6 使用表的别名 aliases
    7. 5.7 列排序 order by
    8. 5.8 分组与合计
    9. 5.9 选择要返回的行
  6. 六、使用子查询
    1. 6.1 基本子查询
    2. 6.2 子查询操作符
    3. 6.3 关联子查询
    4. 6.4 行子查询
    5. 6.5 使用子查询作为临时表
  7. 七、更新数据库记录
  8. 八、修改表结构
  9. 九、删除数据库记录

MySQL 基础篇

本文是 《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 desc
limit 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(所有书)
  2. 然后将查询结果传入子查询(括号内语句)
  3. 接着执行子查询(已被订购的书)
  4. 最后将子查询的结果返回主查询(没有被订购的书)

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;
支持一下
扫一扫,支持一下