发现有一些错误,本文已经废弃

数据库SQL操作

DDL 数据定义

1.库的基本操作

1.创建库
create database db1 [if not exists db1];    #判断库是否存在，不存在则创建
            
2.查看所有数据库(可在任意库中使用)
show databases;

3.修改数据库字符信息
alter database db1 character set utf8; 

4.删除数据库
drop database db1; 

5.进入库(可在任意库中使用)
use db1;

2.表的基本操作

1.创建表
create table student(
    id int AUTO_INCREMENT NOT NULL,
    name varchar(32) DEFAULT '小王子',
    age int NOT NULL,
    score double(4),
    birthday date UNIQUE,
    insert_time timestamp
    Sex CHAR(1) CHECK (Sex IN ('M', 'F')),
    PRIMARY KEY (id),
    FOREIGN KEY (birthday_time) REFERENCES Persons(birthday_time)
);
#字段类型,名称,长度,约束

2.修改表
ALTER TABLE <表>    ADD  COLUMN  <新列名> <数据类型>             #加列
                     ADD  CONSTRAINT <完整性约束名>   约束        #加约束    
                    DROP COLUMN <列名>[CASCADE|RESTRICT]         #删列
                     DROP CONSTRAINT <完整性约束名> [CASCADE|RESTRICT] #删约束
                    RENAME COLUMN <列名> TO <新列名>               #重命名
                    ALTER  COLUMN <列名> TYPE <数据类型>           #改类型


3.删除表
DROP TABLE 表名［RESTRICT| CASCADE］；

RESTRICT：限制(保守)。
如果存在依赖该表的对象，则此表不能被删除
CASCADE：没有限制(连坐)。
在删除基本表的同时，相关的依赖对象一起删除 



4.建立索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>    ON <表名>(<列名>[<次序>]…)；        
索引可以建立在多列上，升序：ASC，降序：DESC。默认ASC
UNIQUE表明此索引的每一个索引值只对应唯一的数据记录
CLUSTER表示要建立的索引是聚簇索引
create unique index A on SC(SNO);

一张表只能创建一个聚簇索引。

约束类型
AUTO_INCREMENT          #自增,只能是数字,必加NOT NULL且一般作为Pkey

DEFAULT "xxx"    #默认值,当没有插入该列时,默认

UNIQUE          #唯一

NOT NULL         #非空即不是NULL

CHECK(条件)       #检查,可追加在col后面,但这样条件必须只能由col,也可单独成行
#int col CHECK(col IN ('x1','x2'...)),    #追加
#CHECK(col1 > 2 AND col2 <100),    #单独成行
#条件可以是 IN选择,<>大小,支持AND,OR,不支持通配符

PRIMARY KEY (col)        #设为主键,唯一非空,可追加在col后面也可单独成行

FOREIGN KEY (col1) REFERENCES tb(col2)    #外键,将tb表的col2作为本表col1上的外键,插入的值必须是col2上存在的 

表内元素的数据类型与约束
数字    
    int,smallint,bigint  #范围有差别  
    
小数
    numeric(p,d)  精度p,小数点后有d位(数字总数可以小于p,但d位小数必须有)   #等价于decimal(p,d)  dec(p,d)
    精度 float(p,d)<double(p,d)<dec(p,d)  #虽然指定了精度与小数位,但实际存储还是因为位数有误差      
    
时间
     年月日  date
     年月日时分秒  datetime
     时区时间timestamp  #适用于经常插入或更新 
     时分秒 time
     年份  year
     
字符串
    固定长度的字符串    char(n)    占用n字符
    可变长度的字符串    varchar(n)    占用实际字符+一部分存储长度
    较长的文本数据        text    大小取决于数据库
    存储固定长度的二进制数据    binary
    可变长度的二进制数据  varbinary  #存图片,音频,视频

DML 数据库操纵

1.插入数据insert

1.指定列添加
insert into 表名(列名1,列名2) values(值1,值2);
2.全列添加(不指定列名)
insert into 表名 values(值1,值2,...值n);
3.导入表
insert into  新表  select 语句(原表)

