SQL Commands

SQL is a DATABASE computer Language designed for retrieval and management of data of a relative database. SQL stand for Standard Query Language

Before starting SQL is a standard query language is a computer language used for manipulating , storing and retrieval of stored data in a relational database. All ralational database systems are like MYSql, MS Access, ORACLE, informis,Sybase ans SQL server use SQL as standard database language.

Here We will learn SQL Commands:

Login(from unix shall) : #[mysql dir]/bin/mysql -h hostname -u root -p

Create a Database in SQL Server : Create Database [DATABASE NAME];

List all database in SQL Server : mysql> show database;

Switch in Database : mysql> use [DATABASE NAME];

To see all the tables in database: mysql>show tables;

To see database field format: mysql>describe[table name];

To delete a DB: mysql> drop [DATABASE NAME];

To delete a table: mysql> drop [table name];

To see all data in a table: mysql> SELECT * FROM [table name];

Show the use of WHERE:
mysql> SELECT * FROM [table name] WHERE [Condition]

Show all the records containing the name "Bob" and cell number "3654":
mysql> SELECT* FROM [table name] WHERE name="Bob" and Cell_number="3654";

Use regular expression to find record:
mysql> SELECT * FROM [table name] WHERE rec RLIKE '^a';

Show unique record: mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected record in ascending(acc) or descending (desc) order:
 mysql> SELECT [col 1, col 2] FROM [table name] ORDER BY [col 1] DESC;

Return number of rows: mysql>SELECT COUNT (*) FROM [table name];

Sum column: mysql> SELECT SUM(*) FROM [table name];

To update info already in table: mysql> UPDATE [table name] SET [value] WHERE [condition];

Delete row from database: mysql> DELETE FROM [table name] WHERE [field name] ='whatever';

Update database permission: mysql> flush privileges;

Delete column : mysql> ALTER table [table name] DROP column [column name];

Add a new column to db:
mysql> 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];

Make a column: mysql> ALTER table [table name] modify [column name] VARCHAR(3);


Join tables on common columns:
mysql> 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;

Creating a new user, login as root
#mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host, username, password) VALUES ('%', 'Username', PASSWORD('password'));
mysql> flush privileges;

Change a user password from unix shell:
#[mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change a user password using mysql prompt. Login as root
#mysql -u root -p
mysql> SET PASSWORD user@hostname =PASSWORD ('Password');
mysql>flush privileges;

Recover mysql root password. Stop the Mysql server
#/etc/init.d/mysql stop
#mysqld_safe--skip-grant-table &
#mysql -u root
mysql>use mysql;
mysql> update user set password=PASSWORD('newrootpassword') WHERE USER='root';
mysql> flush privileges;
mysql>quit
#/etc/init.d/mysql stop
#/etc/init.d/mysqlstart

Set a root password if there is no root password: #mysqladmin -u root password 'newpassword '

Update root password: #mysqladmin -u root -p 'oldpassword' 'newpassword'

Load CSV file into table:
mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY' ,' LINES TERMINATED BY '\n' (field1, field2, field3);

Dump all database for backup:
#[mysql dir]/bin/mysqldump -u root -ppassword --opt>/tmp/alldatabases.sql

Dump one database for backup
#[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename>/tmp/databasename.sql;

Dump a table from database:
#[mysql dir]/bin/mysqldump -u username -ppassword --databases databasename tablename>/tmp/databasename.tablename.sql;

Create a table statement:
mysql> CREATE table [table name] (personid int(50) not null auto_increament primary key, first_name varchar(35), middle_name varchar(20), lastname varchar(20));

Create Index: CREATE INDEX [index name] ON [table name];