mysql笔记
1 2 #登录数据库 3 mysql -hlocalhost -uroot -p; 4 #修改密码 5 mysqladmin -uroot -pold password new; 6 7 8 #显示数据库 9 show databases; 10 #显示数据表 11 show tables; 12 #选择数据库 13 use examples; 14 #创建数据库并设置编码utf-8 多语言 15 create database `examples` default character set utf8 collate utf8_general_ci; 16 #删除数据库 17 drop database examples; 18 #创建表 19 create table test( 20 id int(10) unsigned zerofill not null auto_increment, 21 email varchar(40) not null, 22 ip varchar(15) not null, 23 state int(10) not null default '-1', 24 primary key (id) 25 )engine=InnoDB; 26 #显示表结构 27 describe 28 #删除表 29 drop table test； 30 #重命名表 31 alter table test_old rename test_new; 32 #添加列 33 alter table test add cn int(4) not null; 34 #修改列 35 alter table test change id id1 varchar(10) not null; 36 #删除列 37 alter table test drop cn; 38 #创建索引 39 alter table test add index (cn,id); 40 #删除索引 41 alter table test drop index cn 42 #插入数据 43 insert into test (id,email,ip,state) values(2,'qq@qq.com','127.0.0.1','0'); 44 #删除数据 45 delete from test where id = 1; 46 #修改数据 47 update test set id='1',email='q@qq.com' where id=1; 48 #查数据 49 select * from test; #取所有数据 50 select * from test limit 0,2; #取前两条数据 51 select * from test email like '%qq%' #查含有qq字符 _表示一个 %表示多个 52 select * from test order by id asc;#降序desc 53 select * from test id not in('2','3');#id不含2,3或者去掉not表示含有 54 select * from test timer between 1 and 10;#数据在1,10之间 55 56 #---------------------------表连接知识------------------------------ 57 #等值连接又叫内链接 inner join 只返回两个表中连接字段相等的行 58 select * from A inner join B on A.id = B.id; #写法1 59 select * from A,B where A.id = B.id; #写法2 60 select a.id,a.title from A a inner join B b on a.id=b.id and a.id=1;#写法3 表的临时名称 61 select a.id as ID,a.title as 标题 from A inner join B on A.id=B.id;#添加as字句 62 63 #左连接又叫外连接 left join 返回左表中所有记录和右表中连接字段相等的记录 64 select * from A left join B on A.id = B.id; 65 66 select * from A left join (B,C,D) on (B.i1=A.i1 and C.i2=A.i2 and D.i3 = A.i3);#复杂连接 67 68 #右连接又叫外连接 right join 返回右表中所有记录和左表中连接字段相等的记录 69 select * from A right join B on A.id = B.id; 70 71 #完整外部链接 full join 返回左右表中所有数据 72 select * from A full join B on A.id = B.id; 73 74 #交叉连接 没有where字句 返回卡迪尔积 75 select * from A cross join B; 76 -------------------------表连接结束------------------------------------------------------------ 77 -----------------索引创建------------------------------------------------ 78 show index from A #查看索引 79 alter table A add primary key(id) #主键索引 80 alter table A add unique(name) #唯一索引 81 alter table A add index name(name) #普通索引 82 alter table A add fulltext(name) #全文索引 83 alter table A add index name(id,name) #多列索引 84 85 #常用函数 86 abs(-1)#绝对值 87 pi()#pi值 88 sqrt(2)#平方根 89 mod(-5,3)#取余-2 90 ceil(10.6)#进位+1 结果11 ceil(10.0)结果10 91 floor(10.6)#取整 10 92 round(2.5)#四舍五入到整数 结果3 93 round(2.5,2)#保留两位小数 结果2.50 94 truncate(2.5234,3)#取小数后3位不四舍五入 2.523 95 sign(-2);#符号函数 返回-1 0还是0 正数返回1 96 pow(2,3),exp(2);#2的3次幂 或e的2次幂 97 log(2),log10(2);#求对数 98 radians(180),degrees(0.618);#角度弧度转换 99 sin(0.5),asin(0.5)#正弦和反正弦 类似cos acos tan atan 100 length('hi')#计算字符长度 101 concat('1',1,'hi')#合并字符串 102 insert('12345',1,0,'7890');#从开头第1个字符开始到0个结束，替换成后边字符串，0表示在最前边插入 103 ucase('a'),lcase('A')#转成大写和小写 104 left('abcd',2),right('abcd',2);#返回前两个字符和后两个字符 105 ltrim(' 0 '),rtrim(' 0 '),trim(' 0 ')#删除空格 106 replace('1234567890','345678','0');#替换输出12090 107 substring('12345',1,2)#取字符 输出12 1是位置 2是长度 108 instr('1234','234');#取得234位置是2 109 reverse('1234');#反序输出4321 110 current()#返回日期 111 curtime()#返回时间 112 now()#返回日期时间 113 month(now())#当前月份 monthname 英文月份 114 dayname(now())#星期英文 dayofweek()1是星期天 weekday()1是星期二 115 week(now())#本年第多少周 116 dayofyear(now()),dayofmonth(now())#今天是本年第多少天 今天是本月第多少天 117 year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now())#返回年月日 时分秒 118 time_to_sec(now()),sec_to_time(3600*8);#转换时间为秒和还原 119 version()#mysql版本 120 database()#当前连接的数据库 没有为null 121 user()#获取用户名 122 md5('a')#加密字符串 123 ascii('a')#ascii值97 124 bin(100),hex(100),oct(100)#返回二进制 十六进制 八进制 125 conv(10001,2,8);#各种进制相互转换 126 rand()#生成0到1之间随机数 127 sleep(0.02)#暂停秒数 128 129 数据库优化 130 .开启缓存，尽量使用php函数而不是mysql 131 . explain select 语句可以知道性能 132 .一行数据使用 limit 1； 133 .为搜索字段重建索引 比如关键字 标签 134 .表连接join保证字段类型相同并且有其索引 135 .随机查询使用php $r = mysql_query("SELECT count(*) FROM user"); 136 $d = mysql_fetch_row($r); 137 $rand = mt_rand(0,$d[0] - 1); 138 $r = mysql_query("SELECT username FROM user LIMIT $rand, 1"); 139 .避免使用select * 应该使用具体字段 140 .每张表都是用id主键，并且是unsigned int 141 .对于取值有限而固定使用enum类型，如性别 国家 名族 部门 状态 142 .尽可能使用not null ip存储使用int(4),使用ip 转化函数ip2long()相互long2ip() 143 .delete和insert语句会锁表，所以可以采用分拆语句操作 144 while(1){操作语句;usleep(2000);} 145 .选择正确的存储引擎；MyISAM适合大量查询 写操作多用InnoDB支持事务 146 147 #存储过程 148 #存储程序 149 delimiter #定义存储程序 150 create procedure getversion(out params varchar(20)) #params是传出参数 in传进 out传出 inout传回 151 begin 152 select version() into params; #版本信息赋值params 153 end 154 call getversion(@a); #调用存储过程 155 select @a; 156 delimiter #定义存储函数 157 create function display(w varchar(20)) returns varchar(20) 158 begin 159 return concat('hello',w); 160 end 161 select display('world'); 162 163 drop procedure if exists spName; #删除一个存储过程 164 alter function spName [];#修改一个存储过程 165 show create procedure spName;#显示存储过程信息 166 declare varName type default value;#声明局部变量 167 #if语句 168 if 条件 then 语句 169 elseif 条件 then 语句 170 else 语句 171 end if 172 #case语句 173 case 条件 174 when 条件 then 语句 175 when 条件 then 语句 176 else 语句 177 end case 178 #loop语句 179 fn:loop 180 语句 181 end loop fn; 182 leave fn #退出循环 183 #while语句 184 fn：while 条件 do 185 语句 186 end while fn 187 188 189 #mysql使用帮助资料 190 ? contents; #列出帮助类型 191 ? data types;#列出数据类型 192 ？ int;#列出具体类型 193 ? show;#show语句 194 ? create table;# 195 #常见表的比较 196 Myisam BDB Memory InnoDB Archive 197 存储限制 no no yes 64T no 198 事物安全 支持 支持 199 锁机制 表锁 页锁 表锁 行锁 行锁 200 全文索引 支持 201 外键支持 支持 202 myisam frm存储表定义 MYD存储数据 MYI存储索引 203 InnoDB 用于事务处理 204 char 和 varchar保存和索引都不相同 205 浮点数float(10,2) 定点数decimal(10,2) 206 长度一定下，浮点数表示更大数据范围，缺点是引起精度丢失，货币等使用定点数存储 207 索引适合于where字句或者连接字句列 208 对于唯一值使用唯一索引 209 210 添加新用户 grant select,insert,update,delete on *.* to Yoby@localhost identified by 'mysql'; 211 # *.* 数据库名.表名，限制登录某一个数据库 test.* localhost是本地主机 网络可以使用 '%'代替所有主机 'mysql'是密码 Yoby是用户名 所有权限可以用 all代替 212 查看用户权限 show grants for 'root'@'localhost'; 213 移除权限 revoke all on *.* from root@localhost; 214 group by id 分组 215 having 限制字句 216 select1 union select2 联合查询有重复去掉保留一行 217 select2 union all select2 所有行合并到结果集中去
转载地址：http://www.jb51.net/article/30438.htm
