Mysql学习笔记

一、对数据库的操作

1. 进入Mysql数据库

mysql -h 主机名 -u 用户名 -p

解释：

• -h：后面接的是主机名，表示你要连接到哪台主机的 MySQL数据库。本机为localhost
• -u：后面接的是用户名，MySQL默认的就是root。
• -p：指定需要使用密码登陆MySQL数据库，如果密码为空，该参数可以省略。

2. 查看已有的数据库

show databases;

3. 创建数据库

create database 数据库名;

4. 选择要操作的数据库

use 数据库名[;] //加不加分号都是可以的。

5. 修改MySQL数据库的登陆密码

mysqladmin -r root -p password 新密码

6. 在登陆MySQL数据库时指定要操作的数据库

mysql -D 选择要操作的数据库名 -h 主机名 -u root -p

7. 通过文件的方式创建数据库表

mysql -D 选择要操作的数据库名 -h 主机名 -u root -p < .sql类型文件

二、对数据库表的操作

1. 在数据库中创建数据库表

create table 数据库表名(列声明);

列声明语句	解释说明
id char(10) not null primary key	创建一列，名称为id；数据类型为char字符类型，字符的最大长度为10个字符；并且该列内容不允许为空；同时把这一列作为这张表的主键，用来区分表中不同行。
name char(16) not null	创建一列，名称为name；数据类型为char字符类型，字符的最大长度为16个字符；并且该列内容不允许为空。
sex char(6) not null	创建一列，名称为sex；数据类型为char字符类型，字符的最大长度为6个字符；并且该列内容不允许为空。
age int not null	创建一列，名称为age；数据类型为int整型；并且该列内容不允许为空。
address char(36) not null	创建一列，名称为sex；数据类型为char字符类型，字符的最大长度为36个字符；并且该列内容不允许为空。

1.1 列名称

每一列的名称必须是不相同的才行。

1.2 数据类型

• 1.2.1 数值类型

类型	大小	用途
TINYINT	1 字节	小整数值
SMALLINT	2 字节	大整数值
MEDIUMINT	3 字节	大整数值
INT或INTEGER	4 字节	大整数值
BIGINT	8 字节	极大整数值
FLOAT	4 字节	单精度浮点数值
DOUBLE	8 字节	双精度浮点数值
DECIMAL	DECIMAL(M,D)	小数值

• 1.2.2 日期和时间类型

类型	大小	格式	用途
DATE	3	YYYY-MM-DD	日期值
TIME	3	HH:MM:SS	时间值或持续时间
YEAR	1	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

• 1.2.3 字符串类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777215字节	中等长度文本数据
LONGBLOB	0-4294967295字节	二进制形式的极大文本数据
LONGTEXT	0-4294967295字节	极大文本数据

1.3 建表约束

• 1.3.1 主键约束

  •   primary key
    

  他能够唯一确定一张表中的一条记录，也就是我们通过某个字段添加约束，就可以是的字段不重复且不为空

  • 使用方式 1：

      create table user(
      Id int(20) primary key, 
      Name vachar(20)
      );
    

  • 使用方式 2：联合主键—2个主键值不重复且不为空

      create table user2(
      Id int(20) ,
      Name varchar(20),
      Primary key(id,name)
      ); 
    

• 1.3.2 自增约束

    auto_increment
    Create table user3(
    Id int primary key auto_increment,
    Name varchar(20)
    );
  

  • 如果创建表的时候忘记创建主键

      create table user4(
      Id int(20),
      Name varchar(20)
      );
    

  • 修改表结构

      alter table user4 add primary key(id);
    

  • 使用alter删除约束

      alter table user4 drop primary key;
    

  • 使用modify修改字段，添加约束

      alter table user4 modify id int primary key;
    

• 1.3.3 唯一约束

  • 约束字段的值不可以重复

      create table user5(
      Id int(20),
      Name varchar(20)
      );
    

  • 添加唯一约束

      alter table user5 add unique(name);
    

  • 创建表的时候添加

      create table user6(
      Id int(20) unique,
      Name varchar(20)
      );
    

  • 两个字段组合在一起不重复

      create table user7(
      Id int(20),
      Name varchar(20),
      Unique(id,name)
      );
    

  • 删除唯一约束

      alter table user7 drop index name;
    

  • modifiy添加

      alter table user7 modify name varchar(20) unique;
    

• 1.3.4 非空约束

修饰的字段不能为空NULL

    create table user8(
    Id int(20),
    Name varchar(20) not null
    );

• 1.3.5 默认约束

当插入字段值的时候，如果没有传值，就会使用默认值

    create table user9(
    Id int(20),
    Name varchar(20),
    Age int(20) default 10
    );

• 1.3.6 外键约束

设计到两个表：父表-子标、主表-副表