2.删除数据delete

1.删除表中某行记录
delete from 表名 where  列名=值;
2.删除表中所有记录(未删除属性列)
delete from 表名;
3.删除表中所有记录
truncate table 表名;
#删除列需要使用DDL

删除多个表的有关数据
delete from 表名1,表名2 
    join 表2  on  连接条件 
    ...
    where  列名=值;

例:
DELETE p, spj
FROM p
         JOIN spj ON p.PNO = spj.PNO
WHERE p.COLOR = '蓝色';

3.修改数据update

1.会修改列中所有的值
update 表名 set 列名=值;
2.修改列中特定的值
update 表名 set 列名=值1 where 列名=值2;  #将值2修改成值1

DQL数据查询

1.基础查询

select  [distinct]  列名  自定义列名 from   表名   [where  表达式]

distinct可以去重

别名与常量as可以省略
select  PNO '学号' ,color '颜色'    from  P

常量,写在属性列前可以使属性列全显示常量
select 3220 PNO,'红' clolor from P 

where表达式关键字
1.between  and  (针对int型数据)
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
2.and与or  (and等价于&&)
SELECT * FROM student WHERE age >= 20 AND  age <=30;
SELECT * FROM student WHERE age = 22 OR age = 18;
3.in(只要符合其一就选择)
SELECT * FROM student WHERE age IN (22,18,25);
4.is null 与is  not  null  (空值判断)
SELECT * FROM student WHERE english  IS NOT NULL;
5.like(模糊查询)
 _:单个任意字符
 %：多个任意字符
查询姓马的有哪些？
SELECT * FROM student WHERE NAME LIKE '马%';

where模糊查询like案例
查询姓马的有哪些？
SELECT * FROM student WHERE NAME LIKE '马%';
查询姓名第二个字是化的人            
SELECT * FROM student WHERE NAME LIKE "_化%";                
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';                    
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';

2.排序查询

select  [distinct]  列名  from   表名  order  by  列名
#默认升序ASC(从上到下)

SELECT * FROM person ORDER BY math DESC; 
#降序声明DESC

3.聚合函数

1.count：计算个数
2.max：计算最大值
3.min：计算最小值
4.sum：计算和
5.avg：计算平均数
6.DISTINCT:去重
7.HAVING:查询重复记录函数    


只有count不忽略空值

1.COUNT 计算数量
SELECT COUNT(AGE) AS 年龄小于30岁的人数 FROM 表名 WHERE AGE<30
2.MAX    最大值
SELECT MAX(AGE) AS 最大年龄 FROM 表名 WHERE SEX='女'
3.MIN    最小值
SELECT MIN(AGE) AS 最小年龄 FROM 表名 WHERE SEX='男'
4.SUM   求和 
SELECT SUM(AGE) AS 年龄总和 FROM 表名
5.AUG    平均值
SELECT AVG(AGE) AS 年龄平均值 FROM 表名
6. DISTINCT 去重
SELECT DISTINCT(AGE) AS 唯一年龄 FROM UserInfor
7.HAVING
常常与GROUP BY(聚合函数 分组)一起使用
SELECT AGE,COUNT(AGE) AS 重复数量 FROM UserInfor GROUP BY AGE HAVING COUNT(AGE)>1
#按AGE分组后显示AGE数大于1的组(注意,having中的关键字必须在分组后有的关键字中)

4.分组查询

select  列名  from  表名 group   by  表

按照性别分组。分别查询男、女同学的平均分,人数
SELECT sex , AVG(math),COUNT(id) FROM student GROUP BY sex;
按照性别分组。分别查询男、女同学的平均分,人数 要求：分数低于70分的人，不参与分组,分组之后。人数要大于2个人
SELECT sex , AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2;

select指定的字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。
having后没有要求,既可以用聚合函数,也可用所有的属性(可以不在group by 中)


运行顺序
1.where
2.group by
3.聚合函数
4.having



