enujo

-- 오름차순 값이 적은 것부터 큰 순서대로
SELECT * FROM table_nm ORDER BY field_nm ASC;

-- 내림차순 값이 큰 것부터 작은 순서대로 
SELECT * FROM table_nm ORDER BY field_nm DESC;

enujo

		SELECT
			book.book_no as "bookNo", 
			book.disbook_no as "disbookNo", 
			book.library_no as "libraryNo", 
			book.book_name as "bookName", 
			book.book_writer as "bookWriter", 
			book.book_pulisher as "bookPulisher", 
			book.book_firstrent as "bookFirstrent",
			book.book_totalrent as "bookTotalrent", 

enujo

		SELECT
			book.book_no as bookNo, 
			book.disbook_no as disbookNo, 
			book.library_no as libraryNo, 
			book.book_name as bookName, 
			book.book_writer as bookWriter, 
			book.book_pulisher as bookPulisher, 
			book.book_firstrent as bookFirstrent,
			book.book_totalrent as bookTotalrent, 
			book.book_totalday as bookTotalday, 

enujo

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> 
		<property name="dataSource" ref="dataSource" /> 
		<property name="mapperLocations"> 
			<list>
				<value>classpath:/com/tistory/luahius/service/LibraryMapper.xml</value>
			</list> 
	 	</property> 
</bean> 

enujo

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

enujo

-- MySQL,MS-SQL
	INSERT 테이블명(컬럼명)
		SELECT 테이블명2.컬럼명
		FROM 테이블명2
		WHERE 조건

-- MS-SQL (INTO일 경우 테이블명 테이블이 자동 생성되며 값 insert)
	SELECT 컬럼명
		INTO 테이블명

enujo

-- MySQL null check
	SELECT IFNULL(컬럼명,'치환값') 보여질컬럼명
	FROM 테이블명;
	
-- MS-SQL null check
	SELECT ISNULL(컬럼명,'치환값') 보여질컬럼명
	FROM 테이블명;

-- ORACLE null check
	SELECT NVL(컬럼명,'치환값') 보여질컬럼명

enujo

-- 지역
CREATE TABLE region (
	region_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	region_name VARCHAR (30) NOT NULL
)DEFAULT CHARSET=euckr COMMENT '지역';	

-- 도서관
CREATE TABLE library(
	library_no INT NOT NULL PRIMARY KEY COMMENT '도서관 일련번호',
	region_no INT NOT NULL COMMENT '지역 번호',

enujo

-- mysql 테이블 형식 추출 쿼리

SELECT
   t1.table_name, ORDINAL_POSITION, column_name 'Column Name', COLUMN_TYPE 'COLUMN TYPE', column_key 'Key', 
   case 
when is_nullable = 'YES' THEN 'Y'
    when is_nullable = 'NO' THEN 'N'
   END AS 'Null able', 
   column_default 'Default Value', column_comment 'Comment',t1.table_comment,extra 'Extra'
FROM

enujo

-- DB 생성
CREATE DATEABASE library DEFAULT CHARSET euckr;;

-- 사용자 계정 생성 및 권한 부여
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON library.* TO 'userid'@'localhost' IDENTIFIED BY 'userpw';