(十一)学习笔记:MySQL数据库的使用总结

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);

注意：

1. 和null进行算数运算结果都为null 例如：select 11 + null 结果为null
2. null意味着没有值或者是未知值
3. 对于外键, 如果从表(多的那个表)中存在关联,主表(一的表)不能删除
4. 从表中不能添加主表不存在的记录

七. 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;