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

bitsCN.com

　　　　看到院子里总结的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;#降序descselect * 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; #写法1select * from A,B where A.id = B.id; #写法2select 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)#取余-2ceil(10.6)#进位+1 结果11 ceil(10.0)结果10floor(10.6)#取整 10round(2.5)#四舍五入到整数 结果3round(2.5,2)#保留两位小数 结果2.50truncate(2.5234,3)#取小数后3位不四舍五入 2.523sign(-2);#符号函数 返回-1 0还是0 正数返回1pow(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 atanlength('hi')#计算字符长度concat('1',1,'hi')#合并字符串insert('12345',1,0,'7890');#从开头第1个字符开始到0个结束，替换成后边字符串，0表示在最前边插入ucase('a'),lcase('A')#转成大写和小写left('abcd',2),right('abcd',2);#返回前两个字符和后两个字符ltrim('  0  '),rtrim(' 0 '),trim('  0  ')#删除空格replace('1234567890','345678','0');#替换

输出12090substring('12345',1,2)#取字符 输出12 1是位置 2是长度instr('1234','234');#取得234位置是2reverse('1234');#反序输出4321current()#返回日期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()#当前连接的数据库 没有为nulluser()#获取用户名md5('a')#加密字符串ascii('a')#ascii值97bin(100),hex(100),oct(100)#返回二进制 十六进制 八进制conv(10001,2,8);#各种进制相互转换rand()#生成0到1之间随机数sleep(0.02)#暂停秒数数据库优化1.开启缓存，尽量使用php函数而不是mysql2. explain select 语句可以知道性能3.一行数据使用 limit 1；4.为搜索字段重建索引 比如关键字 标签5.表连接join保证字段类型相同并且有其索引6.随机查询使用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");7.避免使用select * 应该使用具体字段8.每张表都是用id主键，并且是unsigned int9.对于取值有限而固定使用enum类型，如性别 国家 名族 部门 状态10.尽可能使用not null ip存储使用int(4),使用ip 转化函数ip2long()相互long2ip()11.delete和insert语句会锁表，所以可以采用分拆语句操作 while(1){操作语句;usleep(2000);}12.选择正确的存储引擎；MyISAM适合大量查询 写操作多用InnoDB支持事务#存储过程#存储程序delimiter #定义存储程序create procedure getversion(out params varchar(20)) #params是传出参数 in传进 out传出 inout传回beginselect version() into params; #版本信息赋值paramsendcall getversion(@a); #调用存储过程select @a;delimiter #定义存储函数create function display(w varchar(20)) returns varchar(20)beginreturn concat('hello',w);endselect 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 64T 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)

bitsCN.com