MySQL数据库
数据库有关系型和非关系型之分,MySQL属于关系型数据库。
关系型数据库的优势：
1.复杂的查询, 可以使用SQL语句在一个或者多个表之间进行复杂的查询
2.事务的支持,提高安全性能
非关系型数据库的优势：
1.性能, NoSQL是基于键值对的,不需要SQL层的解析,性能高。
2.可拓展,数据之间没有耦合性, 水平扩展非常容易
一. 安装好数据库后进行MySQL数据库
# 在终端中输入
>> mysql -h主机名 -u用户名 -p # 回车
>> 输入密码
实例：
>> mysql -hlocalhost -uroot -p
>> 123456
# 本地可以不用加主机名
>> mysql -uroot -p
二.对数据库的操作
对数据库的操作命令: create创建, drop删除,alter修改,show查看
# 1. 查看所有的数据库
show databases;
# 2. 选择数据库
use 库名;
# 3. 查看当前所有数据库
select database();
# 4. 创建数据库
create database myTest;
# 5. 查看当前所创建的数据库
show create database 库名; (将;换成\G竖着显示)
# 6. 创建一个不存在的数据库
create database if not exists myTest;
# 7. 删除数据库
drop database 库名;
drop database if exists 库名; # 如果不存在删除数据库,可能会报错
# 8. 创建数据库并设置字符集
create database 库名 character set utf8;
# 9. 修改数据库的字符集
alter database 库名 character set utf8;
注意：
1> 在MySQL中要以;作为语句结束的标志
2> 命令不区分大小写
3> 数据库，表不能重名
4> 当在命令中 多输入引号以后 所有输入的内容都被认为是引号内的内容 将引号补全即可
5> \c 撤销当前命令
6> 数据库的退出\q 或 exit 或 quit
三.对数据库中表的操作
实例：
# 1. 创建表
create teable 表名(
字段名1 类型 约束条件,
字段名2 类型 约束条件,....
);
# 2. 删除表
drop table 表名;
# 3. 查看当前所创建的表
show create table 表名; (将;换成\G竖着显示)
# 4. 查看当前数据库中的所有表
show tables;
# 5. 查看创建的表的结构
desc 表名;
# 6. 添加索引(不写索引名)
alter table 表名 add 索引类型(索引字段);
例如： alter table A add index(username);
# 7. 添加索引(起索引名称)
alter table 表名 add 索引类型 索引名(索引字段)
例如： alter table A add unique uname(username); # 添加唯一索引
# 8. 查看当前表的所有索引
show index from 表名;
# 9. 创建和表b一样的表a
create table b like a;
# 10. 删除索引
alter table 表名 drop index 索引名;
例如：alter table A drop index uname;
四.对表结构的修改
实例：
# 1. 修改表的字符集
alter table 表名 character set 字符集;
# 2. 给表添加新的字段
alter table 表名 add 字段名 字段类型 约束条件 [after/first];
例如： alter table myInt add age tinyint first -- 将age添加到第一位
# 3. 修改表字段类型和约束条件
alter table 表名 modify 字段名 类型 约束条件 [after/first];
例如：alter table 表名 modify 字段名 类型 约束条件 character set utf8;
# 4. 修改字段名
alter table 表名 change 旧字段名 新字段名 类型 约束条件;
# 5. 删除字段名
alter table 表名 drop 字段名;
# 6. 修改表名
alter table 表名 rename 新表名;
例如：alter table company rename employee;
小技巧:
# 开启不严谨报错
可以修改my.ini的配置文件
我的路径是在:C:\ProgramData\MySQL\MySQL Server 5.7
# 找到这行并修改为下面即可
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
五.MySQL的数据类型
MySQL支持多种类型, 大致可以分为四类：数值型、浮点型、日期/时间和字符类型。
1. 数值型
| 类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
|---|---|---|---|---|
| tinyint | 1字节 | -128 ~ 127 | 0~255 | 最小整数值(年龄,状态) |
| smallint | 2字节 | -32768 ~ 32767 | 0-65535 | 整数值 |
| int | 4字节 | -2^31 ~ 2^31-1 | 0 ~ 2^32-1 | 整数值 |
| bigint | 8字节 | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 | 存储大的整数值 |
2. 浮点型
| 类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
|---|---|---|---|---|
| float | 4字节 | -2^31 ~ 2^31-1 | 0 ~ 2^32-1 | 单精度浮点型 |
| double | 8字节 | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 | 双精度浮点型 |
| decimal | 8字节 | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 | 更加精准的小数类型 |
3. 日期、时间型
| 类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
|---|---|---|---|---|
| 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 | 存储年份 |
| datetime | 8个字节 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合时间 |
4. 字符类型
| 类型 | 大小 | 用途 |
|---|---|---|
| char | 0-255字节 | 存储定长字符串 |
| varchar | 0-255字节 | 存储变长字符串 |
| text | 0-65535字节 | 长文本数据 |
| enum('w', 'm') | 最多65535字节 | 枚举：可赋予某个枚举成员来存储 |
| set('w','m') | 最多64个成员 | 集合, 多个集合成员,用逗号隔开 |
注意：
char和varchar的区别？
答：char的执行效率相比varchar较好, 但varchar比char更节省内存空间
enum和set区别？
答：enum只能选择创建表时设置的值中的某一个值进行存储; set可以选择一个或多个值进行存储, 如果存在重复，会去重。
六.字段约束
(1) unsigned 无符号整形
只能用于设置数值类型,只能存储无符号, 存储的正数存储范围会扩大一倍(即0-xx)。
(2) zerofill 零填充
只能设置数值类型, 当数值位数长度不足设置的长度,会使用0自动填充到指定的长度。
(3) auto_increment 主键自增
用于设置字段值的自动增长, 当每增加一条数据的时候, 当前值会自动加1。
(4) default 默认值
可以给某个字段设置默认值, 当不给当前字段添加值的时候,该字段的值为默认值。
(5) null 和 not null
默认为null, 当给当前表添加数据的时候不给某个字段添加值,则当前字段的值为null, 如果设置为not null, 那么在添加数据的时候就必须给当前字段添加值。
(6) comment 设置当前字段的说明
(7) foreign key外键约束
使用外键可以保证数据的完整性。
在从表添加一列字段,作为外键。
添加外键:
alter table 从表(多的那一表) add [constraint] [外键名] foreign key 从表外键字段名 references 主表(主表的主键);
删除外键:
# 法一：
alter table product(从表) drop foreign key 外键名(指定的外键名,如果外键名省略了,不能删);
# 法二： 先删除从表product中的数据
delete from product where pid in ('p009','p008');
# 在删除主表(category)
delete from category where cid = 'c003';
一对多关系：
alter table product add FOREIGN key(category_id) REFERENCES category(cid);
多对多的关系：
# 三张表,一个是stu学生表, 一个是course课程表, 第三张表stu-course存储两个表的关系
alter table 从表(stu-course) add foreign key(sno) references stu(sid);
alter table 从表(stu-course) add foreign key(cno) referencescourse(cid);
注意：
- 和null进行算数运算结果都为null 例如：select 11 + null 结果为null
- null意味着没有值或者是未知值
- 对于外键, 如果从表(多的那个表)中存在关联,主表(一的表)不能删除
- 从表中不能添加主表不存在的记录
七. MySQL的索引
MySQL的索引包括: (1) 主键索引primary key; (2) 唯一索引unique; (3) 常规索引index; (4) 全文索引fulltext
(1) 主键索引
主键索引(primary key)是关系型数据库中最常见的一种索引类型，用来记录唯一标识表中某一行的属性或属性组，一个表只能有一个主键，可以确保执行数据更新、删除的时候不会出现错误。而且主键除了上述作用外，还常常与外键构成参照完整性约束，防止出现数据不一致。数据库在设计时，主键起到了很重要的作用。
# 每次删除所有的数据(delete而不是truncate), 下次在添加数据的还是会从上次记录的位置开始，继续自增。
# 让主键自增归位(从新开始自增)
alter table 表名 auto_increment = 1;
# 或者通过清空表并将自增归位
truncate 表名;
注意：
1> 每个表最好都有一个主键索引, 但是不指定不会报错。
2> 一个表只有有一个主键, 主键的值不能为NULL。
3> 主键可以有多个约束条件, 比如auto_increment, not null等。
(2) 唯一索引
唯一索引和主键索引相同的地方是都可以防止创建值的重复，确保数据的唯一性，但是唯一索引在一个表中可以有多个。
通过使用unique对对应字段添加唯一索引。
(3) 常规索引
常规索引的唯一任务是加快对数据的访问速度， 但是缺点也是明显的，会占用更多的磁盘空间，而且会减慢删改的效率。
通过使用index或key对对应字段添加常规索引。
实例：
mysql> create table user(
-> id int unsigned primary key auto_increment, # 添加了 unsigned和auto_increment 字段约束和主键索引
-> username varchar(50) not null, # 添加了not null的字段约束
-> userpass varchar(32) not null,
-> telnum varchar(11) not null unique, # 添加了唯一索引
-> sex enum('m','w') not null default 'm', # 添加了default字段约束
-> birthday date not null default '0000-00-00',
-> index iuser(username), # 也可以通过下面格式添加索引： 索引类型 索引名称不写为字段名 (要添加索引的字段)
->key (userpass) , # key同上面的index, 给usepass添加常规索引，索引名为字段名, 括号必须要加。
-> );
八. MySQL中MyISAM与InnoDB两种引擎的区别
在MySQL中MyISAM和InnoDB两种引擎的表的类型最为重要, 关于二者的不同。
(1) MyISAM不支持事务处理, InnoDB支持事务
(2) MyISAM不支持外键, InnnoDB支持
(3) MyISAM的执行效率要高于InnoDB
(4) 存储结构上：
首先都会创建一个名为 .frm文件的文件；不同点是创建一个表时MyISAM会生成三个文件, .MyD是存My Data 表数据的文件, .MyI是存My Index索引的文件, .log日志文件; 而InnoDB引擎创建表的时候只会生成有一个.ibd的文件存储数据库的表数据和索引
(5) 存储空间上：
MyISAM可被压缩，存储空间较小; InnoDB需要更多的内存和存储。
九.InnoDB引擎的操作相关
1. 查看当前表的存储引擎
命令：show create table 表名;
2. 引擎的修改
命令：alter table 表名 engine = InnoDB;
3. 查看是否为自动提交
命令：select @@autocommit
注意：autocommit是mysql中的InnoDb数据表特有的语句。（只有在表引擎=InnoDb时, autocommit才会生效）在InnoDb表中，所有的语句都是需要commit后，才会在真实数据库中生效, 设置为1之后会自动commit,不用手动在commit一次, 但是只在没有开始事务的时候是这样的，如果开启了事务, 必须要手动commit才能插入或修改数据库的内容。
修改命令：set autocommit = 0；（默认是1）
4.修改为手动提交后
开启事务: begin;
进行sql操作: ~~~
提交或回滚:
commit work; # 提交
rollback work; # 回滚
十. 对数据的增删改查
对数据常见的四种操作: 增(insert)删(delete)改(update)查(select)；
1. insert 添加
(1) 指定字段添加值
语句: insert into 表名(字段名1, 字段名2,...) values(值1, 值2,..);
(2) 不指定字段添加值，有多少字段就要添加几个值，要一一对应
语句: insert into 表名 values(值1,值2,..);
(3) 一条语句添加多个值
语句1: insert into 表名 values(值1,值2,..),(值1,值2,..),...
语句2: insert into 表名(字段名1, 字段名2,..) (值1,值2,..),(值1,值2,..),...
2. select查询
语句: select 字段 from 表名 [where 条件][group by having 字段][order by 字段]
(1) 不指定字段查询
语句: select * from 表名;
(2) 指定字段查询
语句: select 字段1,字段2,.. from 表名;
(3) 给查询的字段起别名
语句1: select 字段名 别名, 字段名 别名 from 表名;
语句2: select 字段名 as 别名, 字段名 as 别名 from 表名;
(4) 添加where条件
# (1) 添加比较运算符
> 大于 select * from 表名 where age > 18;
< 小于 select * from 表名 where age < 18;
>= 大于等于 select * from 表名 where age >= 18;
<= 小于等于 select * from 表名 where age <= 18;
!= 或<> 不等于 select * from 表名 where id != 1;
= 等于 select * from 表名 where id =1;
# (2) 逻辑运算符
and 逻辑与 select * from 表名 where username ='zs' and age = 18; # 用户名为zs且为18岁的记录
or 逻辑或 select * fom 表名 where username = 'zs' or age = 18; # 用户名为zs或为18岁的记录
between...and 在..之间 包含值本身 select * from 表名 where id between 3 and 9; 类同与 select * from 表名 where id >=3 and id <= 9;
not between ... and 不在..之间 select * from a where id not between 3 and 9;
in 在..里 select * from 表名 where id in (1,2,10);
not in 不在..里 select * from 表名 where not in (1,2,10);
# (3) 子查询, 条件还是一条sql语句
select * from 表名 where id in (select id from 表名 where age = 18);
# (4) order by 排序
语法：order by 字段名 asc/desc(升序/降序);
select * from 表名 order by id desc; # 按照id降序
select * from 表名 where age > 18 order by age; # 查看age 大于18的并按照升序排序，默认为升序, order by 要放在所有数据都处理完毕 再将数据排序显示
# (5) is 或 is not
因为null是一个特殊的值不能使用比较运算符操作
select * from 表名 where username is null; # 查询username为空的记录
# (6) limit取值
limit x,y 从索引x的位置取出y条数据
limit y 从索引0开始取出y条数据
select * from 表名 order by age desc limit 0,2; # 从0开始取出两条
select * from 表名 where age between 112 and 255 and username is not null order by id desc limit 1; # 哈哈这个语句长不长？ 取年龄在112-255之间且name不为空的数据按照id排序取第一条
# (7) MySQL聚合函数
count(字段名) # 统计记录的个数
select count(id) from 表名; # 统计记录条数
select count(*) from 表名; # 同上
max(字段名) # 最大值
select max(id) from 表名;
min(字段名) # 最小值
sum(字段名) # 求和
avg(字段名) # 平均值
select count(*) as con, max(age) as mage, min(age) as mmin, sum(age) as sumage,avg(age) as avgage from 表名; # 求age的最大值，最小值等并起别名
# (8) group by 分组
select classid, count(*) as con from 表名 group by classid; # 按照班级id进行分组并统计没班的人数
select sex,count(*) as con from 表名 group by sex;
select sex,count(*) as con from 表名 group by sex order by con desc; # 按照性别分组，统计男女的人数，并按降序显示
select classid,sex,count(*) from 表名 group by classid,sex; # 按照班级和性别排序
# having 条件
select classid,sex,count(*) as con from 表名 group by classid,sex having con > 5; # 按照班级和性别分组并显示记录大于5的
select classid,sex,count(*) as con from 表名 group by classid,sex having con>1 and sex='w'; # 查询 人数>1且性别为w
select classid,sex,count(*) as con from 表名 group by classid,sex having classid in ('j140');
# (9) 模糊查询
1. "%value%" 值包含就显示
select * from 表名 where username like '%三%'; # 查看所有的用户名中包含 三 的记录
2. "value%" 以value值作为开头的数据
select * from 表名 where username like '三%';
3. '%value' 以value值作为结尾的数据
select * from 表名 where username like '%张' and age>72 order by age desc limit 2;
# (10) distinct 去除重复数据
select distinct age from 表名;
3.delete 删除
语句： delete from 表名 [where 条件]
# 删除所有记录
delete from 表名；
# 带条件的删除
delete from 表名 where id > 5; # 删除id大于5的记录
4.update 修改
语句： update 表名 set 字段名=值[,字段名=值,...][where 条件]
# 修改所有记录
update 表名 set age = 100；
# 带条件的修改
update 表名 set age = 100 where id > 5; # 修改id大于5的记录
十一. 其他的补充内容
1. MySQL的密码的修改
命令: set password for 用户名@localhost = password('新密码')
2. 对用户的操作
(1) 选择mysql数据库
use mysql;
(2) 查看当前数据库中都有哪些用户
select user from user;
(3) 创建用户
create user 用户名 indentified by '密码';
(4) 分配权限
grant all on 库名.表名 to 用户名;
其中的all可以修改为(select, update, insert,delete)其中一个,
表名可以修改为*代表所有表；
(5) 回收权限
revoke all on 库名.表名 from 用户名;
(6) 删除用户
drop user 用户名;
(7) 刷新服务
flush privileges;