例:
select Cno,avg(Salary) from WORKS  group by Cno having avg(Salary) >20000
#此处按Cno分组,但是avg中的属性不是Cno

5.分页查询

select  列名  from  表名 limit 开始索引,查询数量

SELECT * FROM student LIMIT 0,3; -- 第1页 0-3 
SELECT * FROM student LIMIT 3,3; -- 第2页 3-6
SELECT * FROM student LIMIT 6,3; -- 第3页 6-9

6.连接查询

a.内连接

隐式内连接
select  表.列   from   表1,表2  where  表1.列1=表2.列2  (连接)

SELECT A.column1, B.column2 FROM A, B WHERE A.common_column = B.common_column;
#内连接的结果是建立在两个表共有的分量上,如果在连接的属性中(此#处是common_column)左表有某分量但右表没有,那么该分量就不会
#出现在连接表中

显示内连接
select 列名 from 表名1  join 表名2 on 条件
SELECT A.column1, B.column2 FROM A [INNER] JOIN B ON A.common_column = B.common_column;
#inner可省略

b.外连接

左外连接
select  列名  from  表1  join  left   表2
右外连接
select  列名  from  表1  join  right  表2

#外连接用于保存某些无法被内连接保存的分量,例如左表有但右表没有连接属性的某分量,可以使用左外连接保存左表有右表没有的连接分量

参考案例 1

如果是自身连接,需要起别名否则无法分辨
SELECT A1.column1, A2.column2 FROM A A1 JOIN A A2 ON A1.common_column = A2.common_column;

7.嵌套查询

嵌套查询：一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询。
     SELECT Sname        外层查询/父查询
     FROM Student
     WHERE Sno IN
         （SELECT Sno              内层查询/子查询
             FROM SC
             WHERE Cno= ' 2 '）；

子查询的结果是集合要使用in 否则可使用=

1.带有谓词ANY或ALL 的子查询
例:
> ANY    大于子查询结果中的某个值        
<= ALL    小于等于子查询结果中的所有值
= ANY    等于子查询结果中的某个值        
!=（或<>）ANY    不等于子查询结果中的某个值
!=（或<>）ALL    不等于子查询结果中的任何一个值


2.带有谓词EXISTS 的子查询
EDXISTS只会返回false或true
针对"没有某个人"的条件使用NOT EXISTS很有效
针对"选了全部课程的人"的条件转化为没有一门课它不选,只要使用NOT EXISTS判断"没有一门课"和"他不选"这两个条件即可

8.条件函数

1.case

多分支的函数
CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 
…
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

实例:
SELECT CASE WHEN age<25 OR age IS NULL THEN '25岁以下' 
            WHEN age >= 25 THEN '25岁及以上'
            END age_cut,COUNT(*)number
FROM user_profile
GROUP BY age_cut

2.if

IF(expr1,expr2,expr3)
如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。(三目表达式)

实例:
select if(age<25 or age is null,'25岁以下','25岁及以上') age_cut,
count(device_id) number from user_profile
group by age_cut

3.nullif

NULLIF(expr1, expr2)
#如果 expr1 = expr2 成立，那么返回值为 null，否则返回值为 #expr1 的值

4.COALESCE

COALESCE(值1,值2...);
#用于从一组值中返回第一个非 NULL 值。如果所有的值都是 NULL，那#么 COALESCE 将返回 NULL。

#如果值1为null就继续找下一个直到找到非null值就返回第一个非null值,或没找到非null值返回null

SELECT Sname　　　　　　/*查询一个学生的学号 （在Student表中循环取学生信息）*/
FROM Student
WHERE NOT EXISTS(　　　 /*不存在课程  (在Course中循环取所有课程信息)*/　　　　　　　　　　　
　　SELECT *
　　FROM Course
　　WHERE NOT EXISTS(　 /*没选修  (在SC表中循环比对) */
　　　　SELECT *
　　　　FROM SC
　　　　WHERE SC.Sno = Student.Sno  AND SC.Cno = Course.Cno
　　)
);

