学习MYSQL过程中自己写的数据库操作
查看所有数据库
show databases;
进入数据库
use 数据库名字；
创建数据库
create database 数据库名字 【charset/character set 字符集】
根据数据库设置中文编码
set names 大写的中文编码; //如set names UTF8;
查看数据库的创建语句
show create database 数据库名字
查看所有表
show tables;
查看一张表的所有内容
select * from 表名;
修改数据库的字符集:
alter database 数据库名字 charset/character set 字符集；
创建数据表
create table [if not exists] 表名 ( 字段名字 数据类型 , 字段名字 数据类型-- 最后一行不需要逗号 ) 【charset/character set 字符集】;
【if not exists: 如果表名不存在,那么就创建,否则不执行创建代码: 检查功能】
创建指定表所属的数据库 // Create table 数据库名.表名();
create table [if not exists] 数据库名.表名 (字段名字 数据类型,字段名字 数据类型 -- 最后一行不需要逗号)【charset/character set 字符集】;
【将当前数据表创建到指定的数据库下】
查看表的创建语句:
show create table 表名;
查看表结构
desc/describe/show columns from 表名;
修改表名
rename table 老表名 to 新表名;
修改数据表字符集
Alter table 表名 charset/character set 字符集;
增加数据表字段
alter table 表名 add [column] 字段名 数据类型 [列属性] [位置];
First: 第一个位置
after 字段名-----在哪个字段之后;默认的是在最后一个字段之后
修改数据表字段: 修改通常是修改属性或者数据类型
alter table 表名 modify 字段名 数据类型 [属性] [位置];
重命名数据表字段
alter table 表名 change 旧字段 新字段名 数据类型 [属性] [位置];
删除数据表字段
alter table 表名 drop 字段名;
删除数据表
drop table 表名1,表名2...; -- 可以一次性删除多张表
新增数据
Insert into 表名 values (值列表)[,(值列表)];-- 值列表的顺序必须与选定的字段的 顺序一致
Insert into 表名 (字段列表) values (值列表)[,(值列表)]; -- 同上
查看数据
select */字段列表 from 表名 [where 条件(字段名=值)];
查看数据完整语法
select [select选项] 字段列表[ [as]字段别名] /* from 数据源 [where条件子句] [group by子句] [having子句] [order by子句] [limit 子句];
Select选项: select对查出来的结果的处理方式
All: 默认的,保留所有的结果
Distinct: 去除重复, 查出来的结果,将重复给去除(所有字段都相同)
字段别名：对字段名进行重命名: 别名
语法：字段名 [as] 别名;
数据源
本质上只要保证数据类似二维表,最终都可以作为数据源.
数据源分为多种: 单表数据源, 多表数据源, 查询语句
单表数据源: select * from 表名;
多表数据源: select* from 表名1,表名2...;
从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保 留:(记录数和字段数),将这种结果成为: 笛卡尔积(交叉连接): 笛卡尔 积没什么卵用, 所以应该尽量避免.
子查询: 数据的来源是一条查询语句(查询语句的结果是二维表)
Select * from (select 语句) as 表名;
Where子句: 用来判断数据,筛选数据.
Where子句返回结果: 0或者1, 0代表false,1代表true.
Group by子句
Group by:分组的意思, 根据某个字段进行分组(相同的放一组,不同的分到不同的组)
分组会自动排序: 根据分组字段:默认升序
Group by 字段名 [asc|desc]; -- 对分组的结果然后合并之后的整个结果进行排序 asc是升序 desc是降序
group by 字段名1，字段名2； 多字段分组: 先根据一个字段进行分组,然后 对分组后的结果再次按照其他字段进行分组
group_concat(字段名); 可以对分组的结果中的某个字段进行字符串 连接(保留该组所有的某个字段)
基本语法: group by 字段名;
分组为了统计数据(按组统计: 按分组字段进行数据统计)
Count(): 统计分组后的记录数: 每一组有多少记录
两种参数:
*代表统计记录
字段名代表统计对应的字段(NULL不统计)
Max(): 统计每组中最大的值
Min(): 统计最小值
Avg(): 统计平均值
Sum(): 统计和
Having子句:
与where子句一样: 进行条件判断的.
Where是针对磁盘数据进行判断: 进入到内存之后,会进行分组操作: 分组结果就 需要having来处理.Having能做where能做的几乎所有事情, 但是where却不能做 having能做的很多事情.
Having能够使用字段别名: where不能: where是从磁盘取数据,而名字只可能是字 段名: 别名是在字段进入到内存后才会产生.
Order by子句
排序, 根据某个字段进行升序或者降序排序, 依赖校对集.
基本语法 Order by 字段名 [asc|desc]; -- asc是升序(默认的),desc是降序
多字段排序
先根据某个字段进行排序, 然后排序好的内部,再按照某个数据进行再 次排序
语法： Order by 字段名1，字段名2…… [asc|desc]
Limit子句
一种限制结果的语句: 限制数量.
两种使用方式
方案1: 只用来限制长度(数据量): limit 数据量;
方案2: 限制起始位置,限制数量: limit 起始位置,长度;
Limit方案2主要用来实现数据的分页: 为用户节省时间,提交服务器的 响应效率,减少资源的浪费.
对于用户来讲: 可以点击的分页按钮: 1,2,3,4
对于服务器来讲:根据用户选择的页码来获取不同的数据: limit(offset,length)
Offset: offset = (页码 - 1) * 每页显示量
Length: 每页显示的数据量: 基本不变(程序员可自由设定)
修改数据
update 表名 set 字段名=值 where条件(字段名=值) [limit 修改数量];
删除数据
delete from 表名 where条件(字段名=值) [limit 删除数量];
按指定的数据排序
select * from 表名 order by birth DESC; // -- 倒序DESC 正序ASC
删除数据库
drop database [if exists] 数据库名字;
数据类型
整数型(一个字节八位(二进制))
无符号数据: 数据类型 unsigned; -- 无符号: 从0开始
cinyint: 迷你整型,使用一个字节存储, 表示的状态最多为256种(常用)
smallint: 小整型,使用2个字节存储,表示的状态最多为65536种
mediumint: 中整型, 使用3个字节存储
Int: 标准整型, 使用4个字节存储(常用)
Bigint: 大整型,使用8个字节存储
数据类型之后都会自带一个括号,里面有指定的数字Tinyint(3)
显示宽度: 没有特别的含义, 只是默认的告诉用户可以显示的形式而已: 实际上用户是可以控制
的,这种控制不会改变数据本身的大小.
显示宽度的意义: 在于当数据不够显示宽度的时候,会自动让数据变成对应的显示宽度: 通常需要搭
配一个前导0来增加宽度, 不改变值大小: zerofill(零填充): 零填充会导致数值自动变成无符号
零填充的意义(显示宽度): 保证数据格式
小数型
浮点型
浮点型数据是一种精度型数据: 因为超出指定范围之后, 会丢失精度(自动四舍五入)
Float: 单精度, 占用4个字节存储数据, 精度范围大概为7位左右
Double: 双精度,占用8个字节存储数据, 精度方位大概为15位左右
浮点的使用方式: 直接float表示没有小数部分;
float(M,D): M代表总长度,D代表小数部分长度, 整数部分长度为M-D
浮点型数据的插入: 整型部分是不能超出长度的,但是小数部分可以超出长度(系统会自动四舍五入)
浮点数如果是因为系统进位导致整数部分超出指定的长度,那么系统也允许成立.
定点型:
绝对的保证整数部分不会被四舍五入(不会丢失精度),小数部分有可能(理论小数部分也不 会丢失精度)
decimal(P，D)表示可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。
P是表示有效数字数的精度。 P范围为1?65。默认时为10
D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于P。默认时为2
插入数据: 定点数的整数部分一定不能超出长度(进位不可以),
小数部分的长度可以随意超出(系统自动四舍五入)
字符串类型
定长字符串:
char, 磁盘(二维表)在定义结构的时候,就已经确定了最终数据的存储长度.
Char(L): L代表length, 可以存储的长度, 单位为字符, 最大长度值可以为255.
Char(4): 在UTF8 环境下,需要4 * 3 = 12个字节
变长字符串
varchar, 在分配空间的时候,按照最大的空间分配，但是实际上最终用了多少,是根据具体 的数据来确定.
Varchar(L): L表示字符长度 理论长度是65536个字符, 但是会多处1到2个字节来确定存储的实际长度:
但是实际上如果长度超过255,既不用定长也不用变长, 使用文本字符串text
Varchar(10): 的确存了10个汉字, utf8环境, 10 * 3 + 1 = 31(bytes) 存储了3个汉字: 3 * 3 + 1 = 10(bytes)
| 实际存储数据 | char(4) | varchar(4) | char占用字节 | archar占用字节 |
| ABCD | ABCD | ABCD | 4*3=12 | 4*3+1=13 |
| A | A | A | 4*3=12 | 1*3+1=4 |
| ABCDE | 错误 | 错误 | 数据超出长度 | 数据超出长度 |
文本字符串
如果数据量非常大, 通常说超过255个字符就会使用文本字符串
文本字符串根据存储的数据的格式进行分类: text和blob
Text: 存储文字(二进制数据实际上都是存储路径)
Blob: 存储二进制数据(通常不用)
枚举字符串
enum, 事先将所有可能出现的结果都设计好, 实际上存储的数据必须是规定好的数据中的一个.
枚举的使用方式
enum(可能出现的元素列表); //如enum(‘男’,’女’,’不男不女’,’妖’,’保密’);
使用: 存储数据,只能存储上面定义好的数据
加入数据: 作用之一: 规范数据格式: 数据只能是规定的数据中的其中一个
作用之二: 节省存储空间(枚举通常有一个别名: 单选框): 枚举实际存储的是数值而不 是字符串本身.
枚举元素的实际规律: 按照元素出现的顺序, 从1开始编号
集合字符串
实际存储的是数值,而不是字符串(集合是多选)
集合使用方式:
Set (元素列表)
使用: 可以使用元素列表中的元素(多个), 使用逗号分隔
插入数据: 可以使用多个元素字符串组合, 也可以直接插入数值
集合中每一个元素都是对应一个对应二进制位
集合中元素的顺序没有关系: 最终系统都会去匹配顺序
时间日期类型
Datetime: 时间日期, 格式是YYYY-mm-dd HH:ii:ss,表示的范围是从1000到9999年,有0值: 0000-00-00 00:00:00
Date: 日期,就是datetime中的date部分
Time: 时间(段), 指定的某个区间之间, -时间到+时间
Timestamp: 时间戳, 并不是时间戳,只是从1970年开始的YYYY-mm-dd HH:ii:ss格式与datetime完全一致
Year: 年份,两种形式, year(2)和year(4): 1901-2156
插入数据: 时间time可以是负数,而且可以是很大的负数, year可以使用2位数插入,也可以使用4位数
Timestamp字段: 只要当前所在的记录被更新, 该字段一定会自动更新成当前时间
列属性
列属性: 真正约束字段的是数据类型, 但是数据类型的约束很单一. 需要有一些额外的约束, 来更 加保证数据的合法性.
列属性有很多: NULL/NOT NULL, default, Primary key, unique key, auto_increment,comment
空属性
NULL(默认的)和NOT NULL(不为空)
列描述
comment 描述, 没有实际含义: 是专门用来描述字段,会根据表创建语句保存: 用来给程 序员(数据库管理员)来进行了解的.
默认值
default 某一种数据会经常性的出现某个具体的值, 可以在一开始就指定好: 在需要真 实数据的时候,用户可以选择性的使用默认值.在数据进行插入的时候,不给改字 段赋值
主键
primary key, 主要的键. 一张表只能有一个字段可以使用对应的键, 用来唯一的约束 该字段里面的数据, 不能重复: 这种称之为主键.
一张表只能有最多一个主键.
增加主键 前提: 表中字段对应的数据本身是独立的(不重复)
方案1:
在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空)
优点: 非常直接; 缺点: 只能使用一个字段作为主键
字段名 数据类型 primary key；
方案2:
在创建表的时候, 在所有的字段之后, 使用primary key(主键字段列表)来创建主键(如果有 多个字段作为主键,可以是复合主键)
primary key(字段列表)；
方案3:
当表已经创建好之后, 额外追加主键: 可以通过修改表字段属性, 也可以直接追加.
Alter table 表名 add primary key(字段列表);
删除主键
Alter table 表名 drop primary key;
自增长
auto_increment
特点
1.任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值)
2.自增长字段必须是数字(整型)
3.一张表最多只能有一个自增长
修改当前自增长已经存在的值: 修改只能比当前已有的自增长的最大值大,不能小(小不生效)
Alter table 表名 auto_increment = 值;
删除自增长
Alter table 表名 modify 字段 类型;
唯一键
unique key 一张表往往有很多字段需要具有唯一性,数据不能重复: 但是一张表中只能有一个 主键: 唯一键就可以解决表中有多个字段需要唯一性约束的问题.
唯一键的本质与主键差不多: 唯一键默认的允许自动为空,而且可以多个为空(空字段不参与唯一性 比较)
增加唯一键
方案1:
在创建表的时候,字段之后直接跟unique/unique key
字段名 数据类型 unique/unique key
方案2:
在所有的字段之后增加
unique key(字段列表); -- 复合唯一键
方案3:
在创建表之后追加唯一键
alter table 表名 add unique/unique key (字段名);
删除唯一键
Alter table 表名 drop index 索引名字; -- 唯一键默认使用字段名作为索引名字
MySQL常用函数
文本函数
SELECT CONCAT('X','Y',……) 创建形如 xy 的新字符串(两个字符串相连)
SELECT LENGTH('储存的值') 返回列中储存的值的长度
SELECT LEFT('储存的值',x) 从列的值中返回最左边的 x 个字符
SELECT RIGHT('储存的值',x) 从列的值中返回最右边的 x 个字符
SELECT TRIM('储存的值') 从存储的值删除开头和结尾的空格
SELECT UPPER('储存的值') 把存储的字符串全部大写
SELECT LOWER('储存的值') 把存储的字符串全部小写
SELECT SUBSTRING('储存的值', x,y) 从 '储存的值'中的第x个开始返回y个字符（索引从 0 开始）
SELECT MD5('储存的值') 把储存的字符串用 MD5 加密
SELECT SHA('储存的值') 把存储的字符串用 SHA 加密
日期和时间函数
SELECT NOW() 返回当前时间和日期
SELECT HOUR(NOW()) 只返回储存日期的小时值
SELECT MINUTE(NOW()) 只返回储存日期的分钟值
SELECT SECOND(NOW()) 只返回储存日期的秒值
SELECT DAYNAME(NOW()) 返回日期值中星期几
SELECT DAYOFMONTH(now()) 返回日期值中当月第几天
SELECT MONTHNAME(NOW()) 返回日期值中月份的名称
SELECT MONTH(NOW()) 返回日期值中月份的数字值
SELECT YEAR(NOW()) 返回日期值中年份的数字值
SELECT CURDATE() 返回当前日期
SELECT CURTIME() 返回当前时间
格式化日期和时间 SELECT DATE_FORMAT('NOW()','格式化名词')和 TIME_FORMAT('NOW()','格式化名词')
