SQL

What is SQL??

Stands for Structured Query Language.In short it is a language that is used to communicate with the database engine to insert/update/delete/view records from within its stuctures. It is the standard language for relational database management systems(RDMBS).
 * 1) Installing mysqld - downloading repo and installing mysql-community-edition-server
 * 2) /usr/bin/mysql_secure/installation - temporary password for root is in /var/log/mysqld
 * 3) mysql -u root -p mysql - log into mysql server with root account on mysql

Creating user with password - CREATE USER 'testuser@localhost' IDENTIFIED BY 'SecretQ!12';

Grant privileges for user to database -  GRANT ALL on dbtest.* to 'testuser@'localhost';

Grant privileges with passsword - grant all privileges on dbTest.* to testuser@'localhost' IDENTIFIED by 'Qwerty!2' with grant option; Creating database - CREATE DATABASE dbTest:

Dropping database - DROP DATABASE dbTest;

Showing database - SHOW DATABASES;

Using database - USE dbCustomerInfo;

Showing tables - SHOW TABLES;

Creating tables - CREATE TABLE tblCustomerInfo(custInfoFirstName varchar(50),custInfoLastName varchar(50), custInfoAddr1 varchar(50), custInfoAddr2 varchar(50), custInfoCity varchar(50), custInfoState varchar(10), custInfoZipCode varchar(10), custInfoPhone varchar(12));

CREATE TABLE nameofTAble (name_of_column datatype(size));

DROPPING TABLE - DROP TABLE tblTest;

Selecting data from table - SELECT * FROM name_of_table;

Selecting fields(name of columns) - SHOW FIELDS FROM tblCustomerIDInfo;

Creating table with primary key - CREATE TABLE tblCustomerIDInfo(custID varchar(10) PRIMARY KEY,custInfoFirstName varchar(50),custInfoLastName varchar(50), custInfoAddr                                                                0), custInfoState varchar(10), custInfoZipCode varchar(10), custInfoPhone varchar(12));

Deleting - DELETE FROM tblCustomerInfoBkup WHERE custInfoLastName='Jones';

Create table with primary key and auto_increment - CREATE TABLE tblInvoices(invID int(11) AUTO_INCREMENT PRIMARY KEY, invCustName varchar(50),invNumberItems int(11));

Inserting - INSERT INTO tblCustomerInfo (custInfoFirstName,custInfoLastName,custInfoAddr1,custInfoAddr2,custInfoCity,custInfoState,custInfoZipCode,custInfoPhone) VALUES ('John','Smith','111 Main St',,'AnyTown','NY','43211','214242424');

Copying from one table to another - INSERT INTO tblCustomerInfoBkup SELECT * FROM tblCustomerInfo;

Adding field to the table - ALTER TABLE tblCustomerInfoBkup ADD custInfoDOB varchar(10);

Modifying data type in the table field. - ALTER TABLE tblCustomerInfoBkup MODIFY custInfoDOB year;

Modyfing data type in the column - ALTER TABLE tblVendors MODIFY COLUMN vendorName varchar(50) NOT NULL;

Adding column - ALTER TABLE tblAcctExec ADD COLUMN execPhoneNumber INT NOT NULL; Deleting inserted data - DELETE FROM tblCustomerInfoBkup WHERE custInfoLastName='Jones'; CREATING INDEX - CREATE INDEX indexCustomInfoNames ON tblCustomerIDInfo(custInfoFirstName,custInfoLastName);

DROPING INDEX - ALTER TABLE tblCustomerIDInfo DROP INDEX tblCustInfoID;

Truncating database(cleaning out all the records) - TRUNCATE TABLE name_of_table;

Creating table with auto-increment field - CREATE TABLE tblEmpInfo(empID int PRIMARY KEY AUTO_INCREMENT, empLastName varchar(50),empSSN varchar(11)); - by default increment by 1

Altering number of increment - ALTER TABLE tblEmpInfo AUTO_INCREMENT=1000;