DCL数据控制

参考数据库安全性管理

视图

视图的优点
视图隐藏了底层的表结构，简化了数据访问操作
因为隐藏了底层的表结构，所以大大加强了安全性

视图的缺点
如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护

创建表
CREATE  VIEW 视图名 AS SELECT 属性 FROM 表名;
修改视图
ALTER VIEW 视图名称 AS查询语句
删除视图
DROP VIEW IF EXISTS 视图名称;
#修改,删除视图都不会对基本表产生影响

1.可跟新视图: 一般的行列式子集视图可以更新
2.不可更新视图,有五种:
(1)select目标列包含聚集函数.
(2)select字句使用了unique或distinct
(3)视图中包括group by字句
(4)视图中包括经算法表达式计算出来的列
(5)由单个表的列构成,但并不包括主键的视图

索引

索引的优点
索引可以大大提高MySQL的检索速度
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

索引的缺点
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占物理空间
如果某个数据列包含许多重复的内容或者小表，为它建立索引就没有太大的实际效果。

优先建立索引的列条件
1.在经常需要搜索的列上，可以加快搜索的速度
22.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
3.在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
4.在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
5.在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6.在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

索引

逻辑分类
PRIMARY KEY
主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
ALTER TABLE TableName ADD PRIMARY KEY(column); 

UNIQUE
唯一索引：数据列不允许重复，允许为NULL值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
ALTER TABLE TableName ADD UNIQUE(column); 

INDEX
普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
ALTER TABLE TableName ADD INDEX IndexName(column);

例如:
为关系表SPJ创建一个基于QTY列的按降序排列的IX_SPJ索引；
ALTER TABLE SPJ
ADD INDEX IX_SPJ (QTY DESC);

修改索引index名称
#需要结合drop与add
ALTER TABLE SPJ
    DROP INDEX IX_SPJ,
    ADD INDEX New_IX_SPJ (QTY DESC);

tips
按列数分为单例索引与组合索引

物理分类
聚簇索引
提高某个属性的查找效率
非聚簇索引

参考一文搞懂MySQL索引（清晰明了）-CSDN博客

数据库安全性管理

禁止非法用户和非法操作的进入

用户创建

创建用户
CREATE USER 用户名[@'ip']  IDENTIFIED BY 密码    #给跨服务器的用户授权,默认ip是localhost

IDENTIFIED BY可以设置密码,也可以不设置密码
实例:
CREATE USER 'xs001'@'localhost' IDENTIFIED BY '123456';

修改密码
ALTER USER 用户名 IDENTIFIED BY 'new_password';
实例:
ALTER USER 'xs001'@'localhost' IDENTIFIED BY 'new_password';

这个用户可以用于登录MYSQL

tips
用户名中可以指定登录ip如 'peiqi'@'localhost'  ,限制用户的登录途径
如果创建时指定了登录ip,那么其他操作用户名要带上ip操作

1)自主存取控制

通过 SQL 的GRANT 语句和REVOKE 语句实现
用户权限组成：数据对象、操作类型
定义用户存取权限：定义用户可以在哪些数据库对象上进行哪些类型的操作
定义存取权限称为授权

授权
GRANT 权限
       ON 对象类型 对象名
       TO 用户
       [WITH GRANT OPTION];
       
禁止循环授权
WITH GRANT OPTION表示该用户可以将自己的权限分享给其他用户,例如A用户由查询SC表的权限,那么它就可以创建一个有查询SC表的权限的用户

角色授权
每一个用户都是权限的集合,所以可以用用户代替权限(角色包含权限与权限对象)
GRANT 角色
       TO 角色
       [WITH GRANT OPTION];

使用GRANT语句，使角色R1拥有Student表的    SELECT权限
GRANT SELECT
    ON TABLE Student 
    TO R1;
    
将这个角色授予王平，张明，赵玲。使他们具有角色R1所包含的全部权限
GRANT  R1 
       TO 王平,张明,赵玲;

