10-04-2017, 08:31 PM
[attachment=6761]
Basic commands in MySQL
Create, List, Use and Delete Databases Commands
create database
This command is used to create a Database on the sql server..
Syntax: create [db name];
Eg:
create employees;
show databases
This command id used to list all databases on the sql server.
Syntax: show databases;
use database
This command is used to switch to a database.
Syntax: use [db name];
drop database
This command is used to delete a database.
Syntax: drop [db name];
Creating tables and working with it
create tabe
This command is used to create tables in a database.
Eg:
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));
show tables
This command is see all the tables in the database.
describe table
To see database's field formats.
describe [table name];
drop table
To delete a table.
drop table [table name];
Show all data in a table.
SELECT * FROM [table name];
Returns the columns and column information pertaining to the designated table.
show columns from [table name];
Show certain selected rows with the value "something".
SELECT * FROM [table name] WHERE [field name] = "something";
Show all records containing the name "Telson" AND the phone number '2255'.
SELECT * FROM [table name] WHERE name = "Telson" AND phone_number = '2255';
Show all records not containing the name "Telson" AND the phone number '2255' order by the phone_number field.
SELECT * FROM [table name] WHERE name != "Telson" AND phone_number = '2255' order by phone_number;
Show all records starting with the letters 'Tel' AND the phone number '2255'.
SELECT * FROM [table name] WHERE name like "Tel%" AND phone_number = '2255';
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 [column1],[column2] FROM [table name] ORDER BY [column2] DESC;
Return number of rows.
SELECT COUNT(*) FROM [table name];
Sum column.
SELECT SUM(*) 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;