Mysql使用大全从基础到存储过程

看到园子里总结的Mysql用法，我觉得没有我的全面，我的从登录到高级的存储过程都涉及到，这部分是我平常不会或是出现问题都会拿来看，不过现在就和我一起来使用命令模式学习一下数据库最基本的吧

平常习惯了phpmyadmin等其他工具的的朋友有的根本就不会命令，如果让你笔试去面试我看你怎么办，所以，学习一下还是非常有用的，也可以知道你通过GUI工具的时候工具到底做了什么。Mysql用处很广，是php最佳拍档，Java中使用也很方便。

　　　　我是通过Windows 7 操作的，所以打开运行-输入cmd吧，然后输入mysql -hlocalhost -uroot -p;回车后就可以输入密码了，这里可以*号显示，当然也可以和-p连写的，这就是登录mysql。修改密码mysqladmin -uroot -pold password new;这里的root是用户名 new是你的新密码。退出是什么命令，曾有人问我，我说你直接点X好了，不过命令是quit;退出到cmd环境，退出cmd环境命令是exit;接着就是操作mysql的增删改查，常称为CURD操作。
代码如下:

#登录数据库 
mysql -hlocalhost -uroot -p; 
#修改密码 
mysqladmin -uroot -pold password new; 

#显示数据库 
show databases; 
#显示数据表 
show tables; 
#选择数据库 
use examples; 
#创建数据库并设置编码utf-8 多语言 
create database `examples` default character set utf8 collate utf8_general_ci; 
#删除数据库 
drop database examples; 
#创建表 
create table test( 
id int(10) unsigned zerofill not null auto_increment, 
email varchar(40) not null, 
ip varchar(15) not null, 
state int(10) not null default '-1', 
primary key (id) 
)engine=InnoDB; 
#显示表结构 
describe 
#删除表 
drop table test； 
#重命名表 
alter table test_old rename test_new; 
#添加列 
alter table test add cn int(4) not null; 
#修改列 
alter table test change id id1 varchar(10) not null; 
#删除列 
alter table test drop cn; 
#创建索引 
alter table test add index (cn,id); 
#删除索引 
alter table test drop index cn 
#插入数据 
insert into test (id,email,ip,state) values(2,'qq@qq.com','127.0.0.1','0'); 
#删除数据 
delete from test where id = 1; 
#修改数据 
update test set id='1',email='q@qq.com' where id=1; 
#查数据 
select * from test; #取所有数据 
select * from test limit 0,2; #取前两条数据 
select * from test email like '%qq%' #查含有qq字符 _表示一个 %表示多个 
select * from test order by id asc;#降序desc 
select * from test id not in('2','3');#id不含2,3或者去掉not表示含有 
select * from test timer between 1 and 10;#数据在1,10之间 
#---------------------------表连接知识------------------------------ 
#等值连接又叫内链接 inner join 只返回两个表中连接字段相等的行 
select * from A inner join B on A.id = B.id; #写法1 
select * from A,B where A.id = B.id; #写法2 
select a.id,a.title from A a inner join B b on a.id=b.id and a.id=1;#写法3 表的临时名称 
select a.id as ID,a.title as 标题 from A inner join B on A.id=B.id;#添加as字句 
#左连接又叫外连接 left join 返回左表中所有记录和右表中连接字段相等的记录 
select * from A left join B on A.id = B.id; 
select * from A left join (B,C,D) on (B.i1=A.i1 and C.i2=A.i2 and D.i3 = A.i3);#复杂连接 
#右连接又叫外连接 right join 返回右表中所有记录和左表中连接字段相等的记录 
select * from A right join B on A.id = B.id; 
#完整外部链接 full join 返回左右表中所有数据 
select * from A full join B on A.id = B.id; 
#交叉连接 没有where字句 返回卡迪尔积 
select * from A cross join B; 
-------------------------表连接结束------------------------------------------------------------ 
-----------------索引创建------------------------------------------------ 
show index from A #查看索引 
alter table A add primary key(id) #主键索引 
alter table A add unique(name) #唯一索引 
alter table A add index name(name) #普通索引 
alter table A add fulltext(name) #全文索引 
alter table A add index name(id,name) #多列索引 
#常用函数 
abs(-1)#绝对值 
pi()#pi值 
sqrt(2)#平方根 
mod(-5,3)#取余-2 
ceil(10.6)#进位+1 结果11 ceil(10.0)结果10 
floor(10.6)#取整 10 
round(2.5)#四舍五入到整数 结果3 
round(2.5,2)#保留两位小数 结果2.50 
truncate(2.5234,3)#取小数后3位不四舍五入 2.523 
sign(-2);#符号函数 返回-1 0还是0 正数返回1 
pow(2,3),exp(2);#2的3次幂 或e的2次幂 
log(2),log10(2);#求对数 
radians(180),degrees(0.618);#角度弧度转换 
sin(0.5),asin(0.5)#正弦和反正弦 类似cos acos tan atan 
length('hi')#计算字符长度 
concat('1',1,'hi')#合并字符串 
insert('12345',1,0,'70');#从开头第1个字符开始到0个结束，替换成后边字符串，0表示在最前边插入 
ucase('a'),lcase('A')#转成大写和小写 
left('abcd',2),right('abcd',2);#返回前两个字符和后两个字符 
ltrim(' 0 '),rtrim(' 0 '),trim(' 0 ')#删除空格 
replace('12345670','345678','0');#替换

