SQL规范
以 “ ; ” 结尾；关键字之间要有空格(可以由多个空格)；SQL语句中可以一个或多个换行；关键字不区分大小写。
数据库相关SQL
查询所有数库库:
show databases;
创建数据库:
create database 数据库名称;
查看单个数据库详情
show create database 数据库名称;
删除数据库
drop database 数据库名称;
创建数据库时制定字符集
create database 数据库名称 character set gbk/utf8;
使用数据库
use 数据库名称;
表相关SQL
什么是表：在关系型数据中保存数据库的单元，类似于Excel中表，创建表的时候需要指定表的字段信息。
创建表
create table 表名 (字段名 字段类型,字段名 字段类型...);
查看所有表
show tables;
查看单个表信息
show create table 表名;
删除表
drop table 表名;
创建表指定字符集和引擎
charset engine create table 表名 (字段名 字段类型,字段名 字段类型) engine=myisam/InnoDB charset=gbk/utf8;(InnoDB：支持数据库的感激操作，如事务、外键等；MyIsam:只支持数据基础的增删改查)
查看表字段信息
desc 表名;
修改表相关SQL
修改表名
rename table 原名 to 新名;
修改表的引擎和字符集
alter table 表名 engine=innodb/myisam charset=utf8/gbk;
添加表字段(在表的最后位置添加)
alter table 表名 add chinese int;
在表的最前面添加
alter table 表名 add chinese int first;
在某个字段的后面添加
alter table 表名 add 新字段名 新字段表名 after 老字段名;
删除表字段
alter table 表名 drop 字段名;
修改表字段的名称和类型
alter table 表名 change 老字段表明 新字段表名 字段类型;
修改字段类型和字段的位置
alter table 表名 modity 字段名 新类型 first/after XXX;
表的增删改查
全表插入格式
insert into 表名 values (数据1,数据2,数据3);
指定字段插入
insert into 表名 (字段名1,字段名2...) values (数据1,数据2);
批量插入数据格式
insert into 表名 values (数据1,数据2,数据3),(数据1,数据2,数据3);
指定字段批量格式
insert into 表名 (字段名1，字段名2) values ((数据1,数据2),(数据1,数据2);
所有字段查询
select * from 表名;
指定字段查询
select 字段名,字段名 from 表名;
条件查询格式
select * from 表名 where 条件;
修改数据
update 表名 set 字段名=新的数据 where id=位置;
删除数据
delete from 表名 (表内所有的数据) where 条件;
去重(distinct)
select distinct XX from 表名;
编码问题
在连接数据库后执行set names gbk;把数据库的解码字符集修改成gbk。
主键约束
主键：表示表中数据唯一性的字段称为主键。
约束：约束就是创建表的时候给表中字段添加的限制条件。
主键约束：添加了主键约束的字段，保存的数值必须唯一而且非空
create table 表名 (字段名 类型 primary key,字段名 类型);
主键自增约束
create table 表名 (字段名 类型 primary key auto_increment,字段名 类型);
自增的特点
值为null时会自动赋值；以表中曾出现的最大值基础上+1；删除数据自增数值不减少；delete清空表，自增数值不变。
如何让自增数值清零
使用truncate关键字，相当于drop+create。
truncate table 表名;
注释
创建表的时候可以通过 comment 关键字对字段进行描述
create table 表名 (字段名 类型 primary key auto_increment comment'这个字段是主键',name varcher(20) comment '姓名');
` 和 ' 的区别
`的作用是用来修饰表名和字段名(可以省略)；
create table `表名` (id int ,name varchar(20));
' '是作用是用来修饰字符串。
数据冗余
由于表设计不够合理，出现大量的重复数据，成为冗余数据；可以通过拆分表的形式，把可能重复的数据保存到一张新的表中，在原表中只需要通过一个id建立关系即可，这种建立关系的字段称之为外键；
create table T1(id int primary key auto_increment,name varchar(20));
create table T2(id int primary key auto_increment,name varchar(20),T1_id int (上级分类));
事务
事务是数据库中执行SQL语句的最小工作单位，把多条SQL语句放在事务中可以保证多条SQL要么全部成功，要么全部失败。
查看数据库的提交状态
show variables like '%autocommit%';
关闭/打开自动提交
set autocommit = 0/1;
事务回滚
rollback；此关键字会把数据库内存中的数据回滚到上一次commit的点；
savepoint 设置回滚点
savepoint 标识(随便起);
指定的回滚到某个回滚点
rollback to 标识;
事务的应用场景
当执行某一件事需要多行SQL语句时，并且要求多行SQL要么全部成功，要么全部失败，此时必须使用事务，不然就会出现多行SQL部分成功部分失败的情况。
SQL分类
DDL: (Deta Definition Language)数据定义语言，包括create、drop、alter、truncate；不支持事务。
DML: (Deta Manipulation Language)数据操作语言，包括insert、delete、update、select(DQL)；支持事务。
DQL: (Data Query Language)数据查询语言，只包括select；不支持事务。
TCL: (transaction Control Language)事务控制语言，包括commit、rollback、savepoint、rollback to等。
DCL: (Data Control Language)数据控制语言；分配用户权限相关的SQL。
数据类型
整数:
int(m)：四个字节，和JAVA中的int一致；
bigint(m)：八个字节，和JAVA中的long一致；
m代表显示长度，但是必须和zerofill关键字结合使用 create table 表名(num int(10) zerofill);
浮点数类型
double(m,d)：m代表总长度，d代表小数点后面的长度；
decimal(m,d)：超高精度小数，需要涉及高精度运算时使用;
字符串类型
char(m)：长度不可变，执行效率高，最大长度255；
varchar(m)：长度可变，节省空间，最大长度65535(超255建议使用text)；
text：长度可变，通常保存长度大于255的文本数据，最大值65535；
日期类型
date：只保存年月日；
time：只保存时分秒；
datetime：年月日时分秒，默认值为null，最大值9999-12-31；
timestamp(时间戳)：年月日时分秒，默认为当前时间，最大值为2038-01-19。
别名
查询表中所有的字段名 并把原字段名改成XXX 以下有三种方法：
select 原字段名 as '别名' from 表名
select 原字段名 '别名' from 表名
select 原字段名 别名 from 表名
and or in
and 和 java中的&&效果一样
or 和 java中的||效果一样
select * from 表名 where sal=1500 and deptno=10;
select * from 表名 where sal=1500 or sal=3000 or sal=5000;
select * from 表名 where sal in (5000,1500,3000);
between X and Y 查询X和Y之间的信息 (包括X和Y)
select * from 表名 where job between X and Y;
查询
用于模糊查询
_:代表单个未知字符 包含字符a： _a%
%：代表0个或多个位置字符 以a开头： a%；以a结尾： %a
不包含XXX
select * from 表名 where 字段名 not like '%啪啪%';
包含XXX
select * from 表名 where 字段名 like '%啪啪%';
排序
order by 写在 where 的后面；没有 where && limit 写在后面；
order by 后面写排序的字段名，可以写一个或多个，默认排序规则为升序，也可以指定规则：升序(asc) 降序(desc)。
分页查询(limit)
limit X,Y ：x代表跳过的条数；y代表查询的条数(每页的条数)。
案例：查询第n页没页m条数据 limit (n-1)*m,m;
日期及相关函数
select 'Hello World';
now() 获取当前日期+时间 select now();
获取当前日期 select curdate();
获取当前时间 select curtime();
从年月日时分秒提取年月日 和提取时分秒 select date(now()); select time(now());
从年月日时分秒中提取年，月，日，时，分，秒
select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());
日期的格式化 格式：date_format(日期,格式)；
%Y 代表四位年 %y 两位年
%m 两位月 %c 一位月
%d 日
%H 24小时 %h 12小时
%i 分 %s 秒
改变格式
select date_format(now(),'%Y年%m月%d日 %H点%i分%s秒');
把非标准格式的时间转成标准格式
str_to_date(非标准格式的时间,格式);
转成标准格式
select str_to_date ('15.08.2016 08:00:00','%d.%m.%Y %H:%i:%s');
ifnull函数
age=ifnull(x,y) 如果x的值为null 则 age=y, x不为null则age=x
update emp set comm=ifnull(comm,0);
聚合函数
对多条数据进行统计查询
求平均值 avg(字段名) select avg(字段名) from 表名;
最大值 max(字段名) select max(字段名) from 表名;
最小值 min(字段名) select min(字段名) from 表名;
总和 sum(字段名) select sum(字段名) from 表名;
查询符合条件的条数 select count(*) from 表名 where 字段名=XX;
字符串相关函数
字符串拼接 concat(s1,s2) 结果为 s1s2
select 字段名,concat(字段名,拼接数据) from 表名;
获取字符串的长度 char_length(字段名)
select 字段名,char_length(字段名) from 表名;
获取字符串在另一个字符串中出现的位置
格式一：instr(str,substr); select instr('asdfg','d')； //3
格式二：locate(substr,str); select locate('d','asdfg'); //3
转换大小写
select upper('abc');select lower('NBA');
去两端空白
字符串中间的空白不能去 select trim(' ab c ');
截取字符串
从左边截取： select left('abcdefg',2);
从右边截取： select right('abcdefg',3);
从某个位置截取： select substring('abcdefg',2,3);//从第二个数据开始截取三个数据
重复
select repeat('ab',3);
反转
select reverse('abc');
替换
select replace('abcdefg','c','m');
插入
select insert('abcdefg',3,2,'m');//把从第三个位置开始的两个数据替换成‘m’。
内容来源于网络如有侵权请私信删除
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
