MySQL完整版详解

一、数据库的操作

1.创建数据库

若在可视化软件上创建数据库，参考如下图
如果要创建的数据库不存在，则创建成功

1. create database if not exists westos;

复制代码

2.删除数据库

1. drop database if exists westos;

复制代码

3.使用数据库

1. use tmalldemodb;
2. //tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``

复制代码

4.查看数据库

1. show databases;

复制代码

5.清空当前命令行界面命令

1. clear;

复制代码

6.创建表

• auto_increment：自增
  
• 字符串使用单引号括起来
  
• 所有语句后面加逗号，英文的，最后一个不用加
  
• primary key主键，一般一个表只有一个唯一的主键，且必须要有
  
• engine=innodb：数据库表的引擎
  

1. mysql> create table if not exists student(
2.     -> id int(4) not null auto_increment comment '学号',
3.     -> name varchar(30) not null default '匿名' comment '姓名',
4.     -> pwd varchar(20) not null default '123456' comment '密码',
5.     -> sex varchar(2) not null default '女' comment '性别',
6.     -> birthday datetime default null comment '出生日期',
7.     -> address varchar(100) default null comment '家庭地址',
8.     -> email varchar(50) default null  comment '邮箱',
9.     -> primary key(id)
10.     -> )engine=innodb default charset=utf8;

复制代码

创键成功之后的图

7.常用命令

(1)查看创建数据库的语句

1. show create database 数据库名;

复制代码

运行结果图

(2)查看表的创建语句

1. show create table 表名;

复制代码

(3)显示表的结构

1. desc 表名；

复制代码

8.数据表的类型

(1)数据库的引擎

1. innodb //默认使用
2. myisam//早些年使用的

复制代码

• 事务：假设有两个SQL都执行，要么都成功，要么都失败，如果有一个成功一个失败，它就提交不上去
  
• 数据行锁定：假设有两条SQL去查同一个表，他会把这个表先锁住，剩下的数据再查的时候就要排队等待
  
• 外键约束：一张表连接到另一张表
  
• 全文索引：比如在百度上根据你输入的关键词(在数据库叫字段)区搜索你想要的结果
  

myisaminnodb事务支持不支持支持数据行锁定不支持 （支持表锁）支持外键约束不支持支持全文索引支持不支持表空间的大小较小较大，约为myisam的2倍常规使用操作：

• myisam 节约空间，速度较快
  
• innodb安全性高，事务的处理，多表多用户操作
  

(2)在物理空间存在的位置

所有的数据库文件都存在data目录下本质还是文件的存储！
9.修改和删除表的字段

(1)修改表名

alter table 旧表名 rename as 新表名;

1. alter table teacher rename as teacher1;

复制代码

(2)增加表的字段

alter table 表名 add 字段名 列属性;

1. alter table teacher1 add age int(11);

复制代码

(3)修改表的字段（重命名，修改约束）

①修改约束modify(不能重命名)：alter  table 表名 modify 字段名 新的列属性;

1. alter table teacher1 modify age varchar(11);

复制代码

②字段重命名（既可以给字段重命名，又可以修改约束）
alter table 表名 change 旧字段名 新字段名 列属性;

1. alter table teacher1 change age age1 int(11);

复制代码

(4)删除表的字段

alter table 表名 drop 字段名;

1. alter table teacher1 drop age1;

复制代码

(5)删除表

如果要删除的表存在，则删除drop table if exists 表名;

1. drop table if exists teacher1;

复制代码

所有的创建和删除操作都尽量加上判断，以免报错
二、列的数据类型详解

1.数值

• tinyint    十分小的数据  1个字节
  
• smallint   较小的数据 2个字节
  
• int           标准的整数 4个字节      常用
  
• bigint      较大的数据  8个字节
  
• float         浮点数        4个字节
  
• double     浮点数         8个字节
  
• decimal      字符串形式的浮点数
  

2.字符串

• char       字符串固定的大小       0-255
  
• varchar       可变字符串      0-65535      常用
  
