MYSQL

1、关系型数据库：
使用二维表来存储数据，二维表之间又可能存在联系或依赖关系
2、常见的关系型数据库管理软件（RDBMS）
Oracle、MySQL、sql server、db2、sqllite、sybase...
3、MySQL：小巧灵活、速度快、开源、支持分布式
阿里提出去IOE：I(ibm)服务器、O(oracle)数据库、E(emc)硬件


4、sql：结构化查询语句，几乎所有的关系型数据库都支持标准的sql语句
但是每个数据库管理系统又可能对标准sql做出一些扩展


5、sql语法：
1、列类型：创建数据表时每个列需要数据类型，分为：数值、日期、字符（串）
    数值类型：tinyint,smallint,mediumint,int bigint,float,double,decimal,numeric
    最大显示宽度M：如果输入的数据位数小于M，则在数据前面补空格，[zerofill]:用0填充；
如果输入的数据位数大于显示位数，则存入输入的数据，M值不会影响存入的数据
    float[(M,D)]：M为整个数据的位数包括小数位，D是小数点后面的位数，如果数据小数点后面的位数大于D        ，则按照四舍五入
    如果指定unsigned，则不能为负值


    日期时间：date,datetime,timestamp,time,year,都可以使用日期时间格式的字符串格式数据
    auto_increment主键自增长，只能用在主键，以上一条记录自动加1
    insert xxx values(0,'19980812');
    insert xxx values(null,'1998-08-12');
    timestamp:时间戳，更改其他字段，自动更新到系统最新时间
    update 表名 set 列名=要修改的地方；
    now();返回一个时间戳，系统最新时间


    字符串不区分'和"，但必须成对出现
    ``数字1前面的键，当取名时与系统有命名冲突时使用


    char(n):定长字符串。不管存入的数据是几个字符都会占用n个字符空间


    varchar(n)：变长字符串。存入几个字符则会分配几个字符的空间，同时会分配一个用来存储字符长度


    enum枚举类型：在插入数据时只能从其中的值选择一个，可以使用值的序号，从1开始
    create table t_t9(name varchar(20),sex enum('男','女'));
    insert t_t9 values("jim",'男');
    insert t_t9 values('jim',1);


   set集合类型：可以包含多个值，使用时可以从中选择任意值
   create table t_t10(id int primary key auto_increment ,house set("北京","上海","广州","深圳"));
   insert t_t10 values(0,('上海,深圳'));

   desc 表名;描述表的列定义等信息
   default 'xxx':给列设置默认值
   name char(20) default 'hello';


   create database 数据库名;创建数据库时默认编码方式为安装时选择的编码方式
   alter database 数据库名 character set gbk;
   <==>create database 数据库名 charset=gbk;修改编码方式为gbk
   
   DDL:用来定义和修改数据库对象（数据库，表等）的结构
   create：创建数据库，数据表
    alter：更改数据库，数据表结构
alter table 表名 add col_name type  [first | after col_name]；添加列，默认添加到最后一列
change 修改：alter table student change sex gender enum('男','女');//修改名称
modify 修改列的其他约束：alter table student modify name varchar(20) default 'jim';
rename to 修改表的名称：alter table student rename to stu；
   drop：删除数据库、数据表包括里面的数据
drop database testdb；
   备份：
备份数据库需要退出MySQL，在Windows控制台状态中使用mysqldump命令
mysqldump -u root -p testdb2 > f:/abc.sql(不要分号)
使用方法：mysqldump -u root -p 要备份的数据库名 ＞ 备份到的目的地（例如：E:/a.sql）
数据恢复需要登录MySQL，同时选中一个数据库（即要将备份文件恢复到的数据库）
使用：source 备份文件的绝对路径
source f:/abc.sql

未登录MySQL情况下:mysql -u root -p 要恢复到的数据库名  < 备份文件的绝对路径

