sql随笔1

CREATE TABLE T_Employee (
    FNumber VARCHAR (20),
    FName VARCHAR (20),
    FAge INT,
    FSalary DECIMAL (10, 2),
    PRIMARY KEY (FNumber)
)


INSERT  INTO  T_Employee(FNumber,FName,FAge,FSalary)
VALUES('DEV001','Tom',25,8300);
INSERT  INTO  T_Employee(FNumber,FName,FAge,FSalary)
VALUES('DEV002','Jerry',28,2300.80);
INSERT  INTO  T_Employee(FNumber,FName,FAge,FSalary)
VALUES('SALES001','John',23,5000);
INSERT  INTO  T_Employee(FNumber,FName,FAge,FSalary)
VALUES('SALES002','Kerry',28,6200);
INSERT  INTO  T_Employee(FNumber,FName,FAge,FSalary)
VALUES('SALES003','Stone',22,1200);
INSERT  INTO  T_Employee(FNumber,FName,FAge,FSalary)
VALUES('HR001','Jane',23,2200.88);
INSERT  INTO  T_Employee(FNumber,FName,FAge,FSalary)
VALUES('HR002','Tina',25,5200.36);
INSERT INTO T_Employee(FNumber,FName,FAge,FSalary) VALUES('IT001','Smith',28,3900);




SELECT * FROM T_Employee


#MSSQL SERVER中的用法 ^代表取反   查询：FName 中不含有 S J
SELECT * FROM T_Employee
WHERE FName LIKE '[^SJ]%'
#MYSQL中的用法
SELECT * FROM T_Employee
WHERE NOT(FName LIKE 'S%') AND NOT(FName LIKE 'J%')

SELECT * FROM T_Employee
WHERE FAgem<>22 AND FSALARY<=2000

SELECT * FROM T_Employee
WHERE FAge!=22 AND FSALARY>=2000

SELECT * FROM T_Employee
WHERE NOT(FAge=22) AND NOT(FSALARY<2000)



ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);
ALTER TABLE T_Employee ADD FDepartment VARCHAR(20);



UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Development'
WHERE FNumber='DEV001';
UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='Development'
WHERE FNumber='DEV002';
UPDATE  T_Employee  SET
FSubCompany='Beijing',FDepartment='HumanResource'
WHERE FNumber='HR001';
UPDATE  T_Employee  SET
FSubCompany='Beijing',FDepartment='HumanResource'
WHERE FNumber='HR002';
UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='InfoTech'
WHERE FNumber='IT001';
UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='InfoTech'
WHERE FNumber='IT002';
UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Sales'
WHERE FNumber='SALES001';
UPDATE T_Employee SET FSubCompany='Beijing',FDepartment='Sales'
WHERE FNumber='SALES002';
UPDATE T_Employee SET FSubCompany='ShenZhen',FDepartment='Sales'
WHERE FNumber='SALES003';










SELECT FAge FROM T_Employee
GROUP BY FAge

SELECT FAge FROM T_Employee
WHERE FSubCompany = 'Beijing'
GROUP BY FAge

SELECT FSubCompany,FDepartment FROM T_Employee
GROUP BY FSubCompany,FDepartment


SELECT FName, LENGTH(FName) AS namelength FROM T_Employee
WHERE FName IS NOT NULL


SELECT FName, SUBSTRING(FName,2,3) FROM T_Employee
WHERE FName IS NOT NULL


CREATE  TABLE  T_TempEmployee  (FIdCardNumber  VARCHAR(20),FName
VARCHAR(20),FAge INT ,PRIMARY KEY (FIdCardNumber))

INSERT  INTO  T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890121','Sarani',33);
INSERT  INTO  T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890122','Tom',26);
INSERT  INTO  T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890123','Yalaha',38);
INSERT  INTO  T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890124','Tina',26);
INSERT  INTO  T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890125','Konkaya',29);
INSERT  INTO  T_TempEmployee(FIdCardNumber,FName,FAge)
VALUES('1234567890126','Fotifa',46);

