MySQL命令整理

注：下文中的$:仅仅是一行的开始，不是命令的一部分（虽然这是一句废话）

1.可以使用多种方式连接上数据库

2.在进行数据库操作之前，需要先选择数据库，这个在命令行需要单独输入use＊命令，但是在java中，在第一步中已经选择好数据库了

3.Mysql命令集合

$: show databases;

$: show tables;

$: show columns from myTable;

$: desc myTable;

$: select col1 from myTable;

$: SQL语句不区分大小写，通常的写法是将SQL的关键字大写，其他标识符小写，忽略本文的大小写习惯

$: select col1,col2 from myTable;

$: select * from myTable;

$: select myTable.col1 from myDatabase.myTable;

$: select col1,col2,col3 from MyTable order by col1,col2;	Order by应放到SQL结果处理语句的最后
$: select col1 from myTable where id > 0;	where过滤行

$: SQL语句中，所有非数字型字面值需要使用单引号扩起来

$: select * from myTable where id > 0 and name is not null;

and优先级高于or，可使用括号避免优先级混乱

4.Mysql进阶语句

$: select distinct col1,col2 from myTable;	distinct应用于所有列
$: select col1 from myTable limit 5;	从行0开始的5行
$: select col1 from myTable limit 5,3;	从行5开始的3行
$: select col1 from myTable limit 5 offset 3;	从行3开始的5行
$: select col1,col2 from myTable order by col1 desc,col2 asc;	desc降序，默认asc
$: select col1 from myTable where id between 5 and 10;	优于>and<复合语句
$: select col1 from myTable where name is null;	空值检验
$: select col1 from myTable where name is not null;	非空检验
$: update myTable set col1 = null where name is null;	设置空值，不同于0或 ''
$: select col1 from myTable where id in(exVal1,exVal2);	In子句，通常结合sql子句使用
$: select col1 from myTable where id not in(exVal1,exVal2);	Not in子句
$: select col1 from myTable where name like 'James%';	like子句和多字符通配符％
$: select col1 from myTable where name like 'j_ke';	like子句和单字符通配符_

$:使用Mysql的正则表达式

$: select * from myTable where name regexp 'jack';	name中含有jack行,regexp行内匹配，like整行匹配
$: select * from myTable where name regexp 'j.ck';	.匹配任意一个字符
$: select * from myTable where name regexp 'jack|jerk';	or匹配
$: select * from myTable where name regexp '[123] to';	匹配多个字符中的一个
$: select * from myTable where name regexp '[^123]to';	字符排除匹配
$: select * from myTable where name regexp '[1-3a-z]t';	匹配字符范围
$: select * from myTable where name regexp '\\.';	匹配特定字符
$: select * from myTable where name regexp '[123]{2,}t';	*,+,?,{n},{m,},{m,n}多匹配
$: select * from myTable where name regexp '^jack$';	定位元字符，^位于集合内部表示为否定集合，位于此表示字符串的开始，$表示结束

$:创建计算字段

$: select concat(firstName,' ',lastName) as name  from myTable;	concat函数链接字符串,不支持＋或 ||
$: select trim(firstName) from myTable;	还支持ltrim和rtirm函数
$: select price*num as accounts from myTable;	支持数值计算,as重命名

$:文本处理函数（以下所有表格均为部分函数）

$: upper()	大写转换
$: left()	返回最左字符
$: right()	返回最右字符
$: length()	返回字符串长度
$: lower()	小写转换
$: subString()	返回子串
$: locate()	找出子串

$:时间处理函数

