Mysql常用语句小结

1、数据库相关语句

//1、说明：创建数据库
CREATE DATABASE database-name

//2、说明：删除数据库
drop database dbname

//3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC 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;//注意没有before

ALTER TABLE workers_info ADD column qq int; 

ALTER TABLE workers_info MODIFY EmployedDates date after qq;  

//把email字段修改成mailbox
ALTER 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','xing@qq.com','yanfa',736019646,20121221);  
 
//修改其中两条记录的tel和mailbox
UPDATE workers_info SET mailbox = 'haha@qq.com',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&#