SELECT FNumber,FName,FAge FROM T_Employee
UNION
SELECT FIdCardNumber,FName,FAge FROM T_TempEmployee

CREATE TABLE T_Person (FIdNumber VARCHAR(20),
FName VARCHAR(20),FBirthDay DATETIME,
FRegDay DATETIME,FWeight DECIMAL(10,2))


INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789120','Tom','1981-03-22','1998-05-01',56.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789121','Jim','1987-01-18','1999-08-21',36.17);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789122','Lily','1987-11-08','2001-09-18',40.33);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789123','Kelly','1982-07-12','2000-03-01',46.23);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789124','Sam','1983-02-16','1998-05-01',48.68);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789125','Kerry','1984-08-07','1999-03-01',66.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789126','Smith','1980-01-09','2002-09-23',51.28);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789127','BillGates','1972-07-18','1995-06-19',60.32);


SELECT FWeight - 50,ABS(FWeight - 50) , ABS(-5.38) FROM T_Person


SELECT POWER(56.67 ,-0.5)


SELECT
    FWeight,
    POWER(FWeight ,- 0.5),
    POWER(FWeight, 2),
    POWER(FWeight, 3),
    POWER(FWeight, 4)
FROM
    T_Person


SELECT SQRT(100)


SELECT
    FWeight,
    SQRT(FWeight)
FROM
    T_Person


SELECT RAND(100)

SELECT RAND()


SELECT FName,LTRIM(FName),LTRIM(' abc ') FROM T_Person
SELECT FName,RTRIM(FName),RTRIM(' abc ') FROM T_Person


SELECT SUBSTRING('abcdef111',2,3)

SELECT REPLACE(' abc 123 wpf',' ','') , REPLACE(' ccw enet wcf f',' ','')

SELECT ASCII('a') , ASCII('abc')


SELECT
FBirthDay,
DATE_FORMAT(FBirthDay,'%y-%M %D %W') AS bd,
FRegDay,
DATE_FORMAT(FRegDay,'%Y年%m月%e日') AS rd
FROM T_Person

UPDATE T_Person SET FBirthDay=null WHERE FName='Kerry';
UPDATE T_Person SET FBirthDay=null AND FRegDay=null WHERE FName='Smith'

SELECT * FROM T_Person

#处理空值
SELECT FName,FBirthDay,FRegDay,
COALESCE(FBirthDay,'2008-08-08') AS ImportDay
FROM T_Person

#处理空值的简化版本
SELECT FBirthDay,FRegDay,
IFNULL(FBirthDay,'2008-08-08') AS ImportDay
FROM T_Person


#流程控制函数CASE
SELECT
FName,
(CASE FName
WHEN 'Tom' THEN 'GoodBoy'
WHEN 'Lily' THEN 'GoodGirl'
WHEN 'Sam' THEN 'BadBoy'
WHEN 'Kerry' THEN 'BadGirl'
ELSE 'Normal'
END) as isgood
FROM T_Person


#IF判断函数
SELECT
FName,
FWeight,
IF(FWeight>50,'太胖','正常') AS ISTooFat
FROM T_Person

#填充函数
SELECT FName,LPAD(FName,5,'*'),RPAD(FName,5,'*')
FROM T_Person

#重复函数
SELECT REPEAT('*',5), REPEAT('OK',3)

#字符串颠倒
SELECT FName, REVERSE(FName)
FROM T_Person

#case函数加强版本   索引-->位置
SELECT
ELT(2, 'ej', 'Heja', 'hej', 'foo'),
ELT(3, 'ej', 'Heja', 'hej', 'foo')

#case函数加强版本  位置-->索引
SELECT FIELD('vip','normal','member','vip') as f1,
FIELD('foo', 'Hej', 'ej', 'Heja', 'hej', 'foo') as f2

#当前数据库名
SELECT DATABASE()

# 以一个字符串形式返回MySQL 服务器的版本
SELECT VERSION()

#返回当前 MySQL 用户名
SELECT USER()

#获得一个随机数
SELECT UUID()