输出12090
substring('12345',1,2)#取字符 输出12 1是位置 2是长度
instr('1234','234');#取得234位置是2
reverse('1234');#反序输出4321
current()#返回日期
curtime()#返回时间
now()#返回日期时间
month(now())#当前月份 monthname 英文月份
dayname(now())#星期英文 dayofweek()1是星期天 weekday()1是星期二
week(now())#本年第多少周
dayofyear(now()),dayofmonth(now())#今天是本年第多少天 今天是本月第多少天
year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now())#返回年月日 时分秒
time_to_sec(now()),sec_to_time(3600*8);#转换时间为秒和还原
version()#mysql版本
database()#当前连接的数据库 没有为null
user()#获取用户名
md5('a')#加密字符串
ascii('a')#ascii值97
bin(100),hex(100),oct(100)#返回二进制 十六进制 八进制
conv(10001,2,8);#各种进制相互转换
rand()#生成0到1之间随机数
sleep(0.02)#暂停秒数

数据库优化
.开启缓存，尽量使用php函数而不是mysql
. explain select 语句可以知道性能
.一行数据使用 limit 1；
.为搜索字段重建索引 比如关键字 标签
.表连接join保证字段类型相同并且有其索引
.随机查询使用php $r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
.避免使用select * 应该使用具体字段
.每张表都是用id主键，并且是unsigned int
.对于取值有限而固定使用enum类型，如性别 国家 名族 部门 状态
.尽可能使用not null ip存储使用int(4),使用ip 转化函数ip2long()相互long2ip()
.delete和insert语句会锁表，所以可以采用分拆语句操作
while(1){操作语句;usleep(2000);}
.选择正确的存储引擎；MyISAM适合大量查询 写操作多用InnoDB支持事务

#存储过程
#存储程序
delimiter #定义存储程序
create procedure getversion(out params varchar(20)) #params是传出参数 in传进 out传出 inout传回
begin
select version() into params; #版本信息赋值params
end
call getversion(@a); #调用存储过程
select @a;
delimiter #定义存储函数
create function display(w varchar(20)) returns varchar(20)
begin
return concat('hello',w);
end
select display('world');

drop procedure if exists spName; #删除一个存储过程
alter function spName [];#修改一个存储过程
show create procedure spName;#显示存储过程信息
declare varName type default value;#声明局部变量
#if语句
if 条件 then 语句
elseif 条件 then 语句
else 语句
end if
#case语句
case 条件
when 条件 then 语句
when 条件 then 语句
else 语句
end case
#loop语句
fn:loop
语句
end loop fn;
leave fn #退出循环
#while语句
fn：while 条件 do
语句
end while fn

#mysql使用帮助资料
? contents; #列出帮助类型
? data types;#列出数据类型
？ int;#列出具体类型
? show;#show语句
? create table;#
#常见表的比较
Myisam BDB Memory InnoDB Archive
存储 no no yes T no
事物安全 支持 支持
锁机制 表锁 页锁 表锁 行锁 行锁
全文索引 支持
外键支持 支持
myisam frm存储表定义 MYD存储数据 MYI存储索引
InnoDB 用于事务处理
char 和 varchar保存和索引都不相同
浮点数float(10,2) 定点数decimal(10,2)
长度一定下，浮点数表示更大数据范围，缺点是引起精度丢失，货币等使用定点数存储
索引适合于where字句或者连接字句列
对于唯一值使用唯一索引

添加新用户 grant select,insert,update,delete on *.* to Yoby@localhost identified by 'mysql';
# *.* 数据库名.表名，登录某一个数据库 test.* localhost是本地主机 网络可以使用 '%'代替所有主机 'mysql'是密码 Yoby是用户名 所有权限可以用 all代替
查看用户权限 show grants for 'root'@'localhost';
移除权限 revoke all on *.* from root@localhost;
group by id 分组
having 字句
select1 union select2 联合查询有重复去掉保留一行
select2 union all select2 所有行合并到结果集中去

这是一份最完整的mysql笔记，需要的可以复制保存了！

(原创 Yoby)