Shulou(Shulou.com)06/01 Report--

Linux 056 Notebook DATABASE, MySQL

MySQL www.mysql.com

Data query mode

Dichotomy

After sorting the data first, and then taking the middle position to query and match each time, it takes an average of 14 queries.

B+ tree index

It only takes 4 times to query the data

Indexes

Items that are extracted and sorted separately from the columns to be retrieved in the data file, and these entries

There is a corresponding pointer to the location of the original data

Query standard

Search code

Api (application programming interface) library

Function

ODBC (Open DataBase Connection)

Drive Connection

Data presentation

User view

Physical view

DBA view

Organizational structure of data

Hierarchical type

Reticular type

Relational type

Two-dimensional table

DBMS (DataBase Management System)

RDBMS (Relational DataBase Management System)

1. Database creation, deletion and modification

2. Create tables, delete tables, modify tables

3. Creation and deletion of index

4. User and rights management

5. Addition, deletion and modification of data

6. Query

DB command classification

DML (Data Manapulate Language) operation

INSERT, REPLACE, UPDATE, DELETE

DDL (Data Defination Language) definition

CREATE, ALTER, DROP

DCL (Data Control Language)

GRANT, REVOKE, SELECT

Data management software

The user's required index can be automatically established for the data, and when the data entry is deleted, the relative index entry

It can also be deleted automatically without manual operation.

To add, delete, modify and check

Manage data integrity

Authority management

RDBMS software

Commercial Oracle, Sybase, Infomix, SQL Server (medium), DB2

Open source MySQL, PostgreSQL, EnterpriseDB

SQL (Structured Query Language) ANSI

Sql86, sql89, sql92, sql99

Go to IOE:IBM,Oracle, EMC (production storage equipment)

Oracle R & D Software Red

SUN integrated software service provider-- > Development MySQL-- > acquired by Oracle

MySQL-- > Marion (database management software)

MySQL-- > Percona (company)

OpenOffice

LibreOffice

Acquire BEA: WebLogic 8.3 billion

Acquisition of PeopleSoft, a management company that provides customer relationship software 12.8 billion

IBM integrated software service provider Blue Giant

Inverse relation model: NoSQL

MongoDB

Redis

HBase

DBMS advantage

Independence of data management

Effectively complete data access

Ensure data integrity and security

Centralized data management

Concurrent storage and fault recovery

Reduce application development time

DBMS

Analyzer

Plan executor

Optimizer

Access to documents

Register

Disk space manager

Recovery Manager

Transaction manager

Lock manager

MySQL

Community Edtion

GA (General Availability)

Enterprise Edition

MySQL package format

Format specific to the package manager

Rpm package .exe format, etc.

Universal binary format (similar to windows green software)

Source program

MySQL's RPM package

Mysql, mysql-server

Mysql

Mysqld

Tcp/3306, mysql, mysql

Install MySQL

Yum install mysql-server-y

Initialization

Create a source database (mysql) to store table names, field names, attribute definitions, etc.

Connect to mysql

Mysql

Service mysqld start

Mysql connects to mysql server

-u USERNAME

-P PASSWORD

-h MySQL_server

Linux: socket

Windows: memory

Which clients are allowed to log in by user user@host

Mysql client mode

Interactive mode

Client command

Server command

Must use a statement Terminator, which defaults to a semicolon

Batch processing mode

Execute mysql script

SQL interface

Oracle, PL/SQL

SQL server, T-SQL

Relational database object

Library

Table

Indexes

View

Constraint

Stored procedure

Storage function

Trigger

Vernier

User

Authority

Business

Tables: solid

Row, column

Line: row

Column: field, colum

Field name, field type, type modifier (constraint),

Character

CHAR (n) has a fixed length, even if the defined length is not stored.

VARCHAR (n) characters of variable length

BINARY (n) case-sensitive fixed length

VARBINARY (n) variable length, case sensitive

TEXT (n) is very long

BLOD (n) extra long case sensitive

Numerical value

Exact value

× × ×

TINYINT

SMALLINT

MEDI

INT

BIGINT

Modifier: UNSIGNED unsigned

NOT NULL

Decimal system

DECIMAL

Approximate value

Floating point type

FLOAT

DOUBLE

Date and time

DATE

TIME

DATETIME

STAMP timestamp

Boolean

True or false 01

MySQL common commands

DDL (Data Defination Language)

CREATE

ALTER

DROP

DML (Data Manapulate Language)

INSERT

UPDATE

DELETE

DCL (Data Control Language)

GRANT

REVOKE

Creat database

CREATE DATABASE db_name

CREATE DATABASE IF NOT EXISTS db_name

1. USE db_name

2. CREATE TABLE tb_name (col1,col2...)

Drop database

DROP DATABASE db_name

DROP TABLE [IF EXISTS] tb_name

View

SHOW TABLES FROM db_name; views tables in the library

USE db_name; indicates which database to operate on next

DESC tb_name; view table structure

SELECT * FROM tb_name; view all data in the table

Modify the table

ALTER TABLE tb_name

MODIFY

CHANGE

ADD

DROP

ALTER TABLE students ADD course VARCHAR (100)

ALTER TABLE students CHANGE course Course VARCHAR (100) AFTER Name

ALTER TABLE students DROP Course

DML

INSERT INTO tb_name (col1,col2,...) VALUES | VALUE ("STRING", NUM...)

When inserting multiple data entries, you can separate them with commas

INSERT INTO students VALUE ('LuFei',25,'M'), (' NaMei',23,'F')

INSERT INTO students (Name,Age) VALUE ('Liming',20), (' Tom',18)

Without the insertion of fields, it means that each field is inserted into the corresponding value.

UPDATE tb_name SET column=value WHERE specifies which line, otherwise change it all

UPDATE students SET Age=30 WHERE Name='Tom'

DELETE FROM students WHERE

DELETE FROM students WHERE Age=25

REPLACE INTO

Choice

SELECT field FROM tb_name WHERE CONDITION

* all fields

WHERE has no condition to show all lines

Create a user

CREATE USER 'USERNAME'@'HOST' INDENTIFIED BY' PASSWORD'

DROP USER 'USERNAME'@'HOST'

HOST:

IP

HOSTNAME

NETWORK

Wildcard character

_ match any single character 172.16.0.characters _ 10-99

% any character, any length

(note that wildcards should be used in quotation marks)

DCL

Authority management

GRANT pri1,pri2,... ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST'

[IDENTIFIED BY 'PASSWORD']; this is a command written in two lines because of the layout

GRANT ALL PRIVILEGES ON mydb.students to 'tom'@'%'

REVOKE pri1,pri2,... ON DB_NAME.TB_NAME TO 'USERNAME'@'HOST'

REVOKE ALL PRIVILEGES ON mydb.students FROM 'tom'@'%'

View user authorization

SHOW GRANTS FOR 'USERNAE'@'HOST'

Selection and projection

Select: specify a field as the search code, make a logical comparison, filter the rows that meet the criteria; (row) WHERE

Projection: hiding some fields (columns)

Summary:

DDL (Data Definition Language)

Used to add, delete, and change databases, tables, and definitions

CREATE creation

DROP deletion

ALTER change

DML (Data Manipulation Language)

Used to manipulate data entries in the table, etc.

INSERT

UPDATE

DELETE

SELECT

SHOW

DCL (Data Control Language)

The control of users' rights, etc.

You can use HELP CONTENTS to query

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.