#md5加密
SELECT MD5("hello world")

#SHA加密
SELECT SHA("hello world")

#SHA1加密
SELECT SHA1("hello world")

drop TABLE T_Person

#第六章


#创建表
#T_Person 为记录人员信息的数据表，
#其中字段 FNumber 为人员的编号，FName 为人员姓名，FAge 为人员年龄。
CREATE TABLE T_Person (
    FNumber VARCHAR (20),
    FName VARCHAR (20),
    FAge INT
)

#创建索引
CREATE INDEX idx_person_nameage ON T_Person(FName,FAge)

#删除索引
DROP INDEX idx_person_nameage ON T_Person;

#非空约束


CREATE TABLE T_Person1 (
    FNumber VARCHAR (20) NOT NULL,
    FName VARCHAR (20),
    FAge INT
)

#测试非空约束
INSERT INTO T_Person1 (FNumber, FName, FAge) VALUES ( NULL , 'kingchou', 20)  #正确
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( '1' , 'kingchou', 20)    #错误


#唯一约束
CREATE TABLE T_Person2 (
    FNumber VARCHAR (20) UNIQUE,
    FName VARCHAR (20),
    FAge INT
)

#测试唯一约束
INSERT INTO T_Person2 (FNumber, FName, FAge)
VALUES ( '1' , 'kingchou', 20);
INSERT INTO T_Person2 (FNumber, FName, FAge)
VALUES ( '2' , 'stef', 22);
INSERT INTO T_Person2 (FNumber, FName, FAge)
VALUES ( '3' , 'long', 26);
INSERT INTO T_Person2 (FNumber, FName, FAge)
VALUES ( '4' , 'yangzk', 27);
INSERT INTO T_Person2 (FNumber, FName, FAge)
VALUES ( '5' , 'beansoft', 26);
#不唯一数据无法插入 Duplicate（重复） entry（记录） '5' for key 'FNumber'
INSERT INTO T_Person2 (FNumber, FName, FAge)
VALUES ( '5' , 'beansoft', 26);

#删除测试表
DROP TABLE T_Person
DROP TABLE T_Person1
DROP TABLE T_Person2

#定义复合唯一约束
CREATE TABLE T_Person (FNumber VARCHAR(20),
FDepartmentNumber VARCHAR(20),
FName VARCHAR(20),FAge INT,
CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))

#测试复合唯一约束
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)
VALUES ( '1' , 'dev001','kingchou', 20);
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)
VALUES ( '2' , 'dev001', 'stef', 22);
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)
VALUES ( '1' , 'sales001', 'long', 26);
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)
VALUES ( '2' , 'sales001', 'yangzk', 27);
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)
VALUES ( '3' , 'sales001', 'beansoft', 26);
#错误数据（同时违反定义的多个列时才算失败）因为 FNumber 等于'2'且 FDepartmentNumber 等于'sales001'的记录在表中已经存
#在了，所以 在数据库中执行此 SQL 语句后数据库会报出下面错误信息：
#违反了 UNIQUE KEY 约束 'unic_dep_num'。不能在对象 'dbo.T_Person' 中插入重复键。
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)
VALUES ( '2' , 'sales001', 'daxia', 30);

#删除测试表
DROP TABLE T_Person

#可以在一个表中添加多个复合 唯一约束，只要为它们指定不同的名称即可
CREATE TABLE T_Person (FNumber VARCHAR(20),
FDepartmentNumber VARCHAR(20),
FName VARCHAR(20),FAge INT,
CONSTRAINT unic_1 UNIQUE(FNumber,FDepartmentNumber) ,
CONSTRAINT unic_2 UNIQUE( FDepartmentNumber , FName ))


#检查约束
CREATE TABLE T_Person (
FNumber VARCHAR(20),FName VARCHAR(20),
FAge INT CHECK(FAge >0),
FWorkYear INT CHECK(FWorkYear>0))
#测试检查约束
INSERT INTO T_Person(FNumber, FName, FAge, FWorkYear)
VALUES('001','John',25,-3)