mysql数据库

学习mysql数据库之前，首先要把数据库里的字符编码都统一设置成utf8,这样就能有效的防止在编码上出错的问题

1、查看mysql数据库相关的编码
SHOW VARIABLES LIKE ‘character%’;

2、设置编码
服务器：SET character_set_server=utf8;
数据库：SET character_set_database=utf8;

数据库基础

查询
sql语句编写顺序

select '列名'
from '表名'
where '条件'
group by '分组字段（根据谁来分组）'
having '过滤条件'
order by  '排序列(依据哪一列的数据进行排序)'
limit '起始行','总条数' #起始行的下标从0开始

sql语句执行顺序

from: '指定数据来源表（没有数据怎么查询，所以from是最先执行的）'
where:  '对查询的数据做第一次过滤（这个筛选处在数据还未取出来之前（也因为这个原因，where里面无法使用聚合函数），此时的过滤是一行一行的进行）'
group by : '分组'
having :'对分组后数据做第二次过滤，这时候过滤的就是一次一个的分组（也就是几行数组一起过滤，也有可能是一行，在这里可以使用聚合函数作为字段用于筛选）
having也必须写在分组之后，如果不分组的话写having，没有意义，where完全可以代替'
select: '查询各字段的值，等做完所有的筛选之后就把所有数据显示出来，
（其实select就象java中的print，把数据输出在控制台，但是它与print不一样，它不仅可以把数据输出在控制台，也可以把数据输入到一个变量中（select into 变量名））'
order by: '排序'
limit:'限定查询结果（放在最后）'

别名：as关键字，可以给表取别名，也可以给字段取别名，如果一次查询的时候需要用到多个表，那么这些表就需要用到别名，然后通过别名点字段去指定字段。

去重：distinct 写在select之后，它的去重方法根据select所有字段都相同，那么则算作重复

排序，升序、降序
order by 使用排序的时候查询的字段不能是除了被指定为排序依据字段的其他字段，（也就是只能查询这个依据字段，以及聚合函数，因为经过排序，那么查询的数据个数就会根据这个排序依据字段的重复数进而减少，假如查询其他的字段只会查询到每个分组的第一个值，这样查询就不是我们想要的结果）
asc 默认升序，desc降序
如有多个排序依据，用逗号分隔

逻辑判断 多个条件之间的连接符号用 and(&&)、or(||)、not(!);

模糊查询： 关键字like “%“表示0到n个字符，"_"表示一个字符（可以多写）

分支结构：

case
when '条件1' then '结果1'   #自动break；我需要手动添加
when '条件2' then '结果2'
else '结果3'  #以上条件均不满足时.

条件判断

if 'bool判断语句' 
	then '满足条件时执行的语句'
elseif  '另一个bool判断语句'  #注意elseif是连着写得
	then '满足条件时执行的语句'
else then '执行语句'

循环语句

#一般使用在存储过程中，所以就直接在下面声明变量了

declare num int default 1;
out_loop:loop   #loop循环开始的地方，out_loop循环的别名，用于退出循环
   num=num+1;   #设置迭代
   if num=2 then leave out_loop; end if;  #当num等于2的时候退出循环，leave相当于java中的break打断循环（退出循环） 
   iterate out_loop;  #相当于java中的conntinue跳过本次循环后面的语句进入下一次循环
end loop;

时间查询：
select sysdate();
year(date)； //括号后面是日期类型数据，取得当前年份。
datediff(date1,date2) //获取两个日期之间相隔天数
adddate(date,n) //把什么日期增加n天

字符串连接：
concat(str1,str2,str3);

聚合函数：
sum(); //括号内填写字段名
avg();
max();
min();
count();

子查询：
把查询结果作为筛选条件，用小括号括起来。

枚举查询条件：
假如子查询有多个值，而我的只需要满足其中一个值就能取出来，这时就可以使用关键字:in(子查询结果或者多个值)；
当我需要查询的结果要大于子查询的所有人时，就在子查询前面添加all,大于一个时，添加any

合并查询：
关键字 unoin联合两个查询语句，但是有一个限制，两个查询语句查询的字段个数要一样，查询出来之后就变成了一个表，后面查询的结果就显示在表的后半部分。
union会去重复 union all不会去重复

连接查询（重点）:

