SELECT <firstColumnName>, <secondColumnName>, …. FROM <tableName>

Eg: SELECT * FROM sample

Eg: SELECT DISTINCT city FROM sample

Eg: SELECT id, name, city FROM sample LIMIT 5

Eg: SELECT id, name FROM sample LIMIT 5, 10

Eg: SELECT tableName.columnName FROM tableName

Eg: SELECT name FROM sample ORDER BY name

Eg: SELECT name FROM sample ORDER BY name DESC

Eg:

SELECT id, name FROM sample WHERE id=54

SELECT id, name FROM sample WHERE name = ‘Deep’

SELECT name, city FROM sample WHERE city = ‘Delhi’ OR city = ‘London’

SELECT name, city FROM sample WHERE city = "New York" AND country = "USA“

  SELECT name, state FROM sample WHERE state = 'CA' OR state = 'NC' or state = 'NY‘

  SELECT name, state FROM sample WHERE state IN ('CA', 'NC', 'NY')

Lets see an example how wildcard searches are made using SQL: 

SELECT address FROM sample WHERE address LIKE "%Drive"

SELECT * FROM sample WHERE city LIKE "n%s"

SELECT * FROM sample WHERE zip LIKE "441__"

Its more like a language itself.

Eg: SELECT * FROM sample WHERE name REGEXP "john"

So if you search for "."

Eg: SELECT * FROM sample WHERE zip REGEX ".11"

Another one: | (pipe symbol): 

Eg: SELECT * FROM sample WHERE address REGEXP "drive|street"

One more: sets

Sets are typed inside square brackets. 

Eg: SELECT * FROM sample WHERE address REGEXP "[0123456789] Pineview Drive"

Eg: SELECT name, CONCAT(city, ', ', state) FROM sample

Now to give the new column a name: 

Eg: SELECT name, WeeklyExpenses, WeeklyExpenses + 20 AS NewWeeklyExpenses FROM sample

Available mathematical operators:

  + -> Add

  - -> subtract

  * -> Multiply

  / -> Divide

Eg:

UPPER() -> This function takes name of a column and it upper cases everything passed on to its parenthesis

SELECT name, UPPER(name) AS NameInCaps FROM sample

Eg: AVG() function gives you a single output for an entire column of data passed to it.

Eg: We wanted to figure out the average weekly expenses of a person for a particular city

SELECT AVG(WeeklyExpenses) FROM sample WHERE city =

"New York“

SELECT COUNT(name) FROM sample WHERE state="CA"

  SELECT AVG(WeeklyExpenses) FROM sample WHERE state = "CA"

  SELECT COUNT(name) AS people_count,
AVG(WeeklyExpenses) AS avg_weekly_expense,
MAX(WeeklyExpenses) AS max_expense,
MIN(WeeklyExpenses) AS min_expense,
state
FROM sample
WHERE state = "CA“

MAX() and MIN() return the maximum and minimum from a given column.

  SELECT city, COUNT(*) as people_count FROM sample GROUP BY city HAVING COUNT(*) >= 2

  SELECT city, COUNT(*) AS people_count FROM sample GROUP BY city ORDER BY people_count DESC

SELECT AVG(WeeklyExpenses) FROM sample

SELECT name FROM sample WHERE state = "AZ“

  SELECT sample.id, name, Company
FROM sample, companyinfo
WHERE sample.id = companyinfo.id

For Column: column1 AS c1

For Table: table1 AS t1

Here's an example query:

  SELECT s.id, s.name, s.address, c.Company
FROM sample AS s, companyinfo as c
WHERE s.id = c.id

  SELECT s.id, s.name, c.Company FROM companyinfo as c, sample as s WHERE s.id = c.id

SELECT * FROM sample WHERE WeeklyExpenses >= 1500

SELECT * FROM sample WHERE state="CA“

SELECT * FROM sample WHERE WeeklyExpenses >= 1500

UNION

SELECT * FROM sample WHERE state="CA"

  ALTER TABLE ADD FULLTEXT()

  SELECT name, address FROM sample WHERE Match(name) Against("john")

  INSERT INTO companyinfo(id,Company) VALUES
("95","Helter Inc"),
("96","Rebecca LLC"),
("97","Junior Inc")

UPDATE companyinfo
SET Company = "Mastercard Inc“
WHERE id=97

  UPDATE sample SET name="xxx", address="xxx" WHERE id=15

DELETE FROM companyinfo WHERE id="97“

  CREATE TABLE (
ColumnType(INT,VARCHAR,etc) MaxStorageSize
ColumnType(INT,VARCHAR,etc) MaxStorageSize
)

  userid int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(userid)

  CREATE TABLE devices(
devid int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
department varchar(20) NULL,
PRIMARY KEY(devid)
)

  ALTER TABLE <tableName> ADD <columnName> <dataType for table>

  ALTER TABLE accounts
ADD firstname varchar(15)

  ALTER TABLE accounts
DROP COLUMN firstname

  DROP TABLE <tableName>

  DROP TABLE accounts

RENAME TABLE <currentTableName> TO <newTableName>

  SELECT id,name, phone, WeeklyExpenses FROM sample ORDER BY WeeklyExpenses DESC LIMIT 20

  $dbhost = "localhost"; //database to which the connection is to be setup

  $user = "testuser"; //username

  $pass = "test"; //password for the user $user

  $db = "tecwallet"; //database to which we want to connect

  $link = mysqli_connect($dbhost , $user, $pass) or die("Unable to Connect!" . mysqli_error($link));

  mysqli_select_db($link, $db);

  $query = "SELECT * FROM sample" or die("Error in the consult.." . mysqli_error($link));

  $result = mysqli_query($link, $query);

  while($row = mysqli_fetch_array($result)) {

  echo $row["name"] . " lives at " .   $row["address"] . " " . $row["city"] . " " .   $row["country"] . "<br/>";

  }

  1. Inserting data directly using PHP code

  2. Inserting data by mode of a form created using HTML form tag

  1. Inserting data directly using PHP code

  2. Inserting data by mode of a form created using HTML form tag

  1. Inserting data directly using PHP code

  2. Inserting data by mode of a form created using HTML form tag