mysql opti_MySQL基础操作

查看帮助：? 关键词 如 ? trigger

一、Mysql常用基础操作

1、mysql表复制

1) create table t2 like t1;   --复制表结构，t2与t1表结构一致

2) insert into t2 select * from t1;   --复制数据，建议指明字段

2、mysql索引

1) ALTER TABLE创建普通索引、UNIQUE索引和PRIMARY KEY索引(推荐)

① ALTER TABLE table_name ADD INDEX index_name(column_list)   --创建普通索引

② ALTER TABLE table_name ADD UNIQUE(column_list)   --创建UNIQUE索引

③ ALTER TABLE table_name ADD PRIMARY KEY(column_list);   --创建PRIMART KEY索引

2) create index创建索引

① CREATE INDEX index_name ON table_name(column_list);    --创建普通索引

② CREATE UNIQUE INDEX index_name ON table_name(column_list);    --创建UNIQUE索引

3) drop index删除索引

DROP INDEX index_name ON table_name;   --删除索引

4) alter table drop

① ALTER TABLE table_name DROP INDEX index_name;   --删除普通索引

② ALTER TABLE table_name DROP PRIMARY KEY;   --删除PRIMARY KEY索引

注：① create不能创建主键索引，alter可以创建任意索引。

② show index from table_name;   --查看表的索引

3、mysql视图

1) 创建视图：CREATE VIEW view_name as 子查询

例：CREATE VIEW view v1 as select * from t1;

2) 删除视图：DROP VIEW view_name;

4、mysql内置函数

4.1、字符串函数

1) CONCAT(string2[,...])    //连接字符串

2) LCASE(string2)        //转换成小写

3) UCASE(string2)        //转换成大写

4) LENGTH(string)        //计算字符串长度

5) LTRIM(string2)        //去除前空格

6) RTRIM(string2) 　　   //去除后空格

7) REPEAT(string2,count)   //重复count次

8) REPLACE(str,search_str,replace_str)    //在str中用replace_str替换search_str

9) SUBSTRING(str,position[,length])     //从str的position开始，截取length个字符  注意序号从1开始

10) SPACE(count)          //生成count个空格

4.2、数学函数

1) BIN(decimal_number)     //十进制转二进制

2) CEILING(number2)       //向上取整

3) FLOOR(number2)         //向下取整

4) MAX(col)　　　　       //取最大值，聚合时使用

5) MIN(col) 　　　　　　　//取最小值，聚合时使用

6) SQRT(number2) 　　　　 //开平方

7) RAND()　　　　　　　　 //返回0~1内随机数

例:① select * from t1 order by rand();     --表t1的查询结果随机进行排序

② select * from stu order by rand() limit 3;  --随机取出3条记录

4.3、日期函数

1) CURDATE()         //返回当前日期

2) CURTIME()　　　　 //返回当前时间

3) NOW()　　　　　　 //返回当前日期和时间

4) UNIX_TIMESRAMP(date)　　//返回当前date的UNIX时间戳

5) FROM_UNIXTIME(int)    //返回UNIX时间戳的日期值

6) WEEK(DATE)         //返回date为一年中的第几周

7) YEAR(date)　　　　 //返回日期date的年份

8) DATEDIFF(expr,expr2)   //返回起始时间和结束时间之间的天数,结果为expr比expr2晚的天数

5、mysql预处理语句

例：a、mysql>prepare stmt1 from "select * from t1 where id>?";    --设置stmt1预处理，传递一个数据作为一个where的判断条件

b、mysql>set @i=3;   --设置一个变量

c、mysql>execute stmt1 using @i;    --执行预处理stmt1，使用变量@i

d、mysql>drop prepare stmt1;    --删除预处理stmt1

6、mysql事务处理

例：a、mysql>set autocommit=0;   --关闭事务的自动提交

b、mysql>delete from t1 where id>5;    --执行一些操作

c、mysql>rollback;      --事务回滚，回滚

d、mysql>commit;    --事务提交

e、savepoint p1;   --设置还原点p1，保存当前的数据库状态

f、rollback to p1;    --还原到还原点p1时的状态

注：1) 常见的只有InnoDB引擎支持事务。( ①查看表引擎：show create table t1; ②修改表引擎：alter table t1 engine=InnoDB; )

2) 查看autocommit值:select @@autocommit;

7、mysql存储

1) 新建存储过程

create procedure procedure_name()

begin

....//处理语句

end

2) 调用存储过程 call procedure_name

3) 查看数据库中所有的存储过程 show procedure status;

4) 查看存储过程详细信息 show create procedure procedure_name;

例：\d //        --修改输入结束符为// 默认为;  \d 是 delimiter的简写

create procedure p1()

begin

set @i=1;

while @i<=100 do

insert into t1(name) values(concat("user",@i));

set @i=@i+1;

end while;

`　　　end//

\d ;    --创建存储过程后，修改输入结束符为;

8、mysql触发器

1) 创建触发器 格式

create trigger [before|after] [inser|update|detele] on for each row

2) 查看触发器

show triggers;

3) 删除触发器

drop trigger trigger_name;

例：1)创建一个触发器，向t1插入数据时，同时向t2插入数据

\d //

create trigger tg1 before insert on t1 for each row

begin

insert into t2(name) values(new.name);

end//

2)修改t1数据，同时修改t2数据

\d //

create trigger tg2 before update on t1 for each row

begin

update t2 set name=new.name where name=old.name;

end//

9、重排auto_increment值

1) 使用truncate清空表时，auto_increment会自动还原为1

2) 使用ALTER手动重置auto_increment。格式：alter table tablename auto_increment=1;

10、正则表达式

例：1) select name,email from t where email regexp "@126.com$"

11、MySQL中help的使用

1) ? create   --查看create语句的帮助信息

2) ? opti%   --匹配optimize

3) ? contents;   --查询所有的帮助信息

12、查看技巧，\G行列转换

show create table t1\G;