mysql操作和数据基本类型

db操作

创建database

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] ...]
create_specification:
	[DEFAULT] CHARACTER SET charset_name
	[DEFAULT] COLLATE collation_name

说明：

• 大写的表示关键字
• [] 是可选项
• CHARACTER SET: 指定数据库采用的字符集
• COLLATE: 指定数据库字符集的校验规则

修改默认的字符集和校验规则

这个网上很多文章都没说清楚，我总结一下。

第一步：打开这个文件

第二步：加入框内的几句话，位置一定要对。一个在mysqld上面，一个在mysqld下面

第三步：重启mysql服务（不是简单的退出重进）
输入箭头指向的那一行命令，重启mysqld

成功后原来的Latin1会变成utf8

说明一下：如果没有默认配置好字符集和校验规则，它出来的结果并不一定是utf8

在没有配置好之前，是这样的。

---

好，回到创建数据库~

举几个常用的例子：

1. 创建名字为db1的数据库
   create database db1
   

2. 创建一个使用utf8字符集的 db2 数据库
   create database db2 charset=utf8;
   

3. 创建一个使用utf字符集，并带校对规则(collate)的 db3 数据库。
   create database db3 charset=utf8 collate utf8_general_ci;

啥是校对规则呢？
我们拿数据的时候是怎么拿的，最简单的方法就是遍历一遍。遍历的时候如果编码格式不同，同样的二进制编码出来的效果是不同的。

总结一下：校队规则就是拿数据的时候采取的编码格式

---

由于我们默认的字符集已经设置好了，因此以后创建数据库用

create database db

查看系统默认的字符集和校验规则

show variables like ‘character_set_database’;
show variables like ‘collation_database’;

查看所有支持的字符集

show charset

字符集主要是控制用什么语言。比如utf8就可以使用中文

校验规则对数据库的影响

先用utf8来做测试，往db里面插入了四个数据：

然后从里面拿出alpha = a的字符，发现它给了我们两个。可以得出结论：
校验规则使用utf8_ general_ ci是不区分大小写的。

现在我们把db的collate改成utf8_bin，它是区分大小写的。

注意此时要把表删掉重新建一个，因为db的collate改了，但是表的collate还没有改。因此校验方式还是没有变的。

重新建表之后，再查看， 发现它是区分大小写的了

查看所有database

show databases;

显示创建语句

你用什么语句完成数据库修改的，就可以用show来显示出来
show create database 数据库名；

• 数据库名字的反引号``是用来说明这个是字符串的，可以防止使用的数据库名刚好是关键字。

• /*!40100 default… */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话

修改数据库

关键字alter，可以修改的东西有三个，一个是数据库名字，一个是字符集，一个是校验规则。

下面这个例子就是把字符集给改掉了。

这个是把校验规则改了，改成utf8_bin了。

删除数据库

关键字drop

表操作

创建表

create table table_name(
field1 datatype,
field2 datatype,
…
)character set 字符集 collate 校验规则 engine 存储引擎;

• field 表示列名
• datatype 表示列的类型
• character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
• collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准
• 分号结束
  
  show可以看一切create语句，加上\G可以格式化输出一下，以免打印出来乱了。
  
  还可以用desc来查看表，desc 表名字

表中插入数据

insert into 表名字 values(field1, field2, field3, field4, …)

ps：md5是啥先不用管

select * from users;(拿出表中所有数据)
password是一串东西，就是经过md5加密之后的等长字符串。

修改表

修改表就是增加字段，删除字段，修改字段。字段就是列名

增加列

alter table table名 add xxxxxxxxxx after xxxxxx
add 后面加列名， after代表这个新增加的列放在谁后面

after id，因此插入到了id后面。

修改列（修改type）

alter table table_name modify xxx xxx
注意：只能修改field type。

注意：其实修改列还可以用来加上某些属性。比如一个primary key没有auto_increment，可以用这个帮它加上auto_increment

可以发现，从之前的20变到60了

修改表名

alter table table_name rename to xxx;

修改列名

alter table table_name change old_name new_name new_type;
修改列明的时候必须加上newtype

修改数据

