数据库常用知识点

• SQL的两部分:
  • 1 Data Definition Language（DDL）=数据定义语言
  • 2 Data Manipulation Language（DML）=数据操作语言
• SQL中最重要的 DDL语句：
  • CREATE DATABASE - 创建新的数据库
  • ALTER DATABASE - 修改数据库
  • CREATE TABLE - 创建新的数据库表
  • ALTER TABLE - 变更（改变）数据库表
  • DROP TABLE - 删除表
  • CREATE INDEX - 创建索引（搜索键）
  • DROP INDEX - 删除索引
  • SQL的数据操作语言（DML）:
  • INSERT INTO - 向数据库表中插入数据
  • SELECT - 从数据库表中获取数据
  • UPDATE - 更新数据库表中的数据
  • DELETE - 从数据库表中删除数据
• 数据类型：
  • Int/Integer/Smallint/Tinyint---- 整数位
  • CHAR----（知道固定长度 0-255）
  • VARCHAR----（经常变化的字段0-80000）
  • Date----容纳时间 （yyyymmdd）
  • FLOAT----数常用于有小数点的单位，如：价格、分数）
  • Decimal----容纳带有小数的数字
  • Between----在某个范围内
  • Like----搜索某种模式
• 约束：
  • NOT NULL----数据类型的约束名，（不能为空）
  • UNIQUE----数据类型的约束名，唯一（如品牌的系列）
  • PRIMARY KEY----PK（主键）每个表中‘必有’且‘仅有’一个PK列且不能   包含NULL
• 运算符:
  • =等于，
  • <>不等于，
  • >大于，
  • <小于，
  • >=大于等于，
  • <=小于等于，
  • （BETWEEN在某个范围内，LIKE搜索某种模式） 值------查看某一个特定条件的内容。
• 步骤：
  • 新建一个数据库: 
    • CREATE DATABASE 数据库名称;
  • USE 数据库名称 连接到数据库
  • 新建一个表:    
    • CREATE TABLE 表名称(
      • 列名称1 数据类型(**) 约束,
      • 列名称2 数据类型(**) 约束，
      • 列名称3 数据类型(**) 约束);
• 插入第一行值;  
  • INSERT INTO 表名称 VALUES('值1'，'值2'，···);  列名表名和数字不加引号/字符串要加'' 。
• 插入新的列和行 ：
  • INSERT INTO 表名称(列1，列2，···) VALUES('值1'，   '值2'，···);
• 挑选表里所有的内容 ：
  • SELECT * FROM 表名称;
• 挑选表里单个列：
  • SELECT 列1 FROM 表名称;
• 挑选表里多个列：    
  • SELECT 列1,列2,列3 FROM 表名称;
• 变改列的别名称:
  • SELECT column_name AS alias_name FROMtable_name;
  • Select 列名 AS 改名 From 表名称;
• 变改多个列的别名称:     
  • Select 列名1 AS 改名1，列名2 AS 改名2 From 表名称;      
  • 或者   Select 列名1 改名1,列名2 改名2 From 表名称;
• 更改表的别名称:
  • SELECT column_name(s) FROM table_name AS alias_name;
  • Select 列名 From 表名称 AS 新表名;
• 更改多个表的别名称:   
  • Select 新表名.列名1,新表名.列名2 From 表名称 AS 新表名;            
  • 或者   Select 新表名.列名1,新表名.列名2 From 表名称 新表名;
• 混合更改:      
  • SELECT 原列名 AS 新列名 FROM 原表名 AS 新表名
• 有条件的从表中选取一列的数据:
  • SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
• 有条件的从表中选取所有数据：
  • SELSCT * FROM 表名称 WHERE 列 运算符 '值';
• 在已有的表中：                                           修改时有的列名要加 []。
  • ALTER TABLE----在已有的表中修改列
  • ALTER COLUMN----在已有的列中修改值
  • ADD----添加       DROP----删除    UPDATE----修改、更新       SET----设置
  • 添加新的列 
    • ALTER  TABLE  table_name  ADD  column_name  data-type  [constraint];
    • ALTER TABLE 表名称 ADD 列名称 数据类型 约束;
  • 修改列（不包括列名）的语法：
    • ALTER  TABLE  table_name  ALTER  COLUMN column_name  datatype;
    • ALTER  TABLE 表名称 ALTER  COLUMN 列名称 数据类型 约束;
  • 更改列的名称：
    •  EXEC SP_RENAME '表名.原列名','新列名','COLUMN';
  • 给‘列名称’增加约束:
    • ALTER  TABLE  table_name ADD  CONSTRAINT  constraint_name UNIQUE column_name;
    • ALTER  TABLE 表名 ADD CONSTRAINT[谁便取别名] UNIQUE(列名称);
    • ALTER  TABLE 表名 ADD CONSTRAINT [约束别名] 约束名 (列名);
  • 删除列的语法：
    • ALTER  TABLE  table_name  DROP  COLUMN  column_name;
  • 删除已有表中的某个列：      
    • ALTER TABLE 表名称 DROP COLUMN 列名称;
  • 删除已有表中某个列的约束：  
    • ALTER TABLE 表名称 DROP CONSTRAINT [约束别名];
  • 为某一列修改、更新数据 ：
    • UPDATE 表名 SET 列名 = 新数据;
  • 为某一列的某一行修改、更新数据：
    • UPDATE  表名称  SET  列名称 = 新值 WHERE  列名称 = 某值;
    • UPDATE 表名 SET 列名 = 新数据 WHERE 列名称 = '某行';
  • 为某一行修改、更新数据：
    • UPDATE  表名称  SET  列名称1 = 新值1, 列名称n = 新值n WHERE  列名称 = 某值   
    • UPDATE 表名 SET 列名1 ='新数据1', 列名n ='新数据n' WHERE 列名称 = '某行';
  • 返回唯一不同的值：
    •  SELECT  DISTINCT 列名称 FROM 表名称;
