SQL Base Note Memo

The type of data field defined in MySQL is important for optimizing your database.

MySQL supports multiple types and can be broadly divided into three categories: numeric, date/time, and string (character) types.

Numeric type:
tinyint smallint mediumint int bigint float double

Decimal (size,d) numeric (size,d) equivalent
"Size" Specifies the maximum number of digits. "D" Specifies the maximum number of digits to the right of the decimal point.

Date and Time type:

Date Yyyy-mm-dd
Time HH:MM:SS
Year YYYY
DateTime YYYY-MM-DD HH:MM:SS
Timestamp YYYYMMDD HHMMSS timestamp types have proprietary auto-update features

String type:
CHAR 0-255-byte fixed-length string
VARCHAR 0-65535-byte variable-length string
Tinyblob a binary string of 0-255 bytes and no more than 255 characters
Tinytext 0-255 bytes Short text string
BLOB 0-65 535-byte long text data in binary form
Text 0-65 535-byte long text data
Mediumblob 0-16 777 215 bytes Medium-length text data in binary form
Mediumtext 0-16 777 215 bytes Medium length text data
Logngblob 0-4 294 967 295 byte binary form of large text data
Longtext 0-4 294 967 295 byte maximum text data

------------------------------------------------------------------------------------

The following information is required to create a MySQL data table:

Table name
table field Name
Define each table field

National Standard GB2312: one kanji = 2 bytes
UTF-8: One kanji = 3 bytes (general)
First to determine the MySQL version
4.0 version, varchar (50), refers to 50 bytes, if stored UTF8 kanji, can only save 16 (each Kanji 3 bytes)
Above version 5.0, varchar (50), refers to 50 characters, regardless of whether it is stored in numbers, letters or UTF8 Kanji (3 bytes per kanji), can be
Storage of 50

CREATE TABLE Nangua (nangua_id INT not NULL auto_increment,
Nangua_title VARCHAR (200),
Nangua_author VARCHAR (30),
Nangua_date date,
PRIMARY KEY (nangua_id));

Do not want the field to be null to set the property to NOT NULL, if the data entered in the field is null when manipulating the database, an error will be
Auto_increment defines a property that is self-increasing, typically used for a primary key, and the value is automatically added to 1.
The PRIMARY key keyword is used to define the column as the primary key. You can use multiple columns to define a primary key, and the columns are separated by commas.

PHP Way to create:

Header ("Content-type:text/html;charset=utf-8");
$con =mysql_connect (' localhost ', ' root ', 123);
if (! $con) {
Die (' Cannot connect to database '. Mysql_error ());
}
Echo ' Successfully connected data '. ' <br/> ';
$sql = "CREATE Table Qiezi (".
" nangua_id INT not NULL auto_increment, ".
"Nangua_title VARCHAR (200),".
"Nangua_author VARCHAR (30),".
"Nangua_date date,".
PRIMARY KEY (nangua_id).
")";
mysql_select_db (' Managerie ');
$newtable =mysql_query ($sql, $con);
if (! $newtable) {
Die (' cannot create table '. Mysql_error ());
}
Echo ' Successfully created the table! '. ' <br/> ';
Mysql_close ($con);

------------------------------------------------------------------------------------
To delete a table:
Header ("Content-type:text/html;charset=utf-8");
$con =mysql_connect (' localhost ', ' root ', 123);
if (! $con) {
Die (' Cannot connect to database '. Mysql_error ());
}
Echo ' Successfully connected data '. ' <br/> ';
mysql_select_db (' Managerie ');
$sql = ' DROP TABLE qiezi ';
$drop =mysql_query ($sql, $con);
if (! $drop) {
Die (' not removed successfully '. Mysql_error ());
}
Echo ' Delete succeeded! ';
Mysql_close ($con);

------------------------------------------------------------------------------------

Insert data:
<?php

