（my）sql 入门基础

#由于本人对oracle数据库比较熟悉，所以这里只记录和oracle数据库不一样的用法：


1、DDL语句
##创建数据库
mysql> create database test1;


##查看所有数据库
mysql> show databases;


##链接数据库
mysql> use test1;


##删除数据库
mysql> drop database test1;


##查看所有表
mysql> show tables;


##显示表定义
mysql> desc emp;


##显示建表语句
mysql> show create table emp \G


##修改表字段
##add、modify、drop column其它的用法都和oracle一模一样。下面只介绍不一样的地方：
##change和modify都可以修改表的定义，不同是change后面需要写两次列名，不方便。但是change的优点是可以修改列名称，modify则不能。
##可选项first|alter col_name，这个选项可以用来修改字段在表中的位置，add增加的新字段默认是加在表的最后位置，而change/modify默认都不会改变字段的位置。
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name];
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];
ALTER TABLE tablename DROP [COLUMN] col_name;


##数值不够的空间用“0”填满
alter table emp modify deptno int(6) zerofill;


##更改表名
mysql> alter table emp rename to test;


2、DML语句
##一次性插入多条记录
insert into emp
values
  ('aa', '2015-11-23', 10.23, 1),
  ('bb', '2015-11-23', 10.23, 2),
  ('cc', '2015-11-23', 10.23, 3);


##一次更新多个表中数据
update emp a, dept b
   set a.sal = a.sal * 100, b.deptname = 'XXX'
 where a.deptno = b.deptno;


##一次删除多个表的数据
delete a, b from emp a, dept b
 where a.deptno = b.deptno
   and a.ename = 'aa';


##分页查询
##显示第三条记录开始的3条记录。
select * from emp a order by a.deptno asc,a.sal desc limit 2,3;


3、DCL语句
##创建一个数据库用户z1，具有对test数据库中所有表的SELECT/INSERT权限：
grant select,insert on test.* to 'z1'@'localhost' identified by '123';


#收回z1用户的INSERT权限：
revoke insert on test.* from 'z1'@'localhost';


4、事务控制和锁定语句
4.1 LOCK TABLE 和UNLOCK TABLE
MySQL 支持对MyISAM 和MEMORY 存储引擎的表进行表级锁定，对BDB 存储引擎的表进行页级锁定，对InnoDB 存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有的情况下，用户需要明确地进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制和锁定语句来完成。


LOCK TABLES 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止。
UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES 时，或当与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁，具体语法如下：
mysql> ? lock
Name: 'LOCK'
Description:
Syntax:
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...


lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE


UNLOCK TABLES


4.2 事务控制
MySQL 通过SET AUTOCOMMIT、START TRANSACTION、COMMIT 和ROLLBACK 等语句支持本地事务，具体语法如下：
mysql> ? COMMIT
Name: 'START TRANSACTION'
Description:
Syntax:
START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}


默认情况下，MySQL 是自动提交（Autocommit）的，如果需要通过明确的Commit 和Rollback 来提交和回滚事务，那么需要通过明确的事务控制命令来开始事务，这是和Oracle的事务管理明显不同的地方。如果应用是从Oracle 数据库迁移到MySQL 数据库，则需要确保应用中是否对事务进行了明确的管理。
 START TRANSACTION 或BEGIN 语句可以开始一项新的事务。
 COMMIT 和ROLLBACK 用来提交或者回滚事务。
 CHAIN 和RELEASE 子句分别用来定义在事务提交或者回滚之后的操作，CHAIN 会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接。
 SET AUTOCOMMIT 可以修改当前连接的提交方式，如果设置了SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。


如果在锁表期间，用start transaction 命令开始一个新事务，会造成一个隐含的unlocktables 被执行。因此，在同一个事务中，最好不使用不同存储引擎的表，否则ROLLBACK 时需要对非事务类型的表进行特别的处理，因为COMMIT、ROLLBACK 只能对事务类型的表进行提交和回滚。


