Tag Archives: SQL

sql language

Error: MySQL 5.7 이상 Group by – this is incompatible with sql_mode=only_full_group_by

MySQL 5.7이상에서 Group By 쿼리 사용시 발생하는 오류

0 12 12:27:17 select tp1.idx, tp1.category, tp1.contents, tp1.view_yn, tp1.status, tp1.recordid, max(tp1.recordtime)
  from TBL_PAGE tp1 group by tp1.RECORDTIME
  LIMIT 0, 1000 Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'solarbridge_dev.tp1.IDX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.000 sec	Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 12 12:27:17 select tp1.idx, tp1.category, tp1.contents, tp1.view_yn, tp1.statu' at line 5	0.000 sec


Error Code: 1140. In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'wordpressdb_test.TC.IDX'; this is incompatible with sql_mode=only_full_group_by

 

Solution.1)
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Solution.2)
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

ANY_VALUE라고 싸줌.

Postgresql – ,로 구분된 키값으로 조인

다음 사이트들을 참고해서 해겷했다.
http://stackoverflow.com/questions/8584967/split-comma-separated-column-data-into-additional-columns
http://stackoverflow.com/questions/11730777/postgres-not-in-array
http://stackoverflow.com/questions/10738446/postgresql-select-rows-where-column-array
나머지는 진짜 참고만 했고 첫번째 링크에 솔루션이 있다.

테이블 두개…

CREATE TABLE alarm_info
(
  alarm_id bigserial PRIMARY KEY,
  alarm_name character varying(20),
  created_at timestamp without time zone DEFAULT now(),
  updated_at timestamp without time zone DEFAULT now(),
  description text,
  admin_ids character varying(255),-- 3,6,7
)

CREATE TABLE admin_info
(
  admin_id bigserial NOT NULL PRIMARY KEY,
  admin_username character varying NOT NULL,
  admin_email character varying NOT NULL,
  admin_password character varying,
  created_at timestamp with time zone NOT NULL,
)

admin_ids가 문제다. 배열이 아닌 문자열형태로 ,구분자로 저장…
m-n조인을 하는것도 좋지만… 편의를 위해서 저렇게 하는 경우가 자주 있다.

이런경우에 조인을 하려면…
설명 생략하고 테스트과정을 순차적으로 다 써놓는다.
나야 보면 생각나겠지만… 다른사람들도 순서대로 실행시켜보면 뭔지 알 수 있을거다. 아마…

SELECT	admin_ids
FROM	alarm_info

SELECT	regexp_split_to_array(admin_ids,',')
FROM	alarm_info

SELECT	cast(regexp_split_to_array(admin_ids,',') as integer[])
FROM	alarm_info

SELECT	cast(regexp_split_to_array(admin_ids,',') as integer[])
FROM	alarm_info

select (ARRAY[1, 2])

SELECT * FROM admin_info
WHERE	admin_id = ANY(3,4)


SELECT
  a.admin_id as id,
  a.admin_username as username,
  a.admin_email as email,
  a.admin_phone as phone,
  a.admin_name as name,
  n.subject as subject,
  n.message
FROM
(
SELECT
  cast(regexp_split_to_array(admin_ids,',') as bigint[]) as ids,
  alarm_name as subject,
  alarm_description as message
FROM
  alarm_info
) n, admin_info a
where
  a.admin_id = any(n.ids)

내용 변경했으니까 그냥 막 올려도 되겠지??? 아닌가…

Hibernate nativesql 사용시 발생하는 문제

Exception String : org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [coalesce] during auto-discovery of a native-sql query

환경 : Postgresql, Hibernate 4.x, Java7, Spring3.1 … etc

상황 : 복잡한 조인을 사용해서 DB의 데이터를 조회해야하는 상황..HQL은 익숙하지 않고 Criteria로 하기에는 불편한 상황이라서 native sql을 사용하려고 했는데….

pgadmin에서 돌렸을 때 아무 문제도 없는 상황인데 session.createSQLQuery(queryString)을 사용하면 문제가 발생했다.

 

처리 :

alias문제… coalesce 함수를 두번 사용했는데 두개가 동일한 alias name으로 인식된 것 같다. pagadmin에서는 문제가 없었다.  as iteam1, as item2와 같이 두개에 다른 이름을 부여하니 문제없이 정상 동작했다.

 

 

