文章目录
1 了解SQL
1.1数据库基础
Q1:数据库是什么?
数据库(database):保存有组织的数据的容器
数据库管理系统(DBMS):我们不直接访问数据库,而是由DBMS代替我们访问
Q2:表是什么?
表(table):特定类型数据的结构化清单。
为了把不同类型数据区分开,往往需要在一个数据库里建多个表,方便以后的检索和访问。
Q3:模式是什么?
模式(schema):关于数据库和表的布局及特性的信息
用来描述数据库中特定的表以及整个数据库;有时候也可用作数据库的同义词。
Q4:列与行
列(column):表中的一个字段。所有表都是由一个或者多个列组成的。
表由列组成。列中存着某类特定信息,例如地址、城市、邮政编码存在各自的列中。正确地将数据分解为多个列很重要,通过对数据进行分解,才能利用特定列对数据进行排序和过滤。
数据类型(datatype):每个列都有相应的数据类型,它限制着改列存储的数据。
行(row):表中的一个记录
行(row)和记录(record)的说法很大程度上可以相互替代。
Q5.1:主键
主键(primary key):一行中唯一标识自己的一列(或一组列)
应该总是定义主键,虽然并不总是都需要主键,但还是应该保证创建的每个表都具有一个主键,以便以后的数据操纵和管理。
Q5.2 Mysql主键值规则:
- 主键值是唯一的
- 主键列不允许NULL值
主键通常定义在一列上,但也可以是多个列。当多个列作为主键时,所有列值的组合应该是唯一的。
Q5.3主键设置的好习惯
- 不更新主键值
- 不重用主键值
- 不在主键列中使用可能会更改的值
1.2 什么是SQL
SQL是 Structured Query Language 的缩写,是一种专门用来与数据库通信的语言。
和Java等语言相比,SQL由很少的词构成,目的是提供一种从数据库读写数据的简单有效的方法。
2 MySQL简介
2.1什么是MySQL
MySQL是一种DBMS,它有几个优势:
- 成本低。免费使用
- 性能好。执行很快
- 可信赖。非常多公司都使用它
- 很简单。很容易安装和使用
缺点:
- 不总是支持其他DBMS的功能和特性。但这一点在不断得到改善。
Q1:客户机-服务器软件
DBMS可分为两类:
- 基于共享文件系统的DBMS,如EXCEL、Mysrosoft Acess,通常用于桌面用途
- 基于客户机-服务器的DBMS。可能安装在不同的计算机上,客户机提交请求,服务器处理请求。
Mysql DBMS是服务器软件
Python、Java是客户机
3 数据库和表
3.1数据库
查询数据库 SHOW
显示可用数据库的列表
SHOW DATABASES;
选择数据库 USE
USE 数据库名称;
# USE study;
3.2数据库表
查询可用表 SHOW
返回当前数据库内可用表的列表
SHOW TABLES;
显示表列 DESCRIBE
SHOW COLUMNS FROM 表名;
DESCRIBE 表名;
为了学习,创建了一个user表,如下所示:
SELECT * FROM user;
±—±------------±-------+
| ID | name | gender |
±—±------------±-------+
| 1 | xiaoming | 男 |
| 2 | xiaohong | 女 |
| 3 | xiaogang | 男 |
| 4 | xiaochongzi | 男 |
| 5 | xiaohua | 女 |
±—±------------±-------+
4 检索
检索单个列 SELECT FROM
SELECT name FROM user;
数据没有过滤也没有排序
检索多个列
SELECT name,gender FROM user;
检索所有列
SELECT * FROM user;
尽量避免使用通配符*
虽然会省事,但会降低检索和应用程序的性能。
检索不同的行 DISTINCT
SELECT DISTINCT gender FROM user;
返回不同的值,上面代码返回
±----------+
| gender |
±----------+
| 男 |
| 女 |
±----------+
多个列时,将会检索他们不同排列组合的情况
SELECT DISTINCT gender,name FROM user
限制结果 LIMIT
限制返回前三行
SELECT name FROM user LIMIT 3;
从行3开始查3行
SELECT name FROM user LIMIT 3,3;
Mysql 5支持另一种方式
SELECT name FROM user LIMIT 3 OFFSET 4;
从行4开始,查3行
完全限定表名
SELECT user.name FROM study.user;
5 排序检索
关型数据库认为,如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。
SQL语句由子句(clause)构成,有些子句是必须的,有些是可选的。
单个列排序 ORDER BY
# 利用order by来实现顺序排序
SELECT name FROM user ORDER BY name;
多个列排序
例如先按姓排,再按名排
SELECT id,name,gender FROM user ORDER BY gender,id;
±—±------------±-------+
| id | name | gender |
±—±------------±-------+
| 2 | xiaohong | 女 |
| 5 | xiaohua | 女 |
| 1 | xiaoming | 男 |
| 3 | xiaogang | 男 |
| 4 | xiaochongzi | 男 |
±—±------------±-------+
降序排列 DESC
# 需要降序的末尾加上DESC
SELECT id,name,gender
FROM user
ORDER BY gender DESC,id DESC;
6 过滤数据
where子句
SELECT id,name,gender
FROM user
WHERE id > 2
ORDER BY gender;
# 顺序上,SELECT > FROM > WHERE > ORDER BY > LIMIT
SELECT id,name,gender
FROM user
WHERE id > 2
ORDER BY gender LIMIT 2 ;
where子句操作符
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
BETWEEN … AND … | 在指定两个值之间(包含始末位置) |
空值检查 IS NULL
SELECT id,name
FROM user
WHERE gender IS NULL;
条件过滤的时候,空值的记录不会被匹配到
7 操作符 过滤数据
AND
SELECT id,name,gender FROM user WHERE id < 3 AND gender = '男';
OR
SELECT id,name,gender
FROM user
WHERE id = 3 OR id = 2;
当同时出现AND和OR时,应注意加括号,不能过分依赖计算次序。
IN
SELECT id,name
FROM user
WHERE id IN (2,3)
可以用OR来代替。但IN的速度更快,也更清楚。最大的好处是可以包含其他SELECT语句。
NOT
SELECT id,name
FROM user
WHERE id NOT IN (2,3)
NOT用来对其他条件取反
8 通配符 过滤
LIKE操作符
通过LIKE,使得在搜索子句中使用通配符。
% 通配符
%是最常使用的通配符。%表示任何字符出现任意次数。
找到所有以词xiao起头的姓名:
SELECT id,name
FROM user
WHERE name LIKE 'xiao%';
_ 通配符
和%类似,但只能匹配单个字符
使用通配符的技巧
- 不要过度使用通配符,太慢了
- 如果需要使用,不要把他们用在搜索模式的开始处,这样最慢
9 用正则表达式进行搜索
介绍
Q: 为什么使用正则表达式?
随着过滤条件的复杂性的增加,WHERE子句本身的复杂性也会增加。用正则会简单便捷。
基本字符匹配 REGEXP
检索列 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;
区分大小写
自3.23.4版本后,正则表达式不区分大小写。为区分大小写,可以用关键字BINARY
SELECT prod_name
FROM products
WHERE prod_name
REGEXP BINARY 'Apple.range'
ORDER BY prod_name;
OR匹配 |
SELECT prod_name
FROM products
WHERE prod_name
REGEXP '1000|2000';
匹配几个字符 [ ] ^
匹配’1 ton’、‘2 ton’、‘3 ton’
SELECT prod_name
FROM products
WHERE prod_name
REGEXP '[123] ton';
^ 表示否定一个字符集(取反)
匹配除了’1 ton’、‘2 ton’、‘3 ton’之外的带’ ton’的字符
SELECT prod_name
FROM products
WHERE prod_name
REGEXP '[^123] ton';
匹配范围
匹配 0-9 :[0123456789] 或者 [0-9]
匹配 c-k : [c-k]
匹配特殊字符
为了匹配特殊字符,必须用 \\ 为前导。
匹配带 - 的字符
SELECT prod_name FROM products WHERE prod_name REGEXP '\\-';
匹配字符类
类 | 说明 |
---|---|
[:alnum:] | 任意字母和数字(同[a-zA-Z0-9]) |
[:alpha:] | 任意字符(同[a-zA-Z]) |
[:blank:] | 空格和制表符(同[\\t]) |
[:cntrl:] | ASCII控制字符(ASCII0-31 和 127) |
[:digit:] | 任意数字(同[0-9]) |
[:graph:] | 与 [:print:] 相同,但不包括空格 |
[:lower:] | 任意小写字符(同 [a-z] ) |
[:print:] | 任意可打印字符 |
[:punct:] | 既不在[:alnum:],又不在[:cntrl:]中的任意字符 |
[:space:] | 包括空格在内的任意空白字符(同[\\f\\n\\r\\t\\v]) |
[:upper:] | 任意大写字符(同 [A-Z] ) |
[:xdigit:] | 任意十六进制数字(同 [a-fA-F0-9] ) |
匹配多个实例
元字符 | 说明 |
---|---|
* | 0个或多个匹配 |
+ | 1个或多个匹配,等于{1,} |
? | 0个或1个匹配,等于{0,1} |
{n} | 指定数目的匹配 |
{n,} | 不少于指定数目的匹配 |
{n,m} | 匹配数目的范围(m不超过255) |
几个例子:
‘\\([0-9] sticks?\\)’
- \\( 表示(
- sticks? 中的 ? 表示 s 可以是0或1个
‘[[:digit:]]{4}’
表示连在一起的4个数字
定位符
匹配特定位置的字符
元字符 | 说明 |
---|---|
^ | 文本的开始 |
$ | 文本的结束 |
[[:<:]] | 词的开始 |
[[:>:]] | 词的结尾 |
例如匹配以数字开始的所有字符
SELECT id,name FROM user WHERE id REGEXP '^[0-9].';
^有两种用法,在 [] 中则为否定,否则指串的开始处
10 创建计算字段
拼接字段
多数DBMS使用 “+” 或者 “||”来拼接,但MySQL使用Concat()函数来完成拼接
# 拼接id和name,形成name(id)的样式
SELECT Concat(name,'(',id,')')
FROM user
ORDER BY id;
Trim函数去掉空格
# 去掉id和name右边的空格
SELECT Concat(RTrim(name),'(',RTrim(id),')')
FROM user
ORDER BY id;
RTrim( ) 去掉右边的空格
LTrim( ) 去掉左边的空格
Trim( ) 去掉左右两边的空格
别名(alias)
单纯的拼接因为没有字段名,所以没办法引用,只能查看结果。因此需要别名。
# name_id即为别名
SELECT Concat(name,'(',id,')') AS name_id
FROM user
ORDER BY id;
计算字段
#
SELECT Concat(name,'(',id,')') AS name_id,id,id*1000
AS id_1000
FROM user
ORDER BY id;
11 使用数据处理函数
注意:函数的可移植性不强,需要做好注释
文本处理函数
函数 | 说明 |
---|---|
Lower( ) | 转换为小写 |
Trim( ) | 删除空格 |
Upper( ) | 转换为大写 |
Left( ) | 返回串左侧字符 |
Length( ) | 返回串的长度 |
Locate( ) | 找出串的一个子串 |
SubString( ) | 返回子串的字符 |
Soundex( ) | 返回串的SOUNDEX值(读音),例如检索Soundex(Lee),Lie也能被检索到 |
日期和事件处理函数
函数 | 说明 |
---|---|
AddDate()、AddTime() | 增加一个日期(天、周等);增加一个时间(时、分等) |
Now() | 返回当前的日期及时间 |
CurDate()、CurTime() | 返回当前日期; 时间 |
Date()、Year()、Month()、Day()、Time()、Hour()、Minute()、Second() | 返回日期时间的日期、年份、月份、天数、时间、小时、分钟、秒钟部分 |
DateDiff() | 计算两个日期之差 |
Date_Format() | 返回一个格式化的日期或时间串 |
DayOfWeek() | 返回一个日期对应的星期几 |
日期的格式必须为yyyy-mm-dd (例如2021-04-06)
# 检索所有2015年9月的订单SELECT id,name,Date(time) From ordersWHERE Year(time) = 2015 AND Month(time) = 9;
数值处理函数
函数 | 说明 |
---|---|
Abs() | 绝对值 |
Cos()、Sin()、Tan() | 三角函数 |
Exp() | 指数值 |
Mod() | 返回除操作的余数 |
Pi() | π值 |
Rand() | 随机数 |
Sqrt() | 平方根 |
数值处理函数的一致性一般很好
12 汇总数据
我们经常会统计各种数据,而不必把数据实际检索出来,因此可使用聚集函数。
函数 | 说明 |
---|---|
AVG() | 平均值 |
COUNT() | 行数 |
MAX() | 最大值 |
MIN() | 最小值 |
SUM() | 某列和 |
AVG平均
只能求单个列的平均
会忽略NULL值
COUNT行数
两种使用方式:
- COUNT(*) 对所有行数进行计数,包括NULL值
- COUNT(列名) 忽略NULL值
MAX最大 MIN最小 SUM和值
允许对于文本数据使用,返回文本最大值、最小值
13分组数据
创建分组
想要统计每个类别的数量时,通过分组把数据分为多个逻辑组,以便对每个组进行聚集计算。
SELECT gender,COUNT(*) AS gender_num from user Group by gender;
±-------±---------+
| gender | gender_num |
±-------±---------+
| 男 | 3 |
| 女 | 2 |
±-------±---------+
使用WITH ROLLUP关键字,可以得到每个分组以及每个分组汇总级别的值
SELECT gender,COUNT(*) AS gender_num from user Group by gender WITH ROLLUP;
±-------±---------+
| gender | gender_num |
±-------±---------+
| 女 | 2 |
| 男 | 3 |
| NULL | 5 |
±-------±---------+
过滤分组
WHERE用来过滤行,因此不能用来过滤分组,相应的操作可以用HAVING来代替。除了过滤对象不同外,HAVING 和WHERE没有任何差别。
SELECT gender,COUNT(*) AS gender_num from user Group by gender HAVING COUNT(*) > 2;
±-------±-----------+
| gender | gender_num |
±-------±-----------+
| 男 | 3 |
±-------±-----------+
14 使用子查询
版本要求:MYSQL4.1以上版本支持子查询
查询(query):任何sql语句都是查询,但此术语一般指SELECT语句。
子查询(subquery):即嵌套在其它查询中的查询。
利用子查询进行过滤
例如:查找名字里带’hong’的用户里住在上海的
新建一个表address:
±—±-----+
| id | city |
±—±-----+
| 1 | 北京 |
| 2 | 上海 |
| 3 | 北京 |
| 4 | 杭州 |
| 5 | 上海 |
±—±-----+
SELECT id,name
FROM user
WHERE name regexp 'hong' AND id IN (SELECT id
FROM address
WHERE city = '上海');
±—±---------+
| id | name |
±—±---------+
| 2 | xiaohong |
±—±---------+
作为计算字段的子查询
新建一个表 orders:
±---------±------------±------+
| order_id | name | order_num |
±---------±------------±------+
| 1 | xiaoming | 23 |
| 2 | xiaoming | 36 |
| 3 | xiaogang | 77 |
| 4 | xiaohong | 51 |
| 5 | xiaochongzi | 123 |
| 6 | xiaoming | 11 |
| 7 | xiaogang | 13 |
±---------±------------±------+
统计住在北京的用户各自的订单总金额
select name,sum(order_num)
from orders
group by name
having name in (select name
from user
where id in(select id
from address
where city = '北京'));
±---------±---------------+
| name | sum(order_num) |
±---------±---------------+
| xiaoming | 70 |
| xiaogang | 90 |
±---------±---------------+
15 联结表
Q:为什么使用联结?
我们通常会分解数据为多个表,来进行有效地存储,这样使得数据方便处理且具有更大的可伸缩性。相应的,查询就会变得复杂。
如果数据存储在多个表中,怎样用单条SELECT语句检索出数据?答案是使用联结。
联结是一种机制,用来在一条SELECT语句中关联表。
内部联结(等值联结)
创建联结很简单,只需要规定要联结的所有表,以及它们如何关联即可。
select user.id,name,city
from user,address
where user.id = address.id
order by user.id;
±—±------------±-----+
| id | name | city |
±—±------------±-----+
| 1 | xiaoming | 北京 |
| 2 | xiaohong | 上海 |
| 3 | xiaogang | 北京 |
| 4 | xiaochongzi | 杭州 |
| 5 | xiaohua | 上海 |
±—±------------±-----+
【分析】
FROM 从两个表取数据
WHERE 指示mysql匹配user表的id和address表的id
需要完全限定列名,比如user.id,以避免二义性
WHERE子句的重要性:如果不用WHERE进行限定,那么联结的结果就会是笛卡尔积。行数=两个表的行数之积。
对于内部联结可以使用稍微不同的语法来明确指定联结的类型。下面的SELECT语句返回与前面完全相同的数据:
SELECT user.id,name,city
FROM user INNER JOIN address
ON user.id = address.id
ORDER BY user.id;
【分析】
SELECT语句相同,但FROM子句不同,
两个表之间的关系用 INNER JOIN 指定。
在使用这种语法时,联结条件用特定的ON子句而不是WHERE。
联结多个表
select user.id,user.name,city,order_num
from user,address,orders
where user.id = address.id
and user.name = orders.name;
±—±------------±-----±----------+
| id | name | city | order_num |
±—±------------±-----±----------+
| 1 | xiaoming | 北京 | 23 |
| 1 | xiaoming | 北京 | 36 |
| 1 | xiaoming | 北京 | 11 |
| 2 | xiaohong | 上海 | 51 |
| 3 | xiaogang | 北京 | 77 |
| 3 | xiaogang | 北京 | 13 |
| 4 | xiaochongzi | 杭州 | 123 |
±—±------------±-----±----------+
16 创建高级联结
使用表别名
使用表别名可以缩短sql语句,便于在单条select语句中多次使用相同的表。
select u.id, u.name, city, order_num
from user as u, address as a, orders as o
where u.id = a.id
and u.name = o.name;
自联结
联结的两个表是同一个表
select u1.id, u1.name, u1.gender
from user as u1, user as u2
where u1.id = u2.id
and u2.gender = '男';
自然联结
自然联结排除多次出现,使得每个列只返回一次。
事实上,我们迄今为止建立的每个联结都是自然联结,很可能永远都不会用到不是自然联结的内部联结。
外部联结
有时候需要包含没有关联行的那些行。
内部联结:
select u.id, u.name, o.order_num
from user as u inner join orders as o on u.name = o.name
order by u.id;
±—±------------±----------+
| id | name | order_num |
±—±------------±----------+
| 1 | xiaoming | 23 |
| 1 | xiaoming | 36 |
| 1 | xiaoming | 11 |
| 2 | xiaohong | 51 |
| 3 | xiaogang | 77 |
| 3 | xiaogang | 13 |
| 4 | xiaochongzi | 123 |
±—±------------±----------+
外部联结:
select u.id, u.name, o.order_num
from user as u left outer join orders as o on u.name = o.name
order by u.id;
±—±------------±----------+
| id | name | order_num |
±—±------------±----------+
| 1 | xiaoming | 23 |
| 1 | xiaoming | 36 |
| 1 | xiaoming | 11 |
| 2 | xiaohong | 51 |
| 3 | xiaogang | 77 |
| 3 | xiaogang | 13 |
| 4 | xiaochongzi | 123 |
| 5 | xiaohua | NULL |
±—±------------±----------+
与内部联结不同的是,外部联结还包括没有关联行的行。
使用OUTER JOIN时,必须指定 RIGHT 或者 LEFT ,以指定选择哪一边的所有行。
使用带聚集函数的联结
检索住在北京的人各自的订单金额总数:
SELECT u.id, u.name, u.city SUM(o.order_num) AS amount
FROM (
user AS u INNER JOIN address AS a
ON u.id = a.id AND a.city = '北京'
)
INNER JOIN orders AS o
ON u.name = o.name
GROUP BY u.id;
±—±---------±-------+
| id | name | amount |
±—±---------±-------+
| 1 | xiaoming | 70 |
| 3 | xiaogang | 90 |
±—±---------±-------+
17 组合查询
MySQL也允许使用多个查询(SELECT语句),并将结果作为单个查询结果集返回。
这些组合查询通常称之为并(union)或复合查询
使用组合查询的两种基本情况:
- 在单个查询中,从不同的表返回类似结构的数据;
- 对单个表执行多个查询,按单个查询返回数据。
使用UNION
使用UNION操作符来组合数条SQL查询
例:找到gender为男的用户,找到id为4、5的用户
使用单条SELECT:
SELECT id,name,gender
FROM user
WHERE gender = '男'
OR id in (4,5);
±—±------------±-------+
| id | name | gender |
±—±------------±-------+
| 1 | xiaoming | 男 |
| 3 | xiaogang | 男 |
| 4 | xiaochongzi | 男 |
| 5 | xiaohua | 女 |
±—±------------±-------+
使用UNION:
SELECT id,name,gender
FROM user
WHERE gender = '男'
UNION
SELECT id,name,gender
FROM user
WHERE id in (4,5);
在这个简单例子里,使用UNION可能更为复杂,但对于更复杂的过滤条件,或者从多个表中检索数据的情形,UNION可能更简单
UNION 会自动去除重复行
UNION ALL 会保留重复行
18 全文本搜索
并非所有引擎都支持全文本搜索
两个最常使用的引擎:MyISAM 和 InnoDB ,前者支持,后者不支持
Q:LIKE 通配操作符匹配和 REGEXP 正则表达式匹配的限制
A:
- 性能——会尝试匹配所有行,非常耗时
- 明确控制——这两种方式很难明确控制匹配什么不匹配什么
- 智能化的结果——不能提供智能化的选择结果的方法,例如,一个特殊词的搜索会返回包含该词的所有行,而不区分包含单个匹配的行和包含多个匹配的行
全文本索引: 不需要分别查看每个行,不需要分别分析和处理每个词;创建指定列中各词的一个索引,搜索可以针对这些词进行
使用全文本搜索
首先必须索引被搜索的列,而且要随着数据的改变不断重新索引。
在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引。
19 插入
插入完整行
INSERT INTO user
VALUES(
'0001',
'xiaoming',
'男');
虽然这种语法很简单,但并不安全,应该尽量避免使用。理由:高度依赖表中列的定义次序。
安全方法:
INSERT INTO user(
id,
name,
gender)
VALUES(
'0002',
'xiaohong',
'女');
不需要按照列的次序,即使表结构变化也能正常工作
提高整体性能
数据库经常被多个客户访问,INSERT操作可能很耗时,并且它可能降低等待处理的SELECT语句的性能。
如果数据检索是最重要的,可以在INSERT 和 INTO 之间添加关键词LOW_PRIORITY 来降低INSERT的优先级。
INSERT LOW_PRIORITY INTO 表名
这个方法也适用于 UPDATE 和 DELETE 语句
插入多个行
INSERT INTO user(
id,
name,
gender)
VALUES(
'0002',
'xiaohong',
'女'),
(
'0003',
'xiaogang',
'男'
);
单条INSERT插入多个 比 多条INSERT插入多个 要快。
插入检索出的数据
INSERT INTO user(
name,
gender)
SELECT writer_name,
writer_gender
FROM weibo;
可以从另一个表里检索出数据进行插入
20 更新和删除数据
更新
注意不要省略 WHERE 语句,稍有不注意,就会更新表中的所有行。
更新id = ‘5’ 的人的性别:
UPDATE user
SET gender = '男'
WHERE id = '5';
IGNORE 关键字:如果UPDATE语句更新多行,其中一行出现错误,则整个UPDATE工作被取消,之前更新的行也会被恢复。即使发生错误,也继续更新的话可使用IGNORE关键字
UPDATE IGNORE user
SET gender = '男'
WHERE id = '5';
删除
删除某个列的值,可设置它为NULL
UPDATE IGNORE user
SET gender = NULL
WHERE id = '5';
删除某一行:
DELETE FROM user
WHERE id = '5';
删除所有行,不要使用DELETE,用TRUNCATE TABLE语句,速度更快。它实际是删除原来的表并创建一个新表。
更新和删除的指导原则
- 除非准备删除、更新每一行,否则一定要带上WHERE
- 保证每个表都有主键
- 对数据进行UPDATE 和 DELETE 时,要先用SELECT 进行测试,保证过滤正确
21 创建和操纵表
创建
CREATE TABLE customers
(
cust_num int NOT NULL,
cust_id int NOT NULL,
cust_name char(50) NOT NULL,
cust_city char(30) NOT NULL,
cust_email char(255) NULL,
cust_account DECIMAL(10,2) NOT NULL, # 精确到两个小数的10位数
PRIMARY KEY(cust_num,cust_id)
)ENGINE = InnoDB;
处理现有的表:在创建新表时,指定的表名必须不存在,否则将出错,需要先手动删除该表。
主键可以是多个列的组合
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT, # 每当增加一行时自动增量
cust_name char(50) NOT NULL,
cust_city char(30) NOT NULL DEFAULT ‘北京’, # 默认值
cust_email char(255) NULL,
cust_account DECIMAL(10,2) NOT NULL, # 精确到两个小数的10位数
PRIMARY KEY(cust_num,cust_id)
)ENGINE = InnoDB;
InnoDB引擎:可靠的事务处理引擎,不支持全文本搜索
MEMORY引擎:功能等同于MyISAM,但由于数据存储在内存中,速度很快
MyISAM引擎:性能极高,支持全文本搜索,但不支持事务
外键不能跨引擎
更新
ALTER TABLE customers
ADD cust_address char(255) NULL;
需要小心使用ALTER TABLE,应在改动前进行完整备份(模式和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除他们。
删除
DROP TABLE customers;
重命名
RENAME TABLE customers TO customer;
22 视图
需要MySQL5以后的版本
视图:虚拟的表,与包含数据的表不同,视图只包含使用时动态检索数据的查询。视图本身不包含数据
好处:
- 可以重用SQL语句
- 简化SQL操作
- 使用表的组成部分而不是整个表
- 保护数据
创建视图
create view productcustomers as
select cust_name,cust_contact,prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;