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 <trigger_name> [before|after] [inser|update|detele] on <table_name> 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;