mysql笔记

连接数据库
mysql -h 地址 -P 端口 -u 登录名 -p 密码

数据类型

• 字符串
  char：定长字符串
  varchar：变长字符串
  tinyblob：不超过255个字符的二进制字符
  tinytext：短文本字符串
  blob：二进制形式长文本数据
  text：长文本数据
  mediumblob：二进制
  mediumtext：中等长度文本数据
  longblob：二进制大文本数据
  longtext：大文本数据

• 数值型
  tinyint
  smallint
  mediumint
  int或integer
  bigint
  float
  double
  decimal

• 日期和时间类型
  date
  time
  year
  datetime
  timestamp

关系运算符
关系运算符在 where 之后。
> = >= < <= !=

逻辑运算符
与 and ，或 or ，非not

创建库
create database db;

删除库
drop database db;

创建库指定编码
create database db character set utf-8;

创建表(table)
create table 表名 ( 字段1 数据类型， 字段2 数据类型，... );
默认值 ：default
非空：not null
主键：primary key 唯一
唯一键：unique 不能重复
自动增长：auto-increment 尽量用int

添加(insert)
第一种：
insert into 表名 ( 字段1 ， 字段2 ，... ) values (值1，值2，...);
第二种：
insert into 表名 set 字段 = 值 ,...;
第三种：
insert into 表名 ( 字段1 ， 字段2 ) values (值1，值2), (值1, 值2) ,(值1，值2);

删除(delete)
delete from 表名 where 条件;

查询(select)
select 字段1，字段2，... from 表名 [where 条件];

修改(update)
update 表名 set 字段= 值，... where 条件;

sql语句分类

• 数据库查询语言(DQL)： select
• 数据库定义语言(DDL)： create database, drop database;
• 数据库操作语言(DML)： update， insert ，delete

查询指定数据库详细信息

• show create database 库名;
• show databases; //所有库的详细信息
• select database(); //查看当前用户连接哪个数据库
• show tables; //查看当前库下的所有表
• desc 表名; // 查表结构

修改库结构(alter)
alter database 库名 character set [gbk]; //修改库编码

修改表结构(alter)

rename table 旧表名 to 新表名; //重命表名
alter table 表名 add 字段 数据类型; //加字段
alter table 表名 drop 字段  // 删字段
alter table 表名 change 旧字段名  新字段名  类型; //字段重命名

数据库备份和还原
第一种：通过命令
mysqldump -u -p 需要备份的数据库名 > 备份后的sql脚本名
第二种：通过可视化工具备份

排序(order by)

中文不能排序，英文可以排序

• 降序 desc
• 升序 asc

聚合函数

• 最大值 max(字段)
• 最小值 min(字段)
• 平均值 avg(字段)
• 求和 sum(字段名)
• 总条数 count(字段名)

常用函数

select now();   // 当前系统时间
select curdate(); // 当前日期
select curtime(); // 当前时间
select week(date) // 返回第几周
select year(date) // 返回年份
select hour(date) // 返回小时
select minute(date) // 返回分钟
select datediff(date1, date2) // 相隔天数
select adddate(date, n) // 加上n天后的日期

数学函数

select ceil(x)  // 向上取舍
select floor(x) // 向下取舍
select rand()  // 返回随机数 -小数

查多条

select * from 表  where 字段  in (数值，数值，数值);
select * from 表  where 字段  not in (数值，数值，数值);

分组查询(group by)

select 字段 from 表 group by 字段;
// 带条件
select 字段 from 表 group by 字段 having 字段 = 数值;

分页

select * from 表 limit index(起始下标) ， pageSize(每页显示);
// index从零开始
index(起始下标) = (pageNo当前页号 - 1) * pageSize(每页显示;

时间格式化函数

date_format(birthday , %Y年%m月%d日%H:%i:%s);

多表查询

union // 自动去重
union all // 不能自动去重

内连接 inner join
外连接

• 左外连接 left join ： 以左边表为主， 左表显示 右表为null
• 右外连接 right join： 以右表为主， 右表显示 左表为null

模糊查询

like ‘%字段’;   // 以字段结尾
like ‘字段%’;  // 以字段为开头

视图(view)
只有查询权限
创建视图

 create view  

删除视图

drop view

添加视图

insert into 

修改视图

update 

数据库建模(powerdesigner)
file --> new model --> physical data model

事务

• 原子性 ；多组操作为一个整体, 不能分割。
• 一致性 ： 操作前后最终的总量一样。
• 隔离性 ： 多个事务之间相互隔离互不干扰

mysql 四种隔离级别：

1. read uncommitted
2. read committed
3. repeatable read
4. serializable

查看mysql隔离级别 ：select @@tx_isolation; 修改默认隔离级别 set global transaction isolation level 隔离级别;

1. 脏读： 当mysql隔离级别为 read uncommited 时 会引发脏读。 含义：一个事务可以读取另一个事务未提交的数据。 如何解决？ 改为 read committed
2. 虚读与幻读 ： 当隔离级别为 repeatable read时 会引发虚读(幻读)。
3. 不可重复读 ： 当mysql 事务隔离级别为 read committed 的时候 会引发不可重复读。 含义： 在同一个事务中多次读取结果不一致。

• 持久性 ： 数据进入到数据库中， 数据需要持久存在

// 开启事务
start transaction;
// 提交事务
commit;
// 回滚
rollback;

存储过程
一组sql语句的集合。

delimiter  //    # 以//开头
create procedure  方法名(参数)
begin
       sql语句;
end  //    # 以//结尾
delimiter;

调用存储过程

call 存储过程(参数名);

删除存储过程

drop procedure [if exists] 存储过程名;

带返回值的存储过程

例：求和 传递两个float类型的形参，返回两个数的和。

// in 为入参 可省略   out 为出参
delimiter //
create procedure test(in i float, j float, out num float)
begin
      set num = i+j;
end //
delimiter;

call test(10,, 20, @result);
select @result;

存储过程判断语句

if(条件) then
     代码块;
end if;
//  带else if
if(条件) then
     代码块;
else if(条件) then
     代码块;  
else
     代码块;        
end if;
//  带else
if(条件) then
     代码块;
else 
     代码块;     
end if;
// case
case i
   when 1 then
      ....;
   when 2 then
      ...;
   else 
      ...;      

存储过程循环

// 声明变量
 declare  a  int;   
 // 两种赋值方法
 // 第一种
 declare a  int default 1;
 // 第二种
 declare a int;
  set a = 1

while a<= 1 do
     代码块;
 end while;

loop 循环别名 : loop
      循环体;
      leave loop  循环别名;
end loop;