PHP Notes (Advanced PHP Chapter), PHP Notes

The Advanced Chapter will cover the use of databases and Cookie and Session sessions to improve the development and operating efficiency of PHP

MySQL operations that PHP programmers need to master

• Design table for project
• Use SQL statement
• MySQL directory structure
  • The data directory stores library files
  • MySQL management commands are stored in the bin directory
  • *.ini files record MySQL configuration

Connect to MySQL DB:

• mysql -h sql address -u username -p password, such as mysql -h localhost -u root -p123456
• Safe method: first enter "mysql -h sql address -u username -p", press Enter, and then enter the password

Data Definition Language (DDL)

• Definition: Used to create various objects in the database ----- tables, views, indexes, synonyms, clusters, etc.
• SQL statement
  • Create database

  • <span>CREATE</span> <span>DATABASE</span> <span>[</span><span>IF NO EXISTS</span><span>]</span> DatabaseName

  • Create table

<span>CREATE</span> <span>TABLE</span> <span>[</span><span>IF NOT EXISTS</span><span>]</span><span> TableName (
colname1 type </span><span>[</span><span>property</span><span>]</span> <span>[</span><span>index</span><span>]</span><span>,
colname2 type </span><span>[</span><span>property</span><span>]</span> <span>[</span><span>index</span><span>]</span><span>,
...
)[tableType] [tableCharSet];</span>

• Modify table
  • alter table operation
• Data type
  • Numerical type
    • UNSIGNED: Specified as unsigned storage
    • Integer 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) Extremely large 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 862 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-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
        事務(wù)處理	不支持	支持
        數(shù)據(jù)行鎖定	不支持	支持
        外鍵約束	不支持	支持
        表空間占用	相對較小	較大
        全文索引	支持	不支持

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
      • mysqli advantages
        • Indicates improvement
        • Function added
        • Greatly increased efficiency
        • More stable
      • Three classes provided by mysqli extension
        • mysqli: classes related to connections
          • Construction method
            • mysqli([$host [, $username [, $passd[, $dbname [,$port [, $socket ]]]]] )
            • 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
        • Submit transaction
          • commit(): Submit transaction (multiple executed sql statements)
        • Rollback transaction
          • rollback(): rollback transaction (multiple executed sql statements)
      • Other methods
        • 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");

      • 其他方法
        • add：添加數(shù)據(jù)
        • set/replace：修改數(shù)據(jù)
        • get：獲取數(shù)據(jù)
        • delete：刪除數(shù)據(jù)
        • ......
      • 何時使用memcache
        • 數(shù)據(jù)庫中讀出來的數(shù)據(jù)，方便下次使用
        • 會話控制中使用
      • 技巧
        • 用sql語句作為key
        • 用md5()修改sql語句，使sql語句變短，便于保存

      會話控制：面向連接的可靠的連接方式，通過會話控制，判斷用戶的登錄行為

      • cookie技術(shù)
        • 服務(wù)器給客戶端的一個文件，通過客戶端的這個文件，保存用戶信息，服務(wù)器根據(jù)文件，區(qū)分用戶
        • 設(shè)置cookie
          • setcookie($key,$value,$time)：頭信息，不能有任何輸出
        • 獲取cookie
          • 使用全局?jǐn)?shù)組$_COOKIE[]獲取cookie內(nèi)容
        • 刪除cookieti
          • 用setcookie設(shè)置$value為空或不設(shè)置，$time設(shè)置為0或不設(shè)置
      • session技術(shù)
        • 在服務(wù)器中保存用戶數(shù)據(jù)，會產(chǎn)生一個SessionID，可使用cookie和url傳遞該id
        • session配置
          • 配置服務(wù)器端的php.ini
        • 開啟會話
          • session_start()：讓php的核心程序?qū)⒑蛃ession有關(guān)的內(nèi)建環(huán)境變量預(yù)先載入到內(nèi)存中
            • 開啟一個會話
              • 基于cookie的session，使用該函數(shù)不能有任何輸出
            • 返回已開啟的會話
        • 設(shè)置和獲取session
          • 使用$_SESSION[]設(shè)置和獲取session
          • session_id()獲取和設(shè)置session的id
        • 刪除session
          • $_SESSION=array()；將session設(shè)置為空數(shù)組
          • 刪除cookie中的session
          • session_destory()：銷毀session
        • 基于url傳遞sessionid，設(shè)置url的參數(shù)為session_name，session_start()后，會自動尋找該參數(shù)
        • 常量SID，當(dāng)用戶關(guān)閉cookie時，該常量表示session_name和session_id；當(dāng)用戶開啟cookie時，該常量為空
        • 設(shè)置php.ini中的session.use_trans_sid=1，會使頁面跳轉(zhuǎn)（超鏈接、header、表單）后面自動添加SID
      • session高級技術(shù)
        • php.ini中，session的設(shè)置
          • session_name：設(shè)置存在cookie以及SID中的session_name
          • session.use_trans_sid：設(shè)置SID是否開啟，開啟后，可自動添加SID
          • session.save_path：設(shè)置session文件的保存位置，如果不設(shè)置，則不生成session文件
          • session.gc_maxlifetime：設(shè)置session文件有效時間，超過該時間session未刷新，session文件將失效
          • session.gc_probability和session.gc_divisor結(jié)合使用，定義session垃圾回收概率，算法為session.gc_probability/session.gc_divisor
          • session.use_cookie：設(shè)置session寫入到cookie中
          • session.cookie_path：設(shè)置哪些文件的session寫入到cookie中
          • session.cookie_lifetime：設(shè)置session的生命周期
          • session.save_handler：設(shè)置session寫入方式及位置，當(dāng)值為user時，可使用session_set_save_handler()函數(shù)
        • session_set_save_handler(open(),close(),read(),write(),destroy(),gc())：可自定義session文件的存儲路徑及存儲方式等
          • 使用該函數(shù)定義了各個方法，像往常一樣使用session
          • open()：在執(zhí)行session_start()時，被調(diào)用
          • close()：在執(zhí)行session_write_close()時，被調(diào)用
          • read()：在調(diào)用open()后，被調(diào)用
          • write()：腳本結(jié)束時和session_write_close()執(zhí)行時，被調(diào)用
          • destroy()：當(dāng)session使用session_destroy()或者session_regenerate_id()被銷毀時，被調(diào)用
          • gc()：由session.gc_probability和session.gc_divisor決定，任何時候軍可能被調(diào)用?
          • 具體用法
          • 將Session寫入數(shù)據(jù)庫
          • 將Session寫入Memcache

      至此，PHP的基礎(chǔ)學(xué)習(xí)算是完成了，需要多做多學(xué)，方能提高！

      http://www.bkjia.com/PHPjc/980026.htmlwww.bkjia.comtruehttp://www.bkjia.com/PHPjc/980026.htmlTechArticlePHP筆記（PHP高級篇），php筆記 高級篇中將涉及數(shù)據(jù)庫的使用以及Cookie和Session會話，提高PHP的開發(fā)效率和運(yùn)行效率 PHP程序員需要掌握的MyS...