Mysql usage summary, mysql Summary

Mysql usage summary, mysql Summary
Database Management System (DBMS)

DBMS, Database Management System Database Management, Simply put: is to manage a dataLarge software. DBMS manages and maintains data, or processes data security and integrity.

Common DBMS systems:

ACCESS: A small database management system developed by Microsoft;

SQL SERVER: developed by Microsoft for large and medium-sized websites;

Oracle: applications developed by Oracle in the United States for large-scale or ultra-large data;

MySQL: a perfect combination of Linux + Apache + PHP + MySQL. MySQL is developed by a Swedish AB company and is now acquired by Oracle.

Related Concepts in Databases

Database: Is a data storageWarehouse. For exampleFolder. Like a company file cabinet.

Data Table: It is the specific place where data is stored. EquivalentDifferent types of files. Like a company file.

 

Record: the content of a row in a data table, called "One record"

Therefore, when creating a data table, we must create an id column to identify "the number of records". The values of the id column cannot be the same and must be unique, which is equivalent to the same id card number.

Field:Each column in a table is called"Field", Each field in the database is specified, such as the Data Type of the field, null and null judgment, automatic growth, and so on.

 

Data: The intersection of rows and columns is the true"Data".

MySQL operations: (1) Use phpMyAdmin software operations (2) operate on the MySQL client (that is, under the doscommand line)

 

Log on to and exit the MySQL client

1. log on to the MySQL client

Syntax format: mysql-h host name or IP address-u user name-p Password

Example:Mysql-hlocalhost-uroot-proot

Parameter description:

-H: indicates the MySQL host name or IP address, for example,-h127.0.01-hlocalhost.

-U: the username in MySQL. The default value is root.

-P: the password used in MySQL. The default value is root.

Note:

The fields in the syntax are separated by spaces. If you do not want others to see the password you entered, you can log on to the MySQL client without entering the password and press enter to enter the password, the password is displayed as "*". After phpStudy is installed, there is only one root user, which is a super administrator.

2. commands for exiting the MySQL client

Exit or quit

Under the doscommand line, you can use the "Up and down arrows" on the keyboard to re-execute commands that have been used.

 

3. Modify the password of the root user

1. Use the mysqladmin.exe program for modification (under the doscommand)

C: \>Mysqladmin.exe-Hlocalhost-uroot-proot password New password

Note: The command mysqladmin.exe is used in the doscommand, not the MySQL client.The new password can be unquoted;

 

2. Change the password of the current account on the MySQL client)

Syntax format:

Mysql> set password =Password('New password ');

Note:

Password () is an encryption function of MySQL.

Md5 () is an encryption function in PHP.

 

Database Operations

1. Create a database

Syntax:Create Database [if notexists] db_name [CHARSET]

Parameters:

CreateDatabase is a database creation command;

[Ifnot exists] is optional. If it does not exist, create it again;

Db_name: the name of the database to be created. The name is the same as the variable, but the $ symbol is not added;

[CHARSET] sets the character set of the database. If not set, the default Character Set latin1 of MySQL will be used;

Example:

Createdatabase if not exists zhangsan CHARSETutf8;

CREATEDATABASE zhangsan; // use the default Character Set latin1

 

2. display all databases

Syntax: show databases

 

Iii. delete a database

Syntax: drop database [if exists] db_name

Note:

Dropdatabase is a command for deleting databases;

[IFEXISTS] is optional. If it exists, it will be deleted without any error;

Example:

Dropdatabase if exists zhang; // Delete the database zhang

4. Select a database

Syntax: USE db_name

5. Change the default Character Set of the database

1. Change the MySQL configuration file: C: \ Program Files (x86) \ phpStudy \ MySQL \ my. ini

Client Section: default-character-set = gbk

Server Section: default-character-set = latin1

2. Use commands on the MySQL client to modify

Alter database dbname default character set gbk

 

 

Data Table operations

A website can have multiple tables: News table, Administrator table, product table, and message table.

1. display all tables in the current database

Syntax: show tables FROM db_name

Queries all tables in a database.

2. Create a data table

Syntax structure:

Create table table_name (

Attribute of the type column of column 1,

Attribute of the Data Type column of column name 2,

Attribute of the Data Type column of column name 3

)

Parameter description:

Column name 1: Specifies the name of each field. The name is the same as that of a variable;

Column data type: Specifies the data stored by each field;

Column attributes: More detailed column settings

Example:

CREATE TABLE 007_news(       id          int                        notnull auto_increment primary key,       title         varchar(50)            not null,       content    text                       null,       addate     int(12)                  not null);

 

Common attributes of a column

(1) not null | null specifies whether the column value can be null or not. The default value is null. Generally, the id field cannot be blank;

(2) DEFAULT default_value: Set the DEFAULT value of a column. The DEFAULT value can be a string or number.

Example: sex tinyint not null DEFAULT 1;

(3) auto_increment: Specifies an automatic growth type for a column. It generally refers to the id field, which ensures that the id value will never be repeated;

(4) primary key: primary key index. The primary key index must be added to fields with the auto_increment attribute. Only one primary key index can be used, and others are common indexes.

Index: equivalent to a bookDirectory IndexTo query the contents through the directory, it is much faster than directly turning the book.

The id field is a required field for each data table. The id field must have these three attributes: not null, auto_increment, and primarykey.

 

3. modify a data table

Syntax: alter table table_name ......

Tip: Use phpMyAdmin to modify the data table

 

4. delete a data table

Syntax: drop table table_nameFROM db_name

 

V. display table structure

Syntax: Describe table_name or Desc table_name

Function: displays the structure of a table.

