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'.
来源: http://www.bubuko.com/infodetail-2813298.html