以下為Navicat Premium界面中的代碼
--創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE YGGL
ON
(????name = 'YGGL_data',
????filename = '/Users/aubrey/Desktop/sql/YGGL.mdf',
????size = 10 mb,
????maxsize = 50 mb,
????filegrowth = 5%
)
log on
(????name = 'YGGL_log',
????filename = '/Users/aubrey/Desktop/sql/YGGL.ldf',
????size = 2 mb,
????maxsize = 5 mb,
????filegrowth = 1 mb
)
go
--中文亂碼的解決：中文編碼轉換
ALTER DATABASE [YGGL] COLLATE Chinese_PRC_CS_AS_WS
GO
--創(chuàng)建表
use YGGL
go
create table Employees
(????EmployeeID char(6) not null PRIMARY KEY,
????Name char(10) not null,
????Education char(4) not null,
????Birthday date not null,
????Sex bit not null default 1,
????WorkYear tinyint null,
????Address varchar(40) null,
????PhoneNumber char(12) null,
????DepartmentID char(3) not null
)
go
create table Departments
(????DepartmentID char(3) not null PRIMARY KEY,
????DepartmentName char(20) not null,
????note varchar(100) null
)
go
create table Salary
(????EmployeeID char(6) not null primary key,
????InCome float not null,
????OutCome float not null
)
go
—輸入數(shù)據(jù)
INSERT?INTO?Employees?VALUES('000001','王林','大專','1966-01-23',1,8,'中山路-1-508','83355668','2'),
????????????????????????????????????????????????????????('010008','伍容華','本科','1976-03-28',1,3,'北京東路-2','83321321','1'),
????????????????????????????????????????????????????????('020010','王向容','碩士','1982-12-09',1,2,'四牌樓-0-108','83792361','1'),
????????????????????????????????????????????????????????('020018','李麗','大專','1960-07-30',0,6,'中山東路-2','83413301','1'),
????????????????????????????????????????????????????????('102201','劉明','本科','1972-10-18',1,3,'虎距路-2','83606608','5'),
????????????????????????????????????????????????????????('102208','朱俊','碩士','1965-09-28',1,2,'牌樓巷-3-106','84708817','5'),
????????????????????????????????????????????????????????('108991','鐘敏','碩士','1979-08-10',0,4,'中山路-3-105','83346722','5'),
????????????????????????????????????????????????????????('111006','張石兵','本科','1974-10-01',1,1,'解放路-1-203','84563418','5'),
????????????????????????????????????????????????????????('210678','林濤','大專','1977-04-02',1,2,'中山北路-24-35','83467336','3'),
????????????????????????????????????????????????????????('302566','李玉珉','本科','1968-09-20',1,3,'熱河路-209-3','58765991','4'),
????????????????????????????????????????????????????????('308759','葉凡','本科','1978-11-18',1,2,'北京西路-7-52','83308901','4'),
????????????????????????????????????????????????????????('504209','陳林琳','大專','1969-09-03',0,5,'漢中路-4-12','84468158','4')
insert into Departments Values('1','財務部',NULL),
????????????????????????????????????????????????????????????('2','人力資源部',NULL),
????????????????????????????????????????????????????????????('3','經理辦公室',NULL),
????????????????????????????????????????????????????????????('4','研發(fā)部',NULL),
????????????????????????????????????????????????????????????('5','市場部',NULL)
insert into Salary Values('000001',2100.08,123.09),????
???????????????????????????????????????????????? ('010008',1582.62,88.03),
???????????????????????????????????????????????? ('102201',2569.88,185.65),
???????????????????????????????????????????????? ('111006',1987.01,79.58),
???????????????????????????????????????????????? ('504209',2066.15,108.0),
???????????????????????????????????????????????? ('302566',2980.7,210.2),
???????????????????????????????????????????????? ('108991',3259.98,281.52),
???????????????????????????????????????????????? ('020010',2860.0,198.0),
???????????????????????????????????????????????? ('020018',2347.68,180.0),
???????????????????????????????????????????????? ('308759',2531.98,199.08),
???????????????????????????????????????????????? ('210678',2240.0,121.0),
???????????????????????????????????????????????? ('102208',1980.0,100.0)
