-- 注释//自增插入数据，必须一一对应表的每一个数据列，不可空出来。
CREATE TABLE class(id int PRIMARY KEY auto_increment,
sname VARCHAR(50) not null DEFAULT '');
# 插入改INSERT INTO class(sname) VALUES('豆豆');
INSERT INTO class VALUES(null,'123d ');
INSERT INTO class(name,sex) VALUES('123d ','nan'); //id自增这里可以省略id项
//提高整体性能 LOW_PRIORITY 插入耗时影响select性能的时候使用
//INSERT LOW_PRIORITY INTO
//单条 INSERT 语句处理多个插入比使用多条 INSERT语句快。
#一次性insert 多行 逗号隔开即可
INSERT INTO class VALUES(null,'我'),(null,'是');
//update UPDATE class SET id=8,sname ='是我' WHERE id = 7; where 表达式； 表达式为真就修改
//delete delete from class where id = 7;
//# truncate 表名; delete 区别 前者复制表的结构，删除旧表 后者删行记录
// SELECT sname,id from class where id = 6;
建表 即声明字段
//字段类型
数值型
整型
Tinyint//为 1 字节 -128 127
smallint//2 个字节
mediumint//一个中等大小整数，有符号的范围是-8388608到8388607，无符号的范围是0到16777215。 一位大小为3个字节。
int//一个正常大小整数。有符号的范围是-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字），无符号的范围是0到4294967295。一位大小为 4 个字节。
int 的 SQL-92 同义词为 integer。
bigint//从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字），无符号的范围是0到18446744073709551615。一位为 8 个字节
//zerofill 必须是unsigned类型 /数据类型（M）zerofill/ M 的解析 补0宽度
小数（浮点/定点型）
Float（M,D） decimal（M,D） // M为标度 代表小数总位数 D代表精度 代表小数点右边的位数
//Float(6,2) -9999.99 9999.99 无符号的话 0 9999.99
//decimal(M,D) 为定点型 整数小数 分开存储比float精确 数据过长float会损失精度
字符串
//char（M） varchar(M) text文本类型 blob存图像音频等二进制信息防止因为字符集的问题导致信息丢失
//定长 查找快速 浪费空间 不够N补空格到N，取出时删除右侧所有空格 利用率<=100% M 0 255 限制的是字符而不是字节
//变长 相对慢 节约空间 增删效率低 数据前会有一个标识位1-2byte 标记往后读几个字符 利用率<100% M 0 65535
日期类型 date 能存储1000-01-01 到 9999-12-31 没有数据可以修为特殊值 0000-00-00 3byte
时间类型time 20：20：20 3byte // year 1byte 1901-2155 未填为0000
日期时间类型datetime YYYY-mm-dd HH:ii:ss 8byte
timestamp 和时间类型相同 不赋值的或可以 ts timestamp default CURRENT_TIMESTAMP 4byte
增加列
alter table 表名 add 列名 列类型 列参数; --加的列在表的最后
alter table 表名 add 列名 列类型 列参数 after 某列;
alter table 表名 add 列名 列类型 列参数 first;
删除列
alter table 表名 drop 列名
修改列的类型参数
alter table 表名 modify 列名 列类型 列参数;
修改列的名字类型番薯
alter table 表名 change 旧列名 新列名 新列类型 新列参数
重命名表名
ALTER TABLE user10 RENAME TO user11;
RENAME TABLE user11 TO user10;a desc,b asc 先a降序，在b升序排序
select 5种子句 desc asc升（默认） 2，3 跳过两条，取接下来的两条
where 条件，group by 分组，having 筛选，order by排序，limit限制结果条数
order by a,b;
重要的是理解在按多个列排序时，排序的顺序完全按规定进行。换句话说，对于上述例子中的输出，仅在多个行具有相同的prod_price值时才
对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;
等价于 prod_price,prod_name;
为什么要使用IN操作符？其优点为：
在有很多合法选项时，IN操作符的语法更清楚，更直观。
在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。
IN操作符一般比一组OR操作符执行得更快（在上面这个合法选项很少的例子中，你看不出性能差异）。
IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。第 11 课会对此进行详细介绍。
子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误
where 列不变 计算分组别名，列可能改变 having order排序limit
表----->满足条件的行---------------------------->结果集-------->最终结果集-------------->
and || 优先级比 or &&高 in not ！ between...and包括边界值 列为变量
!= <> 字段 as 别名
like xx% _匹配一个 %多个 '[JM]%' J或M开头 用 ^否定 '[^JM]%'
仅sqlserver支持 where只对表中的数据起作用，为表达式，不能对查询结果起作用必须用having
例如 select user as name from test where name = ‘xxx’； 错 误
使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
文本数据时，MIN()返回该列排序后最前面的行MAX同
DISTINCT ALL
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
DISTINCT 不能用于 COUNT(*)
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
TOP和TOP PERCENT。GROUP BY与统计函数
max（） 文本数据时返回最后一行 函数忽略列值为 NULL 的行
min（） 文本数据时返回第一行 函数忽略列值为 NULL 的行
sum（） 函数忽略列值为 NULL 的行
avg（） 函数忽略列值为 NULL 的行
count（*） 绝对行数 ，（列名）字段不统计null
只包含不同的值，指定 DISTINCT 参数 不指定 DISTINCT ，则假定为 ALL
select AVG(DISTINCT prices) from goods where id = 100;
DISTINCT 只能用于 COUNT() 不能用于 COUNT(*) 类似地， DISTINCT 必须使用列名select id，sum（prices ）from goods group by id ； id有多少组
GROUP BY a,b,c; select中查询的字段一般而言只能从abc中选择
1.求平均分，并且挂科两门以上的学生 表名result name，score，course
select name,avg(score) from result group by name;
select name,course,score,score<60 as guake from result;
select name,avg(score),sum(score<60) as g from result group by name having g>=2
字符串拼接 concat(str1,str2,...) 若有一个为空,则查询为空
concat_ws(separator,str1,str2,...) 用分隔符分开 分隔符为NULL，则返回结果为
NULL ，参数中存在NULL，则会被忽略
ifnull(str,设定) str为空则用设定的值代替
rtrim(),ltrim() 去左右空格
SUBSTRING()
CONVERT()数据类型转换
CURDATE()
UPPER()
LOWER()
LENGTH()
Locate() 找出串的子串
LEFT(str,n) 左边数n个字符 RIGTH(str,n) SELECT sname FROM student where SOUNDEX(sname) = SOUNDEX('小')
Soundex() 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法
sname 中发音类似 小 的
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012; year()取年
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分等值联结（ equijoin ）也称为内联结（ inner join ） 联结的表越多，性能下降越厉害
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;自联结（ self-join ） 用自联结而不用子查询
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones'); 找到jim工作公司，找到公司的所有人
自然连接 自然联结排除多次出现，使每个列只返回一次
通过MySql自己的判断完成连接过程，不需要指定连接条件。MySql会使用表内的，相同的字段，作为连接条件
select * from student natural join paper;
select * from student natural left join paper;
SELECT c.*,o.id,o.num from cus as c,order as o where c.id = o.id and o.name = 'xx';
外联结（ outer join ）
全连接（全外连接）
MySQL目前不支持此种方式，可以用其他方式替代解决
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为:
A.aID = B.bID). B表记录不足的地方均为NULL.
右同理
select * from a_table as a left join b_table as b on a.a_id = b.b_id;#并（ union ）或 复合查询（ compound query ）
UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2
1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名
2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，
每条 SELECT 语句中的列的顺序必须相同
如果不想去掉重复的行，可以使用union all。
如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。
(select * from a order by id) union (select * from b order id)复制表
1.复制表结构及数据到新表
create table 新表 select * from 旧表
2.只复制表结构到新表
方法1：(低版本的mysql不支持，mysql4.0.25 不支持，mysql5已经支持了)
create table 新表 like 旧表
方法2：
create table 新表 select * from 旧表 limit 0
方法3：
create table 新表 select * from 旧表 where 不成立条件
3.复制旧表的数据到新表
1、(假设两个表结构一样)
insert into 新表 select * from 旧表
2、(假设两个表结构不一样)
insert into 新表(字段1,字段2,…….) select 字段1,字段2,…… from 旧表更新表
UPDATE Customers //若一行出现错误整个 UPDATE 操作被取消
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
UPDATE IGNORE Customers //若一行出现错误忽略继续修改后面的行
SET cust_contact = 'Sam Roberts',
cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';
删除表
DELETE FROM Customers
WHERE cust_id = '1000000006';
truncate table
表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，而速度更快（因为不记录数据的变动）。 删除原来的表并重新创建一个表 而不是逐行删除表中的数据
CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL DEFAULT ‘’,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc text NULL
); LIKE 匹配整个串而 REGEXP 匹配子串
正则表达式 可以用 | & 连接多个表达式
select * from dept where dname regexp ‘正则表达式’；
select * from dept where dname regexp BINARY ‘正则表达式’； //使用BINARY区分大小写
[1|2|3] Ton = [123] Ton =[1-3] Ton
. 代表任一字符 查找‘ . ’ 需要用转义符号 \\.
[:alnum:] 任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:blank:] 空格和制表（同[\\t]）
[:cntrl:] ASCII控制字符（ASCII 0到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）
select * from dept where dname regexp ‘\\([0-9] sticks?\\)’；
[0-9] 匹配任意数字
? 匹配它前面的任何字符的0次或1次出现 sticks? -> s出现0次或1次
select * from dept where dname regexp ‘[[:digit:]]{4}’；
匹配连在一起的任意4位数字 出现4次 如：1000 = [0-9][0-9][0-9][0-9]
^ 文本的开始 ^[0-9\\.] 搜索以 一个数字 或 . 开头
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
外键（foreign key） 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系.
1.供应商信息不重复，从而不浪费时间和空间；
2.如果供应商信息变动，可以只更新 vendors 表中的单个记录，相
关表中的数据不用改动；
3.由于数据无重复，显然数据是一致的，这使得处理数据更简单。MyISAM 和 InnoDB
前者支持全文本搜索，而后者不支持 搜索不区分大小写 除非使用 BINARY 方式
使用两个函数 Match() 和 Against() 执行全文本搜索
Match(note_text) 指示MySQL针对指定的列进行搜索
Match() 的值必须与FULLTEXT() 定义中的相同指定多个列必须列出而且次序正确
Against('rabbit') 指定词 rabbit 作为搜索文本由于有两行包含词 rabbit ，这两个行被返回
SELECT 可与 Match() 和 Against() 一起使用
启用全文本索引
create table productnotes
{
note_id int not null auto_increament,
note_text text null,
primary key(note_id),
fulltext(note_text) //启用 全文本搜索
}engine=MyISAM;
select note_text,match(note_text) against('rabbit') as rank from productnotes;
两个行都包含词 rabbit ，但包含词 rabbit 作为第3个词的行的等级比作为第20个词的行高
等级的行先返回
指定多个搜索项，则包含多数匹配词的那些行先返回
查询扩展，能找出可能相关的结果 表中的行越多查询扩展返回的结果越好
select note_text from productnotes
where match(note_text) against('rabbit' with query expansion) ;
布尔文本搜索 没有 FULLTEXT 索引也可以使用 非常缓慢
select note_text from productnotes
where match(note_text) against('rabbit -rope*' in boolean mode) ;
搜索包含rabbit 不包含 rope- 开头的行
+ 包含，词必须存在
- 排除，词必须不出现
> 包含，而且增加等级值
< 包含，且减少等级值
() 把词组成子表达式（允许这些子表达式作为一个组被包含、
排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
"" 定义一个短语（与单个词的列表不一样，它匹配整个短语以
便包含或排除这个短语）
select note_text from productnotes
where match(note_text) against('+rabbit +rope' in boolean mode) ;
包含 rabbit和rope
select note_text from productnotes
where match(note_text) against('rabbit rope' in boolean mode) ;
包含 rabbit或rope
select note_text from productnotes
where match(note_text) against('"rabbit rope"' in boolean mode) ;
包含rabbit rope
以下是几个需要知道的引擎： // 外键不能跨引擎
InnoDB 是一个可靠的事务处理引擎（参见第26章），它不支持全文
本搜索；
MEMORY 在功能等同于 MyISAM ，但由于数据存储在内存（不是磁盘）
中，速度很快（特别适合于临时表）；
MyISAM 是一个性能极高的引擎，它支持全文本搜索（参见第18章），
但不支持事务处理
视图
重用SQL语句。
1.简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必
知道它的基本查询细节。
2.使用表的组成部分而不是整个表。
3.保护数据。可以给用户授予表的特定部分的访问权限而不是整个
表的访问权限。
4.更改数据格式和表示。视图可返回与底层表的表示和格式不同的
数据。
1.与表一样，视图必须唯一命名（不能给视图取与别的视图或表相
同的名字）。
2.对于可以创建的视图数目没有限制。
3. 为了创建视图，必须具有足够的访问权限。这些限制通常由数据
库管理人员授予。
4. 视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造
一个视图。
5. ORDER BY 可以用在视图中，但如果从该视图检索数据 SELECT 中也
含有 ORDER BY ，那么该视图中的 ORDER BY 将被覆盖。
6.视图不能索引，也不能有关联的触发器或默认值。
7. 视图可以和表一起使用。例如，编写一条联结表和视图的 SELECT
语句。
CREATE VIEW 创建
SHOW CREATE VIEW viewname 查看创建视图的语句
DROP VIEW viewname
CREATE OR REPLACE VIEW 更新视图
create view as select name,id,sex from class where class.id=dept.id;
视图定义中有以下操作，则不能进行视图的更新
分组（使用 GROUP BY 和 HAVING ）；
联结；
子查询；
并；
聚集函数（ Min() 、 Count() 、 Sum() 等）；
DISTINCT；
导出（计算）列。存储过程
1.简化复杂的操作
2.不要求反复建立一系列处理步骤，这保证了数据的完整性 防止错误保证了数据的一致性
3.简化对变动的管理 如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码
安全性 限制对基础数据的访问减少了数据讹误
4.提高性能。因为使用存储过程比使用单独的SQL语句要快
5.存在一些只能用在单个请求中的MySQL元素和特性存储过程可以使用它们来编写功能更强更灵活的代码
create procedure productpricing() //存储过程名productpricing
begin
select avg(prod_price) as priceaverage
from product;
end;
调用
call productpricing();
删除
drop procedure productpricing;
create procedure productpricing(out p1 deciaml(8,2),out p1 deciaml(8,2))
begin
select min(prod_price) into p1
from product;
select max(prod_price) into p2
from product;
end;
call productpricing(@p1,@p2);
select @p1,@p2;
//pl 存储产品min价格 p2 存储产品max价格
//关键字 OUT 指出相应的参数用来从存储过程传出一个值（返回给调用者）
// IN （传递给存储过程） OUT （从存储过程传出，如这里所用） INOUT （对存储过程传入和传出）
// COMMENT 注释 SHOW PROCEDURE STATUS （like ）'存储过程名' 的结果中显示
//IF THEN ELSEIF 和 ELSE 触发器
只有表才支持触发器，视图、临时表 不支持 每个表最多支持6个触发器
触发器是MySQL响应以下任意语句 自动执行的一条MySQL语句
DELETE ；
INSERT ；
UPDATE
唯一的触发器名；
触发器关联的表；
触发器应该响应的活动（ DELETE 、 INSERT 或 UPDATE ）；
触发器何时执行（处理之前或之后）。
create trigger newproduct after insert on products
for each row select 'Product added';
// 创建名为 newproduct 的新触发器 after insert 在 INSERT 语句成功执行后执行
// for each row 对每个插入行执行
// select 'Product added' 显示 Product added 消息
如果 BEFORE 触发器失败,不执行请求的操作 BEFORE 用于数据验证和净化
BEFORE 触发器或语句本身失败,不执行 AFTER 触发器
//使用虚拟表
create trigger newproduct after insert on products
for each row select NEW.pname;
create trigger updatevendor before update on vendors
for each row set NEW.vend_state = Upper(NEW.vend_state)
//每次更新一个行时 NEW.vend_state 中的值（将用来更新表行的值）都用 Upper(NEW.vend_state) 替换
删除
drop trigger newproduct;
事务管理
来维护数据库的完整性 不能回退 CREATE 或 DROP 操作
事务（ transaction ）指一组SQL语句；
回退（ rollback ）指撤销指定SQL语句的过程；
提交（ commit ）指将未存储的SQL语句结果写入数据库表；
保留点（ savepoint ）指事务处理中设置的临时占位符（place-
holder），你可以对它发布回退（与回退整个事务处理不同）
start transaction;
rollback; //只能在一个事务处理内使用
commit; //仅在不出错时写出更改 第一条 DELETE 起作用，但第二条失败，DELETE 不会提交
//当 COMMIT 或 ROLLBACK 语句执行后动关闭（将来的更改会隐含提交）。
savepoint name;
rollback to name;
//保留点在事务处理完成（执行一条 ROLLBACK 或COMMIT ）后自动释放可以用 RELEASE SAVEPOINT
//更改默认的提交行为
//autocommit标志决定是否自动提交更改 0 （假）不自动提交更改直到 autocommit 被设置为真为止
//autocommit针对每个连接 而不是服务器
set autocommit = 0;
1353
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
