mysql常用sql语句总结

sql语言简洁只有7个动词:SELECT , DROPALTERCREATE, INSERTUPDATE ,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

字符串:charvarchartextbolb(字符串当二进制看)

日期时间类型:时间戳: 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

 

此条目发表在程序设计分类目录,贴了标签。将固定链接加入收藏夹。