常用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地址