±â¼ú ¡í IT ¡í ÀÎÅͳÝ
SQL ¸í·É¾î
HTML,CSS
- sql
- À©µµ, ÇÁ·Î±×·¥ ASP, VB ÆÁ
- ¹®ÀÚÇ¥, »ùÇÃ
- ½ÄÇ°°ü·Ã S/W
1. Å×À̺íÀ» »ý¼ºÇÏ¸é¼ Å×À̺íÀÇ µ¥ÀÌŸ º¹»ç
select * into »ý¼ºµÉÅ×À̺í¸í from ¿øº»Å×À̺í¸í
Å×ÀÌºí ±¸Á¶¸¸ º¹»çÇÏ°Ú´Ù¸é
select * into »ý¼ºµÉÅ×À̺í¸í from ¿øº»Å×À̺í¸í where 1=2
2. Å×À̺íÀÌ ÀÌ¹Ì »ý¼ºµÇ¾î Àִ°æ¿ì µ¥ÀÌŸ¸¸ º¹»ç
insert into Ä«ÇǵÉÅ×À̺í¸í select * from ¿øº»Å×À̺í¸í
ƯÁ¤ µ¥ÀÌŸ¸¸ º¹»ç ÇÏ°Ú´Ù¸é
insert into Ä«ÇǵÉÅ×À̺í¸í select * from ¿øº»Å×À̺í¸í where °Ë»öÁ¶°Ç
UPDATE Å×À̺í¸í SET Ä÷³ = °ª where Á¶°ÇÀý;
SELECT * FROM Å×À̺í¸í WHERE Á¶°ÇÀý /* Á¶°Ç¿¡ ÇØ´çÇÏ´Â µ¥ÀÌŸ °¡Á®¿À±â */
SELECT * FROM Å×À̺í¸í WHERE Ä®·³ BETWEEN x AND y
SELECT * FROM Å×À̺í¸í WHERE Ä®·³ LIKE 'ÆÐÅÏ' /* Ä®·³ÀÌ ÆÐÅÏ°ú °°Àº µ¥ÀÌŸ °¡Á®¿À±â */
** ÆÐÅÏ¿¡ »ç¿ëµÇ´Â ±âÈ£´Â %, _°¡ ÀÖ´Ù
'k%'(k·Î ½ÃÀ۵ǴÂ), '%k%'(Áß°£¿¡ k°¡ ÀÖ´Â), '%k'(k·Î ³¡³ª´Â)
'p_'(p·Î ½ÃÀÛÇÏ´Â 2ÀÚ¸®), 'p___'(p·Î ½ÃÀÛÇÏ´Â 4ÀÚ¸®), '__p'(3ÀÚ¸® µ¥ÀÌŸÁß p·Î ³¡³ª´Â)
SELECT * FROM Å×À̺í¸í WHERE Ä®·³ IS NULL /* Ä®·³ÀÌ NULLÀÎ µ¥ÀÌŸ °¡Á®¿À±â */
SELECT * FROM Å×À̺í¸í WHERE Ä®·³ IS NOT NULL /* Ä®·³ÀÌ NULLÀÌ ¾Æ´Ñ µ¥ÀÌŸ °¡Á®¿À±â */
SELECT * FROM Å×À̺í¸í WHERE Ä®·³>=x AND Ä®·³<=y
SELECT * FROM Å×À̺í¸í ORDER BY Ä®·³1 ASC, Ä®·³2 DESC /* º¹¼ö Ä®·³ Àç¹è¿Çϱâ */
1. SELECT ¹®ÀÇ ¿¬»ê
SELECT Ä®·³1, Ä®·³2, Ä®·³3+Ä®·³4 AS 'º°¸í' FROM Å×À̺í¸í
2. ORDER BY ±¸ÀÇ ¿¬»ê
SELECT Ä®·³1, Ä®·³2, Ä®·³3+Ä®·³4 AS 'º°¸í' FROM Å×À̺í¸í ORDER BY Ä®·³3+Ä®·³4 DESC
SELECT Ä®·³1, Ä®·³2, Ä®·³3+Ä®·³4 AS 'º°¸í' FROM Å×À̺í¸í ORDER BY 3 DESC
3. WHERE ±¸ÀÇ ¿¬»ê
SELECT Ä®·³1, Ä®·³2, Ä®·³3+Ä®·³4 AS 'º°¸í' FROM Å×À̺í¸í WHERE Ä®·³2>=(Ä®·³3+Ä®·³4)
5. ³¯Â¥ ¿¬»ê
SELECT GETDATE() /* ¼¹öÀÇ ÇöÀç ³¯Â¥¸¦ ±¸ÇÑ´Ù */
SELECT ³¯Â¥Ä®·³, ³¯Â¥Ä®·³-7 FROM Å×À̺í¸í
SELECT ³¯Â¥Ä®·³, ³¯Â¥Ä®·³+30 FROM Å×À̺í¸í
SELECT ³¯Â¥Ä®·³, DATEDIFF(day, ³¯Â¥Ä®·³, GETDATE()) FROM Å×À̺í¸í
2. ¹®ÀÚ¿ ÇÔ¼ö
SUBSTRING(¹®ÀÚ¿, À§Ä¡, ¹®ÀÚ¼ö) /* ƯÁ¤ À§Ä¡ ¹®ÀÚ¿ ÃßÃâ */
LEFT(¹®ÀÚ¿, ¹®ÀÚ¼ö) /* ¿ÞÂʺÎÅÍ ÁöÁ¤ÇÑ °¹¼ö ¹®ÀÚ¿ ÃßÃâ */
RIGHT(¹®ÀÚ¿, ¹®ÀÚ¼ö) /* ¿À¸¥ÂʺÎÅÍ ÁöÁ¤ÇÑ °¹¼ö ¹®ÀÚ¿ ÃßÃâ */
LEN(¹®ÀÚ¿) /* ¹®ÀÚ¿ÀÇ ¹®ÀÚ¼ö */
LTRIM(¹®ÀÚ¿) /* ¹®ÀÚ¿ ¿ÞÂÊÀÇ ½ºÆäÀ̽º¸¦ »èÁ¦ */
RTRIM(¹®ÀÚ¿) /* ¹®ÀÚ¿ ¿À¸¥ÂÊÀÇ ½ºÆäÀ̽º¸¦ »èÁ¦ */
UPPER(¹®ÀÚ¿) /* ´ë¹®ÀÚ·Î º¯È¯ */
LOWER(¹®ÀÚ¿) /* ¼Ò¹®ÀÚ·Î º¯È¯ */
3. ³¯Â¥ ÇÔ¼ö
GETDATE() /* ÇöÀç ³¯Â¥¿Í ½Ã°¢ */
DATEADD(µ¹·ÁÁִ°ª, ±â°£, ³¯Â¥) /* ³¯Â¥ ¹× ½Ã°£ÀÇ ´õÇϱâ¿Í »©±â */
DATEDIFF(µ¹·ÁÁִ°ª, ½ÃÀÛ³¯Â¥, ³¡³¯Â¥) /* µÎ ³¯Â¥¿Í ½Ã°£ÀÇ Â÷ÀÌ */
DATEPART(µ¹·ÁÁִ°ª, ³¯Â¥) /* ³¯Â¥¿Í ½Ã°£ÀÇ Æ¯Á¤°ªÀ» µ¹·ÁÁØ´Ù */
** µ¹·ÁÁִ°ª(¾à¾î)
Year-yy, Quarter-qq, Month-mm, DayofYear-dy, Day-dd, Week-wk,
Hour-hh, Minute-mi, Second-ss, Milisecond-ms
SELECT DATEADD(dd, 7, ³¯Â¥Ä®·³)
ÇÔ¼ö
SELECT COUNT(*) FROM Å×À̺í¸í /* Àüü µ¥ÀÌŸÀÇ °¹¼ö °¡Á®¿À±â */
SELECT SUM(Ä®·³) FROM Å×À̺í¸í /* Ä®·³ÀÇ ÇÕ°è ±¸Çϱâ */
SELECT MAX(Ä®·³) FROM Å×À̺í¸í /* Ä®·³ÀÇ ÃÖ´ë°ª ±¸Çϱâ */
SELECT MIN(Ä®·³) FROM Å×À̺í¸í /* Ä®·³ÀÇ ÃÖ¼Ò°ª ±¸Çϱâ */
SELECT AVG(Ä®·³) FROM Å×À̺í¸í /* Ä®·³ÀÇ Æò±Õ°ª ±¸Çϱâ */
GROUP BY¹®
SELECT Ä®·³ FROM Å×À̺í¸í GROUP BY Ä®·³
SELECT Ä®·³1, SUM(Ä®·³2) FROM Å×À̺í¸í GROUP BY Ä®·³1
SELECT Ä®·³1, COUNT(*) FROM Å×À̺í¸í GROUP BY Ä®·³1
SELECT Ä®·³1, Ä®·³2, MAX(Ä®·³3) FROM Å×À̺í¸í GROUP BY Ä®·³1, Ä®·³2
ÀϺ°,ÁÖº°,¿ùº° Åë°è
-- ÁÖ ´ÜÀ§
Select DATEPART(ww, order_dt), count(order_no) From ÁÖ¹®Å×À̺í
group by DATEPART(ww, order_dt)
order by DATEPART(ww, order_dt)
-- ¿ù´ÜÀ§
Select DATEPART(mm, order_dt), count(order_no) From ÁÖ¹®Å×À̺í
group by DATEPART(mm, order_dt)
order by DATEPART(mm, order_dt)
-- ³â´ÜÀ§
Select DATEPART(yy, order_dt), count(order_no) From ÁÖ¹®Å×À̺í
group by DATEPART(yy, order_dt)
order by DATEPART(yy, order_dt)
µ¥ÀÌŸ ÆíÁý
Ãß°¡
INSERT INTO Å×À̺í¸í VALUES (°ª1, °ª2, ...) /* ¸ðµç Çʵ忡 µ¥ÀÌŸ¸¦ ³ÖÀ» ¶§ */
INSERT INTO Å×À̺í¸í (Ä®·³1, Ä®·³2, ...) VALUES (°ª1, °ª2, ...) /* ƯÁ¤ Ä®·³¿¡¸¸ µ¥ÀÌŸ¸¦ ³ÖÀ» ¶§ */
INSERT INTO Å×À̺í¸í SELECT * FROM Å×À̺í¸í2 /* ÀÌ¹Ì Á¸ÀçÇÏ´Â Å×ÀÌºí¿¡ µ¥ÀÌŸ Ãß°¡ */
INSERT INTO Å×À̺í¸í(Ä®·³1, Ä®·³2, ...) SELECT Ä®·³1, Ä®·³2, ...) FROM Å×À̺í¸í2
°»½Å
UPDATE Å×À̺í¸í SET Ä®·³1=°ª1, Ä®·³2=°ª2 WHERE Á¶°Ç /* Á¶°Ç¿¡ ÇØ´çµÇ´Â µ¥ÀÌŸ °»½Å */
»èÁ¦
DELETE FROM Å×À̺í¸í /* Àüü µ¥ÀÌŸ »èÁ¦ */
DELETE FROM Å×À̺í¸í WHERE Á¶°Ç /* Á¶°Ç¿¡ ÇØ´çµÇ´Â µ¥ÀÌŸ »èÁ¦ */
CREATE TABLE µ¥ÀÌŸº£À̽ºÀ̸§.¼ÒÀ¯ÀÚÀ̸§.Å×À̺íÀ̸§ (Ä®·³ µ¥ÀÌŸÇü Á¦¾à, ...) /* Å×ÀÌºí ¸¸µé±â */
3. Stored Procedure(ÀúÀå ÇÁ·Î½ÃÀú)
** µ¥ÀÌŸº£À̽º³»¿¡¼ SQL ¸í·ÉÀ» ÄÄÆÄÀÏÇÒ¶§ ij½Ã¸¦ ÀÌ¿ëÇÒ ¼ö ÀÖÀ¸¹Ç·Î 󸮰¡ ¸Å¿ì ºü¸£´Ù
¹Ýº¹ÀûÀ¸·Î SQL ¸í·ÉÀ» ½ÇÇàÇÒ °æ¿ì ¸Åȸ ¸í·É¸¶´Ù ³×Æ®¿öÅ©¸¦ °æÀ¯ÇÒ ÇÊ¿ä°¡ ¾ø´Ù
¾îÇø®ÄÉÀ̼Ǹ¶´Ù »õ·Î ¸¸µé ÇÊ¿ä¾øÀÌ ÀÌ¹Ì ¸¸µé¾îÁø ÇÁ·Î½ÃÀú¸¦ ¹Ýº¹ »ç¿ëÇÑ´Ù
µ¥ÀÌŸº£À̽º ·ÎÁ÷À» ¼öÁ¤½Ã ÇÁ·Î½ÃÀú´Â ¼¹öÃø¿¡ ÀÖÀ¸¹Ç·Î ¾îÇø®ÄÉÀ̼ÇÀ» ´Ù½Ã ÄÄÆÄÀÏÇÒ ÇÊ¿ä°¡ ¾ø´Ù
** ÀúÀå ÇÁ·Î½ÃÀúÀÇ ¼Ò½º Äڵ带 º¸°í ½ÍÀ¸¸é SP_HELPTEXT ÇÁ·Î½ÃÀú¸í À» »ç¿ëÇÑ´Ù
CREATE PROC ÇÁ·Î½ÃÀú¸í AS SQL¹® /* ÀúÀå ÇÁ·Î½ÃÀú */
CREATE PROC ÇÁ·Î½ÃÀú¸í º¯¼ö¼±¾ð AS SQL¹® /* Àμö¸¦ °¡Áö´Â ÀúÀå ÇÁ·Î½ÃÀú */
CREATE PROC ÇÁ·Î½ÃÀú¸í WITH ENCRYPTION AS SQL¹® /* ÀúÀå ÇÁ·Î½ÃÀú º¸¾È ¼³Á¤ */
4. Trigger(Æ®¸®°Å)
** ÇÑ Å×À̺íÀÇ µ¥ÀÌŸ°¡ ÆíÁý(INSERT/UPDATE/DELETE)µÈ °æ¿ì¿¡ ÀÚµ¿À¸·Î ´Ù¸¥ Å×À̺íÀÇ
µ¥ÀÌŸ¸¦ »ðÀÔ, ¼öÁ¤, »èÁ¦ÇÑ´Ù
** Æ®¸®°Å ³»¿ëÀ» º¸°í ½ÍÀ¸¸é SP_HELPTRIGGER Æ®¸®°Å¸í À» »ç¿ëÇÑ´Ù
CREATE TRIGGER Æ®¸®°Å¸í ON Å×À̺í¸í FOR INSERT AS SQL¹® /* INSERT ÀÛ¾÷ÀÌ ¼öÇàµÉ¶§ */
CREATE TRIGGER Æ®¸®°Å¸í ON Å×À̺í¸í AFTER UPDATE AS SQL¹® /* UPDATE ÀÛ¾÷ÀÌ ¼öÇàµÇ°í ³ ÈÄ */
CREATE TRIGGER Æ®¸®°Å¸í ON Å×À̺í¸í INSTEAD OF DELETE AS SQL¹®
DROP TRIGGER Æ®¸®°Å¸í
MS-SQL ¹é¾÷ ¹× º¹¿ø
Àüü ¹é¾÷ (Full backup)
- óÀ½ DB »ý¼º½Ã
- Æ®·£Àè¼Ç ·Î±×¸¦ ºñ¿üÀ»½Ã
- DB¿¡ º¯°æÀÌ »ý°åÀ»¶§(ALTER DB)
- Â÷µî, ·Î±× ¹é¾÷ Àü Çѹø ÀÌ»ó
BACKUP DATABASE µðºñÀ̸§ TO ÆÄÀÏ&ÀåÄ¡
Â÷µî ¹é¾÷ (Differential Backup)
- ¸¶Áö¸· ¹é¾÷ ÀÌÈÄ¿¡ º¯°æµÈ ¸ðµç µ¥ÀÌÅ͸¦ ¹é¾÷
- º¹±¸½Ã ¸¶Áö¸· Â÷µî¹é¾÷°ú
¸¶Áö¸·°ú °¡Àå °¡±î¿î Àüü¹é¾÷ ÆÄÀÏ
BACKUP DATABASE µðºñÀ̸§ TO ÆÄÀÏ&ÀåÄ¡
WITH DIFFERENTIAL
¹Ì·¯ ¹é¾÷ (Mirror Backup)
- ¹é¾÷ µ¥ÀÌÅÍÀÇ À¯½ÇÀ̳ª ÆÄ¼Õ ½Ã
µ¥ÀÌÅÍ À¯½ÇÀ» ¸·±â À§ÇØ »ç¿ë
- ¹é¾÷ ÀåÄ¡ ÀÌ¿Ü¿¡ ÇϳªÀÇ ÀåÄ¡¿¡ ¶È°°ÀÌ ¹é¾÷
BACKUP DATABASE µðºñ¸í
TO DISK = 'C:\a.bak'
MIRROR TO DISK = 'D:\a.bak'
WITH FORMAT
º¹»ç Àü¿ë ¹é¾÷ (Copy Backup)
- µ¥ÀÌÅÍ º£À̽ºÀÇ º¹»ç³ª À̵¿ Å×½ºÆ®½Ã »ç¿ë
BACKUP DATABASE µðºñÀ̸§ TO ÀåÄ¡
WITH COPY_ONLY
RESTORE DATABASE µðºñÀ̸§ FROM ÀåÄ¡
---Excel ¿±â
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet : Dim filteext As String = ""
'check for the file type
If IO.Path.GetExtension(fileName) = "xls" Then
filteext = "Excel 8.0"
ElseIf IO.Path.GetExtension(fileName) = ".xlsx" Then
filteext = "Excel 12.0"
End If
'open connection
MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & fileName & "';Extended Properties=" & filteext & ";")
MyConnection.Open()
Dim myTableName = MyConnection.GetSchema("Tables").Rows(0)("TABLE_NAME")
Dim MyCommand As OleDbDataAdapter = New OleDbDataAdapter(String.Format("SELECT * FROM [{0}]", myTableName), MyConnection)
MyCommand.TableMappings.Add("Table", "TestTable")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView1.DataSource = DtSet.Tables(0)
'DtSet.DataSetName.
MyConnection.Close()