虽然并不总是需要主键,但应保证每个表具有一个主键,以便于以后的数据操纵和管理。
表中的任何列都可以作为主键,只要它满足以下条件:
任意两行都不具有相同的主键值;
每个行都必须具有一个主键值(主键列不允许NULL值)。
也可以一起使用多个列作为主键。
完整的命令行选项和参数列表可用mysql --help获得。
help select获得使用SELECT语句的帮助
为了使用crashcourse数据库,应该输入以下内容:
USE crashcourse
SHOW DATABASES:返回可用数据库的一个列表。
SHOW TABLES:返回当前选择的数据库内可用表的列表
SHOW COLUMNS FROM customers;显示表列
SHOW STATUS:用于显示广泛的服务器状态信息
SHOW CREATE DATABASE:用来显示创建特定数据库的MySQL语句
SHOW CREATE TABLE:用来显示创建特定表的MySQL语句
SHOW GRANTS:用来显示授予用户(所有用户或特定用户)的安全权限
SHOW ERRORS:显示服务器错误
SHOW WARNINGS:显示服务器的警告信息
检索数据
多条SQL语句以分号(;)分隔
SQL关键字大写,所有列和表名小写。
- SELECT prod_name FROM products:从products表中检索一个名为prod_name的列(返回的数据的顺序没有特殊意义)
- SELECT prod_id,prod_name,prod_rice FROM products;从products表中选择多个列,列名之间逗号隔开
3.SELECT * FROM products;返回表中所有列
4.SELECT DISTINCT vend_id FROM products;返回products表中,不同的vend_id行
5.SELECT prod_name FROM products LIMIT 5;返回products表中prod_name列的前5行。
6.SELECT prod_name FROM products LIMIT 5,5;第一个5是开始位置,第二个为要检索的行数。(检索出的第一行为0,而不是1)
7.LIMIT 4 OFFSET 3意为从3开始取4行
8.完全限定的表名:SELECT products.prod_name FROM products;
SELECT products.prod_name FROM crashcourse.products;
排序检索数据
用ORDER BY 子句取一个或多个列的名字,据此对SELECT语句检索出的数据进行排序。
1.SELECT prod_name FROM products ORDER BY prod_name;
也可以用非检索的列排序数据。
2.为了按多个列排序,只要指定列名,列名之间逗号分开即可。
SELECT prod_name, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;
检索出3个列,并按其中两个列对结果排序——首先按照价格,再按名称。
3.默认升序排序;若降序排序,指定DESC关键字。
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;
用多个列排序:以价格降序排列产品,然后产品名升序排序,DESC关键字只应用到直接位于其前面的列名。
SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;
ORDER BY和LIMIT组合,能够找出一个列中最高或最低的值:
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
过滤数据
搜索条件也被称为过滤条件。
SELECT prod_name, prod_price
FROM products
WHERE prod_price=2.50
上述语句从products表中检索两个列,但不返回所有行,只返回prod_price值为2.50的行。
注意:同时使用ORDER BY和WHERE子句时,应该让ORDER BY 位于WHERE之后,否则出错。
SELECT prod_name, prod_price
FROM products
WHERE prod_name = 'fuses';
返回prod_name的值为Fuses的一行。MySQL在执行匹配时默认不区分大小写,所以fuses与Fuses匹配。
注意:单引号用来限定字符串,如果将值与串类型的列进行比较,则需要限定引号。
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
用AND连接指定的开始值和结束值。
SELECT prod_name
FROM products
WHERE prod_price IS NULL;
上述返回具有NULL值的列。
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id=1003 AND prod_price<=10;
上述 语句返回vend=1003并且prod_peics<=10的列(注意用AND连接WHERE子句,可连接多个WHERE子句)
注意:OR表示检索匹配任意给定条件的行,AND优先级比OR高。
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
上述SELECT语句检索供应商1002和1003制造的所有产品。IN操作符完成与OR相同的功能。IN操作符一般比OR操作符清单执行更快。
SELECT prod_name, prod_price
FROM products
WHERE vend_id=1002 OR vend_id=1003
ORDER BY prod_name
NOT操作符
NOT:WHERE子句中用来否定后跟条件的关键字。
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
上述语句列出除1002和1003之外的所有供应商制造的产品。
用通配符进行过滤
通配符%:表示任何字符出现任意次数。
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%'
上述语句将检索除任意以jet起头的词。
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%';
上述语句搜索出任何位置包含文本anvil的值。
下划线_:只匹配单个字符
用正则表达式进行搜素
基本字符匹配
下面语句检索列prod_name包含文本1000的所有行。
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
**.**表示匹配任意一个字符,因此上述语句返回1000和2000匹配的。
注意:MySQL的正则表达式匹配不区分大小写,为区分大小写可以用BINARY关键字。如:
WHERE prod_name REGEXP BINARY 'JetPack .000'
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
|为正则表达式的OR操作符,表示匹配其中之一。
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name
[和]:[123]定义一组字符,意思是匹配1或2或3,为[1|2|3]的缩写。
[^123]匹配除这些字符外的任何东西。
[123456789]等价于[0-9],[a-z]匹配任意字母字符。
为了匹配特殊字符,必须用\为前导,\-表示查找-,\.表示查找.。
\f:换页
\n:换行
\r:回车
\t:制表
\v:纵向制表
\\:匹配反斜杠(\)字符本身。
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;
\(匹配),[0-9]匹配任意数字,sticks?匹配stick和sticks(s后的?使s可选,?表示匹配它前面的任何字符的0次或1次出现)
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;
[:digit:]匹配任意数字,因而它为数字的一个集合。{4}确切的要求它前面的字符(任意数字)出现4次。所以[[:digit:]]{4}匹配连在一起的任意4位数字。
上面语句等价于:
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'
ORDER BY prod_name;
定位符
^:在集合中(用[和]定义),表示否定该集合;否则,指串的开始处。
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
分析:^匹配串的开始。上述检索语句表示,只在.或任意数字为串中第一个字符时菜匹配他们。
注意:LIKE匹配整个串,而REGEXP匹配字串。
创建计算字段
字段基本上与列的意思相同。
在MySQL的SELECT语句中,可使用Concat()函数来拼接两个列。
SELECT Concat (vend_name, '(', vend_country, ')')
FROM vendors
ORDER BY vend_name;
Concat()拼接串,即把多个串连接起来形成一个较长的串,各个串之间用逗号分隔。
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
FROM vendors
ORDER BY vend_name;
RTrim()函数去掉值邮编的所有空格。
LTrim()去掉串左边的空格。
Trim()去掉串两边的空格。
别名(alias)是一个字段或值得替换名。别名用AS关键字赋予。
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
执行算数运算
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
SELECT Now()利用Now()函数返回当前日期和时间。
使用数据处理函数
SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;
Upper()将文本转换为大写。
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。
SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact)=Soundex('Y Lie');
匹配所有发音类似于Y.Lie的联系名。
不管是插入或更新表值还是用WHERE子句进行过滤,日期必须为格式yyyy-mm-dd。
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date)= '2005-09-01';
检索出2005年9月的所有订单:
SELECT cust_id,order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
或者
SELECT cust_id,order_num
FROM orders
WHERE Year(order_date)=2005 AND Month(order_date)=9;
汇总数据
AVG()函数
AVG()可返回所有列的平均值,也可返回特定列或行的平均值。
返回表中所有产品的平均价格:
SELECT AVG(prod_price) AS avg_price
FROM products;
返回vend_id=1003的列:
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id=1003;
COUNT()函数
COUNT()函数有两种使用方式。
- 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空
值(NULL)还是非空值。 - 使用COUNT(column)对特定列中具有值的行进行计数,忽略
NULL值。
SELECT COUNT(*) AS num_cust
FROM customers;
上述语句利用COUNT(*)对所有行计数,不管行中各列由什么值。
SELECT COUNT(cust_email) AS num_cust
FROM customers;
只对具有电子邮件地址的客户计数。
MAX()函数
MAX()返回指定列中的最大值。MAX()要求指定列名。
SELECT MAX(prod_price) AS max_price
FROM products;
MIN()函数
MIN()函数返回指定列中的最小值,要求指定列名。
SUM()函数
返回指定列值的和(总计)。
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num=20005;
SUM()也可合计计算值。
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num=20005;
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id=1003;
分组数据 (GROUP BY 和HAVING子句)
WHERE过滤行,HAVING过滤分组。
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
注意:1.如果分组列中具有NULL值,则NULL将作为一个分组返回。如果列中由多行NULL值,他们将分为一组。
2.GROUP BY 子句必须出现在WHERE 子句之后,ORDER BY子句之前。
WHERE过滤行,HAVING过滤分组。HAVING支持所有WHERE操作符。
SELECT cust_id,COUNT(*) AS orders
FROM ORDERS
GROUP BY cust_id
HAVING COUNT(*)>=2;
上述语句过滤COUNT(*)>=2的那些分组。
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price>=10
GROUP BY vend_id
HAVING COUNT(*)>=2;
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50;
为按总计订单价格排序,需要添加ORDER BY子句:
SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price)>=50
ORDER BY ordertotal;
使用子查询
在SELECT语句中,子查询总是从内向外处理。
SELECT order_num
FROM orderitems
WHERE prod_id='TNT2';
SELECT cust_id
FROM orders
WHERE order_num IN(20005,20007);
现在将第一个查询变为子查询,组合两个查询。
SELECT cust_id
FROM orders
WHERE order_num IN(SELECT order_num
FROM orderitems
WHERE prod_id='TNT2');
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id=customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
联结表
联结的创建非常简单,规定要联结的所有表以及它们如何关联即可。
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name, prod_name;
笛卡尔积:由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。如下:
SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name, prod_name;
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id='TNT2'));
下面是使用联结的相同查询:
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num
AND prod_id='TNT2';
创建高级联结
使用表的别名:
SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id=o.cust_id
AND oi.order_num=o.order_num
AND prod_id='TNT2';
自联结
使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。
SELECT prod_id, prod_name
FROM products
WHERE vend_id=(SELECT vend_id
FROM products
WHERE prod_id='DTNTR');
使用联结的相同查询:
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id=p2.vend_id
AND p2.prod_id='DTNTR';
自然联结
自然连接排除多次出现,使每个列只返回一次。
SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
AND oi.order_num=o.order_num
AND prod_id='FB';
外部联结
下面的SELECT语句给出一个简单的内部联结。它检索所有客户及订单:
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
ON customers.cust_id=orders.cust_id;
外部联结类似。为了检索所有客户,包括那些没有订单的客户,可如下进行:
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id=orders.cust_id;
组合查询
利用UNION语句进行组合查询。
有两种基本情况,其中需要使用组合查询:
1.在单个查询中,从不同的表返回类似结构的数据。
2.对单个表执行多个拆线呢,按单个查询返回数据。
创建UNION涉及编写多条SELECT语句。
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price<=5
UNION
SELECT vend_id, prod_id, prod_peice
FROM products
WHERE vend_id IN (1001,1002);
注意:1.在使用UNION时,重复的行被自动取消。如果想返回所有匹配行,可使用UNION ALL而不是UNION。
2.在用UNION组合查询时,只能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后。
全文本搜索
布尔文本搜索
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
上个搜索匹配包含词rabbit和bait的行。
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
上述搜索匹配包含rabbit和bait中的至少一个词的行。
WHERE Match(note_text) Against(' "rabbit bait" ' IN BOOLEAN MODE);
杉树搜索匹配包含rabbit bait而不是匹配两个词rabbit和bait。
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
匹配rabbit和carrot,增加前者的等级,降低后者的等级。
WHERE Match(note_text) Against(' +safe +(<combination)' IN BOOLEAN MODE);
上述搜索匹配词safe和combination,降低后者的等级。
插入数据 INSERT语句
插入可以用几种方式使用:
1.插入完整的行
2.插入行的一部分
3.插入多行
4.插入某些查询的结果
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
SELECT语句从custnew检索出要插入的值,插入customers中。
更新和删除数据 (UPDATE 和 DELETE语句)
可采用两种方式使用UPDATE:
1.更新表中特定行
2.更新表中所有行
基本的UPDATE语句由3部分组成:
1.要更新的表
2.列名和他们的新值
3.确定要更新行的过滤条件
UPDATE customers
SET cust_name='The Fudds',
cust_email='elmer@fudd.com'
WHERE cust_id=10005;
更新多个列时,只需要使用单个SET命令,最后一列之后不用逗号。
为了删除某个列的值,可设置它为NULL(假如表定义允许NULL值)。如下:
UPDATE customers
SET cust_email=NULL
WHERE cust_id=10005;
删除数据
可以用两种删除数据:
1.从表中删除特定的行
2.从表中删除所有行
从customers表中删除一行:
DELETE FROM customers
WHERE cust_id=10006;
注意:DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列,使用UPDATE语句。
TRUNCATE TABLE:从表中删除所有行。
MySQL没有撤销按钮。
创建和操纵表
一般有两种创建表的方法:
1.使用具有交互式创建和管理表的工具。
2.直接用MySQL语句操纵。用 CTEATE TABLE 语句。
利用 CREATE TABLE创建表,必须给出下列信息:
1.新表的名字,在关键字CREATE TABLE之后给出
2.表列的名字和定义,用逗号分隔
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
注意:如果你想在一个表不存在时创建它,应该在表名后给出 IF NOT EXISTS。
使用NULL值
NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列,该列必须有值。
主键
主键值必须唯一。即,表中的每个行必须具有唯一的主键值。如果主键使用多个列,则它的值必须唯一。如果使用多个列,则这些列的组合值必须唯一。
为创建由多个列组成的主键,应该以逗号分隔的列表给出各列名,如下:
CREATE TABLE orderitems
(
order_num int NOT NULL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
PRIMARY KEY (order_num,order_item)
) ENGINE=InnoDB;
使用AUTO_INCREMENT
SELECT last_insert_id()
此语句返回最后一个AUTO_INCREMENT值。
引擎类型
1.InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索。
2.MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表)
3.MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
更新表 用ALTER TABLE语句
为了使用ALTER TABLE更改表结构,必须给出下面的信息:
1.在ALTER TABLE之后给出要更改的表名(该表必须存在,否则将出错)。
2.所做更改的列表。
给表添加一个列:
ALTER TABLE venders
ADD vend_phone CHAR(20);
这条语句给vendors表增加一个名为vend_phone的列,必须明确其数据类型。
删除刚刚添加的列:
ALTER TABLE vendorss
DROP COLUMN vend_phone;
ALTER TABLE的一种常见用途是定义外键。
删除表
删除表(删除整个表而不是其内容)非常简单,使用 DROP TABLE 语句即可:
DROP TABLE customers2;
重命名表
RENAME TABLE所做的仅是重命名一个表。可以使用下面的语句对多个表重命名:
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;
使用视图
视图的创建:
1.用 CREATE VIEW 语句创建
2.使用 SHOW CREATE VIEW viewname; 来查看创建视图的语句。
3.用DROP删除视图,其语法为DROP VIEW viewname;。
4.更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。