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

#登录数据库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,'70');#从开头第1个字符开始到0个结束，替换成后边字符串，0表示在最前边插入ucase('a'),lcase('A')#转成大写和小写left('abcd',2),right('abcd',2);#返回前两个字符和后两个字符ltrim(' 0 '),rtrim(' 0 '),trim(' 0 ')#删除空格replace('12345670','345678','0');#替换