SQL DE EXEMPLO

/* acessar o console do msql de forma interativa*/
mysql –u root -p
/* criar um banco de dados*/
CREATE
DATABASE
senaiti08;
/* visualizar bancos de daos existentes*/
SHOW DATABASES;
/* Selecionar(abrir) um banco de dados */
USE senaiti08;
/* criar uma tabela*/
CREATE TABLE funcionarios (
codigo INTEGER NOT NULL PRIMARY KEY,
nome VARCHAR(40) NOT NULL,
 salario DECIMAL(10,2)
);
/* visualizar a estrutura de uma tabela*/
DESCRIBE funcionarios;
/* excluir banco de dados*/
DROP senaiti08;

CREATE TABLE cadfun (
codfun INTEGER NOT NULL PRIMARY KEY,
nome VARCHAR(40) NOT NULL,
depto CHAR(2) ,
funcao CHAR(20) ,
salario DECIMAL(10,2)
);

CREATE TABLE dependente (
coddep  INT(11) NOT NULL PRIMARY KEY,
nome VARCHAR(40) NOT NULL ,
idade CHAR(3),
graupar CHAR(20)
);

CREATE TABLE cargo (
codfun INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment 'código do cargo',
nomecargo VARCHAR(50) NOT NULL comment 'Nome do cargo',
codsetor INT NOT NULL comment 'Cod do setor'
);

ALTER TABLE cargo CHANGE
codfun cod INT(3) NOT NULL
AUTO_INCREMENT comment 'Código de cargo';

mysql> USE livraria;
DATABASE changed
mysql> DESCRIBE dependente;
+---------+-------------+------+-----+---------+-------+
| FIELD   | TYPE        | NULL | KEY | DEFAULT | Extra |
+---------+-------------+------+-----+---------+-------+
| coddep  | INT(11)     | NO   | PRI | NULL    |       |
| nome    | VARCHAR(40) | NO   |     | NULL    |       |
| idade   | CHAR(3)     | YES  |     | NULL    |       |
| graupar | CHAR(20)    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 ROWS IN SET (0.00 sec)

mysql> DESCRIBE cargo;
+-----------+-------------+------+-----+---------+----------------+
| FIELD     | TYPE        | NULL | KEY | DEFAULT | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| cod       | INT(3)      | NO   | PRI | NULL    | AUTO_INCREMENT |
| nomecargo | VARCHAR(50) | NO   |     | NULL    |                |
| codsetor  | INT(11)     | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 ROWS IN SET (0.01 sec)

mysql> ALTER TABLE dependente ADD datanasc DATE;
Query OK, 0 ROWS affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE dependente;
+----------+-------------+------+-----+---------+-------+
| FIELD    | TYPE        | NULL | KEY | DEFAULT | Extra |
+----------+-------------+------+-----+---------+-------+
| coddep   | INT(11)     | NO   | PRI | NULL    |       |
| nome     | VARCHAR(40) | NO   |     | NULL    |       |
| idade    | CHAR(3)     | YES  |     | NULL    |       |
| graupar  | CHAR(20)    | YES  |     | NULL    |       |
| datanasc | DATE        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 ROWS IN SET (0.02 sec)

mysql> ALTER TABLE dependente MODIFY COLUMN graupar CHAR(15);
Query OK, 0 ROWS affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE dependente;
+----------+-------------+------+-----+---------+-------+
| FIELD    | TYPE        | NULL | KEY | DEFAULT | Extra |
+----------+-------------+------+-----+---------+-------+
| coddep   | INT(11)     | NO   | PRI | NULL    |       |
| nome     | VARCHAR(40) | NO   |     | NULL    |       |
| idade    | CHAR(3)     | YES  |     | NULL    |       |
| graupar  | CHAR(15)    | YES  |     | NULL    |       |
| datanasc | DATE        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 ROWS IN SET (0.01 sec)

mysql>
ALTER TABLE dependente MODIFY COLUMN INSERT INTO dependente (cooddep, nome) VALUES (2, 'João');
Enter password:
Welcome TO the MySQL monitor.  Commands END WITH ; OR \g.
Your MySQL connection id IS 5
Server version: 5.5.8-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle AND/OR its affiliates. ALL rights reserved.

Oracle IS a registered trademark OF Oracle Corporation AND/OR its
affiliates. Other names may be trademarks OF their respective
owners.

TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the CURRENT INPUT statement.

mysql> INSERT INTO (coddep, nome) VALUES (2, joao);
ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that
corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near '(codd
ep, nome) values (2, joao)' at line 1
mysql> ALTER TABLE dependente MODIFY COLUMN
    -> INSERT INTO (cooddep, nome)
    -> VALUES (2, João);
ERROR 1046 (3D000): No DATABASE selected
mysql> USE livraria;
DATABASE changed
mysql> DESCRIBE dependente;
+----------+-------------+------+-----+---------+-------+
| FIELD    | TYPE        | NULL | KEY | DEFAULT | Extra |
+----------+-------------+------+-----+---------+-------+
| coddep   | INT(11)     | NO   | PRI | NULL    |       |
| nome     | VARCHAR(40) | NO   |     | NULL    |       |
| idade    | CHAR(3)     | YES  |     | NULL    |       |
| graupar  | CHAR(15)    | YES  |     | NULL    |       |
| datanasc | DATE        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 ROWS IN SET (0.01 sec)