Subscribe
Duration : 1 Year
Author : mysql Tutor
Price : $20
Tecwallet Funds available: $0
LogIn Forgot password? Create account
OR
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')
SELECT * FROM sample WHERE city NOT IN ('Delhi', 'London') ORDER BY city
Its not just about lists, but whenever you put NOT before any testing condition, it simply reverses the test
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 * FROM sample
WHERE WeeklyExpenses > (
SELECT AVG(WeeklyExpenses) FROM sample (placeholder for 4650 – Average Weekly Expenses)
)
ORDER BY WeeklyExpenses DESC
SELECT name FROM sample WHERE state = "AZ“
SELECT name, address, city, state, zip, MIN(WeeklyExpenses)
FROM sample
WHERE name IN
(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 sample.id, sample.name, companyinfo.Company
FROM sample
LEFT OUTER JOIN companyinfo
ON sample.id = companyinfo.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("94","Saler Ltd.")
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
)
CREATE TABLE accounts(
userid int,
username varchar(30),
password varchar(20),
PRIMARY KEY(id)
)
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
CREATE VIEW highestWeeklyExpenses AS
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
Comments ( To Post Your Comment Please Login First! )