Simple mysql database operations required in php, phpmysql_PHP tutorial

Simple mysql database operations required in php. Simple mysql database operations required in php, phpmysql1. database connection 1.1 use the windows command line to connect to the database server. Several DOS commands are not used in php in the DOS environment for simple operations on the mysql database. phpmysql
1. database connection 1.1 use windows command line to connect to the database server

Several DOS commands

In the DOS environment, there is no semicolon behind the command, and in the MySQL environment, there is a semicolon behind the command

Enter the drive letter:

Syntax: drive letter:

Enter a folder under the drive letter

Syntax: cd path

Parent Directory :../

Enter the root directory :/

1.2 parameters required to connect to the MySQL server

Host-h

Username-u

Password-p

Port-P

E: \ wamp \ bin \ mysql \ mysql5.6.17 \ bin \ mysql-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: \ wamp \ bin \ mysql \ mysql5.6.17 \ bin \ mysql-uroot-p

2. log out of the database.

A) exit;

B) quit;

C) \ q;

2. database operation 2.1 create a database

Syntax:

Create database name;

B) if the created database already exists, an error is returned;

C) make a judgment when creating the instance. if the instance does not exist, create the instance;

Syntax: create database if not exists database name;

D) specify the character encoding when creating a database

Syntax: create database name charset = character encoding;

2.2 query a database

Syntax: show databases;

2. 3. display the database creation statement

Syntax: show create database name;

2.4 Change database

Change the character encoding of a database

Syntax: alter database name charset = character encoding;

2.5 delete a database

A) syntax; drop database name;

B) if you delete a database that does not exist, an error is returned.

C) before deleting a database, check whether the database exists. If yes, delete the database.

Syntax: drop database if exists database name;

2.6 select database

A) syntax: use database name;

3. database table operations

3.1 Concepts

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

A column is also called a field. a column is a field. Fields are also called attributes.

A table contains multiple fields.

3.2 create a table

Syntax:

Create table name (

Field 1 data type [null | not null] [default] [auto increment] [primary key],

Field 2 data type

..........

)

Not null: Not empty

Default: Default value

Auto increment: automatic growth

Primary key: Primary key

(Feature: it cannot be repeated. it cannot be blank. a table can only have one primary key. a primary key can contain multiple fields)

3.3 data type

Int: integer

Decimal (total digits, Decimal places): saved Decimal places

Char (): Character (fixed length)

Varchar (): Character (variable length)

Text: large text

3.4 View all tables

Syntax: show tables;

3.5 display the statement for creating a table

Syntax: show create table name [\ G];

\ G: the table and create table fields are vertically arranged.

3.6 display table structure

Describe table name; (describe can be abbreviated as desc)

Describe: Description

3.7 delete a table

Syntax: drop table name;

Delete multiple tables: drop table 1, table 2 ,....;

3.8 create a complex table

4. data operation 4.1 insert data (add)

A) syntax: inset into table name (field name 1, field name 2...) values (value 1, value 2 .....)

B) the inserted field can be different from the database field order, but the values must be consistent with the inserted field order.

C) when the inserted values are consistent with the order and number of fields in the data table, the inserted fields can be omitted.

D) auto-increment insert

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

E) insert the default value

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

4.2 modify data)

Syntax: update table name set Field 1 = value 1, field 2 = value 2 where condition

For example, change Li Bai's gender to female.

Update stu set stu_sex = 'female 'where stu_name = 'Li Bai ';

Turn all gender into female.

Update stu set stu_sex = 'female ';

4.3 query data (query)

A) syntax:

Select column name from table [where condition] [order by sorting]

[Limit the start position and number of records obtained];

Sort: asc in ascending order

Desc in descending order

The start position in the Limit is from 0.

B) operator

I. Comparison operators

Operator description

>
> =
<
<=
=	Equal
<>	Not equal

Ii. logical operators

Operator description

And	And
Or	Or
Not	Non

Example: 1. query the names and gender of all students

Select stu_name, stu_sex from stu;

2. query all information of all students.

Select * from stu;

3. query information about all boys.

Select * from stu where stu_sex = 'male ';

4. query information about all girls and boys in Beijing.

Select * from stu where stu_sex = 'female 'or (stu_sex = 'male' and stu_address = 'Beijing ');

5. Sort By score from high to low.

Select * from stu order by stu _ score desc;

6. take the information of the first two students.

Select * from stu limit 2;

7. take the scores of the two students from the second student.

Select * from stu limit 1, 2;

8. find the first two.

Select * from stu order by stu_score desc limit 2;

4.4 delete data

Syntax: delete from table name [where condition];

Example: 1. delete Li Bai

Delete from stu where stu_name = 'Li Bai ';

2. delete all data in the table.

Delete from stu;

Protocol 1. database connection 1.1 use windows command lines to link several DOS commands on the database server. in the DOS environment, the command is not followed...