完整性约束：
1、记录完整性，使用主键唯一标识一条记录，可以使用key(col1,col2...)定义联合主键，联合主键不能自增长
     create table company(address varchar(20),range varchar(30),money float,primary key(address,range));
2、列完整性，定义列时添加的列的约束属性
     列的数据类型，not null,null,unique,auto_increment,index,default,comment '注释'
     主键自带not null,unique,index
     主从表：被引用者为主表，引用者为从表
     定义外键列时最好加上非空约束
     创建外键：create table student(id int primary kry auto_increment,name varchar(30) not null,tid int,
--constraint起别名
constraint fk_student_teacher_id foreign key(tid)
--references参考引用主表中的主键或索引列
references teacher(id));
外键对从表的约束：必须引用主表中已有的数据
  外键对主表的约束：如果有从表在引用其中的记录，则不允许修改和删除，如果要修改和删除则需要修改从表
可以在创建外键时添加级联
on delete xxx on update xxx
xxx:restrict | cascade | set null | no action
restrict:不允许操作
cascade:级联
set null:主表改变，则从表设置为null
no action类似restrict


      使用alter table语句对已存在的表结构进行修改
      添加外键列和外键
      alter table stu add tid int,
      add constraint fk_stu_tea_tid foreign key(tid) references tea(id)
      on update cascade on delete set null;
      添加主键：
      alter table tea add primary key(id);
      删除主键：
      alter table tea drop primary key;


      alter database用于修改数据库的全局特性
       character set 用于修改数据库字符集
       collate用于更改数据库字符集校验


      drop :删除数据库和数据表
主键的建立有：直接在列定义后面加primary key，或在最后加primary key(xxx,xxx,...)做联合主键，或使用alter添加主键
modify修改列的约束，只能修改已存在的列
change在modify能做的基础上，还能修改列名称
colum_definition:名称、类型、约束


一对多的关系把外键设置到多的一方
一对一的关系外键可以随意设置
多对多的关系会再创建一个中间表来描述多对多关系

--DDL：用来操控结构
--DML:用来操控数据
   1、insert：向表中插入数据记录，插入时需要指明表的列，如果不指定则需要按照表的列定义顺序依次插入所有列，一次   可以插入多条记录
   insert [into] 表名 [(col_name,...)] values (...),(...),...
  insert student(sno,sname) values(0,'zs');
mysql>  insert sc(sno,courseid,grade)
    ->  values('95001',1,98),('95002',1,50),('95004',1,70)
    ->  ('95003',4,95),('95005',4,40),('95006',4,77),
    ->  ('95001',2,80),('95002',3,58),('95006',2,70);
   2、update：更新表中的列，可以使用where进行记录的筛选
        where中可以使用关系和逻辑运算符进行记录的筛选
        update 表名 set 列名 = 更新值 [,col=newvalue,...] [where 更新条件]
        eg:update student set sage=sage-2 ;
   3、delete：删除表中的记录，如果直接使用delete则删除所有记录，所以往往用where进行记录筛选
单表删除：
        delete from 表名 [where 删除条件]
eg:删除某一列： delete from student where sno=95008;
多表删除：
        delete 表名1 [,表名2,...] from 表名1 [,表名2,...] [where 删除条件]
eg: delete student,sc from student,sc where student.sno=sc.sno and student.sno='95001';


        由于MySQL是自动提交事务，所以使用delete删除数据是直接删除
        可以使用begin手动开启事务，最后使用rollback回滚事务，还可以commit提交事务


MySQL运算符：
１、算术运算符：
      包括：+、-、*、/(DIV)、%(mod)和其他编程语言一样
mysql> select 4+6;
+-----+
| 4+6 |
+-----+
|  10 |
+-----+
as:给列、表起别名，可省略
mysql> select 6+1 as result;
+--------+
| result |
+--------+
|      7 |
+--------+


