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 쿼리 사용시 발생하는 오류

 

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
나머지는 진짜 참고만 했고 첫번째 링크에 솔루션이 있다.

테이블 두개…

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

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

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

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으로 분리

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

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

DateAdd function

 

Postgresql Database 시간함수

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

간단한 예제…

part는 hour만 가져온다. 값이 20이 된다.

date_trunc는 timestamp형태를 유지한다. 시간 밑으로는 절삭한다.

 

 

Postgresql SQL update query – column value to another column

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

데이터 이동 방향
dept_name = dept_code

 

create query

update query

 

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

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

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

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

테이블 용량확인

데이터베이스 용량확인

 

어디서 퍼왔는지는 까먹음

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,
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 아이디/암호