Getting Started with MySQL and PHP

Log in to MySQL mysql-hlocalhost-uroot-proot exit MySQL exit

You must add a semicolon after each statement:--------------------------------------------

Display Database Show DATABASES; see how many databases are on a MySQL server

Create a database CREATE DATABASE db_name [IF not exitsts][charset UTF8];

Separate each command with a space

Db_name Custom database name alphanumeric underline

[If not exitsts] if the created database already exists, no error is made

[CHARSET UTF8] Specifies the character set of the current database, which defaults to ' Latin ',

Remember: The middle of the UTF8 does not add horizontal lines

Use of the show command;

Show create database db_name; View the statement procedure for creating the databases

Deleting a database DROP DATABASE db_name [ifexitsts];

[If exitsts] will not error if there is a delete

Modify the character set of the MySQL database: ALERT database db_name DEFAULT CHARACTERSET character set;

Data table Operations:

Select Current database------>USEdb_name; Display Data sheet--------->show TABLES; Create a data table---------->createtable table_name (the column 1 Type column property, the column 2 Type column property, ...). ）；

Example: CREATE TABLE table1 (id int NOT NULL auto_increment primarykey,title varchar (+) not null,author varchar (a) Not null,s Ource varchar (+) NOT NULL,

Hits int (5) NOT null default 0,

Is_ppt tinyint (1) NOT null default0,content text null,addate int (+) not NULL);

Auto_increment----Auto-grow primarykey----PRIMARY key

Attribute of ID field: required----NOT NULL Auto_increment PRIMARY key

Type of column (MySQL data type): integer, float, character, text, date

Column Properties : Empty, Notnull | | Null

Autogrow: Auto_increment can only be used for IDs, and one table can have only one autogrow attribute

Defaults: Default value

Primary key: (primary index) only one, PRIMARYKEY is generally assigned to the ID field, a table can only have one

Integer: Tinyint---One byte (0-255) SMALIMT----Two bytes (0-65535) int----Four bytes (2.1 billion) bigint----8 bytes ()

Float type: float (m,d) can be accurate to 7 digits after the decimal point, M for the total width, D for the decimal place

Double (m,d) can be accurate to 15 digits after the decimal point

Character type and text

Char (m) fixed-length character, m stands for length

Varchar (m) variable-length character, m stands for length

Char accesses data faster than varchar

Text type

Tinytext 0-255 min. text type

Text 16.77 million medium text

Longtext 4.2 billion

Date Time

Date: "Yyyy-mm-dd"

Time such as: "HH:MM:SS"

DateTime as: "Yyyy-mm-dd HH:MM:SS"

Show data table

SHOW TABLE from table_name;

Show the structure of a table

DESCRIBE table_name;

Delete a table

DROP trable table_name;

Modify

Use phpMyAdmin to modify

Data processing SQL

Structured Query Language

Key features of SQL

Change and delete

Added:-----------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 of the client request (Chinese garbled solution): Set names GBK;

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

WHERE Fields > values

If you omit the where condition, you have to delete

Delete from table1 where id>5; delete all IDs greater than 5

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

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

Describe command to view fields in the database table

Import saixinjituan.sql files to MySQL database

First set up a database : Then select the current database, select the menu bar import;

SELECT Field List |* fromtable_name [where Condition][order by][limit]

Field List |*from------Query specify information for several fields, use wildcard * to query all fields

Where condition if omitted will show all records

Order BY----------the records of the query are sorted by that field ASC Ascending (default) desc Descending

Select* from News order by ID Desc;

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

Use% instead of fuzzy query

Selectid,title,hits from 007_news where id<50 the 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

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

Update table1 set title= ' entertainment ', author= ' as ', addate= ' 14399999 ' 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 return data character set--------mysql_query ("Set names UTF8")

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

Total number of records taken from the aggregate-----------mysql_num_rows ()

Fetch a row of data from the result set---------mysql_fetch_row (), mysql_fetch_array (), MYSQL_FETCH_ASSOC ()

PHP supplemental functions----------include (), require (), MD5 (), UrlEncode (), UrlDecode ();

Php+mysql Database Programming Steps---------

First, log in to the MySQL server

Second, select the current database

Iii. setting the request character set

IV. Executing SQL statements

PHP function connection MySQL database--------mysql_connect ()

Resource $link =mysql_connect ($db _host, $db _user, $db _pwd)

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

Resource The connection succeeds, returns the identifier of a resource type, and returns False if it fails

$db _host represents the host name or IP address of the MySQL server, local localhost

$db _user user account on behalf of MySQL server

$db _pwd user password on behalf of MySQL server

Exit () outputs a message and terminates the program run

void exit ([string $status]);

Exit ("The program has gone wrong")

Mysql_error () is mainly used for testing and can no longer be used once on-line

Output last action MySQL error text message

Syntax: Mysql_error ([Resource $link])

$link represents the current active link

@ Shielding System error message

Select Database mysql_select_db ()

Select a database to frustrate------return a Boolean value

BOOL mysql_select_db (String $database _name [, Resource $link _indentifier])

$database the name of the database you want to manipulate

[$link] optional, representing the current active link

Return value: Success------true failed-----false

Set the data character set returned by MySQL

mysql_query ("Set names UTF8")

Execute SQL statement

mysql_query ()---------execute various SQL statements

Grammar:

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

$SQL various SQL statements

Increment $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]";

Check $sql = "SELECT * FROM table_name";

$link the link to the current activity, if omitted, whichever is the above open link

Return value: The successful return result set is the data type of a resource when the SQL statement is executed, and the failure returns false

Reading data from the result set

Take out a row----mysql_fetch_row () read one row of data at a time

Reads a row of data from the result set and returns it as an enumerated array

Returns an array of arrays mysql_fetch_row (Resource$result)

$result returns an enumerated array on behalf of the returned result set, which means that the row is in an array,

$row = Mysql_fetch_row ($result, $link);

Getting Started with MySQL and PHP