在事务中可以通过定义SAVEPOINT，指定回滚事务的一个部分，但是不能指定提交事务的一个部分。对于复杂的应用，可以定义多个不同的SAVEPOINT，满足不同的条件时，回滚不同的SAVEPOINT。需要注意的是，如果定义了相同名字的SAVEPOINT，则后面定义的SAVEPOINT 会覆盖之前的定义。对于不再需要使用的SAVEPOINT，可以通过RELEASESAVEPOINT 命令删除SAVEPOINT，删除后的SAVEPOINT，不能再执行ROLLBACK TO SAVEPOINT命令。


4.3 分布式事务的使用
MySQL 从5.0.3 开始支持分布式事务，当前分布式事务只支持InnoDB 存储引擎。一个分布式事务会涉及多个行动，这些行动本身是事务性的。所有行动都必须一起成功完成，或者一起被回滚。


5、查看帮助文档
##安装层次看帮助
mysql> ? contents


##快速查阅语法
mysql> ? crate table
mysql> ? select
mysql> ? insert


6、MySQL 中的常用字符串函数
6.1 字符串函数
CANCAT(S1,S2,…Sn) 连接S1,S2,…Sn 为一个字符串
select concat('aaa','bbb','ccc') ,concat('aaa',null);


INSERT(str,x,y,instr) 将字符串str 从第x 位置开始，y 个字符长的子串替换为字符串instr
select INSERT('beijing2008you',12,3, 'me') ;


LOWER(str) 将字符串str 中所有字符变为小写
UPPER(str) 将字符串str 中所有字符变为大写
select LOWER('BEIJING2008'), UPPER('beijing2008');


LEFT(str ,x) 返回字符串str 最左边的x 个字符
RIGHT(str,x) 返回字符串str 最右边的x 个字符
SELECT LEFT('beijing2008',7),LEFT('beijing',null),RIGHT('beijing2008',4);


LPAD(str,n ,pad) 用字符串pad 对str 最左边进行填充，直到长度为n 个字符长度
RPAD(str,n,pad) 用字符串pad 对str 最右边进行填充，直到长度为n 个字符长度
select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');


LTRIM(str) 去掉字符串str 左侧的空格
RTRIM(str) 去掉字符串str 行尾的空格
select ltrim(' |beijing'),rtrim('beijing| ');


REPEAT(str,x) 返回str 重复x 次的结果
select repeat('mysql ',3);


REPLACE(str,a,b) 用字符串b 替换字符串str 中所有出现的字符串a
select replace('beijing_2010','_2010','2008');


STRCMP(s1,s2) 比较字符串s1 和s2
select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');


TRIM(str) 去掉字符串行尾和行头的空格
select trim(' $ beijing2008 $ ');


SUBSTRING(str,x,y) 返回从字符串str x 位置起y 个字符长度的字串
select substring('beijing2008',8,4),substring('beijing2008',1,7);


6.2 数值函数
ABS(x) 返回x 的绝对值
select ABS(-0.8) ,ABS(0.8);


CEIL(x) 返回大于x 的最大整数值
select CEIL(-0.8),CEIL(0.8);


FLOOR(x) 返回小于x 的最大整数值
select FLOOR(-0.8), FLOOR(0.8);


MOD(x，y) 返回x/y 的模
select MOD(15,10),MOD(1,11),MOD(NULL,10);


RAND() 返回0 到1 内的随机值
select RAND(),RAND();
select ceil(100*rand()),ceil(100*rand());


ROUND(x,y) 返回参数x 的四舍五入的有y 位小数的值
TRUNCATE(x,y) 返回数字x 截断为y 位小数的结果
##注意TRUNCATE 和ROUND 的区别在于TRUNCATE 仅仅是截断，而不进行四舍五入。
select ROUND(1.1),ROUND(1.1,2),ROUND(1,2);
select ROUND(1.235,2),TRUNCATE(1.235,2);


6.3 日期和时间函数
CURDATE() 返回当前日期
select CURDATE();


CURTIME() 返回当前时间
select CURTIME();


NOW() 返回当前的日期和时间
select NOW();


UNIX_TIMESTAMP(date) 返回日期date 的UNIX 时间戳
select UNIX_TIMESTAMP(now());


FROM_UNIXTIME() 返回UNIX 时间戳的日期值
select FROM_UNIXTIME(1184134516) ;