mysql> select 5/6,5%6,6/5,6%5;
+--------+------+--------+------+
| 5/6    | 5%6  | 6/5    | 6%5  |
+--------+------+--------+------+
| 0.8333 |    5 | 1.2000 |    1 |
+--------+------+--------+------+
“/”得到的数为浮点数形式
mysql> select 6 DIV 5;
+---------+
| 6 DIV 5 |
+---------+
|       1 |
+---------+
使用DIV除法得到小数点前面的整数形式


2、比较运算符
   比较运算符允许我们对表达式的左边和右边进行比较，一个运算符的结构总是1（真）、0（假）、或者是null（不能确定）
比较运算符包括：>、<、=、!=(<>)、>=、<=、in、beteween and、like、is null、is not null等
 select * from student where sage<19;
 select * from student where ssex <> '女';等价于
 select * from student where ssex != '女';


in():括号内为一个集合
 select * from student where sdept in('计算机系','信息系');


between and:在什么范围之内
 select * from student where sno between 95003 and 95005;[95003,95005]都是闭区间


like:模糊查询
 select * from student where sname like('欧阳%');
“%”：代表任意长度（长度可以为0）的字符串
a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab 等都满足该匹配串
+-------+----------+------+------+----------+
| sno   | sname    | ssex | sage | sdept    |
+-------+----------+------+------+----------+
| 95002 | 欧阳炎   | 男   |   19 | 计算机系 |
| 95006 | 欧阳宝宝 | 男   |   19 | 美术系   |
+-------+----------+------+------+----------+


 select * from student where sname like('欧阳_');
“_”：匹配任意单个字符串
a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串


可以使用is null或者is not null运算符来测定是否为空
3、逻辑运算符
    &&、and；||、or；！、not


字符串自动转换：
用字符串表示的数字在任何可能便于计算的地方都被自动地转换为数字，遵循两个基本规则：
1、如果第一位是数字的字符串被用于一个算数运算中，那么它将被转换为这个数字的值；
2、如果一个包含字符和数字混合的字符串不能被正确的转换为数字，那么它被转换成0
0表示假，1表示真
mysql> select 123='123';
+-----------+
| 123='123' |
+-----------+
|         1 |
+-----------+


mysql> select '124a'>123;
+------------+
| '124a'>123 |
+------------+
|          1 |
+------------+
第一个遇到数字转换为数字的值，遇到字符自动将字符转换为0
mysql> select 'a124'>123;
+------------+
| 'a124'>123 |
+------------+
|          0 |
+------------+
开始遇到字符直接转换为0，后面数字不再遇到


--DQL:查询语句
select [distinct | distinctrow | all] select_expression
[from table_references
[where where_definition]
[group by col_name]
[having where_definition]
[order by {unsigned_integer | col_name | formula} [asc | desc],...]
[limit [offset,]rows]
]
from字句：指定查询数据的表
where字句：查询数据的过滤条件
group by 字句：对匹配where字句的查询结果进行分组
having字句：对分组后的结果进行条件限制
order by 字句：对查询结果进行排序，后面跟desc降序或asc升序（默认）
limit字句：对查询的现实结果限制数目


select * from student;
select * from student where ssex='女';
select * from student where sname like '%小%';
select * from student group by sdept;
select count(sno) from student group by sdept;
select count(sno),sdept from student group by sdept;
select avg(grade),sno from sc group by sno;
select avg(grade) avg,sno from sc group by sno order by avg desc;
查询平均分最高分
select avg(grade) avg,sno from sc group by sno order by avg desc limit 0, 1;
平均分最高的2,3,4名
select avg(grade) avg,sno from sc group by sno order by avg desc limit 1, 3;
平均分最高的前三名
select avg(grade) avg,sno from sc group by sno order by avg desc limit 3;
查询平均成绩大于等于60的学生的学号和平均分
select avg(grade) avg,sno from sc group by sno having avg >=60;
查询平均成绩大于等于60的学生的学号和平均分，安装平均分升序排序
select avg(grade) avg,sno from sc group by sno having avg >=60 order by avg asc;
=只有用在set中才是赋值，其他都是比较运算符
limit offset,rows; offset偏移多少条之后再取，rows是取得行数；如果是一个参数，则表示偏移量为0;limit n等价于 limit 0,n
查询成绩在60以上的学生的平均成绩，且只看平均成绩超过70的学生的学号和平均分
select avg(grade) avg, sno from sc where grade >= 60 group by sno having avg >= 70;


