MySQL Database design

MySQL Database design

String escape sequence

NUL (ASCLL 0)

\‘

\"

\b backspace

\ n New line

\ r Enter

\ t tab

\ \ Counter slash

Numeric column type

TINYINT 1 bytes very small integer signed value:-128 to 127 unsigned value: 0 to 255

SMALLINT 2 -byte small integer signed value: -32768

Mediumint 3 -byte medium-sized integer with symbolic value:

The INT 4-byte standard integer has a signed value:

BIGINT 8-byte large integer with signed value:

Float 4-byte single-precision floating-point number with a signed value:

double 8-byte dual-precision floating-point number has a signed value:

DECIMAL M bytes A string of floating-point numbers with a signed value:

CHAR

VARCHAR

Tinyblob

Blob

Mediumblob

Longblob

Tinytext

TEXT

Mediumtext

Longtext

Enum

SET

Date and Time type formats

DATE "Yyyy-mm-dd"

Time "Hh:mm:ss"

DATETIME "Yyyy-mm-dd hh:mm:ss"

TIMESTAMP "Yyyymmddhhmmss"

Year "YYYY"

Range of values for numeric column types

Column properties for numeric columns:

Auto_increment

Create a unique glyph or sequential value

CREATE Table A (I int auto_increment not NULL primary key);

UNSIGNED

Suppress negative values for this property

CREATE TABLE Test (num tingint, num2 tingint unsigned);

NULL and NOT NULL

Default is NULL

Create Talbe Test (num int, num2 int default 1, num3 int default null)

Operator

= equals

<> or! = Not Equal to

<=> NULL safe equals (Null-safe)

< less than

> greater than

<= less than or equal to

>= greater than or equal to

Between exists with specified range

in exists in the pointing set

is null null

is isn't null

Like wildcard match

RegExp or rlike regular expression matching

% (percent percent)

A%b represents a string of any length ending with a b at the beginning of a

_ (Lower horizontal line)

A_b represents any character with a length of 3 ending with a beginning B,

PRIMARY KEY

UNIQUE

Not NULL

Auto_increment

DEFAULT Default_value

PRIMARY KEY and unique

Database operations:

Show database;

Show tables;

Use Dataname;

Create database name;

CREATE TABLE Test (

ID int primary KEY auto_increment,

Title varchar (16)

);

describe table name;

DESC table name;

Drop Talbe tablename;

Alter TALBE table name action

Add Column Name

Add primary Key

ALTER COLUMN name set default

Drop Column Name

Drop PRIMARY Key

Drop INDEX Index_name

Rename as new table name

Insert into table name (Id,name,...) valuse (1,liqingbo);

Update table name set column name = data value WHERE clause;

Delet from table name WHERE clause;

Select

GROUP BY

Having

ORDER BY

Limit

DESC ASC

Count sum avg Max min

Resource mysql_connect (Host,root,password);

Mysql_close ();

BOOL mysql_select_db (tablename[,], connection return variable);

Resource mysql_query (SQL statement)

Resource Mysql_db_query (tablename[,]);

Array mysql_fetch_row ();

Array mysql_fetch_array ();

MYSQL_ASSOC:

Mysql_num:

Mysql_both:

Mysql_num_fields (); get the number of fields in the result set

Mysql_num_rows (); get the number of rows in the result set

Mysql_result ();

Mysql_free_result ();

Mysql_close ();

Mysql_pconnect;

mysql_create_db;

mysql_drop_db;

Mysql_fetch_object;

Mysql_affected_rows;

Instance:

Establishing a database connection

$link = mysql_connect ("host", "Root", "123456");

mysql_select_db ("MyTable", $link);

$sql = "SELECT * from user";

$send = mysql_query ($SLQ);

while ($row = Mysql_fetch_row ($sen)) {

foreach ($row as $v)

{

echo "$v";

}

}

MySQL Database design