SQL

SQL notes

        
            
                Action
                SQL Server
                MySQL
            
            
                Logon
                
                mysql --user=user_name --password=user_password
mysql --user=user_name --password
mysql --host=host.yourdomain.com --user=user_name --password
            
            
                See connection status
                
                status;
            
            
                List databases
                
                show databases;
            
            
                Select database
                use database_name
                use database_name;
            
            
                List tables
                sp_help
                show tables;
show tables from database_name;
            
            
                Describe table
                sp_help table_name
                show tables from table_name;
describe tables_name;
            
            
                Display results horizontally
                
                select * from table_name \g;
            
            
                Display results vertically
                
                select * from table_name \G;
            
            
                Limit rows
                set rowcount 10
select * from table_name
                select * from table_name
limit 10;
            
            
                Skip rows
                
                select * from table_name
limit 1,10
            
            
                Run script from command line
                
                mysql --user=user_name --password < script.sql
            
            
                Run script from inside client
                
                source sript_filename
            
            
                Save output
                
                select * from table_name \T output_file;
            
            
                Create database
                
                create database database_name;
            
            
                Delete database
                
                drop database database_name
            
            
                Create table
                
                create table table_name (name varchar(50)) egine MyISAM;
            
            
                Prompt types
                
                -> waiting for next line of a command
