MYSQL基本操作

来源：互联网发布：车牌选号软件编辑：程序博客网时间：2024/06/06 07:52

一、DDL

1.数据库操作

》create database dbname default charset=utf8 ;###创建数据库

》alter database dbname charset=utf8;

》show databases；

》show create database dbname;###查看数据库详细信息

》use dbname;###使用数据库dbname

》drop database dbname;###删除数据库

2，表操作

》创建数据库表tablename：在mysql>后粘贴以下SQL语句，存储引擎为MYISAM，字段id为主键、唯一索引

CREATE TABLE tablename( id INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT , username VARCHAR( 20 ) NOT NULL , password CHAR( 32 ) NOT NULL , time DATETIME NOT NULL , number FLOAT( 10 ) NOT NULL , content TEXT NOT NULL , PRIMARY KEY ( id ) ) ENGINE = MYISAM default charset=utf8 ;

》drop table tly;

》修改表结构

alter table tly charset=utf8;###修改表字符编码集为utf8

alter table tly rename [to] tangluyang;###该表名字

alter table tly modify [column] username text;###修改列数据类型

alter table tly change [column] username uname varchar(20);###改变列

alter table tly add [column] salary double;###增列

alter table tly drop[column] salary ;###删列

alter table tly add [constraint] <约束名> 约束定义；###添加约束

alter table tly drop [constraint] <约束名>；###删除约束

》desc tablename;###查看表结构

》show create table tablename；###查看表详细信息

二、DML

>>查询满足条件的元组

比较（比较运算符） =、>、>=、!=

确定范围 between .. and、not between .. and

确定集合 in 、not in（常量1，常量2，...，常量n）

字符匹配 like not like 通配符: _(任意单个字符)、%（匹配0个或者多个字符）

空值 is null 、is not null

多重条件 and、 or

》对查询结果排序

order by 《列名》 [ASC | DESC]

》使用统计函数汇总数据(常和group by条件一起使用)

count（*）、count（[distinct]<列名>）、SUM( )、AVG( ) 、MAX( )、MIN( )

select cno as 课程号，count（sno） as 选课人数

from sc group by cno

》从tly表中检索出唯一的不重复记录：

SELECT DISTINCT field1 field2 ..field n FROM tly;

》从tly表n中检索特定的行：字段username等于abc，字段number等于1，按字段id降序排列

SELECT * FROM tly WHERE username = abc AND number=1 ORDER BY id DESC;

》插入信息到tly表

INSERT INTO tly (id, username, password, time, number, content) VALUES (, abc, 123456,2007-08-06 14:32:12, 23.41, hello world),(,..,),(,..,)...;

》更新tly表中的指定信息

UPDATE tly SET content = hello china WHERE username = abc;

》删除tly表中的指定信息

DELETE FROM tly WHERE id = 1;

》分组查询

select function(field) from tablename where condition group by field;

当function是group_concat（field）时，表示本分组field字段的所有字段取值显示出来；

mysql查询的五种子句 where(条件查询)、having（筛选）、group by（分组）、order by（排序）、limit（限制结果数）

正确理解where(条件查询)、having（筛选）、group by（分组）子句作用与执行顺序。

》where子句用于筛选从from子句指定数据源产生的行数据

》group by子句用于where子句删选后结果数据进行分组

》having子句用于对分组后统计结果再进行筛选

》排序和限制

select * from tablename [where condition] [order by field1] [desc | asc], [order by field2] [desc | asc]....[limit offset_start,row_count]

》聚合.

select [field1,field2,...fieldn] fun_name###聚合函数如max,min,count记录数目,sum

from tablename

[where where_conditon]###聚合前过滤

[group by field1,field2,...fieldn]

[with rollup]###是否对聚合后数据再汇总

[having where_condition]###分类后结果再进行条件的过滤

例子：select deptno,count(1) from emp group by deptno having count(1)>1

》表连接（左连接(包含左表所有记录，即使右表没有匹配的纪录)和右连接）

select ename,deptname from emp left | right [outer] join dept

on emp.deptno=dept.deptno where .....;

》子查询

select * from emp --------------------外层查询

where deptno in (select deptno from dept);--------子查询

》记录联合（untion（去重）和untion all），查询结果合并输出

select deptno from emp

union

select deptno from dept;

三、DCL语句（DBA来管理系统中对象权限使用）

》登录和退出

》对某用户进行数据库、表相关操作权限的授予和控制。

grant [select ],[insert], [delete], [update] on dbname.tablename to 'user'@'host' identifiedby 'password';

四、表的导入和导出

》》导出。有两个方法：

1，select field1,field2,,,, from tablename into outfile 'target_file' [option]

option的参数可以是：

field terminted by 'string' ##字符分隔符，默认为‘\t’.

lines starting by 'string' ##每行前的字符串默认为‘’

lines terminated by 'string' ##行结束符，默认为‘\n’

2，mysqldump导出数据为文本

mysqldump -u username -T target_dir dbname tablename [option]

option为

