Simple operations of the mysql database that need to be used in php, phpmysql_PHP tutorial

Simple operations of mysql database that need to be used in php, phpmysql

1. Database connection

1.1Use windows command line to connect to the database server

Several DOScommands

In the DOS environment, there is no semicolon after the command. In the MySQL environment, there is a semicolon after the command

Enter drive letter:

Syntax: Drive letter:

Enter a folder under the drive letter

Syntax: cd Path

Parent directory: ../

Enter the root directory: /

1.2LinkMySQLParameters required by the server

Host Host -h

Username Username -u

Password Password -p

Port Port -P

E:wampbinmysqlmysql5.6.17binmysql -hlocalhost -uroot -p -P3306

If the port number is 3306, the port number can be omitted.

If the link is local MySQL, the database address can also be omitted.

E:wampbinmysqlmysql5.6.17binmysql -uroot -p

2. Exit the database

A) exit;

B) quit;

C) q;

2. Database operation

2.1Create database

Grammar:

Create database Database name;

B)If the created database already exists, an error will be reported;

C)Check when creating, if it does not exist, create it;

Syntax: create database if not exists database name;

D) Specify the character encoding when creating the database

Syntax: create database Database name charset=Character encoding;

2.2Query database

Syntax: show databases;

2.3.Display the database creation statement

Syntax: show create database Database name;

2.4Change Database

Change the character encoding of the database

syntax: alter database database name charset=Character encoding;

2.5Delete database

A)Syntax; drop database Database name;

B)If you delete a non-existing database, an error will be reported.

C)Before deleting, it is necessary to determine whether the database exists, and only delete it if it exists.

Syntax: drop database if exists Database name;

2.6Select database

A) syntax: use database name;

3. Database table operations

3.1A few concepts

A line is also called a record, and a line is a record.

Columns are also called fields, and a column is a field. Fields are also called attributes.

A table contains multiple fields.

3.2 Create table

Grammar:

Create table Table name(

Field1 Data type [null | not null] [default] [auto increment] [primary key],

Field2 Data Type

..........

)

Not null :Not empty

Default: Default value

auto increment:Automatic increase

Primary key: Primary key

(Features: cannot be repeated, cannot be empty, a table can only have one primary key, and the primary key can be composed of multiple fields)

3.3Data Type

Int:Plastic surgery

            Decimal(Total number of digits, number of decimal places): Save decimal

Char(): Character (fixed length)

Varchar(): Character (variable length)

text : Large text

3.4View all tables

Syntax: show tables;

3.5Display the statement that creates the table

Syntax: show create table Table name [G];

                        G: means table and create tablefield vertical arrangement

3.6Show table structure

Descrip Table name; (you can description as desc

Describe:Describe

3.7Delete table

Syntax: drop table table name;

                                                                                                                                                                                                                                                                             Delete multiple tables: 🎜>....; 3.8Create complex tables

4. Data operations

4.1Insert data (increase)

                       A) Syntax: inset into table name (field name1, field name2...)values(value1, value2.....)

                         B)The inserted fields can be inconsistent with the order of the fields in the database, but the order of the values ​​and the inserted fields must be consistent

C) When the inserted value is consistent with the order and number of fields in the data table, the inserted field can be omitted .

D)Insertion of automatic growth

Insert into stu values(null,'Li Qingzhao','female','Shanghai',78);

E)Insertion of default value

Insert into stu values(null,'Xin Qiji','male',default,90);

4.2Modify data (change)

syntax : update table name set field1=value1, field2=value2 wherecondition

Example: Change Li Bai’s gender to female.

Update stu set stu_sex = 'Where stu_name =' Li Bai ";

Make all genders female.

Update stu set stu_sex = 'female';

4.3Query data (check)

A) Grammar:

Select The name From Table [where condition ] [order by Sort]

[limit Limit starting position, number of records obtained];

Sort: Ascending asc

Descending order desc

The starting position in Limit starts from 0.

                B)Operator

i.Comparison operator

Operators Description

Logical operators