常用SQL语言总结

来源：互联网发布：龙之谷数据库怎么修改编辑：程序博客网时间：2024/05/16 08:33

DDL语句：对数据库内部的对象进行创建、删除、修改的操作语言。

l 数据库操作：显示所有数据库(showdatabases;)，创建数据库(create database name;)，删除数据库(drop databasename;)，选择数据库(use name;)。

l 表操作：显示当前数据库下的所有表(show tables;)，创建表(create table name;)，删除表(drop table name;)，显示表(desc name;)，显示表定义(show create table name \G;)，更改表名(alter tablename rename [to] newname;)，修改表字段类型(alter table tablename modify [column] column_definition [first|aftercol_name];)，增加表字段(alter table tablename add [column] column_definition [first|aftercol_name];)，删除表字段(alter table table_name drop [column] col_name)，字段改名(altertable table_name change [column] old_column_name new_column_definition [first|aftercol_name];)，修改字段排列顺序(使用modify和add命令时指示位置)，

l DDL操作实例

Enter password: root

Welcometo the MySQL monitor. Commands end with; or \g.

YourMySQL connection id is 1

Serverversion: 5.1.32-community MySQL Community Server (GPL)

Type'help;' or '\h' for help. Type '\c' to clear the buffer.

创建数据库

mysql>create database test1

-> ;

Query OK,1 row affected (0.00 sec)

mysql>create database test1

-> ;

ERROR1007 (HY000): Can't create database 'test1'; database exists

显示数据库

mysql>show databases;

+--------------------+

|Database |

+--------------------+

|information_schema |

|menagerie |

|mysql |

|test |

|test1 |

+--------------------+

5 rows inset (0.09 sec)

选择数据库

mysql>use test1

Databasechanged

查看数据库中的所有表

mysql>show tables

-> ;

Empty set(0.00 sec)

mysql>use mysql

Databasechanged

mysql>show tables

-> ;

+---------------------------+

|Tables_in_mysql |

+---------------------------+

|columns_priv |

| db |

|event |

|func |

|general_log |

|help_category |

|help_keyword |

|help_relation |

|help_topic |

|host |

|ndb_binlog_index |

|plugin |

|proc |

|procs_priv |

|servers |

|slow_log |

|tables_priv |

|time_zone |

|time_zone_leap_second |

|time_zone_name |

|time_zone_transition |

|time_zone_transition_type |

|user |

+---------------------------+

23 rowsin set (0.34 sec)

删除数据库

mysql>drop database test1;

Query OK,0 rows affected (0.19 sec)

mysql>show databases

-> ;

+--------------------+

|Database |

+--------------------+

|information_schema |

|menagerie |

|mysql |

|test |

+--------------------+

4 rows inset (0.00 sec)

创建表

mysql>creat table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno

int(2));

ERROR1064 (42000): You have an error in your SQL syntax; check the manual that

correspondsto your MySQL server version for the right syntax to use near 'creat

table emp(ename varchar(10),hiredate date,saldecimal(10,2),deptno int(2))' at

line 1

mysql>create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno

int(2));

Query OK,0 rows affected (0.14 sec)

显示表

mysql>desc emp

-> ;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

|sal | decimal(10,2) | YES | |NULL | |

|deptno | int(2) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

4 rows inset (0.01 sec)

显示创建表信息

mysql>show create table emp \G;

***************************1. row ***************************

Table: emp

CreateTable: CREATE TABLE `emp` (

`ename` varchar(10) DEFAULT NULL,

`hiredate` date DEFAULT NULL,

`sal` decimal(10,2) DEFAULT NULL,

`deptno` int(2) DEFAULT NULL

)ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row inset (0.00 sec)

ERROR:

No queryspecified

删除表

mysql>drop table emp;

Query OK,0 rows affected (0.06 sec)

mysql>create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno

int(2));

Query OK,0 rows affected (0.03 sec)

修改表类型

mysql>alter table emp modify ename varchar(20);

Query OK,0 rows affected (0.05 sec)

Records:0 Duplicates: 0 Warnings: 0

mysql>desc emp;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