Access SQL query 샘플들

DateTime을 Date / Time으로 분리

SELECT 
  REGDATE,
  Int(REGDATE) AS ONLY_DATE,
  REGDATE-Int(REGDATE) AS ONLY_TIME
FROM 
  WeatherData;

http://webcheatsheet.com/SQL/access_functions/dateadd.php

http://www.techonthenet.com/access/functions/date/dateadd.php

DateAdd function

DateAdd( interval, number, date)

interval :
yyyy Year
q Quarter
m Month
y Day of the year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

date :
now(), regdate... etc

dateadd("n", 10, '2012-01-01 01:50')

 

Postgresql SQL update query – column value to another column

한컬럼의 값을 다른 컬럼으로 복사하기

데이터 이동 방향
dept_name = dept_code

 

create query

create table department(
  dept_id bigint,
  dept_code varchar(100),
  dept_name varchar(100)
)

update query

--가장쉬운방법
update department a
set dept_name = b.dept_code
from department b
where a.dept_id = b.dept_id
-- 조인을 이용
update department a
set dept_name = c.dept_code
from department b,
(
  select dept_id, dept_code from department
) c where a.dept_id = c.dept_id
-- 명시적인 inner join 이용... 은 잘 안된다.

 

한 테이블을 값을 이용해서 다른테이블 업데이트하기

셀렉트 쿼리처럼 해서 사용하면된다.

update user t1
set phone=t2.phone, address=t2.level
from 
	employee t2
where
	t1.email = t2.email

다른테이블의 값을 조인해서 다른 테이블 업데이트하기

update user t1
set phone=t2.phone, address=t2.level
from 
	employee t2,
	seller t3
where
	t1.email = t2.email and
	t2.email = t3.email

MySQL 데이터 용량확인 – 테이블, 데이터베이스

테이블 용량확인

SELECT
concat(table_schema,'.',{TABLE_NAME}),
concat(round(table_rows/1000000,2),'M') rows,
concat(round(data_length/(1024*1024*1024),2),'G') DATA,
concat(round(index_length/(1024*1024*1024),2),'G') idx,
concat(round((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(index_length/data_length,2) idxfrac
FROM information_schema.TABLES
where table_name = 'dfs_data' ;

데이터베이스 용량확인

SELECT table_schema "{DATABASE_NAME}",
SUM(data_length + index_length) / 1024 / 1024 "Size(MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

 

어디서 퍼왔는지는 까먹음

mysql date query 날짜 더하기빼기

SELECT DATE_SUB(NOW(), INTERVAL 30 day);

SELECT TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day));

SELECT UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 day));

 

MySQL뿐만 아니라 쿼리문에서 날짜를 더하고 빼야하는 일이 많다 이럴때 쓰는 쿼리.

 

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html :

SELECT DAY(your_date_field) AS dtDay, MONTH(your_date_field) AS dtMonth, YEAR(your_date_field) AS dtYear FROM your_table

일,월,년 구하는 쿼리

게시판 댓글개수 보이는 쿼리 짜는 과정 연습장

밑에서부터시작…

작업툴 pgadmin, 작업대상db : postgresql, 만들어야할 것 : 게시판 목록에서 댓글 개수 보이게 하는 쿼리

오랜만에 만지니 쿼리를 어떻게 만들어야할지 도무지 생각이 나지 않아서 검색시작…

레프트 조인문마저 생각이 안남..;;;

실행순서 정하기

페이징을 할 때 인덱스를 타고 스캔해서 코스트를 최소화하기 위해 나름대로 생각했는데 잘 되는건지 모르겠다. 데이터 누적되도 앞쪽부터 조회하게 돼 있으니까 별 문제는 없을것같다.

SELECT
  b.board_seq AS boardSeq,
  b.username,
  b.title,
  b.content,
  b.ip,
  b.reg_date AS regDate,
  b.read_count AS readCount,
  b.recommend_count AS recommendCount,
  b.last_modified_date AS lastModifiedDate,
  b.parent_seq AS parentSeq,
  b.depth,
  c.n_cmt AS nCmt
FROM
  board b
  LEFT OUTER JOIN (SELECT c.board_seq, count(c.board_seq) AS n_cmt FROM board_comments c WHERE c.board_seq BETWEEN 0 AND 10 GROUP BY c.board_seq) AS c
