MySQL extension in PHP: MySQL database Overview

MySQL extension in PHP: MySQL database Overview

I. SQL: Structured Query Language

Structured Query Language (Structured) is a standard Language used to Query and modify Database Information and manage and maintain databases. The SQL language has a simple structure and powerful functions, and is supported by many DBMS.

SQL is divided into four parts:

Data Definition Language (DDL ):Defines and manages data objects, including databases and data tables. Such as create/drop/alter

Data Operation Language (DML ):Operate on data contained in database objects, such as insert, update, and delete.

Data Query Language (DQL ):Query the data contained in the database object and return the data result, such as select

Data Control Language (DCL ):Manage database objects, including permission management and data changes, such as grant, revoke, commit, and rollback.

Ii. Common MySQL operations

1. Connection and close:Mysql-h [server host address]-u user name-p User Password quit and exit are used to close the connection

2. Create a user and authorize the user:GRANT permission ON database. data table TO user name @ login host identified by "password"

1: // Add a new user named phper password dwqs. log on to any host and have the permission to query, insert, modify, and delete all databases.

2: // Log On As the root user before creating a user

   3: GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO phper@"%" IDENTIFIED BY "dwqs"

   4:  

5: // only create a user for the mydb database and authorize it

   6: GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO phper@localhost IDENTIFIED BY "dwqs"

3. Database Operations

1: // create a database named test

   2: create database [if not exists] test;

3: // delete test

   4: drop database [if exists] test

5: // display the Database List

   6: show databases

7: // use the test database

   8: use test

4. Data Table operations

1: // create a table in test with the table name dwqs

   2: create table dwqs 

   3: <

   4: id int not null auto_increment,

   5: name varchar(20) not null default '',

   6: site varchar(20) not null default 'www.ido321.com,

   7: email varchar(15) not null default ''

   8: >

1: // view the table in the test Database

   2: show tables

3: // view the structure of the dwqs table

4: desc dwqs or description dwqs

5: // Delete the dwqs table

   6: drop table dwqs

5. Data Table content management

1: // insert record

2: insert [into] Table name [(field name 1), (field name 2 ),..., (field name n)] values ('value 1', 'value 2 ',..., 'value n ')

3: // update records

4: update table name set field name 1 = expression 1 [, field name 2 = expression 2,..., field name n = expression n]

5: [where condition] [order by field] [number of limit rows]

6: // delete a record

7: delete from table name [where condition] [order by field] [number of limit rows]

8: // query records

   9: select [all|distinct]

  10: {*|table.*|[table.]field1[AS alias1],[table.]field2[AS alias2][,...]}

  11: from tablaname[,...][in externaldatabase]

12: [where condition] [group by field] [having condition] [order by field] [number of limit rows]

Note:In the query, distinct is used to exclude duplicate data and only one is returned. The default value is all. As is used to mark the column alias: field name as 'Alias '. In the query result, the field name is displayed as an alias. in is used for subqueries. You can also use like to perform fuzzy queries. For example, if like '% php %'-> contains php words, % indicates 0 or any character; like 'DW _ s'->, such as dwqs and dwrs, _ represents an arbitrary character. Group by is a group of query results. At the same time, you can embed SQL expressions or functions in select, such as count ();

6. modify a table

1: // This "modify" mainly modifies the table structure, such as adding fields and modifying field attributes.

2: alter table name action

3: // the specific content of the action

4: // Add a new field

5: alter table name add field Name Description [first | after column name]

6: alter table dwqs add qq int [14] not null // add a qq field to the dwqs table. The type is an integer and is not empty.

7: // modify a field

8: alter table name change (modify) List description

9: alter table dwqs change qq varchar (15) default 0 // change the qq field type to varchar, the default value is 0

10: alter table dwqs modify qq varchar (15) default 0 // change the qq field type to varchar, the default value is 0

11: // modify the table name

12: alter table old table name rename as new table name

Description: [First | after column name] location of the new field in the Table. first inserts the field into the first column, and after inserts the column Name field into the specified column name. This value is not specified. It is added at the end by default.

The difference between modify and change: In addition to changing the type, change also changes the column name. All qq fields appear twice. The first is the old column name, and the last is the new column name. Modify does not have the function of modifying the column name. All qq appears only once.