Header (' Content-type:text/html;charset=utf-8 ');
if (Isset ($_post[' Sub ')) {

$con =mysql_connect (' localhost ', ' root ', ' 123 ');
if (! $con) {

Die (' Connection not successful! '. Mysql_error ());
}

Echo ' link was successful! ‘;

if (! GET_MAGIC_QUOTES_GPC ()) {

$nangua _title=addslashes ($_post[' nangua_title ');
$nangua _author=addslashes ($_post[' nangua_author ');
$nangua _date=addslashes ($_post[' nangua_date ');

}else{

$nangua _title=$_post[' Nangua_title '];
$nangua _author=$_post[' Nangua_author '];
$nangua _date=$_post[' nangua_date '];

}

$sql = "CREATE Table Kk4 (".
"nangua_id SMALLINT UNSIGNED not NULL auto_increment,".
"Nangua_title VARCHAR (200),".
"Nangua_author VARCHAR (100),".
"Nangua_date date,".
"PRIMARY KEY (nangua_id)".
")";

mysql_select_db (' Managerie ');

if (Mysql_num_rows (mysql_query ("SHOW TABLES like ' kk4 '") ==0) {

$newtable =mysql_query ($sql, $con);

if (! $newtable) {

Die (' CREATE table unsuccessful '. Mysql_error (). ' <br/> ');

}

Echo ' created table succeeded '. ' <br/> ';

}

$insert _sql= "INSERT INTO Kk4 (".
"Nangua_title,nangua_author,nangua_date)".
"VALUES".
"(' $nangua _title ', ' $nangua _author ', ' $nangua _date ')";

$write =mysql_query ($insert _sql, $con);
if (! $write) {
Die (' Write Data unsuccessful '. Mysql_error (). ' <br/> ');
}
Echo ' writes data successfully! ‘;
Mysql_close ($con);

}
else{
?>

<form method= "POST" action= "<?php $_php_self?>" >
<table width= "border=" 0 "cellspacing=" 1 "cellpadding=" 2 ">
<tr>
&LT;TD width= ">tutorial" title</td>
<td>
<input name= "Nangua_title" type= "text" id= "Nangua_title" >
</td>
</tr>
<tr>
&LT;TD width= ">tutorial" author</td>
<td>
<input name= "Nangua_author" type= "text" id= "Nangua_author" >
</td>
</tr>
<tr>
&LT;TD width= ">submission" Date [Yyyy-mm-dd]</td>
<td>
<input name= "Nangua_date" type= "text" id= "Nangua_date" >
</td>
</tr>
<tr>
&LT;TD width= "> </td>
<td> </td>
</tr>
<tr>
&LT;TD width= "> </td>
<td>
<input name= "Sub" type= "Submit" id= "sub" value= "" >
</td>
</tr>
</table>
</form>
<?php
}
?>

------------------------------------------------------------------------------------
SELECT Query data:

SELECT field1, Field2,... fieldn table_name1, table_name2 ...
[WHERE Clause]
[OFFSET M] [LIMIT N]

You can use one or more tables in a query statement, split between tables using commas (,), and use the where statement to set the query criteria.
The SELECT command can read one or more records.
You can use an asterisk (*) instead of another field, and the SELECT statement returns all the field data for the table
You can use the WHERE statement to include any condition.
You can specify the data offset for the SELECT statement start query by using offset. By default, the offset is 0.
You can use the LIMIT property to set the number of records returned.

Header (' Content-type:text/html;charset=utf-8 ');

$con =mysql_connect (' localhost ', ' root ', ' 123 ');
if (! $con) {

Die (' Connection not successful! '. Mysql_error ());
}

Echo ' link was successful! ‘;

$sql = "Select Nangua_title,nangua_author,nangua_date from Kk4";

mysql_select_db (' Managerie ');

$return _result=mysql_query ($sql, $con);

if (! $return _result) {

Die (' Cannot query data '. Mysql_error ());

}

while ($row =mysql_fetch_array ($return _result,mysql_assoc)) {

The "echo" title is: {$row [' nangua_title ']}<br/> '.
"The author is:". $row [' Nangua_author ']. ' <br/> '.
"The publication Time is: {$row [' nangua_date ']}<br/> ';

}
Mysql_free_result ($con);
echo ' Traversal done! ‘;
Mysql_close ($con);

After we execute the SELECT statement, it is a good practice to release the cursor memory. can be implemented by PHP function Mysql_free_result ().
The release of memory.

------------------------------------------------------------------------------------
Update updates to modify data:

The data in MySQL needs to be modified or updated, and we can use the SQL UPDATE command to manipulate
$sql = "UPDATE kk4 SET nangua_title=11111111111 WHERE nangua_id = 122";

mysql_select_db (' Managerie ');

$return _result=mysql_query ($sql, $con);

------------------------------------------------------------------------------------

Delete Deletes:

$sql = "DELETE from Kk4 WHERE nangua_title=11111111111";

------------------------------------------------------------------------------------

Like clause:
Use percent sign (%) characters in a LIKE clause to represent any character, similar to an asterisk (*) in UNIX or regular expressions
If you do not use a percent sign (%), the LIKE clause has the same effect as the equals (=).
$sql = ' SELECT nangua_id from Kk4 WHERE nangua_author like ' 4% ';

mysql_select_db (' Managerie ');

$return _result=mysql_query ($sql, $con);

while ($xx =mysql_fetch_assoc ($return _result)) {

echo "{$xx [' nangua_id ']}";

}
------------------------------------------------------------------------------------

ORDER BY sort clause
SELECT field1, Field2,... fieldn table_name1, table_name2 ...
ORDER by field1, [field2 ...] [ASC [DESC]]

$sql = ' SELECT nangua_date from Kk4 ORDER by nangua_id DESC ';

mysql_select_db (' Managerie ');

$return _result=mysql_query ($sql, $con);

while ($xx =mysql_fetch_array ($return _result,mysql_assoc)) {

echo "{$xx [' nangua_date ']}<br/> ';

}

------------------------------------------------------------------------------------

Select VERSION (); View MySQL version information

To select only a different value from the column, you need to use the Select DISTINCT statement:

Select Current_date (); View current Date Select Curdate ();

Select 5*2; Calculation

Select Now (); View current Time

\c Cancel

Quit Quit \q quit

Select User (); View Current User

LOAD DATA LOCAL INFILE '/path/pet.txt ' into TABLE pet; Loading a text file into a database
LINES TERMINATED by ' \ r \ n ';

Year () years of interception

Right (Curdate (), 5) Back to the rightmost string of Len characters

Drop Delete Table Delete database drop table if exists kk4; Drop database Kk4;

Alter MODIFY TABLE structure Delete add column

ALTER TABLE test rename test1; Modify Table Name

ALTER TABLE table_name Add COLUMN_NAME datatype//Add column

ALTER TABLE table_name DROP column column_name//delete columns

ALTER TABLE ' TableName ' MODIFY column ' FieldName ' VARCHAR (14)//Modify Column Properties

ALTER TABLE PP Modify ID int default 1; modifying column Properties
ALTER TABLE PP change id newid int; Modify column names

SQL uses single quotation marks to wrap text values (most database systems also accept double quotes). Do not use quotation marks if it is numeric.

TOP clause
The TOP clause is used to specify the number of records to return.
The TOP clause is useful for large tables with thousands of records.

SELECT * from name limit 4;
SELECT * from name ORDER BY id DESC limit 2;
Order BY and limit first

"%" can be used to define wildcard characters (missing letters in the pattern) one or more
SELECT * from name where FirstName like '%7 ';

Not included: Not like

SELECT * from name where FirstName '%7% ';

% replaces one or more characters
_ Replaces only one character

The in operator allows us to specify multiple values in the WHERE clause.
SELECT * from name where FirstName in (' Bb8 ', ' Bb6 ');

operator between ... and selects a range of data between two values. These values can be numeric, text, or date.
Note the different databases to the between ... The and operator is handled differently.
SELECT * from name where newid between 2 and 7; MySQL is comprised of 2 and 7

Aliases alias as or omit the General multi-table query when adding an individual name more convenient
SELECT * from name as n where N.firstname= ' Bb3 ';
SELECT * from name N where n.firstname= ' Bb3 ';
SELECT Po. OrderID, P.lastname, P.firstname
From Persons as P, product_orders as PO
WHERE p.lastname= ' Adams ' and p.firstname= ' John '

Join==inner join: Returns a row if there is at least one match in the table
Left JOIN: Returns all rows from the table, even if there is no match in the right table
Right JOIN: Returns all rows from the correct table even if there is no match in the left table
Full JOIN: Returns rows if there is a match in one of the tables MySQL does not support

The UNION operator is used to combine the result set of two or more SELECT statements.
The SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, in each SELECT statement
The order of the columns must be the same.

Select INTO MySQL does not support

Constraints are used to restrict the type of data joined to the table
Not NULL
Unique and primary key differences for unique constraints are that the primary key in a table can have only one
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

CREATE TABLE Persons
(
id_p int not NULL UNIQUE, setting constraint
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255)
)

Delete Constraint
ALTER TABLE Nangua DROP INDEX name;

When the table has been created, create a UNIQUE constraint in the id_p column:
ALTER TABLE Nangua add unique (id_p)

Naming UNIQUE constraints
ALTER TABLE Persons
ADD CONSTRAINT Uc_personid UNIQUE (id_p,lastname)

Foreign key foreign key in one table points to PRIMARY key in the other table.

CREATE TABLE Orders
(
Id_o int not NULL,
OrderNo int not NULL,
id_p int,
PRIMARY KEY (Id_o),
FOREIGN KEY (id_p) REFERENCES Persons (id_p)
)

A CHECK constraint is used to limit the range of values in a column.
If you define a CHECK constraint on a single column, the column only allows a specific value.
If a CHECK constraint is defined on a table, the constraint restricts the value in a specific column.

CREATE TABLE Persons
(
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255),
CONSTRAINT Chk_person CHECK (id_p>0 and city= ' Sandnes ')
)

ALTER TABLE Persons
ADD CONSTRAINT Chk_person CHECK (id_p>0 and city= ' Sandnes ')

ALTER TABLE Persons
DROP CHECK Chk_person

ALTER TABLE users auto_increment=10000; Modifying the starting Value

Default setting defaults
CREATE TABLE Persons
(
id_p int not NULL,
LastName varchar (255) is not NULL,
FirstName varchar (255),
Address varchar (255),
City varchar (255) DEFAULT ' Sandnes '
)

ALTER TABLE Persons
ALTER City SET DEFAULT ' Sandnes '

The CREATE INDEX statement is used for creating indexes in the table.
Updating a table that contains an index requires more time than updating a table that does not have an index, because the index itself needs to be updated. Therefore, the ideal
The practice is to create indexes only on columns (and tables) that are often searched.

CREATE INDEX Personindex
On person (LastName)

CREATE INDEX Personindex
On person (LastName DESC)

By using the DROP statement, you can easily delete indexes, tables, and databases.
ALTER TABLE table_name DROP INDEX index_name

SQL Date Function:

The now () function returns the current date and time.
ALTER TABLE Wokao ADD orderdate datetime NOT NULL default now ();
Insert into Wokao (name,age2) VALUES (' Z ', 20);

The Curdate () function returns the current date.
Select Curdate ();

The Curtime () function returns the current time.

The date () function returns the day part of a date or date/time expression.
Date (Curtime ()) Extract Dates section

The EXTRACT () function is used to return a separate part of a date/time, such as year, month, day, hour, minute, and so on.
The EXTRACT (unit from date) Date parameter is a valid day-expression.
SELECT EXTRACT (year from OrderDate) as OrderYear,
EXTRACT (MONTH from OrderDate) as OrderMonth,
EXTRACT (day from OrderDate) as Orderday
From Orders
WHERE orderid=1

Select Extract (year from OrderDate) from Wokao;

The Date_add () function adds a specified time interval to a date.
Select Date_add (orderdate,interval 2 week) from Wokao where id=102;

Date_sub () Subtracts a specified time interval from a date
Select Date_sub (orderdate,interval 2 week) from Wokao where id=102;

DATEDIFF () returns the number of days between two dates
DATEDIFF (DATE1,DATE2)

SQL functions:
Select AVG (Nihao) from Wokao; Find average

The count count () function returns the number of rows that match the specified condition.
Select COUNT (Nihao) from Wokao where nihao=12;
Select COUNT (Distinct Nihao) from Wokao; Remove the duplicate

First () function
The first () function returns the value of the number one record in the specified field.
Tip: You can use the order BY statement to sort records.
MySQL does not have the first function

MAX () function
The Max function returns the maximum value in a column. NULL values are not included in the calculation.
Select Max (Nihao) from Wokao;

SUM () function
The SUM function returns the total number of numeric columns (total).

GROUP by statement subtotals.
The GROUP BY statement is used to combine aggregate functions to group result sets based on one or more columns.
Select Name,sum (Nihao) from Wokao Group by Nihao;
Select Name,sum (Nihao) from Wokao Group by Nihao ORDER BY Nihao Desc

Having a sentence allows us to filter the various data after the group, where it filters the records before aggregating, that is, the group by and
Having a sentence before. The HAVING clause filters the group records after aggregation.

Select Name,sum (Nihao) from Wokao GROUP by name have sum (Nihao) <20;
Select Name,sum (Nihao) from Wokao where name= ' nn ' GROUP by name have sum (Nihao) <20;

UCASE () function
The UCASE function converts the value of a field to uppercase.
SELECT UCASE (column_name) from table_name

LCASE () function
The LCASE function converts the value of the field to lowercase.

MID () function
The MID function is used to extract characters from a text field.
SELECT MID (Column_name,start[,length]) from table_name
column_name required. The field to extract the characters from.
Start Required. Specifies the starting position (the starting value is 1).
Length is optional. The number of characters to return. If omitted, the MID () function returns the remaining text.
Select Mid (nihao,1,1) from Wokao;

ROUND () function
The ROUND function is used to round a numeric field to a specified number of decimal digits.
SELECT ROUND (column_name,decimals) from table_name

Select Round (ff,2) from Wokao;

SQL Base Note Memo