#条件：两个表之间需要有相同的字段
#连接两个表 sql正规写法
SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.tno=t2.tno;
#连接两个表的 mysql能编译的写法
SELECT * FROM table1 t1,table2 t2 WHERE t1.tno=t2.tno;
#在连接两个表时没有后面的连接条件，那么连接的结果就会根据笛卡尔积来计算

#左外连接
SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.tno=t2.tno;
#右外连接
SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON t1.tno=t2.tno;

#分别以左、右表为主，其他连接的表用null补全空位，或者缩短表的数据。

增、删、改

#增
insert into '表名'('字段名','字段名','字段名') value ('值','值','值')
#值的个数与字段数相匹配.
#在没有写字段名的情况下，value里面的个数要和表的字段个数相同，如字段有默认值，则可以用default填充。

#修改
#修改ing学生字段的数据类型及长度和解释
ALTER TABLE student MODIFY stu_ing VARCHAR(10) COMMENT '学生日期';
#修改ing的字段名
ALTER TABLE student CHANGE stu_ing stu_birth VARCHAR(20) COMMENT '真正的学生日期';
#comment 该字段的注释
#修改student表明
ALTER TABLE student RENAME student_2;
#删除字段名
ALTER TABLE student_2 DROP stu_birth;
#删除学号为02的学生信息
DELETE FROM student WHERE sid ='02';

数据类型
int
double(m,d) m:浮点数的总长度，d:小数点后的数据长度
decimal(m,d) 浮点数的精确值用作运算。
date
char 不可变
varchar 可变字符串这个可变指的是会把剩余的空间收回，不能超过定义的长度。
blob 二进制文件
text 长文本数据

创建表

#创建员工表
CREATE TABLE emp(
  emp_id INT(8) PRIMARY KEY AUTO_INCREMENT,   #primary key 主键，auto_increment 设置自增，如果在存贮的时候没有值或者是default，那么他就会在已知的最高数字上加1存入表中。
  emp_name VARCHAR(20) NOT NULL, #设置非空
  emp_salary DOUBLE(10,2) DEFAULT 8000, #设置默认为8000
  dno INT(4) NOT NULL,
  CONSTRAINT fk_dept_dno FOREIGN KEY emp(dno) REFERENCES dept(dno)   #设置dno为外键
);

#设置自增的初始值为1000  这个设置每个表可以不一样
alter table emp auto_increment=1000;

数据表的修改

#向现有表中添加列：
alter table subject add '字段名' '数据类型' #后面可以添加主键，注释，非空，默认值等。
#修改ing学生字段的数据类型及长度和解释
ALTER TABLE student MODIFY stu_ing VARCHAR(10) COMMENT '学生日期';
#修改ing的字段名
ALTER TABLE student CHANGE stu_ing stu_birth VARCHAR(20) COMMENT '真正的学生日期';
#修改student表明
ALTER TABLE student RENAME student_2;
#删除字段名
ALTER TABLE student_2 DROP stu_birth;
#删除表格
DROP TABLE student_2;

视图

#创建视图，as后面是一段查询语句
CREATE VIEW aa AS
SELECT * FROM category;
#用视图查询
SELECT * FROM aa;
#改变视图
ALTER VIEW aa AS SELECT * FROM orderitem;
#删除视图
DROP VIEW aa;

#视图就相当于把查询过来的数据写在一个临时表中，但是真正的表，然后其他的查询语句就能通过这个临时表查询数据。

存储过程（重点）

-- 练习创建存储过程
-- 声明存储过程的结束符  如果不声明结束符，默认就是分号结束，那么将无法存储多条语句，下面将结束符设置为两个钱符号
DELIMITER $$
-- 创建存储过程
CREATE PROCEDURE demo()
-- 存储过程的开始位置 begin
BEGIN
-- 声明局部变量，并给一个局部变量添加初始值
DECLARE `name` VARCHAR(20) DEFAULT '张三';
DECLARE age INT(4);   #声明变量和java一样，使用前需要先赋值
-- 给局部变量赋值
 SET `name`= '李四';  #变量赋值只能用set
SET `age`= 18;
-- 输出变量的值
SELECT `name`;
SELECT `age`;
END $$    #结束过程

-- 调用存储过程
CALL demo;