ON (c.board_seq = b.board_seq)
WHERE
  b.board_seq BETWEEN 0 AND 10

(
SELECT
b.board_seq AS boardSeq,
b.username,
b.title,
b.content,
b.ip,
b.reg_date AS regDate,
b.read_count AS readCount,
b.recommend_count AS recommendCount,
b.last_modified_date AS lastModifiedDate,
b.parent_seq AS parentSeq,
b.depth,
COUNT(board_comments.comment_seq) over () AS comment__count
FROM
board b
LEFT OUTER JOIN board_comments ON (b.board_seq = board_comments.board_seq)
GROUP BY b.board_seq, board_comments.comment_seq
ORDER BY b.board_seq
)

SELECT
b.board_seq AS boardSeq,
b.username,
b.title,
b.content,
b.ip,
b.reg_date AS regDate,
b.read_count AS readCount,
b.recommend_count AS recommendCount,
b.last_modified_date AS lastModifiedDate,
b.parent_seq AS parentSeq,
b.depth
FROM
board b
LEFT OUTER JOIN board_comments ON (b.board_seq = board_comments.board_seq)
GROUP BY b.board_seq
ORDER BY b.board_seq
(SELECT
b.board_seq AS boardSeq,
b.username,
b.title,
b.content,
b.ip,
b.reg_date AS regDate,
b.read_count AS readCount,
b.recommend_count AS recommendCount,
b.last_modified_date AS lastModifiedDate,
b.parent_seq AS parentSeq,
b.depth
–COUNT(cmt.comment_seq) over () AS comment__count
FROM
board b,
(SELECT * FROM board_comments WHERE board_seq BETWEEN 0 AND 10) cmt
WHERE
b.board_seq = cmt.board_seq

LIMIT 10)

select * from board;
select * from board_comments

SELECT
b.board_seq AS boardSeq,
b.username,
b.title,
b.content,
b.ip,
b.reg_date AS regDate,
b.read_count AS readCount,
b.recommend_count AS recommendCount,
b.last_modified_date AS lastModifiedDate,
b.parent_seq AS parentSeq,
b.depth,
COUNT(board_comments.comment_seq) over () AS comment__count
FROM
board b
LEFT OUTER JOIN board_comments ON (b.board_seq = board_comments.board_seq)
LIMIT 10

 

(퍼온쿼리)

SELECT articles.id,
articles.datestamp,
articles.title,
articles.shorttitle,
articles.description,
articles.markdown,
articles.body,
articles.idxfti,
articles.published,
articles.type,
COUNT(comments.id) over () AS comment__count
FROM articles
LEFT OUTER JOIN comments ON (articles.id = comments.article_id)
WHERE (articles.type = ‘NEWS’)
ORDER BY articles.datestamp DESC
LIMIT 1

오라클 기본 관리자(sys,system) 및 scott 계정 정보

오라클 설치시 자동으로 생성되는 계정

sys,system,scott

sys/change_on_install – DBMS의 데이터 딕셔너리 소유자, 오라클 데이터베이스 관리자(super user)

system/manager – SQL*Forms등의 툴을 위한 데이터 딕셔너리 소유자. 모든 권한이 SYS와 같으나 DB생성 권한은 없음

scott/tiger – sample사용자 계정 (연습용)

 

scott계정이 기본적으로 락이 걸려 잇어서 풀어줘야한다.(윈도우 기준)

cmd

sqlplus “/as sysdba”

show user

select username, account_status from dba_users where username=’SCOTT’;//sql쿼리 락이 걸렸나 확인

alter user scott account unlock;//스코뜨 언락

alter user scott identified by tiger; //scott의 비번을 tiger로

conn scott/tiger//접속해보기접속되면 되는거

 

다른 계정도 마찬가지 암호를 잊어먹으면…

cmd창에서 설정해주면 된다.

sqlplus “/as sysdba” 스퀄플러스로 접속

alter user sys identified by 암호;

alter user system identified by 암호;

 

connect sys/암호 as sysdba

connect system/암호

 

사용자 추가방법

create user  아이디 identified by 암호;

connect 아이디/암호

–이렇게 하면 권한이 업다고 뜸

connect /as sysdba //sys권한으로 접속

grant connect, resource to 아이디;

conn 아이디/암호