| No. | Keterangan | SQL Server | MySQL |
| 1 | List columns of a table | sp_columns table_name | describe table_name |
| 2 | Get datetime | GetDate() | Now() |
| 3 | Get timezone | SELECT SYSDATETIMEOFFSET() | SELECT @@system_time_zone; |
| 4 | Length of String | Len(‘String’) | Character_Length(‘String’) |
| 5 | Concatenation of strings | SELECT (‘String1’ + ‘String2’) | SELECT Concat(‘String1’, ‘String2’) |
| 6 | Select first 10 records | SELECT TOP 10 * from Table | SELECT * FROM Table LIMIT 10; |
| 7 | Update Join Table | Update table1 set column_name = b.column_name from table1 a inner join table2 b where a.key_id = b.key_id | update table1 a inner join tale2 b on a.key_id = b.key_id set a.column_name = b.column_name; |
| 8 | Create procedure | Create procedure proc_name (@argument data_type) as begin….end | DELIMITER $$ CREATE PROCEDURE proc_name(IN argument data_type) begin…..end$$DELIMITER ; |
| 9 | Select into | Select column1, column2 into new_table_name from table | create table new_table_name select column1, column2 from table; |
| 10 | Cursor | create procedure Test as begin declare @Nama varchar(255) declare @SemuaNama varchar(255)set @SemuaNama = ”declare Cursor1 cursor for select Nama from UserPass where FlagAktif = ‘A’ Open Cursor1 Fetch Cursor1 into @Nama While @@Fetch_Status = 0 begin select @SemuaNama = @SemuaNama + @Nama +’;’ Fetch Cursor1 into @Nama end Close Cursor1 deallocate Cursor1select @SemuaNamaend | DELIMITER $$ CREATE PROCEDURE createEmailList ( INOUT emailList varchar(4000) ) BEGIN DECLARE finished INTEGER DEFAULT 0; DECLARE emailAddress varchar(100) DEFAULT “”;— declare cursor for employee email DEClARE curEmail CURSOR FOR SELECT email FROM employee where level = 3 and active = 1;— declare NOT FOUND handler DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;OPEN curEmail;getEmail: LOOP FETCH curEmail INTO emailAddress; IF finished = 1 THEN LEAVE getEmail; END IF; — build email list SET emailList = CONCAT(emailAddress,”;”,emailList); END LOOP getEmail; CLOSE curEmail;END$$ DELIMITER ; |
MySQL, SQL Server
Beda Query Script SQL Server dan MySQL