#存储过程能把多条语句包装起来，之后调用过程的时候就等于同时调用了多条语句，
#SQL语句也是通过先编译，后执行的步骤，而过程在创建之后就直接编译，连带着里面的查询语句也会编译完成，这是在调用的时候执行时效率就会块一点
#缺点是不能改变，如需改变就只能删除重创。

使用输入输出参数创建存储过程

-- 测试传参，传入一个参数和传出一个参数
DELIMITER $$
CREATE PROCEDURE demo1(IN name1 VARCHAR(20),IN name2 VARCHAR(20),OUT message VARCHAR(60))  #参数可以有多个，参数的定义分三部分，第一个声明输入输出参数
BEGIN 
  SELECT CONCAT(name1,'.',name2,cname) INTO message FROM category WHERE cid='c002';
END $$

SET @messag='';  #声明用户变量，相当于java中的成员变量，在整个查询文件中都可以使用，声明之后需要付初始值（特点：前面有个@符号）
CALL demo1('张','三',@message);  #调用过程并传入相应的参数，其中out参数需要使用用户变量来接收值
SELECT @message;  #输出用户变量的值

游标（重点）

用来把查询得到的数据一行一行的调用

-- 使用游标
#游标一般也写在过程里面
DELIMITER $$
CREATE PROCEDURE demo6(IN `name` VARCHAR(20))
BEGIN
-- 声明一个变量用来指示下一个是否找到
  DECLARE end_fetch INT DEFAULT 0;
  -- 我已经知道我声明的游标遍历的时候每一行都能拿出来三个数据，所以我要声明三个变量又来存储数据
  DECLARE sid VARCHAR(20);
  DECLARE cid VARCHAR(20);
  DECLARE sscore INT(3);
  -- 声明游标  关键字是 cursor for  后面接一条查询语句
  DECLARE yb CURSOR FOR
    SELECT sc.s_id,sc.c_id,sc.s_score 
    FROM student st,score sc 
    WHERE st.s_id=sc.s_id 
    AND st.s_name=`name`;
  -- 处理未找到情况（not found） 游标将在下面逐行往下找，当他发现后面没有数据之后就会执行下面一条语句（我理解是这样，我觉得下面的这条语句应该是游标的一个触发器，在下面声明）
  -- 当后面没值的时候把end_fetch设置为1，代表游标的功能执行完毕了，该退出循环。  关键字 continue handler for not found
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_fetch =1;
  -- 打开游标
  OPEN yb;
  -- 循环检索游标数据  如果没有循环，游标只执行一次
  out_loop:LOOP
    -- 检索游标指向数据行,游标下的查询语句有多少列，
    -- 那么每一行查询出来的数据就有多少个，然后就需要多少个变量去存储，
    -- 或者放到其他的数据库里  关键字fetch 相当于java中迭代器里的next()
    FETCH yb INTO sid,cid,sscore;
    IF end_fetch=1 THEN LEAVE out_loop;
    END IF;
    -- 把读取到的三个值插入到分数副表中
    INSERT INTO score1 VALUE(DEFAULT,sid,cid,sscore);
  END LOOP;
  CLOSE yb;
END $$

CALL demo6('钱电');

-- 创建一个分数副表，用来存储使用游标读到的值
CREATE TABLE score1(
  s_id VARCHAR(20) NOT NULL,
  c_id VARCHAR(20) NOT NULL,
  s_score INT(3) NOT NULL
);

-- 为分数副表添加一个字段，用于自增
ALTER TABLE score1 ADD xuhao INT(4) PRIMARY KEY AUTO_INCREMENT;

-- 删除分数副表的序号字段
ALTER TABLE score1 DROP xuhao;

-- 为分数副表添加字段，并且添加到称为第一个字段
ALTER TABLE score1 ADD xuhao INT(4) PRIMARY KEY AUTO_INCREMENT FIRST;

-- 创建触发器
-- 触发器设置在每个表上面，每当有连接更改这张表，就会根据触发器的语法做出相应的操作
CREATE TRIGGER cfq BEFORE INSERT ON score1 FOR EACH ROW
INSERT INTO jilu VALUE(DEFAULT);

-- 触发器可以为表设置当表有被修改、添加、删除等操作时，触发执行操作，并且可以设置在他们触发前后进行操作。