Mysql常用语句小结

1、数据库相关语句

//1、说明：创建数据库CREATE DATABASE database-name//2、说明：删除数据库drop database dbname//3、说明：备份sql server--- 创建 备份数据的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'--- 开始 备份BACKUP DATABASE pubs TO testBack

2、表

1、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

例子：建立员工档案表
要求字段：员工员工编号，员工姓名，性别，工资，email，入职时间，部门。
合理选择数据类型及字段修饰符，要求有NOT NULL，auto_increment, primary key等。

DROP TABLE IF EXISTS `workers_info`;  CREATE TABLE `workers_info` (    `id` int(11) NOT NULL AUTO_INCREMENT,    `workername` varchar(20) NOT NULL,    `sex` enum(F,M,S),    `salary` int(11) DEFAULT '0',    `email`  varchar(30),    `EmployedDates`  date,    `department`  varchar(30),    PRIMARY KEY (`id`)  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8; 

2、添加，修改，删除表的列

ALTER TABLE：添加，修改，删除表的列，约束等表的定义。
查看列：desc 表名;
修改表名：alter table t_book rename to bbb;
添加列：alter table 表名 add column 列名 varchar(30);
删除列：alter table 表名 drop column 列名;
修改列名MySQL： alter table bbb change nnnnn hh int;
修改列名SQLServer：exec sp_rename't_student.name','nn','column';
修改列名Oracle：lter table bbb rename column nnnnn to hh int;
修改列属性：alter table t_book modify name varchar(22);

//新增qq和tel字段，要求tel字段位于email前，要求入职时间是最后一个字段ALTER TABLE workers_info ADD column tel varchar(15) after salary;//注意没有beforeALTER TABLE workers_info ADD column qq int; ALTER TABLE workers_info MODIFY EmployedDates date after qq;  //把email字段修改成mailboxALTER TABLE workers_info CHANGE email mailbox varchar(30);

3、增、删、改、查表

//删除新表drop table tabname//添加主键Alter table tabname add primary key(col)//删除主键Alter table tabname drop primary key(col)//向表里添加1条记录INSERT INTO workers_info (workername,salary,tel,mailbox,department,qq,EmployedDates) values('xing',10000,'1598232123','[e-mail]','yanfa',736019646,20121221);   //修改其中两条记录的tel和mailboxUPDATE workers_info SET mailbox = '[e-mail]',tel='1246543423' WHERE id = 13;  //删除数据DELETE FROM workers_info WHERE workername = 'Mike' //查看所添加记录select * from workers_info;//查看姓名和入职时间记录select workername,EmployedDates from workers_info;//查询入职时间在2003年以前的select * from workers_info where year(EmployedDates) < 2003;  //查询工资最高和最低的员工姓名select * from workers_info ORDER BY salary limit 1;  select * from workers_info ORDER BY salary desc limit 1;  //查询平均工资select avg(salary) from workers_info;  //统计男员工人数、女员工人数select count(男) from workers_info where sex="M";  select count(女) from workers_info where sex="F";  //按照入职时间先后进行排序，并显示前5位员工姓名select * from workers_info ORDER BY EmployedDates limit 5; 

几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count('') as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

//COUNT()函数//1、COUNT（*）对表中行的数目进行计数，不管列标中包含的是空值（NULL）还是非空值select count(*) as totalcount from workers_info;//2、COUNT（column)对特定的列中具有值的行进行计数，忽略NULL值select count(sex) as totalcount from workers_info;//AVG()函数//AVG()只能用来求特定数值列的平均值，为了获得多个列的平均值，必须使用多个AVG()函数//AVG()函数忽略列值为NULL的行select avg(salary) as avgNum from workers_info;//MAX（）函数//返回指定列中的最大值,忽略NULL值select max(salary) as maxNum from workers_info;//MIN（）函数//返回指定列的最小值select min(salary) as maxNum from workers_info;//SUM（）函数//返回指定列值的和select sum(salary) as sumPrice from workers_info;//SUM也可用来合计计算值//下面先列出要计算的数据SELECT SUM(salary*id) AS salary_id FROM workers_info   WHERE id = 1;//聚集不同的值，关键字DISTINCT//对于SUM（），MAX（），MIN（），AVG（），COUNT（），默认的参数为ALL，如果要计算只包含不同的值，//需指定DISTINCT参数select sum(DISTINCT salary) as sumPrice from workers_info;

4、order by（排序）、group by（分组）

order by: 从英文里理解就是行的排序方式，默认的为升序。 order by 后面必须列出排序的字段名，可以是多个字段名。ORDER BY 子句中的列必须包含在聚合函数或 GROUP BY 子句中。 order by stafferId ASC（升）, monthId DESC（降）

group by： 从英文里理解就是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。

什么是“聚合函数”？
像sum()、count()、avg()等都是“聚合函数”使用group by 的目的就是要将数据分类汇总。

一般如：
select 单位名称,count(职工id),sum(职工工资) form [某表]
group by 单位名称
这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。

在sql命令格式使用的先后顺序上，group by 先于 order by。

注：排开聚合函数不说 select 后面的列+order by 后面的列 必须在group by 里面。也就是说 select 和 order by 后面的列是 group by 列的子集。而 select 和 order by 之间是没有什么瓜葛的。

mysql分组取每组前几条记录(排序) 

CREATE TABLE `mygoods` (    `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT,    `cat_id` int(11) NOT NULL DEFAULT '0',    `price` tinyint(3) NOT NULL DEFAULT '0',    `status` tinyint(3) DEFAULT '1',    PRIMARY KEY (`goods_id`),    KEY `icatid` (`cat_id`)  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;  表mygoods为商品表，cat_id为分类id，goods_id为商品id，status为商品当前的状态位（1:有效，0:无效）。1、每个分类找出价格最高的有效的两个商品（正确）mysql> select a.*     -> from mygoods a     -> where (select count(*) from mygoods     -> where cat_id = a.cat_id and price > a.price and status=1  ) <2     -> and status=1     -> order by a.cat_id,a.price desc ;3.每个分类找出价格最高的有效的两个商品（正确）mysql> select a.*     -> from mygoods a     -> left join mygoods b     -> on a.cat_id = b.cat_id and a.price < b.price and b.status=1    -> where a.status=1    -> group by a.goods_id,a.cat_id,a.price    -> having count(b.goods_id) < 2    -> order by a.cat_id,a.price desc;

5、外键、级联

create table blog_article(  article_Id int unsigned not null  auto_increment,  article_title varchar(20) not null unique,  article_content longtext not null,  article_date datetime not null,  article_readTime int unsigned not null default 0,  user_Name char(15) not null,  category_Name char(18) not null,  primary key(article_Id),  foreign key(user_Name) references blog_user(user_Name) on delete cascade on update cascade,  foreign key(category_Name) references blog_category(category_Name) on delete cascade on update cascade)engine=innodb default charset=utf8 auto_increment=1;

6、时间范围及limit

select count(*) from tablename where create_date between '1900-1-1' and '1999-12-31' FROM_UNIXSTAMP()  和 UNIX_TIMESTAMP()函数mysql>SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d' )  ->20071120mysql> SELECT UNIX_TIMESTAMP('2009-08-06') ;->1249488000//选出2010-01-01到2013-12-31入职的职工select * from workers_info where EmployedDates between '2010-01-01' and '2013-12-31';select * from workers_info where EmployedDates between '20100101' and '20131231';select * from workers_info where UNIX_TIMESTAMP(EmployedDates)<UNIX_TIMESTAMP('2013-12-31') andUNIX_TIMESTAMP(EmployedDates)>UNIX_TIMESTAMP('2010-01-01');//选出最近一个月内入职的员工select * from workers_info where  to_days(now())-to_days(EmployedDates) < 30;

LIMIT：

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢?不用担心，mysql已经为我们提供了这样一个功能。

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，

第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

这是两个参数，第一个是偏移量，第二个是数目
select * from employee limit 3, 7; // 返回4-11行
select * from employee limit 3,1; // 返回第4行

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

一个参数
select * from employee limit 3; // 返回前3行 ，换句话说，LIMIT n 等价于 LIMIT 0,n。

7、索引

1.添加PRIMARY KEY（主键索引） mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引 mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) 查询索引SHOW INDEX FROM table_name;删除索引drop index index_name on table_name ;alter table table_name drop index index_name ;alter table table_name drop primary key ;其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。

唯一索引:唯一索引不允许两行具有相同的索引值。

主键索引:
主键索引是唯一索引的特殊类型。数据库表通常有一列或列组合，其值用来唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时，它还允许快速访问数据。

它们的一些比较：
(1)对于主健/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；
(2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；
(3)主健可作外健，唯一索引不可；
(4)主健不可为空，唯一索引可；
(5)主健也可是多个字段的组合；

主键与唯一索引不同的是：
a.有not null属性；
b.每个表只能有一个。

8、视图

1.什么是视图？
视图是由查询结果形成的一张虚拟表。

2.什么时候要用到视图？
如果某个查询结果出现的非常频繁，也就是，要经常拿这个查询结果来做子查询

3.视图的创建语法：
create view 视图名 as select 语句;

4.使用视图有什么好处呢？
①简化查询语句

比如：有一张商品表，我们经常要查每个栏目下商品的平均价格select cat_id,avg(shop_price) from goods gropy by cat_id;
这时候我们就可以创建一张视图：create view avgPrice as select cat_id,avg(shop_price) from goods gropy by cat_id;
创建完，以后我们要查每个栏目的平均价格时，只要这么写select * from avgPrice;就可以了。

②可以进行权限控制

把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据列

比如我们的goods商品表，我们不想让别人看到我们的销售价格，这时候我们就可以把查看商品表的权限封闭，创建一张视图
create view showGoods as select goods_id,goods_name from goods;不出现销售价格列就可以了。

③大数据分表时可以用到
比如表的行数据超过200万行时，速度就会变慢，可以把一张表的数据拆成4张表来存放
News表  newsid  1,2,3,4...  news1,news2,news3,news4表
把一张表的数据分散到4张表里，分散的方法有很多， 最常用的是id取模来计算 id%4+1=[1,2,3,4]...
还可以用视图，把四张表形成一张视图
create view news as select * from news1 union select * from news2 union ...

5.视图的修改
 alter view 视图名 as select 语句

6.视图与表的关系
视图是表的查询结果，自然表的数据变了，会影响视图的结果

7.那么视图改变了会影响到表吗？
①视图的增删改也会影响表； ②但视图并不总是能增删改的；
视图的数据与表的数据一一对应时可以修改； 对于视图的insert还应注意：视图必须包含表中没有默认值的列。

9、存储过程简介(参考来源：存储过程详解)
什么是存储过程：存储过程可以说是一个记录集吧，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。

存储过程的好处：
1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。
2.一个存储过程在程序在网络中交互时可以替代大堆的T-SQL语句，所以也能降低网络的通信量，提高通信速率。
3.通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

//同时具有返回值、输入参数、输出参数的存储过程 -------------创建名为GetUserAccount的存储过程----------------create Procedure GetUserAccountRe1@UserName nchar(20),@UserID int outputasif(@UserName>5)select @UserID=COUNT(*) from UserAccount where UserID>25elseset @UserID=1000return @@rowcountgo-------------执行上面的存储过程----------------exec GetUserAccountRe1 '7',null

10、几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。

B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在TABLE1中但不在TABLE2中的行并消除所有重复行而派生出一个结果表。当ALL随EXCEPT一起使用时 (EXCEPT ALL)，不消除重复行。

C： INTERSECT 运算符
INTERSECT 运算符通过只包括TABLE1和TABLE2中都有的行并消除所有重复行而派生出一个结果表。当ALL随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

11、使用外连接
A、left （outer） join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right （outer） join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full/cross （outer） join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。