WEEK(date) 返回日期date 为一年中的第几周
YEAR(date) 返回日期date 的年份
select WEEK(now()),YEAR(now());


HOUR(time) 返回time 的小时值
MINUTE(time) 返回time 的分钟值
select HOUR(CURTIME()),MINUTE(CURTIME());


MONTHNAME(date) 返回date 的英文月份名称
select MONTHNAME(now());


DATE_FORMAT(date,fmt) 返回按字符串fmt 格式化日期date 值
%S,%s 两位数字形式的秒（00,01,...,59）
%i 两位数字形式的分（00,01,...,59）
%H 两位数字形式的小时，24 小时（00,01,...,23）
%h,%I 两位数字形式的小时，12 小时（01,02,...,12）
%k 数字形式的小时，24 小时（0,1,...,23）
%l 数字形式的小时，12 小时（1,2,...,12）
%T 24 小时的时间形式（hh:mm:ss）
%r 12 小时的时间形式（hh:mm:ssAM 或hh:mm:ssPM）
%p AM 或PM
%W 一周中每一天的名称（Sunday,Monday,...,Saturday）
%a 一周中每一天名称的缩写（Sun,Mon,...,Sat）
%d 两位数字表示月中的天数（00,01,...,31）
%e 数字形式表示月中的天数（1,2，...,31）
%D 英文后缀表示月中的天数（1st,2nd,3rd,...）
%w 以数字形式表示周中的天数（0=Sunday,1=Monday,...,6=Saturday）
%j 以3 位数字表示年中的天数（001,002,...,366）
%U 周（0,1,52），其中Sunday 为周中的第一天
%u 周（0,1,52），其中Monday 为周中的第一天
%M 月名（January,February,...,December）
%b 缩写的月名（January,February,...,December）
%m 两位数字表示的月份（01,02,...,12）
%c 数字表示的月份（1,2,...,12）
%Y 4 位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”
select DATE_FORMAT(now(),'%M,%D,%Y');


DATE_ADD(date,INTERVAL expr type) 返回一个日期或时间值加上一个时间间隔的时间值
HOUR 小时 hh
MINUTE 分 mm
SECOND 秒 ss
YEAR 年 YY
MONTH 月 MM
DAY 日 DD
YEAR_MONTH 年和月 YY-MM
DAY_HOUR 日和小时 DD hh
DAY_MINUTE 日和分钟 DD hh:mm
DAY_ SECOND 日和秒 DD hh:mm:ss
HOUR_MINUTE 小时和分 hh:mm
HOUR_SECOND 小时和秒 hh:ss
MINUTE_SECOND 分钟和秒 mm:ss
select now() current,date_add(now(),INTERVAL 31 day) after31days,date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;
select now() current,date_add(now(),INTERVAL -31 day) after31days,date_add(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;


DATEDIFF(expr,expr2) 返回起始时间expr 和结束时间expr2 之间的天数
select DATEDIFF('2008-08-08',now());


6.4 流程函数
IF(value,t f) 如果value 是真，返回t；否则返回f
select if(salary>2000,'high','low') from salary;


IFNULL(value1,value2) 如果value1 不为空返回value1，否则返回value2
select ifnull(salary,0) from salary;


CASE WHEN [value1] THEN[result1]…ELSE[default]END 如果value1 是真，返回result1，否则返回default
select case when salary<=2000 then 'low' else 'high' end from salary;


CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END 如果expr 等于value1，返回result1，否则返回default
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;


6.5 其他常用函数
DATABASE() 返回当前数据库名
select DATABASE();


VERSION() 返回当前数据库版本
select VERSION();


USER() 返回当前登录用户名
select USER();


INET_ATON(IP) 返回IP 地址的数字表示
select INET_ATON('192.168.1.1');


INET_NTOA(num) 返回数字代表的IP 地址
select INET_NTOA(3232235777);
select * from t where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip)<=inet_aton('192.168.1.20');


PASSWORD(str) 返回字符串str 的加密版本
select PASSWORD('123456');


MD5() 返回字符串str 的MD5 值
select MD5('123456');