mysq必知必会(一）
1：主键
1.1使用一列作为主键，值不唯一，
1.2使用多列作为主键，列值的组合必须唯一
注意、：不更新主键的值，不重用主键的值。
例如：当使用名字作为主键时，客户名字多个或者修改时，主键的值也会随着修改。（主键的值默认不修改，主键为的是关联的时候检索表的其他字段信息）
2：sql（结构化查询语言），专门为了和数据通信的语言。
数据的所有存储、检索、管理和处理实际上是由数据库软件——DBMS（数据库管理系统）完成的。MySQL是一种DBMS，即它是一种数据库软件。
3DESCRIBE语句
MySQL支持用DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式。换句话说，DESCRIBE customers;是SHOW COLUMNS FROM customers;的一种快捷方式。
3：检索 使用SELECT检索表数据，必须至少给出两条信息——想选择什么，以及从什么地方选择。
sql不区分大小写，其中（sql语句）所有的空格也会被忽略。
4：使用通配符（*）
一般，除非你确实需要表中的每个列，否则最好别使用*通配符。虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。
5：distinct(去重) 检索不同的行。
SELECT distinct cno from sc 告诉mysql只返回不同（唯一）的cno行
注意：
![]()
不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT distinct cno，sno，除非指定的两个列都不同，否则所有行都将被检索出来。
6：limit SELECT语句返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句.
LIMIT x, y指示MySQL返回从行x开始的y行。第一个数为开始位置，第二个数为要检索的行数.下标从0开始。
注意：
6.1：在行数不够时 LIMIT中指定要检索的行数为检索的最大行数。如果没有足够的行（例如，给出LIMIT 10, 5，但只有13行），MySQL将只返回它能返回的那么多行。
6.2：MySQL 5支持LIMIT的另一种替代语法：4 OFFSET 3意为从行3开始取4行
select * from student limit 3 OFFSET 0 等同于 select * from student limit 0,3
7：排序（order by）检索数据，
如果不指定检索数据的顺序，默认是以添加顺序排序。但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响
7.1：通过非选择列进行排序
通常，ORDER BY子句中使用的列将是为显示所选择的列。但是，实际上并不一定要这样，用非检索的列排序数据是完全合法的。
例如：select sname from student ORDER BY sno
7.2：按多个列排序
select sname from student ORDER BY sno，sname
仅仅对多个行具有相同的sno列时，才会对sname排序，假如sno是唯一的，就不会对sname进行排序。
7.3：指定排序方向（默认是asc,逆序desc）
7.3.1：当对对列排序时（select sname from student ORDER BY sno，sname），在sno不唯一的时候，DESC关键字只应用到直接位于其前面的列名。
sno列指定desc(降序),sname不指定desc（默认升序）
7.3.2：在多个列上降序排序 如果想在多个列上进行降序排序，必须:对每个列指定DESC关键字（默认升序，多列不用指定asc)。
select sname from student ORDER BY sno desc,sname desc
8：ORDER BY子句的位置 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。
例如：select * from student ORDER BY sno desc limit 1
9：过滤数据（where）
select * from student where sname='张三' #判断是否有张三，没有就返回一个空的表，（不仅仅为相等测试）
9.1 :范围值检查(between...and...)
select * from sc where score between 80 and 90
在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。
9.2:空值检查,is null,is not null
例如：select * from student where sname is not null #检索sname不为空的所有行。
9.3：组合WHERE子句，以AND子句的方式或OR子句的方式使用
select * from course where cno between 'c001' and 'c004'
select * from course where cno>='c001' and cno <='c004' （两句使用结果一样)
and逻辑操作符，判断cno的范围，和between..and...使用效果类似。where 之间可以嵌套多个and或者or。
9.3.1：在and和or操作符中，and的优先级超过or，所以在使用中尽量将or的字句用（）括起来。
例如：select cno,score from sc where (cno='c001' or cno='c002') and score>80 # 检索cno为c001和c002的，并且score大于80的。
9.4：IN操作符。 IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。
例如：select * from sc where sno in ('s001','s005') #检索sno在数s001到s005之间，与or的功能一样。
in操作符一般比or执行速度快
9.5：not操作符.WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。
10:用通配符进行过滤。（一般和like操作符搭配使用）
通配符（wildcard） 用来匹配值的一部分的特殊字符。
10.1：百分号（%）通配符，在搜索串中，%表示任何字符出现任意次数。
例如：select * from student where sname like '陈%' # 检索姓陈的所有学生。（通配符可以在中间，开头或者结尾）
%代表搜索模式中给定位置的0个、1个或多个字符
注意NULL 虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。
10.2：下划线（_）通配符。
下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。
例如：select * from student where sname like '_三' #只能检索 第二个字符是三的学生，比如张三。
注意：
正如所见，MySQL的通配符很有用。但这种功能是有代价的：通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。
10.2.1不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
10.2.2在确实需要使用通配符时，除非绝对有必要，否则不要把它们用
10.2.3 在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
10.2.4仔细注意通配符的位置。如果放错地方，可能不会返回想要的数
11：mysql算数操作符
+加 -减 *乘 /除
例如：select danjia,num,danjai+num from price #检索单价乘以数量后的总价
11.1：测试运算，虽然SELECT通常用来从表中检索数据，但可以省略FROM子句以便简单地访问和处理表达式。（体现sql作为检索数据语言的特性）
select trim('q') #输出q select 4*5 输出20
11.2：而SELECTNow()利用Now()函数返回当前日期和时间
12：汇总数据
AVG()返回某列的平均值，COUNT()返回某列的行数，MAX()返回某列的最大值，MIN()返回某列的最小值，SUM()返回某列值之和
12.1：AVG()函数忽略列值为NULL的行。
例如：select avg(danjia) from price #值统计单价不为空的数据的平均值（null的行值也不计）
12.2：COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
例如：select count(id) from price #统计价格表的商品数量。
注意：
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
12.3：虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。（MAX()函数忽略列值为NULL的行。）
例如：select max(num) from price #找出价格表中数量最大的
12.4：MIN()函数和max函数一样的性质，不同的是取最小的值。
例如：select min(num) from price #找出价格表中数量最小的
12.5：SUM()用来返回指定列值的和（总计）（SUM()函数忽略列值为NULL的行。）
例如：select sum(num) from price #找出商品的数量之和
12.6：聚集不同值distinct（去重）
ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。
例如：select sum(DISTINCT danjia) from price #检索单价去重后的总和
注意：
如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。
12.6：组合聚集函数
例如：select count(danjia),max(danjia),min(danjia),avg(danjia) from price #检索价格表中的单价数量，单价最大值，单价最小值，单价的平均值。
注意：
聚集函数用来汇总数据。MySQL支持一系列聚集函数，可以用多种方法使用它们以返回所需的结果。这些函数是高效设计的，它们返回结果一般比你在自己的客户机应用程序中计算要快得多。
13：数据分组（group by)分组是在SELECT语句的GROUP BY子句中建立的。
返回学生重名的数据？返回每个供应商提供的产品数目怎么办？或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商怎么办？
例如：select sname,count(sname) as s from student GROUP BY sname #对学生表的sname字段进行分组，得到每个名字和数量。
13.1：使用rollup,使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值
13.2:过滤分组。（having字句）
HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。
HAVING支持所有WHERE操作符
例如：SELECT sname,count(sname) from studentGROUP BY sname HAVING count(sname)>1 #通过学生的名字分组，过滤出名字大于一的组（帅选出重名的学生）
13.2：HAVING和WHERE的差别
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组
13.3：分组和排序
13.3.1：order by和group by 的区别
ORDER BY GROUP BY
- 排序产生的输出 分组行。但输出可能不是分组的顺序
- 任意列都可以使用（甚至 只可能使用选择列或表达式列，而且必须使用每个选择列表达式
- 非选择的列也可以使用）
- 不一定需要 如果与聚集函数一起使用列（或表达式），则必须使
注意：
group by 默认是分组的顺序，最后搭配order by 使用可以得到我们实际想要的顺序输出。
14：子查询（嵌套在其他查询中的查询）
例如：SELECT sno,score from sc where sno in (select sno from student where sno in ('s001','s002')) # 查询学号为s001和s002的学生的学号，成绩（在学生表和成绩表里查询）
相当于利用子查询进行过滤，
14.1：列必须匹配
在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。（例如上个语句中sno匹配）
15：联结（join）表
主键：作为表信息的唯一标识（具有唯一性）
外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系
15.1：创建联结 （要理解联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。）
例如：SELECT student.sno,student.sname,sc.score from student,sc where student.sno=sc.sno #查询所有学生的成绩（学生表和成绩表联结）
注意：在使用联结时必须限定列名（如上sql句标黄部分）
15.2：笛卡儿积：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
所以要保证所有的联结都有where字句，这样的结果才是正确数据
笛卡尔积又称叉联结的笛卡尔积的联结类型
15.3：内部联结（inner join），又称等值联结。
例如：SELECT student.sno,student.sname,sc.score from student INNER JOIN sc on student.sno=sc.sno #等同上面sql语句（查询所有学生的成绩）
15.4：使用别名（列名，表名，计算字段）
例如：SELECT a.sno,b.score from sc as a,sc as b where a.sno=b.sno and a.cno='c001' and b.cno='c002'
#查询学过课程c001和c002的学生的学号和成绩（一张表两次使用）
16：不同类型的联结
16.1：自联结
例如：SELECT a.sno,b.score from sc as a,sc as b where a.sno=b.sno and a.cno='c001' and b.cno='c002'
很多时候处理联结远比处理子查询快得多
16.2：内部联结（inner join）：
例如：SELECT st.sno,st.sname,s.score from student as st INNER JOIN sc as s on st.sno=s.sno #查询所有学生的成绩（两个表为主）
16.3：外部联结（ outer join）
16.3.1：左外部联结
例如：SELECT st.sno,st.sname,s.score from student as st LEFT OUTER JOIN sc as s on st.sno=s.sno（左表表为主）
#查询所有学生（包括没有成绩的学生）的成绩，没有成绩的学生的成绩为null
16.3.2：右外部联结
例如：SELECT st.sno,st.sname,s.score from student as st RIGHT OUTER JOIN sc as s on st.sno=s.sno（右边表为主）
#查询所有学生（只包括有成绩的学生）的成绩。这里等同于内部联结
17：组合查询（执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回）
17.1：创建组合查询（可用UNION操作符来组合数条SQL查询）
例如：
SELECT st.sno,st.sname,s.score from student as st
INNER JOIN sc as s
on st.sno=s.sno
WHERE s.score>60
UNION
SELECT st.sno,st.sname,s.score from student as st
INNER JOIN sc as s
on st.sno=s.sno
WHERE s.cno='c001'
等同于
SELECT st.sno,st.sname,s.score from student as st
INNER JOIN sc as s
on st.sno=s.sno
WHERE s.score>60 OR
s.cno='c001'
注意：
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）
（使用UNION ALL，MySQL不取消重复的行）
SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。
18：插入数据（insert into ）
插入一条
INSERT INTO price VALUES(
4,26,7
)
插入多条
INSERT INTO price VALUES(
5,32,11
),
(6,36,14
)
多条插入数据的时候可以一条一条的插入，也可以直接全部插入（推荐使用全部一次插入）
MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。
18.1:INSERT SELECT将查询的结果插入到表中
例如：INSERT INTO price(id,danjia,num) SELECT id,danjia,num from price
19：更新表（update set）搭配where使用
19.1：更新表中特定的行
例如：UPDATE price set danjia=35 where id=6 #将ID为6的行的danjia字段修改为35
19.1.1：更新表中特定行的几个字段
例如：UPDATE price set danjia=35，num=4 where id=6 #将ID为6的行的danjia和num字段修改为，35,7.
19.2：更新表中全部的行
例如：UPDATE price set danjia=35 #将所有的行的danjia都修改为35
亦可以在where后加多行，同时修改多行，但是只要有一行更新信息错误就会全部不更新（可使用IGNORE关键字，即使发生错误，也会继续更新正确的信息）
update设置null去除列中的值（为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值））。
20：删除数据（delete）DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。
20.1：删除表中特定的行，
例如：delete from price where id=6 #删除ID为6的行
20.2：删除表中全部的行
例如：delete from price #删除price表所有的行
等同于
truncate price
TRUNCATE TABLE（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。
21：创建视图
CREATE VIEW dhsj
AS
SELECT * from student
#将学生表作为一个视图dhsj，可以直接在视图dhsj中检索学生表的信息.
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
22:delete，truncate，drop的区别
- 在速度上，一般来说，drop> truncate > delete。
- 在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
- 如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；
- 如果想保留表而将所有数据删除，如果和事务无关(不能回滚)，用truncate即可；
- 如果和事务有关，或者想触发trigger，还是用delete；
- 如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。
浙公网安备 33010602011771号