mysql数据库基础汇总

MySQL  数据库  

mysql  端口3306 ，字符集CharSet  选择utf8    

数据库 其实是由很多表构成的

从列的角度 ： 字段 ，从行的角度 ： 数据

---------------------------------------------------------mysql数据类型

 整数型 ：  int    

 浮点型 ： double
定点类型：  DECIMAL(m,d)   m 传入的数据  ，d  代表保留的小数位，按照四舍五入处理
日期时间类型  ：date  datetime 
字符串类型：  char（固定不变）   varchar（可变）  text（大文本）
图片和视频 :  BLOB   存的二进制的类型


-----------------------------------------------------数据库基本操作    （DDL 操作）
查看表的字段信息：desc 表名;
查看表的所有信息：show create table 表名;
添加主键约束：alter table 表名 add constraint 主键 （形如：PK_表名） primary key 表名(主键字段);
添加外键约束：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除主键约束：alter table 表名 drop primary key;
删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）;
修改表名：alter table t_book rename to bbb;
添加列：alter table 表名 add column 列名 varchar(30);
删除列：alter table 表名 drop column 列名;
修改列名MySQL： alter table bbb change nnnnn hh int;
修改列名SQLServer：exec sp_rename't_student.name','nn','column';
修改列名Oracle：alter table bbb rename column nnnnn to hh int;
修改列属性：alter table t_book modify name varchar(22);
约束条件：
    primary key  主键    表明该字段的数据是唯一的，并且是不能为空
    foregin key  外键     存在关系，用外键来进行关联
    not  null    不能为空
    auto_increment  自动增长

    defalut  数据   默认

    unique 唯一

    primary key = unique + not null+一个表只能有一个主键

设计到主外键关系：

一对一   在一的一方设置外键
一对多   在多的一方设置外键
多对多   采用第三表来维护多对多之间的关系

---------------------------------------------------------------------数据库的增删改查  CRUD 操作   DML语句 

增加   insert into   表名  （列1，列2）  values  （值1，值2）
删除   delete from   表名  where  条件 
更新   update   表明  set  列名=新值  where   条件
查询  select

核心操作查询：  select 
条件查询  where
  SELECT  sname  from  student where  sage=18
SELECT  sname  from  student where  sage>18

带in  关键字  not in  包含或者是不包含
select sname from  student where  sage not in (18,34)

带between  and  关键字  范围
SELECT  sid,sname,sage  from student where  sage BETWEEN 18  and  21;

带模糊查询  like  not like  %  _
“%”匹配任意数目字符(包括零个字符)
“_”匹配任何单个字符(可多个一起使用)
“[]”匹配范围内的字符
select  sname  from  student  where  sname like '张_';  

and  和 or  条件查询
and代表所有条件需要满足   or 代表 主要满足条件的都查询出来
SELECT  * from  student  where  sname ='张三'  OR  sage ='34';

空值查询  null   not  null
SELECT  * from  student  where   sage  is  （not） NULL;

排序： order by   asc 和  desc   升序和降序
SELECT  * from  student  ORDER BY sage desc;

去重复  DISTINCT  过滤掉多余的重复记录只保留一条
SELECT  DISTINCT  sname  from  student


别名 ：  as  或者 空格
select  *  from  student   
(select  sid as ssid，sname  from student) as student1

连接查询： 内连接  

1. 使用 where  某一个外键 等于 另外一个主键 

2 使用inner join    

select teacher.tid, teacher.tname,student.sname  
from  teacher,student 
where teacher.tid=student.tid

SELECT  teacher.tname,student.sname  
from  teacher  
INNER JOIN  student
on teacher.tid=student.tid

外连接：左连接left join  右连接right join  全连接union


SELECT  teacher.tname,student.sname  
from  teacher  
LEFT JOIN  student
on teacher.tid=student.tid

SELECT  teacher.tname,student.sname  
from  teacher  
right JOIN  student

on teacher.tid=student.tid

UNION 内部的 SELECT 语句必须拥有相同数量的列和相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同

SELECT  sname  from  student
UNION  （ALL）
SELECT  sname  from  student

子查询  相当于 查询还有查询

1.where 里面在跟查询
SELECT  sname  from student  where  sage  in (SELECT sage from student);

2.exists  和  not EXISTS
SELECT  sname  from  student  where  EXISTS (SELECT sname FROM student where sname='张飞')  

3.any 和 all
SELECT  sname  from  student  where  sage > ANY(select sage  from student where sname='吕布' or sname='张飞')

SELECT  sname  from  student  where  sage >ALL(select sage  from student )

limit分页查询

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

内置函数

平均函数   平均值
SELECT AVG(age)  from  student3

COUNT() 函数  计数
SELECT COUNT(*)  from  student3  

max（） 获取指定列最大的数据
SELECT  MAX(age)  from  student3

sum（）求合
SELECT  sum(age)  from  student3

UCASE() 字符串大小写转换
SELECT UCASE(LastName) FROM student3  where Id_P=6   

MID（列名，起始位置，截取的长度）
SELECT MID(LastName,2)  FROM student3

