编程知识 cdmana.com

MySQL in 5 days: basic operation of concept, database and table

Database concept


database (Database) Is in accordance with the Data structure to organize Storing and managing data A warehouse built on computer storage devices .

database : A warehouse for storing data

Database classification

Network database


Network database refers to the introduction of database technology into the computer network system , With the help of network technology, a large number of information stored in the database will be released in time ; And the computer network with the help of mature database technology to effectively manage all kinds of data in the network , And realize the real-time dynamic data interaction between the user and the database in the network .

Hierarchical database


Hierarchy model is essentially a kind of directed ordered tree with root nodes ( In mathematics " Trees " It is defined as a connected graph with no return ).

relational database


relational database , It's based on relational model Based on the database , With the help of set algebra and other mathematical concepts and methods to deal with the data in the database .

Another way to distinguish databases : Based on the storage medium

There are two types of storage media : Disk and memory

Relational database : Stored on disk

Non relational database : Stored in memory

Relational database

Basic concepts


relational database , It's based on relational model Based on the database , With the help of set algebra and other mathematical concepts and methods to deal with the data in the database . All kinds of entities in the real world and the relationships between them are represented by relational models . The relational model was developed by Edgar · Yukod 1970 First proposed in , And cooperate with “ The twelve laws of COD ”. Now, although there are some criticisms on this model , But it's also the traditional standard for data storage . The relationship model is made up of Relational data structure Set of relational operations Relationship integrity constraints Three parts .

Relational data structure : How is the data stored , It's a two-dimensional table

The essence : Two-dimensional table

full name

Age

height

weight

Zhang San

30

187

70

Li Si

40





Set of relational operations : How to associate and manage the corresponding stored data ,SQL Instructions

Get Zhang San's age : The known condition is name

Select Age from Two-dimensional table where full name = Zhang San ;

Relationship integrity constraints : There is a corresponding relationship within the data , And there is a corresponding relationship between data and data

full name

Age

height

weight

Zhang San

30

187

70

Li Si

40





