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

How to quickly start MySQL, in view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Preface

This article is some of the basic knowledge of MySQL that I summed up after I studied MySQL by myself. I use MySQL 5.7myself.

one。 Basic knowledge of database

1. What is a database?

A database is a warehouse that organizes, stores and manages data according to a certain data structure. it is an efficient solution for managing a large amount of information.

Database system DBS= database DB+ database management system DBMS.

two。 Classification of databases

The database is divided into relational database and sub-relational database.

Relational: a database based on a relational model, which, as its name implies, is a two-dimensional table model, which is used to record entities and the relationship between entities and entity information. The common relational databases are Oracle, MySQL, SQL Server.

Non-relational database: a database that is not based on a relational model. There are mainly MongoDB Redis.

3. Common languages of databases

Database system DBS

Structured query language SQL

Database DB

Database management system DBMS

Table table

Line row = > record record

Column column = > Field field

4. The common operation mode of database

DOS command:

Connect to the remote host: mysql-h hostname / IP address-P port number-u user name-p password

Connect to the host: mysql-u username-p password

Client (visualization software): Navicat

Code:

Web web page: PHPMyAdmin

Architecture of 5.MySQL

Cpact S architecture:

The server manages and stores data

The client sends an operation request

two。 Addition, deletion, modification and query of the database

1. Create database # Syntax for creating database create database `library name`charset=utf8/gbk

Before creating a database, you need to connect to the database and practice with your own local database.

two。 Query database # query all databases show databases;# query like by condition, where% represents any number of characters,-represents any character. Create databases like'% -'; # query database building statement show create database;3. Modify database # modify database (only options can be modified, that is, character sets) alter database `library name` [new option]; 4. Delete database # Delete database (the syntax is simple, but the consequences are serious. Generally speaking, you don't have permission, .) drop database `library name`; three. Addition, deletion, modification and query of the table

1. Create a table # before creating a table, specify the database use `specify the library name`; # create the create table `table name of the table (`field 1` field 1 type field 1 attribute,.` field N` field N type field N attribute); [option]

There are three main categories of options:

Character set charset=utf8 / GBK...

Data engine engine=innodb / mysiam

Remarks comment='' remarks''

Other field types and field properties are described in detail later.

two。 Query table # query all tables show tables;# condition query show tables like'% -'; # query table structure desc `table name `; # query table construction statement show create table `table name`; 3. Modify table # modify table option alter table `table name `[new option]; # modify table name rename table `old table command `table name `new table name`; # modify field alter table `table name `change `old field name ``new field name `new field type; # add new field to the end of field `Table name `add `new field name` type attribute # after adding a field to the corresponding field, alter table `table name `add `new field name` type attribute `corresponding field` # add a field to the first alter table `table name`add `new field name` type attribute first;4. Delete the table # delete the table if it exists, otherwise report an error drop table [if exists] `Table name`; four. Addition, deletion, modification and search of records

1. Insert record # insert record insert into `table name `(`field 1`,... `field`) values ('value 1', 'Magi' value N'); # when all values are passed in at once, you can omit the field insert into `table name `values ('value 1', 'Magi' value N') # when multiple records need to be passed in at a time, the insert into `table name is `(`field 1`,... field`) values ('value 1records, (value 1records), (value 1records,), (values, (values, 1records, and fields are all passed, insert into `student` values (values list 1), (values list 2), (values list n); 2. Query record (most commonly used) # query syntax select [selection] Field list as alias from `Table name `where conditional expression

1. Conditional expression:

Logical operator: and or not

Comparison operator: +-* /

=! =

2. Options:

All: query all. Default is all if you don't write.

Distinct: remove the weight. Repetition means that in the queried data, all the fields of the record are the same before they are recognized as duplicates.

As: alias. Set an alias to the field after the query for easy reference.

Common aggregate functions: count (), Max (), Min (), Sum (), avg ()

3. Query join with tables

1. Internal connection inner join # queries the records that meet the connection conditions in the two tables participating in the connection, and filters out those that do not match. Select * form `Table 1`inner join` Table 2` on connection conditions; 2. The left outer connection left Join# will query the records in the left table that participate in the connection even if they do not match the connection conditions. If the right table does not match, filter out the select * from `table 1`left join` table 2` on connection conditions; 3. The right outer connection right join # will query the records in the right table that participate in the connection even if they do not match the connection conditions. If the left table does not match, filter out the select * from `Table 1`right join `Table 2` on connection conditions. Modify record # syntax update `table name `set `field` = 'new value' where conditional expression; 4. Delete record # syntax delete from `Table name `where conditional expression; V. Field type 1. Digital 1.1 Integer

Tinyint: occupies one byte and represents a total of 256digits

Signed:-128x127

Unsigned: 00255

Int: occupies 4 bytes

Signed:-2.1 billion ~ 2.1 billion

No characters: 0 ~ 4.2 billion

1.2 Decimal type

Floating point number:

Float (MMagar D): single precision floating point number

Double (MMagar D): double precision floating point number

Fixed points:

Decimal (MMagar D): the type of decimal where data will not be lost, often used to record currencies

two。 Text type

Char (M): fixed length character, M represents the maximum number of characters. The advantage is the high speed of operation. A fixed-length character commonly used within 255 characters. Such as: ID card, telephone number, etc.

Varchar (M): variable length characters, M represents the maximum number of characters. The advantage is to save space. Characters that are usually less than 255 characters and whose length is uncertain.

Text: commonly used in text with more than 256characters

3. Date and time

Datetime: fixed date time

Timestamp: timestamp: when a record is added or updated, it is automatically updated to the current system time, which is used to record the time when the record was last modified or when a new record was inserted

six。 Field Properti

Not null: set the value of this field not to be empty. If you don't write it, it can be empty by default.

Default default value: set a default value. If no data is passed in, the default value is used. If data is passed in, the incoming value is used.

Comment: Chinese character memo field for notes to facilitate later maintenance

Unique key: unique value the value of this field cannot be duplicated, but can be empty

Primary key primary key:

Used to uniquely identify a record

A table can have only one primary key at best

Cannot be empty

Cannot be repeated

Auto_increment automatic growth: when a new record is inserted, it automatically adds 1 to the maximum value of this field, and the type of this field must be an integer. Often used with the primary key, but not necessarily with the primary key.

This is the end of the answer to the question about how to get started with MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

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.