MYSQL相关

MySQL命令

1.mysql  -uroot -p  进入到MySQL客户端

2.show databases; 显示所有的数据库列表

3.use *;连接到某个数据库

4.show create database  * ;显示这个数据库的建库信息(字符编码...)

5.show tables [from *]; 显示某个数据库下所有的数据表;

6.show create table  * ;显示某个数据表的建表信息(引擎类型,字符编码,字段及属性等);

7.create table table_name[if not exists](

id tinyint unsigned primary key auto_increment,

name varchar(20) not null); 建表语句

8.insert [into] table_name[field_name1,...] values(value1,...),(value2,...);插入记录,可同时插入多条记录

9.insert table_name [field_name1,...] select expression; 将查找到的记录直接插入.

10.delete from table_name where expression;删除符合where条件的所有记录;

11.update table_name set column_name1=new_value1,column_name2=new_value2,..

     where expression;将符合where条件的记录进行更改;

12.select column_name from table_name where expression group by column_name having 条件 order by 条件 limit 条件;

理解顺序:从表中选择出符合where条件的所有记录,再按照group by 的条件进行分组,

符合group by 条件的所有记录分为一组,再由having 过滤条件进行过滤,最终按照order by 的条件进行排序并输出.[having 过滤表达式是对group by 后的各个组进行过滤后,再输出]

13.子查询subquery;

     由比较运算符引发的子查询: = ,<,>,<=,>=,!=,<>;

      由[not] in / exists 引发的子查询;

14.多表连接进行更新

建表的同时,将查询的结果进行插入:

create table table_name(expr) select columns from table_name;

多表连接进行更新,可以对数据表瘦身,减小存储体积.

update table_name1 [as] alias1 inner join table_name2 [as] alias2 

ON 连接条件

SET更新内容;

将两个表进行连接,当某条记录符合连接条件时,则执行SET 语句进行记录的更新;

15.连接包括Inner join , left join,right join 三种.内连接用的多.

select [columns] from table_name1 as alias1 

inner join table_name2  as alias2 ON 连接条件

inner join table_name3  as alias3 ON 连接条件

...;

多表的连接查询;

内连接只显示符合连接条件的所有记录;

左外连接显示左表中的所有记录 , 以及   右表中符合连接条件的记录;

右外连接显示右表中的所有记录,  以及   左表中符合连接条件的记录;

16.delimiter //       delimiter ; 改变命令行的分界符;

17.运算符及函数

1.字符函数: concat('manny','rose')='mannyrose',

    concat_ws('--','manny','rose','L')='manny--rose--L',

    format(1111.111,1),

    lower('MANNY')='manny',

     upper('manny')='MANNY',

    left('manny',2)='ma',

    right('manny',2)='ny',length(' manny ')=7,

    ltrim(' manny')='manny',[删除前导空格]

    rtrim('manny ')='manny',[删除后继空格]

    trim(' manny ')='manny',[删除前后空格]

    trim(leading '?' from '??manny')='manny',[删除前导的特定字符]

    trim(trailing '?' from 'manny??')='manny',[删除后继的特定字符]

    trim(both '?' from '??manny??')='manny',[删除前导与后继的特定字符]

    substring('manny',2,2)='an',[注意:mysql中字符是从下标1开始排序]

    [not] like ,[模式匹配] %表示任意多个的任意字符; _表示一个任意字符; 

    select * from test where first_name LIKE '%1%%' escape '1';

    表示'1'后面的%不需要按任意字符去解析.

    replace('manny','n','m')='mammy'[将某些字符换成另外一些字符]

2.数值运算符及函数

ceil(3.3)=4;[进一取整]

3 div 4 = 0;[结果取整]

floor(3.3)=3,floor(3.8)=3;[舍一取整]

10 mod 4 = 2; 10.2 mod 4=2.2; -10 mod 4=-2;[取余,同运算符号 %]

power(2,2)=4;[幂运算]

round(3.3)=3;round(3.5)=4;[四舍五入]

truncate(1111.11,1)=1111.1;[数字截取]

