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] <约束名>;###删除约束
SELECT DISTINCT field1 field2 ..field n FROM tly;
SELECT * FROM tly WHERE username = abc AND number=1 ORDER BY id DESC;
INSERT INTO tly (id, username, password, time, number, content) VALUES (, abc, 123456,2007-08-06 14:32:12, 23.41, hello world),(,..,),(,..,)...;
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子句用于对分组后统计结果再进行筛选
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. +------+
8.
9. +------+
10. | p_in |
11. +------+
12. | 2 |
13. +------+
14.
15. mysql> SELECT @p_in;
16. +-------+
17. | @p_in |
18. +-------+
19. | 1 |
20. +-------+
|
Ⅱ.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. +-------+
8.
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. +---------+
8.
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变量 |