• 第一个条件和第二个条件都成立:
  • SEECT * FROM 表名称 WHERE 列2='值2' AND 列1='值1';
• 第一个条件和第二个条件中只要有一个成立:
  • SEECT * FROM 表名称 WHERE 列2='值2' OR 列1='值1';
• AND 和 OR 结合:  
  •  SELECT * FROM 表名称(列2='值2' OR 列2='值3') AND 列1='值1';
• 以字母顺序对结果集进行排序:
  • 升序 SELECT 列名 FROM 表名 ORDER BY 列名;     ASC是升序的意思，可以不加
  • 降序 SELECT 列名 FROM 表名 ORDER BY 列名DESC;
• 规定要返回的记录的数目：
  • SELECT TOP number/percent 列名 FROM 表名;
• 规定要返回的记录的数目语法：
  • SELECT  TOP number|percent  column_name(s)  FROM  table_name;
• 选取介于两个列之间的数据范围：
  • SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 ANDvalue2;
• 删除表中的某行或所有行:
  • DELETE  FROM  表名称  WHERE  列名称  =  值
• 另一种删除所有表数据（行），但不破坏表结构的方式:
  • TRUNCATE  TABLE  表名称;
• 编号从1递增         
  • 实例1：
    • CREATE  TABLE  Auto_Increment_Table
      • (  Id  INT  PRIMARY  KEY  IDENTITY ,
      • 姓名  VARCHAR (100)  NOT  NULL);
• 编号从1000开始递增     
  • 实例2：       （1000，n）
    • CREATE  TABLE  Employee
    • (  Id  INT  PRIMARY  KEY  IDENTITY(1000, 1) ,
    • 姓名  VARCHAR (100)  NOT  NULL）;
• 要找的东西“像”什么：
  • SELECT  column_name(s) FROM  table_name WHERE  column_name LIKE pattern;
  • 使用通配符“_”查询品牌等于迪奥的各种实例：
    • SELECT * FROM Perfume WHERE Brand LIKE 'Dio_';
    • SELECT * FROM Perfume WHERE Brand LIKE 'D_o_';
    • SELECT * FROM Perfume WHERE Brand LIKE 'D_ _r';
  • 使用通配符“[charlist]”查询香水品牌的各种实例：
    • SELECT * FROM Perfume WHERE Brand LIKE '[DC]%';
    • SELECT * FROM Perfume WHERE Brand LIKE '[CD]%';
    • SELECT * FROM Perfume WHERE Brand LIKE '%[DC]%'
    • SELECT * FROM Perfume WHERE Brand LIKE '%[C]%';
  • 使用通配符"[^charlist]"查询不以"C"或"D"字母开头的香水品牌：
    • SELECT * FROM Perfume WHERE Brand LIKE '[^CD]%';
    • SELECT * FROM Perfume WHERE Brand LIKE '[^DC]%';
    • SELECT * FROM Perfume WHERE Brand LIKE '[^cD]%';
• IN的操作符：
  • SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
• 实例1我想要限制考生的年龄在18~30之间（含），请问这张表怎么做.只允许输入“男”或者“女”
  • CREATE  TABLE  CHECK_AGE_AND_SEX_MANNER1
    • (
    • 姓名  VARCHAR (100) ,
    • 年龄  INT  CHECK  ( 年龄 >= 18 AND 年龄 <= 30 ), -- 这里不能用OR
    • 性别  CHAR(4)  CHECK  性别 = '男' OR 性别 = '女') -- 这里不能用AND
    • );
  • 或者   CREATE  TABLE  CHECK_AGE_AND_SEX_MANNER2
    • (
    • 姓名  VARCHAR (100) ,
    • 年龄  INT,
    • 性别  CHAR(4),
    • CONSTRAINT  check_age  CHECK ( 年龄 >= 18 AND 年龄 <= 30 ),
    • CONSTRAINT check_sex  CHECK ( 性别 = '男' OR 性别 = '女' )
    • );
