MySQL

MySQL

1.初识数据库

1.1连接数据库

命令行连接

mysql -uroot -p123456 --连接数据库
update mysql user set authentication_string=password('123456') where user='root' and Host='localhost'; --修改用户密码
--所有的语句都使用;结尾
show databases; --查看所有的数据库
use school; --切换数据库 use 数据库名
show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息
create database westos; --创建一个数据库
exit; --退出连接
--单行注释
/*
多行注释
*/

数据库xxx语言

DDL 定义

DML 操作

DQL 查询

DCL 控制

2.操作数据库

2.1操作数据库（了解）

1.创建数据库

create database if not exists westons;

2.删除数据库

drop database if exists westons;

3.使用数据库

use `world`;--如果你的表名或者字段名是一个特殊字符，则使用``

4.查看数据库

show databases;

2.2数据库的列类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串类型的浮点数 金融计算的时候。一般使用decimal

字符串

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

时间日期

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

null

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

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

Unsigned：

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

zerofill

• 0填充的
• 不足的位数，使用0来填充，int(3) ,5 005

自增

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

非空 not null

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

默认

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

2.4创建数据库表(重点)

1.创建表

-- 注意点；使用英文()，表的名称 和 字段尽量使用 ``括起来
-- auto_increment 自增
-- 字符串使用单引号括起来
-- 所有的语句后面加英文, 最后一个字段不用加
-- primary key 主键，一般一个表只有一个唯一的主键
create table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default '匿名' comment '姓名',
    `pwd.` varchar(20) not null default '123456' comment '登陆密码',
	`sex` varchar(2) not null default'女' comment '性别',
    `brithday` datetime default null comment '出生日期',
    `address` varchar(100) default null comment '家庭住址',
    `email` varchar(50) default null comment '邮件',
    primary key (`id`)
)engine=InnoDB default charset=utf8;

格式

create table[if not exists] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ......
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释];

常用命令

show create database westons; -- 查看创建数据库的语句
show create table student; -- 查看student数据表的定义语句
desc student; -- 显示表的结构

2.5数据表的类型

-- 关于数据库引擎
/*
InnoDB 默认使用
MyISAM 早些年使用
*/

	MyISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规使用操作

• MyISAM 节约空间，速度较快

• InnoDB 安全性高，事务的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储

MySQL引擎在物理文件上的区别

• InnoDB 在数据库表中只有一个*.frm 文件，以及上级目录下的ibdata1文件
• MyISAM 对应文件
  • *.frm -表结构的定义文件
  • *.MYD 数据文件（data）
  • *.MYI 索引文件（index）

设置数据库表的字符集编码

charset=utf8

不设置的话，会是mysql默认的字符集编码~（不支持中文）

mysql的默认编码是Latin1，不支持中文

在my.ini 中配置默认的编码

character-set-server=utf8

2.6修改删除表

修改

-- 修改表名 alter table 旧表名 rename as 新表名;
alter table student rename as student1;
-- 增加表的字段 alter table 表名 add 字段名 列属性;
alter table student1 add age int(11);
-- 修改表的字段(重命名，修改约束)
-- alter table 表名 modify 字段名 列属性[];
alter table student1 modify age varchar(11); --修改约束
-- alter table 表名 change 旧字段名 新字段名 列属性[];
alter table student1 change age age1 int(1); --重命名
-- 删除表的字段 alter table 表名 drop 字段名;
alter table student1 drop age1;

删除

-- 删除表(如果存在再删除)
drop table if exists student1;

所有的创建和删除，尽量增加上判断，以免报错

注意点

• `` 所有的字段名，使用这个包裹起来
• 注释 -- /**/
• sql关键字大小写不敏感
• 所有的符号全部用英文

3.MySQL数据管理

3.1外键（了解）

create table `grade`(
	`gradeid` int(10) not null auto_increment comment '年级id',
    `gradename` varchar(50) not null comment '年级名称',
	primary key(`gradeid`)
)engine=InnoDB default charset=utf8;

-- 学生表的 gradeid 字段 要去引用年级表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用） references 引用
create table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default '匿名' comment '姓名',
    `pwd.` varchar(20) not null default '123456' comment '登陆密码',
	`sex` varchar(2) not null default'女' comment '性别',
    `gradeid` int(10) not null comment '学生的年级',
    `brithday` datetime default null comment '出生日期',
    `address` varchar(100) default null comment '家庭住址',
    `email` varchar(50) default null comment '邮件',
    primary key (`id`),
    key `FK_gradeid` (`gradeid`),
    constraint `FK_gradeid` foreign key (`gradeid`) references `grade` (`gradeid`)
)engine=InnoDB default charset=utf8;

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再去删除被引用的表（主表）

方法二

-- 创建表的时候没有外键关系
alter table `student` 
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);