truncate(1111.11,-1)=1110;truncate(1111.11,-2)=1100;

3.比较运算符及函数

[NOT] between ... and ...[在某个区间内]

select 5.5 between 4 and 6; return 1; 0表示false,1表示true;

[NOT] in()[在列出的值范围内]

select 5.5 in (4.5,5.5,6.5); return 1;

IS [NOT] NULL[为空]

select null is null; return 1;

select '' is null; return 0;

select * from test where first_name is not null;

    4.日期时间函数

NOW();当前日期与时间

select now();

CURDATE(); 当前日期

select curdate();

CURTIME(); 当前时间

select curtime();

DATE_ADD();日期加或减

select date_add('2014-02-02',interval 2 week);

select date_add('2014-2-2',interval 2 year);

select date_add('2014-2-2',interval 2 day);

select date_add(curdate(),interval 2 month);

DATEDIFF();日期差值[以日为单位]

select datediff('2014-2-2','2015-2-2');return -365;

select datediff(curdate(),'2014-2-2');

DATE_FORMAT();时间/日期格式化

select date_format('2014-2-2','%m/%d/%Y');return 02/02/2014;[带有前导0]

select date_format('2014-2-2','%M/%D/%y');return February/2nd/14;

select date_format(now(),'%Y年%m月%d日 %H点:%i分:%s秒');

[只能用i,不能用m]

[h表示12小时制,H表示24小时制]

[return 2014年07月30日 22点:48分:04秒]

[注意大小写的差别]

     5.信息函数

CONNECTION_ID();[连接ID(一个连接是一个线程滴!!!)]

DATABASE(); [当前数据库]

LAST_INSERT_ID();[最后插入记录的ID][注意:如果一次插入多条记录时,按一条计算这个ID]

USER(); [当前的用户名]

VERSION(); [当前MySQL软件的版本号]

     6.聚合函数

[特点:只有一个返回值]

AVG();[平均值函数]

COUNT();[计数函数] select count(*) from test;[计算所有的记录数]

MAX();[最大值函数]

MIN();[最小值函数]

SUM();[求和函数]

     7.加密函数

MD5();[信息摘要函数]

select md5('admin'); 返回一个32位的字符串;

PASSWORD();[密码算法]

set password=password('manny');[更改客户端登陆密码]

[以上为均为内置函数]

18.自定义函数[UDF:user-defined function]

CREATE  FUNCTION  function_name

RETURNS type{STRING,INTEGER,REAL,DECIMAL}

routine_body[函数体]

关于函数体:

1>函数体由合法的SQL语句构成

2>函数体可以是简单的SELECT或INSERT语句;

3>函数体如果为复合结构则使用BEGIN

...

   END语句;

4>复合结构可以包含声明/循环/控制结构;

1.不带参数的自定义函数

CREATE FUNCTION f1()

RETURNS  varchar(30)

RETURN    date_format(now(),'%Y年%m月%d日 %H点:%i分:%s秒');

2.带有参数的自定义函数

CREATE FUNCTION f2(num1 smallint unsigned,num2 smallint unsigned)

RETURNS float(10,2) unsigned

RETURN   (num1+num2)/2 ;

调用 SELECT f2(1,2); return 1.5;

删除 DROP FUNCTION f2;

3.具有复合结构函数体的自定义函数

此时分界符必须修改成其他 DELIMITER //,否则与复合体中的;混淆

CREATE FUNCTION addUser(username varchar(20))

RETURNS  int unsigned

BEGIN

INSERT test(username) values(username);

RETURN LAST_INSERT_ID();

END

//

19.编码:  创建数据库时可以指定编码方式[create database charset utf8]

这样在此数据库中建表时,这个表默认使用的编码方式和数据库的编码方式一样.

当然也可以单独修改某个表的编码方式[alter table test charset utf8]

而SET NAMES GBK 则只是改变了客户端的编码,写入到数据表中的数据还是

按照服务器中数据表所指定的编码方式进行存储.

20.存储过程 [PROCEDURE]

