PHP Database Operations Command Essence Daquan

1, table structure//Column information 2, table data//row information 3, table index//Add rows in the column to the index (in general, a table must be the ID of this column of all the data is added to the primary key index)

2, [DOS] close mysql:net stop MySQL
Open mysql:net start MySQL
Login mysql:mysql-uroot-p123--tee=c:\mysql.log
View database commands: show databases;
Enter test database: Use test
View database tables: show tables;
Creating a table: Create TABLE User (id int,name varchar (), pass varchar (30));
View table structure or field: DESC user
View Table data: Select*from user;
View all indexes in a table:show index from T2;
Inserting data into a table: INSERT into User (Id,name,pass) VALUES (1, "Hello", "123");
View the Id:select*from user where id=1 in the table;
Delete the id:delete from user where id=1 in the table;
Modify the values in the table: Update user set name= ' hello ' where id=1;
Exit Myaql:exit;
Creating a database: Create databases Txet;
Delete databases: drop database;
Modify table Name: Rename table user to User1;
Delete tables: drop table user1;

Type of table field:
1, Value: Int//int (3) Independent of the length, not enough 3 bits of the front to fill 0, the default does not show
Float
2, String: char (N) 255 bytes occupy N bytes
varchar (n) up to 65535 bytes deposit how many bytes
Text 65535 bytes
Longtext 4.2 billion bytes
3. Date: Date time datetime year

Base-field properties for the database:
1, unsigned unsigned, full of positive numbers
2, Zenrofill 0 fill, int (3), not enough 3 bit 0
3. Auto_increment Self-increment
4, null This column value is allowed to be null
5, NOT NULL This column value is not allowed to be null
6. Default value is not allowed to be null

View four character sets with \s:
1. Server Characterset:utf8 Character Set
2, DB Characterset:utf8 database character set
3, Client Characterset:utf8
4. Conn. Characterset:utf8 Client Connection Character Set
View database Character Set command: SOHW CREATE database text;
View table Character Set command: show create table user;
Set MySQL client and connection character sets in php: $sql = "set names UTF8";

Table field Index:
1. Primary KEY index
2. General Index
Check the SQL statement: DESC SELECT * from user where id=3\g//add \g turn the table upside down
Rows 1 indicates that a id=3 person was found to retrieve a row
View all indexes in a table: Show index from user;

Post-Maintenance General index:
1. Add normal index: ALTER TABLE t2 add index In_name (name);
2. Delete Normal index: ALTER TABLE t2 DROP INDEX in_name;

Post-Maintenance database fields:
1. Add fields
ALTER TABLE T1 add age int;
2. Modify Fields
ALTER TABLE T1 modify age int not null default 20;
3. Delete fields
ALTER TABLE T1 drop age;
4. Modify field names
ALTER TABLE T1 change name username varchar (30);

Structured Query Language SQL consists of four parts:
1. DDL/Data definition language, Reate,drop,alter
2. DML//Data manipulation language, Insert,update,delete
3, DQL//Data query Language, select
4. DCL//Data Control statement, Grant,commit,rollback

Add-insert:insert INTO T1 (username) values (' G ');
change-update:update T1 set username= ' F ' where id=6;
change multiple values at once:update t1 set id=10, username= ' BB ' where id = 7;
Delete-delete:
Delete from T1 where id=6;
Delete from T1 where ID in (1,2,5);
Delete from T1 where id=1 or id=3 or id=5;
Delete from T1 where id>=3 and id<=5;
Delete from T1 where ID between 3 and 5;
Check-select:

1. Select a specific field: SelectID,namefrom user where id=3;
2. Alias the field-as:selectPassAsP, id from user where id=3;
Select Pass P,id from user where id=3;
3. Take out the duplicate values in the column: SELECT distinct name form user;
4. Query with WHERE Condition: SELECT * from user where id>=3 and id<=5;
5, query null value null:select *from user where pass is null;
Select *from user where pass is not null;
6, Search like keyword: select * form user wher name like '%3% ';
SELECT * Form user wher name like '%3% ' or the name like '%1% ';
SELECT * Form user wher name RegExp '. *3.* ';
SELECT * Form user wher name RegExp ' (. *3.*) | (. *5.*) ';
7. Use order by to sort the results of the query:
Ascending select * from the user Ordeer by IDASCNOTE: ASC can not be written in ascending order.
Descending select * from the user Ordeer by IDdesc;
8. Limit the number of outputs using limit:
SELECT * from the user order by id DESC limit 0, 3;
SELECT * from the user order by id desc limit 3;
9. concat function-string connector: Select Concat ("A", "-", "C");
10. Rand function-random sort: Selec 8 from user order by rans () limit3;
11. Count Statistics: SELECT COUNT (*) from user;//http://www.pprar.com
Select COUNT (id) from user;
Select COUNT (id) from user where name= ' user1 ';//Count User1 occurrences
12. Sum sum: select SUM (ID) from user where name= ' user1 ';//match the required ID
13. AVG Average: Select AVG (ID) from user;
14, Max Max: select Max (ID) from user;
15, Min min: select min (id) from user;
16. Group Aggregation: SELECT Name,count (ID) tot from mess group by name ORDER by tot Desc;//tot is an alias//group by must be written in front of order by
Select Name,count (ID) tot from mess group by name has a tot>=5;//group by must be written in the having before//group plus conditions must be used with having, not where
17. Add the UID field after the ID field: ALTER TABLE post add UID int after ID;
18.Multi-table query:
(1) General enquiry-Multi-table
(2) Nested query-Multiple tables
(3) Left connection query-multi-table
General Query:
Select User.name,count (post.id) from User,post where User.id=post.uid group by Post.uid;
Left Join answer query:
Select User.name,post.title,post.content from user-left join post on user.id=post.uid;//Show all users sent or not sent names plus content
General Query:
The person who gets the post-general query mysql> select DISTINCT user.name from User,post where User.id=post.uid;
Nested queries:
The person who gets the post-nested query mysql> select name from the user where ID in (select UID from post);

PHP Operations Database:

1. Connect to MySQL database via PHP
2. Select Database
3. Insert operation via PHP
4. Delete operation via PHP
5. Update operation via PHP
6. Select Operation via PHP
Connect MySQL database via PHP: mysql_connect ("localhost", "root", "123");
Select database: mysql_select_db ("test");
Set the client and connection character set: mysql_query ("Set names UTF8");
Insert operation via PHP://Receive data from form $uername= "User1"; $passwoed = "123";
$sql = "INSERT into T1 (Username,password) VALUES (' $username ', ' $password ')";
Execute this MySQL statement var_dump (mysql_query ($sql));
Release the connection resource mysql_close ($conn);
Update operation via PHP $sql= "update t1 set username= ' User2 ', pssword= ' 111 ' where id=8 ';
Delete operation via php: $sql = "delete form t1 where id=8";
To fetch data from the result set:
mysql_fetch_assoc//Associative arrays
mysql_fetch_row//indexed arrays
mysql_fetch_array//Mixed arrays
mysql_fetch_object//Object
Select operation via PHP $sql= "select form T1";
Take all the data from the result set: while ($row =mysql_fech_assoc ($result)) {echo

"; Print_r ($row) echo "

";}
mysql_insert_id//gets the ID generated by the insert operation in the previous step
mysql_num_fields//the number of rows affected by the Insert,update,delete operation musql_num_rows//get the rows affected by the select operation
Get the total number of table rows: $sql = "SELECT count (*) Form T1";
$rst =mysql_query ($sql);
$tot =mysql_fetch_row ($rst);

PHP Database Operations Command Essence Daquan