mysql语句总结_mysql常用sql语句总结

sql语言简洁只有7个动词：SELECT , DROP, ALTER, CREATE,INSERT, UPDATE,DELETE；

获取表结构：Desc 表名；(show databases;tables;数据库和表)。

INSERT INTO 表名称 VALUES (值1, 值2,....), (值1, 值2,....), (值1, 值2,....);

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....);

update 表名 set

列1=新值1 ,

列2=新值2

Where expr;(不加影响所有行);

delete from 表名 where expr;

select 列1,列2,......列n from 表名 where expr;

select * from tmp where name is not null;mysql> select * from tmp where name is null;

当条件比较多时,应用括号来避免混乱。

and的优先级比or高。

内存里面的结果集筛选得使用having。

like 模糊匹配

% 通配任意字符

_ 通配单一字符

In:in (4,11); between: between 100 and 500;

NULL查询

select * from tmp where name is not null;

select * from tmp where name is null;

建表语法

create table 表名 (

列名1 列类型1 列1参数,

列名2 列类型2 列2参数,

....

...

列名n 列类型n 列n参数

)engine myisam/innodb/bdb charset utf8/gbk/latin1...

mysql> create table tmp(

-> id int,

-> name varchar(20)

-> )charset utf8 engine myisam;

列类型

数值型：整型( tinyint/smallint/int)、浮点型(Float/double/decimal)、定点型。Int(4byte,0-2^32-1)

属性：Unsigned,zerofill,M

字符串：char、varchar、text、bolb(字符串当二进制看)

日期时间类型：时间戳: timestamp

Zerofill属性默认决定列为unsigned;

列的默认值

1.NULL查询不便

2.NULL索引效果不高；

所以实用中避免列值为NULL；

如何避免，声明列 NOT NULL default 默认值；

例：

mysql> create table t9(

-> id int not null default 0,

-> name char(10) not null default ''

-> );

主键primary key 是能区分每一行的列。此列不重复

mysql> create table t11 (

-> id int primary key not null default 0,

-> name char(2) not null default ''

-> );

mysql> create table t12 (

-> id int,

-> name char(2),

-> primary key(id)

-> );

mysql> create table t3(

-> id tinyint primary key not null auto_increment);

表操作

列的删除增加与修改

Alter table 表名 add 列名称 列类型 列参数; [加的列在表的最后]

例: alter table m1 add birth date not null default '0000-00-00';

Alter table 表名 add 列名称 列类型 列参数 after 某列; [把新列加在某列后]

例: alter table m1 add gender char(1) not null default '' after username;

Alter table 表名 add 列名称 列类型 列参数 first; [把新列加在最前面]

例: alter table m1 add pid int not null default 0 first;

删除列

Alter table 表名 drop 列名;

修改列类型

Alter table 表名 modify 列名 新类型 新参数; (不能修改列名);

例:alter table m1 modify gender char(4) not null default '';

修改列名及列类型

Alter table 表名 change 旧列名 新列名 新类型 新参数;

例:alter table m1 change id uid int unsigned;

改表名

Rename table regist3 to reg3;

删除表

drop table 表名;

增加列

mysql> alter table t12 add high tinyint unsigned not null default 0;

表的增删改查

INSERT INTO 表名称 VALUES (值1, 值2,....), (值1, 值2,....), (值1, 值2,....);

INSERT INTO t1(field1,field2) VALUE(v001,v002);

update 表名 set 列1=新值1 ,列2=新值2 Where expr;(不加影响所有行)

delete from 表名 where expr;

select 列1,列2,......列n from 表名 where expr

视图(view)

创建视图

视图创建语法：

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;

Create [algorithm = UNDEFINED/MERGE/TEMPTABLE] view view_name as select statement;([]里面的可写可不写)

mysql> create view v1 as select goods_name,goods_id,shop_price from goods;

查看所有表： show tables;

查看表结构：desc 表名/视图名;

查看建表过程： show create table 表名;

查看建视图过程： show create view 视图名;

删除表： drop table 表名;

删除视图： drop view 视图名;

查看表详细信息： show table status;能看出是否是视图

查看某张表详细信息： show table status where name=’表名’;(/G竖着排列)

改表名： rename table oldname to newname;

清空表数据： truncate 表名;(相当于删除表,再重建两步操作)快

Delete from 表名;(DML操作,删除之后主键还是在自增长;)

查看带character变量：show variables(变量) like '%character%';

Set names utf8

索引(index)

索引种类

Key 普通索引;

Unique key 唯一索引;(主要起到约束的作用，比如注册邮箱；)

Primary key 主键索引;(一张表只能有一个;)

Fulltext 全文索引;(中文环境下无效,,要分词 索引,一般用第三方解决方案.如sphinx);

查看索引详情信息 explain select 语句；