$: now()	当前日期和时间
$: curDate()	当前日期
$: curTime()	当前时间
$: date()	返回一个日期时间的日期部分（数值）
$: time()	返回一个日期时间的时间部分（数值）
$: day()	返回一个日期时间的天部分（数值）
$: dayOfWeek()	返回一个日期时间的星期（数值）
$:hour()	返回一个日期时间的小时（数值）
$: minute()	返回一个日期时间的分钟（数值）
$: month()	返回一个日期时间的月份（数值）
$: second()	返回一个日期时间的秒数（数值）
$: year()	返回一个日期时间的年份（数值）
$: select * from myTable where date(tradeDate)='2016-01-02';	date的文本格式为yyyy-mm-dd
$: select * from myTable where time(tradeDate)='11:20:10';	time的文本格式为hh-mm-ss

$:数值处理函数

$: abs()	绝对值
$: cos()	余弦
$: exp()	指数值
$: pi()	返回圆周率
$: rand()	返回一个随机数
$: tan()	正切值
$: sin()	正弦值
$: sqrt()	平方根
$: mod()	取余值

$: 汇总处理函数，可结合distinct关键字只计算不重复列，而此时distinct就做用于个别列

$: avg()	取平均
$: count()	行数
$: max()	最大值，支持文本列
$: min()	最小值，支持文本列
$: sum()	求和

$:数据分组

$: select groupId, count(*) as accs from myTable having  accs > 0;	分组伴随汇总函数，having过滤分组
$: group by,order by是中间结果处理语句，放到中间结果后面	limit为结果处理语句，放到最后

$:创建表联结，可用复合查询解决，但使用联结语法更简洁

$: select * from tab1,tab2 where tab1.col2=tab2.col3;	返回表笛卡尔积的where条件子集
$: select * from tab1,tab2,tab3;	返回表笛卡尔积的行集合
$: select * from tab1 inner join tab2 on tab1.col2=tab2.col3;	内部联结（等值联结/叉联结）
$: select * from tab1 t1,tab1,t2 where t1.col1=t2.col1 and t1.col2='jack';	自联结
$: select * from tab1 left outer join tab2 on tab1.col1 = tab2.col2;	外部联结
$:使用外部联结需要指明左右，以指明要输出所有列的表	无匹配行的其他列为null值

$:使用Union组合结果，可用复合查询代替

$:注意所有被组合的查询结果的栏目列表必须相同

使用order by的话要放倒最后，只能有一个order by

$:使用全文索引

$:对中文的支持需要插件

也可以使用mysql＋lucene的做法实现

$: Insert进阶

$:利用批处理优化巨量数据插入到（远程）数据库	另外可以利用preparedstatement优化
$:插入检索出的数据即 insert | select	使用的是列位置对应填充

$:修改表结构

$: alter myTable add newCol varchar(10);	添加新列
$: alter myTable drop column certainCol;	删除某列
$: alter myTable add constraint csName foreign key(col1) references myTable2(col2);	添加外键
$: rename table tab1 to tab2;	表重命名

$:使用视图

$:视图并不是物理存在，使用视图的目的是为了查询结果重用，但是每次使用视图的时候都要重新查询

$: create view viewName asselect子句	创建视图
$: drop view viewName;	删除视图
$: create or replace view viewName as select子句	更新视图

$:事务支持

$:可以使用mysql内置的事务支持，但是其灵活度不高，而且实际直接用的时候也不多

$:java的语句执行默认是一句一个事务，之后默认自动提交。可以先关闭自动提交，然后手动提交和回滚，就是实现事务了，其内部实现用的就是mysql内置的事务支持

$:用户管理

$: create user userName@hostIP identified by yourPwd;	创建用户
$: grant villageName on dataBase.tableName to userdName;	可使用＊通配数据库和数据库表，赋权
$: show grants for userName;	查看用户的权限
$: revoke villageName on dataBase.tableName to userName;	可使用＊通配数据库和数据库表，去权
$: set password for userName = password(yourPwd);	重置密码

$:其他

1：mysql的innodb存储引擎支持全文搜索

2：mysql支持存储过程，存储过程即使用sql语句编写的函数

3：mysql支持游标，只不过游标仅仅支持在存储过程中使用

4：mysql支持触发器，触发器是在update，delete，insert语句执行前／后触发执行的mysql语句集合