mysql------基础及常见SQL技巧

基础 1.1 mysql表复制

复制表结构+复制表数据
mysql>create table t3 like t1;
mysql>insert into t3 select * from t1;

1.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)
ALTER TABLE table_name ADD PRIMARY KEY(column_list)

2.create index

CREATE INDEX index_name ON table_name(column_list)
CREATE UNIQUE INDEX index_name ON table_name(column_list)

3.drop index

DROP INDEX index_name ON table_name

4.alter table table drop

ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

1.3mysql视图

mysql>create view v_t1 as select * from t1 where id>4 and id<11;
mysql> ? view   //view视图的帮助信息
ALTER VIEW
CREATE VIEW
DROP VIEW
mysql>show tables;  //查看视图
mysql>drop view v_t1;   //删除视图v_t1

1.4mysql内置函数 字符串函数：

CONCAT(string2[,...])   //连接字符串
LCASE(string2)  //转换成小写
UCASE(string2)  //转换成大写
LENGTH(string)  //string长度
LTRIM(string2)  //去除前端空格
RTRIM(string2)  //去除后端空格
REPEAT(string2,count)   //重复count次
REPLACE(str,search_str,replace_str) //在str中用replace_str替换search_str
SUBSTRING(str,position[,length])    //从str的position开始，取length个字符
SPACE(count)    //生成count个空格

数学函数：

BIN(decimal_number) //十进制转二进制
CEILING(number2)    //向上取整
FLOOR(number2)  //向下取整
MAX(col)    //取最大值,聚合时使用
MIN(col)    //取最小值,聚合时使用
SORT(number2)   //开平方
RAND()  //返回0-1内的随机值

日期函数：

CURDATE()   //返回当前日期
CURTIME()   //返回当前时间
NOW()   //返回当前的日期时间
UNIX_TIMESTAMP(date)    //返回当前date的UNIX时间戳
FROM_UNIXTIME() //返回UNIX时间戳的日期值
WEEK(date)  //返回日期date为一年中的第几周
YEAR(date)  //返回日期date的年份
DATEDIFF(expr,expr2)    //返回起始时间expr和结束时间expr2间天数

1.5mysql预处理语句

设置stmt1预处理，传递一个数据作为一个where判断条件
mysql>prepare stmt1 from 'select * from t1 where id>?';
mysql>set @i=1; //设置一个变量
mysql>execute stmt1 using @i;   //执行stmt1预处理
mysql>set@i=5;  //设置@i为5
mysql>execute stmt1 using @i;   //再次去执行stmt1
mysql>drop prepare stmt1:   //如何删除预处理stmt1

1.6mysql事务处理

mysql>set autocommit=0; //关闭自动提交功能
mysql>delete from t1 where id=11;   //从表t1中删除了一条记录
mysql>savepoint p1; //此时做一个p1还原点
mysql>delete from t1 where id=10;   //再次从表t1中删除一条记录
mysql>savepoint p2; //再次做一个p2还原点
mysql>rollback to p1;   //此时恢复到p1还原点，即p2自动失效
mysql>rollback; //退回到最原始的还原点

1.7mysql存储 创建一个存储p1();
mysql>d //改变语句界定符
mysql>create procedure p1()
->begin
->set @i=0;
->while @i<10 do
->select @i;
->set @i=@i+1;
->end while;
->end;
->//
mysql>d;
mysql>call p1(); //执行存储p1();
mysql>show procedurestatusG //查看procedure p1()的status信息
mysql>show create procedure p1G //查看procedure p1()的具体信息 1.8mysql触发器 (用于增删改)

创建一个名字为tg1的触发器，当向t1表中插入数据时，就向t2表中插入一条数据

修改delimiter为//
mysql>d //
mysql>create trigger tg1 before insert on t1 for each row
>begin
>insert into t2(id) values(new.id);
>end//
准备两个空表t1和t2
mysql>select * from t1;
mysql>select * from t2;
向t1表中插入多条数据：
mysql>insert into t1 values(1),(2),(3),(4);

mysql>create trigger t2 before delete on t1 for each row
-> begin
-> delete from t2 where id=old.id;
-> end//

如何制作删除表t1后t2表中的记录也会跟着删除呢？
mysql>create trigger tg3 before delete on t1 for each row
>begin delete from t2 where id=old.id;
>end//

如何制作更改表t1后t2表也跟着修改呢？
mysql>create trigger tg4 before update on t1 for each row
>begin update t2 set id=new.id where id=old.id;
>end//
改回语句界定符为分号
mysql>d;
查看触发器：
mysql>show triggers;

1.9重排auto_increment值

MYSQL数据库自动增长的ID如何恢复
清空表的时候，不能用delete from tablename;
而是要用：truncate table tablename;
这样auto_increment就恢复成1了
或者
清空内容后直接ALTER命令修改表：
alter table tablename auto_increment=1;

常见SQL技巧 2.1 正则表达式的使用 （？ regexp）

使用正则表达式“$”和“[…]”进行匹配：

mysql>select name,email from t where email REGEXP “@163[.,]com$”

使用like方式查询：
mysql>select name,email from t where email like “%@163.com” or email like “%@163,com”

2.2 巧用RAND()提取随机行

MYSQL数据库中有一个随机函数rand()是获取一个0-1之间的数，利用这个函数一起和order by能够把数据随机排序。

mysql>select * from stu order by rand();

下面是通过limit随机抽取了3条数据样本

mysql>select * from stu order by rand() limit3;

2.3 利用GROUP BY的WITH ROLLUP子句统计 使用group by 的 with rollup子句可以检索出更多的分组聚合信息
对demo表按照cname、pname列分组对pname列进行聚合计算如下
同样使用with rollup 关键字后，统计出更多的信息，如下（注意：with rollup不可以和order by同时使用）
2.4 利用BIT GROUP FUNCTIONS做统计 在使用group by语句时可以同时用使用bit_and、bit_or函数来完成统计工作。这两个函数的作用主要是做数值之间的逻辑位运算
mysql>select id,bit_or(kind) from order_rab group by id; 对order_rab表中id分组时对kind做位与和或计算。
mysql>select id,bit_and(kind) from order_rab group by id; 2.5 使用外键需要注意的问题

创建外键的方式如下
mysql>create table temp(id int,name char(20),foreign key(id) references outTable(id) on delete cascade on update cascade);
注意：Innodb类型的表支持外键，myisam类型的表，虽然创建外键可以成功，但是不起作用，主要原因是不支持外键。

2.6 mysql中help的使用 在mysql中那么多的命令如何才能记得住是个问题，这里有一个特别好的获得帮助的好方法，当然是在mysql》的提示下的操作：

？ %
可以获得所有的mysql>里的命令，这个是最多的，那么这里的东西如何去进一步获得帮助呢？

？create

? opti%
因为记不住optimize的全称，这个时候可以用%来替代

? reg%
获得了记不住了的regexp用法

查看所有用？ contents可以得到所有的帮助大纲，通过这个目录再用？继续往下细查。