Day1 MySQL installation and basic operation

Data and databases

1. Data : Symbolic representation of objective things.

2. storage Media : paper, disc, disk, U disk, cloud disk ...

3. purpose of Storage : Search (query)

The amount of data stored increases the difficulty of retrieval.

4. database (db:database): A warehouse (relational model) that stores data according to a certain data structure.

Classification of data

A) structured data: can be described according to a certain structure.

b) Unstructured: not possible.

c) Semi-structured: between the two.

Database classification

A) relational database: structured data

b) Non-relational database: unstructured and semi-structured

Database management Systems (Dbms:database management System)

Manipulate and manage databases.

Classification of database management systems a) relational database management system (RDBMS:RELATIONSHIP):

A. Oracle:oracle Company, a large-scale relational management system. Wide range of application scenarios. (medicine, finance, traditional internet ...)

B. Mysql:oracle company, open source free

C. SQL Server:microsoft Company, small and medium sized database.

D. DB2:IBM Company "1984"

b) Non-relational database management system (NOSQL): not only SQL

Redis

Mongdb

Hbase

MySQL Database introduction

Mysql AB Company products. Open-Source Free relational database management system.

1996, Version 1.0

2000, open source

Acquired by Sun for $1 billion in 2008

In 2009, Sun was acquired by Oracle for $7.4 billion

The sub-database of sub-database and sub-table thought

Divide multiple databases (according to different business divisions, Baidu, Huawei ...).

Sub-table

Multiple tables are stored in the database, and data is stored in each table. Data is stored in a two-dimensional tabular format.

GPL: Open source software protocol

Download and installation of MySQL 1) download

https://dev.mysql.com/downloads/mysql/

2) Installation

Installation version: MSI (Fool-mounted)

Green version: Zip

A. Unzip to a non-Chinese path

B. Enter the Services.msc command in the Run (view service)

C. Use the administrator to enter the DOS command, switch to the bin directory, the mysqld command to implement the installation: Mysqld–install

D. Uninstall: Mysqld–remove

E. Command line startup: net start MySQL

F. Stop service: net stop MySQL

G. Login database: mysql–uroot–p password　　　　　

H. No password, change password: mysqladmin–uroot–p password new password

follow the precautions:

Administrator status

Turn off anti-virus software firewall

Do not use multiple networks (ping 127.0.0.1)

C:/windows/my.ini file, delete

3) Basic Commands

A) Exit: Exit

b) Show databases: View database

c) Use dbname: Switch to a database

d) Show tables: Show all database tables

4) Client Tools

Navicat/sqlyog

Sql

Structured Query Language (structure query language), a common language for database data manipulation.

Storage Engine (InnoDB): Core, statement is case insensitive.

Classification

　　A) DDL ( data definition Language): Database definition language

Creation, modification and deletion of database objects (databases, tables, views, triggers ...), create alter drop

　　b) DML (datamanipulation language): Manipulation language

Adding and deleting data, insert Delete update

　　c) DQL (data Query Language): querying language

Select

　　d) TCL (Transaction Control Language): Transaction control Language

Commit COMMIT, rollback rollback

　　e) DCL (Data Control Language): Information-controlled language (Oracle)

Grant: Authorization, REVOKE: Canceling authorization

Operations on the database (DDL) 1. Creating a Database Create

-- Create a database Create Database IF  not EXISTS mydb;

2. Destroy Database Drop

-- destroying the database Drop Database IF EXISTS mydb;

Table Operations (DDL)

Table: The form of a two-dimensional table stores data.

A row is called a record;

A column is called a field.

1. Create a table

-- CREATE TABLE Save student's information  -- Student number  -- Student Name Create Table Student (     int,     varchar()); -- View Table Structure desc student;

2. Modification of the table alter

--Add FieldModify Table structure add field (default append)Alter TableStudentAddSexvarchar( -) First|After column name--Modify the length, type, name of the fieldAlter TableStudent Change Sname snamevarchar( -);--Modify LocationAlter TableStudent Change AgeintAfter sex;--table field DeletionAlter TableStudentDropCid

3. Destruction of the Table drop

-- Destroying Tables Drop table Student;

Data type 1. Numerical https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

Integral type data:

Floating point type:

Float (m,n): Length and number of decimal places

Double (m,n):

Decimal (M,n):

2. String type

Char: fixed-length string

VARCHAR: variable-length strings

3. Date type

Date: Day

Time:

DateTime: Date and Time

Timestamp: Time stamp

4. Other types

Text: Long text

Blob: Saving binary data

Enum (' F ', ' M '): Enumeration

Integrity constraint Integrity

Accuracy of data

Categorical entity Integrity

Entity: Record

1. PRIMARY KEY constraint: Primary key, unique and cannot be empty

3 Ways to create

--The first type:Create TableStudent (Sidint Primary Key, Snamevarchar( -));--The second type:Create TableStudent (Sidint, Snamevarchar( -),   Primary Key(SID));--Third KindAlter TableStudentAdd CONSTRAINTPk_sidPrimary Key(SID);

2. Unique constraint: Unique, non-repeating

Create Table Student (     intPRIMARYKEY,   varchar( ),   varchar(unique);

3. Primary key self-increment (MySQL applies, Oracle does not apply (sequence))

Create Table  intPRIMARYKEY  auto_increment,   varchar( ),   varchar(unique);

Domain integrity

Domain: The value of the field is accurate.

　　 A. Type constraintsB. Non-null constraint: NOT NULLC. Defaults: Default

Create TableStudent (Sidint PRIMARY KEYauto_increment, Snamevarchar( -) not NULL, cardvarchar( -)Unique, Sexvarchar(Ten)default 'Mans');

Referential integrity: between multiple tables

FOREIGN KEY constraint: (primary foreign key) foreign key, referential integrity

The value of a field (foreign key) for a table must refer to the primary key value of another table.

Alter Table Add CONSTRAINT Foreign Key REFERENCES Dept (DEPTNO)

Custom Integritycheck (MySQL does not support) Oracle

-- Add Constraint Guarantee department number can only be between 1 and 120 Alter Table Add CONSTRAINT Check between 1  and )

Delete Constraint

Alter Table Drop Primary Key ; Alter Table Drop Foreign key;

Day1 MySQL installation and basic operation