--field-terminated-by='string'

--lines-terminnated-by='string'

》》导入数据

load data [local] infile 'filename' into table tablename [option]

五，多表查询（连接）

》内连接(由笛卡尔积筛选出来的，常用为等值连接和自然连接（去重复列）)

等值连接（有比较运算符（=））

select * from student [inner] join sc on student.sno=sc.sno;

为了避免重复列，把 * 换做具体列名。

》外连接（分为左、右、全外连接三种，在自然连接基础上，保留左、右表或两表的剩余不满足连接条件的元组）

六，索引的操作

Create table table_name(

属性名数据类型，

….，

Index / key 索引名(属性名1 (长度) (ASC / DESC))

);

在已有表中创建：

Create index 索引名 on table_name (属性名 (长度) (ASC / DESC))

Alter table table_name add index/key 索引名(属性名 (长度) ASC/DESC)

索引类型：(在index前加入相应的关键词)

唯一索引：unique

全文索引：fulltext（只能建立在MyISAM引擎表中）

创建多列索引：(同理在已有表中创建)

Create table table_name(

属性名数据类型，

….，

Index / key 索引名(属性名1 (长度) (ASC / DESC)，

属性名2 (长度) (ASC / DESC))

);

5.2、删除索引

Drop index index_name on table_name;

视图的操作、

视图本质上是一种虚拟表，其内容与真实表类似，但是均来自基本表，可以理解为是查询语句的封装处理，以便下一次相同查询的时候进行调用。视图的建立和删除不影响基本表，而更新(添加、删除、修改)直接影响基本表。，当来自多个表时，不允许添加和伤处数据。

6.1、创建视图：

Create view view_name as 查询语句

Select * from view_name;

可以实现包括常量语句、聚合函数、排序语句、外连接语句、子查询语句、记录联合等语句。

6.2、视图操作

Show view_name; //类似于查看表操作

Show table status from view_name; //查看详细信息

Show create view view_name; //查看视图定义信息

DESC/describe view_name; //查看视图设计信息

Drop view view_name,…; //删除视图

Create or replace view view_name as 查询语句； //替换(修改)视图

Alter view view_name as 查询语句； //修改视图

还可以通过查看系统表information_schema中的views信息。

触发器操作

只有delete、insert、update操作才能激活触发器。

相关语句：

Create trigger trigger_name

before/after trigger_event

on table_name for each row trigger_stmt(执行语句)

触发多条语句：

Delimiter $$

Create trigger trigger_name

before/after trigger_event

on table_name for each row

begin

trigger_stmt(执行语句)

end $$

delimiter;

查看触发器：

Show triggers;

或者查看系统表information_schema中的triggers信息。

删除触发器：

Drop trigger trigger_name;

创建存储过程：

CREATE PROCEDURE sp_name ( [ proc_parameter ] )

[ characteristics ...] routune_body

说明： proc_parameter 的形式有： [IN|OUT|INOUT] parameter_name type .

routine_body：这是存储过程的主体部分，也叫做存储过程体。里面包含了在过程调用的时候必须执行的语句，这个部分总是以begin开始，以end结束。当然，当存储过程体中只有一个SQL语句时可以省略BEGIN-END标志。

IN 输入参数
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数
该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数：调用时指定，并且可被改变和返回。

characteristic：存储过程的某些特征设定：

language sql：表明编写这个存储过程的语言为SQL语言，目前来讲，MySQL存储过程还不能用外部编程语言来编写，也就是说，这个选项可以不指定。将来将会对其扩展，最有可能第一个被支持的语言是PHP。

deterministic：设置为DETERMINISTIC表示存储过程对同样的输入参数产生相同的结果，设置为NOT DETERMINISTIC则表示会产生不确定的结果。默认为NOT DETERMINISTIC。

contains SQL：表示存储过程不包含读或写数据的语句。NO SQL表示存储过程不包含SQL语句。reads SQL DATA表示存储过程包含读数据的语句，但不包含写数据的语句。modifies SQL DATA表示存储过程包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。

SQL SECURITY：SQL SECURITY特征可以用来指定存储过程使用创建该存储过程的用户（DEFINER）的许可来执行，还是使用调用者（INVOKER）的许可来执行。默认值是DEFINER。

COMMENT 'string'：对存储过程的描述，string为描述内容。这个信息可以用SHOWCREATE PROCEDURE语句来显示。

带参数的存储过程：

CREATE PROCEDURE CountProc( OUT param1 INT )

BEGIN

SELECT COUNT(*) INTO param1 FROM product;

END;

参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名数据类形...])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:调用时指定，并且可被改变和返回

Ⅰ. IN参数例子

创建:

1. mysql > DELIMITER //

2. mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)

3. -> BEGIN

4. -> SELECT p_in;

5. -> SET p_in=2;

6. -> SELECT p_in;

7. -> END;

8. -> //

9. mysql > DELIMITER ;

执行结果:

1. mysql > SET @p_in=1;

2. mysql > CALL demo_in_parameter(@p_in);

