mysql数据库基本操作

数据库前置知识

数据库客户端用于和用户进行交互,用户输入sql语句操作数据库,而数据库本体数据存储在数据库服务器中。数据库服务器中的数据存储在硬盘中,所以可大量存储,并且可以长久化存储, 每个数据库服务器中可以存储多个数据库。每个数据库中可以包括多个表,对于关系型数据库,表中的每一行称为记录,每一列称为字段。

数据库基本操作

1. 创建数据库: create database 数据库名
   create database if not exists 数据库名 character set utf8mb4
   用两个字节表示一个汉字最典型的编码方式是GBK方式,window简体中文版默认的编码方式就是gbk方式。java中内置的编码方式是unicode, 也是用两个字节表示汉字。而当今最主流的编码方式是utf-8, 因为utf-8可以支持各种语言文字,utf-8是一种变长编码,长度不固定,对于汉字来说utf-8一般用三个字节表示。
   mysql中的utf-8mb4比utf-8更完整,支持了一些特殊符号。

2. 查看数据库: show databases

3. 使用数据库: use 数据库名

4. 删除数据库: drop database 数据库名
   

数据类型

数值类型

1. bit(m) 表示二进制数,默认占一个字节,具体几个由m指定,即m确定二进制数的位数(m取值由1到64)。默认情况下对应java中的boolean和c语言中的char[]
2. tinyint 占一个字节,对应java中byte ,c语言中signed char
3. shortint 占两个字节,对应java中的short, c语言中shortint

4. int 占四个字节,对应c语言和java中的int

5. longint 占8个字节, 对应java中的long,c语言中的long long int

6. float (m, d)单精度浮点数,占四个字节,m为有效位数,d为小数点后位数

7. doble(m, d)双精度浮点数,占八个字节,m为有效位数,d为小数点后位数

8. decimal (m, d) 占字节数为m/d的最大值+2, 可以精确表示浮点数,牺牲了存储空间和计算速度来确保数据的精确

以上常用的数据类型有 ing double decimal

字符串类型

1. varchar(m) 可变型字符串,`最常用`的字符串类型,m指定最大长度
   varchar(256) 表示这个列最多有256个字符,字符不是字节

2. text 适合于长文本数据
3. mediumtext 适合中等长度文本数据
4. blob 存储二进制数据
   最常用的就是varchar,在c语言中学过用fopen打开文件,有两个参数,第一个是文件路径,第二个是文件打开方式,文件打开方式分为以下几种:

1. r 以只读方式打开字符值类型文件
2. rb 以只读方式打开二进制类型文件
3. w 以只写方式打开字符值类型文件
4. wb 以只写方式打开二进制类型文件
5. a 以追加方式打开字符值类型文件
6. ab 以追加方式打开二进制类型文件
   word 富文本编辑器以及excle 都是以二进制类型存在

时间类型

1. datatime 八个字节,时间可表示从1000到9999年,不能自动检索时区

2. timestamp 四个字节,时间可表示从1970到2038年,能自动检索时区

timestamp是以时间戳的方式来计算时间的,所谓时间戳就是从1970年1月1日起到现在经过了多少秒,计算机中存储的就是四字节的时间戳,然后格式化成时间格式呈现出来,因为四字节最大只能是42亿九千万秒,所以到2038年时间戳就会用尽。

表基本操作