• 举例：班级-学生

    Create table classes(
    Id int primary key,
    Name varchar(20)
    );
    Create talbe students(
    Id int primary key,
    Name varchar(20),
    Class_id int,
    Foreign key(class_id) references classes(id)
    );
  

  • 主表classes中没有的数据，在副表中式不可以使用的
  • 主表中的记录被副表引用，是不可以被删除的

1.4 数据库表的设计范式

• 1.4.1 第一范式INF

  • 数据库表中的所有字段都是不可分割的原子值
  • 如果字段值还可以继续拆分的，就不满足第一范式
  • 范式，设计得越详细，对于某些实际操作可能更好，但是不一定都是好处
  • 最终以实际开发来确定

• 1.4.2 第二范式

  • 必须是满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键
  • 如果要出现不完全依赖，只可能发生在联合主键的情况下
  • 除主键以外的其他列，只依赖与主键的部分字段
  • 如果不满足就需要拆表

• 1.4.3 第三范式

  • 必须先满足第二范式，除开主键列的其他列之间不能有传递依赖关系

2. 修改数据库表

2.1 修改数据库表中的列

  alter table 数据库表名 change 列名称 新数据类型 [其它];

例如：

• 将列名称"adress"修改为"addr"，其它保持不变

    alter table 数据库表名 change address addr char(30) not null;
  

• 将列"name"的数据类型修改为最大可以存放20个字符的char类型，其它保持不变

    alter table 数据库表名 change name name char(20) not null;
  

• 同时修改列"sex"的名称和数据类型

    alter table 数据库表名 change sex Sex char(10) not null;
  

2.2 增加数据库表中的列

  alter table 数据库表名 add column 新列名称 新列数据类型 [其他];

例如：

• 增加到第一列

    alter table 数据库表名 add column 新列名称 新列数据类型 not null first;
  

• 增加到指定列后面

    alter table 数据库表名 add column 新列名称 新列数据类型 not null after 指定列;
  

2.3 删除数据库表的列

  alter table 数据库表名 drop 列名称;

• 举例：删除addr这一整列

    alter table 数据库表名 drop addr;
  

2.4 重命名数据库表

  alter table 原数据库表名 rename 新数据库表名;

2.5 删除数据库表

  drop table 数据库表名;

3. 删除数据库

  drop database 数据库名;//直接删除这个数据库

三、对数据库表中的数据的操作