Alterning the field to support autoincrement - ALTER TABLE tblCustomerIDInfo MODIFY custID int AUTO_INCREMENT; Changing character - alter table accounts convert to character set utf8 collate utf8_general_ci; Updating entry - UPDATE accounts SET full_name = 'Dariusz ąść" WHERE preffered_email = 'dchrzascik@dd.com'; COUNT NUMBER of records - SELECT COUNT(* or column name,constrain) FROM tblCustomerInfo;

How many is distinct(unique) records - SELECT COUNT(DISTINCT custInfoLastName) FROM tblCustomerInfo;

How many we have records where is some info = SELECT COUNT(custInfoState) FROM tblCustomerInfo WHERE custInfoState='NY';

AVERAGE NUMBER From colums - SELECT AVG(invNumberItems) FROM tblInvoices;'SUM - SELECT SUM(invNumberItems) FROM tblInvoices; 

Three STATEMENTS in one row - SELECT COUNT(*),AVG(invNumberItems),SUM(invNumberItems) FROM tblInvoices; 

SEARCHING Information :

1)SELECT * FROM tblCustomerInfo WHERE custInfoLastName='Smith';'

2) LIKE OPERATOR - SELECT * FROM tblCustomerINFO WHERE custInfoLastName LIKE '%S'; - end with S letter 3) SELECT * FROM tblCustomerInfo WHERE custInfoLastName LIKE 'S%'; - start with S letter'

4) SELECT * FROM tblCustomerInfo WHERE custInfoLastName LIKE '%mit% - in the middle have mit'

5) SELECT COUNT(*) FROM tblCustomerInfo WHERE custInfoLastName LIKE '%S'; - count number with S letter end

CREATING VIEW - CREATE VIEW viewOverTwenty AS SELECT COUNT(*),AVG(invNumberItems),SUM(invNumberItems) FROM tblInvoices WHERE invNumberItems > 20;

INNER JOIN - combine rows from multiple tables - returns rows when there is a match in both tables.

SELECT tblOrders.ordID,tblCustomerIDInfo.custInfoLastName,tblCustomerIDInfo.custInfoFirstName,tblOrders.ordDate FROM tblOrders INNER JOIN tblCustomerIDInfo ON tblOrders.ordID=tblCustomerIDInfo.custID;

LEFT JOIN - returns all rows from the left table, even if there are no matches in the right table.

SELECT  tblCustomerIDInfo.custInfoLastName,tblOrders.ordID FROM tblCustomerIDInfo LEFT JOIN tblOrders ON tblCustomerIDInfo.custID=tblOrders.custID ORDER BY tblCustomerIDInfo.custInfoLastName;

RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.

SELECT tblOrders.ordID,tblCustomerIDInfo.custInfoLastName FROM tblOrders RIGHT JOIN tblCustomerIDInfo ON tblOrders.custID=tblCustomerIDInfo.custID ORDER BY tblOrders.ordID;

FULL JOIN: returns rows when there is a match in one of the tables.

SELECT tblOrders.ordID,tblCustomerIDInfo.custInfoLastName FROM tblCustomerIDInfo FULL OUTER JOIN tblOrders ON tblOrders.custID=tblOrders.custID;

SELECT tblCustomerIDInfo.custID, tblCustomerIDInfo.custInfoLastName,tblCustomerIDInfo.FirstName FROM tblCustomerIDInfo UNION SELECT * FROM tblOrders;

Sorting records

- SELECT * FROM tblCustomerIDInfo ORDER BY custID ASC LIMIT 1; ascending order, limit to one record

- SELECT * FROM tblCustomerIDInfo ORDER BY custInfoLastName DESC limit 2;

Minimum and Maximum Values

-SELECT MIN(custID) FROM tblCustomers;

-SELECT MAX(custID) from tblCustomers;

Upper and Lower Case Conversion

SELECT LCASE(custInfoFirstName),UCASE(custInfoLastName) FROM tblCustomerIDInfo;

Now function

SELECT prodID,prodName,prodDescc,prodPrice,Now AS prodPriceDate from tblProducts;