MySQL, PHP, and mysqlphp

MySQL, PHP, and mysqlphp
Log on to MySQL mysql-hlocalhost-uroot-proot and exit MySQL exit

Each statement must be followed by a plus sign :--------------------------------------------

Show DatabaseShow databases; check the number of DATABASES on the MySQL server

 

Create a databaseCreate database db_name [if not exitsts] [CHARSET utf8];

Separate commands with spaces

Db_name custom database name, letters, numbers, and underscores

[If not exitsts] IF the created database already exists, no error is reported.

[CHARSET utf8] specifies the character set of the current database. The default value is 'Latin ',

Remember: do not add a horizontal line in the middle of utf8

 

Use of the SHOW command;

Show create database db_name; view the statement process for creating a database

 

Delete DatabaseDrop database db_name [IFEXITSTS];

[If exitsts] IF deletion exists, no error is returned.

 

Modify the character set of the mysql DATABASE: alert database db_name default characterset Character Set;

Data Table operations:

 

Select current dataDatabase ------> USEdb_name; display data table ---------> show tables; create data table ----------> CREATETABLE table_name (attributes of column 1, attributes of column 2 ,.....);

 

Example: create table table1 (id int not null auto_increment primarykey, title varchar (100) not null, author varchar (20) not null, source varchar (30) not null,

Hits int (5) not null default 0,

Is_ppt tinyint (1) not null default0, content text null, addate int (16) not null );

 

Auto_increment ---- Auto-increment primarykey ---- primary key

 

Property of the id field: Required ---- NOT null auto_increment primary key

Column type (mysql Data Type): integer, floating point, character, text, and date

Column attributes: NULL or not | NULL

Auto-increment: AUTO_INCREMENT can only be used for IDs. A table can only have one auto-increment attribute.

DEFAULT value: DEFAULT value

Primary Key: (Primary Index) There is only one primary key. PRIMARYKEY is generally assigned to the id field. A table can only have one primary key.

 

Integer: tinyint --- one byte (0-255) smalcap ---- two bytes (0-65535) int ---- four bytes (0-21 million) bigint ---- eight bytes ()

 

Float: float (M, D) can be precise to 7 digits after the decimal point, M represents the total width, and D represents the decimal place.

Double (M, D) can be precise to 15 digits after the decimal point

Character Type and text

Char (M) Fixed Length characters, M represents the length

Varchar (M) Variable Length characters, M represents the length

 

Char data access speed is faster than varchar

Text Type

Tinytext 0-255 min text

Text 0-medium text

Long text 4.2 billion

Date and Time

Date such as: "YYYY-MM-DD"

For example, "HH: MM: SS"

Datetime For example: "YYYY-MM-DD HH: MM: SS"

 

Show data tables

Show table from table_name;

Display table structure

DESCRIBE table_name;

Delete table

Drop trable table_name;

Modify

Use phpmyadmin to modify

 

Data processing SQL

Structured Query Language

Main functions of SQL

Add, delete, modify, and query

Add: ----------- insert into table_name (Field 1, Field 2 ,....) VALUE (VALUE 1, VALUE 2 ,.....);

You can specify the ID field and assign a value to the id.

Set the character set requested by the client (Chinese garbled): set names gbk;

DELETE: ----------- delete from table_name [WHERE condition];

WHERE field> Value

If the where condition is omitted, delete all

Delete from table1 where id> 5; delete all

Delete from table1 where id> 15AndContent = 'large listed companies ';

Query: --------- select field from table_name;

 

Describe command to view fields in the database table

 

Import the saixinjituan. SQL file to the mysql database.

CreateDatabase: Select the current database. Select import from the menu bar;

SELECT Field List | * FROMtable_name [WHERE condition] [order by] [LIMIT]

Field List | * FROM ------ query the information of the specified fields. Use the wildcard * to query all fields.

If the where condition is omitted, all records are displayed.

Order by ---------- query records are sorted by that field ASC in ascending order (default) DESC in descending order

Select * from news order by ID desc;

LIMIT limits the number of records output ----- LIMIT start line number, number of records (for data paging)

 

Fuzzy search is replaced by %

 

Selectid, title, hits from 007_news where id <50 order by id;

Select * from 007_news where id <50 order by id, hits desc;

Selectid, title, hits from 007_news where id <50 order by id limit 0, 5;

Selectid, title form 007_news where keywords is null; query id or title is empty

Modify: -------- UPDATE table_name SET field 1 = new value 1, Field 2 = new value 2 [WHERE condition];

Update table1 set title = 'enter', author = 'as', addate = '123' where id = 33;

 

 

PHP connection to MySQL Server

Php connection to MySQL Server: mysql_connect (), exit (), mysql_error ()

Select database ----------- mysql_select_db ()

Set mysql returned data Character set -------- mysql_query ("set names utf8 ")

Execute the SQL statement --------- mysql_query ()

Retrieve the total number of records from the set ----------- mysql_num_rows ()

Retrieve a row of data from the result set --------- mysql_fetch_row (), mysql_fetch_array (), mysql_fetch_assoc ()

 

 

PHP supplement functions ---------- include (), require (), md5 (), urlencode (), urldecode ();

 

PHP + MySQL database programming steps ---------

1. log on to the mysql server

2. Select the current database

3. Set the request Character Set

4. Execute SQL statements

PHP function connection to mysql database -------- mysql_connect ()

Resource $ link = mysql_connect ($ db_host, $ db_user, $ db_pwd)

Resource $ link = mysql_connect ("localhost", "root", ") not recommended

If the resource connection is successful, an identifier of the resource type is returned. If the resource fails, false is returned.

$ Db_host indicates the host name or IP address of the mysql server. The local host is localhost.

$ Db_user represents the user account of the mysql server

$ Db_pwd indicates the user password of the mysql server.

 

Exit () Outputs a message and terminates the program running.

Void exit ([string $ status]);

Exit ("program error ")

Mysql_error () is mainly used for testing and cannot be used again once launched.

Output the text information of the last mysql operation Error

Syntax: mysql_error ([resource $ link])

$ Link indicates the current activity link.

@ Shield system error messages

Select database mysql_select_db ()

Select the database to be frustrated ------ return a Boolean Value

 

Bool mysql_select_db (string $ database_name [, resource $ link_indentifier])

 

$ Database Name of the database you want to operate on

[$ Link] (optional) indicates the current active link.

 

Returned value: Success ------ true failed ----- false

 

Set the data Character Set returned by MySQL

Mysql_query ("set names utf8 ")

Execute SQL statements

Mysql_query () --------- execute various SQL statements

Syntax:

Resource $ result = mysql_query ($ SQL [, $ link]);

$ SQL various SQL statements

Add $ SQL = "insertinto table_name (title, id) values ('title', 'id ')";

Delete $ SQL = "delete from table_name [where id = 5]";

Change $ SQL = "update table_name set title = 'new title' [where id = 5]";

Query $ SQL = "select * from table_name ";

 

$ Link: the link of the current activity. If the link is omitted, the above open link prevails.

 

Returned value: When an SQL statement is executed, the successful return result set is the data type of a resource. If the SQL statement fails, FALSE is returned.

 

Read data from the result set

Retrieve a row ---- mysql_fetch_row () read a row of data each time.

Read a row of data from the result set andEnumerated ArrayReturn

Returns an array mysql_fetch_row (resource $ result)

$ Result indicates that an enumerated array is returned for the returned result set, that is, this row exists in the array,

$ Row = mysql_fetch_row ($ result, $ link );