In table constraints : The corresponding specific column can only put the corresponding data ( Don't leave it out of order )

Inter table constraints : Every entity in nature has a corresponding relationship ( Foreign keys )

Typical relational database


Oracle、DB2、Microsoft SQL Server、Microsoft Access、MySQL、SQLite

Small relational databases :Microsoft Access,SQLite

Medium sized relational database :SQL Server,Mysql

Large relational databases :Oracle,DB2

Mysql At present Oracle It's a company : Affiliated to the Oracle

SQL Introduce

SQL Basic introduction


Structured query language (Structured Query Language) abbreviation SQL, It's for a special purpose Programming Language , Is a database query and programming language , For data access and query 、 Update and manage relational database system ; It is also the extension of database script file .

SQL It is specially designed for relational database .

SQL classification


1、 Data query language (DQL:Data Query Language):

Its statement , Also known as “ Data retrieval statements ”, To get data from a table , Determine how the data is presented in the application . Reserved words SELECT yes DQL( Also all SQL) The most used verb , other DQL The common reserved words are WHERE,ORDER BY,GROUP BY and HAVING. these DQL Reserved words are often associated with other types of SQL Statement together .

Dedicated to querying data : The representative instruction is select/show

2、 Data operation language (DML:Data Manipulation Language):

Its sentences include verbs INSERT,UPDATE and DELETE. They are used to add , Modify and delete rows in the table . Also known as action query language .

Dedicated to writing data : The representative instruction is insert,update and delete

3、 Transaction language (TPL):

Its statement ensures that it is DML All rows of the table affected by the statement are updated in time .TPL Statements include BEGIN TRANSACTION,COMMIT and ROLLBACK.( Not all relational databases provide transaction security )

Dedicated to transaction security :transaction

4、 Data control language (DCL):

Its sentences are passed through GRANT or REVOKE Get permission to , Determine the access of individual users and user groups to database objects . some RDBMS You can use GRANT or REVOKE Control access to form columns .

Dedicated to rights management : The representative instruction is grant and revoke

5、 Data definition language (DDL):

Its sentences include verbs CREATE and DROP. Create a new table or delete a table in the database (CREAT TABLE or DROP TABLE); Index the table, etc .DDL It includes many reserved words related to obtaining data in the human database directory . It's also part of the action query .

Dedicated to structural management : For instructions create and drop(alter)

MySQL Basic introduction


MySQL It's a Relational database management system , The Swedish MySQL AB Companies to develop , At present belongs to the Oracle Its products .MySQL Is one of the most popular relational database management systems , stay WEB Application aspect ,MySQL It's the best RDBMS (Relational Database Management System, Relational database management system ) Application software .

AB Company being Sun Company purchase ---》Oracle Buy again Sun company

1、 Mysql It's an open source and free database product

2、 Mysql Yes PHP Your support is the best (wamp perhaps lamp)

Mysql The operation instruction used in is SQL Instructions

Start and stop MySQL service


Mysql It's a kind of C/S structure : Client and server

The software corresponding to the server :Mysqld.exe

Command line mode


adopt Windows Open down cmd controller , Then use the command to manage

Net start service (mysql): Opening service

Net stop mysql: Close the service

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

System service mode


Premise : Installed Mysql Will be mysql Add to Windows In the service of

The way 1 Access to services :

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


The way 2 Access to services : From the command line :services.msc

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Through service to Mysql Server Management

programme 1: Right click Service , Then choose on or off

programme 2: Double click the service , Enter the service details screen , You can click the start or stop button

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Login and logout MySQL System


Through the client (mysql.exe) Connect and authenticate with the server , It can be operated

Usually : The server and the client are not on the same computer

Sign in


1、 find mysql.exe( adopt cmd Console : If you specify mysql.exe The path is the environment variable , You can go directly to ; without , Then you have to get into mysql.exe Location path )

2、 Enter the corresponding server address :-h:host -h[IP Address / domain name ]

3、 Enter in the server Mysql Listening port : -P:port –P:3306

4、 enter one user name :-u:username -u:root

5、 Input password :-p:password –p:root

Connection authentication basic syntax :

Mysql.exe/mysql -h The host address -P port -u user name -p password

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


matters needing attention

1、 Usually, the port can default to :mysql Strong ports are usually 3306

2、 You can enter the password first -p, Line break directly , Then enter the password as ciphertext

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

sign out


Disconnect from the server : Usually Mysql The number of servers available is limited , Once the client runs out , The suggestion is to disconnect .

Suggested way : Use SQL Instructions provided

Exit; //exit Semicolon

\q; //quit abbreviation

Quit:

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Mysql Server architecture


Mysql The server architecture consists of the following layers :

1、 Database management system ( Outermost layer ):DBMS, Manage everything on the server side

2、 database ( The second floor ):DB, A warehouse dedicated to storing data ( There can be many )

3、 Two dimensional data table ( The third level ):Table, Dedicated to storing data for specific entities

4、 Field ( The fourth level ):Field, Specific storage of certain types of data ( The actual storage unit )

Several key words commonly used in database

Row: That's ok

Column: Column (field)

Basic operation of database


Database is the outermost layer of data storage ( The largest unit )

Create database


Basic grammar :create database Database name [ Library options ];

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Library options : Database related properties

Character set :charset Character set , Represents the default character set of data stored in all tables in the current database ( If not currently specified , So using DBMS default )

Proofreading set :collate Proofreading set

Create database Database name charset Character set name ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Display database


Every time a user passes SQL Instruction to create a database , Then the system will generate a corresponding folder to store data (data)

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Actually , There is one in every database folder opt file , Save the corresponding database options .

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Show all


Basic grammar :show databases;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Display part


Basic grammar :show databases like ‘ Matching mode ’;

_: Match single character at current position

%: Matches multiple characters at the specified position

To get to my All databases at the beginning : ‘my%’;

obtain m start , The first letter is not sure , Finally database The database of ;’m_database’;

To get to database The end of the database :’%database’;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Display the database creation statement


Basic grammar :show create database Database name ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Select database


Why choose a database ? Because data is stored in a data table , The table exists in the database . If you want to manipulate data , Then you must enter the corresponding database .

Basic grammar :use Database name ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

modify the database


Modify database character set ( Library options ): Character sets and proofreading sets

Basic grammar :alter database Database name charset = Character set ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Once the modification is successful , So the corresponding opt It will be reflected in the document

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Whether the database name can be modified ?mysql5.5 It can be modified before rename command ; however 5.5 After that, you can't .

Delete database


Basic grammar :drop database Database name ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Deletion is simple , But remember to do a good job in safe operation : Make sure there's no problem with the data .( important )

After deleting the database : The corresponding data storage folder will also be deleted (opt The file was also deleted )

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Data table operation

Create data table

Normal create table


Basic grammar :create table Table name ( Field name Field type [ Field properties ], Field name Field type [ Field properties ],…) [ Table options ]

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


The above error description : The table must be placed in the corresponding database : There are two ways to mount a table to a specified database

1、 Add the database name before the data table name , use “.” Connect to : database . Data sheet

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


2、 Before creating a data table, you can enter a specific database :use Database name ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Table options : Similar to database options

Engine: Storage engine ,mysql How to store data , There is one by default innodb(5.5 The default used to be myisam)

Charset: Character set , Only valid for your current watch ( Higher level than database )

Collate: Proofreading set

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Copy existing table structure


Make a copy of the existing table ( Just copy the structure : If there is data in the table that is not copied )

Basic grammar :create table The new name of the table like Table name ; // Just use the database . Table name , You can access the table names of other databases under any database

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Show data sheet


Every time a data table is created , Then some files will be created under the corresponding database ( It's about the storage engine )

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Be careful : This structure file comes from innodb Storage engine ,innodb All files of the storage engine are stored outside ibdata In file

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Show all tables


Basic grammar :show tables;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Match display


Basic grammar :show tables like ‘ Matching mode ’;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Display table structure


Essential meaning : Display the field information contained in the table ( name , type , Properties, etc )

Describe Table name

Desc Table name

show columns from Table name

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Show table creation statement


Look at the statement when the data table was created : The result of this statement is no longer what the user entered before .

Basic grammar :show create table Table name ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Mysql There are multiple statement terminators in

; And \g The effect is the same , It's all fields in the top row , Here is the corresponding data

\G The fields are vertical on the left , The data is on the right

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Set table properties


Table properties refer to table options :engine,charset and collate

Basic grammar :alter table Table name Table options [=] value ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Be careful : If the database has been determined , There's a lot of data in it , Don't change table options easily ( Character sets don't matter much )

Modify table structure


Modify the name of the table :rename table The old name of the table to The new name of the table

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Modify table options :alter table Table name Table options [=] The new value

New fields :alter table Table name add [column] new field name Column type [ Column properties ] [ Location first/after Field name ]

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Field location : Where the field is to be stored

First: Before so and so ( front ), First field

After Field name : After a specific field ( default )

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Modify field name :alter table Table name change Old field name new field name Field type [ Column properties ] [ New location ]

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Modify field type ( attribute ):alter table Table name modify Field name new type [ new property ] [ New location ]

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Delete field :alter table Table name drop Field name

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Delete table structure


Basic grammar :drop table Table name [, Table name 2…], You can delete multiple data tables at the same time

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Bulk delete table

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Data base operations

The insert


Essential meaning : Taking data to SQL Stored in the specified data table ( Field ) Inside

Basic grammar : Insert data... Into the specified fields in the table

Insert into Table name [( Field list )] values( List of corresponding fields )

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


1、 Be careful : Back (values in ) The corresponding value list only needs to correspond to the previous field list ( It doesn't have to be exactly the same as the table structure )

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


2、 Be careful : The field list does not have to have all the fields in the table

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Basic grammar : Insert data... Into all fields in the table

Insert into Table name values( Corresponding table structure ) // The value list must be consistent with the field list

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Query operation


Query all the data in the table :select * from Table name ; //* Means match all fields

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Some fields in the query table :select Field list from Table name ; // Use commas... For the field list “,” separate

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Simple condition query data :select Field list /* from Table name where Field name = value ; //mysql There is no == Symbol

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Delete operation


Basic grammar :delete from Table name [where Conditions ]; // without where Conditions : This means that the system will automatically delete all data in this table ( Use with caution )

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

update operation


to update : Modify the data ( It is usually to modify some field data )

Basic grammar :update Table name set Field name = The new value [where Conditions ];// without where Conditions , Then the corresponding field in all tables will be modified to a uniform value .

5 Heaven plays MySQL: Concept 、 Basic operation of database and table

Character set

The concept of character encoding


character (Character) It's a general term for all kinds of words and symbols , Including national characters 、 Punctuation 、 Graphic symbols 、 Numbers etc. .

Anything you see in a computer is made up of characters .

Character encoding (character code) It's computers for all kinds of symbols , A binary storage code in a computer .

Character set concept


Character set (Character set) It's a collection of characters , There are many types of character sets , Each character set contains a different number of characters .

Common character set names :ASCII Character set GB2312 Character set 、BIG5 Character set 、 GB18030 Character set 、Unicode Character set, etc. . The computer should deal with all kinds of characters accurately , Character encoding required , So that computers can recognize and store all kinds of characters . The number of Chinese characters is large , And it can be divided into simplified Chinese and traditional Chinese , And computers were originally designed for English single byte characters , therefore , Code Chinese characters , Is the technical basis of Chinese information exchange .

Set all client character sets


If you go straight through cmd Under the mysql.exe Insert Chinese data , Then there could be a mistake

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Cause of error :

1、 The user is through mysql.exe To operate mysqld.exe

2、 real SQL Execution is Mysqld.exe To execute

3、 mysql.exe Send data to mysqld.exe When , There is no corresponding symbol rule ( Character set ), and mysqld I don't have the ability to judge myself , Will use their own default ( Character set )

Solution :mysql.exe The client tells the character set it uses before data operation mysqld

Cmd Under the mysql.exe There is only one character set by default :GBK

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Mysql.exe If you tell Mysqld.exe The corresponding character set type is gbk?

Shortcut :set names Character set

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Re insert data : chinese (GBK)

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Deep principle : client , Server side , adjoining course (show variables like ‘character_set_%’)

Mysql.exe And Mysqld.exe There are three levels of relationship between them

The client passes in data to the server :client:character_set_client

The server returns data to the client :server:character_set_results

The connection between the client and the server :connection:character_set_connection

Set names The nature of character sets : It's a character set that can get through three layers at once , Become consistent .

There are three variables in the system to record the character set corresponding to these three relationships :show variables like ‘character_set_%’;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


View the corresponding character set relationship of a new client

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Change the value of the server-side variable

Set Variable name = value ;

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Results of data insertion and re viewing : Insert OK, But check the mess

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Change the result character set to GBK

5 Heaven plays MySQL: Concept 、 Basic operation of database and table


Connection Just for the convenience of client and server for character set conversion .

Set names gbk;

Set character_set_client = gbk; // In order for the server to recognize the data from the client

Set character_set_connection = gbk;// Better help client and server for character set conversion

Set character_set_results = gbk;// In order to tell the client server all the returned data character sets

版权声明
本文为[Yan Jiadong]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/04/20210422043503653h.html

Scroll to Top