LENGTH() 获取字符串长度
SELECT LENGTH(LastName)  FROM student3

ROUND(列名，指定保留小数位数)  按照四舍五入
SELECT ROUND(student3.weight,0) FROM student3

Now（）  获取当前时间
SELECT student3.LastName ,  NOW() FROM student3

groupby  和  having  用法

 groupby ：内部聚合显示，一条 ，但可以通过sum avge等得出内部

having：用在groupby后筛选 

with  rollup：在groupby 后使用  多加一行显示包含内聚值的所有值和
SELECT   student3.gradeName,AVG(weight)
from  student3
GROUP BY  student3.gradeName  WITH ROLLUP


事务

使用条件和使用对象：Innodb数据库引擎的情况下才能使用事务，，用于管理insert、update、delete语句

事务的四大特性： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

• 1、事务的原子性：一组事务，要么成功；要么撤回。
• 2、稳定性 ：有非法数据（外键约束之类），事务撤回。
• 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
• 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。注：可靠性和高速度不可兼得。

使用：

start transcation/begin; 开启事务；
mysql1--;
mysql2--;
commit / rollback;   注';'必不可少

  SET AUTOCOMMIT=0 禁止自动提交

       SET AUTOCOMMIT=1 开启自动提交

触发器

能处理批量操作

四要素：1.监视地点(table)  2.监视事件(insert/update/delete)  3.触发时间(after/before)  4.触发事件(insert/update/delete)

注：触发删除事件事，如果删除有外键的表， 在删除前先删除关联表在删除外键表

create trigger triggerName
after/before   insert/update/delete on   表名
for each row   #这句话在mysql是固定的
begin
sql语句;
end;

索引

用于提高查询效率

分类：普通（index） 唯一（unique） 主键（primary key）

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次

视图

由查询结果得到的虚拟表，不可提高效率，可以使用户或开发人员只看到需要的数据，保证安全性。

create view view__name as select 。。。。

增删改语法和普通表一样，同时会影响原表，  删除视图时（不是数据删除）不会删除原表

部分视图是不可更新的，如查询结果带有函数（sum avg等），以及多表连接查询的

  存储过程
定义：SQL语句的集合（DML）。 避免我们开发人员重复写相同的语句
      存储过程执行是在我们的数据库中，减少了客户端和服务端的数据传输。

一.创建存储过程
create procedure sp_name(parameter。。) 注：参数：in  out  inout
begin
.........
end

二.调用存储过程
1.基本语法：call sp_name(parameter（in）。。)
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递


三.自定义变量：DECLARE   a INT ; SET a=100;    可用以下语句代替：DECLARE a INT DEFAULT 100;

CREATE PROCEDURE p4(IN s1 INT,OUT s2 INT,INOUT s3 VARCHAR(10))
BEGIN
DECLARE x1 VARCHAR(10) DEFAULT 'this is x1'; 
DECLARE x2 VARCHAR(10) DEFAULT 'this is x2'; 
SET s1 = 11;
SET s2 = 12;
SET s3 = 's3';

//循环四选一
/*
if语法
*/
IF s1 = 11 AND s2 = 12 THEN
SELECT s1,s2;
END IF;


IF s3 = 's3' OR s1 = s2 THEN
SELECT s3;
ELSE 
SELECT s1,s2,s3;
END IF;


/*
case语法
*/
CASE s3
WHEN 's1' THEN
SELECT 'this is s1';
WHEN 's2' THEN
SELECT 'this is s2';
ELSE
SELECT 'this is s3';
END CASE;


/*
while循环
*/
-- WHILE s1>1 DO
-- SET s1=s1-1;
-- END WHILE; 
-- SELECT s1;

/*
repeat循环语句
与while不同的是while满足条件就执行，repeat始终执行直到满足条件终止
*/
REPEAT  
SET s1 = s1-1; 
UNTIL s1=1  
END REPEAT; 
SELECT s1;


/*
LOOP循环
LOOP没有循环条件，会不停的循环直到遇到 "LEAVE ZiDingYi;" "ZiDingYi"是自定义的LOOP标记
*/
ZiDingYi:LOOP
SET s1 = s1+1;
IF s1 = 5 THEN
LEAVE ZiDingYi;
END IF;
END LOOP;

end；

//设置参数值

SET @p_in=1; 
SET @p_out=2;
SET @p_inout = 's3';

CALL p4(@p_in,@p_out,@p_inout);//调用

 游标

CREATE  PROCEDURE  copyData()
BEGIN
DECLARE  sname,sdept VARCHAR(10);
DECLARE  done int;
/*创建游标*/
DECLARE  sd CURSOR FOR  SELECT  student.name,student.department from student;
/*定义异常处理  必需*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
/** 打开游标*/
OPEN  sd;
/**使用循环操作游标*/
aa:LOOP
/**移动游标*/
FETCH sd INTO  sname,sdept;
/*业务逻辑操作*/
INSERT  into  student_temp VALUES(sname,sdept);
/*结束循环*/
IF done=1 THEN
LEAVE  aa;
END IF;
END LOOP;
/*关闭游标*/
CLOSE  sd;
END