High-level article will involve the use of databases and cookies and session sessions, improve PHP development efficiency and operational efficiency
PHP programmers need to master the MySQL operation
Design a table for a project
Using SQL statements
Directory structure of MySQL
A library file is stored in the data directory
The bin directory is stocked with MySQL admin commands
The *.ini file records the configuration of MySQL
Connect MySQL DB:
Mysql-h SQL Address-u user name-p password, such as Mysql-h localhost-u root-p123456
Security method: First Enter "Mysql-h SQL address-u user name-P", enter, and then input the password
Data definition language (DDL)
Definition: Used to create various objects in a database-----tables, views, indexes, synonyms, clusters, etc.
SQL statements
Create a database
CREATE DATABASE [IF NO EXISTS] DatabaseName
Create a table
CREATE TABLE [IF not EXISTS] TableName (colname1 type [property] [Index],colname2 Type [property] [index],...) [Tabletype] [Tablecharset];
Modify Table
ALTER TABLE operation
Data type
Numeric type
UNSIGNED: Specified as unsigned storage
Integral type
TINYINT 1 Byte (-128,127) (0,255) Small integer value
SMALLINT 2 Byte (-32 768,32 767) (0,65 535) Large integer value
Mediumint 3 Byte (-8 388 608,8 388 607) (0,16 777 215) Large integer value
int or integer 4 Byte (-2 147 483 648,2 147 483 647) (0,4 294 967 295) Large integer value
BIGINT 8 Byte (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) Maximum integer value
Floating point Type
FLOAT 4 bytes ( -3.402 823 466 e+38,1.175 494 351 E-38), 0, (1.175 494 351 e-38,3.402 823 466 351 e+38) 0, (1.175 494 351 e-38,3. 402 823 466 e+38)
DOUBLE 8 bytes (1.797 693 134 862 315 7 e+308,2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 8 315 7 e+308) 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308)
Character type
CHAR 0-255byte Fixed-length string,
VARCHAR 0-255byte variable long string, must specify length
Tinyblob 0-255byte A binary string of no more than 255 characters
Tinytext 0-255byte Short Text string
BLOB 0-65 Long text data in 535Byte binary form
Text 0-65 535Byte Long text data
Mediumblob 0-16 777 Medium-length text data in 215Byte binary form
Mediumtext 0-16 777 215Byte Medium Length text data
Logngblob 0-4 294 967 295Byte Binary form of large text data
Longtext 0-4 294 967 295Byte Maximum text data
Char processing speed is faster, varchar has a variable size
Binary save is primarily used to save non-text files
Enum, enum type, can store up to 65,535 values, one field can only have one value
Set, collection type, can store up to 64 values, one value segment can hold multiple values
Date type
Date 3Byte 1000-01-01/9999-12-31 Yyyy-mm-dd Day value
Time 3Byte ' -838:59:59 '/' 838:59:59 ' HH:MM:SS value or duration
Year 1Byte 1901/2155 YYYY years value
DateTime 8Byte 1000-01-01 00:00:00/9999-12-31 23:59:59 yyyy-mm-dd HH:MM:SS Blend date and time values
TIMESTAMP 8Byte 1970-01-01 00:00:00/2037 YYYYMMDD hhmmss Mixed date and time value, timestamp
※ Any data type is stored as a string and can be automatically converted to type
※ Save time as PHP timestamp for easy operation
Data Field Properties
Unsigned: Sets the field to be an unsigned value, only a numeric type
Zerofill: When the value of the record that sets the field does not reach the specified number of digits, it is filled with "0" and can only be numeric
Auto_increment: Sets the value of this field to automatically grow, or to set custom values, you need to set the index or primary key, only the numeric type
NULL and NOT NULL: Sets whether the field is allowed to be empty, recommended to be non-null, and with default use
Default: Sets the defaults for this field, and if not, use the default value
Index
Advantages:
Improve query speed
Disadvantages:
High cost of creation and maintenance
Occupy Resources
Primary key index (primary key): Index values must be unique, each table has only one
Unique index (unique): The index value must be unique, but a table can have more than one
Regular index: The most basic index, not too many restrictions
Full-text index (FILLTEXT): can only be used on MyISAM, the larger the table, the better the effect, but slower
Create and use to see a list of MySQL index types to make MySQL run efficiently
Data table type and storage location
MySQL can choose the optimal storage engine for different storage engine requirements
The data table type is the storage engine
Specifying a table type using the type or engine keyword
Common table Types
MyISAM
Emphasis on fast read operations
Not supported for some features (transactional)
InnoDB
Support for some features not supported by MyISAM
Full-text indexing is not supported
occupy a larger space
| Function |
MyISAM |
InnoDB |
| Transaction processing |
Not supported |
Support |
| Data row Locking |
Not supported |
Support |
| FOREIGN KEY constraints |
Not supported |
Support |
| Table Space occupancy |
Relatively small |
Larger |
| Full-Text Indexing |
Support |
Not supported |
MySQL Default character set
Recommended UTF8
CharSet: A way to define the MySQL storage string
Specifying a character set using the character Set keyword
Proofing rules: Defines how strings are compared to rules
Use collate to specify proofing rules
Data manipulation Language (DML)
There are three main types:
1) Inserting: Insert
Insert INTO tablename[(field list)] VALUES (value list 1) [, (Value List 2) ...]
After the table name, if there is a field list, the value list corresponds to the field list one by one, and if there is no field list, the Value list corresponds to field one by one in the table
2) Updates: Update
Update tablename SET field name = ' value ' [condition]
3) Remove: Delete
Delete from TableName [condition]
You can use operators, including arithmetic operators, logical operators, comparison operators, bitwise operators
Data Query Language (DQL)
The basic structure is made up of select[all| DISTINCT] clause, from clause, where
clause consists of a query block:
SELECT <字段列表>
From <表或视图名>
[WHERE <查询条件> /group By/order by]
Distinct indicates that duplicate records are not displayed
Use the AS keyword to alias a field name for a field name that may cause ambiguity
Data Control Language (DCL)
Definition: Used to grant or reclaim access to a database of some kind of privilege, and control the time and effect of database manipulation transactions, monitoring the database and so on.
MySQL built-in functions
Location: SELECT statement, and clause where order by has, update DELETE statement and clause
A function can use a field name as a variable, and the value of the variable is all the values of that column.
Common
String functions
Concat: Concatenate incoming arguments into a string
Insert (Str,x,y,insert): Starts at the x position of STR, replacing the Y-length string with the insert
Lower (str), Upper (str): Converts a string to uppercase, lowercase
Left (str,x) right (STR,X) returns the X-character of Str to the Ieft, and x to NULL to return NULL
Lpad (Str,n,pad) rpad (Str,n,pad) fills the string str from the leftmost (right) with a pad until the total length n
Trim (str), LTrim (str), RTrim (str) Remove both sides, left, right space
Replace (STR,A,B) replaces all string A with string B in string str
strcmp (S1,S2): If S1 is smaller than S2, return-1, if S1 is larger than S2 return 1, if equal returns 0 (ASCII code is compared)
SUBSTRING (str,x,y) returns a substring of length y from position x in the string str
numeric functions
ABS (x): Return absolute value
Ceil (x): Returns the smallest integer greater than X
Floor (x): Returns the largest integer less than x
MoD (x, y): Returns the modulo of X and Y
RAND (): Returns a random number between 0-1
Round (x, y): Returns the rounding result of the Y-decimal fraction of the parameter X
Truncate (x, y): Returns the result of a number x truncated to a Y decimal place
Date function
Curdate (): Returns the current month day
Curtime (): Returns the current time and seconds
Now (): Returns the current date
Unix_timestamp (Time): Returns the UNIX timestamp
From_unixtime (): Converts a Unix timestamp to a date
Week (): Returns the time stamp of the week
Year (): Returns the years of the timestamp
Hour (): Returns the hour of the timestamp
Minute (): Returns the minute of the timestamp
Month (): Returns the months of the timestamp
Date_format (Time, "%y-%m-%d%h:%i:%s"): Formatted return times
Process Control functions
if (value,t,f): If the value is true, return T if the value is false, return F
Ifnull (value1,value2): Returns value2 if value1 is empty, returns value1 if value1 is not empty
Case
When value1 then value2
When Value3 then Value4
......
Else fault END
When value1 is true, returns value2, when Value3 bit true, returns VALUE4, and so on, otherwise returns fault
Other usage: MySQL statement case
Other functions
Database (): Returns the names of the databases
Version (): Return to MySQL version
User (): Back to MySQL
Inet_aton (IP): convert IP to network byte order
Inet_nton (): Convert network byte order to IP
Password (): MySQL user password encryption
MD5 (STR): Encrypt a string
PHP Operations Database
Connecting to a database
Mysql_connect (IP,USER,PSW): IP for database address, user name, PSW for user password, connection successful, return database resource, Connection failed, return false
Select Library
mysql_select_db ($dbname, [$res]): $dbname is the name of the library, $res is the returned resource for the connection database, and the default is the most recently created database resource if the parameter is not added
SQL statement Input
mysql_query (): Executes the SQL statement, if the statement has a return result set, the function execution successfully returns the result set, if the statement does not return the result set, the function execution succeeds returns true
Resolve Errors
Mysql_errno (): Return error number
Mysql_error (): Return error message
Close Database Resources
Mysql_close (): Close the database resource without using parameters, turn off open resources by default (recommended)
Function
MYSQL_INSERT_ID (): Returns the auto-growing ID, or False if no auto_increment is set
Mysql_affected_rows (): Gets the number of rows affected
Extracting data from the result set
Mysql_fetch_row ($result): Gets a piece of data from the result set, returning an indexed array
MYSQL_FETCH_ASSOC ($result): Gets a piece of data from the result set, returns the associative array
Mysql_fetch_array ($result): Gets a piece of data from the result set, returning an indexed array and an associative array
Mysql_fetch_object ($result): Gets a piece of data from the result set, returns the object
Mysql_data_seek ($result, $row): Move the pointer to the specified position
Get a field from a result set
Mysql_num_rows ($result): Gets the number of fields in the result set
Mysql_num_fields ($result): Gets the number of columns in the result set
Mysql_field_name ($result): Gets the field name of the result set
MYSQLI Operating Database
The newly added functionality after PHP5 is object-oriented, so mysqli is added as an object.
MYSQLI Advantages
Represents an improvement
Increased functionality
greatly increased efficiency
More stable
Three classes provided by the mysqli extension
Mysqli: Classes related to connections
Construction method
Mysqli ([$host [, $username [, $PASSD [, $dbname [, $port [, $socket]] []]])
The connection successfully returned the object, and the failure returned false
View connection Failure Information
Connect_errno (): Return connection Error number
Connect_error (): Return connection error message
SQL statement Input
Query (SQL): Executes the SQL statement, if the statement has a return result set, the function execution successfully returns the result set object Mysqli_result, if the statement does not return the result set, the function execution succeeds returns true
Method
Affected-rows (): Returns the number of rows affected
errno (): Return error number
Error (): Returns the wrong message
INSERT_ID (): Returns the auto-growing ID
Close Resource
Close (): Close connection
Mysqli_result: Expression of the result set returned by a query to a database
Property:
$num _rows: Number of records in the result set
$field _count: Number of fields in the result set
$current _field: Get the position of the forefront
Method:
Processing Records
Fetch_row (): Consistent with mysql_fetch_row ()
FETCH_ASSOC (): Consistent with MYSQL_FETCH_ASSOC ()
Fetch_array (): Consistent with mysql_fetch_array ()
Fetch_object (): Consistent with mysql_fetch_object ()
Data_seek (): Consistent with Mysql_data_seek ()
Free (): Release result set
Working with Fields
Fetch_field (): Takes out column information and returns it as an object
Fetch_fields (): Takes out all column information and returns it as an object
Field_seek (): Move field pointer
Execute multiple SQL statements
Multi_query (SQL1[;SQL2]): Executes multiple SQL statements, using ";" between statements Separated, if there are multiple result sets, it will be returned
Next_result (): Returns the next result set of Multi_query ()
More_results (): Check if the next result set is included
MYSQLI_STMT: Preprocessing class
Advantages:
Mysqli and Mysqli_result can complete the function, mysqil_stmt can complete
The efficiency is high, executes many identical SQL statements, only the data is different, does not have to repeat the statement, passes the data directly can
Prevent SQL injection because the data is used only as a value class and not as an executable statement
Creating objects
After you create the Mysqli object, use the object's Stmt_init () method to initialize the Mysqli_stmt object
Prepare and Send statements
The value of the parameter in the statement is to use the placeholder "?" Replace
Use the Prepare ($sql) method in mysqli_stmt to send statements to the server for preparation
Instead of creating a Mysqli_stmt object, prepare the SQL statement directly using prepare ($SQL) in mysqli and return the Mysqli_stmt object
Assign a value to a placeholder (binding parameter)
Using Bind_param ($type, $var 1[, $var 2 ...]) Binding parameters
$type can be I, D, S, b, representing Integer, double, string, and binary resources, respectively
The number of types in the $type is the same as the placeholder, and the number of $var is the same as the number of placeholders
Assigning values to variable $var
Execute SQL statement
No result set returned
Executes the inserted parameter using the Execute () method, returning a Boolean type
There is a result set returned
Using Bind_result ($var 1[, $var 2 ...]) Binding result Set
Execute the statement using FETCH (), fetch one result at a time, and pass it to the variable in Bind_result ()
Executes the statement using Store_result (), fetches all results once, returns the result set, and fetches each record with fetch ()
Result_matedate () Returns the result set for obtaining field information
Use Result_free () to release the result set
Close Resource
Use the Close () method to close
Function
Mysqli and Mysqli_result support functions, MYSQLI_STMT Basic Support
Transaction processing
Create a table
Table Type MyISAM does not support transactional functionality and requires the creation of INNODB types of tables
Turn off auto-commit
Autocommit (): When the parameter is 0 or FALSE, auto-commit is turned off
Commit a transaction
Commit (): Commit TRANSACTION (multiple SQL statements after execution)
Rolling back a transaction
Rollback (): ROLLBACK TRANSACTION (multiple executed SQL statements)
Other methods
Set_charset ($string): Setting the Remove character set
Pdo
Advantages:
No code changes when replacing a database
Disadvantages:
Less efficient than MySQL and mysqli
Three classes
PDO: Represents a connection between PHP and the database service
Create a PDO object
DPO ($dsn, $username, $PASSD [, $array]): When $DSN connected to MySQL database, set to ' Mysql:host=ip:port;dbname= $string ', $array for tuning parameters
DSN (data source name) data source: Includes host location, library name, and drive required for different databases
Use GetAttribute ($attribute) to view properties, set properties using SetAttribute ($attribute, $value)
Execute SQL statement
Query ($string): Executes the statement returned by the result set, returning the Preprocessed object pdostatement
EXEC ($string): Executes a statement that affects the table, returns the number of rows affected
Design Error Reporting
Using setattribute () to set the error reporting mode
Errmode_silent: Error not displayed, developer self-check error
ErrorCode: Return error number
ErrorInfo: Returns an array of error messages
Errmode_warning: An error occurred, displaying a e_warning message
Errmode_exception: Error occurred, throwing Pdoexception exception
Transaction processing
Use SetAttribute (), set turn on transaction processing, turn off autocommit
Commit an executed SQL statement using commit ()
To roll back an executed SQL statement using rollback ()
Pdostatement: Represents a preprocessing statement and represents a related result set after the statement is executed
Role
Prepare a statement
Working with result sets
Prepare and Send statements
The parameter values in the statement can use the placeholder "?"
Placeholder ": Placeholder name" instead
Use PDO::p repare ($sql) method to send statements to server preparation, return Pdostatement objects, store result sets
Assign a value to a placeholder (binding parameter)
Binding parameters using Bind_param ($key, $value)
“?” Placeholder
$key set to the index number,
$value set to transfer values
Name placeholder
$key set to the key name
$value set to transfer values
SQL statement Execution
Statement that executes a bound parameter using the Execute () method
Use Execute ($array) to add parameters $array arrays to avoid binding parameters
Record acquisition
Use Fetch () to get each record in the result set, return an index and associate a mixed array
parameter is PDO::FETCH_ASSOC, returns the associative array
parameter is Pdo::fetch_num, returns an indexed array
parameter is Pdo::fetch_both, return index associative mixed array
Fetchall () gets each record of the result set, returning a two-dimensional array
Use Setfatchmode () to set the Get mode to avoid setting the mode for each fetch
Field gets
ColumnCount () Gets the number of fields
Getcolumnmeta () returns the metadata for a column in the result set
Pdoexception: Represents an error generated by PDO. In your own code should not throw a pdoexception exception
Use try catch to catch various exceptions, including connection exceptions, SQL statement exceptions, and so on
Mamcache/memcached
A high-performance distributed memory object caching system. Maintain in-memory data by maintaining a huge hash table in memory
Working principle
When PHP queries data for the first time, it stores the data in Mamcache, and the next time you query, it accesses Mamcache.
Installation
Install under Linux
Based on the Libevent event, you must first install the Libevent library
Install under Windows
Default Port 11211
Memcache command
Command Description Example
| Get |
Reads a value |
Get MyKey |
| Set |
Set a key unconditionally |
Set MyKey 0 60 5 |
| Add |
ADD a new key |
Add Newkey 0 60 5 |
| Replace |
Overwrite existing Key |
Replace key 0 60 5 |
| Append |
Append data to existing key |
Append key 0 60 15 |
| Prepend |
Prepend data to existing key |
Prepend Key 0 60 15 |
| incr |
Increments numerical key value by given number |
INCR MyKey 2 |
| Decr |
Decrements numerical key value by given number |
DECR MyKey 5 |
| Delete |
Deletes an existing key |
Delete MyKey |
| Flush_all |
Invalidate specific items immediately |
Flush_all |
| Invalidate all items in n seconds |
Flush_all 900 |
| Stats |
Prints General Statistics |
Stats |
| Prints Memory Statistics |
Stats Slabs |
| Prints Memory Statistics |
Stats malloc |
| Print Higher level allocation statistics |
Stats items |
|
Stats Detail |
|
Stats sizes |
| Resets statistics |
Stats Reset |
| Version |
Prints Server version. |
Version |
| Verbosity |
Increases log level |
Verbosity |
| Quit |
Terminate Telnet session |
Quit |
Using Memcache in PHP
Class: Memcache
Connection: Memcache::connect ($host, $port)
1
Connect ("localhost", 11211) or die ("could not Connect");
Other methods
Add: Adding Data
Set/replace: Modifying Data
Get: Get Data
Delete: Deleting data
......
When to use Memcache
Read the data in the database to make it easy to use next time
Use in Session control
Skills
Use SQL statements as key
Modify the SQL statement with MD5 () to make the SQL statement shorter and easier to save
Session Control: Connection-oriented, reliable connection mode, through session control, to determine the user's logon behavior
Cookie Technology
Server to the client of a file, through the client of this file, save the user information, the server according to the file, distinguish users
Set cookies
Setcookie ($key, $value, $time): Header info, cannot have any output
Get cookies
Use global array $_cookie[] to get COOKIE content
Delete Cookieti
Set $value to empty or not set with Setcookie, $time set to 0 or not set
Session Technology
Saving user data in the server generates a sessionid that can be passed by using cookies and URLs
Session Configuration
Configuring the server-side php.ini
Open session
Session_Start (): Allow PHP's core program to pre-load the session-related built-in environment variables into memory
Open a session
Cookie-based session, using this function cannot have any output
Return a session that has been opened
Set up and get session
Use $_session[] to set and get SESSION
SESSION_ID () Gets and sets the session ID
Delete session
$_session=array (); Set SESSION to an empty array
Delete a session in a cookie
Session_destory (): Destroy session
Based on URL delivery sessionid, setting the URL parameter to Session_name,session_start () will automatically look for the parameter
Constant SID, which represents Session_name and session_id when the user closes the cookie, which is null when the user opens the cookie
Setting session.use_trans_sid=1 in PHP.ini causes the page to jump (hyperlinks, headers, forms) After the SID is automatically added
Session Advanced Technology
PHP.ini, Session settings
Session_name: Set the presence of cookies and session_name in SIDS
SESSION.USE_TRANS_SID: Set SID to be enabled, automatically add SID when enabled
Session.save_path: Set the session file save location, if not set, the session file is not generated
Session.gc_maxlifetime: Set session file valid time, more than that time session is not refreshed, session file will be invalid
Session.gc_probability and Session.gc_divisor are used together to define the probability of garbage collection in session, the algorithm is Session.gc_probability/session.gc_divisor
Session.use_cookie: Set session to write to cookie
Session.cookie_path: Sets which files of the session are written to the cookie
Session.cookie_lifetime: Setting the session lifecycle
Session.save_handler: Set the session write mode and location, when the value is user, you can use the Session_set_save_handler () function
Session_set_save_handler (open (), close (), read (), write (), Destroy (), GC ()): Customizable session file storage path and storage mode, etc.
Use this function to define each method, using the session as usual
Open (): Called when executing session_start ()
Close (): Called when executing session_write_close ()
Read (): Called when Open () is called
Write (): Called when script ends and Session_write_close () is executed
Destroy (): Called when session is destroyed using Session_destroy () or session_regenerate_id ()
GC (): Determined by session.gc_probability and Session.gc_divisor, any time the army may be called
Specific usage
Write session to Database
Write session to Memcache
At this point, PHP Basic learning is completed, need to learn more, can improve!
