Section 1
Preview this deck
SELECT * FROM cats WHERE age=4;
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (68)
Section 1
(50 cards)
SELECT * FROM cats WHERE age=4;
intro where function
SELECT CONCAT_WS ( '-', title, autho_fname, authoer_lname FROM table
shortcut to insert break between every
SELECT UPPER('Hello World'); SELECT LOWER('Hello World');
pick the case upper or lower
DEFUALT
auto insert if nothing provided
SELECT released_year FROM books ORDER BY released_year DESC;
how to ordre descending
SELECT released_year FROM books ORDER BY released_year;
how to order ascending
WHERE clause
choose specific data in a column
UPDATE
what table
source filename.sql
run saved queries in
show databases;
list available databses
SHOW COLUMNS FROM tablename; DESC tablename;
See details of a created table
SELECT * FROM cats WHERE name='Egg';
intro where funtion not case sensitive
source folder/file.sql
make sure to go into correct source
ORDER BY
A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order).
DROP DATABASE database_name;
delete a database
SET
the change you want to make
mysql-ctl start;
Start Sql cloud 9
WHERE
data you want to change
SELECT DISTINCT ...... FROM ......;
use distinct function properly
mysql-ctl stop;
Stop sql shell cloud 9
SELECT ----
choose columns in table, use comma to seperate for multiple columsn
CREATE TABLE cats3 ( name VARCHAR(20) DEFAULT 'no name provided', age INT DEFAULT 99 );
setting default code
distinct
use with select, gets rid of duplicates, only unique ttiles
UPDATe
altering exisitng data
INSERT INTO table_name(column_name) VALUES (data);
insert data into a created table
CHAR LENGTH
counts the characters
SELECT CONCAT ( SUBSTRING(title, 1, 10), '...' ) AS 'short title' FROM books;
CREATE TABLE unique_cats ( cat_id INT NOT NULL, name VARCHAR(100), age INT, PRIMARY KEY (cat_id) );
primer keys
INSERT INTO table_name (column_name, column_name) VALUES (value, value), (value, value), (value, value);
insert multiple pieces of data into a table
USE <database name>;
choose the database you want to work with
aliases -- "AS **'
change the column name when displayed
SELECT DISTINCT author_fname, author_lname FROM books;
connecting two columns and using distinct
SELECT SUBSTRING(REPLACE(title, 'e', '3'), 1, 10) AS 'weird string' FROM books;
SUBSTRING (column , 1-5) AS 'short title'
select specific character to pick out to display
CREATE DATABASE database_name;
general command to create a database
NOT NULL
makes a field required and not be null
SELECT author_fname AS first, author_lname AS last, CONCAT(author_fname, ', ', author_lname) AS full FROM books;
CONCAT (author_fname,' space', author_lname 'text FROM table
combine with text and add data
INT
used for whole numbers
SELECT REPLACE('Hello World', 'Hell', '%$#@');
^CCtrl-C--exit!
quit out of cloud 9 shelll
VARCHAR (#)
used for text/ # of character
SELECT CHAR_LENGTH('Hello World');
counts characters
CREATE TABLE unique_cats2 ( cat_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), age INT, PRIMARY KEY (cat_id) );
auto increment adding a table
SELECT REVERSE('Hello World');
show everything backwards
CREATE TABLE tablename ( column_name data_type, column_name data_type );
How to create a table in a database
DROP TABLE <tablename>;
delete a table
mysql-ctl cli;
Start sql shell cloud 9
DELETE
use intsead of select to delete stuff
SELECT CONCAT (x,y,z)
combine column names together
Section 2
(18 cards)