Node Notes

SSH Notes

Show, Create, Drop and Use Database

Use SHOW DATABASES; to find out what databases exist on server.

mysql> SHOW DATABASES;



CREATE DATABASE creates a database with the given name.

CREATE DATABASE menagerie2;


 

DROP DATABASE Syntax

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE drops all tables in the database and deletes the database.

It will remove it permanently.

DROP DATABASE menagerie2;



USE Syntax
USE database name;
USE, like QUIT, does not require a semicolon
(You can terminate such statements with a semicolon if you like; it does no harm.)
The USE statement is special in another way,
too: it must be given on a single line.

To open a Database type "USE database name".
Set the default (current) (USE) database.
mysql> USE test

0
0
0
s2smodern

Entering Queries

A query is a question or a request.from a database for specific information and have a recordset returned.

Example of query below.

SELECT VERSION(), CURRENT_DATE;

 

Simple calculator.

SELECT 3+5;

SELECT (4+1)*5;

Statements on a single line.

SELECT VERSION(), NOW(), USER(), CURRENT_DATE;

Multiple-line statement.

SELECT
VERSION(),
NOW();

 

0
0
0
s2smodern

CREATE, INSERT INTO, SHOW Tables and DESCRIBE

Use a CREATE TABLE statement to specify the layout of your table:

CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE);

 

CREATE TABLE test_names (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
lastname VARCHAR(30) NOT NULL,
firstname VARCHAR(25)
);

 

INSERT INTO test_names (lastname, firstname)
VALUES ('Last','First');

Once you have created a table, SHOW TABLES should produce some output:

SHOW TABLES;

 

To verify that your table was created the way you expected, use a DESCRIBE statement:

DESCRIBE pet;

 

 

0
0
0
s2smodern

Loading Data into a Table

LOAD DATA LOCAL INFILE '/path to pet txt/pet.txt' INTO TABLE pet;

pet.txt

INSERT

When you want to add new records one at a time, the INSERT statement is useful.

INSERT INTO pet VALUE ('Puffball','Daiane','hamster','f','1999-03-30',NULL);

0
0
0
s2smodern

SELECT, WHERE, DISTINCT

SELECT Syntax

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

 

 

TheSELECTstatement is used to pull information from a table.

Selecting All Data

SELECT * FROM pet;

Fix only the erroneous record with anUPDATEstatement:

UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

Selecting Particular Rows

SELECT * FROM pet WHERE name = 'Bowser';

Specify conditions on any column:

SELECT * FROM pet WHERE birth >= '1998-1-1';

 

Combine conditions: query uses the AND logical operator.

SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';

 

Query uses the OR logical operator.
SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';

 

AND and OR may be intermixed, although AND has higher precedence than OR. If you use both operators, it is a good idea to use parentheses to indicate explicitly how conditions should be grouped:

SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-> OR (species = 'dog' AND sex = 'f');

Selecting Particular Columns

SELECT name, birth FROM pet;

SELECT owner FROM pet;

 

Minimize the output, retrieve each unique output record just once by adding the keyword DISTINCT:

SELECT DISTINCT owner FROM pet;

Use a WHERE clause to combine row selection with column selection

.
SELECT name, species, birth FROM pet
-> WHERE species = 'dog' OR species = 'cat';

 

SELECT name,
species,
sex,
birth,
death
FROM pet;

 

0
0
0
s2smodern

Sorting Rows (ORDER BY)

ORDER BY keyword is used to sort the data in a recordset.

To sort a result, use an ORDER BY clause

SELECT name, birth FROM pet ORDER BY birth;

The default sort order is ascending, with smallest values first.

To sort in reverse (descending) order, add the DESC keyword to the name of the column you are sorting by:

SELECT name, birth FROM pet ORDER BY birth DESC;

Sort on multiple columns;
Animal in ascending order; birth date within animal type in descending order.

SELECT name, species, birth FROM pet
ORDER BY species, birth DESC;

0
0
0
s2smodern

Date Calculations

TIMESTAMPDIFF() function


SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
FROM pet;

ORDER BY name

SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE())
AS age FROM pet ORDER BY name;

Sort the output by age rather than name, just use a different ORDER BY clause:

SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
FROM pet ORDER BY age;

 

Query can be used to determine age at death for animals that have died

SELECT name, birth, death,
TIMESTAMPDIFF(YEAR,birth,death) AS age
FROM pet WHERE death IS NOT NULL ORDER BY age;

Query that displays the value of both birth and MONTH(birth):

SELECT name, birth, MONTH(birth) FROM pet;

 

Finding animals with birthdays in the upcoming month 5 (May)

SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

There is a small complication if the current month is December. You cannot merely add one to the month number (12) and look for animals born in month 13, because there is no such month. Instead, you look for animals born in January (month 1).

You can write the query so that it works no matter what the current month is, so that you do not have to use the number for a particular month. DATE_ADD() enables you to add a time interval to a given date. If you add a month to the value of CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays:

A different way to accomplish the same task is to add 1 to get the next month after the current one after using the modulo function (MOD) to wrap the month value to 0 if it is currently 12:
MONTH() returns a number between 1 and 12. And MOD(something,12) returns a number between 0 and 11.
So the addition has to be after the MOD(), otherwise we would go from November (11) to January (1).

SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

 

 

0
0
0
s2smodern

Working with NULL Values

To test for NULL, use the IS NULL and IS NOT NULL operators

SELECT 1 IS NULL, 1 IS NOT NULL;

You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

0
0
0
s2smodern

Pattern Matching LIKE or NOT LIKE and REGEXP

SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters).

LIKE or NOT LIKE comparison

Find names beginning with 'b%':

SELECT * FROM pet WHERE name LIKE 'b%';

Find names ending with '%fy':

SELECT * FROM pet WHERE name LIKE '%fy';

Find names containing a '%w%'

SELECT * FROM pet WHERE name LIKE '%w%';

Find names containing exactly five characters, use five instances of the '_' pattern character:

SELECT * FROM pet WHERE name LIKE '_____';

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

The following list describes some characteristics of extended regular expressions:

“.” matches any single character.

A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.

“*” matches zero or more instances of the thing preceding it. For example, “x*” matches any number of “x” characters, “[0-9]*” matches any number of digits, and “.*” matches any number of anything.

A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)

To anchor a pattern so that it must match the beginning or end of the value being tested, use “^” at the beginning or “$” at the end of the pattern.

 

To find names beginning with “b”, use “^” to match the beginning of the name:

SELECT * FROM pet WHERE name REGEXP '^b';

To find names ending with “fy”, use “$” to match the end of the name:

SELECT * FROM pet WHERE name REGEXP 'fy$';

To find names containing a “w”, use this query:

SELECT * FROM pet WHERE name REGEXP 'w';

To find names containing exactly five characters, use “^” and “$” to match the beginning and end of the name, and five instances of “.” in between:

SELECT * FROM pet WHERE name REGEXP '^.....$';

 

 

0
0
0
s2smodern

Counting Rows (COUNT AND GROUP BY)

Counts the number of rows COUNT(*)

SELECT COUNT(*) FROM pet;

Counts how many pets each owner has:

SELECT owner, COUNT(*) FROM pet GROUP BY owner;

Use of COUNT() in conjunction with GROUP BY:

Number of animals per species:

SELECT species, COUNT(*) FROM pet GROUP BY species;

Number of animals per sex:

SELECT sex, COUNT(*) FROM pet GROUP BY sex;

Number of animals per combination of species and sex:

SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

 

 

When performed just on dogs and cats, looks like this:

SELECT species, sex, COUNT(*) FROM pet
WHERE species = 'dog' OR species = 'cat'
GROUP BY species, sex;

Or, if you wanted the number of animals per sex only for animals whose sex is known:

SELECT species, sex, COUNT(*) FROM pet
WHERE sex IS NOT NULL
GROUP BY species, sex;

0
0
0
s2smodern

SSL Checker

Server Hostname:

(e.g. www.google.com)
Check SSL

| |

Copyright © 2017. All Rights Reserved.

SiteLock