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 则只是改变了客户端的编码,写入到数据表中的数据还是

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

Alter table brand charset [=] utf8 ;

Alter database db1 charset [=] utf8;