• tinytext        微型文本       2^8-1
  
• text        保存大文本       2^16-1
  

3.时间和日期

• date       日期格式：YYYY-MM-DD
  
• time       时间格式：HH:mm:ss
  
• datetime       日期格式：YYYY-MM-DD      HH:mm:ss      常用
  
• timestamp      时间戳，从1970.1.1至现在的毫秒数     常用
  
• year      年份表示
  

4.null

• 没有值，未知
  
• 注意，不要使用NULL进行运算，结果为NULL
  

三、数据库的字段属性（重点）

1.unsigned

• 无符号的整数
  
• 不能声明为负数
  

2.zerofill

• 0填充的
  
• 假设你现在要写一个长度为10的int类型，但是你只写了个1，则他会用自动给你在1前面填充9个零
  

3.自增

• 通常理解为自增，自动在上一条记录的基础上+1（默认）
  
• 通常用来设计唯一的主键~index,必须是整数类型
  
• 可以自定义设置主键自增的起始值和步长
  

4.非空

• 假设设置为not null ，如果不给它赋值，就会报错！
  
• Null,如果不填写值，默认就是null！
  

5.默认

• 设置默认的值
  
• sex,默认值为男，如果不指定该列的值，则会有默认的值！
  
• 设置默认的值
  

四、MySQL数据管理

1.外键(了解即可)


删除有外键关系的表的时候，必须要先删除字表，才能删除父表
2.DML语言（全部背住）

• insert
  
• update
  
• delete
  

3.添加

insert into 表名(字段名1，字段名2，字段名3，....) value(值1，值2，值3，....)==注意：一般写插入语句，我们一定要数据和字段一一对应！==(1)插入一行数据

1. insert into grade(gradename) value('大三');

复制代码

(2)插入多行数据

==注意：给一个字段添加多行值时，每个值都用括号括起来，且中间用逗号隔开。==

1. insert grade(gradename) value('大二'),('大一');

复制代码

1. insert into student(name,pwd,sex)  values('张三','aaaa','男'),('李四','vvvv','女');

复制代码

4.修改

(1)修改一条数据

格式：update 表名 set colnum_name = value where 条件下面这行代码的意思是将student表中id=2的name值设置为TWQ

1. update student set name='TWQ' where id =2;

复制代码

(2)修改多条数据

条件：where子句，运算符id等于某个值，大于某个值，或在某个区间内修改操作符含义范围结果=等于5=6false或！=不等于56true</tdtd小于/tdtd56/tdtdtrue/td/trtrtd/tdtd大于/tdtd5>6false=6falsebetween a and b在a到b这个闭包区间内[a,b]trueand我和你 &&5>1  and 1>2falseor我或你5>1  or 1>2true5.删除

(1)delete命令

语法：delete from 表名 where  条件;

1. delete from student where id =1;

复制代码

(2)truncate命令

作用：完全清空一个数据库表，表的结构和索引不会变

1. truncate table student;

复制代码

(3)truncate和delete命令区别

• 相同点：都能删除数据，都不会删除表结构
  
• 不同：①truncate重新设置自增列，计数器会归零。②truncate不会影响事务
  使用delete删除表中所有数据，不会影响自增的值
  拓展（了解即可）关于delete删除的问题：删除后重启数据库
  
• 若该表使用的引擎为innodb，重启数据库后，自增列会从1开始（存在内存当中断电即失）
  
• 若采用的引擎是myisam 重启数据库后，会继续从上一个自增变量开始（存储在文件中，不会丢失）
  

1. delete from grade;

复制代码

使用truncate删除表中所有数据，自增变量会归零

1. truncate table grade;

复制代码

五、DQL查询数据（重点中的重点）

select语法


5.1指定查询字段

(1)查询某个表中全部的数据

如查询student表中所有的数据

1. select * from student;

复制代码

(2)查询某个表中指定字段

①查询student表中name和pwd字段的值

1. select name,pwd from student;

复制代码

