小结：MySQL
[TOC]
安装与登陆
Linux系统：可以从官网下载deb安装包安装，或者使用apt-get命令安装
-
Win7系统：从官网下载MySQL Server包，解压到安装目录。找到ini配置文件做相应修改。添加环境变量。做初始化：
mysqld --initialize-insecure mysqld --install net start mysql mysql -u root -p 安装注意：Ubuntu系统中中文的输入可能会遇到问题，和默认编码有关。Win7中的主要问题是mysql服务能否正常开启。
-
关于用户与登陆
设置密码：登陆后使用
set password for 'root'@'localhost'=password('pw')设置密码：在命令行中使用
mysqladmin -u root -p password pw, 不推荐这种方式，密码可能会被泄露。登陆：
mysql -u username [-h hostname] -p, 注意，本地登陆中括号的部分可以省略，远程登陆需要对mysql做一些设置。新建用户：
create user 'username'@'localhost' identified by 'pw'-
新建用户：本质上是在mysql.user中新增一条记录，删除用户即删除这条记录。
insert into mysql.user(host,user,password) values("localhost","username",password("pw")); flush privileges; #刷新 授权：
grant all privileges on dbname.* to uname@hname identified by 'pw'或者grant all on \*.* to uname @hname
- 问题：删除某个用户后，该用户的数据库会自动删除吗？（不会，root用户对所有用户创建的数据库都拥有权限）
- 删除用户：可以直接操作mysql.user，也可以使用drop语句
数据库和表常用命令
- 创建：
create database dbname,create table tbname(varname char(20),...) - 删除：
drop database dbname,drop table tbname - 查看：
show databases,show tables,describe tbname,select * from tbname - 新增：
insert into tbname value() - 使用：
use dbname,select database()
数据导入导出
- 从命令行导入：
mysql -u uname -p dbname < file.sql - 登陆后导入：
source file.sql，注意路径中的斜杠 - 从命令行导出数据库：
mysqldump --no-defaults -u root -p dbname > name.sql - 导出xls格式文件：
select * from tbname into outfile 'name.xls'
secure_file_priv=F:\data\ #在my.ini中的设置
select * from tbname into outfile 'F:/data/name.xls' #这里用'\'出错
检索与排序
- 排序：
select * from tbname order by colname [desc] - 多列：
select * from tbname order by name1, name2 desc, name3 - 部分结果：
select * from tbname order by clname limit 5前五条结果。注意，不同的数据库具体实现不同。 - 检索优化：尽量不要使用通配符‘*’
操作符
一般的检索语句如下：
select 计算字段
as name #别名语句
from tbname
过滤语句
order by colname1,colname2 desc #排序语句
limit 5 [limit 3,4] #限制语句
-
计算字段：
连接字符：
select concat(colname1, '**',colname2)，注意不同数据库的实现不同，有使用‘||’或者‘+’作为连接符的。-
数学计算：可以直接使用运算符号做基本运算
select 1+1 as name -
函数：sql提供了一些基本函数，比如处理字符串的函数，提取时间的函数，数值计算的函数，基本的统计函数。但是不同数据库的实现可能不同，有可能影响可移植性。
聚集函数：
avg() count(*),count(colname)#全计数，忽略NULL的计数 max(),min() sum()
-
过滤语句：
where 子句 #子句类型： where colname >1 #数学运算符：>,<,=,!=,<>,<=,>= where colname between 1 and 3 where colname is null where colname in ( 1, 2, 3) #子句类型-通配符：注意'%'不匹配NULL；注意不要过度使用通配符，会影响效率 where colname like '%fish%' #通配符：% , _ ,[abc] #子句类型-子查询：select语句可以嵌套，即在查询结果中查询 #注意子查询时的缩进，提高代码可读性。 select ... from ... where ... in (select ... from ... where ... in (select ... from ... where ...)) #注意：子查询不仅可以在子句中使用，也可以在计算字段中使用。 #注意：子查询可以操作不同的表，因此最好使用完全的列名，防止歧义。 #子句组合：子句可以使用逻辑操作符not,and,or组合起来,注意优先级。适当使用括号。 where colname >2 and colname <6 where not colname in(1,2,3) where colname not in(1,2,3)
高级功能
-
分组
select ... from ... [where语句] group by ... having ... #类似where 用来过滤组 order by ... -
组合查询
select ... union [all] select ... -
插入
#插入时尽量指明列名 insert into tbname(var1,var2,var3) values(v1,v2,v3) #可以将查询结果插入 insert into tbname(var1,var2,var3) select var1,var2,var3 from ... where ... #复制到新表 create tbnew as select 语句 -
表的操作
#修改和删除行 update tbname set colname=... where ...#不加过滤语句会更新全表 delete from tbname where ... truncate table tbname #清空全表 #表的创建 create table tbname ( varname char(10) [not null] [default 'abc'] [unique], varname integer not null primary key [check varname > 0], varname char(10) not null references tbname(primkey) #外键的值需要在另一表的主键中 ) #表的修改(尽量在设计之初完成，不在包含数据时操作) alter table tbname #+语句： add varname ... #增加列 drop column varname #删除列 add constraint primary key (varname) #主键 add constraint foreign key (varname) references tbname (prim_key_name) #外键 add constraint check (varname like '[abc]') #检查约束 -
更多高级功能
- 索引：优化检索速度 index
- 触发器：维持数据一致性 trigger
- 权限管理：grant， revoke
联结表
sql中数据以表的形式储存，每条数据是表中的一行。为了管理某个项目的全部数据一般需要多个表，不同的表之间会有关联。因此需要在多个表之间做对比查询。
#内联结
select var1,var2,var3
from tb1 inner join tb2
on tb1.somevar = tb2.somevar #内连接
select ...
from tb1,tb2,tb3
where tb1.somevar = tb2.somevar
and tb3.somevar = tb1.somevar
#注意联结的表可能会非常耗费资源，尤其是大表之间的联结和多表之间的联结。
#注意联结表时（from后有多于一个的表），要给出联结条件，否则会做逐条的配对：一个表的一条记录和其他表的每条记录配对。
#自联结 利用表的别名，表可以自己和自己联结
select ...
from tbname as name1, tbname as name2
where name1.somevar = name2.somewar
and ...
#外联结 会给出部分没有匹配的数据
select var1,var2,var3
from tb1 left inner join tb2 # 左联结，会包括tb1中没有匹配的行
on tb1.somevar = tb2.somevar
函数
数据库语言自带了一些函数，比如文本处理的函数（rtrim去空格，concat拼接），基本的数学函数，以及五个聚集函数。Mysql中可以自定义函数，同时Mysql中也可以创建存储过程。这两个概念类似于Fortran中的函数和过程。除了函数和过程，sql还提供了视图作为封装检索语句的方法。视图是虚拟的表，使用视图时会自动执行相应的检索语句。
-
视图
create view name as select 语句 #功能上等价于： create table name as select 语句 #但是view并没有真的创建表，而是使用name的时候会自动调用相应的select语句 #视图可以嵌套；需要留意视图的性能；创建视图时最好不包含特定的数据，方便重用。 -
自定义函数
-
存储过程
进一步的学习
- 下载《sql必知必会》中的脚本，实践书中的例子。（尤其是函数部分）
- 留意sql的性能优化。
- 留意sql与其他编程语言的结合。
- 在具体的项目中使用。
