64bit 윈도우즈 7에 오라클 10g 64bit를 설치하고 Toad 9.5를 깔았더니 토드가 오라클을 인식 못 해...
구글링 결과 토드가 64bit 오라클을 못 찾는다고... 레지스트리 변경해서 인식하게 해주는 방법도 있으나 여러모로 불안정해보여서
그냥 32bit 오라클을 설치... http://likebnb.tistory.com/79
추가적으로 윈도우즈7에 오라클 10g 설치 시 지원하지 않는 운영체제?라는 메시지와 함께 오라클 설치가 불가능.
오라클 설치용 설정파일 수정 필요 http://guisin.net/69
UPDATE /*+ bypass_ujvc */
(
SELECT
CONTENT_ID,
HDV_YN,
META18 AS META_HDV_YN
FROM
(
SELECT
A.CONTENT_ID
,CASE
WHEN COUNT(B.META02) > 0 THEN 'Y'
ELSE 'N'
END AS HDV_YN
,C.META18
FROM
SUB_CONTENT_META A, SUB_CONTENT_META_EXTEND B, CONTENT_META_EXTEND C
WHERE
A.SUB_CONTENT_ID = B.SUB_CONTENT_ID
AND A.CONTENT_ID = C.CONTENT_ID
AND B.META02 = 'PD009703'
-- AND A.CONTENT_ID = ''
GROUP BY
A.CONTENT_ID, C.META18
)
)
SET META_HDV_YN = HDV_YN
CONTENT_META_EXTEND 의 컬럼을 업데이트하고 싶은데 위와 같이 안쪽(?)에서 뷰를 만들 때 같이 조인해버리면
"가상 열은 사용할 수 없습니다" 라는 에러가 난다.
[가상 Table에 정보를 저장(Insert) 하거나 변경(update)를 수행할 수 없지요
즉, UPDATE (SELECT....)
SELECT 부분이 실 Table이어야만 하지 Select된 결과에 대한 것은 변경이나 저장을
할 수 없는 것이지요.
따라서 Select 부분을 조건절로 보내고, 실 Table을 정의하시면 됩니다.]
UPDATE /*+ bypass_ujvc */
(
SELECT
T.CONTENT_ID,
T.HDV_YN,
C.META18 AS META_HDV_YN
FROM
(
SELECT
A.CONTENT_ID
,CASE
WHEN COUNT(B.META02) > 0 THEN 'Y'
ELSE 'N'
END AS HDV_YN
FROM
SUB_CONTENT_META A, SUB_CONTENT_META_EXTEND B
WHERE
A.SUB_CONTENT_ID = B.SUB_CONTENT_ID
AND B.META02 = 'PD009703'
AND A.CONTENT_ID = '0000024061'
GROUP BY
A.CONTENT_ID
) T, CONTENT_META_EXTEND C
WHERE
T.CONTENT_ID = C.CONTENT_ID
)
SET META_HDV_YN = HDV_YN;
(
select e.PRODUCT_ID, e.PROD_DESC o_PROD_DESC,f.PROD_DESC N_PROD_DESC
from (
select b.CHANNEL_ID,c.PRODUCT_ID,max(d.PROD_DESC) PROD_DESC
from PRODUCT_INFO a, PRODUCT_CATEGORY b, PRODUCT_INFO c, TBL_DP_PROD d
where a.CONTENT_TYPE='09'
and a.PRODUCT_ID = b.CHANNEL_ID and b.PRODUCT_ID = c.PRODUCT_ID
and b.PRODUCT_ID <> b.CHANNEL_ID
and b.CHANNEL_ID = d.PROD_ID
group by b.CHANNEL_ID,c.PRODUCT_ID
) e, TBL_DP_PROD f
where e.PRODUCT_ID = f.PROD_ID
The SQLite library includes a simple command-line utility named sqlite3 (or sqlite3.exe on windows) that allows the user to manually enter and execute SQL commands against an SQLite database. This document provides a brief introduction on how to use the sqlite3 program.
Getting Started
To start the sqlite3 program, just type "sqlite3" followed by the name the file that holds the SQLite database. If the file does not exist, a new one is created automatically. The sqlite3 program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.
For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:
$ sqlite3 ex1
SQLite version 3.6.11
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>
You can terminate the sqlite3 program by typing your systems End-Of-File character (usually a Control-D). Use the interrupt character (usually a Control-C) to stop a long-running SQL statement.
Make sure you type a semicolon at the end of each SQL command! The sqlite3 program looks for a semicolon to know when your SQL command is complete. If you omit the semicolon, sqlite3 will give you a continuation prompt and wait for you to enter more text to be added to the current SQL command. This feature allows you to enter SQL commands that span multiple lines. For example:
sqlite> CREATE TABLE tbl2 (
...> f1 varchar(30) primary key,
...> f2 text,
...> f3 real
...> );
sqlite>
Aside: Querying the SQLITE_MASTER table
The database schema in an SQLite database is stored in a special table named "sqlite_master". You can execute "SELECT" statements against the special sqlite_master table just like any other table in an SQLite database. For example:
$ sqlite3 ex1
SQLite vresion 3.6.11
Enter ".help" for instructions
sqlite> select * from sqlite_master;
type = table
name = tbl1
tbl_name = tbl1
rootpage = 3
sql = create table tbl1(one varchar(10), two smallint)
sqlite>
But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against the sqlite_master table. The sqlite_master table is updated automatically as you create or drop tables and indices from the database. You can not make manual changes to the sqlite_master table.
The schema for TEMPORARY tables is not stored in the "sqlite_master" table since TEMPORARY tables are not visible to applications other than the application that created the table. The schema for TEMPORARY tables is stored in another special table named "sqlite_temp_master". The "sqlite_temp_master" table is temporary itself.
Special commands to sqlite3
Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements.
For a listing of the available dot commands, you can enter ".help" at any time. For example:
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
.dump ?TABLE? ... Dump the database in an SQL text format
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.
.genfkey ?OPTIONS? Options are:
--no-drop: Do not drop old fkey triggers.
--ignore-errors: Ignore tables with fkey errors
--exec: Execute generated SQL immediately
See file tool/genfkey.README in the source
distribution for further information.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.indices TABLE Show names of all indices on TABLE
.iotrace FILE Enable I/O diagnostic logging to FILE
.load FILE ?ENTRY? Load an extension library
.mode MODE ?TABLE? Set output mode where MODE is one of:
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator string
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Print STRING in place of NULL values
.output FILENAME Send output to FILENAME
.output stdout Send output to the screen
.prompt MAIN CONTINUE Replace the standard prompts
.quit Exit this program
.read FILENAME Execute SQL in FILENAME
.restore ?DB? FILE Restore content of DB (default "main") from FILE
.schema ?TABLE? Show the CREATE statements
.separator STRING Change separator used by output mode and .import
.show Show the current values for various settings
.tables ?PATTERN? List names of tables matching a LIKE pattern
.timeout MS Try opening locked tables for MS milliseconds
.timer ON|OFF Turn the CPU timer measurement on or off
.width NUM NUM ... Set column widths for "column" mode
sqlite>
Changing Output Formats
The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.
The default output mode is "list". In list mode, each record of a query result is written on one line of output and each column within that record is separated by a specific separator string. The default separator is a pipe symbol ("|"). List mode is especially useful when you are going to send the output of a query to another program (such as AWK) for additional processing.
sqlite> .mode list
sqlite> select * from tbl1;
hello|10
goodbye|20
sqlite>
You can use the ".separator" dot command to change the separator for list mode. For example, to change the separator to a comma and a space, you could do this:
In "line" mode, each column in a row of the database is shown on a line by itself. Each line consists of the column name, an equal sign and the column data. Successive records are separated by a blank line. Here is an example of line mode output:
sqlite> .mode line
sqlite> select * from tbl1;
one = hello
two = 10
one = goodbye
two = 20
sqlite>
In column mode, each record is shown on a separate line with the data aligned in columns. For example:
sqlite> .mode column
sqlite> select * from tbl1;
one two
---------- ----------
hello 10
goodbye 20
sqlite>
By default, each column is at least 10 characters wide. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command. Like this:
sqlite> .width 12 6
sqlite> select * from tbl1;
one two
------------ ------
hello 10
goodbye 20
sqlite>
The ".width" command in the example above sets the width of the first column to 12 and the width of the second column to 6. All other column widths were unaltered. You can gives as many arguments to ".width" as necessary to specify the widths of as many columns as are in your query results.
If you specify a column a width of 0, then the column width is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data. This makes the column width self-adjusting. The default width setting for every column is this auto-adjusting 0 value.
The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the examples above, the column labels are on. To turn them off you could do this:
sqlite> .header off
sqlite> select * from tbl1;
hello 10
goodbye 20
sqlite>
Another useful output mode is "insert". In insert mode, the output is formatted to look like SQL INSERT statements. You can use insert mode to generate text that can later be used to input data into a different database.
When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into. For example:
sqlite> .mode insert new_table
sqlite> select * from tbl1;
INSERT INTO 'new_table' VALUES('hello',10);
INSERT INTO 'new_table' VALUES('goodbye',20);
sqlite>
The last output mode is "html". In this mode, sqlite3 writes the results of the query as an XHTML table. The beginning <TABLE> and the ending </TABLE> are not written, but all of the intervening <TR>s, <TH>s, and <TD>s are. The html output mode is envisioned as being useful for CGI.
Writing results to a file
By default, sqlite3 sends query results to standard output. You can change this using the ".output" command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use ".output stdout" to begin writing to standard output again. For example:
The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.
For example, to see a list of the tables in the database, you can enter ".tables".
sqlite> .tables
tbl1
tbl2
sqlite>
The ".tables" command is similar to setting list mode then executing the following query:
SELECT name FROM sqlite_master
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
UNION ALL
SELECT name FROM sqlite_temp_master
WHERE type IN ('table','view')
ORDER BY 1
In fact, if you look at the source code to the sqlite3 program (found in the source tree in the file src/shell.c) you'll find exactly the above query.
The ".indices" command works in a similar way to list all of the indices for a particular table. The ".indices" command takes a single argument which is the name of the table for which the indices are desired. Last, but not least, is the ".schema" command. With no arguments, the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. If you give the name of a table to ".schema", it shows the original CREATE statement used to make that table and all if its indices. We have:
sqlite> .schema
create table tbl1(one varchar(10), two smallint)
CREATE TABLE tbl2 (
f1 varchar(30) primary key,
f2 text,
f3 real
)
sqlite> .schema tbl2
CREATE TABLE tbl2 (
f1 varchar(30) primary key,
f2 text,
f3 real
)
sqlite>
The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:
SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type!='meta'
ORDER BY tbl_name, type DESC, name
Or, if you give an argument to ".schema" because you only want the schema for a single table, the query looks like this:
SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
You can supply an argument to the .schema command. If you do, the query looks like this:
SELECT sql FROM
(SELECT * FROM sqlite_master UNION ALL
SELECT * FROM sqlite_temp_master)
WHERE tbl_name LIKE '%s'
AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%'
ORDER BY substr(type,2,1), name
The "%s" in the query is replace by your argument. This allows you to view the schema for some subset of the database.
sqlite> .schema %abc%
Along these same lines, the ".table" command also accepts a pattern as its first argument. If you give an argument to the .table command, a "%" is both appended and prepended and a LIKE clause is added to the query. This allows you to list only those tables that match a particular pattern.
The ".databases" command shows a list of all databases open in the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "temp", the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file.
sqlite> .databases
Converting An Entire Database To An ASCII Text File
Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3.
A good way to make an archival copy of a database is this:
This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:
$ zcat ex1.dump.gz | sqlite3 ex2
The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:
$ createdb ex2
$ sqlite3 ex1 .dump | psql ex2
Other Dot Commands
The ".explain" dot command can be used to set the output mode to "column" and to set the column widths to values that are reasonable for looking at the output of an EXPLAIN command. The EXPLAIN command is an SQLite-specific SQL extension that is useful for debugging. If any regular SQL is prefaced by EXPLAIN, then the SQL command is parsed and analyzed but is not executed. Instead, the sequence of virtual machine instructions that would have been used to execute the SQL command are returned like a query result. For example:
The ".timeout" command sets the amount of time that the sqlite3 program will wait for locks to clear on files it is trying to access before returning an error. The default value of the timeout is zero so that an error is returned immediately if any needed database table or index is locked.
And finally, we mention the ".exit" command which causes the sqlite3 program to exit.
Using sqlite3 in a shell script
One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as an added convenience, sqlite3 allows a single SQL command to be entered on the command line as a second argument after the database name. When the sqlite3 program is launched with two arguments, the second argument is passed to the SQLite library for processing, the query results are printed on standard output in list mode, and the program exits. This mechanism is designed to make sqlite3 easy to use in conjunction with programs like "awk". For example:
SQLite commands are normally terminated by a semicolon. In the shell you can also use the word "GO" (case-insensitive) or a slash character "/" on a line by itself to end a command. These are used by SQL Server and Oracle, respectively. These won't work in sqlite3_exec(), because the shell translates these into a semicolon before passing them to that function.
Compiling the sqlite3 program from sources
The source code to the sqlite3 command line interface is in a single file named "shell.c" which you can download from the SQLite website. Compile this file (together with the sqlite3 library source code to generate the executable. For example:
Here is what you do to start experimenting with SQLite without having to do a lot of tedious reading and configuration:
Download The Code
Get a copy of the prebuilt binaries for your machine, or get a copy of the sources and compile them yourself. Visit the download page for more information.
Create A New Database
At a shell or DOS prompt, enter: "sqlite3 test.db". This will create a new database named "test.db". (You can use a different name if you like.)
Enter SQL commands at the prompt to create and populate the new database.
Additional documentation is available here
Write Programs That Use SQLite
Below is a simple TCL program that demonstrates how to use the TCL interface to SQLite. The program executes the SQL statements given as the second argument on the database defined by the first argument. The commands to watch for are the sqlite3 command on line 7 which opens an SQLite database and creates a new TCL command named "db" to access that database, the invocation of the db command on line 8 to execute SQL commands against the database, and the closing of the database connection on the last line of the script.
#!/usr/bin/tclsh
if {$argc!=2} {
puts stderr "Usage: %s DATABASE SQL-STATEMENT"
exit 1
}
load /usr/lib/tclsqlite3.so Sqlite3 sqlite3 db [lindex $argv 0] db eval [lindex $argv 1] x {
foreach v $x(*) {
puts "$v = $x($v)"
}
puts ""
} db close
Below is a simple C program that demonstrates how to use the C/C++ interface to SQLite. The name of a database is given by the first argument and the second argument is one or more SQL statements to execute against the database. The function calls to pay attention to here are the call to sqlite3_open() on line 22 which opens the database, sqlite3_exec() on line 27 that executes SQL commands against the database, and sqlite3_close() on line 31 that closes the database connection.
See also the Introduction To The SQLite C/C++ Interface for an introductory overview and roadmap to the dozens of SQLite interface functions.
#include <stdio.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0;
int rc;
UPDATE TBL_US_MEMBER
SET OP_HP_NO = (SELECT REPLACE(OP_HP_NO, '-', '') FROM TBL_US_MEMBER A WHERE TBL_US_MEMBER.MBR_NO = A.MBR_NO) WHERE MBR_CAT_CD = 'US000207'
SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
DECODE(B.CONSTRAINT_TYPE,
'P','PRIMARY KEY',
'U','UNIQUE KEY',
'C','CHECK OR NOT NULL',
'R','FOREIGN KEY') CONSTRAINT_TYPE,
A.CONSTRAINT_NAME CONSTRAINT_NAME
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = UPPER('&table_name')
AND A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 1;
To resolve these problems, go to Edit → Editor Options and look under "General Options", under the "General Options" node, where there are several options related to tab stops. Unchecking "Auto indent" and "Tab to next column" will make the step size consistent. Unchecking "Insert TABs into text for TAB characters" and "Insert mixture of tab/space for optimal fill" will make TOAD use spaces instead of Tabs, which will prevent any problems where TOAD's tab stop difference is different from another program's.
SELECT rownum AS rn, A.num, A.status
FROM
(
SELECT num, status FROM bp_test1 WHERE status != 'D' ORDER BY status ASC, num ASC
) A
WHERE rn >= 1 AND rn <= 10
이건 안됨. rn을 못찾는군...
SELECT rownum, A.num, A.status
FROM
(
SELECT num, status FROM bp_test1 WHERE status != 'D' ORDER BY status ASC, num ASC
) A
WHERE rownum >= 1 AND rownum <= 10
이건 되는군.
근데 웃기는건 WHERE 절을 rownum >= 2로 하면 데이타가 없다. 꼭 2가 아니라 2 이상으로 하면 안나온다.
찾아보니
rownum이 1보다 크다는 조건은 항상 거짓이 됩니다.
select문으로 조회된 첫 번째 로우에 대해서 rownum이 1로 지정될텐데
그럼 rownum이 1보다 크다는 조건이 거짓이 되므로 제외됩니다.
그러면 그 다음의 두번째 로우가 첫번째 로우가 되고 rownum이 1이 지정됩니다.
역시 조건이 거짓이 되므로 제외됩니다.
그런 식으로 모든 로우에 대해 조건을 만족하지 못하게 되니 조회되는게 없게 됩니다.
이렇다는군...
그럼.
select * from (
select rownumrn, a.*
from rowtest a
where rownum <= 10
) a where rn >= 6
이렇게 하라는데, 이건 안쪽의 쿼리에 정렬이 없을 때에나 사용가능하다.
조건 처리 후, 정렬 하기 때문에, 정렬 후에 rownum으로 갯수를 한정하고 싶으면 서브쿼리로 해야할 듯.
삼중으로 가야할 듯 해서 뒤져보니 역시나...
SELECT *
FROM
(
SELECT rownum rn, A.*
FROM
(
SELECT num, status
FROM bp_test1
WHERE
status != 'D'
ORDER BY status ASC, num ASC
) A
WHERE
rownum <= 20 --Maximun
)
WHERE
rn >= 6 --Start#
1. DB를 사용하기위해 ORACLE로 LOGIN 방법
가. SQLPLUS [Enter]
User ID : xxxxxx
password : xxxxxxxx
나. SQLPLUS user_id [Enter]
password : xxxxxxxx
다. SQLPLUS user_id/password [Enter]
※ 다. 와 같은 방법으로 Login 하게 되면 사용자명과 암호가 노출된다
그러므로 가장좋은 방법은 가, 그리고 나. 이다
2. SQLPLUS 상에서는 Auto Commit이 안된다. 그러므로 중간 중간 " COMMIT "를
수행한다.
3. SQL 문장의 Terminate 는 ==> “ ; ”
4. 한 화면씩 보여주는 기능
==> set pause on; (설정)
set pause off; (해제)
※ 설정을 하고나서 SQL 명령을 수행하면 커서가 그 다음 LINE에서 대기하고
있으므로 [Enter] Key를 친다. 그 다음부터는 Enter키로 한화면씩 보면 됨
5. SQL 명령어를 모를 경우 ?
==> help [명령어]
6. Unix 명령어를 사용하기 위해서는 ?
==> “ ! ” 를 붙여 사용한다.
7. 바로전에 실행한 SQL문을 FILE로 저장하려면 ?
==> “ save ”
8. 파일의 내용을 메모리로 불러오려면 ?
==> “ get ” 를 붙여 사용한다.
9. 메모리로 불러온 SQL문이나 메모리에 있는 명령을 실행 하려면 ?
==> “ / ” 를 붙여 사용한다.
10. 바로 전에 수행한 명령어를 편집하려면 ?
==> “ ed ” 를 붙여 사용한다.
11. 바로 전에 수행한 명령어 보려면 ?
==> “ l ” 를 붙여 사용한다.
12. SQL문이 있는 FIle을 바로 실행하려면 ?
==> “ @ ” 또는 " Start " 를 붙여 사용한다.
13. set heading off / on
==> Columns 명을 나타내지 않는다.
14. set arraysize line_number(숫자);
==> 한번에 DATA를 가져오는 단위
15. set timing on / off
==> sql문을 수행하는데 소요되는 시간을 나타내어 준다.
ROUND
ROUND는 반올림 함수이다.
형식: ROUND(숫자, 자릿수)
ROUND는 자릿수만 조심하면 된다. 2번째 결과는 8을 반올림하여 그 앞의 9에 1이 더해진것이고 3번째 결과는 9가 반올림된것이다.
CEILING, FLOOR
CEILING은 소수점 첫번째 값을 기준으로 올림을 하고 FLOOR는 소수점 첫번째 값을 기준으로 내림을 한다.
형식: CEILING(숫자) ... FLOOR도 동일
CEILING과 FLOOR는 소수 첫번째값이 5 이상이든 이하든간에 무조건 올림, 내림을 수행한다.
REPLACE
REPLACE함수는 지정한 문자열을 다른 문자열로 바꾸어준다.
형식: REPLACE(문자, 타겟문자, 바꿀문자)
REPLICATE
REPLICATE함수는 문자열을 지정된 횟수만큼 반복한다.
형식: REPLICATE(문자, 횟수)
STUFF
STUFF함수는 문자열에서 특정 시작위치에서 지정된 길이만큼 문자를 바꾸어준다.
형식: STUFF(문자, 시작위치, 길이, 바꿀문자)
SUBSTRING
SUBSTRING은 STUFF와 비슷하지만 문자를 바꾸는 것이 아니라 그 문자를 반환한다.
형식: SUBSTRING(문자, 시작위치, 길이)
PATINDEX, CHARINDEX
PATINDEX와 CHARINDEX는 문자열에서 지정한 패턴이 시작되는 위치를 뽑아준다.
형식: PATINDEX(문자패턴, 문자) - 문자패턴은 Like 사용과 같다.
형식: CHARINDEX(문자패턴, 문자) - 문자패턴은 일반형식을 사용한다.
SPACE
SPACE함수는 지정한 수 만큼 공백을 추가한다.
형식: SPACE(횟수)
시간 및 날짜 함수
GETDATE()
GETDATE()는 현재 시간을 표시해준다.
DATEADD
DATEADD함수는 날자에 지정한 만큼을 더한다.
형식: DATEADD(날자형식, 더할 값, 날자)
DATEDIFF
DATEDIFF함수는 두 날자사이의 날자형식에 지정된 부분을 돌려준다.
형식: DATEDIFF(날자형식, 시작 날자, 끝 날자)
DATENAME
DATENAME함수는 지정한 날자의 날자형식의 이름을 돌려준다.
형식: DATENAME(날자형식, 날자)
DATEPART
DATEPART함수는 날자에서 지정한 날자형식부분만 추출해줍니다.
형식: DATEPART(날자형식, 날자)
주일은 일요일부터 1로 시작해서 토요일날 7로 끝나게 된다.
--*******************************************************************
-- 함수(Conversion)
--*******************************************************************
--cast, convert : 문자로 치환/number와 date를 문자로 치환.
-- /간단한 형 변환(CAST)
-- /날짜를 문자로 변환 시 여러가지 형식이 가능하므로 주로 사용하는 함수는(CONVERT)이다.
select convert(char, 123456789) result1,
cast(123456789 as char) result2
--
select convert(varchar,456789) result1,
cast(456789 as varchar) result2,
str(456789, 10) result3
/* 숫자를 바꾸는 convert함수를 썼는데 2번째줄의 경우는 '300567'이라는 6섯자를
'char(4)' 4자로 바꾸라는 것: 이것은 말이 안되므로 * 표시가 프린트됨!(주의 사항) */
select convert(char(7),300567) result1,
convert(char(4),300567) result2
--
select convert(char,getdate(),0) result1,
cast(getdate() as char) result2
--
select convert(CHAR, GETDATE(), 109) RESULT
select convert(CHAR, GETDATE(), 111) RESUlT
select convert(CHAR(10), GETDATE(), 121) RESULT
select convert(CHAR, GETDATE(), 114) RESULT
--1. 방법1
select mem_id "회원ID",
left(mem_id,1) + right(convert(char(4),right(mem_id,3) + 1010),3) "조합회원ID"
from member
where mem_name = '이쁜이'
--2. 방법2
select mem_id "회원ID",
left(mem_id,1) + right(convert(char(4),substring(mem_id,2,4)+1010),3) "조합회원ID"
from member
where mem_name = '이쁜이'
--***********************************************************************
-- 함수(conversion) : 날짜로 치환
--***********************************************************************
--날짜로 치환/모양만 날짜형인 문자를 DATE로 치환
select'19990101'result1,'1999-01-01'result2,
'1999-01-01 00:00:00.000'result3
--
select convert(datetime,'20201025',112)" result1",
convert(datetime,'20201025')" result2",
convert(datetime,'2020-10-25 10:15:20.000') " result3",
cast('2020-10-25 10:15:20.000' as datetime) " result4"
--
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
--1900년대 사람들만 있다고 가정했을 경우 19를 더해서 밀레니엄버그를 없앨 수 가 있다.
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 500일을 더한 날짜를 검색!
--(Alias는 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) + 500 치환날짜
from member
--회원테이블에서 주민등록번호1을 날짜로 치환한 후 '1974-01-01'부터 '1975-12-31'사이의 날짜를 검색!
--(Alias 회원명, 주민등록번호1, 치환날짜)
select mem_name 회원명, mem_regno1 주민등록번호1,
convert(datetime,'19'+mem_regno1) 치환날짜
from member
where convert(datetime,'19'+mem_regno1)between'1974-01-01'and'1975-12-31'
--회원테이블에서 생일을 문자로 치환한 후 LIKE '1975%'의 조건을 사용하여 해당회원을 검색!
--Alias는 회원명, 생일)
select mem_name 회원명, mem_bir 생일
from member
where convert(char, mem_bir, 121) like '1975%'
--******************************************************************
-- 함수(NULL)
--******************************************************************
/* ## 데이터를 처리할 때 NULL값의 사용은 최대한 줄여야 하지만 사용해야 할 경우가 있다.
## 학생에 대한 정보를 입력할 때 '전화번호' 속성은 전화번호가 없는 학생이 있을 수가 있다 .이런 경우에는 NULL값을 사용한다.
## NULL값은 0.1과 같은 특정한 값이 아니고 아무 것도 없는 것을 뜻한다.
## SQL에서 NULL값을 허용하지 않는 속성에 대해 NULL값으로 수정하려 한다면 에러가 발생한다. */
--null값을 찾을 때에는 is null
select buyer_name buyer_charger
from buyer
where buyer_charger is null
--null인 값을 찾을 때 '='은 성립되지 않는다.
select buyer_name buyer_charger
from buyer
where buyer_charger=null
--
select buyer_name buyer_charger
from buyer
where buyer_charger =''
--null값이 아닌 값을 찾으려 할 때 is not null
select buyer_name buyer_charger
from buyer
where buyer_charger is not null
--ISNULL(c,d) / c가 NULL값이면 d값으로 치환
--null값일 때 '이름없다' 로 출력!
select buyer_name, buyer_charger, isnull(buyer_charger, '이름없다')
from buyer
--null에다 100을 더하면 null이다.
select null + 100
--
select isnull(null,0)+100
--
select mem_name, mem_mileage, mem_mileage+100
from member
--
select mem_name, mem_mileage, mem_mileage + 100
from member
where mem_name like '[바-
WHILE @i>=@j
BEGIN
INSERT INTO @temp(date) values(@temp_date)
SET @j=@j +1;
SET @temp_date= DATEADD(dd,@j,@s_date)
END
/*SELECT date FROM @temp*/
INSERT INTO DUAL_CALENDAR
SELECT
DATENAME(yy, date), DATENAME(mm, date),
/*DATENAME(dd, date), */
SUBSTRING(CONVERT(char(10), date,121), 9, 10),
CONVERT(char(10), date,121) AS date
FROM @temp
/*여기까지 한번에 실행할 것. 100년까지는 한번에 안되는듯. 50년은 됨.
50년 마다 한번씩 실행되게 스케줄러에 등록 또는 1년마다 한번씩 되도록 등록*/