update table_name set xxx=xxx where xxx=xxx(指定一个条件）
关键字是update 和 set

删除列

alter table table名 drop xxx（fieldname）

删除行

delete from tablename where 列名=xxx；
下面就是把tiny（key）为-128的行全部删掉

删除表

drop table table_name

数据类型分类

数值类型

int家族

TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT 4字节
BIGINT 8字节

mysql里面整数越界是不被允许的

拿tinyint来举例子：它的范围是-128-127.我插入128，是不被允许的。

其余同理。
如果类型是无符号的tinyint, 就写成tinyint unsigned;

bit（*）

bit是位图，可以有1-64的长度。
位图是结构体，因此位图具体怎么显示由软件决定。
mysql的方案是：把位图对应的ASCII码值对应的符号显示出来，这个符号显示不出来就不显示了

我给的两个数字对应的符号都显示不出来，所以打印出来是空的。

比如说：如果bit类型被复制97，它普通的显示出来就是字母a。

如果想以位图的形式来查看对应的值，需要用bin(列的名字)。如下：

如果想以位图的形式来插入对应的值，需要以字符串的形式写入bitmap，并在最前面加上一个B。如图：

浮点数

float

float(M, N)
语法格式：4代表总共有四个数字，2代表小数点后有两位数字

小数点超过位数会自动四舍五入

因此最大范围是-99.99-99.99；

decimal

decimal是精度更高的浮点数，以后用decimal就可以了。
语法和float是一样的。

字符串类型

char

char不是字符，是字符串。它可以指定最大字符个数。
比如char(5)，最大能存5个字符，存3个是没问题的。
最大可以存255个字符。

为什么要强调是字符，因为它的单位并不是字节。在utf8编码中，一个非ASCII字符占3个字节。

这样也是可以的，实际上这已经15个字节了。

varchar

varchar是可变字符串，它和语言层面的字符串不是一个概念。
varchar(N),这个N也是最大能放下的字符个数。

在这个概念上varchar和char是一样的。

那varchar和char的区别在哪呢？
区别在于char不管你放多少个字符进来，它所用的空间大小都是固定的，为最大字符数×每个字符的字节数。比如hi，在char(5).utf8编码下，就是5×3 = 15（字节）
varchar所用空间大小是可变的，为当前字符数×每个字符的字节数+1-3个字节（这段空间是用来存放有多少个字符的，相当于string的size).比如hi，在varchar(5)utf8编码下，就是2×3+1=7（字节）

---

重要的讲完了，讲一些不那么重要的。

varchar最大能存放的数字却是以字节为单位的。最大能放下65535字节，有1-3字节是用来存放当前字符串有多少个字符的。因此最大能放下65532个字节。

然而我们不同编码，每一个字符对应的字节也不同，因此导致了varchar在不同编码下最长字符数的上限也是不一样的，

比如utf8，一个字符是3个字节，因此最多只能放65532/3个字符。
比如gbk，一个字符是2个字节，因此最多只能放65532/2个字符。

超过就不给创建了。
在utf8的情况下：21844就可以创建，21845就不可以了

日期和时间类型

• date :日期 ‘yyyy-mm-dd’ ，占用三字节
• datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ，占用八字节
• timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节

timestamp是不需要自己填的，系统自己帮你填。

创建一个表

插入两个date。

如果只想插入其中的几列，可以在表名后面加上一个括号，把要插入的列写在里面。

写日期的时候不需要写前导0，sql会自动帮你补好。

注：当你在修改行数据的时候，它对应的事件戳也会自动发生变化。
其实插入也可以看成一种修改了。

修改语句

修改完了之后发现，timestamp也自己修改了。

应用场景是：一篇文章每次被修改的时候，发布时间（时间戳）都要被修改成当前时间。

enum（单选）

enum：枚举，“单选”类型；
enum(‘选项1’,‘选项2’,‘选项3’,…);

插入一个数据

不能一次插入两个。

在系统里面，枚举类型第一个以数字1来存储，第二个以数字2来存储，以此类推（不是从0开始的）

因此下面这个写法等价于写成插入男，因为男的选项值是1.

set

set：集合，“多选”类型；
set(‘选项值1’,‘选项值2’,‘选项值3’, …);

set的选项值不是1234…，而是1 2 4 8 16 …

初始化的时候写成set(‘a’,‘b’,…);
插入集合的时候写成’xxx’;

---

创建一个集合，每一个选项都要单引号括起来。这个好容易写错的。
'a,b,c’这种写法是错的。

插入选项值为4的，也就是插入c。

在插入集合的时候，要写成’a,b’不能写成’a’,‘b’！！！

如果要同时插入两个选项，选项之间用逗号间隔，不用有空格出现！！！

总结一下怎么理解这种写法：
set(‘a’,‘b’,‘c’）相当于把很多元素都放进set去初始化，因此每一个元素都要括起来。
后面插入的时候插入的是集合，而不是元素了，因此’xxx’这样才是表示集合的。

find_in_set

这个函数是用来在集合查找数据的，如果有包含了某个数据，它就返回。

如果用where来选，它只能选到只含a的集合。

用find_in_set配合where就可以了

–

单独讲一下find_in_set,如果找到了，就返回1，找不到就返回0.