MySQL Usage Summary

DBMS for database management systems

Dbms,database Management System Database management, simply said: is a large software to manage data. The DBMS primarily manages, maintains, and processes data, or handles the security and integrity of the data.

Common DBMS Systems:

Small database management system developed by Access:microsoft Company;

SQL Server:microsoft company developed for large and medium-sized websites;

Oracle: Oracle, a large-scale or ultra-large data application developed in the United States;

MySQL: The perfect combination linux+apache+php+mysql,mysql was developed by the Swedish AB Company and is now being acquired by Oracle.

Related Concepts in the database

database : is a warehouse where data is stored. For example: equivalent to a folder in the system. Like a file cabinet for a company.

Data Sheet : The specific place where the data is stored. Equivalent to different types of files in the system. Like a company file.

Record: A row in a data table, called a record

Therefore, when creating a data table, be sure to create an ID column that identifies "this is the number of records", the value of the ID column cannot be the same, and must be unique, equivalent to the identity card number.

fields: each column in a table is called a field , and every field in the database is specified, such as: The data type of the field, the judgment of null and void, the automatic growth, and so on.

data : The intersection of rows and columns is the real " data ".

MySQL Operation: ( 1 ) using PhpMyAdmin Software Operations ( 2 ) in MySQL Client to operate ( i.e. DOS command line )

Log in and exit the MySQL client

First, log in 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: Represents the hostname or IP address of MySQL, such as:-h127.0.01-hlocalhost

-U: Represents the user name in MySQL, default is root

-P: Represents the password used in MySQL, the default is root

Attention:

Each paragraph in the grammar is separated by a space; If you do not want others to see the password you entered, the login MySQL client can not lose the password, direct enter, will prompt for the password, this time the secret is to "*" number display; After installing Phpstudy, there is only one root user, it is super Administrator.

Ii. commands to exit the MySQL client

Exit or quit

in the DOS command line, you can use the "up and down arrows" on the keyboard to re-recall the commands you have used to perform.

Third, modify the root user's password

1, use the Mysqladmin.exe program to modify (under DOS command)

c:\>mysqladmin.exe –hlocalhost–uroot–proot password new password

Note: Mysqladmin.exe the use of this command is used in DOS commands, not MySQL of the client. The new password can be unquoted;

2, in the MySQL client to modify the password (current account password)

Syntax format:

mysql> set password=password(' new password ');

Attention:

password () is a MySQL one of the cryptographic functions

MD5 () is a PHP one of the cryptographic functions in

Database operations

First, create a database

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

Parameters:

CreateDatabase is the command to create a database;

[Ifnot EXISTS] is optional, if not present, then create;

Db_name: Is the name of the database to be created, named in the same way as a variable, but without the $ symbol;

[CHARSET] Sets the character set of the database, if not set, the default character set that will be used by MySQL latin1;

Example:

CreateDatabase IF not EXISTS zhangsan CHARSETUTF8;

CreateDatabase Zhangsan; is using the default character set Latin1

Ii. Show All databases

Syntax: Show Databases

Third, delete the database

Syntax:DROP DATABASE [IF EXISTS] db_name

Description

Dropdatabase is the command to delete the database;

[Ifexists] is optional, if present, and then deleted, there will be no error information;

Example:

Dropdatabase IF EXISTS Zhang; Delete Database Zhang

Iv. Selecting a Database

Syntax: Use db_name

V. Changing the database default character set

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

Clients (Client section):DEFAULT-CHARACTER-SET=GBK

Server-side (Server section):default-character-set=latin1

2. Use the command to modify the MySQL client

ALTER DATABASE dbname DEFAULT CHARACTER SET GBK

Data table Operations

A Web site can have more than one table: News sheet, Administrator table, product table, message form.

First, show all tables in the current database

Syntax: Show tables from db_name

Description: Query All tables in a database

Second, create a data table

Syntax structure:

CREATE TABLE table_name(

Column Name 1 column property of type Type column,

Column Name 2 column property of the data type column,

Column Name 3 column property of the data type column

)

Parameter description:

Column Name 1, specifying the name of each field, named the same as the variable;

Data type of column: Specify what data to store for each field;

Properties of the column: more detailed settings for the column

Example:

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

Common Properties for columns

(1) NOT NULL | NULL Specifies whether the value of the column can be null or not, the default is null, and the General ID field cannot be empty;

(2) Default Default_value, set a value for a column, the default value can be a string or a number.

Example: Sex tinyint not null DEFAULT 1;

(3) Auto_increment: Specify an automatic growth type, generally referred to as the ID field, you can ensure that the value of the ID is never repeated;

(4) Primary key: Is the primary key index. The primary key index must be added to the field that has the Auto_increment attribute. The primary key index is only one, and the others are normal indexes.

Index: the equivalent of a book directory index , through the catalog query to see the content, more than the direct turn over the book to be much faster.

ID fields are the fields that are required for each data table, ID The field must have these three properties: Not null , auto_increment , PrimaryKey .

Third, modify the data table

Syntax: ALTER TABLE table_name ...

Tip: Use phpMyAdmin to modify data tables

