对于MySQL,我能想到的就是这几个单词:create,drop,alter,insert,delete,select,update,grant,revoke,commit。
1:创建数据库:在创建的过程中需要注意以下两点
- 校验规则(collate):utf8_bin(区分大小写),utf8_general_ci(不区分大小写)
说明:当我们创建数据库没有指定字符集(charset)和校验规则时,系统使用默认字符集:utf8,校验规则是:utf8_ general_ ci
- 存储引擎:数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
查看存储引擎:show engines;
关于varchar(len),len到底是多大,这个len值,和表的编码密切相关:
- varchar长度可以指定为0到65535之间的值,但是有1 - 3 个字节用于记录数据大小,所以说有效字节数是 65532。
- 当我们的表的编码是utf8时,varchar(n)的参数n最大值是65532/3=21844[因为utf中,一个字符占用3个字 节],如果编码是gbk,varchar(n)的参数n最大是65532/2=32766(因为gbk中,一个字符占用2字节)。
实际存储 | char(4) | varchar(4) | char占用字节数 | varchar占用字节数 |
abcd | abcd | abcd | 4*3 = 12 | 4*3 + 1 = 13 |
A | A | A | 4*3 = 12 | 1*3 + 1 = 4 |
Abcde | no | no | 数据超过长度 | 数据超过长度 |
mysql> create table tt11(id int, name varchar(21845))charset=utf8; --验证了utf8确实是 不能超过21844
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
mysql> create table tt11(name varchar(21844)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)
显示创建语句:show create database 数据库名
2:修改数据库:对数据库的修改主要指的是修改数据库的字符集,校验规则
例如:将 mytest 数据库字符集改成 gbk
mysql> alter database mytest charset=gbk;
3:删除数据库:
drop database [if exists]数据库名;
执行删除之后的结果:
- 数据库内部看不到对应的数据库
- 对应的数据库文件夹被删除,级联删除,里面的数据表全部被删
4:表的操作
create table users (
id int,
name varchar(20) comment '用户名',
password char(32) comment '密码是32位的md5值',
birthday date comment '生日' ) character set utf8 engine MyISAM;
不同的存储引擎,创建表的文件不一样。
users 表存储引擎是 MyISAM ,在数据目中有三个不同的文件,分别是:
users.frm:表结构
users.MYD:表数据
users.MYI:表索引
5:查看表结构
desc 表名;
6:修改表
在项目实际开发中,经常修改某个表的结构,比如字段名字,字段大小,字段类型,表的字符集类型,表的存储引 擎等等。我们还有需求,添加字段,删除字段等等。
增加字段:alter table stu add age int comment '增加年龄属性,并且位于birthday之后' after birthday;
修改字段大小:alter table stu modify name varchar(30);
修改表名:alter table stu rename 新表名;
修改字段名:alter table stu change name xingming varchar(32);
删除字段:alter table stu drop 字段名;
向表中增加两条记录:
mysql> insert into stu values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');
7:日期数据类型
mysql> create table birthday (t1 date, t2 datetime, t3 timestamp); //创建表
mysql> insert into birthday(t1,t2) values('1997-7-1','2008-8-8 12:1:1'); //插入数据
说明:添加数据时,时间戳自动补上当前时间
mysql> update birthday set t1='2000-1-1';
datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从1000到9999,占用八字节
date:日期 'yyyy-mm-dd',占用三字节
timestamp:时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss格式和datetime完全一致,占用四字节
8:enum和set
enum:枚举,“单选”类型;
enum('选项1','选项2','选项3',...);
mysql> create table t(
-> username varchar(30),
-> hobby set('登山','游泳','篮球','武术'),
-> gender enum('男','女'));
插入数据
insert into votes values('雷锋', '登山,武术', '男');
insert into votes values('Juse','登山,武术',2);
集合查询使用find_ in_ set函数:
find_in_set(sub,str_list) :如果 sub 在 str_list 中,则返回下标;如果不在,返回0; str_list 用逗 号分隔的字符串。
查询爱好登山的人:
mysql> select * from votes where find_in_set('登山', hobby);
9:主键:
primary key用来唯一的约束该字段里面的数据,不能重复,不能为空,一张表中最多只能有一个主键;主 键所在的列通常是整数类型。在创建表的时候,在所有字段之后,使用primary key(主键字段列表)来创建主键,如果有多个字段作为主 键,可以使用复合主键。
mysql> create table t(
-> id int unsigned,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id, course) -- id和course为复合主键
-> );
当表创建好以后,可以再次追加主键:alter table 表名 add primary key(字段列表)
主键约束:主键对应的字段中不能重复,一旦重复,操作失败。
删除主键:alter table 表名 drop primary key;
10:自增长
auto_increment:当对应的字段,不给值,会自动的被系统触发,系统会从当前字段中已经有的最大值+1操作, 得到一个新的不同的值。通常和主键搭配使用,作为逻辑主键。
自增长的特点:
- 任何一个字段要做自增长,前提是本身是一个索引(key一栏有值)
- 自增长字段必须是整数
- 一张表最多只能有一个自增长
mysql> create table t(
-> id int unsigned primary key auto_increment,
-> name varchar(10) not null default ''
-> );
11:唯一键unique
唯一键的本质和主键差不多,唯一键允许为空,而且可以多个为空,空字段不做唯一性比较。
12:外键
foreign key (字段名) references 主表(列)
先创建主键表
create table myclass (
id int primary key,
name varchar(30) not null comment'班级名'
);
再创建从表
create table stu (
id int primary key,
name varchar(30) not null comment '学生名',
class_id int,
foreign key (class_id) references myclass(id)
);