Sql schemas: Difference between revisions
From wikinotes
No edit summary |
|||
Line 11: | Line 11: | ||
+-- database | +-- database | ||
+-- database | +-- database | ||
+-- table | |||
+-- table | |||
+-- row | |||
+-- row | |||
</syntaxhighlight> | </syntaxhighlight> | ||
</blockquote><!-- Overview --> | </blockquote><!-- Overview --> |
Latest revision as of 18:34, 19 September 2021
The structure of your database is referred to as your schema.
Overview
Your schema may define multiple databases,
which may have multiple tables,
which have rows of data.schema +-- database +-- database +-- table +-- table +-- row +-- row
Databases
CREATE DATABASE [IF NOT EXISTS] pets_db # Create Database SHOW DATABASES; # Show All Databases USE pets_db; # Select database to work with DROP DATABASE pets_db; # Delete Database
Tables
CREATE
CREATE TABLE users ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(10), last_name VARCHAR(10), age TINYINT );ALTER
ALTER TABLE users RENAME TO employees;DROP
DROP TABLE users;
Columns
ALTER TABLE pet ADD COLUMN alive INT; # Add Column ALTER TABLE pet ADD COLUMN alive INT AFTER dead; # Add Column, and choose placement ALTER TABLE pet MODIFY alive dead INT; # Rename Column/modify datatype ALTER TABLE pet DROP COLUMN alive; # Delete Column
Indexes
Rows can be indexed for faster lookup times.
Combinations of row-values must be unique if an index is created on multiple rows.CREATE INDEX my_index ON my_table (first_name, last_name); SHOW INDEX FROM table;