Skip to content

Instantly share code, notes, and snippets.

@edoves edoves/SQL_basics.sql
Last active Jun 12, 2019

Embed
What would you like to do?
-- command to create DATABASE
CREATE DATABASE aliendatabase;
-- Before you can create the table inside the database, you need to make sure you’ve got our new database selected. Enter the command
USE aliendatabase;
-- command for creating username and password of specific data base
GRANT ALL PRIVILEGES ON db_name.*
TO 'username'@'localhost'
IDENTIFIED BY 'password';
-- SQL command that creates a new table CREATE TABLE All the other stuff is detailed information about what kinds of data can be stored in the table.
CREATE TABLE table_name (
-- collation or character set
first_name varchar(30),
last_name varchar(30),
when_it_happened varchar(30),
how_long varchar(30),
how_many varchar(30),
alien_description varchar(100),
what_they_did varchar(100),
fang_spotted varchar(10),
other varchar(100),
email varchar(50)
);
-- CREATE TABLE subjects (
id INT(11) NOT NULL AUTO_INCREMENT,
menu_name VARCHAR(255),
position INT(3),
visible TINYINT(1),
PRIMARY KEY (id)
);
-- Example of creating tables from lynda.com
CREATE TABLE (
id INT(11) NOT NULL AUTO_INCREMENT,
menu_name VARCHAR(30) NOT NuLL,
position INT(3) NOT NULL,
visible TINYINT(1) NOT NULL,
PRIMARY KEY (id)
);
-- This is basically will show us what table looks like
SHOW COLUMNS FROM table_name;
-- creatin another table on a database that can connect to other table
CREATE TABLE pages (
id INT(11) NOT NULL AUTO_INCREMENT,
subjects_id INT(11) NOT NULL, -- Foreign key
menu_name VARCHAR(30) NOT NULL,
position INT(3) NOT NULL,
visible TINYINT(1) NOT NULL,
content TEXT,
PRIMARY KEY (ID),
INDEX (subjects_id) -- index is going to tell mysql that it should also created index for it for past look ups
);
-- creatin another table on a database that can connect to other table
CREATE TABLE pages (
id INT(11) NOT NULL AUTO_INCREMENT,
subject_id INT(11) NOT NULL, -- Foreign key
menu_name VARCHAR(30) NOT NULL,
position INT(3) NOT NULL,
visible TINYINT(1) NOT NULL,
content TEXT,
PRIMARY KEY (ID)
);
-- foreign key needs to have an index
ALTER TABLE pages ADD INDEX fk_subject_id (subject_id);
--Ralational
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (1, 'Globe Bank', 1, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (1, 'History', 2, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (1, 'Leadership', 3, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (1, 'Contact Us', 4, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (2, 'Banking', 1, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (2, 'Credit Cards', 2, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (2, 'Mortgages', 3, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (3, 'Checking', 1, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (3, 'Loans', 2, 1);
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (3, 'Merchant Services', 3, 1);
-- Select key word for reading the data into the data base
-- Select all of the data for customers with a first name of Martin:
SELECT * FROM email_list WHERE first_name = 'Martin'
-- Select only the last name for customers with a first name of Bubba:
SELECT last_name FROM email_list WHERE first_name = 'Bubba'
-- Select the first name and last name for the customer with an email address of ls@objectville.net.
SELECT first_name, last_name FROM email_list WHERE email = 'ls@objectville.net'
-- Select all of the columns for customers with a first name of Amber and a last name of McCarthy:
-- The WHERE clause can be made dependent on multiple pieces of information, in this case a match for both a first name AND a last name.
SELECT * FROM email_list WHERE first_name = 'Amber' AND last_name = 'McCarthy'
-- The INSERT TO keyword is use to CREATe data into the data base
INSERT INTO table_name (column_name1, column_name2, ... ) VALUES ('value1', 'value2', ... )
INSERT INTO movies (title, year) VALUES ('Avatar', 2009), ("Avatar 2", NULL )
-- Another way of inserting data into the table using the SET keyword
INSERT INTO movies SET title = "Back to the future", year = 1985
-- Updating data into the data base using the UPDATE keyword with the SET keyword example below:
UPDATE subjects SET visible = 1 WHERE id = 4;
UPDATE movies SET year = 2015 WHERE title = 'Avatar 2'
-- updating multiplae values
--bedore the year is 2015 ansd he tite was Avatar 2 with this code it will update the data into the database
UPDATE movies SET year = 2016, title = "Avatar Reloaded" WHERE title = 'Avatar 2'
-- DDeleting data into the database with keyword DELETE
-- IT will delete everting from the table
DELETE FROM table_name;
-- Delete the data on the specific column on the table
DELETE FROM movies WHERE title = "Avatar Reloaded " AND year = 2016
-- Maniulating Schema with the use of DDL or Data Definition Language;
--reanme the tabl movies to movie_table
RENAME TABLE movies TO movie_table;
-- If you want to change the two table in one go this is how you do it
RENAME TABLE movies TO movie_table, actors TO actor_table;
-- This the command for remove/delete the table
DROP TABLE table_name;
-- Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
TRUNCATE TABLE table_name;
-- Rename the database
-- ways to read from the database
$query = "SELECT * FROM users";
while($row = mysqli_fetch_assoc($result) ) {
echo '<pre>';
print_r( $row );
echo '</pre>';
}
--Create data from form nd insert to the database
$query = "INSERT INTO users( username, password ) ";
$query .= "VALUES ( '$username', '$password' )";
-- other way in doing the connection to the database using an array and foreach loop
$db['db_host'] = 'localhost';
$db['db_user'] = 'edwin_cms';
$db['db_pass'] = 'msDYw6w37aqx9EnV';
$db['db_name'] = 'edwin_cms';
foreach ($db as $key => $value) {
define(strtoupper($key), $value);
}
------------------------------------------------------------------------
$connection = mysqli_connect( DB_HOST, DB_USER, DB_PASS, DB_NAME );
-- PHP to MySQL connection
-- 1. Create a DATABASE connection
$dbhost = 'localhost';
$dbuser = 'widget_cms';
$dbpass = 'secretpassword';
$dbname = 'widget_corp';
$connection = mysqli_connect( $dbhost, $dbuser, $dbpass, $dbname );
-- Test if Connection occured.
if( mysqli_connect_errno() ) {
die( 'Database connection failed: ' . mysqli_connect_error() . ' (' . mysqli_connect_errno() . ')' );
}
-- another way for creatign database connection using array and contant variable
$db['db_host'] = 'localhost';
$db['db_user'] = 'root';
$db['db_pass'] = '';
$db['db_name'] = 'cms';
-- loop through the array and make the key big letters o define on constant
foreach( $db as $key => $value ) {
define(strtoupper( $key), $value );
}
$connection = mysqli_connect( DB_HOST, DB_USER, DB_PASS, DB_NAME );
if( $connection ) {
echo 'We are connected';
}
-- 2. Perform daabase query
$query = 'SELECT * FROM subjects';
-- Test to see if the query is succeded or not
$result = mysqli_query ($connection, $query );
-- test to see if the query is succeded or not
if( !$result ) {
die( 'Database query failed.');
}
-- 3 use returned data (if any) yu can ut this code on the inside the body html
while ( $row = mysqli_fetch_row($result) ) {
-- output data from eah row
var_dump($row);
echo '</hr>';
}
-- 4 realeas the retrun data
mysqli_free_result($result);
// 5. Closer database connection
mysqli_close( $connection );
-- creating records in to the data base
$query = "INSERT INTO subjects (";
$query .= " menu_name, position, visible";
$query = ") VALUES (";
$query = " '{$menu_name}', {$position}, {$visible}"; -- curly brase is optional
$query = ")";
$result = mysqli_query ($connection, $query );
--$result check the query more details information
if($result) {
-- success
-- redirect_to(somepage.php)
echo 'Success!!!';
} else {
--failure
die( 'Database query failed.' . mysqli_error($connection));
}
-- UPDATE records in to the data base
$query = "UPDATE subjects SET ";
$query .= "menu_name = {$menu_name}', ";
$query = "position = {position}, ";
$query = "visible = {$visible} "; -- curly brase is optional
$query = "WHERE id = {$id}";
-- SQL function that ignore the some special characters like the '
$username = mysqli_real_escape_string( $connection, $username );
$password = mysqli_real_escape_string( $connection, $password );
-- testing the query
if (!$create_category) {
die("QUERY FAILED" . mysqli_error($connection));
} }
-- note: we are going to redirect user to a certain page
-- <?php ob_start(); ?> is a output buffering. putting this to a header.php is to turn on your output buffering
-- header("Location: ../index.php"); header() function dependent on the output buffering function
-- This is the basic SQL statement to create a table.
CREATE TABLE actors(name VARCHAR(50)); -- actors is the table name and inside the parenthesis is the olumns definition
-- Table with two columns
CREATE TABLE movies(title VARCHAR(200), year INTEGER);
-- Insert date into the movies table
INSERT INTO movies VALUES("Avatar",2009) ;
#Retrieving a Result Set
SELECT * FROM movies; # Select all or show all from movies table
SELECT movies.title, movies.year FROM movies;
SELECT movies.year, movies.title FROM movies; #alter the oder of the column by switching the year and title
SELECT year, title FROM movies; #alter the oder of the column by switching the year and title
SELECT title, year FROM movies; #alter the oder of the column by switching the year and title
SELECT title FROM movies; #Show only the title column
#Querying Tables
SELECT * FROM movies WHERE year = 1999; #show only movies that has adate of 1999
SELECT * FROM movies WHERE year != 1999; #show moveis that not include movies from 1999
SELECT * FROM movies WHERE year > 1999; # get movies after year 1999
SELECT * FROM movies WHERE year >= 1999; #get movies from 1999 to the present time
SELECT * FROM movies WHERE year < 1999; #get movies before 1999
SELECT * FROM movies WHERE year <= 1999; # get movies before and in the year 1999
SELECT * FROM movies WHERE year = 1999 AND title = "The Matrix"; #get the movies from the year 199 and the title is the matrix
SELECT * FROM movies WHERE year = 1998 OR year = 2000; # get the movies from year 1998 2000 only
SELECT * FROM movies WHERE year BETWEEN 1998 AND 2000; # get the movies from year 1998 up to year 2000
#### if you want perform a basic search over a string by using part of a sting you can us the keyword LIKE with a combination of wildcards.####
SELECT * FROM movies WHERE title LIKE "godfather"; #get or look to all movies in the database that has the string of "godfather" in the title.
SELECT * FROM movies WHERE title LIKE "%godfather"; # use a wild card in the string. a wild card is a special character that can be substitued by any number of characters. A wildcard can be placed anywere in the string.
SELECT * FROM movies WHERE title LIKE "%godfather%"; # A wildcard can be placed anywere in the string. in SQL a wild card is the percent "%" symbol
#Ordering the Result Set
SELECT * FROM movies ORDER BY year;
SELECT * FROM movies ORDER BY year DESC;
SELECT * FROM movies ORDER BY year ASC;
SELECT * FROM movies ORDER BY year ASC, title DESC;
#Limiting the Result Set
SELECT * FROM movies LIMIT 10; # show the 10 Result
SELECT * FROM movies LIMIT 10 OFFSET 1; # showing 10 result but starting tio the second row ofsetting ht first row
SELECT * FROM movies LIMIT 10 OFFSET 0; # show the 10
SELECT * FROM movies LIMIT 10 OFFSET 10; # showing 10 result but start in row 11
SELECT * FROM movies LIMIT 10 OFFSET 20; # showing 10 result but stari t00 20th row
SELECT * FROM movies LIMIT 10 OFFSET 250;
SELECT * FROM movies LIMIT 20, 10; # should start at the 21sr row. and bring back only 10 results sets.
SELECT * FROM movies LIMIT 0, 1000;
SELECT * FROM actors LIMIT 0, 1000;
#Dealing with NULL
SELECT * FROM movies WHERE year IS NULL;
SELECT * FROM movies WHERE year IS NOT NULL ORDER BY year;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.