Learning basic MySQL - examples, syntax and descriptions reference

learning.sql

# used to create a database
CREATE DATABASE your_database_name;

# switches context to the database you specify
USE your_database_name;

# lists the available databases
SHOW databases;

#lists the available tables in the database you've switched to.
SHOW tables;

# Shows columns and attributes
EXPLAIN your_database_name;

# databses are made of tables
# tables are made up of columns and rows (records - each record is a single entry - intersect between a column and row is reffered to as a field.)

# creating tables  tables must have at least 1 col - with at least a name and a datatype attribute
CREATE TABLE table_name(
		col_name varchar(100),
		col2_name integer
);

#Datatypes : 
varchar(100)  		# string - variable character - the number is the upper limit for the number of charazcters allowed
					# in this field. - mxa number of char 65000 (deceptive) 
text				# string - no theoretical upper list
integer				# basic whole numbers
integer unsigned 	# integer without negative numbers
	auto_increment	# can only be gi ven primary keys 
BIGINT
MEDIUMINT
SMALLINT
TINYINT
decimal(precision, scope) # precision is total number of digits, scope is how many are allowed after the decimal point
date 				# YYYY-MM-DD
time				# [H]HH:MM:SS
datetime			# YYYY-MM-DD HH:MM:Shows
boolean				# true or false - 1 / 0  - any number other than 0 is true

# default values - put the keywork 'default' after the datatype
#properties - NULL  / NOT NULL - current_timestamp for datetime - or 0 or 1 for boolean - defaults do not have to be set/


# putting Data into tables. - basic - must use same order as columns in existing table
INSERT INTO table_name VALUES(
			"varchar string",
			4
);

# better way - ALLOWING COLUMN ORDER and INSERTING INTO A PARICULAR COLUMN OR SET OF COLUMNS
INSERT INTO table_name(col2_name, col_name) VALUES (
			4
			"varchar string"
);

#getting data out of the table. - * - all
SELECT * FROM table_name;

# select specific columns - order is user definable.
SELECT col1_name, col2_name FROM table_name;


#primary Keys - every table should have a primary keya column that uniquely identifies each row,it can never be null, must be set on record creation and never changed.

# add a primary key column like this  -  inside the columns on table creation

CONSTRAINT constraint_name_pk PRIMARY KEY (id)

# -- there is a debate between using 'natural' and 'surogate' keys.  auto incrementing surogate pk make the most sense in most cases


# ALTERING tables
ALTER TABLE table_name RENAME new_table_name;

# ALTER - add a col
ALTER TABLE table_name ADD col_name datatype attributes;

# ALTER - removing a column
ALTER TABLE table_name DROP col_name;

# ALTER  - change col
ALTER TABLE table_name CHANGE old_col_name new_col_name new_type;

# adding a primary key col after the fact
ALTER TABLE table_name ADD id INT NOT NULL;
# - will need to update values then
ALTER TABLE table_name ADD PRIMARY KEY (id);
#add auto increment prop
ALTER TABLE table_name CHANGE id id NOT NULL AUTO_INCREMENT;

#deleting
DELETE FROM table_name;  # empties table
DROP TABLE table_name; # deletes tabes


# UPDATE - the whole col - notcie the opperator
UPDATE table_name SET col_name = col_name * 24;

# selecting specific recordcs
SELECT col_name FROM table_anme WHERE col_name="specific_data";
# WHERE a specific condition is true note that we use te same operater for setting and reading a value


# logical operators
=  is
!= is not
<> is not
< is less than
> is greater than
>= greater than or equal to
<= less than or equal to
AND keywork - allows us to chain
OR keywork
IN  (array of values)
BETWEEN  value AND value;
LIKE  - SELECT * FROM table_name WHERE 	col_name LIKE "C%"; # starts with a C and has any letters after it
NOT LIKE

#example of UPDATE and WHERE
UPDATE table_name SET col_name = "some_value"  WHERE col_name = "old_value";
DELETE FROM table_name WHERE col_name col_name = "some_value";

# limit returned records
SELECT * FROM table_name LIMIT 3; 

# limit returned records but dont count from 0
SELECT * FROM table_name LIMIT 3 OFFSET 5; 

# filter duplicates
SELECT DISTINCT col_name FROM table_name;

# ordering results
SELECT * FROM table_name ORDER BY col_name;

SELECT * FROM table_name ORDER BY col_name ASC;

SELECT * FROM table_name ORDER BY col_name DESC;

#ordering by multiple criteria
SELECT * FROM table_name ORDER BY col_name, secondary_col_to_sort_by;