|sal | decimal(10,2) | YES | |NULL | |

|deptno | int(2) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

4 rows inset (0.00 sec)

增加表字段

mysql>alter table emp add age int(3);

Query OK,0 rows affected (0.03 sec)

Records:0 Duplicates: 0 Warnings: 0

mysql>

mysql>desc emp;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

|sal | decimal(10,2) | YES | |NULL | |

|deptno | int(2) | YES | | NULL | |

|age | int(3) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows inset (0.01 sec)

删除表字段

mysql>alter table emp drop column age;

Query OK,0 rows affected (0.03 sec)

Records:0 Duplicates: 0 Warnings: 0

mysql>desc emp;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

|sal | decimal(10,2) | YES | |NULL | |

|deptno | int(2) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

4 rows inset (0.02 sec)

修改字段类型

mysql>alter table emp add column age int(3);

Query OK,0 rows affected (0.03 sec)

Records:0 Duplicates: 0 Warnings: 0

mysql>desc emp

-> ;

+----------+---------------+------+-----+---------+-------+

+----------+---------------+------+-----+---------+-------+

|sal | decimal(10,2) | YES | |NULL | |

|deptno | int(2) | YES | | NULL | |

|age | int(3) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

5 rows inset (0.00 sec)

字段更改名称

mysql>alter table emp change column age age1;

ERROR1064 (42000): You have an error in your SQL syntax; check the manual that

correspondsto your MySQL server version for the right syntax to use near '' at

line 1

mysql>alter table emp change age age1;

ERROR1064 (42000): You have an error in your SQL syntax; check the manual that

correspondsto your MySQL server version for the right syntax to use near '' at

line 1

mysql>alter table emp change age age1 int(3);

Query OK,0 rows affected (0.03 sec)

Records:0 Duplicates: 0 Warnings: 0

DML语言：对数据库中表记录的操作。包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select）。

l 插入记录：

Insert into tablename (field1,field2,…fieldn) values(value11,values12,…values1n),…(valuem1,valuem2,…valuemn);

一次可插入单条记录，也可插入多条记录；也可省略field按照表默认顺序插入，也可以值设置需要插入的field，其余使用默认值

l 更新记录：

更新单个表中记录

Update tablenameset field1=value1,field2=value2,……fieldn=valuen [where condition]

更新多个表中记录

Update tablename1t1,tablename2 t2, …… tablenamen tn s et t1.field1=expr1,……tn

.fieldn=exprn [wherecondition]

l 删除记录

删除单个表中记录

Delete fromtablename [where condition]

删除多个表中记录

Delete t1,t2,…tnfrom tablename1 t1,……tablenamen tn [where condition]

l 查询记录

选择不重复的记录

Select distinct fieldname from tablename;

条件查询

Select * from tablename [where condition];

排序和限制

Select * from tablename [where conditong] [order by field1[desc|asc],field2 [desc|asc],…fieldn[desc|asc]] [limit offset_start,row_count];

Offset_start表示从第几条记录开始显示，row_count表示显示几条记录。

聚合

Select [field1,……fieldn] fun_name from tablename [whereconditon] [group by field1,……fieldn [with rollup]] [having condition]

Fun_name表示聚合操作，sum(求和),count(*)(记录数),max(最大值),min(最小值)。

Group by 表示分类聚合的字段

With rollup 对分类聚合的结果再汇总。

Having 对分类后的结果再进行条件过滤。

表连接

子查询

关键字：in,not in,=,!=,exists,not exists;

记录联合

Select * from table1

Union|union all

Select * from table2

……

Union|union all

Select * from tablen;

l 实例

记录联合

mysql>select deptno from emp

-> union all

-> select deptno from dept;

+--------+

| deptno|

+--------+

| 1 |

| 2 |

| 1 |

| 4 |

| 1 |

| 2 |

| 5 |

+--------+

7 rows inset (0.05 sec)

mysql>select deptno from emp

-> union

-> select deptno from dept;

+--------+

| deptno|

+--------+

| 1 |

| 2 |

| 4 |

| 5 |

+--------+

4 rows in set (0.02 sec)