• 实例3我们学校软件测试精品课的考试时间已经确定在2018年5月20日，为了图个方便，我能不能在做表时不再重复为每一个学员填入考试时间?
  • CREATE  TABLE  INSERT_DEFAULT_EXAM_DATE
    • (
    • Id  INT  PRIMARY KEY  IDENTITY ,
    • 姓名  VARCHAR(100)  NOT NULL ,
    • 考试时间  DATE  DEFAULT  '2018-05-20' );
• 在已有的表里：
  •  ALTER TABLE 表名称 ADD CONSTRAINT 列名称  DEFAULT ‘值’FOR 列名称;
• NULL 值：
  • SELECT * FROM 表名称 WHERE 列名称 IS NULL ;
  • SELECT * FROM 表名称 WHERE 列名称 IS NOT NULL ;
• 函数的服务对象一定是 列 ：
  • SELECT  function (列)  FROM 表 ；
• 平均值  
  • SELECT AVG(column_name)  FROM  table_name
• 最大值   
  • SELECT MAX(column_name) FROM table_name
• 最小值 
  • SELECT MIN(column_name) FROM table_name
• COUNT() 函数
  • SELECT  COUNT(*)  FROM  table_name
  • SELECT  COUNT(column_name)  FROM  table_name
  • COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
• SUM() 函数 ：  
  • SELECT  SUM(column_name)  FROM  table_name
• LEN 函数返回文本字段中值的长度：  
  • SELECT  LEN(column_name)  FROM  table_name
• 【有时候需要前置任务：换成小数SQL语句：
  • ALTER  TABLE  软件测试考试分数表_重制版 ADD  圆周率 FLOAT;
• ROUND() 函数用于把数值字段舍入为指定的小数位数：
  • SELECT ROUND(column_name,decimals) FROM table_name
  • 实例1：SELECT  ROUND(圆周率, 1) AS 改的别名 FROM table_name。
• 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY  KEY
• 前置任务：建立一张最最简单的班级表，SQL语句如下：
  • CREATE  TABLE  班级表
    • (
    • Class_Id  INT  PRIMARY KEY,
    • 班级  VARCHAR(10)  NOT NULL
    • );
  • 实例1（新建表时就添加外键约束）：
    • CREATE TABLE 学生表
      • (    Student_Id  INT  PRIMARY KEY,
      • 姓名  VARCHAR(10),
      • Student_Class_Id  INT  FOREIGN  KEY  REFERENCES 班级 表(Class_Id)  );
  • 实例2（新建表时，添加外键约束名或同时添加多个外键约束时使用）：
    • CREATE  TABLE  学生表
      • (   Student_Id  INT  PRIMARY  KEY,
        • 姓名  VARCHAR(10),
        • Student_Class_Id  INT,
        • CONSTRAINT  fk_class_and_student  FOREIGN KEY  (Student_Class_Id) REFERENCES  班级表(Class_Id));
  • 实例3（在表已经存在的情况下，添加外键约束）：
    • ALTER  TABLE  学生表  ADD  FOREIGN  KEY (Student_Class_Id)  REFERENCES  班级表(Class_Id);
  • 实例4（在表已经存在的情况下，添加外键约束名或同时添加多个外键约束时使用）：
    • ALTER  TABLE  学生表  ADD  CONSTRAINT fk_class_and_student  FOREIGN  KEY (Student_Class_Id) REFERENCES  班级表(Class_Id);
  • 实例5（撤销 FOREIGN KEY 约束）：
    • ALTER  TABLE  学生表  DROP  CONSTRAINT  fk_class_and_student;
• GROUP BY 语句 
  • SELECT column_name,  aggregate_function(column_name) FROM  table_name  WHERE  column_name  operator  valueGROUP  BY  column_name
  • 实例1：
    • SELECT  商场专柜,  SUM(销售价格)  AS  香水总销售额 FROM  商场专柜销售情况表GROUP  BY  商场专柜  HAVING  SUM(销售价格)  >  500 ORDER  BY  香水总销售额  DESC;
  • 实例2：
    • SELECT  商场专柜,  销售时间,  SUM(销售价格) AS  香水总销售额  FROM 商场专柜销售情况表 GROUP  BY  商场专柜,  销售时间  HAVING SUM(销售价格)  >  500  ORDER  BY  香水总销售额  DESC;
• JOIN 方式   例如
  • SELECT * FROM 香水信息表 JOIN 商场专柜销售情况表 ON 香水编号 = 香水编号_外键 ORDER BY Id_Perfume;
• LEFT JOIN 语法：
  • SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
  • 实例1：  SELECT * FROM 香水信息表 LEFT JOIN 商场专柜销售情况表 ON 香水编号 = 香水编号_外键;
• RIGHT JOIN  语法：
  • SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
  • 实例1： SELECT * FROM 商场专柜销售情况表 RIGHT JOIN 香水信息表 ON 香水编号_外键 = 香水编号;