having 和where 都是条件筛选，但是where是在分组前的筛选而having是对分组后的条件筛选
group by是按照某列进行分组，往往会和聚合函数一块使用
distinct用来去重，对谁去重放在谁前面
“=”只有用在set中才是赋值，其他都是比较运算符

嵌套子查询：
select count(avg) from (select avg(grade) avg,ssid from sc group by ssid having avg>=60 order  by avg desc)a;


多表查询：
交叉连接（cross  join）
使用cross join链接两张表，on指定表之间的关系，查询是会将符合关系的记录取出；如果没有on条件则返回两张表的笛卡尔
积结果
MySQL中交叉连接和内连接相似
select * from sc cross join student on sc.sc_sno=student.sno;
 select * from course cross join sc on course.courseid=sc.sc_courseid;
 select * from course cross join sc on course.courseid=sc.sc_courseid cross join student on sc.sc_sno=student.sno;
使用隐式链接，使用where指定关系进行筛选
select * from student,course,sc where student.sno=sc.sc_sno and course.courseid=sc.sc_courseid and course.cname='java';


外链接：左外链接和右外链接
左外链接：left join 
查询时左表为主表，右表为从表，查询时主表中的所有记录都会被去除，右表中的记录根据on后的条件取出，从表中如果没有符合条件的数据则用null填充
右外链接：right join
查询时右表为主表，左表为从表，查询时主表中的所有记录都会被去除，右表中的记录根据on后的条件取出，从表中如果没有符合条件的数据则用null填充
where字句可以单独添加并进行进一步筛选


聚合函数：
计数函数：count(列名) 计算元素的个数 
求和函数：sum(列名) 对某一列的值求和，但属性必须是整型
计算平均值：avg(列名) 对某一列的值计算平均值
求最大值：max(列名) 找出某一列的最大值
求最小值：min(列名) 找出某一列的最小值






DELETE语法
单表语法：


DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]
多表语法：


DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*] ...]
    FROM table_references
    [WHERE where_definition]
或：


DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*] ...]
    USING table_references
    [WHERE where_definition]


DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
或：


DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;



程度从强到弱
1、drop  table tb 
      drop将表格直接删除，没有办法找回
2、truncate (table) tb
      删除表中的所有数据，不能与where一起使用
3、delete from tb (where)
      删除表中的数据(可制定某一行)
区别：truncate和delete的区别
         1、事务：truncate是不可以rollback的，但是delete是可以rollback的；
              原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
         2、效果：truncate删除后将重新水平线和索引(id从零开始) ,delete不会删除索引    
         3、 truncate 不能触发任何Delete触发器。
         4、delete 删除可以返回行数




----------函数：必须要有返回值
数学函数：
abs(X);返回X的绝对值
floor(x);返回不大于X的最大整数值
round(x);四舍五入
...


日期时间函数：
curdate();以yyyy-mm-dd形式返回当前日期
Curtime()以hh:mm:ss形式返回当前事件
Now()返回当前日期和时间
Year(date),month(date),day(date)分别返回date对应的年月日
Dayname(date)返回date对应的工作日，monthname(date)返回date对应的月名称
Hour(time),minute(time),second(time)返回time对应的时分秒
Week(date)返回date对应的是这一年的第多少周
dayofmonth();一个月的第几天
dayofyear();一年中的第几天
...


