SQL Snippets

An SQL Snippets based on MySQL Snippets - Sublime Plugin for vscode

I just converted it ,all appreciate goes to Anton Korniychuk

What's included - contents

Database
Table
Table components
Insert
Select
Query options
Update
Delete
Alter
Trigger
Procedure
Function
Constructions
Show
User
Privileges
Other

Database

Snippet Code	Description
s-db	create a new database
s-db-drop	delete the database

Table

Snippet Code	Description
s-table	create simple table with INT primary key
s--table	create simple table with INT primary key, but first remove the old table
s-table-tmp	create simple temporary table with engine=memory
s--table-tmp	the same, but first remove the old table

s-many-many - Very powerful snippet! Make table for relation many-to-many, make two foreign key constrain, and two indexes in it and make two indexes in corresponding tables

Create Table components

Snippet Code	Description
s-fk	make foreign key constrain for table creation
s--fk	make foreign key constrain for table creation with corresponding indexes
s-idx	make default index for table creation
s-idx-txt	make `FULLTEXT` index for table creation
s-pk	make primary key
s-uk	make unique key

Insert

Snippet Code	Description
s-i	insert multirow
s-ione	insert one row
s-ifrom	isert data from a sample(other table or database)

Select

Snippet Code	Description
s-s	default select for extending with help query-options
s-sone	select one row by where condition
s-sinline	inline select for simple a sample
s-smin	inline select for search min value
s-smax	inline select for search max value
s-scount	inline select for count rows in the table
s-ss	select a value `SELECT '...';`
s-sv	select a variable `SELECT` ...`;`

Query options

Expressions

Snippet Code	Description
s-	the entity. Like `table`.`column`
s--	the entity with a comma. Like , `table`.`column`
s-alias	the entity with alias. Like `table`.`column` AS `my_col`
s--alias	the same with comma. Like , `table`.`column` AS `my_col`
s-and	a part of conditions `AND ( ... )`
s-or	a part of conditions `OR ( ... )`
s--and	a part of conditions with expression AND ( `col` = `col2` )
s--or	a part of conditions with expression OR ( `col` = `col2` )
s-e	an expression ( `col` = `col2` )

Statements

Snippet Code	Description
s-f	`from` statement like FROM `table` AS `alias`
s-j	`inner join` statement
s-jleft	`left join` statement
s-jright	`right join` statement
s-w	`where` statement
s-o	`order by` statement
s-l	`limi` statement
s-g	`group by` statement
s-h	`having` statement
s-u	`union` statement

Update

Snippet Code	Description
s-u	default `update` snippet with `where` condition

Delete

Snippet Code	Description
s-d	default `delete` snippet with `where` condition

Alter - modify structure

All alter snippets begining from s-alter-* prefix, like s-alter-add.

Columns

Snippet Code	Description
add	add a column to table at the last column
add-first	add a column to table at the first column
add-after	add a column to table after someone column
auto-increment	change `auto_increment` counter value
change	`change` the column(rename or change type)
modify	`modify` the column(change type and column order)
drop	drop the column from the table

Other

Snippet Code	Description
idx	add an index to the table
idx-drop	drop the index from the table
order	sort the table by column(yes it indeed possible!)
table-rename	rename the table
table-charset	change table charset and collate
db-charset	change database charset and collate
fk	add foreign key to the table
-fk	add foreign key to the table with index
fk-drop	drop the foreign key from the table
uk	add unique key to exists table

Trigger

Snippet Code	Description
s-trig	create new trigger
s--trig	replace trigger(drop and create new)
s-trig-list	list triggers for table
s--trig-list	list triggers for table(also specify DB)
s-trig-drop	drop the trigger

Procedure

Snippet Code	Description
s-proc	create new storage procedure
s--proc	replace procedure(drop and create new)
s-proc-drop	drop the storage procedure
s-proc-list	show stored procedures list(only current database)

Function

Snippet Code	Description
s-func	create new function
s--func	replace function(drop and create new)
s-func-drop	drop the function
s-func-list	show list of user-defined functions(only current database)

Show

Snippet Code	Description
s-hcols	details of the table (`SHOW FULL COLUMNS FROM ...`)
s-hcreate	show command for creating the table
s-hidx	show indexes for the table
s-hrel	show relations of a table (using `information_schema`)

Constructions

Declare

Snippet Code	Description
s-dec	declare a variable
s--dec	declare a variable with default value
s-dec-s	declare a string(`VARCHAR`) variable
s--dec-s	declare a string(`VARCHAR`) variable with default value
s-dec-h	declare a `CONTINUE` `HANDLER` `FOR SQLSTATE`
s-dec-cur	declare a cursor

Condition

Snippet Code	Description
s-if	create `if` statement
s--if	create `if else` statement

Case

Snippet Code	Description
s-case	`CASE var_name WHEN 'value' THEN ... END CASE` - value based
s-case-w	`WHEN 'value' THEN ... ;` - value based
s-case-wb	`WHEN 'value' THEN BEGIN ... END;` - value based
s--case	`CASE WHEN var_name = 'value' THEN ... END CASE` - condition based
s--case-w	`WHEN var_name = 'value' THEN ... ;` - condition based
s--case-wb	`WHEN var_name = 'value' THEN BEGIN ... END;` - condition based

Loops

Snippet Code	Description
s-loop	`LOOP ... END LOOP` - Complex snippet. With additional logic!
s-repeat	`REPEAT ... UNTIL ... END REPEAT` construct
s--repeat	`REPEAT BEGIN ... END; UNTIL ... END REPEAT` construct