以上的操作都是物理外键，数据库级别的外键，我们不建议（避免数据库过多造成困扰）

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）

3.2DML语言（全部记住）

DML语言：数据操作语言

• Insert
• update
• delete

3.3添加

insert

-- 插入语句（添加）
-- insert into 表名（[字段名1，字段2，字段3]） values（'值1'）,（'值2'）,（'值3',......)
insert into `grade`(`gradename`) values ('大四');
-- 由于主键自增我们可以省略（如果不写表字段，他就会一一匹配）
-- 一般写插入语句，我们一定要数据和字段一一兑现

-- 插入多个字段
insert into `grade` (`gradename`)
values ('大二'),('大一');

insert into `student`(`name`) values ('张三');
insert into `student`(`name`,`pwd.`,`sex`) values ('李四','aaaaaa','男');

语法：insert into 表名（[字段名1，字段2，字段3]） values（'值1'）,（'值2'）,（'值3',......)

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条数据，values后面的值，需要使用，隔开即可values（），（），......

3.4修改

update 修改谁（条件） set原来的值=新值

-- 修改学员名字,带条件
update `student` set `name`='Lily' where id=1;
-- 修改多个属性，逗号隔开
update `student` set `name`='yy',email='3321508884@qq.com' where id=1;
-- 语法：
-- update 表名 set column_name=value,[column_name=value，] where 条件

条件：where子句 运算符 id等于某个值，大于某个值，在某个区间内修改

操作符会返回bool值

操作符	含义
=	等于
<>或!=	不等于
>
<
>=
<=
between... and...	在某个范围类[]
and	a和b &&
or	a或b ||

语法:update 表名 set column_name=value,[column_name=value，] where 条件

注意

• column_name 是数据库的列尽量带上``
• 条件，筛选的条件
• value，是一个具体的值，也可以是一个变量
• 多个设置的属性之间，使用英文逗号隔开

update `student` set `brithday`=current_time where `id`=2;

3.5删除

delete命令

语法：delete from 表名 [where 条件]

-- 删除指定数据
delete from `student` where id=1;

truncate命令

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

-- 清空 student 表
truncate `student`;

delete和truncate区别

• 相同点：都能删数据，都不会删除表结构
• 不同
  • truncate 重新设置自增列 计数器会归零
  • truncate 不会影响事务

了解即可：delete删除的问题，重启数据库，现象

• InnoDB 自增列会从1开始（存在内存中的，断电即失）
• MyISAM 继续从上一个自增量开始（存在文件中的，不会丢失）

4.DQL查询数据（最重点）

4.1DQL

数据查询语言

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言
• 使用频率最高的

4.2指定查询字段

-- 查询全部的学生
select * from student;
-- 查询指定字段
select studentno,studentname from student;
-- 别名，给结果起一个名字 as 可以给字段起别名，也可以给表起别名
select studentno as 学号,studentname as 学生姓名 from student as s;
-- 函数 Concat(a,b)
select concat('姓名：',studentname) as 新名字 from student;

语法：select 字段，... from 表

有的时候，列名字不是那么的见名知意。我们就起别名 as

去重 distinct

作用：去除select查询出来的结果中重复的数据，只显示一条

select distinct studentno from student;

数据库的列（表达式）

select version(); -- 查询系统版本 （函数）
select 100*3-1 as 计算结果; -- 用来计算 （表达式）
select @@auto_increment_increment; -- 查询自增步长 （变量）

-- 学员考试成绩+1分查看
select studentno,studentresult+1 as 提分后 from result;

4.3where条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或者多个表达式组成！结果 布尔值

逻辑运算符

运算符	描述
and &&	逻辑与，两个都为真，结果为真
or ||	逻辑或，有真则为真
not !	逻辑非，真为假，假为真

尽量使用英文字母

-- and &&
select studentno,studentresult from result 
where studentresult>=95 and studentresult<=100;

-- 模糊查询(区间)
select studentno,studentresult from result 
where studentresult between 95 and 100;

模糊查询：比较运算符

运算符	描述
is null	如果操作符为null，结果为真
is not null	如果操作符不为 null，则结果为真
between	若a在b和c之间，则结果为真
like	SQL匹配，如果a匹配b，则结果为真
in	假设a在a1,a2,a3其中一个值中，则结果为真

-- like结合 %（代表0到任意个字符） _(一个字符)
-- 查询姓赵的同学
select studentno,studentname from student
where studentname like '赵%';
-- 名字后面只有一个字 刘_ 
-- 名字后面有两个字 刘__
-- 名字中间有乐  %乐%

-- 查询 1001，1002，1003号学员
select studentno,studentname from student
where studentno in(1001,1002,1003);

-- 查询地址为空的同学
select studentno,studentname from student
where address=''  or address is null;
-- 查询有出生日期的同学
select studentno,studentname from student
where borndate is not null;

4.4联表查询