mysql表级别的操作_MySQL基础操作 - 表内容级别

1.增加

insert into 表 (列名,列名...) values (值,值,值...);

insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...);

insert into 表 (列名,列名...) select (列名,列名...) from 表;

2.删除

delete from 表

delete from 表 where ......;

3.修改

update 表 set name ＝ 'a' where 条件;

4.查看

select * from 表;

select 列名,列名,列名 from 表 where 条件;

5.选择的条件方式

可以使用and ,or

between a and b

in 和 not in

where name like 'sfenc_' 以sfenc开头的之后有一个字符的名字

where name like 'sfenc%' 以sfenc开头的之后有多个字符的名字

select * from 表 limit 3;前3行

select * from 表 limit 3,4; 从第三行开始的4行

6.排序

select * from 表 order by 列 asc; -- 根据 “列” 从小到大排列

select * from 表 order by 列 desc; -- 根据 “列” 从大到小排列

select * from 表 order by 列1 desc,列2 asc; -- 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

7.分组

select num from 表 group by num;

select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid;

select num from 表 group by num having max(id) > 10 --having后的是分组后的条件

除此之外，group by 必须在where之后，order by之前

8.表的连接

不符合条件的不会显示

select A.name, B.name

from A,B

Where A.nid = B.nid;

以左边的为基础，左边的表都显示，若B中没有对应关系的列则显示为null

select A.name, B.name

from A left join B

on A.nid = B.nid;

以右边的为基础，左边的表都显示，若A中没有对应关系的列则显示为null

select A.name, B.name

from A right join B

on A.nid = B.nid;

两个表互相约束，和上两个方法类似，但把有null的行都去掉了

select A.name, B.name

from A inner join B

on A.nid = B.nid

9.组合

去除重复

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

不去除重复

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

组合后的列名为第一个select语句中的列名

10.视图

创建视图

CREATE VIEW 视图名 AS sql语句

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition;

删除视图

DROP VIEW 视图名

修改视图

将视图名换一个表示的视图

ALTER VIEW 视图名称 AS SQL语句;

无法通过对视图的更新来达成对真实表的更新

查询视图

可将视图看作一个表来查询

11.存储过程

创建存储过程：

delimiter // --将语句终止符;改为//

create procedure p1()

BEGIN

select * from table1;

END//

delimiter ; --将语句终止符改回;

调用存储过程

call p1();

有参数的存储过程

delimiter \\

create procedure p1(

in i int, --in表示传入变量，out表示传出变量，inout表示既传入又传出变量

inout j int,

out o int

)

BEGIN

set i3 = i+o;

end\\

delimiter ;

set @i1=1; --变量加@表示用户变量，不加表示局部变量

set @i2=2;

call p1(2,@i1,@i2);

select @i1,@i2;

删除存储过程

drop procedure 存储过程名;

12.函数

内置函数：

MySQL提供了很多内置函数，具体可参考https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

自定义函数：

delimiter \\

create function func(

i1 int,

i2 int)

returns int

BEGIN

declare num int; --必须用declare申明

set num = i1 + i2;

return(num);

END \\

delimiter ;

删除函数：

drop function 函数名;

使用函数

select name from table1 where nid=func(nid);

查看返回值

declare @i int;

select func(2) into @i; --将一个表的信息复制到另一个表

select @i;

13.事务

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务就是有多个操作，要么一起执行，要么就都不执行。

执行事务

START TRANSACTION; --也可以用begin

DELETE from name_table where name='aaa';

insert into name_table(name)values('sfencs');

COMMIT; --提交事务

事务回滚

rollback; --回滚到执行事务之前

事务与存储过程结合使用

delimiter \\

create PROCEDURE p1(

out p_return int

)

BEGIN

DECLARE exit handler for sqlexception

BEGIN

-- ERROR

set p_return = 1;

rollback;

END;

DECLARE exit handler for sqlwarning

BEGIN

-- WARNING

set p_return = 2;

rollback;

END;

START TRANSACTION;

DELETE from name_table where name='aaa';

insert into name_table(name)values('sfencs');

COMMIT;

-- SUCCESS

set p_return = 0;

END\\

delimiter ;

set @p=0;

call p1(@p);

select @p;

14.触发器

触发器的用途为用户对表进行增删改的操作的前或后，同时进行另一个操作。

创建触发器：

插入前

CREATE TRIGGER 触发器名 BEFORE INSERT ON table1 FOR EACH ROW

BEGIN

...

END

插入后

CREATE TRIGGER 触发器名 AFTER INSERT ON table1 FOR EACH ROW

BEGIN

...

END

除此之外还有删除delete前后和更新update前后

这里定义触发器时一般也需要使用delimiter来改变语句结束符号

对于begin和end之间执行的语句，如果想根据想删除，插入，更新顶点数据进行判别，动态的执行相关语句时，那么就要用一种方式来表示数据，使用new和old来表示

当使用insert语句的时候，如果原表中没有数据的话，那么对于插入数据后表来说新插入的那条数据就是new

当使用delete语句的时候，删除的那一条数据相对于删除数据后表的数据来说就是old

当使用update语句的时候，当修改原表数据的时候相对于修改数据后表的数据来说原表中修改的那条数据就是old，而修改数据后表被修改的那条数据就是new

举例：old.name,就是原表中数据中的名字，new.id就是新数据中的id。

删除触发器：

DROP TRIGGER 触发器名;

15.sql中的if语句

if 条件 THEN

语句1;

ELSEIF 条件 THEN

语句2;

ELSE

语句3;

END IF;

16.sql中的循环语句

WHILE 条件 DO

语句;

END WHILE ;

或

repeat

语句;

until 条件

end repeat;

或

loop_name:loop

语句;

语句;

if 条件 then

iterate loop_name; --回到loop开始的位置

if 条件 THEN

leave loop_name; --跳出循环

end loop;