Summary of three databases

SQL Server Database I. Steps to create a database in development

1.1 Collecting information

1.2 Conceptual Design (E-r map)

1.3 Detailed design (Database Model diagram)

1.4 Audit Database (three main paradigms)

2.1 E-r

Rectangle: Solid

Ellipse: Properties

Diamond: Relationship

3.1 Mapping relationships

One-to-many, multi-pair, many-to-many

4.1 Three main paradigms

The first paradigm: Each column is guaranteed to be atomic, and the points can no longer be divided.

The second paradigm: on the basis of satisfying 1NF, each table can only express one thing (with primary key)

The third paradigm: on the basis of satisfying 2NF, ensure that columns other than the primary key have a direct relationship to the primary key, rather than indirectly to the relationship

Two. Build a database to build a constraint 2.1 build a library

Build Library: Create Database MySchool;

Three file formats: Primary file. mdf, secondary file. ndf, log file. ldf

Create DATABASE MySchool

--Master File

On [primary]

(

Name= ',--logical name

Filename= '. MDF ',--file path

size=10,--Initial Size

maxsize=100,--Maximum Size

FILEGROWTH=10 growth rate cannot add percent sign

),

--Secondary file

(

Name= ',--logical name

Filename= '. ndf ',--file path

size=10,--Initial Size

maxsize=100,--Maximum Size

FILEGROWTH=10 growth rate cannot add percent sign

)

On log

(

Name= ',--logical name

Filename= '. ldf ',--file path

size=10,--Initial Size

maxsize=100,--Maximum Size

FILEGROWTH=10 growth rate cannot add percent sign

)

Adding files to a database

ALTER DATABASE MySchool

Add File

--Secondary file

(

Name= ',--logical name

Filename= '. ndf ',--file path

size=10,--Initial Size

maxsize=100,--Maximum Size

FILEGROWTH=10 growth rate cannot add percent sign

)

2.2 Build Table

Create Table Table Name

(

Column data type data attribute (null or NOT NULL)

)

Autogrow column identity (+)

Like what

Create Table Student

(ID int identity () NOT NULL

)

DROP table Name

Delete the data delete table name, TRUNCATE TABLE name

2.3-Key constraint 2.3.1 PRIMARY KEY constraint

Alter Table Table Name

ADD constraint pk_ constraint name primary key (column name)

2.3.2 Unique Constraint

Alter Table Table Name

ADD constraint uq_ constraint name unique (column name)

2,3.3 CHECK Constraints

Alter Table Table Name

ADD constraint ck_ constraint name check (column name <=100)

2.3.4 Default Constraints

Alter Table Table Name

ADD constraint df_ constraint name default (' default value ') for column name

2.3.5 FOREIGN KEY constraint

Alter Table foreign Key tables

ADD constraint fk_ constraint name foreign key column name references primary key table (column name)

2.3.6 Deleting a constraint

Alter Table Table Name

Drop constraint constraint name

Three SQL Programming 3.1 defining variables

Local variable DECLARE @ variable name data type = value

All variables @ @error error number, @ @identity gets the last value @ @version version number

3.2 Assignment 3.2.1 Set

SET @ variable name = value or (SET @ variable name = (subquery)), only one variable can be assigned at a time

3,2,2 Select

SELECT @ Variable name = value, select @ Variable name = field, @ Variable name = field from table, you can assign values to multiple variables; Take the last one when multiple data is returned

3.3 Convert data type 3.3.1 Convert

Convert (data type, variable)

Special fields that can be converted to a DateTime type such as convert (field, 102)

3.3.2 Cast

Cast (variable name as data type)

3.4 If Else

If (condition)

Begin

---SQL

End

Else

Begin

--sql

End

3.5 while

while (condition)

Begin

End

3.6Case

case when condition then result

When condition then result

Else

End

3.7 GO

Batch processing, put multiple SQL packets together to send the server execution, faster execution

Four: Sub-query

SELECT * FROM (sub-query) aliases

4.1 In

Select * FROM table name where column name in (subquery)

Select * FROM table name where column name in (', ' ', ')

4,2 not in

Select * FROM table name where column name not in (subquery)