②给字段或表名起别名(as)

1. select name as 姓名,pwd as 密码 from student;

复制代码

(3)函数concat(a,b)

1. select concat('姓名:',name) as 新名字 from student;

复制代码

(4)去重distinct

①查询有哪些同学参加了考试(可能有一个同学考了多个学科，造成的数据重复，需要去重)

1. select distinct Student_id from result;

复制代码

(5)查询系统版本

1. select version();

复制代码

(6)用来计算

1. select 100*3-1 as 计算结果;

复制代码

(7)查看学生成绩+1分之后的结果

1. select Student_id,StudentResult+1 as 加一分后 from result;

复制代码

5.2 where条件子句

作用：检索数据中符合条件的值搜索的条件由一个或者多个表达式组成！结果为布尔值(1)逻辑运算符

运算符语法描述anda and b逻辑与ora or b逻辑或Notnot a逻辑非(2)where的运用

①查询23(学科编号)这个学科成绩在95到100之间的学生

1. //方式一：
2. select student_id  as 学生编号,studentresult as 分数 from result
3. where studentresult>=95 and studentresult<=100 and subject_id=23;
5. //方式二：
6. select student_id  as 学生编号,studentresult as 分数 from result
7. where studentresult between  95 and 100 and subject_id=23;

复制代码

注意：执行以上命令可能会如果没有修改MySQL的mode将会报以下错误
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.sub.subject_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决方式：
在Navicat或者sqlyog的命令中执行以下命令即可

1. select name,birthday from student
2. where name like '唐%';

复制代码

5.3数据库级别的MD5加密(扩展)

1. select name,birthday from student
2. where name like '唐_';

复制代码

1. select name,birthday from student
2. where name like '唐__';

复制代码

1. select name,birthday from student
2. where name like '%嘉%';

复制代码

七、事务

7.1什么是事务

要么都成功，要么都失败就是下面这个转账的事务，必须要子啊两条SQL执行完成之后该事务才算结束，体现事物的原子性
①、SQL执行     A给B转账    A最开始有1000   B最开始有200   A给B转200
②、SQL执行    B收到A的钱   此时A有800    B有400
7.2事务原则

ACID 原则：原子性，一致性，隔离性，持久性(1)一致性

还是A给B转账这个事，事务完成后，符合逻辑运算也就是总钱数始终是1200(2)隔离性

所谓的独立性是指并发（多个用户同时操作）的事务之间不会互相影响，如果一个事务要访问的数据正在被另外一个事务修改，只要另外一个事务未提交，它所访问的数据就不受未提交事务的影响隔离所导致的一些问题：①脏读：
指一个事务读取另外一个事务未提交的数据.
②不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同.（这个不一定是错误，只是某些场合不对）.
③虚读（幻读）：
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致.
(3)持久性

事务因提交不可逆转，被持久化到数据库中
（4）事务的实际操作

1. select name,birthday from student
2. where id in (3,4,5);

复制代码

（5）模拟事务场景

1. select name,address from student
2. where address in ('宁波','上海');

复制代码

八、索引

索引的定义：索引是帮助MySQL高效获取数据的数据结构，
提取句子主干，就可以得到索引的本质：索引就是数据结构
8.1索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

• 主键索引(primary key)
  
  
  
  • 唯一的标识，主键不可重复，只能有一个列作为主键
    
  
  

*唯一索引 (unique key)

• 避免重复列出现，唯一索引可以重复，多个列都可以标识唯一索引
  
• 常规索引（key / index）
  
  
  
  • 默认的，index，key关键字来设置
    
  
  
• 全文索引（fulltext）
  
  
  
  • 快速定位数据
    
  
  

(1)索引的使用

1. select name,email from student
2. where email is null;

复制代码

给student表中的name添加字段之后结果图

8.2测试索引

~~~sql-- 插入100万条数据-- 写函数之前必须要写，标志delimiter $$ create function mock_data()returns intbegin  declare num int default 1000000;  declare i int default 0;
while i