Iv. Deleting Data sheets

Syntax:DROP TABLE table_name from 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, character, text, date

First, the integral type

tinyint: Minimum integer, 1 bytes, -128~127 (signed) 0-255, such as: gender, message read

smallint: small integer, 2 bytes, 0-65535, for example: salary

Mediumint: A medium Integer, 3 bytes, 16.77 million

int: General integer, 4 byte representation, 4.2 billion, for example: click-through rate of article

bigint: Large integer, 8-byte representation, 2^64-1

Second, floating-point type

float (m,d) : Can be exactly after the decimal point 7 bit, m represents the total length, D represents the number of decimal digits;

Float (6,2): Indicates a total length of 6 bits (without a decimal point), and the number of decimal places is 2 bits. Example: 1200.65

Double: can be exactly 15 digits after the decimal point.

Three, character type

char (M): fixed width , value range 0-255 characters, such as: News title, post title, etc.

CHAR[10], let's say I've saved 5 characters, and other spaces will be filled with spaces.

Parameter m specifies the width of the field;

varchar (M): auto-retractable , with a range of 0-65535 characters, such as: News headlines, post titles, etc.

varchar (10), assuming I've saved 5 bytes, it should be 6, and the extra 1 is the character length.

Iv. Text Type

tinytext,1 bytes, 0-255 characters

TEXT , 2 bytes, 0-65535 of characters

mediumtext,3 bytes, 16.77 million characters

longtext,4 bytes, 4.2 billion characters

V. Date and Time type

Date: Format YYYY-MM-DD storage, for example: 2014-08-01

Time: Format HH:MM:SS storage, such as: 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 does the client in MySQL display Simplified Chinese?

because MySQL the client default character set, which should be GBK , so when displaying, change the current display character set to GBK ;

Format: Set names GBK

You only need to set the character set of the array library, and the data table inherits the character set from the database.

Show command

1. Display all databases of MySQL host: showdatabases;

2. Show all tables in a database: Show TABLES [from Db_name];

3. Display the statement when creating the database: Show Create DB db_name

4. Display the structure of a table in a database: Show Table table_name [from db_name]

Introduction to SQL

sql,structured Query Language structured queries language. SQL is the language for manipulating and managing databases.

Common SQL statements: Add, delete, modify, query.

One, add data INSERT into

Syntax: INSERT intotable_name (title,author,content,addate) VALUES (' from August onwards everyone can apply for City account ', ' admin ', ' content ... ', 11010101010)

Precautions:

(1) The list of fields and the contents of the values must be the same number and order;

(2) ID field does not need to tube it, it is an auto-grow type.

Second, delete the record delete from

Syntax: deletefrom table_name [WHERE condition ]

Example:

Deletefrom News WHERE id=3; Delete a id=3 record

Deletefrom News WHERE id<4; Delete a id<4 record

Deletefrom News WHERE Id>10 and id<20; Delete a 20>id>10 record

Deletefrom News WHERE id>10 or author= ' admin '; Delete all records of id>10, or author= ' admin ' record

TRUNCATE table_name

Function: Delete all data and return the ID value to 0.

Description: Deleting all data with Deletefrom is much faster.

Example: Truncatenews

Third, modify the record UPDATE SET

Syntax: updatetable_name SET Field 1 = new value 1, field 2 = new value 2 [Where condition]

Note: When updating data, be sure to specify the where condition, otherwise the entire table will be updated to the same

Example:

Updatenews SET author= ' Zhangsan ', hits=100000 WHERE id=120 //id=120 Record modification

Updatenews SET title= ' news new title ' whereid=130;

......

Four, query data select

Syntax: SELECT *| Field List From table_name [WHERE condition ][order by field asc| DESC] [ result of limit limit output ]

Parameters:

*: The data for all fields will be listed, usually when the field is small;

Field List: Specifies the field to query, separated by commas, for example:SELECT id,title,addate from 007_news

[Where Condition] specifies the condition of the query;

Which fields to sort by [order by], sort by ascending (ASC) and descending (DESC)

[Limit] limits the number of records to output

WHERE conditional clauses

Like operator: To implement a field fuzzy query, such as: query all the headings contain all records in Beijing.

%: corresponds to the matching symbol "*" In Search in Windows system

Wheretitle like '% Beijing '; The title contains records of Beijing

Whereauthor like ' a% '; Query for authors that begin with the "a" character

......

ORDER BY sort clause

Sort a field or multiple fields by two keywords: ascending (ASC) default, Descending (DESC)

SELECT * FROM News ORDER by ID DESC //In descending order of ID fields

SELECT * FROM News order by author ASC,addate DESC //author ascending, chronological order

Limit clause

Limit the number of records to output.

Syntax: LIMIT startrow,rows

Parameter: StartRow indicates the start line number, and rows indicates how many records to display

Tip: The limit statement is primarily applied to the page's data paging.

Example: LIMIT 0,10//From line No. 0, Output 10 records, excluding line No. 0.

LIMIT 1,10//From line 1th, output 10 records, excluding line 1th.

LIMIT 15,10//From line 15th, output 10 records, excluding line 15th.

MySQL Usage Summary