Select * FROM table name where column name not in (' ', ' ', ')

4.3 exists and NOT exists

if exists (SQL query) to determine if the data can be detected

Can overwrite in and not

The efficiency of execution is faster than in and not

SELECT * FROM student Wehre exists (SELECT * from grade where Grade.gradeid=student.gradeid)

Five. Stored procedure 5.1 Creating a non-parametric stored procedure

Create Proc Stored Procedure name

As

SQL statements

Go

5.2 Creating a stored procedure with input parameters

Creta Proc Stored Procedure name

@ Variable name data type

As

SQL statements

Go

5.3 Create a stored procedure that has a default value

Creta Proc Stored Procedure name

@ variable name Data type = ' default value '

As

SQL statements

Go

5.4 Creating a stored procedure with input and output

Create Proc Stored Procedure name

@ Input variable data type,

@ output variable data type output

As

SQL statements

Go

5.5 Calling a stored procedure

Exec Stored Procedure Name

Execute Stored Procedure Name

Experience: Exec can omit when a go is encountered

5.6 Business

Start transaction BEGIN TRANSACTION

Commit TRANSACTION COMMIT TRANSACTION

Transaction rollback ROLLBACK TRANSACTION

Six. Index

Unique index: When a unique constraint is given to a column, the system automatically creates a unique index (two rows that cannot be duplicated in the same column)

Primary KEY index: A special unique index that automatically creates a clustered index when a primary key is added to a column

Clustered index: A table can have only one clustered index (similar to the dictionary's phonetic lookup)

Nonclustered indexes: A table can have multiple nonclustered indexes (similar to auto radicals)

Grammar

Create [unique|nonclustered|clustered]

Index ix_ indexed name on Tbale (column, column) with

FILLFACTOR=80 (no percent sign)

Delete Index

Drop Index table. Index name

Mysql database One. Advantages of MySQL

Fast running speed

Low cost of Use

Portability is strong

Applicable user Wide

Community Edition free, open source. For ordinary users

Enterprise Edition fee, not free download function and service more perfect for enterprise users with high function and security requirements

Two MySQL related information 2.1 related configuration

Port number: 3306

Default Character set: UTF8

UTF8: A Unicode encoding to solve the international character of the non-uniform multi-byte encoding, strong versatility

Write the bin directory to the environment variable

Root Password settings

2.2 Main Documents

Bin, Include,lib,share

My.ini: Log MySQL configuration information, can modify configuration content, such as port number, character set

2.3 System Database

Information_schema

Performance_schema

Mysql

Test

MySchool

Bank

Hospitaldb

Three Building a Table Key constraint 3.1 creating a database

Create Database MySchool;

3.2 Switching the current database

Use database name

3.3 Deleting a database

Drop database name

3.4 Creating a Table

CREATE table [IF not EXISTS] Table name (field data type characteristics)

3.5 Constraints and attributes of a field

A non-null constraint not a NULL field is not allowed to be empty

Default constraint defaults give a field default value

Uniquely constrained unique KEY (UK) Setting the value of the field is unique and allowed to be null, but only one null value

PRIMARY KEY constraint PRIMARY key (PK) Set this field as the primary key for the table to uniquely identify the table record

FOREIGN KEY constraint FOREIGN key (FK) is used to establish a relationship between two tables, and you need to specify which field to reference the main table

Autogrow auto_increment Set the column self-increment field by default each increment of 1 is typically used to set the primary key

Primary KEY PRIMARY Key

Note COMMENT ' number ' comment= ' Test table '

Setting character Set Encoding CHARSET = Character Set name

3.6 Viewing tables

See if the table exists with use MySchool; SHOW tables;

View table Definition DESCRIBE table name, or desc table name;

Delete table [IF EXISTS] Table name;

Four Database Operations directives

DML (Data Manipulation language)

Used to manipulate the data contained in the database (Update,delete,insert)

DDL (data definition language)

For operations such as creating and deleting database objects (drop, alter,create)

DQL (data Query Language)

Used to query data in a database (select)

DCL (Data Control Language)

Used to control access permissions, access rights, and so on for Database Components (grant)

Five Data Type 5.1 Digital type

Tinyint very small data has a character value:-27 ~ 27-1 unsigned value: 0 ~ 28-1 1 bytes

SMALLINT smaller data has a character value:-215 ~ 215-1 unsigned value: 0 ~ 216-1 2 bytes

Mediumint medium-sized data has a character value:-223 ~ 223-1 unsigned value: 0 ~ 224-1 3 bytes

INT Standard Integer has a character value:-231 ~ 231-1 unsigned value: 0 ~ 232-1 4 bytes

BIGINT larger integer has a character value:-263 ~263-1 unsigned value: 0 ~264-1 8 bytes

Float single precision floating point number ±1.1754351e-38 4 bytes

Double dual precision floating point number ±2.2250738585072014e-308 8 bytes

Floating-point number decimal (m,d) m+2 bytes in the form of a decimal string

The unsigned property is identified as an unsigned number

Zerofill attribute width (number of digits) is not sufficient to fill 0

5.2 String

Char[(m)] fixed length string M byte

varchar[(M)] variable string variable length

Tinytext Mini text string 0~28–1 byte

Text string 0~216–1 bytes

Date yyyy-mm-dd, format 1000-01-01~ 9999-12-31

DATETIME yy-mm-dd hh:mm:ss:1000-01-01 00:00:00 ~9999-12-31 23:59:59

Time Hh:mm:ss: -835:59:59 ~ 838:59:59

TIMESTAMP Yyyymmddhhmmss 1970 at some point ~2038 year, accuracy is 1 seconds

Year YYYY formatted years 1901~2155

Six. Types of storage engines

MyISAM, InnoDB, Memory, CSV, etc. 9 kinds

Use MyISAM: No transaction, small space, to query access to the main

Using InnoDB: Multiple deletions, update operations, high security, transaction processing, and concurrency control

Modifying the storage engine InnoDB

Position

MyISAM Type Table file: *.frm: Table structure definition file *. MYD: Data file *. MYI: Index File

InnoDB Type Table file: *.frm: Table structure definition file Ibdata1 file!!!

Seven Advanced Query

71 Modifying a table

Modify table name: ALTER table name RENAME [to] new table name;

Add Field: ALTER table name add field name data type [Properties];

Modify field: ALTER TABLE name change original field name new field name data type [Properties];

Delete field: ALTER Table Name drop field name;

7.2 Adding a primary key

ALTER table name ADD CONSTRAINT PRIMARY Key name PRIMARY key table name (primary key field);

7.3 Adding foreign keys

ALTER table name ADD CONSTRAINT foreign key name FOREIGN key (foreign key field) REFERENCES associated table name (associated field);

7.4 Data Insertion

Insert a single data record

INSERT into table name [(List of field names)] Values (value lists);

Inserting more than one data record

INSERT into new Table (field list) VALUES (Value List 1), (Value List 2),......, (value list n);

Inserting query results into a new table

INSERT into new Table (Field 1, Field 2 ...). ) Select field 1, Field 2 ... from the original table;

Select field 1, Field 2 ... into new table from the original table;

Update data records

Update table name set field 1= value 1, field 2= value 2,..., field n= value n[where condition];

Delete data records

DELETE from table name [where condition]; TRUNCATE table name;

The TRUNCATE statement is deleted and the self-increment column is reset, and the table structure and its fields, constraints, and indexes remain unchanged and execute faster than the DELETE statement

7.5DQL statements

List of SELECT < field names >

[WHERE < query conditions;]

[GROUP by < Group field name;]

[Having < conditions;]

[Order by < sorted field name > [ASC or DESC]]

LIMIT clauses

[LIMIT [position offset,] number of rows];

7.6 Common functions--aggregate functions

AVG (): Returns the average of a field

COUNT (): Returns the number of rows in a field

Max (): Returns the maximum value of a field

MIN (): Returns the minimum value of a field

SUM (): Returns a field's sum

7.7 Common functions--string functions

CONCAT (STR1,STR1...STRN) string connection SELECT CONCAT (' My ', ' S ', ' QL '); return: MySQL

Insert (STR,POS,LEN,NEWSTR) string Replace select Insert (' This is SQL Server database ', 3,10, ' MySQL '); back: This is the MySQL database

LOWER (str) converts the string to lowercase select LOWER (' MySQL '); return: MySQL

UPPER (str) converts the string to uppercase Select UPPER (' MySQL '); return: MySQL

SUBSTRING (Str,num,len) string intercept select SUBSTRING (' javamysqloracle ', 5,5); Mysql

7.8 Common functions--time-date functions

Curdate () Gets the current date SELECT curdate (); return: 2016-08-08

Curtime () Gets the current time SELECT curtime (); return: 19:19:26

Now () gets the current date and time for SELECT today (); return: 2016-08-08 19:19:26

WEEK (date) Returns a date of the week of the year SELECT WEEK (now ()); return: 26

Year (date) returns the years of the date, SELECT, now (); return: 2016

HOUR (time) returns the hour value of times SELECT HOUR (now ()); return: 9

MINUTE (time) returns the minute value of the hour, SELECT MINUTE (now ()); return: 43

DATEDIFF (DATE1,DATE2) returns the number of days separated by the date parameter Date1 and Date2

Adddate (Date,n) calculates the date parameter dates plus n days after the date SELECT adddate (now (), 5);

return: 2016-09-02 09:37:07

7.9 Mathematical functions

Ceil (x) returns the smallest integer greater than or equal to the number x SELECT ceil (2.3) returns: 3

Floor (x) returns the largest integer less than or equal to the number x SELECT floor (2.3) returns: 2

RAND () returns the random number between 0~1 SELECT RAND () returns: 0.5525468583708134

7.10 queries

SELECT ... From table 1 WHERE field 1 comparison operator (subquery)

A subquery is a query that is nested in a SELECT, INSERT, UPDATE, or DELETE statement, or in another subquery

General usage of a subquery in a where statement

Eight. Database restore and Backup 8.1. Backup

Using the mysqldump command

Write command in cmd (if error, internal name not supported-"use CMD to open Mysqldump.exe installation directory")

Back up individual tables

Mysqldump-u User name-p password database Name table 1, table 2, table 3...> file path D:\1.sql (Note: cannot be written later ";" No.

Back up all the information in the entire database

Mysqldump-u User name-p password Database name > file path d:\2.sql

8.2 Restore

Mysql-u User name-p password database name < file path

(Note: The following ";" Number cannot be written)

8.3. Export txt

SELECT * FROM tablename to outfile ' file path address ';

8.4 Importing TXT

Load data infile ' file path address ' into table table name

Oracle Database

A CREATE TABLE Space

Create Tablespace SA

DataFile ' D:\orcldata\sa.dbf ' size 200M

Reuse Autoextend on next 1m maxsize unlimited;

--Create user This user's password is ACCP default tablespace is BDQN

Create user sa identified by ACCP default Tablespace sa;

-Two. Give users maximum access

Grant CONNECT,RESOURCE,DBA to SA;

Three.-Add a file to the table space

Alter Tablespace BDQN

Add datafile ' D:\orcldata\bdqn2.dbf ' size 200M

Reuse Autoextend on next 1m maxsize unlimited;

Four. Rankings and pagination

CREATE TABLE score (ID int,

Studentname VARCHAR2 (50),

ClassName VARCHAR2 (50),

Result Number (18));

INSERT into score values (1, ' Zhang San ', ' one Shift ', 90);

INSERT INTO score values (2, ' John Doe ', ' one Shift ', 80);

INSERT into score values (3, ' Harry ', ' one Shift ', 80);

INSERT into score values (4, ' Zhao Liu ', ' one Shift ', 60);

INSERT INTO score values (5, ' small Seven ', ' Class two ', 90);

INSERT into score values (6, ' Jet Li ', ' Class two ', 80);

INSERT INTO score values (7, ' Faye Wong ', ' class two ', 70);

INSERT into score values (8, ' Ouyang ', ' Class two ', 70);

4.1over function

SELECT * from score;

--pagination

Select Id,studentname,classname, result,rownum from score

--The first ranking

Select T.*,rownum MC from (select Id,studentname,classname, result from score

ORDER BY result Desc) T

--Second ranking

Select T.*,rank () over (order by t.result Desc) MC from (select Id,studentname,classname

, result from score

ORDER BY result Desc) T

--The Third Kind

Select T.*, Dense_rank () over (order by t.result Desc) MC From (SELECT ID,

Studentname,classname, result from score

ORDER BY result desc) T;

--fourth, ranked by Group field

Select T.*,dense_rank () over (partition by classname ORDER BY t.result Desc)

MC from (select Id,studentname,classname, result from score

ORDER BY result Desc) T

--Cumulative

Select Tt.*,sum (Tt.result) over (order by MC) from (select T.*,dense_rank ()

Over (partition by classname ORDER BY T.result Desc) MC from

(select Id,studentname,classname, result from score

ORDER BY result Desc) t) TT;

--Cumulative

Select Score.*,sum (Result) over (order by ID) from the score order by ID;

Five. Backup and restore 5.1 local backup (cmd execution call data stick)

EXPDP jhpt/1 dumpfile=hbjy20140923.dmp directory=data_pump_dir full=y--data_pump_dir Find the path in the database and export the file to this folder (SELECT * From Dba_directories view data_pump_dir corresponding directory)

5.2 Native Restore

IMPDP JHPT/1 dumpfile=hbjy20140923.dmp Directory=data_pump_dir full=y

5.3 Remote Backup

Exp Jhpt/[email protected] file=d:\app\user\admin\orcl\dpdump\jhpt23.dmp Log=d:\jhpt20150424.log

You can also add OWNER=JHPT

5.4 Remote Restore

Imp jhpt/[email protected] file=d:\app\user\admin\orcl\dpdump\jhpt23.dmp full=y

5.5 Restore Table

Imp jhpt/[email protected] File=d:\base_purchaseinfo.bak Tables=base_purchaseinfo

5.6 Backup and restore instructions

Native backup and restore is the most efficient, but limited to this machine

Remote backup is to install the Oracle database locally, back up the data from the remote database to the local

Remote restore is to restore locally backed-up data to a remote database

Before restoring, put the user, tablespace, data files all deleted, and then re-create, restore the data there are several data files, need to create several data files or restore to half throw the file is not enough error

/*--Delete table space files that are already in table space

Drop tablespace jhpt including contents and datafiles;

--Delete User

Drop user JHPT cascade;

Summary of three databases