O SQL e MYSQL Comandos Básicos

Basic SQL Commands

examples from Beginning MySQL

by

Robert Sheldon & Geoff Moes

You should then see the MySQL prompt

Starting MySQL

To end your MySQL session use the quit command

On the course server enter the command mysql

mysql>

mysql> quit;

Creating the database • CREATE DATABASE <database name>;

• CREATE DATABASE username;

• On the course server you have only been  granted permission to create a database  whose name is your username.

Using a database

• USE <database name>;

• USE username;

• DROP <database name>;

• DROP username;

Deleting a database

• DROP DATABASE [IF EXISTS] <databasename>; • DROP DATABASE username;

• This deletes the database and all tables and  contents. Use with caution.

Backus Naur Form (BNF) Notation

Create Table

<table definition>::=

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] <table name> (<table element> [{, <table element>}...])

[<table option> [<table option>...]]

<table element>::=

<column definition>

| {[CONSTRAINT <constraint name>] PRIMARY KEY (<column name> [{, <column name>}...])}

| {[CONSTRAINT <constraint name>] FOREIGN KEY [<index name>] (<column name> [{, <column name>}...]) <reference definition>} | {[CONSTRAINT <constraint name>] UNIQUE [INDEX] [<index name>] (<column name> [{, <column name>}...])}

| {{INDEX | KEY} [<index name>] (<column name> [{, <column name>}...])} | {FULLTEXT [INDEX] [<index name>] (<column name> [{, <column name>}...])}

Backus Naur Form (BNF) Notation Create Table (cont)

• <column definition>::=

• <column name> <type> [NOT NULL | NULL] [DEFAULT <value>] [AUTO_INCREMENT] • [PRIMARY KEY] [COMMENT '<string>'] [<reference definition>]

• <type>::=

• <numeric data type>

• | <string data type>

• | <data/time data type>

• <reference definition>::=

• REFERENCES <table name> [(<column name> [{, <column name>}...])] • [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT }] • [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT }] • [MATCH FULL | MATCH PARTIAL]

• <table option>::=

• {ENGINE = {BDB | MEMORY | ISAM | INNODB | MERGE | MYISAM}} • | <additional table options>

Basic MySQL Data Types  Real Text FLOAT CHAR (<length>)

 DOUBLE VARCHAR (<maxlength>)

 DOUBLE PRECISION REAL

Integer TINYINT	DECIMAL DEC
SMALLINT
MEDIUMINT
INT
INTEGER
BIGINT
	NUMERIC
	FIXED

(PartID INT NOT NULL,

PartName VARCHAR(40) NOT NULL, CatID INT NOT NULL,

Create Table

Example

CREATE TABLE Parts

PRIMARY KEY (PartID)

);

Special Note

• If you are using Putty you can copy & paste  the SQL commands from the PowerPoint  slides into MySQL.

PartID PartName CatiIDTABLE Parts

Backus Naur Form (BNF) Notation

Inserting elements

<insert statement>::=

INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO]

{<values option> | <set option> | <select option>}

<values option>::=

<table name> [(<column name> [{, <column name>}...])]

VALUES ({<expression> | DEFAULT} [{, {<expression> | DEFAULT}}...]) [{, ({<expression> | DEFAULT} [{, {<expression> | DEFAULT}}...])}...]

<set option>::=

<table name>

SET <column name>={<expression> | DEFAULT}

[{, <column name>={<expression> | DEFAULT}}...]

<select option>::=

<table name> [(<column name> [{, <column name>}...])]

<select statement>

Inserting elements

INSERT INTO Parts  

(PartID, PartName, CatID)  

VALUES  

(1001,'Guy wire assembly',503),  

(1002,'Magnet',504);

INSERT INTO Parts  

VALUES  

(1003,'Regulator',505);

 PartName CatiID

 Guy wire assembly 503 Magnet 504 Regulator 505 Brushes 504

TABLE Parts

PartID 1001	Generator Dump load system Power assembly	506 506 501
1002
1003
1004
1006
1006
1007

Backus Naur Form (BNF) Notation