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&#