MySQL数据库
数据库: 关系型和非关系型数据库
关系型数据库的优势:
-
复杂的查询
可以使用SQL语句在一个或者多个表之间进行复杂的查询
-
事务支持
可以提高安全性能
非关系型数据库
-
性能
NoSQL 是基于键值对 不需要SQL层的解析,性能高
-
可扩展性
因为基于键值对 所以水平扩展非常的容易
安装就不说了 、brew安装就行,windows的就自行百度吧~~~~~
一. 进入MySQL数据库 (前提先连接数据库)
连接:mysql.server start 断开:mysql.server stop
mysql -h 主机名 -u 用户名 -p
-h : 该命令用于指定客户端所要登录的MySQL主机名, 登录当前机器该参数可以省略;
-u : 所要登录的用户名;
-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。
一般情况下输入 mysql -u root -p 就可以了,没设密码的把-p去掉
注意:数据库结束语句一定要加分号!!!! 如出现下图就进来了
二. 介绍数据库
MySQL->小数据库->数据表->字段->数据
三. 对于数据库的操作
命令:
库和表: create 增加(创建) drop删除 alter(改) show查
-
查看所有数据库
show databases;
-
进入数据库
use 库名(进入的必须存在,不存在要先创建)
-
查看当前所在的数据库
select database();
-
创建数据库
create database mysql1;
-
查看当前所创建的数据库
show create database 库名; (后面加\G 竖着显示)
-
创建一个不存在的数据库
create database if not exists mysql1;
-
删除数据库
drop database 库名;
drop database if exists 库名; # 如果不存在, 防止报错
-
创建库并设置字符集
create database mysql1 character set utf8;
-
修改库的字符集
alter database mysql1 character set utf8;
表的操作
-
创建表
create table 表名(
字段名1 类型 约束条件,
字段名2 类型 约束条件,
…
);
-
删除表
drop table 表名;
-
查看当前所创建表
show create table 表名; (\G竖着显示)
Create Table: CREATE TABLE `user` (
`username` varchar(20) DEFAULT NULL,
`age` tinyint(3) DEFAULT NULL
) ;ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
-
s查看表
show tables;
-
查看表结构
desc 表名;
-
添加索引(不写索引名称)
alter table 表名 add 索引类型(index/key)(索引字段)
例如: alter table a add index(username);
-
添加索引(起索引名称)
alter table 表名 add 索引类型(index/key) 索引名(索引字段)
alter table a add unique uname(username); -
查看当前表的所有索引
show index from 表名;
-
创建一个表b和a一样
create table b like a;
-
删除索引
alter table 表名 drop index 索引名称
修改表结构
-
修改表的字符集
alter table 表名 character set 字符集;
-
给表添加新字段
alter table 表名 add 字段名 字段类型 约束条件 [after 字段名/first (默认新添加的字段排在最后一位)] (可选)
例如: alter table myInt add age tinyint first -- 将age添加到第一位 -
修改表字段类型 和约束条件
alter table 表名 modify 字段名 类型 约束条件 [after 字段名/first (默认新添加的字段排在最后一位)]
例如: alter table employee modify age varchar(10) after; -- 将age添加到最后一位
alter table 表名 modify 字段名 类型 约束条件 character set utf8; -- 设置字段的字符集 -
修改字段名
alter table 表名 change 旧字段名 新字段名 类型 约束条件 [after 字段名/first (默认新添加的字段排在最后一位)]
例如:alter table employee change age newAge int; -
删除字段名
alter table 表名 drop 字段名
例如:alter table employee drop age; -
修改表名
alter table 表名 rename 新表名
例如: alter table company rename employee;
注意:
- 分号作为命令的结束
- 命令不区分大小写
- 数据库,表不能重名
- 当在命令中 多输入引号以后 所有输入的内容都被认为是引号内的内容 将引号补全即可
- \c 撤销当前命令
- 数据库的退出\q exit quit
MySQL开启不严谨报错
修改my.ini配置文件
路径:C:\ProgramData\MySQL\MySQL Server 5.7
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
MySQL表的创建
一. 字段类型
(1) 数值类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
tinyint | 1字节 | -128~127 | 0~255 | 最小整数值(年龄,状态) |
smallint | 2字节 | -32768-32767 | 0-65535 | 大的整数值 |
int | 4字节 | 0~到4开头的十位 | 大的整数值 | |
float | 4字节 | 单精度浮点型 | ||
double | 8字节 | 双精度浮点型 | ||
decimal | 更加精准的小数类型 |
创建表的语句
实例:
create table myInt( age tinyint, -- 字段名,字段类型,约束条件 age2 tinyint unsigned) -- 无符号 ;
注意:
- int(3) 或 tinyint(2) 并不会影响数值存储的范围 只会影响显示在配合zerofill零填充的时候
- 如果不需要0填充的时候 后面的值不需要给
- 当存储关于钱类型的对于浮点数要求精准的时候, 使用decimal不会造成精度损耗
(2) 日期和时间类型
类型 | 大小 | 范围 | 格式 | 用途 |
---|---|---|---|---|
date | 3个字节 | 1000-01-01~9999-12-31 | YYYY-MM-DD | 存储日期值 |
time | 3个字节 | -838:59:59 ~838:59:59 | HH:MM:SS | 存储时间值 |
year | 1个字节 | 1901~2155 | YYYY | 存储年份值 |
datatime | 8个字节 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期 |
注意:
在存储时间的时候 最好使用时间戳来存储,方便我们对时间的计算
(3) 字符串类型
类型 | 大小 | 用途 |
---|---|---|
char | 0-255字节 | 存储定长字符串 |
varchar | 0-255字节 | 存储变长字符串 |
text | 0-65535字节 | 长文本数据 |
enum('w','m') | 65535个成员 | 枚举:可赋予某个枚举成员来存储 |
set('w','m') | 64个成员 | 集合: 可赋予多个集合成员,用逗号隔开 |
实例:
create table if not exists mystring( -- 创建表 username varchar(10), password char(6), article text, sex enum('w','m'), hobby set('代码','game','beautifulgirl' ));
注意:
(1) char 和varchar的区别
- char的执行效率高于varchar varchar比char更节省空间
- char和varchar存储范围都为0-255字节
(2)enum和set区别
- enum只能选择其中某一个值进行存储
- set可以选择一个到多个值进行存储 如果存在重复的值 set会去除重复
一. 字段约束
(1) unsigned 无符号 即整数
只能适用于设置数值类型 不能有符号
最大存储长度会增加一倍
(2) zerofill零填充
只能设置数值类型 当数值长度不足设置的长度时,会使用0自动填充到指定的长度
(3) auto_increment 主键自增
用于设置字段值的自动增长 当每增加一条数据的时候 当前值会自动加1
(4) default默认值
可以给某个字段设置一个默认值 当不给当前字段添加值的时候,该字段的值为默认值
(5)null 和not null
默认为null,当给当前表添加数据的时候不给某个字段添加值 则当前字段的值为null
如果设置了 not null 那么在添加数据的时候就必须给当前字段添加值
(6) comment设置当前字段的说明
注意:
- 和null进行算数运算结果都为null 例如:select 11 + null 结果为null
- null意味着没有值或者是未知值
二. MySQL的索引
主键索引 primary key
唯一索引 unique
常规索引 index
全文索引 fulltext
(1) 主键索引
主键索引(primary key)是关系型数据库中最常见的一种索引类型,用来记录一条数据的位置
注意:
- 每个表最好都有一个主键索引, 但不是必须指定
- 一个表只能有一个主键,主键的值不能为null
- 主键可以有多个约束条件,比如auto_increment, not null
- 每次删除所有的数据,下次在添加数据 auto_increment会从上次记录的位置 继续自增
将主键自增归位:
alter table 表名 auto_increment=1;
清空表并将自增归位:
truncate 表名;
(2) 唯一索引
唯一索引和主键索引相同的地方是都可以防止创建值的重复,确保数据的唯一性
不同: 一个表中只能有一个主键索引,但是可以有多个唯一索引
定义: 使用索引的关键字 unique 对应字段去添加
(3) 常规索引
就是为了提高数据库的性能(查询效率)
缺点:
- 多占用磁盘空间
- 会减慢增删改的效率
使用index或者key去创建
创建表 添加索引实例
mysql> create table user2( -> id int unsigned primary key auto_increment, -> username varchar(50) not null, -> userpass varchar(50) not null, -> telno varchar(20) not null unique, -> sex enum('男','女') not null default '男', -> birthday date not null default '0000-00-00', -> index iuser(username), # 索引类型(index/key) 索引名称(不写默认字段名)(要添加索引的字段) -> index upass(userpass) # 索引类型 索引名称(要添加索引的字段) -> );
三. 数据表的存储类型
MyISAM和InnoDB两种引擎的表的类型最为重要
- MyISAM不支持事务处理 InnoDB支持
- MyISAM不支持外键 InnoDB支持
- MyISAM的执行效率要高于InnoDB
- MyISAM默认会产生3个文件InnoDB只有一个
四. 表产生文件的区别
.frm文件: 存储数据表的框架结构 名称与表同名,每一个表都会对应一个同名的frm文件
(1) MyISAM 数据库表文件
.MyD
:是存My Data 表数据的文件.MyI
:是存My Index索引的文件.log
:日志文件
通常对数据库安全性要求不高的网站来使用,比如贴吧,博客
(2) InnoDB 数据库表文件
.ibd
: 存储数据库的表数据和索引
通过用于对于数据安全性要求高的 比如 电商
五. InnoDB的事务处理
-
先查看当前的 表的存储引擎是否为InnoDB
-
如果不是修改 alter table 表名 engine = InnoDB
-
查找是否为自动提交
select @@autocommit # 如果为1 则自动提交
-
将自动提交改为手动提交
set autocommit = 0 # 0 为手动提交
-
开启事务
begin;
-
进行sql语句的操作
-
提交或者回滚
commit work; # 提交
rollback work; # 回滚
注意:
- InnoDB引擎 当开始事务处理之后 不对它进行提交或者回滚 直接退出 那么数据将不会被报存
- 只有InnoDB支持事务处理
六. 建表的注意事项
- 表的字段直接使用逗号作为分隔
- 字段最后一句 不需要加逗号
- 数据表名 最好不要和字段重名
- 字段名 不能使用mysql的关键字
七. 数据的增删改查
增加: insert into 删除: delete from 修改: update 查询: select
(1) insert 添加
-
指定字段添加值
insert into 表名(字段名1,字段名2,..) values(值1,值2,..)
-
不指定字段添加值(有多少字段 就要添加多少个值 一一对应)
insert into 表名 values(值1,值2,..)
-
添加多个值
- 不指定字段添加多个值
insert into 表名 values(值1,值2,..), (值1,值2,..),..
- 指定字段添加多个值
insert into 表名(字段名1,字段2,..) values(值1,值2,..),(值1,值2,..),…
(2) select 查询
主体结构:
select 字段 from 表名 [where条件][group by having][order by]
-
不指定字段查询
select * from 表名;
-
指定字段查询
select 字段1,字段2,.. from 表名;
-
给查询字段起别名
- select 字段名 别名, 字段名 别名 from 表名;
- select 字段名 as 别名, 字段名 as 别名 from 表名;
where条件:
(1) 比较运算符
-
>
大于select * from a where age > 72;
-
<
小于select * from a where age <72;
-
>=
大于select * from a where age >= 72;
-
<=
小于select * from a where age <=72;
-
! 或<>
不等于select * from a where id != 1;
-
=
等于select * from a where id = 1;
(2) 逻辑运算符
-
and 逻辑与(并且)
select * from a where username ='张三' and age = 72;
-
or 逻辑或
select * from a where username ='张三' or age = 72;
-
between and 在…之间 包含值的本身
select * from a where id between 3 and 9;
select * from a where id >=3 and id <=9;
-
not between and 不在..之间
select * from a where id not between 3 and 9
select * from a where id < 3 or id > 9;
-
in 在..里
select * from a where id in(1,2,10);
-
not in 不在..里
select * from a where id not in (1,2,10);
(3) 子查询sql 的条件 还是一条sql语句
select * from a where id in (select id from user where password =123456);
(4) oder by排序
order by 字段名 asc/desc(升序/降序)
select * from a order by id desc; -- 按照id降序
select * from a order by id asc; -- 按照id升序
select * from a where age > 72 order by age;
注意:
- 不加asc 默认升序
- order by 要放在所有数据都处理完毕 再将数据排序显示
(5) is 或 not is
因为null是一个特殊的值不能使用比较运算符操作
select * from a where username is null;
select * from a where username is not null;
(6) limit 取值
limit x,y从索引x的位置(索引为x,但是为第x+1条数据) 取出y条数据 -- 即从x+1的位置取y条数据
limit y 从索引0的位置取出y条数据
select * from a order by age desc limit 0,2; -- 0表示从0开始第一条数据 2 表示查询两条即 1,2条数据
select * from a order by age desc limit 2;
select * from a where age between 112 and 255 and username is not null order by id desc limit 1;
(7) MySQL 聚合函数
-
count(字段名) 统计个数
select count(*) from a;
-
max(字段名) 最大值
select max(id) from a;
-
min(字段名) 最小值
-
sum(字段名) 求和
-
avg(字段名) 平均数
select count(*) as con,max(age) as mage,min(age) as minage,sum(age) sumage,avg(age) as avgage from a;
(8) group by 分组
select classid,count(*) as con from a group by classid;
select sex,count(*) as con from a group by sex;
select sex,count(*) as con from a group by sex order by con desc;
select classid,sex,count(*) from a group by classid,sex;
having 条件:
select classid,sex,count(*) as con from a group by classid,sex having con>1; # 按照班级和性别划分 查询人数>1
select classid,sex,count(*) as con from a group by classid,sex having con>1 and sex='w'; # 查询 人数>1且性别为w
select classid,sex,count(*) as con from a group by classid,sex having classid in('python1708');
(9) like 模糊查询
-
'%value%' 值包含就查询
select * from a where username like '%四%'
-
'value%' 以value值作为开头的数据
select * from a where username like ‘四%';
-
'%value' 以value值作为结尾的数据
select * from a where username like ‘%张';
select * from a where username like '%张%' and age>72 order by age desc limit 2;
(10) distinct 去除重复数据
select distinct age from a;
select age from a group by age;
(3) delete 删除
主体结构:
delete from 表名 [where]
注意:
- where 如果不加 则删除所有数据
(4) update 修改
主体结构:
update 表名 set 字段名=值 [, 字段名=值,..][where]
注意:
- where 如果不加 会修改一列的数据
多表联查
(1) 隐式内连接
主体结构:select 表名.字段名[,表名.字段名..] from 表1,表2[,表3..] where 关联条件
select u.username,g.goodsname from user as u,goods as g where u.id = g.uid and u.username = 15613773078
(2) 显式内连接
主体结构:select 表名.字段名[,表名.字段名..] from 表1 inner join 表2 on 关联条件 [inner join 表3 on 条件]
select u.username,g.goodsname from user as u inner join goods as g on u.id = g.uid and u.username = 15613773078
(3) 左关联
select * from user as u left join goods as g on u.id = g.uid
注意:
左关联以左表为主表 右表为辅表 会把主表的所有数据都查询出来 附表没有匹配的数据使用null来占位
(4) 右关联
select * from user as u right join goods as g on u.id = g.uid
注意:
右关联以右表为主表 左表为辅表 会把主表的所有数据都查询出来 附表没有匹配的数据使用null来占位
修改密码
set password for 用户名@localhost = password('新密码')
用户的操作
-
进入mysql数据库
use mysql;
-
查询当前数据库都有哪些用户
select user from user;
-
创建用户
create user 用户名 identified by ’密码'
-
分配权限
grant all (select,update,insert,delete) on 库名.表名(* :表示所有表) to 用户名
-
回收权限
revoke all on 库名.* from 用户名
-
删除用户
drop user 用户名;
-
刷新服务
flush privileges;