MySQL常用技巧总结

常用MySQL总结

1.查询表

select * from tablename;

2.修改表

添加列

alter table tablename add col char(20);

删除列

alter table tablename drop column col;

删除表

drop table tablename;

3.插入

普通插入

insert into tablename(col1,col2) value (val1,val2);

插入查询结果

insert into tablename (col1,col2) select col1,col2 from tablename;

4.更新

update tablename set col=val where id = 1;

5.删除

delete from tablename where id=1;

6.进阶查询

LIMIT

用于限制返回的行数

返回前10行

select * from tablename limit 10;

select * from tablename limit 0,10;

返回5-10行数据

select * from tablename limit 5,10;

DISTINCT

用于除出重复数据

select distinct col1,col2 from tablename;

7.排序

• ASC： 升序（默认）

• DESC： 降序

select * from tablename desc;

select * from tablename order by col1 desc,col2 asc;

8.过滤

除null

select * from tablename where col is not null;

操作符	说明
=	等于
<	小于
>	大于
<>或!=	不等于
<=或!>	小于等于
>=或!<	大于等于
between	介于

9.通配符

• % ：匹配>=0个任意字符；
• _ ： 匹配==1个任意字符；
• []： 匹配集合内的字符，匹配[]里面的字符；^通字符可以对其进行否定。

select * from tablename where col like '[^AB]%';

匹配不以AB开头的任意文本.

不要滥用通配符，否则可能导致查询速度会很慢

10.字段计算

字段计算

select col1+col2 as col from tablename;

字段拼接

select concat(col1,col2) as col from tablename; 

11.常用函数

函数	说明
AVG()	平均值
COUNT()	总行数
MAX()	最大值
MIN()	最小值
SUM()	求和
LTRIM()	去除左边空格
TRIM()	去除空格
DATE()	日期
RAND()	随机数

12.分组

select col,count(*) as num from tablename group by col;

where过滤行，having过滤分组，行过滤应当先于分组过滤。

select col,count(*) as num from tablename where col > 2 group by col having num >2;

分组规定：

• group by子句出现在where子句之后，order by子句之前；

• 除了汇总字段外，select语句周静的每一字段必须在group by子句中给出；

• null的行会单独分为一组

• 大多数SQL实现不支持group by列具有可变长度的数据类型。

13.子查询

可以将子查询的结果作为where语句的条件

select * from tablename where col1 in (select col2 from tablename)

select name,(select count(*) from orders where orders.id=consumers.id) as orders_num from consumers order by name;

14.连接

连接用于连接多个表，使用join关键字，并且条件语句使用on而不是where。

连接可以替换子查询，并且比子查询的效率一般会更快。

内连接

inner join

select a.value,b.value from tablenamea as a inner join tablenameb as b on a.key=b.key;

外连接

外连接保留了没有关联的那些行，分为左外连接、右外连接和全外连接，左外连接就是保留左表没有关联的行。

检索所有顾客的订单信息，包括还没有订单信息的顾客。

SELECT Customers.cust_id, Customer.cust_name, Orders.order_id
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

customers 表：

cust_id	cust_name
1	a
2	b
3	c

orders 表：

order_id	cust_id
1	1
2	1
3	3
4	3

结果：

cust_id	cust_name	order_id
1	a	1
1	a	2
3	c	3
3	c	4
2	b	Null

15.组合查询

使用nuion来组合两个查询，每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用nuion all

只能包含一个order by子句，并且必须位于语句的最后。

select * from tablename where col=1 union select * from tablename where col=2;

16.视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通标的操作一样。

视图具有以下好处：

• 简化复杂的sql操作，比如复杂的链接；

• 只使用实际表的一部分数据；

• 通过只给用户访问视图的权限，保证数据的安全性；

• 更改数据格式和表示。

17.存储过程

存储过程可以看成是对一系列sql操作的批处理。

使用存储过程的好处：

• 代码封装：保证一定的安全性
• 代码复用
• 高性能：由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以；为结束符，而存储过程中也包含了分号，因此会看错误把这部分分号当成是结束符，造成语法错误。

包含in、out和inout三种参数。

给变量赋值都需要select into语句。

每次只能给一个变量赋值，不支持集合的操作。

delimiter //

create procedure myprocedure( out ret int )
    begin
        declare y int;
        select sum(col1)
        from mytable
        into y;
        select y*y into ret;
    end //

delimiter ;

call myprocedure(@ret);
select @ret

18.游标

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

• 声明游标
• 打开游标
• 取出数据
• 关闭游标

delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;

        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;

        open mycursor;

        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;

        close mycursor;
    end //
 delimiter ;

19.触发器

触发器会在某个表执行以下语句时而自动执行：delete、insert、update。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用before，之后使用after。

before用于数据验证和净化，after用于审跟踪，将修改记录到另外一张表中。

insert 触发器包含一个名为new的虚拟表

CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;

SELECT @result; -- 获取结果

Delete 触发器包含一个名为old的虚拟表，并且是只读的

update 触发器包含一个名为new和一个名为old的虚拟表，其中new是可以被修改的，而old是只读的。

mysql不允许在触发器中使用call语句，也就是不能调用存储过程。

20.事务管理

基本术语：

• 事务：指一组sql语句
• 回退：指撤销指定sql语句的过程
• 提交：指将未存储的sql语句结果写入数据库表
• 保留点：指事务处理中设置的临时占位符，你可以对它发布回去

不能回退select语句，回退select语句也没有意义；也不能回退create和drop语句。

mysql的事务提交是隐式提交，每执行一条语句就把这条语句当成一个事务然后提交。当出现start transaction语句时，会关闭隐式提交；当commit或rollback语句执行后，事务会自动关闭，重新恢复隐式提交。

设置autocommit为0可以取消自动提交；autocommit标记是针对每个连接而不是针对每个服务器的。

如果没有设置保留点，rollback会回退到start transaction语句处；如果设置了保留点，并且在rollback中指定该保留点，则会回退到该保留点。

21.字符集

基本术语：

• 字符集为字母和符号的集合
• 编码为某个字符集成员的内部表示
• 校对字符指定如何比较，主要用于排序和分组

CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以排序、分组时指定校对：

SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

22.权限管理

mysql的账户信息保存在mysql这个数据库中。

use mysql;
select user from user;

创建账户

新创建的账户没有任何权限

create user myuser identified by 'mypassword'

修改账户名

rename user myuser t0 newuser;

删除账户

drop user myuser;

查看权限

show grants for myuser;

授予权限

账户用username@host的形式定义，username@%使用的都是默认主机名。

grant select ,insert on mydatabase.* T0 myuser;

删除权限

grant和revoke可在几个层次上控制访问权限；

• 整个服务器，使用grant all和revoke all
• 整个数据库，使用on database.*
• 特定的表，使用on database.table
• 特定的列
• 特定的存储过程

revoke select,insert on mudatabase.*from myuser;

更改密码

必须使用password()密码进行加密。

set password for myuser = password('new_password');

参考：git地址