Hint | Food | ¸À°úÇâ | Diet | Health | ºÒ·®Áö½Ä | ÀÚ¿¬°úÇÐ | My Book | À¯Æ©ºê | Frims | ¿ø ·á | Á¦ Ç° | Update | Site |
±â¼ú ¡í 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() |
||||
|
|
|||