3. +------+

4. | p_in |

5. +------+

6. | 1 |

7. +------+

9. +------+

10. | p_in |

11. +------+

12. | 2 |

13. +------+

14.

15. mysql> SELECT @p_in;

16. +-------+

17. | @p_in |

18. +-------+

19. | 1 |

20. +-------+

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

Ⅱ.OUT参数例子

创建:

1. mysql > DELIMITER //

2. mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)

3. -> BEGIN

4. -> SELECT p_out;

5. -> SET p_out=2;

6. -> SELECT p_out;

7. -> END;

8. -> //

9. mysql > DELIMITER ;

执行结果:

1. mysql > SET @p_out=1;

2. mysql > CALL sp_demo_out_parameter(@p_out);

3. +-------+

4. | p_out |

5. +-------+

6. | NULL |

7. +-------+

9. +-------+

10. | p_out |

11. +-------+

12. | 2 |

13. +-------+

14.

15. mysql> SELECT @p_out;

16. +-------+

17. | p_out |

18. +-------+

19. | 2 |

20. +-------+

Ⅲ. INOUT参数例子

创建:

1. mysql > DELIMITER //

2. mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)

3. -> BEGIN

4. -> SELECT p_inout;

5. -> SET p_inout=2;

6. -> SELECT p_inout;

7. -> END;

8. -> //

9. mysql > DELIMITER ;

执行结果:

1. mysql > SET @p_inout=1;

2. mysql > CALL demo_inout_parameter(@p_inout) ;

3. +---------+

4. | p_inout |

5. +---------+

6. | 1 |

7. +---------+

9. +---------+

10. | p_inout |

11. +---------+

12. | 2 |

13. +---------+

14.

15. mysql > SELECT @p_inout;

16. +----------+

17. | @p_inout |

18. +----------+

19. | 2 |

20. +----------+

调用存储过程：

call procedurename(args)

创建函数：

CREATE FUNCTION func_name ( [ func_parameter ] )

RETURNS type

[characteristic ...] routine_body

创建函数：

实例一：

Delimiter $$

create function func_employee_sal(empno int(11))

return double(10,2)

comment’查询某个雇员的工资’

begin

return (select sal

from t_employee

where t_employee.empno=empno);

end $$

delimiter;

实例二：

DELIMITER $$

DROP FUNCTION IF EXISTS `onlineFunction`$$

CREATE FUNCTION `onlineFunction`(rrrr VARCHAR(50)) RETURNS 

第一行DELIMITER 定义一个结束标识符，因为MySQL默认是以分号作为SQL语句的结束符的，而函数体内部要用到分号，所以会跟默认的SQL结束符发生冲突，所以需要先定义一个其他的符号作为SQL的结束符。VARCHAR(255)

BEGIN

IF(rrrr='online') THEN RETURN '上线';END IF;

END$$

DELIMITER ;

调用函数：

select funname(args)

变量的操作

根据mysql手册，mysql的变量分为两种：系统变量和用户变量。但是在实际使用中，还会遇到诸如局部变量、会话变量等概念。根据个人感觉，mysql变量大体可以分为四种类型

一、局部变量。

局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。

局部变量一般用declare来声明，可以使用default来说明默认值。

例如在存储过程中定义局部变量：

drop procedure if exists add;

create procedure add

(

in a int,

in b int

)

begin

declare c int default 0;

set c = a + b;

select c as c;

end;

在上述存储过程中定义的变量c就是局部变量

二、用户变量。

用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接，但是当当前连接断开后，其所定义的用户变量都会消失。

用户变量使用如下(这里我们无须使用declare关键字进行定义，可以直接这样使用):

mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了。

第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量

第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……

注意上面两种赋值符号，使用set时可以用“=”或“：=”，但是使用select时必须用“：=赋值”

示例程序如下：

drop procedure if exists math;

create procedure math

(

in a int,

in b int

)

begin

set @var1 = 1;

set @var2 = 2;

select @sum:=(a + b) as sum, @dif:=(a - b) as dif;

end;

mysql> call math(3, 4);
+------+------+
| sum | dif |
+------+------+
|    7 |   -1 |
+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @var1; //var1为用户变量
+-------+
| @var1 |
+-------+
| 1     |
+-------+
1 row in set (0.00 sec)

mysql> select @var2; //var2为用户变量
+-------+
| @var2 |
+-------+
| 2     |
+-------+
1 row in set (0.00 sec)

3、会话变量

服务器为每个连接的客户端维护一系列会话变量。在客户端连接时，使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效。

设置会话变量有如下三种方式：

set session var_name = value;

set @@session.var_name = value;

set var_name = value;

查看一个会话变量也有如下三种方式：

select @@var_name;

select @@session.var_name;

show session variables like "%var%";

mysql> show session variables;

Declare var_name[, …] type [default value] //定义变量

Set var_name=expr[, …] //赋值变量

Select sal into var_sal

From t_employee

Where empno=7566; //利用select into语句将查到的sal值赋给var_sal变量

0 0