字符串函数：
concat(str1,str2,...);返回结果为连接参数产生的字符串，如果任何一个参数为null，则返回值为null
length(str);返回值为字符串str的长度，单位为字节
一个汉字是两个字节
left(str,len);返回从字符串str开始的len最左字符
rtrim(str);结尾空格被删除
repeat(str,count);返回一个由重复的字符串str组成的字符串，字符串str的数目等于count;若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。


加密函数：
常用加密函数有md5(str),sha1(str),password(str),encrypt(str)等
而encrypt方法在windows平台始终返回null,所以在实际生产中推荐使用md5和sha1加密方法，对于password加密是系统用户密码加密方法，所以不推荐在我们自己的应用中使用此方法进行加密。


自定义函数：
delimiter //--定义结束符“//”
create function ageLeve(age int) returns varchar(30) --return要加s
begin
declare result varchar(30) default '';-- 声明局部变量，默认值为空串
if age < 18 then --  if分支语句
set result='未成年'; -- 修改局部变量的值
elseif age >= 18 && age <=55 then
set result = '壮年';
else set result='老年';
end if;
return result; -- 函数返回
end //


create function sum2(num int) returns int
begin
declare result int default 0;
declare i int default 1;
repeat -- 循环语句
set result = result + i;
set i = i+1;
until i > num end repeat;
return result;
end$$


将int类型的整数倒序输出
 create function intReverse(num int) returns int
 begin
 declare result int default 0;
 declare i int default 0;
 declare j int default length(num)-1;
 while num !=0 do  -- 循环语句
 set i=num%10;
 set num=num DIV 10;
 set result=result+i*pow(10,j);
 set j=j-1;
 end while;
 return result;
 end //


使用函数：因为函数有返回值所以使用select
select intReverse(123)//


--定义存储过程procedure:没有返回值
create procedure mysum(in num1 int,in num2 int,out result int)
begin
set result=num1+num2;
end//
使用：
call mysum(1,2,@a)//
select @a//


@用户变量，仅限于本次登录，退出后消失
@@全局变量，存在于服务器，不能创建全局变量


 create procedure selectByTableName(in tablename varchar(30))
 begin
 if tablename='student' then
 select * from student;
 elseif tablename='course' then
 select * from course;
 elseif tablename='sc' then
 select * from sc;
 else
 select 'no support this table';
 end if;
 end //


create procedure insertUser(in num int)
begin
declare i int default 0;
declare name varchar(30) default '';
repeat
set name = concat('test',i);
insert user values(null,name,round(rand())+1);
set i = i+1;
until i > num end repeat;
end $$
round(rand())+1;产生1和2的随机数


事务控制：
事务特性：
1、原子性：指事物是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
2、一致性：事务必须使数据库从一个稳定状态变换到另外一个稳定状态
3、隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他食物的操作数据所干扰，多个并发事务之间要相互隔离
4、持久性：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
在MySQL命令行的默认下，事务都是自动提交的
可以通过set autocommit=0;命令设置不自动提交。如果事务不自动提交，则在做insert、update、delete操作时需要手动执行commit否则数据库不会更改


MySQL中使用start transaction | begin : 显示的开启一个事务
例如：
begin;--开启事务
Update student set sname=’小明’ where sno = 10002;
Commit;--提交事务，否则update操作不会生效。


开启事务后可以设置保存点(savepoint)，这样就可以使用rollback to savepoint sp1;回滚到sp1之前。
例如：
Begin;
begin;--开启事务
Update student set sname=’小明’ where sno = 10002;
Savepoint sp1;
Update student set sname=”小刚” where sno = 10002;
Rollback to savepoint sp1;
查询student表发现10002的名字又恢复为小明
Commit;--提交事务，否则update操作不会生效。
一个事务中可以有多个savepoint，可以使用RELEASE SAVEPOINT释放保存点。


给自定义数据库权限
 grant select,insert on * to zf;


嵌套子查询：
select count(avg) from (select avg(grade) avg,ssid from sc group by ssid having avg>=60 order  by avg desc)a;