1. 添加数据

    insert into 数据库表名 values(value值1,value值2,.......); //插入行
    insert into 数据库表名 (列名1,列名2,...） values(value值1,value值2,...); //操作列

2. 查询数据

    select 列名称 from 数据库表名 [查询条件];

2.1 查询表中全部数据

	select * from 数据库表名 [查询条件];

2.2 查询表中特定列的数据

	select 列名称1，列名称2... from 数据库表名 [查询条件];

2.3 按特定条件查询表中的数据

	select [参数] 列名称 from 数据库表名 where 查询条件;

• 常用关键字：

名称	关键字	用法
增加	insert	insert into user(name,age,sex) values(值1,值2,值3)；
删除	delete	delete from user where 条件;
修改	update	update user set 字段1=值1，字段2=值2 where 条件;
查询	select	select * from user;
去重	distinct	select distinct 去重字段 from user;
在···之间	between	select * from user where between age 20 and 30; （查询年龄在20-30之间的用户）
模糊匹配	like	select * from user where name like ‘张_%’; （其中_匹配 一个字符，%匹配 一个或多个）
分页查询	LIMIT	Select * from user LIMIT 5,10; (查询第5-第10个记录行)
记录条数	count	select COUNT(*) from user; (查询user表所有记录条数)
求和	sum	select sum(age) from user;(查询所有的年龄和)
最大最小值	max、min	select max(age) from user;(最大的年龄最小同理)
平均值	avg	select avg(age) from user;(所有人年龄的平均值)
排序	order by	select * from user order by age;(默认从小到大的正序， asc 正序，desc倒序)
分组	group by	select sex,count(*) from user group by sex;（分组查询男女总人数）
分组后筛选	having	其实与where用法相似，having后能用聚合函数where不行，分组筛选后建议用having关键字
别名	as	Select name as tname from user;(将name别名为tname)

• 查询例子：

  • 查询表中age为50-80的数据

      (省略) where age between 50 and 80; 
    

  • 使用运算符进行比较

      (省略) where age>=50 and age <=80 ; 
    

  • (相同字段中)表示或者关系的查询 in，查询年龄为60、67、78的数据

      (省略) where age in(60,67,78);
    

  • (不同字段中)查询表中 性别为女 或者 年龄为17 的记录

      (省略) where sex=’女’ or age=17;
    

  • 升序、降序查询

      (省略) order by class desc; 降序
      (省略) order by class [asc]; 升序，默认就是升序，asc可以不用写
    

  • 以age升序，money降序进行查询

      (省略) order by age ase,money desc;
    

  • 查询age为17的学生人数

      select count(*) from 表名 where age=17;
    

  • 查询age最高的人的name和sex (子查询)

      select name,sex from 表名 where age=(select max(age) from 表名);
    

  • 查询女性的平均年龄

      select avg(age) from 表名 where sex=’女’;
    

  • group by分组查询

     select sex,avg(age) from 表名 group by sex;
    

  • 查询表中名字中有张，同年龄段至少有2个以上的平均money

      select name,avg(money) from 表名 group by age having count(age)>=2 and name like ‘张%’;
    

  • 多表联查(表1的id和表2的id相同)

      select name,age,address from 表1,表2 where 表1.id=表2.id;
    

  • 子查询查询age=30的平均money

      select money from 表名 where age=30;
      select sex,avg(money) from 表名 where money in (Select money from student where age=30) group by sex;
    

2.4 连接查询

• 前提表：

  • Person表：id，name，cardid
  • Card表：id，name

• 2.4.1 内连接inner join 或者 join

  • 内连接查询，其实就是两张表中的数据，通过某个字段相对，查询出相关数据

      select * from person inner join card on person.cardid=card.id;
    

• 2.4.2 外连接

  • 2.4.2.1 左外连接 left join 或者 left outer join

    • 左外连接，会把左边表里面的所有数据提取出来，而右边表中的数据，如果有相等的，就会显示出来，如果没有就会补NULL

      select * from person left join card on person.cardid=card.id;
      

  • 2.4.2.2 右连接 right join 或者 right outer join

    • 右外连接，会把右边表里面的所有数据提取出来，而右边表中的数据，如果有相等的，就会显示出来，如果没有就会补NULL

      select * from person right join card on person.cardid=card.id;
      

  • 2.4.2.3完全外连接（在mysql中通过左连接和外连接实现）

      select * from person left join card on person.cardid=card.id
      union
      select * from person right join card on person.cardid=card.id;
    

3. 修改数据库表中的数据

update 数据库表名 set 列名=新value where 更新条件;

• 例如：

  update 数据库表名 set age=age+1;//将所有人的age加1
  update 数据库表名 set name="Li" where id='1';//将id为"1"的name修改为”Li”
  

4. 删除数据库表中的数据

delete from 数据库表名 where 删除条件;

• 例如：

  delete from 数据库表名 where name=’za’;//把表中name为" za "的一行数据删除
  delete from 数据库表名;//将表中全部数据清空
  

四、mysql的事务

1.含义：

• 在mysql中，事务是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性

2. 控制事务

• 2.1 事务开启：

  • 修改默认提交 set autocommit=0;
  • begin; 或 start transaction;

• 2.2 事务手动提交

  • commit;

• 2.3 事务手动回滚

  • rollback;

3. 事务的四大特性

• 3.1 原子性：

  • 事务是最小的单位，不能再被分割

• 3.2 一致性：

  • 事务要求，同一事务中的sql语句，必须保证同时成功或同时失败

• 3.3 隔离性：

  • 事务1 和事务2 之间是具有隔离性的

• 3.4 持久性：

  • 事务一旦结束(commit,rollback)，就不可以返回

4. 事务的隔离性详解

• 4.1 查看数据库的隔离级别

  • 4.1.1系统级别的

      select @@golbal.transaction_isolation;
    

  • 4.1.2 会话级别

      select @@transaction_isolation;
    

• 4.2 read uncommitted 读未提交的

  • 如果有事务a和事务b。事务a对数据进行操作，再操作的过程中，事务没有被提交，但是b可以看见a操作的结果——脏读
  • 脏读：一个事务读到了另一个事务没有提交的数据，就叫脏读

• 4.3 read committed 读已提交的

  • 如果有事务a和事务b。事务a对数据进行查询，但是在事务a查询后，紧接着事务b修改了数据，此时事务a前面所查询的数据和事务b提交后的数据不一致。——不可重复读
  • 不可重复读：读取同一个表的数据，读取时和操作时两个时刻的数据不一样

• 4.4 repeatable read：可以重复读

  • 如果有事务a和事务b。事务a和事务b先读取了一次数据，然后事务a插入了一条数据，随后事务b根据先前查询的数据，插入同一条数据却失败。——幻读
  • 幻读：同时操作一张表，事务a提交的数据，没有被事务b读到。

• 4.5 serializable 串行化—安全性高，性能效率低

  • 当表被一个事务a操作的时候，其他事务里面的写操作无法进行，会进去排队状态，直到事务a执行结束后，其他的事务才会执行。（在等待没有超时的情况下）

• 4.6 隔离与性能：

  • 隔离级别越高，安全性越好，但性能越差