建库：
drop databases if exists tmall_ssm;
create database tmall_ssm default character set utf8;
建表：
在建表过程中，会设置外键约束，所以表和表之间有依赖关系。 因此会先建立被外键指向的表，比如User,Category，然后再是其他表
用户表
| 1 2 3 4 5 6 |
|
分类表
| 1 2 3 4 5 |
|
属性表
从这个表开始， 就有外键约束了。（一对多关系）
本表的外键cid，指向分类表的id字段
代码比较复制代码
| 1 2 3 4 5 6 7 |
|
增删改查
增
insert into 语句
insert into table_name values (值1, 值2,....)；
insert into table_name (列1, 列2,...) values (值1, 值2,....)我们也可以指定所要插入数据的列：
insert into table_name (列1, 列2,...) values (值1, 值2,....)插入表中数据到新表
insert into table_name (列1, 列2) vakues (select 列1, 列2 from table_name2)
删
DELETE 语句用于删除表中的行。
语法
delete from 表名称 WHERE 列名称 = 值
改
Update 语句用于修改表中的数据。 语法：
UPDATE 表名称 SET 列名称 = 新值,列名称 = 新值 WHERE 列名称 = 某值
字段：
truncate table jkdsj:t_tbp_user_bak
drop table 表名 删除整个表可以用
delete from 表名 删除所有数据
delete from 表名 where 列名 = 值 删除所有指定数据
添加字段：
alter table 表名 add 字段名 char(200) type not null default 0
GBase 添加时间字段类型，DATETIME YEAR TO FRACTION(3) 精确到毫秒3位
ALTER TABLE `表名` ADD COLUMN `字段名` varchar(100) NULL COMMENT '字段描述' AFTER `前一个字段名`;
删除字段：
alter table table_NAME drop 列名
修改字段名称：
alter table 表名 rename column A to B
修改字段类型：
alter table table_name alter 列名
修改字段默认值：
alter table 表名 add default (0) for 字段名 with values
如果字段有默认值，则需要先删除字段的约束，在添加新的默认值，
select c.name from sysconstraints a
inner join syscolumns b on a.colid=b.colid
inner join sysobjects c on a.constid=c.id
where a.id=object_id('表名')
and b.name='字段名'
根据约束名称删除约束
alter table 表名 drop constraint 约束名
根据表名向字段中增加新的默认值
alter table 表名 add default (0) for 字段名 with values
总结：
对数据的操作：有from
对表的操作：直接table，没有from
字段的注释：
建表之后添加字段的注释：
COMMENT on column jkdsj:JKFX.T_JKSDXS_YCNOCHANGE_B.EQUIP_ID is '设备ID'; --绕组id
8. < == < > == >
添加索引
1.添加主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)
2.添加唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`)
3.添加全文索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`)
4.添加普通索引
ALTER TABLE `table_name` ADD INDEX index_name (`column` )
5.添加组合索引
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)函数 ：
1. nvl（E1, E2）
如果E1为null，则返回E2，否则返回E1本身。
但此函数有一定局限，所以就有了NVL2函数。Oracle在NVL函数的功能上扩展，提供了NVL2函数。
NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。（支持GBase）
2. case when (注意else)
select *,(case when STATUS==0 then '退出' when STATUS==1 then '退出' else '投入' END)
简单case函数：CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END
case搜索函数：CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END
Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
比如说，下面这段SQL，你永远无法得到“第二类”这个结果
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
WHEN col_1 IN ('a') THEN '第二类' ELSE'其他' END
else：在Case函数中Else部分的默认值是NULL，这点是需要注意的地方。
一、分组：用一个SQL语句完成不同条件的分组。
有如下数据
按照国家和性别进行分组，得出结果如下
普通情况下，用UNION也可以实现用一条语句进行查询。但是那样增加消耗(两个Select部分)，而且SQL语句会比较长。下面是一个是用Case函数来完成这个功能的例子
SELECT country,
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END), --男性人口
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) --女性人口
FROM Table_A GROUP BY country;
二、变更主键 比如说变更主键这种累活。
一般情况下，要想把两条数据的Primary key,a和b交换，需要经过临时存储，拷贝，读回数据的三个过程，要是使用Case函数的话，一切都变得简单多了。
p_key col_1 col_2 a 1 张三 b 2 李四 c 3 王五 假设有如上数据，需要把主键
a和b相互交换。用Case函数来实现的话，代码如下UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a' THEN 'b'
WHEN p_key = 'b' THEN 'a' ELSE p_key END
WHERE p_key IN ('a', 'b');
同样的也可以交换两个Unique key。需要注意的是，如果有需要交换主键的情况发生，多半是当初对这个表的设计进行得不够到位，建议检查表的设计是否妥当。
三、in / exists
有两个表，tbl_A,tbl_B，两个表中都有keyCol列。现在我们对两个表进行比较，tbl_A中的keyCol列的数据如果在tbl_B的keyCol列的数据中可以找到，返回结果'Matched',如果没有找到，返回结果'Unmatched'。
要实现下面这个功能，可以使用下面两条语句--使用IN的时候
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B ) THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
--使用EXISTS的时候
SELECT keyCol,
CASE WHEN EXISTS ( SELECT * FROM tbl_B WHERE tbl_A.keyCol = tbl_B.keyCol ) THEN 'Matched' ELSE 'Unmatched' END Label
FROM tbl_A;
使用IN和EXISTS的结果是相同的。也可以使用NOT IN和NOT EXISTS，但是这个时候要注意NULL的情况。
四、计算一个班男生，女生人数：
用select case when写法如下：
SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex = 2 THEN 1
ELSE NULL
END) 女生数
FROM studentsGROUP BY grade;
3. decode（）
DECODE(dw.optype,1,'升档',0,'降档') as MESSAGE_CONTEN
4. to_date(?,'yyyy-mm-dd')
where create_date>=to_date(?,'yyyy-mm-dd') and create_date<to_date(?,'yyyy-mm-dd') -- 判断时间
select write_time from zj_ty:ZJ_CON_OBJECT_H_LOGRECODE_2018 where write_time>=to_date('20180501','YYYYmmdd')
5. round() 四舍五入
ROUND 函数用于把数值字段舍入为指定的小数位数。
SELECT ROUND(column_name,decimals) FROM table_name
| 参数 | 描述 |
|---|---|
| column_name | 必需。要舍入的字段。 |
| decimals | 必需。规定要返回的小数位数。 |
select station_name,round((c/equip_count)*100,1)||'%' bbhzb from xx
6.row_number()over()
row_number() over (partition by equip_id order by 列 desc) :先把列分组降序排序，再以降序以后的每组记录返回一个序号
select *,row_number() over(partition by equip_id order by occur_time desc)as nums from 表;
8. instr()
instr(content,"包含内容")>0
select * from table1 where name like '%中文%'
select * from yourTable where charindex('中文',name) > 0
insrt速度比like块
9. convert()
10. substr
substr(content,25,length(content)) contenttimestampdiff(minute,occur_time,confirm_time) as minutesunion all语法
1. limit 与 随机抽取
在mysql中查询5条不重复的数据，使用以下：
SELECT * FROM `table` ORDER BY RAND() LIMIT 5
但是真正测试一下才发现这样效率非常低。一个15万余条的库，查询5条数据，居然要8秒以上
搜索Google，网上基本上都是查询max(id) * rand()来随机获取数据。
SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 5;但是这样会产生连续的5条记录。解决办法只能是每次查询一条，查询5次。即便如此也值得，因
为15万条的表，查询只需要0.01秒不到。上面的语句采用的是JOIN，mysql的论坛上有人使用
SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1;需要0.5秒，速度也不错，但是跟上面的语句还是有很大差距。总觉有什么地方不正常。于是我把语句改写了一下。
SELECT * FROM `table` WHERE id >= (SELECT floor(RAND() * (SELECT MAX(id) FROM `table`))) ORDER BY id LIMIT 1;这下，效率又提高了，查询时间只有0.01秒
最后，再把语句完善一下，加上MIN(id)的判断。我在最开始测试的时候，就是因为没有加上MIN(id)的判断，结果有一半的时间总是查询到表中的前面几行。
完整查询语句是：
SELECT * FROM `table` WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`))) ORDER BY id LIMIT 1;SELECT * FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id LIMIT 1;最后对这两个语句进行分别查询10次，
前者花费时间 0.147433 秒
后者花费时间 0.015130 秒
看来采用JOIN的语法比直接在WHERE中使用函数效率还要高很多。
2. 拼接 ||
select *, round((c/equip_count)*100,1)||'%' as 百分比 from ..
3. 递归查询，父节点以下（包括父节点）的数据
递归抽取：
select * from zj_ty.SG_ORG_DCC_B START WITH ID='0021330000' connect BY NOCYCLE prior id=parent_id order by org_type,level_1,id
id=parent_id 查询父节点下面的数据
parent_id=id 查询父节点上面的数据
4. 新增一列固定字段
select time,'开关变位' as sign_type,sig_name from sg_dev_breaker_h_signalchange
5. LEFT()、RIGHT()、SUBSTRING() LOCATE(substr , str )
6. 类型转换
转换成数字：cast(st.top_ac_voltage_type as decimal)
转换成字符串：vol.code||''
7. join
left join
right join
全连接(full join)
12. substr（）
SQL 中的 substring 函数是用来抓出一个栏位资料中的其中一部分。这个函数的名称在不同的资料库中不完全一样：
- MySQL: SUBSTR( ), SUBSTRING( )
- Oracle: SUBSTR( )
- SQL Server: SUBSTRING( )
最常用到的方式如下 (在这里我们用 SUBSTR( ) 为例)：
SUBSTR (str, pos) 第几个开始
由 <str> 中，选出所有从第 <pos> 位置开始的字元。请注意，这个语法不适用于 SQL Server 上。
SUBSTR (str, pos, len) 第几个开始数，共len个
由 <str> 中的第 <pos> 位置开始，选出接下去的 <len> 个字元。
约束
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
