J7U79xEa

1-- creating database with its constraints
2create database cadastro default character set utf8 default collate utf8_general_ci;
3
4-- defining database to use
5use cadastro;
6
7-- creating table with constraints
8create table pessoas (
9	id int not null auto_increment, -- defining primary key
10	nome varchar(30) not null, -- at most 30 chars
11    nascimento date,
12    sexo enum('M', 'F'),
13    peso decimal(5,2),
14    altura decimal(3,2),
15    nacionalidade varchar(20) default 'Brasil', -- setting default value
16    primary key(id) -- setting up primary key
17) default charset utf8;
18
19-- inserting data
20insert into pessoas values(default, "Aurelio", "1997-11-29", "M", "60.0", "1.73", "Brasil");
21insert into pessoas values(default, "Omac", "2000-5-1", "M", "90.0", "1.80", "Brasil");
22
23-- querying tuples
24select * from pessoas;
25
26-- adding new column
27alter table pessoas add column profissao varchar(10); -- it'll always be at end of table
28
29-- removing column
30alter table pessoas drop column profissao;
31
32-- adding column in a specif position (AFTER A COLUMN)
33alter table pessoas add column profissao varchar(10) after nome;  -- will add after name
34
35-- adding column in a specif position (AT FIRST POSITION)
36alter table pessoas add column codigo int first;
37
38-- changing column
39alter table pessoas modify column profissao varchar(20); -- now is varchar(20)
40  
41-- changing column name
42alter table pessoas change column profissao prof varchar(20); -- changed profissao to prof
43 
44-- rename table
45alter table pessoas rename to gafanhotos;
46
47-- describing
48describe gafanhotos;
49 
50 -- creating new table
51create table if not exists cursos (
52	nome varchar(30) not null unique, -- unique is not primary key
53    descricao text,
54    carga int unsigned,
55    totaulas int unsigned,
56	ano year default "2017"
57) default charset = utf8;
58
59-- add primary key to table cursos
60alter table cursos add column idcurso int first; -- first add a column to be primary
61alter table cursos add primary key (idcurso); -- now set that column as primary key
62
63-- how to delete tables
64create table if not exists testtable( age int); -- creating a table to drop
65drop table testtable; -- dropping table