回收
REVOKE 权限
ON 对象类型 对象名
FROM 用户    [CASCADE | RESTRICT];
CASCADE：级连回收
RESTRICT：受限回收

创建数据库模式
create user [with DBA|RESOURSE|CONNECT(默认)]

没有with默认是CONNECT权限
只有root可以创建模式
            创建用户    创建视图    创建表     登录后查询与操作
CONNECT        no            no             no            yes,但必须授权     #默认权限
DBA            yes            yes            yes            yes                #管理员权限
RESOURSE    no            no            yes            yes                #操作表的权限

2)强制存取控制

对数据、用户进行标记，进行存取控制。
对于任意一个对象，只有具有合法许可证的用户才能存取。

数据库完整性

禁止不合语义的数据和不正确的数据进入数据库

1.完整性约束

实体完整性
primary key    # 属性内唯一,不可空,表内唯一

违约策略:拒绝

参照完整性
forigen key # 外键,外键属性分量必须在外关联表内存在才能插入
例如:
student表       SNO,SNAME,SAGE
SC表            SNO,CNO,PNO    其中SNO关联student表内的SNO
如果要插入SC表内(3220,1,2),但3220在studet内的SNO没有,是无法插入的

违约策略:
插入外码:拒绝  ---参照表
修改外码:拒绝     ---参照表
修改主码:拒绝/级联/置空值 ---被参照表
修改主码:拒绝/级联/置空值 ---被参照表

可能破坏参照完整性的情况
被参照表           参照表            违约处理
  X                插入元组            拒绝
  X                修改外码            拒绝
删除元组            X                拒绝/级联删除/设为空值
修改主码            X                拒绝/级联删除/设为空值

自定义完整性
check
约束可以用CONSTRAINT声明名称,命名规则同java变量

定义在属性后是属性约束
Sno NUMERIC(6) CONSTRAINT C1  CHECK(Sno BETWEEN 90000 AND 99999)
定义在表定义最后是元组约束(约束所有属性)
CONSTRAINT S1 PRIMARY KEY(Sno) 

删除check(需要先命名)
ALTER TABLE Student  DROP CONSTRAINT C1;

添加check约束
ALTER TABLE Student   ADD CONSTRAINT C3 CHECK(Sage<40);

2.触发器

触发联级修改或删除
情景:当在Student中修改SNO时,同步修改其他表如SC,Class内的SNO

定义触发器
create  trigger   触发器名
before|after  触发事件 on 表名    
for each {row|statement}     
[when 触发条件] 
Begin 触发动作 END

权限控制:表的拥有者才可以在表上定义触发器

触发事件
包括insert,delete,update 
或组合insert or delete(组合),update of 属性(指定触发属性)


触发时间
包括before,after

触发类型
包括行级触发for each row和语句级触发for each statement
例如:一句update修改了1000行数据,触发1000次行级触发器,触发1次语句级触发器

触发条件when
某些属性达到了条件
例如: SC.SCORE>95

触发动作
达到触发条件后可以进行触发动作,行级触发器可以引用新旧值
BEGIN

END;

特殊变量
如果是行级触发器，用户可以使用NEW和OLD引用UPDATE/ INSERT事件之后的新值与旧值。
如果是语句级触发器，则不能在触发器动作体中使用NEW或OLD进行引用。

可以使用declare声明变量
declare @newname varchar(20)
select name into @newname from SC

触发器的使用
联级删除
CREATE  TRIGGER  tr_t_test_main_Del
   BEFORE  DELETE  ON  t_test_main
FOR  EACH ROW
BEGIN
   DELETE  FROM  t_test_sub  WHERE  main_id = OLD.id;
END ;

删除触发器
DROP TRIGGER <触发器名>  ON <表名>

3.存储过程

创建存储过程
无参构造
Create proc 存储名  as sql语句

入参与出参构造
Create proc(in 变量名 类型,out 变量名  类型) 存储名  as sql语句

删除存储过程
drop proc 存储名