MySQL中的增删改查操作

2016年11月30日
1、插入数据

insert  [INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
或：

INSERT  [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
或：

INSERT  [INTO] tbl_name [(col_name,...)]
    SELECT ...
当有默认值或自增值时，使用null或default即可。

2、删除数据
delete from 表名 where 。。。

3、修改数据
update 表名 set 字段=.. , 字段=.., … where …

4、查询数据

SELECT
[all | distinct | distinctrow ]

[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
  [ASC | DESC] , ...]
[LIMIT {[offset（从0开始起的第几行）,] row_count（查询行数） | row_count OFFSET offset}] 分页查询

    select * from shi limit 3,3;//每页三行，第二页。
                      limit (n-1)*m,m; //n为页数，m为每页行数。

5、建表

create [temporary] table [if not exists] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
或：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(] like old_tbl_name [)]; // 重新建一张空表，和以前的表一样

create_definition:
column_definition

| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)
| INDEX [index_name] [index_type] (index_col_name,…)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,…)
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,…) [reference_definition]
| CHECK (expr)

column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
    [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
    [COMMENT 'string'] [reference_definition]

type:
tinyint| smallint| mediumint| int| integer| bigint[(length)] [UNSIGNED] [zerofill]

| real | double | float | decimal | numeric(length,decimals) [unsigned] [zerofill]

| DATE| TIME | TIMESTAMP | DATETIME | CHAR(length) [binary | ascii | unicode]

| VARCHAR(length) [BINARY]

| tinyblob | blob | mediumblob | longblob

| tinytext | text | mediumtext | longtext [binary]

| ENUM(value1,value2,value3,…)
| SET(value1,value2,value3,…)

check(age >0 && and age < 130)