1. 创建表: create table 表名 (列名 类型, 列名 类型);
   创建表之前,必须先选中数据库, 同一个数据库中的表名不能相同,列名和表名不能是关键字,如果表名需和关键字冲突,需给表名加反引号`, 数据库中注释可用comment,或者-- (杠杠空格), comment只能在创建表时使用;

2. 查看当前数据库中的所有表: show tables;
   

3. 查看指定表结构: desc 表名;
   

4. 删除表: drop table 表名;
   

数据库增删改查(CDUR)

增(creat)

1. 增加create:insert into 表名 value(列类型值, 列类型值, 列类型值)
   查看当前数据库的字符集:show variables like ‘%character%’;
   

   指定列插入数据: insert into 表名 (列名) value/values (值);
   此时未被指定值的列的值为默认值
   

2. 查看表中所有数据: select * from student;

   

3. 插入多行数据:insert into 表名 values (列值, 列值),(列值, 列值)…
   

查询.(select)

查询到的所有数据都是临时表中的数据,查询不会对服务器硬盘中的数据进行修改

1. 全列查询: select * from 表名 全列查询容易将硬盘io或者服务器网络带宽吃满

2. 按列查询: select 列名 from 表名

3. 表达式查询(不能进行行与行之间的表达时操作)

• select 列名＋10 from 表名;

• select 列＋列 from 表名;

• select 列 + 列 别名 from student;

• select 列 + 列 as 别名 from student;.

4. 同一列相同值去重:select distinct 列名 from 表名
5. 针对查询结果进行排序: select * from 表名 order by 列名;(默认增序asc)
   降序排序: select * from 表名 order by 列名 desc

如果sql中没有显式的order by ,其查询结果顺序实际上并没有依据,结果顺序不可信,不可依赖

6. sql中null与任何值运算,结果还是null. 针对表达式的查询排序:select 列名+列名 别名 from 表名 order by 别名;
7. 条件查询:where(条件表达式),将查询到的每一行代入条件表达式中进行验证,看是否为true

• 条件运算符:=, <=>,<不等于>,between a and b, in(a, b, c…),is null, is not null, like(模糊匹配)
• 逻辑运算符:and, or, not;

8. like模糊查询:
   like ‘zhang%’;
   like ‘%zhang%’;
   like ‘%zhang’;
   like ‘zhang_’;
   like ‘zhang__’;
   like ‘zhang’;
   like ‘zhang’;
   like 'zhang’;
   

sql中的语句基本上都可以嵌套使用,如下:

注意: 当查询时起了别名并同时使用了where + 条件时,条件表达式中不能使用别名,因为sql在执行语句时,首先会取出记录带入条件表达式中计算,结果为ture的字段才会生成别名

即使引入了where来限制查询,但当数据库很大时仍有可能造成硬盘io和网络宽带吃满,所以引入了limit进行限制 在查询语句之后之间添加limit N;, 就限制了查询的最大结果有N条

limit 搭配 offset 使用可以实现分页查询的效果,offert代表着查询从第几行开始(为0时从开头查询)
limit 3 offset 2; 等价于 limit 2, 3;

删除(delete)

selete 进行的删除操作是对数据库硬盘中的数据进行修改

1. delete from 表名 where

2. delete from 表名 order by limit嵌套使用
   

改(update)

此修改并不是修改临时表内容,而是修改数据库硬盘中的数据,可以长久储存

1. update 表名 set 列名 where
   如果要同时改多行,可以将列名用逗号隔开
   

2. 通过表达式修改
   update 表名 set 列名表达式;

3. update 嵌套
   update 表名 set 表达式 order by limit;
   

数据库约束

为了帮助程序员更好的判读数据的准确性,数据库对存储的数据进行了一些约束称为数据库约束
数据库约束在数据库创建时申明变量时确认

1. not null 某列不能有空值
2. unique 某列中的某行不能有重复数据
3. default 对没有复制的列设置默认值
4. primary key 主键 每条记录的唯一标识(与unique搭配使用)

• not null + unique = primary key
• 主键值唯一,每条记录的主键值都不同
• 一个数据库的表中只能有一个主键,但是可以将多列合并成一列,称为联合主键
• 主键值可以有masql自动生成,需在创建数据库时primary key auto_increment,这样在插入时主键值可以为null,mysql自动根据上一个主键值来自动生成一个新的主键值(+1),称为自增主键 .如果数据过多超过指定最大值时,需要分库分表

1. 在分布式系统中实现分库分表, 每个表中的主键值也不能相同,但表与表之间并没有可以获取对方主键值的方法, 所以为了给分布式系统中生成唯一的主键值,需要用到生成算法
2. 生成算法的基本思路是:算法 = 时间戳 + 主机编号 + 随机因子

5. foreign key 外键 数据库表之间的管理关系,表与表之间的约束

1. foreign key与其他约束在添加时方式不同,其他约束添加时直接在创建表时加在列名之后就好,中间不需要添加任何的符号
2. 而 foreign key 添加时,若把当前外键列称为a, 与其相关联的表称为b,b表中与当前表相关联的列称为c,则create table 表名 (列, 列, 列, foreign key (a) references b(c));
3. 如此操作则要求当前表中的a列中的值必须要在b表中的c列中存在, 若在当前表中插入行, a列的值在b表中的c列不存在,则插入失败
4. b表对当前表有约束力,称b表为父表, 被子表引用的父表的记录不能被删除,同理父表也不能被删除
5. 子表与父表中有外键关系的那一列,在父表中应用primary key 或者unique约束,因为当给子表中插入记录时,会先查询父表中是否存在对应的外键值,查询就会涉及到遍历, 如果表数据过多时,遍历可能会将硬盘io或者网络带宽吃满, 所以为了提高查询效率应该添加索引, primary key 和unique 带有索引.