mysql数据库简介ppt_mysql数据库详解.ppt

mysql数据库详解.ppt

MySQL

What is MySQL

An open source Database

Why MySQL

Easy-to-use

Vast user base

Company Tradition

MySQL

What we should know about MySQL

SQL

Storage Engine

Index

Transaction

MySQL

SQL

Select

Insert

Update

Delete

Truncate table

Create table

Alter table

MySQL

Commonly-Used SQL

Fetching rows from a single table

select gid, obj_seq, content from comments where obj_seq = ‘beijing_city_45’ order by cre_date desc limit 10;

select gid, obj_seq, content from comments where obj_seq='beijing_city_45' order by gid asc, cre_date desc limit 10;

select gid, obj_seq, content from comments where obj_seq='beijing_city_45' order by gid asc, cre_date desc limit 50,10;

select count(1) from comments where obj_seq='beijing_city_45'

limit

/peiyinjin/archive/2008/04/15/3199.html

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数，它表示返回最大的记录行数目：

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

//换句话说，LIMIT n 等价于 LIMIT 0,n。

MySQL

Commonly-Used SQL

Join tables

Inner Join

Left Join

Right Join

/article/15386.htm

MySQL

Commonly-Used SQL

Inner Join

SELECT a.buyer_name, a.quantity,b. product_name FROM buyers a Inner JOIN products b ON a.pid = b.id;

| buyer_name | quantity | product_name |

| Steve | 2 | Shoes |

| John | 1 | Laptop |

| Larry | 1 | Monitor |

| Michael | 5 | Monitor |

MySQL

Commonly-Used SQL

Left Join

SELECT a.buyer_name, a.quantity, duct_name FROM buyers a LEFT JOIN products b ON a.pid=b.id;

| buyer_name | quantity | product_name |

| Steve | 2 | Shoes |

| John | 1 | Laptop |

| Larry | 1 |