Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;
-
Upload
nicholas-newman -
Category
Documents
-
view
222 -
download
1
Transcript of Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;
![Page 1: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/1.jpg)
Bases de Datos 1
Prof. Daniel Obando Fuentes
![Page 2: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/2.jpg)
CREANDO UNA BASE DE DATOS
CREATE DATABASE dbname;
![Page 3: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/3.jpg)
ENCODINGS
•Character encoding: Es un repertorio de caracteres que permiten representar un determinado lenguaje. •Tambien llamados character set, character map o codeset.•Cada caracter puede contener una cantidad diferente de bytes, permitiendo asi ampliar el rango de caracteres a representar.
![Page 4: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/4.jpg)
ENCODINGS
•US-ASCII, code unit 7 bits.•UTF-8, code unit 8 bits.•EBCDIC, code unit 8 bits.•UTF-16, code unit 16 bits.•UTF-32, code unit 32 bits.
![Page 5: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/5.jpg)
CHARSETS Y COLLATIONS
A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.
Suppose that we have an alphabet with four letters: “A”, “B”, “a”, “b”. We give each letter a number: “A” = 0, “B” = 1, “a” = 2, “b” = 3. The letter “A” is a symbol, the number 0 is the encoding for “A”, and the combination of all four letters and their encodings is a character set.
Suppose that we want to compare two string values, “A” and “B”. The simplest way to do this is to look at the encodings: 0 for “A” and 1 for “B”. Because 0 is less than 1, we say “A” is less than “B”. What we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): “compare the encodings.” We call this simplest of all possible collations a binary collation.
But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters “a” and “b” as equivalent to “A” and “B”; (2) then compare the encodings. We call this a case-insensitive collation. It is a little more complex than a binary collation.
![Page 6: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/6.jpg)
CHARSETS Y COLLATIONS
In real life, most character sets have many characters: not just “A” and “B” but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules, not just for whether to distinguish lettercase, but also for whether to distinguish accents (an “accent” is a mark attached to a character as in German “Ö”), and for multiple-character mappings (such as the rule that “Ö” = “OE” in one of the two German collations).
![Page 7: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/7.jpg)
CUAL ES EL MEJOR ENCODING?
Depende UTF8 soporta la mayoría de lenguajes
occidentales y versiones simplificadas de lenguajes orientales.
Unicode para Mandarin
![Page 8: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/8.jpg)
ALTER DATABASE
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
![Page 9: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/9.jpg)
CREATE TABLE
CREATE TABLE table_name(column_name1 data_type(size),column_name2 data_type(size),column_name3 data_type(size),....);
![Page 10: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/10.jpg)
ALTER TABLE
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
![Page 11: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/11.jpg)
PERSONS
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
![Page 12: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/12.jpg)
DROP
• DROP TABLE table_name• /* limpiar */• TRUNCATE TABLE table_name • DROP DATABASE database_name
![Page 13: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/13.jpg)
CONSTRAINTS
CREATE TABLE table_name(column_name1 data_type(size) constraint_name,column_name2 data_type(size) constraint_name,column_name3 data_type(size) constraint_name,....);
![Page 14: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/14.jpg)
CONSTRAINTS
NOT NULL – Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique
value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures
that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value when specified none for this
column
![Page 15: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/15.jpg)
NOT NULL
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
![Page 16: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/16.jpg)
UNIQUE
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
![Page 17: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/17.jpg)
UNIQUE (COMPAT)
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)
![Page 18: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/18.jpg)
UNIQUE CON ALTER
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
![Page 19: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/19.jpg)
PRIMARY KEY
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
![Page 20: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/20.jpg)
PRIMARY KEY (COMPAT)
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
![Page 21: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/21.jpg)
PK CON ALTER
ALTER TABLE PersonsADD PRIMARY KEY (P_Id)
![Page 22: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/22.jpg)
FOREIGN KEY
CREATE TABLE Orders(O_Id int NOT NULL PRIMARY KEY,OrderNo int NOT NULL,P_Id int FOREIGN KEY REFERENCES Persons(P_Id))
![Page 23: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/23.jpg)
FOREIGN KEY (COMPAT)
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
![Page 24: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/24.jpg)
FK CON ALTER
ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (P_Id)REFERENCES Persons(P_Id)
![Page 25: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/25.jpg)
CHECK
CREATE TABLE Persons(P_Id int NOT NULL CHECK (P_Id>0),LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))
![Page 26: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/26.jpg)
CHECK (COMPAT)
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
![Page 27: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/27.jpg)
CHECK CON ALTER
ALTER TABLE PersonsADD CONSTRAINT chk_Person CHECK
(P_Id>0 AND City='Sandnes')
![Page 28: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/28.jpg)
DEFAULT
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes')
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
![Page 29: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/29.jpg)
DEFAULT (COMPAT)
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
![Page 30: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/30.jpg)
DEFAULT CON ALTER
ALTER TABLE PersonsALTER COLUMN City SET DEFAULT 'SANDNES'
![Page 31: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/31.jpg)
DROPPING
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID ALTER TABLE Persons DROP CONSTRAINT pk_PersonID ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders ALTER TABLE Persons DROP CONSTRAINT chk_Person ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT DROP DATABASE Olympics; DROP TABLE sport;
![Page 32: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/32.jpg)
EJERCICIO
Cree un script que genere la siguiente base de datos para el diagrama ER de OlympicsData UTF8 charset y collation
Cree los primary keys y los foreign keys Cree las tablas intermedias Utilice donde corresponda:
Default
Not null
Check
Unique
![Page 33: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/33.jpg)
REFERENCIAS
http://dev.mysql.com/doc/refman/5.0/en/charset-general.html http://www.w3schools.com/sql/ http://msdn.microsoft.com/en-us/library/ms176061.aspx
![Page 34: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/34.jpg)
TAREA: INVESTIGACIÓN
Cómo conectarse a una base de datos SQL Server desde Java
Envíe un script de ejemplo
![Page 35: Bases de Datos 1 Prof. Daniel Obando Fuentes. CREANDO UNA BASE DE DATOS CREATE DATABASE dbname;](https://reader030.fdocuments.co/reader030/viewer/2022032702/56649cdb5503460f949a5dc8/html5/thumbnails/35.jpg)
SQL SERVER 2014
• Por default UTF-8
• Para soportar UNICODE se usa UTF-16. Para usar UTF-16• Usar tipos nchar, nvarchar, ntext
• Usar collations que tengan _CS
• CREATE DATABASE MyOptionsTest COLLATE Latin1_General_100_CS_AS_SC;