mysql> create table t1 (

-> name char(10),

-> email char(20),

-> key name(name),

-> unique key email(email)

-> );

查看索引:show index from 表名; show create table 表名;

删除索引:alter tbale 表名 drop index 索引名; 或drop index 索引名 on 表名;

添加索引:alter table 表名 add [index/unique] 索引名(列名);

添加主键索引:Alter table 表名 add primary key (列名称);

删除主键索引:Alter table 表名 drop primary key;

事务

引擎Myisam是不支持事务的，innodb支持

事物开启:Start transaction;

事物结束: commit;

撤销:rollbacks;

子查询(嵌套查询)

三种子查询 ：where子查询、from子查询、exists子查询

where子查询

先执行where后的语句，得到内层结果。再执行外层

语法：select 字段 from 表 where 内层结果

eg:select goods_name,goods_id from goods where goods_id =(select max(goods_id) from goods);

from子查询

先执行from后的语句，得到内层结果。再执行外层

语法：select 字段 from (from子查询)

eg:select tmp.article_id,tmp.article_content,article_comments from ( select * from article order by articlecategory_id,article_comments desc ) as tmp group by tmp.articlecategory_id;

exists子查询

将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE 或 FALSE)来决定主查询的数据结果是否得以保留。

语法：SELECT … FROM table WHERE EXISTS (subquery)

eg:SELECT * FROM article WHERE EXISTS (SELECT * FROM user WHERE article.uid = user.uid)

连接查询

通常在项目中对表的查询都是关联多张表，多表查询就涉及到sql的内连接、外连接和交叉连接查询等。

内连接查询

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

外链接查询

外连接(全部并集)mysql不支持，oricle,sql server都可以。

外连接：外连接分为两种，一种是左连接(Left JOIN)、右连接(Right JOIN)、完整外部联接(FULL JOIN)。

eg:select goods_name,goods.cat_id,cat_name,shop_price from goods left join category on goods.cat_id =category.cat_id;

交叉联接查询

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

FROM 子句中的表或视图可通过内联接或完整外部联接按任意顺序指定；但是，用左或右向外联接指定表或视图时，表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息，请参见使用外联接。

联合查询

union查询

语法：sql1 union all sql2;

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SELECT column_name(s) FROM table_name1

UNION

SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

UNION ALL查询

SELECT column_name(s) FROM table_name1

UNION ALL

SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

select子句

五种子句 Where、group by、having、order by、limit

Where、group by、having、order by、limit运用的这个顺序不能变

1、where子句

like 模糊匹配

% 通配任意字符

_ 通配单一字符

In 有括号in (4,11); between between 100 and 500;

select * from tmp where name is not null;

select * from tmp where name is null;

2、group子句

语法：SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name;

group分组与统计函数:(很占资源，能不用就不用，有个排序的过程)

max()求最大

count()求行数

avg()平均值

min()求最小

sum()求总和；这是五个聚合函数。

3、Having子句

having筛选结果集,在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。内存里面的结果集得使用having。

HAVING 语法:

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value;

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。通常，你需要通过在HAVING子句中重复聚合函数表达式来引用聚合值，就如你在SELECT语句中做的那样。例如：

SELECT A COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2

4、order by子句 (排序)

语法：select goods_number,cat_id,shop_price from goods order by cat_id asc,shop_price desc;

降序：desc

升序：asc[默认]

ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序对记录进行排序。

5、limit子句

limit限制取出条目;

select goods_number,cat_id,shop_price from goods order by shop_price desclimit 5,9;

当5是0的时候，可以不写。这个是第6个后面的九个数

三种子查询

1、where子查询

把内层查询的结果当做是外层查询的比较条件

详细: select 字段 from 表 where 内层结果

执行: 先执行where后的语句，得到内层结果。再执行外层

select goods_name,goods_id from goods where goods_id =(select max(goods_id) from goods);

2、from子查询

把内层查询结果当成临时表，供外层sql再次查询

详细: select 字段 from (from子查询)

执行: 先执行from后的语句，得到内层结果。再执行外层

3、exists子查询

把外层查询结果，拿到内层。看内层的查询是否成立。

这个过程是从往外层的查询结果，一行一行代入内层中，判断是否成立。即exists后面的语句是否为真。如果为真，则显示代入的这一行。相反则不用。

内联接查询(交集)

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

默认内联接jion字符。

外连接(全部并集)mysql不支持，oricle,sql server都可以。

外连接：外连接分为两种，一种是左连接(Left JOIN)和右连接(Right JOIN)

语法：

SELECT column_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

JOIN: 如果表中有至少一个匹配，则返回行

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行

RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

FULL JOIN: 只要其中一个表中存在匹配，就返回行

引用两个表

我们可以通过引用两个表的方式，从两个表中获取数据：

谁订购了产品，并且他们订购了什么产品？

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons, Orders

WHERE Persons.Id_P = Orders.Id_P