MySQL:
-> MySQL is an open-source and most popular relational database management system (RDBMS)
-> It is a database system used on the web and is ideal for both small and large applications.
-> It is very fast, reliable, and easy to use
-> It uses standard SQL
-> It compiles on a number of platforms ( AIX, BSDi, FreeBSD, HP-UX, eComStation, i5/OS, IRIX, Linux, OS X, Microsoft Windows, NetBSD, Novell NetWare,
OpenBSD, OpenSolaris, OS/2 Warp, QNX, Oracle Solaris, Symbian, SunOS, SCO OpenServer, SCO UnixWare, Sanos and Tru64)
-> MySQL is developed, distributed, and supported by Oracle Corporation.
-> MySQL is named after co-founder Monty Widenius’s daughter: My
-> The data in a MySQL database are stored in tables.
MySQL server installation:
Ubuntu:
———-
sudo apt-get install mysql-server
Editing the configuration:
Edit the /etc/mysql/my.cnf file to configure the basic settings.
starting the Server:
sudo service mysql restart
Fedora:
———-
yum install mysql-server
Setting Password when old password is empty:
mysqladmin -u root password ‘admin123′
Setting Password when old password is Not empty:
mysqladmin -u root -p’oldpass’ password ‘newpass’
To Start server:
service mysqld restart
To start client:
mysql -h localhost -u root -padmin123
SQL:
-> SQL is a standard language for accessing databases.
-> SQL stands for Structured Query Language.
-> SQL is an ANSI (American National Standards Institute) standard.
-> SQL is the standard database language for Relation Database Systems (like MySQL, MS Access, Oracle, Sybase, Informix, postgres and SQL Server).
Relation Database:
-> A database is a means of storing information in such a way that information can be retrieved from it.
-> In simplest terms, a relational database is one that presents information in tables with rows and columns. Relational tables follow certain integrity
rules to ensure that the data they contain stay accurate and are always accessible.
-> It is a database management system (DBMS) that is based on the relational model as introduced by E.F.Codd.
-> Row/Record: A record, also called a row of data, is each individual entry that exists in a table.
SQL commands:
CREATE DATABASE:
-> The SQL CREATE DATABASE statement is used to create new SQL database.
Syntax:
CREATE DATABASE DatabaseName;
eg:
mysql> CREATE DATABASE testDB;
To show list of databases:
mysql> SHOW DATABASES;
-> Database files are created in location “/var/lib/mysql”.
DROP DATABASE:
-> The SQL DROP DATABASE statement is used to drop(delete) an existing database in SQL schema.
eg:
DROP DATABASE DatabaseName;
USE:
-> When there are multiple databases in SQL Schema, then before starting operation, we need to select a database where all the operations would be performed.
-> The SQL USE statement is used to select any existing database in SQL schema.
Syntax:
USE DatabaseName;
CREATE TABLE:
-> The SQL CREATE TABLE statement is used to create a new table.
-> Creating a basic table involves naming the table and defining its columns and each column’s data type.
Syntax:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
…..
columnN datatype,
PRIMARY KEY( one or more columns )
);
eg:
SQL> CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
DESC:
-> To show table with details.
eg:
SQL> DESC CUSTOMERS;
DROP TABLE:
-> DROP TABLE statement is used to remove a table definition and all data, indexes, triggers, constraints, and permission specifications for that table.
Syntax:
DROP TABLE table_name;
eg:
SQL> DROP TABLE CUSTOMERS;
SELECT:
-> The SELECT statement is used to select data from a database.
-> The result is stored in a result table, called the result-set.
Syntax:
SELECT column_name,column_name
FROM table_name;
or
SELECT * FROM table_name;
DISTINCT:
-> The DISTINCT keyword can be used to return only distinct (different) values.
-> In a table, a column may contain many duplicate values; and sometimes we only want to list the different (distinct) values.
Syntax:
SELECT DISTINCT column_name,column_name
FROM table_name;
eg: SELECT DISTINCT City FROM Customers;
WHERE:
-> The WHERE clause is used to extract only those records that fulfill a specified criterion.
Syntax
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
eg:
SELECT * FROM Customers WHERE Country=’Mexico’;
SELECT * FROM Customers WHERE CustomerID=1;
SELECT * FROM Customers WHERE Country=’Germany’ AND City=’Berlin’;
SELECT * FROM Customers WHERE City=’Berlin’ OR City=’München’;
SELECT * FROM Customers WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);
Operators in The WHERE Clause:
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
ORDER:
-> The ORDER BY keyword is used to sort the result-set by one or more columns.
-> The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
Syntax:
SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
eg:
SELECT * FROM Customers ORDER BY Country;
SELECT * FROM Customers ORDER BY Country DESC;
SELECT * FROM Customers ORDER BY Country ASC, CustomerName DESC;
Here, Result will be ordered in ascending with country column and then among the already ordered country column entries are descending ordered with CustomerName column.
GROUP BY:
-> The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups.
-> The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Syntax:
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
eg:
SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
GROUP BY NAME;
INSERT:
-> The INSERT INTO statement is used to insert new records in a table.
Syntax:
INSERT INTO table_name
VALUES (value1,value2,value3,…);
INSERT INTO table_name (column1,column2,column3,…)
VALUES (value1,value2,value3,…);
eg:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES (‘Cardinal’,’Tom B. Erichsen’,’Skagen 21′,’Stavanger’,’4006′,’Norway’);
-> We can insert a new row, but only insert data in the specified columns “CustomerName”, “City”, and “Country”. Remaining values will be updated with ‘NULL’/its specified values.
eg:
INSERT INTO Customers (CustomerName, City, Country) VALUES (‘Cardinal’, ‘Stavanger’, ‘Norway’);
UPDATE:
-> The UPDATE statement is used to update existing records in a table.
Syntax:
UPDATE table_name
SET column1=value1,column2=value2,…
WHERE some_column=some_value;
eg:
UPDATE Customers SET ContactName=’Alfred Schmidt’, City=’Hamburg’ WHERE CustomerName=’Alfreds Futterkiste’;
DELETE:
-> The DELETE statement is used to delete rows in a table.
Syntax:
DELETE FROM table_name
WHERE some_column=some_value;
eg:
DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’ AND ContactName=’Maria Anders’;
-> Delete all Data from table:
DELETE FROM table_name;
or
DELETE * FROM table_name;
Note: Be very careful when deleting records. You cannot undo this statement.
LIKE:
-> The SQL LIKE clause is used to compare a value to similar values using wildcard operators.
-> There are two wildcards used in conjunction with the LIKE operator:
i.The percent sign (%)
ii.The underscore (_)
-> The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.
Syntax:
SELECT FROM table_name
WHERE column LIKE ‘XXXX%’
or
SELECT FROM table_name
WHERE column LIKE ‘%XXXX%’
or
SELECT FROM table_name
WHERE column LIKE ‘XXXX_’
eg:
WHERE SALARY LIKE ‘200%’ Finds any values that start with 200.
WHERE SALARY LIKE ‘%200%’ Finds any values that have 200 in any position.
WHERE SALARY LIKE ‘_00%’ Finds any values that have 00 in the second and third positions.
vijayababuj 