MySQL Data Type

Integer, floating point, simplified, text, and date

 

I. Integer

Tinyint: minimum integer, expressed in 1 byte,-128 ~ 127 (Signed) 0-255, such as: gender, whether the email has been read

Smallint: small integer, expressed in 2 bytes, 0-65535, for example, salary

Mediumint: a medium-sized integer. The value ranges from 0.

Int: A general integer, expressed in 4 bytes, ranging from 0 to 42 billion. For example, the article click rate.

Bigint: A large integer in 8 bytes, 2 ^ 64-1

 

Ii. Floating Point

Float (m, d): it can be precise to 7 digits after the decimal point. m indicates the total length, and d indicates the number of decimal places;

Float (6, 2): indicates that the total length is 6 digits (excluding the decimal point), and the number of decimal places is 2 digits. Example: 1200.65

Double: It can be accurate to 15 digits after the decimal point.

Iii. Balanced type

Char (M ):Fixed width, With a value range of 0 to characters, such as news titles and post titles.

Char [10]. If I have five characters, other spaces will be filled with spaces.

Parameter M specifies the field width;

Varchar (M ):Auto ScalingThe value range is 0 to characters, such as news titles and post titles.

Varchar (10). Suppose I have five bytes, and its length should be 6. Here, the extra 1 is the character length.

Iv. Text

TINYTEXT, 1 byte, 0-25 characters

TEXT, 2 bytes, 0-characters

MEDIUMTEXT, 3 bytes, 0-characters

LONGTEXT, 4 bytes, 0-42 million characters

V. Date and Time Type

Date: Format YYYY-MM-DD storage, such:

Time: Format: HH: mm: ss storage, for example: 12: 09: 30

Datetime: Format YYYY-MM-DD HH: mm: ss Storage

Timestamp: Format YYYY-MM-DD HH: mm: ss

 

$ Addate = time ();

Date ("Y-m-d H: I; s ");

 

Note: How can I display Simplified Chinese on the MySQL client?

Because the default Character Set of the MySQL client should be GBK, you should change the current display character set to GBK during display;

Format: set names gbk

You only need to set the character set of the array library. The data table inherits the character set of the database.

 

Show command

1. display all databases of the MySQL HOST: SHOWDATABASES;

2. display all TABLES in a database: show tables [FROM db_name];

3. display the statement when creating a database: SHOW create database db_name

4. display the TABLE structure in a database: show table table_name [FROM db_name]

SQL Introduction

SQL, Structured Query Language. SQL is the language used to operate and manage databases.

Common SQL statements: add, delete, modify, and query.

 

1. Add data INSERT

Syntax: INSERT into table_name (title, author, content, addate) VALUES ('everyone can apply for a city hukou from October 1', 'admin', 'content ...... ', 11010101010)

Note:

(1) The number and sequence of fields and values must be consistent;

(2) The id field does not need to care about it. It is an automatic growth type.

 

Ii. DELETE record FROM

Syntax: DELETEFROM table_name [WHERE condition]

Example:

DELETEFROM news WHERE id = 3; // delete records with id = 3

DELETEFROM news WHERE id <4; // Delete the record with id <4

DELETEFROM news WHERE id> 10 and id <20; // Delete record 20> id> 10

DELETEFROM news WHERE id> 10 or author = 'admin'; // delete all records with id> 10 or author = 'admin' records

 

TRUNCATE table_name

Function: delete all data and return the id value to 0 again.

Note: It is much faster to delete all data with deletefrom.

Example: TRUNCATEnews

 

3. Modify record UPDATE SET

Syntax: UPDATEtable_name SET field 1 = new value 1, Field 2 = new value 2 [WHERE condition]

Note: When updating data, you must specify the WHERE condition. Otherwise, the entire table will be updated to the same

Example:

UPDATEnews SET author = 'hangsan ', hits = 100000 WHERE id = 120 // id = 120 record Modification

UPDATEnews SET title = 'news's new title' WHEREid = 130;

......

 

4. query data SELECT

Syntax: SELECT * | field list FROM table_name [WHERE condition] [order by field ASC | DESC] [LIMIT output results]

Parameters:

*: Data of all fields will be listed, which is generally used only when there are few fields;

Field List: Specifies the fields to be queried. Multiple fields are separated by commas, for example, SELECT id, title, addate FROM 007_news.

[WHERE condition] specifies the query condition;

[ORDERBY] which fields are sorted in ascending order (ASC) and descending order (DESC)

[LIMIT] LIMIT the number of output records

 

WHERE Condition Clause

Like OPERATOR: fuzzy query of fields. For example, query all records whose titles contain Beijing.

%: Equivalent to the matching symbol "*" in windows system search

WHEREtitle LIKE '% Beijing %'; // The title contains Beijing records

WHEREauthor LIKE 'a % '; // query the author starting with ""

......

 

 

 

Order By sort clause

Sort one or more fields. There are two keywords: ASC default and DESC)

SELECT * FROM news order by id DESC // sort the id field in descending ORDER

SELECT * FROM news order by author ASC, addate DESC // sort BY authors in ascending ORDER and in descending ORDER of time

 

 

Limit clause

Limit the number of records to be output.

Syntax: LIMIT startrow, rows

Parameter: startrow indicates the start row number, and rows indicates the number of records to be displayed.

Tip: The LIMIT statement is mainly used for pagination of webpage data.

For example, LIMIT 0th, 0th // output 10 records starting from rows, excluding rows.

LIMIT 1st // output 10 records starting from 1st rows, excluding rows.

LIMIT 15th // output 10 records starting from 15th rows, excluding rows.