Creating a Database Schema in MySQL Workbench

Storage: AI

"Auto Increment"

Storage: G

"Generated"

Storage: NN

"Not Null"

Storage: PK NN UQ B UN ZF AI G

"Primary Key" "Not Null" "Unique" "Binary" "Unsigned" "Zero Fill" "Auto Increment" "Generated"

Storage: UQ

"Unique Index"

Storage: UN

"Unsigned"

Storage: ZF

"Zero Fill"

Storage: B

"is binary column"

Storage: PK

'Belongs to Primary Key' MUST be PK NN UQ AI

delete the following record: SELECT * FROM users WHERE users_id = 199;

*BEGIN;* *DELETE* FROM users WHERE users_id = 199; *ROLLBACK;*

What is a schema

A schema is a workspace for our database that allows us to group all of our data together.

make a new schema

CREATE SCHEMA 'my_new_schema' ;

INSERT INTO guides(guides_revenue, guides_title, guides_users_id, guides_qty) VALUES( 500, 'Guide by Jon', *Jon's ID is unknown* , 300);

INSERT INTO guides(guides_revenue, guides_title, guides_users_id, guides_qty) VALUES( 500, 'Guide by Jon', *(SELECT users_id FROM users* * WHERE users_name = 'Jon' LIMIT 1)* , 300);

streamline using WHERE IN SELECT * FROM addresses WHERE addresses_city = 'Queens' OR addresses_city = 'Manhattan';

SELECT * FROM addresses WHERE addresses_city IN ('Queens', 'Manhattan');

select records: 1000<guides_revenue<5000 SELECT * FROM guides;

SELECT * FROM guides *WHERE guides_revenue BETWEEN 1000 AND 5000;*

search in guides for word "my" in the title SELECT * FROM guides;

SELECT * FROM guides *WHERE guides_title* *LIKE '%My%'*;

list all *unique* guide titles (no repetition) SELECT guides_title FROM guides ;

SELECT *distinct* guides_title FROM guides;

list all guide titles in alphabetical order (with repetition) SELECT guides_title FROM guides ;

SELECT guides_title FROM guides *ORDER BY guides_title DESC;*

run for greatest value instead of known value SELECT guides_title, guides_revenue FROM guides WHERE guides_revenue = ( *1500* );

SELECT guides_title, guides_revenue FROM guides WHERE guides_revenue = ( SELECT MAX(guides_revenue) FROM guides );

query all by column(s) to return user name & email

USE devcamp_sql_course_schema; SELECT users_name, users_email FROM users;

insert query

USE devcamp_sql_course_schema; INSERT INTO users(users_name, users_email) VALUES ("replacedname","[email protected]"); --users_id is Auto Increment

query all

USE devcamp_sql_course_schema; SELECT * FROM users;

UPDATE users SET users_email = '[email protected]' WHERE users_id = 2;

update email for users_id = 2 to '[email protected]'