Managing Databases
A database system instance is a single executing copy of a database system.
- personal computers usually run one instance of a database system
- clouds usually run multiple instances of a database system
- each instance usually contains multiple system and user databases
Production database is a database available to end users for normal, day-to-day use.
Database SQL Commands
Create Database
CREATE DATABASE statement creates a new database.
- To create a database:
CREATE DATABASE SampleScriptsAlter Database
ALTER DATABASE statement is used to change a database.
- After creating a database, may need to change it using
ALTERcommand - Example:
ALTER DATABASE [SampleScripts] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SampleScripts] SET ANSI_NULLS OFF
GODelete Database
DROP DATABASE DatabaseName deletes a database, including all tables in the database.
Use Database
USE DatabaseName selects a default database for use in subsequent statements.
View Databases
The SHOW statement provides information about databases, tables, and columns:
SHOW DATABASESlists all databases in the database system instanceSHOW TABLESlists all tables in the default database- optional clause
FROM DatabaseNamelists tables in a named database
- optional clause
SHOW COLUMNS FROM TableNamelists all columns in the TableName table of the default databaseSHOW CREATE TABLE TableNameshows theCREATE TABLEstatement for the TableName table of the default database