SQL Online Course

DescriptionCommand
To login SQL Server use -h only if needed.mysql -h hostname -u root -p
Create a database.create database [databasename];
Print all databases on the sql server. show databases;
Switch to a database.use [db name];
Create tablecreate table [ table name ]
To list all the tables in the db. show tables;
To list all the userselect user, host from mysql.user;
To list database’s field formats.describe [table name];
Delete a column.alter table [table name] drop column [column name];
Add a new column to db.alter table [table name] add column [new column name] varchar (20);
Change column name.alter table [table name] change [old column name] [new column name] varchar (50);
Make a unique column so you get no dupes.alter table [table name] add unique ([column name]);
Make a column bigger.alter table [table name] modify [column name] VARCHAR(3);
Delete unique from table.alter table [table name] drop index [colmn name];
Update table recodeupdate employees set last_name=’google’ where emp_id=1;
Switch to the mysql db. Create a new user. INSERT INTO [table name] (Host,User,Password) VALUES(‘%’,’user’,PASSWORD(‘password’));
Insert table recode ( data )insert into employees (emp_id,first_name,last_name,salary,contact) values (1,’test’,’1′,5000,6985674958);
Remove all record from a table permanentlytruncate table [ Table Name ];
To delete a db.drop database [database name];
To delete a table.drop table [table name];
To delete a column and row.delete from [ table name ] where emp_id (1,3);
To delete userdrop user [user name];
Delete a row(s) from a table.DELETE from [table name] where [field name] = ‘whatever’;
Print all data in a table.SELECT * FROM [table name];
List columns of specific Tableshow columns from [table name];
Show certain selected rows with the value “whatever”.SELECT * FROM [table name] WHERE [field name] = “whatever”;
SQL Logical Operators (AND) select * from [table_name] where first_name=’jhon’ and age=30;
SQL Logical Operators (OR) select * from [table_name] where first_name=’jhon’ or age=20;
SQL Logical Operators (NOT) select * from [table_name] where first_name=’jhon’ not age!=20;
Show all records containing the name “Bob” AND the phone number ‘3444444’.SELECT * FROM [table name] WHERE name = “Bob” AND phone_number = ‘3444444’;
Show all records not containing the name “Bob” AND the phone number ‘3444444’ order by the phone_number field.SELECT * FROM [table name] WHERE name != “Bob” AND phone_number = ‘3444444’ order by phone_number;
Show all records starting with the letters ‘bob’ AND the phone number ‘3444444’.SELECT * FROM [table name] WHERE name like “Bob%” AND phone_number = ‘3444444’;
Use a regular expression to find records. Use “REGEXP BINARY” to force case-sensitivity. This finds any record beginning with a.SELECT * FROM [table name] WHERE rec RLIKE “^a$”;
Show unique records.SELECT DISTINCT [column name] FROM [table name];
Show selected records sorted in an ascending (asc) or descending (desc).SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
Count rows.SELECT COUNT(*) FROM [table name];
1. Checks an attribute value within range
2. Checks an attribute value matches a given string pattern.
3. Checks an attribute value is NULL
4. Checks an attribute value matches any value within a value list
5. Limits values to unique values		SELECT * FROM [Table name] WHERE [column name] between 25 and 35;
SELECT * FROM [Table name] WHERE [column name] like ‘rock’;
SELECT * FROM [Table name] WHERE [column name] is null;
SELECT * FROM [Table name] WHERE [column name] in (20, 30, 35);
SELECT DISTINCT ‘[Column name]’ from employees;
1. Returns the average value from specified columns
2. Returns Number of table rows
3. Returns largest value among the records
4. Returns smallest value among the records
5. Returns the sum of specified column values		select avg([column]) from [table_name];
select count(*) from [table_name];
select min([column]) from [table_name];
select max([column]) from [table_name];
select sum([column]) from [table_name];
Join tables on common columns. select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
SQL comparison Operators – select * from [table_name] where first_name=’rocky’ and age <=30;
select * from [table_name] where first_name=’rocky’ and age >=30;
Change a users password.mysqladmin -u root -h hostname.blah.org -p password ‘new-password’
Change a users password.(from MySQL prompt).SET PASSWORD FOR ‘user’@’hostname’ = PASSWORD(‘passwordhere’);
Switch to mysql db.Give user privilages for a db.INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,’db’,’user’,’Y’,’Y’,’Y’,’Y’,’Y’,’N’);
To update info already in a table.UPDATE [table name] SET Select_priv = ‘Y’,Insert_priv = ‘Y’,Update_priv = ‘Y’ where [field name] = ‘user’;
Update database permissions/privilages.FLUSH PRIVILEGES;
Load a CSV file into a table.LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);
Restore database (or database table) from backup. [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3), officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
Create user commandcreate user “[user name]”@”[hostname]” identified by “[password]”;
Show all user listselect [user name],[host name] from mysql.user;
Give All privileges to usergrant all privileges on [database name].* to “[user name]”@”[hostname]”;
Withdraws access privileges given with the grant commandrevoke [privilege list] on [database name] from [user]
SQL Exporting one databasemysqldump -u [username] -p [database_name] > [output_file_path]
Dump all databases for backup. Backup file is sql commands to recreate all db’s.[mysql dir]/bin/mysqldump -u root -p password –opt >/tmp/alldatabases.sql
Dump a table from a database. mysqldump -c -u username -p password databasename tablename > /tmp/databasename.tablename.sql
SQL Importing one databasemysql -u [username] -p [database_name] < [Input_file_path]

1. How to Convert SQL Server.

Sql server

You can easily convert your SQL Database to the different platforms just flow a few steps.

Before convert SQL server we will require 3 software in your systems:
1. MSSQL Server Or Microsoft SQL Management Studio
2. XAMPP Server
3. Data Loader
4. Click here…

2. SQL Server queries, Create a Table, Database

SQL server command
  • What’s SQL Server ?
  • What’s SQL server Database ?
  • Types of SQL Server Data Models:
  • SQL Constraints
  • DDL – Data Definition Language.
  • DML – Date Manipulation Language
  • DCL – Data Control Language
  • TCL – Transaction Control
  • SQL Server Queries Example..
  • Click here…

3. SQL Operators -Equal, Like, Logical operators

SQL server command
  • SQL Operators – Filter
  • SQL Operators – Logical
  • SQL Operators – comparison
  • SQL Operators – Special
  • SQL Operators – Aggregations
  • Click here…