(十一)学习笔记: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;

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,271评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,275评论 2赞 380
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,151评论 0赞 336
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,550评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,553评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,559评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,924评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,580评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,826评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,578评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,661评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,363评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,940评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,926评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,156评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,872评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,391评论 2赞 342

推荐阅读更多精彩内容

  • 数据库性能优化详解
    转 # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    吕品㗊阅读 9,695评论 0赞 44
  • 001MySQL5.6从零开始学
    MySQL5.6从零开始学 第一章 初始mysql 1.1数据库基础 数据库是由一批数据构成的有序的集合，这些数据...
    星期四晚八点阅读 1,133评论 0赞 4
  • 看别人痛苦以及第一次失恋
    爱是一种有质量的存在 压在人空虚的部分 渗透，塑造，并从内到外的成为一切 从小小的欢欣开始 逐渐积累记忆成为你原始...
    燃溪阅读 185评论 0赞 1
  • 我用一场大雪 埋葬爱情
    漫天银雪纷飞轻撒，白腊梅应景盛放，寒风萧瑟中隐隐幽香，隐隐可见如梅般净透女子。 腊梅树下一袭白狐裘衣的...
    夏筱白阅读 808评论 5赞 9
  • 关于画画的二三事
    图 文／叶听雨 2018年2月26日 星期一 晴 小时候，奶奶给我几张用蓝色颜料印染的画，上面画着盛开的花朵...
    叶听雨阅读 789评论 16赞 14