'> waiting for next line of a string started with a single quote
"> waiting for next line of a string started with a double quote
`> waiting for next line of a string started with a back tick
/*> waiting for next line of a comment started with /*
            
            
                Cancelling a command line
                
                add \c to the end of the command line (no ;)
            
            
                Create user
                
                grant privileges on database.object to 'username@hostname' identified by 'password'
*.* all databases and all their objects
database.* only the database called database and all its objects
datbase.object only the database and its object called object
            
            
                Data types
                
                char(n)        exactly n bytes (<=255)
varchar(n)     up to n bytes (<=65535)
binary(n) or byte(n) exactly n bytes (<= 255) contains binary data
varbinary(n)   up to n bytes (<=65535) contains binary data
tinytext(n)    up to n bytes (<=255) trested as a string with a character set
text(n)        up to n bytes (<=65535) trested as a string with a character set
mediumtext(n)  up to n bytes (<=16777215) trested as a string with a character set
longtext(n)    up to n bytes (<=4294967295) trested as a string with a character set
tinyblob(n)    up to n bytes (<=255) treated as binary data - no character set
blob(n)        up to n bytes (<=65535) treated as binary data - no character set
mediumblob(n)  up to n bytes (<=16777215) treated as binary data - no character set
longblob(n)    up to n bytes (<=4294967295) treated as binary data - no character set
tinyint        1 byte                        -128 to 127
                                      (unsigned 0 to 255)
smallint       2 bytes                     -32768 to 32767
                                      (unsigned 0 to 65535)
mediumint      3 bytes                   -8388608 to 8388607
                                      (unsigned 0 to 16777215)
int or integer 4 bytes                -2147483648 to 2147483647
                                      (unsigned 0 to 4294967205)
bigint         8 bytes        -922337203684775808 to 922337203684775807
                                      unsigned (0 to 18446744073709551615)
float          4 bytes           -3.402823466E+38 to 3.402823466E+38
                                              no unsigned
double or real 8 bytes  -1.79769313486223157E+308 to 1.79769313486223157E+308
                                              no unsigned
datetime  '0000-00-00 00:00:00'
date      '0000-00-00'
timestamp '0000-00-00 00:00:00' (only 1970 to 2037)
time      '00:00:00'
year      0000 (only years 0000 and 1901-2155)

            

                Data type modifiers
                
                unsigned
not null
auto_increment
key (auto-increment )

            
            
                Rename table
                
                alter table classics rename pre1900;
            
            
                Change data type
                
                alter table classics modify year smallint;
            
            
                Add a column
                
                alter table classics add pages smallint unsigned;
            
            
                Rename a column
                
                alter table classics change type varchar(16);
            
            
                Remove a column
                
                alter table classics drop pages;
            
            
                Delete a table
                drop table classics
                drop table classics;
            
            
                Create an index
                
                alter table classics add index(author(20));
OR
create index author on classics author(20);
Note: 20 is number characters of the field used in the index
To use the full text ofthe field
alter table classics add fulltext(author);

                        
            
                Selecting data
                select * from classics
                select * from classics;
            
            
                Counting rows
                select count(*) from classics
                select count(*) from classics;
            
            
                Selecting distinct data
                select distinct author from classics
                select distinct author from classics;
            
            
                Deleting data
                delete from classics where title='Little Dorrit'
                delete from classics where title='Little Dorrit';
            
            
                Limiting results
                select top 10 * from classics
                select * from classics limit 10;
            
            
                match...against
                
                fulltext indexes can be used with match...against
to make case insensitive macthes against all the words in a column.
This can be a straight forward requiremnt that each word occurs in the text.
select * from classics where match(title) against('old shop');
OR in boolean mode that can exclude words
select * from classics where match(author) against('+charles -darwin');

            
            
                Updating data
                update classics set category into 'Classic Fiction' where category = 'Fiction'
                update classics set category into 'Classic Fiction' where category = 'Fiction';
            
            
                Order results
                select * from classics order by author, title
                select * from classics order by author, title;
            
            
                Grouping results
                select author, count(author) from classics group by author
                select author, count(author) from classics group by author;
            
            
                Joining tables
                select name,author,title from customers,classics where customer.isbn=classics.isbn
OR
select name,author,title from customers join classics on customer.isbn=classics.isbn;
                select name,author,title from customers,classics where customer.isbn=classics.isbn;
OR
select name,author,title from customers join classics on customer.isbn=classics.isbn;
OR to automatically join on columns that have the same name
select name,author,title from customers natural join classics;
            
            
                Using aliases
                
                select name,author,title from customers as cust,classics as class where cust.isbn=class.isbn;
            
            
                Transactions
                begin tran
SQL statements
commit tran
OR
rollback tran

                begin;
SQL statements
commit;
OR
rollback;
            
            
                Explain query plan
                
                explain select * from accounts where number='12345'
            
            
                Backing up
                
                Backup all databases
mysqldump -u user -ppassword --all-databases > all_databases.sql
OR
Backup a single database by shutting down down MySQL server (optional)
mysqldump -u user -ppassword publications > publications.sql
OR
backup selected table
lock tables publications.classics READ (optional)
mysqldump -u user -ppassword publications classics > classics.sql
unlock tables
            
            
                Restoring from backups
                
                Restore all databases
mysqldump -u user -ppassword < all_databases.sql
OR
restore a single database
mysqldump -u user -ppassword -D publications < publications.sql
OR
restore a single table
mysqldump -u user -ppassword -D publications < classics.sql

Home page

Action	SQL Server	MySQL
Logon		mysql --user=user_name --password=user_password mysql --user=user_name --password mysql --host=host.yourdomain.com --user=user_name --password
See connection status		status;
List databases		show databases;
Select database	use database_name	use database_name;
List tables	sp_help	show tables; show tables from database_name;
Describe table	sp_help table_name	show tables from table_name; describe tables_name;
Display results horizontally		select * from table_name \g;
Display results vertically		select * from table_name \G;
Limit rows	set rowcount 10 select * from table_name	select * from table_name limit 10;
Skip rows		select * from table_name limit 1,10
Run script from command line		mysql --user=user_name --password < script.sql
Run script from inside client		source sript_filename
Save output		select * from table_name \T output_file;
Create database		create database database_name;
Delete database		drop database database_name
Create table		create table table_name (name varchar(50)) egine MyISAM;
Prompt types		-> waiting for next line of a command '> waiting for next line of a string started with a single quote "> waiting for next line of a string started with a double quote `> waiting for next line of a string started with a back tick /> waiting for next line of a comment started with /
Cancelling a command line		add \c to the end of the command line (no ;)
Create user		grant privileges on database.object to 'username@hostname' identified by 'password' . all databases and all their objects database.* only the database called database and all its objects datbase.object only the database and its object called object
Data types		char(n) exactly n bytes (<=255) varchar(n) up to n bytes (<=65535) binary(n) or byte(n) exactly n bytes (<= 255) contains binary data varbinary(n) up to n bytes (<=65535) contains binary data tinytext(n) up to n bytes (<=255) trested as a string with a character set text(n) up to n bytes (<=65535) trested as a string with a character set mediumtext(n) up to n bytes (<=16777215) trested as a string with a character set longtext(n) up to n bytes (<=4294967295) trested as a string with a character set tinyblob(n) up to n bytes (<=255) treated as binary data - no character set blob(n) up to n bytes (<=65535) treated as binary data - no character set mediumblob(n) up to n bytes (<=16777215) treated as binary data - no character set longblob(n) up to n bytes (<=4294967295) treated as binary data - no character set tinyint 1 byte -128 to 127 (unsigned 0 to 255) smallint 2 bytes -32768 to 32767 (unsigned 0 to 65535) mediumint 3 bytes -8388608 to 8388607 (unsigned 0 to 16777215) int or integer 4 bytes -2147483648 to 2147483647 (unsigned 0 to 4294967205) bigint 8 bytes -922337203684775808 to 922337203684775807 unsigned (0 to 18446744073709551615) float 4 bytes -3.402823466E+38 to 3.402823466E+38 no unsigned double or real 8 bytes -1.79769313486223157E+308 to 1.79769313486223157E+308 no unsigned datetime '0000-00-00 00:00:00' date '0000-00-00' timestamp '0000-00-00 00:00:00' (only 1970 to 2037) time '00:00:00' year 0000 (only years 0000 and 1901-2155)
Data type modifiers		unsigned not null auto_increment key (auto-increment )
Rename table		alter table classics rename pre1900;
Change data type		alter table classics modify year smallint;
Add a column		alter table classics add pages smallint unsigned;
Rename a column		alter table classics change type varchar(16);
Remove a column		alter table classics drop pages;
Delete a table	drop table classics	drop table classics;
Create an index		alter table classics add index(author(20)); OR create index author on classics author(20); Note: 20 is number characters of the field used in the index To use the full text ofthe field alter table classics add fulltext(author);
Selecting data	select * from classics	select * from classics;
Counting rows	select count(*) from classics	select count(*) from classics;
Selecting distinct data	select distinct author from classics	select distinct author from classics;
Deleting data	delete from classics where title='Little Dorrit'	delete from classics where title='Little Dorrit';
Limiting results	select top 10 * from classics	select * from classics limit 10;
match...against		fulltext indexes can be used with match...against to make case insensitive macthes against all the words in a column. This can be a straight forward requiremnt that each word occurs in the text. select * from classics where match(title) against('old shop'); OR in boolean mode that can exclude words select * from classics where match(author) against('+charles -darwin');
Updating data	update classics set category into 'Classic Fiction' where category = 'Fiction'	update classics set category into 'Classic Fiction' where category = 'Fiction';
Order results	select * from classics order by author, title	select * from classics order by author, title;
Grouping results	select author, count(author) from classics group by author	select author, count(author) from classics group by author;
Joining tables	select name,author,title from customers,classics where customer.isbn=classics.isbn OR select name,author,title from customers join classics on customer.isbn=classics.isbn;	select name,author,title from customers,classics where customer.isbn=classics.isbn; OR select name,author,title from customers join classics on customer.isbn=classics.isbn; OR to automatically join on columns that have the same name select name,author,title from customers natural join classics;
Using aliases		select name,author,title from customers as cust,classics as class where cust.isbn=class.isbn;
Transactions	begin tran SQL statements commit tran OR rollback tran	begin; SQL statements commit; OR rollback;
Explain query plan		explain select * from accounts where number='12345'
Backing up		Backup all databases mysqldump -u user -ppassword --all-databases > all_databases.sql OR Backup a single database by shutting down down MySQL server (optional) mysqldump -u user -ppassword publications > publications.sql OR backup selected table lock tables publications.classics READ (optional) mysqldump -u user -ppassword publications classics > classics.sql unlock tables
Restoring from backups		Restore all databases mysqldump -u user -ppassword < all_databases.sql OR restore a single database mysqldump -u user -ppassword -D publications < publications.sql OR restore a single table mysqldump -u user -ppassword -D publications < classics.sql