CHAR 0-255Byte fixed-length string, VARCHAR 0-255Byte variable-length string, the length must be specified TINYBLOB 0-255Byte binary string not exceeding 255 characters TINYTEXT 0-255Byte Short text string BLOB 0-65 535Byte Long text data in binary form TEXT 0-65 535Byte Long text data MEDIUMBLOB 0-16 777 215Byte Medium-length text data in binary form MEDIUMTEXT 0 -16 777 215Byte Medium length text data LOGNGBLOB 0-4 294 967 295Byte Very large text data in binary form LONGTEXT 0-4 294 967 295Byte Very large text data
CHAR is processed faster, VARCHAR has variable size
Binary save is mainly used to save non-text files
ENUM, an enumeration type, can store up to 65535 values, and a field can only store one value
SET, a collection type, can store up to 64 values, and one value segment can store multiple values
Date type
DATE 3Byte 1000-01-01/9999-12-31 YYYY-MM-DD date value TIME 3Byte '-838:59:59'/'838:59:59' HH:MM:SS time Value or duration YEAR 1Byte 1901/2155 YYYY Year value DATETIME 8Byte 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM: SS mixed date and time value TIMESTAMP 8Byte 1970-01-01 00:00:00/sometime in 2037 YYYYMMDD HHMMSS mixed date and time value, timestamp
※Any data type stored in the form of a string can be automatically converted
※Save the time as a php timestamp for easy calculation
Data field attributes
unsigned: Set this field to an unsigned value, which can only be numeric type
zerofill: When the value of the record in this field does not reach the specified number of digits, fill it with "0", which can only be numeric
auto_increment: Set the value of this field to automatically increase. You can also set a custom value. You need to set the index or primary key at the same time. It can only be numeric
null and not null: Set whether the field is allowed to be empty. It is recommended to set it to non-empty and use it with default
default: Set the default value of this field. If not entered, use the default value
Index
Advantages:
Improve query speed
Disadvantages:
The creation and maintenance costs are relatively high
Occupying resources
Primary key: The index value must be unique, there is only one for each table
Unique index (unique): The index value must be unique, but a table can have multiple
Regular index: the most basic index without too many restrictions
Full text index (filltext): can only be used on MyISAM. The larger the table, the better the effect, but the speed is slower
To create and use, you can view the 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 needs
The data table type is the storage engine
Use the type or engine keyword to specify the table type
Commonly used table types
MyISAM
Emphasis on fast read operations
Some functions are not supported (transactions)
InnoDB
Supports some features that MyISAM does not support
Full-text indexing is not supported
Taking up a lot of space
功能
MyISAM
InnoDB
事务处理
不支持
支持
数据行锁定
不支持
支持
外键约束
不支持
支持
表空间占用
相对较小
较大
全文索引
支持
不支持
MySQL default character set
Recommended utf8
Character set: used to define the way MySQL stores strings
Use the character set keyword to specify the character set
Collation rules: The rules define how to compare strings
Use collate to specify collation rules
Data Manipulation Language (DML)
There are three main forms:
1) Insert: 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-to-one. If there is no field list, the value list corresponds to the fields in the table one-to-one
2) Update: UPDATE
update tablename set field name='value' [Condition]
3) Delete: DELETE
delete from tablename [condition]
You can use operators, including arithmetic operators, logical operators, comparison operators, and bitwise operators
Data Query Language (DQL)
The basic structure is composed of SELECT[ALL|DISTINCT] clause, FROM clause, WHERE
Query block composed of
clauses:
SELECT
FROM
[WHERE/GROUP BY/ORDER BY]
DISTINCT means not to display duplicate records
Use the as keyword to create aliases for field names that may cause ambiguity
Data Control Language (DCL)
Definition: Used to grant or revoke certain privileges to access the database, control the time and effect of database manipulation transactions, monitor the database, etc.
MySQL built-in functions
Position: select statement, and clause where order by having, update delete statement and clause
You can use the field name as a variable in the function, and the value of the variable is all the values corresponding to the column
Commonly used
String functions
concat: Concatenate the incoming parameters into a string
insert(str,x,y,insert): Starting from the x position of str, replace the y-length string with insert
lower(str),upper(str): Convert string to uppercase, lowercase
left(str,x) right(str,x) returns x characters to the left (right) of str, if x is null, returns null
lpad(str,n,pad) rpad(str,n,pad) Use pad to pad the string str from the leftmost (right) until the total length n
trim(str), ltrim(str), rtrim(str) remove spaces on both sides, left and right
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 they are equal, return 0 (the comparison is ASCII code)
substring(str,x,y) returns the substring of length y starting from position x in string str
Numerical function
abs(x): Returns the 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 modulus of x and y
rand(): Returns a random number between 0-1
round(x,y): Returns the rounding result of parameter x to y decimal places
truncate(x,y): Returns the result of number x truncated to y decimal places
Date function
curdate(): Returns the current year, month and day
curtime(): Returns the current hour, minute and second
now(): Returns the current date
unix_timestamp(time): Returns unix timestamp
from_unixtime(): Convert Unix timestamp to date
week(): Returns the week of the timestamp
year(): Returns the year of the timestamp
hour(): Returns the hour of the timestamp
minute(): Returns the minute of the timestamp
month(): Returns the month of the timestamp
date_format(time,"%Y-%m-%d %H:%i:%s"): formatted return time
Process control function
if(value,t,f): If value is true, return t, if value is false, return f
ifnull(value1,value2): If value1 is empty, return value2, if value1 is not empty, return value1
case when value1 then value2 when value3 then value4 ...... else fault END
When value1 is true, return value2, when value3 is true, return value4, and so on, otherwise return fault
Other usage: mysql statement case when
Other functions
database(): Returns the database name
version(): Returns the MySQL version
user(): Returns the MySQL user
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 string
PHP operation database
Connect to database
mysql_connect(IP,user,psw): IP is the database address, user is the username, psw is the user password. If the connection is successful, the database resource is returned. If the connection fails, false is returned
Select library
mysql_select_db($dbname,[$res]): $dbname is the library name; $res is the resource returned by connecting to the database. If this parameter is not added, the default is the recently created database resource
SQL statement input
mysql_query(): Execute the SQL statement. If the statement returns a result set, the function execution returns the result set successfully. If the statement does not return the result set, the function execution returns true
Resolve errors
mysql_errno(): Return error number
mysql_error(): Return error message
Close database resources
mysql_close(): Closes database resources, does not use parameters, closes open resources by default (recommended)
Function
mysql_insert_id(): Returns the automatically growing id. If AUTO_INCREMENT is not set, it returns false
mysql_affected_rows(): Get the number of affected rows
Retrieve data from the result set
mysql_fetch_row($result): Get a piece of data from the result set and return the index array
mysql_fetch_assoc($result): Get a piece of data from the result set and return an associative array
mysql_fetch_array($result): Get a piece of data from the result set and return the index array and associative array
mysql_fetch_object($result): Get a piece of data from the result set and return the object
mysql_data_seek($result,$row): Move the pointer to the specified position
Get fields from result set
mysql_num_rows($result): Get the number of fields in the result set
mysql_num_fields($result): Get the number of columns in the result set
mysql_field_name($result): Get the field name of the result set
mysqli operation database
The newly added functions after PHP5 are all object-oriented, so mysqli is added in the form of objects
The object is returned if the connection is successful, false if failed
View connection failure information
connect_errno(): Returns the connection error number
connect_error(): Returns connection error information
SQL statement input
query(sql): Execute a SQL statement. If the statement returns a result set, the function executes successfully and returns the result set object mysqli_result. If the statement does not return a result set, the function executes successfully and returns true
Method
affected-rows(): Returns the number of affected rows
errno(): Returns the error number
error(): returns error message
insert_id(): Returns the automatically growing id
Close resources
close(): Close the connection
mysqli_result: expresses the result set returned by a query to the database
Attributes:
$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 current column
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 the result set
Processing fields
fetch_field(): Fetch column information and return it as an object
fetch_fields(): Fetch all column information and return it as an object
field_seek(): Move field pointer
Execute multiple SQL statements
multi_query(sql1[;sql2]): Multiple sql statements can be executed. The statements are separated by ";". If there are multiple result sets, they will all be returned
next_result(): Returns the next result set of multi_query()
more_results(): Check whether it contains the next result set
mysqli_stmt: preprocessing class
Advantages:
Mysqil_stmt can complete the functions that mysqli and mysqli_result can complete
It is relatively efficient. It can execute multiple identical SQL statements. If only the data is different, there is no need to repeat the statement and the data can be transmitted directly
Prevent sql injection, because the incoming and outgoing data will only be used as value classes and not as executable statements
Create object
After creating the mysqli object, use the stmt_init() method of the object to initialize the mysqli_stmt object
Prepare and send statements
The parameter value in the statement should be replaced by the placeholder "?"
Use the prepare($sql) method in mysqli_stmt to send the statement to the server for preparation
No need to create a mysqli_stmt object, directly use prepare($sql) in mysqli to prepare the sql statement and return the mysqli_stmt object
Pass value to placeholder (bind parameter)
Use bind_param($type,$var1[,$var2...]) to bind parameters
$type can be i, d, s, b, representing integer, double, string and binary resources respectively
The number of types in $type must be the same as the number of placeholders, and the number of $var must be the same as the number of placeholders
Assign a value to variable $var
Execute sql statement
No result set returned
Use the execute() method to execute the inserted parameters and return a boolean type
A result set is returned
Use bind_result($var1[,$var2...]) to bind the result set
Use fetch() to execute the statement, get one result each time, and pass it to the variable in bind_result()
Use store_result() to execute the statement, retrieve all the results at once, return the result set, and then use fetch() to obtain each record
result_matedate() returns a result set, used to obtain field information
Use result_free() to release the result set
Close resources
Use the close() method to close
Function
mysqli and mysqli_result support functions, and mysqli_stmt basically supports them
Transaction processing
Create table
The table type is MyISAM and does not support transaction functions. You need to create an InnoDB type table
Turn off automatic submission
autocommit(): When the parameter is 0 or false, auto-commit is turned off
set_charset($string): Set and retrieve the character set
PDO
Advantages:
When changing the database, there is no need to change the code
Disadvantages:
Not as efficient as mysql and mysqli
Three categories
PDO: represents a connection between PHP and database services
Create PDO object
dpo($dsn,$username,$passd[,$array]): When $dsn connects to the mysql database, it is set to 'mysql:host=ip:port;dbname=$string', and $array is the tuning parameter
DSN (data source name) data source: including host location, library name and drivers required for different databases
You can use getattribute($attribute) to view attributes and setattribute($attribute,$value) to set attributes
Execute sql statement
query($string): Execute the statement that returns the result set and return the preprocessing object PDOStatement
exec($string): Execute statements that affect the table and return the number of affected rows
Design error reporting
Use setAttribute() to set error reporting mode
ERRMODE_SILENT: No errors are displayed, developers can check errors themselves
errorCode: Return error number
errorInfo: Returns an array of error information
ERRMODE_WARNING: An error occurred and an E_WARNING message is displayed
ERRMODE_EXCEPTION: An error occurred and PDOException was thrown
Transaction processing
Use setAttribute() to enable transaction processing and turn off automatic submission
Use commit() to submit the executed sql statement
Use rollback() to roll back the executed sql statement
PDOStatement: represents a prepared statement and represents a related result set after the statement is executed.
Function
Prepare a statement
Processing result sets
Prepare and send statements
The parameter value in the statement can use the placeholder "?"
placeholder ":placeholder name" instead of
Use the PDO::prepare($sql) method to send the statement to the server for preparation, return the PDOStatement object, and store the result set
Pass value to placeholder (bind parameter)
Use bind_param($key,$value) to bind parameters
"?" placeholder
$key is set to the index number,
$value is set to the transmitted value
Name placeholder
$key is set to the key name
$value is set to the transmitted value
SQL statement execution
Use the execute() method to execute a statement with bound parameters
Use execute($array) to add parameters to the $array array to avoid binding parameters
Record acquisition
Use fetch() to get each record in the result set and return a mixed array of index and association
The parameter is PDO::FETCH_ASSOC and returns an associative array
The parameter is PDO::FETCH_NUM, and the index array is returned
The parameter is PDO::FETCH_BOTH, and the index associative mixed array is returned
fetchAll() obtains each record in the result set and returns a two-dimensional array
Use setFatchMode() to set the acquisition mode to avoid having to set the mode every time
Field acquisition
columnCount() gets the number of fields
getColumnMeta() returns the metadata of a column in the result set
PDOException: Represents an error generated by PDO. Your own code should not throw a PDOException exception
Use try catch to catch various exceptions, including connection exceptions, sql statement exceptions, etc.
mamcache/memcached
A high-performance distributed memory object cache system. Maintain data in memory by maintaining a huge hash table in memory
How it works
When PHP queries data for the first time, it will store the data in mamcache. The next time it queries, mamcache will be accessed first.
Installation
Installation under Linux
Based on libevent events, so the libevent library must be installed first
Installation 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
PHP中使用memcache
类：memcache
连接：memcache::connect($host,$port)
<span>1</span> <?<span>php
</span><span>2</span> <span>$memcache</span> = <span>new</span><span> Memcache;
</span><span>3</span> <span>$memcache</span>->connect("localhost",11211) or <span>die</span>("could not connect");
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