DCL语言：DBA用来管理系统中的对象权限时使用。

帮助手册的使用：

l 按层次看帮助：? Contents;->?Data types;->?int

l 快速查阅帮助 ? show

l 查看某个命令的语法 ?create table

l 实例

mysql>? contents;

You askedfor help about help category: "Contents"

For moreinformation, type 'help <item>', where <item> is one of the followi

categories:

Account Management

Administration

Compound Statements

Data Definition

Data Manipulation

Data Types

Functions

Functions and Modifiers for Use with GROUPBY

Geographic Features

Language Structure

Plugins

Table Maintenance

Transactions

User-Defined Functions

Utility

mysql>? data types;

You askedfor help about help category: "Data Types"

For moreinformation, type 'help <item>', where <item> is one of the followi

topics:

AUTO_INCREMENT

BIGINT

BINARY

BIT

BLOB

BLOB DATA TYPE

BOOLEAN

CHAR

CHAR BYTE

DATE

DATETIME

DEC

DECIMAL

DOUBLE

DOUBLE PRECISION

ENUM

FLOAT

INT

INTEGER

LONGBLOB

LONGTEXT

MEDIUMBLOB

MEDIUMINT

MEDIUMTEXT

SET DATA TYPE

SMALLINT

TEXT

TIME

TIMESTAMP

TINYBLOB

TINYINT

TINYTEXT

VARBINARY

VARCHAR

YEAR DATA TYPE

mysql>? int

Name:'INT'

Description:

INT[(M)][UNSIGNED] [ZEROFILL]

Anormal-size integer. The signed range is -2147483648 to 2147483647.

Theunsigned range is 0 to 4294967295.

URL:http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html

mysql>? show

Name:'SHOW'

Description:

SHOW hasmany forms that provide information about databases, tables,

columns,or status information about the server. This section describes

thosefollowing:

SHOWAUTHORS

SHOWCHARACTER SET [like_or_where]

SHOWCOLLATION [like_or_where]

SHOW[FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]

SHOWCONTRIBUTORS

SHOWCREATE DATABASE db_name

SHOWCREATE EVENT event_name

SHOWCREATE FUNCTION func_name

SHOWCREATE PROCEDURE proc_name

SHOWCREATE TABLE tbl_name

SHOWCREATE TRIGGER trigger_name

SHOWCREATE VIEW view_name

SHOWDATABASES [like_or_where]

SHOWENGINE engine_name {STATUS | MUTEX}

SHOW[STORAGE] ENGINES

SHOWERRORS [LIMIT [offset,] row_count]

SHOW[FULL] EVENTS

SHOWFUNCTION CODE func_name

SHOWFUNCTION STATUS [like_or_where]

SHOWGRANTS FOR user

SHOWINDEX FROM tbl_name [FROM db_name]

SHOWINNODB STATUS

SHOW OPENTABLES [FROM db_name] [like_or_where]

SHOWPLUGINS

SHOWPROCEDURE CODE proc_name

SHOWPROCEDURE STATUS [like_or_where]

SHOWPRIVILEGES

SHOW[FULL] PROCESSLIST

SHOWPROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]

SHOWPROFILES

SHOWSCHEDULER STATUS

SHOW[GLOBAL | SESSION] STATUS [like_or_where]

SHOWTABLE STATUS [FROM db_name] [like_or_where]

SHOWTABLES [FROM db_name] [like_or_where]

SHOWTRIGGERS [FROM db_name] [like_or_where]

SHOW[GLOBAL | SESSION] VARIABLES [like_or_where]

SHOWWARNINGS [LIMIT [offset,] row_count]

like_or_where:

LIKE 'pattern'

| WHERE expr

If thesyntax for a given SHOW statement includes a LIKE 'pattern'

part,'pattern' is a string that can contain the SQL "%" and "_"

wildcardcharacters. The pattern is useful for restricting statement

output tomatching values.

SeveralSHOW statements also accept a WHERE clause that provides more

flexibilityin specifying which rows to display. See

http://dev.mysql.com/doc/refman/5.1/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.1/en/show.html