定义:存储过程是SQL语句和控制语句的预编译集合,以一个名字存储并作为一个单元处理.[可以存在多个返回值]

优点:

增强SQL语句的功能与灵活性

实现较快的执行速度.[在创建时已经进行语法分析和编译,再次调用时便不再需要分析和编译]

减少网络流量.

CREATE 

[DEFINER={user|CURRENT_USER}][不指定时,默认当前用户]

PROCEDURE sp_name ([proc_parameter[,...]])[也可不带参数]

[characteristic...] 

routine_body[称为存储过程的过程体]

proc_parameter:

[IN|OUT|INOUT] param_name  type[参数类型,名称]

IN:表示该参数的值必须在调用存储过程时指定.

OUT:表示该参数的值可以被存储过程的过程体改变,并可以返回.

INOUT:表示该参数在调用存储过程时必须指定,并且可以被改变和返回.

关于过程体:1>过程体由合法的SQL语句构成

2>过程体可以是任意的SQL语句.[主要是CRUD语句]

3>过程体如果为复合结构则使用BEGIN[超过了两个及以上的语句]

...

   END语句;

4>复合结构可以包含声明/循环/控制结构;

[存储过程的修改ALTER 只能修改特性/注释,不能修改过程执行体,只能删除后创建.]

1>创建不带参数的存储过程:

create procedure p1()

select version();

2>创建带有IN类型参数的存储过程:

create procedure p2(in p_id int unsigned)

begin

delete from users where id=p_id;

end

//[同样要将定界符修改成其他]

3>创建带有IN和OUT的存储过程:

create procedure p3(in p_id int unsigned, out userNums int unsigned)

begin

delete from users where id=p_id;[注意:列名与参数名不能重合]

select count(*) from usersInto userNums; [将查询结果放到输出参数中]

end

//

call p3(3,@nums);[@声明用户变量] [局部变量declare只在begin...end之间的首行]

select @nums;

4>创建带有多个OUT参数的存储过程:

row_count(): 得到插入/删除/更新操作所影响的行数.

create procedure p4( in p_id int unsigned,out deleteNums int unsigned,

   out remainNums int unsigned)

begin

delete from users where id=p_id;

select row_count() from users INTO deleteNums;

select count(*) from users INTO remainNums;

end

//

call p4(3,@delete,@remain);[调用存储过程]

select @delete,@remain;[查询结果]

21.存储过程与自定义函数的区别

1.存储过程实现的功能更复杂一些,主要针对表来操作,而函数则针对性更强,功能更单一.

2.存储过程可以返回多个值,而函数只能有一个返回值.

3.存储过程一般独立来执行[call p_name([ ])],而函数可以作为其他SQL语句的组成部分出现.[ 可以把某些功能封装成存储过程,而不是全都用程序来实现,这样更有效率. ]

22.存储引擎

什么是存储引擎: MySQL将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎.

每一种存储引擎使用不同的存储机制,索引技巧,锁定水平,最终提供广泛且不同的功能.

了解每种引擎的特点:

并发控制:当多个连接对记录进行修改时保证数据的一致性和完整性.

锁 共享锁(读锁):同一时段,多个用户可以读取同一资源,读取过程中数据不会发生任何变化.

锁 排他锁(写锁):在任何时候,只有一个用户可以进行写入资源,当进行写锁时会阻塞其他用户进行操作(其他的读锁或写锁操作).

锁的粒度: 行锁:开销最大的锁策略

表锁:开销最小的锁策略

事务处理:用于保证数据库的完整性.

[多个过程当作一个整体来对待,要么全部执行完成,要么一点也不执行.]

事务的特性:原子性/一致性/隔离性/持久性[ACID]

外键:用于保证数据一致性的策略.

索引:是对数据表中的一列或多列进行排序的一种结构.可以快速定位查找.

修改数据表存储引擎的方法:

1.default-storage-engine= myisam

2.通过创建数据表时设定:

create table t_name(

...

) engine=myisam;

3.alter table t_name engine [=] myisam;