1.什么是存储引擎
存储引擎就是表的类型，针对不同的存储引擎，mysql会有不同的处理逻辑
2.存储引擎介绍
InnoDB| DEFAULT | Supports transactions, row-level locking, and foreign keys
事物
blackhole 黑洞
配置文件管理
s看mysql当前配置编码情况
表操作之数据类型
常用数据类型：
#整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT
存储大小越来越大↑
#作用：存储年龄，等级，id，各种号码等
create table t1(id int(1));
insert into t1 values(256111);
select * from t1;
create table t2(id int(20));
insert into t2 values(256111);
select * from t2;
create table t3(id int(20) zerofill);
insert into t3 values(256111);
select * from t3;
mysql> create table t4(id int);
Query OK, 0 rows affected (0.46 sec)
mysql> desc t4;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql> insert into t4 values(1111111111111111111111111111111111111111111);
Query OK, 1 row affected, 2 warnings (0.17 sec)
mysql> select * from t4;
+------------+
| id |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)
mysql> create table t5(id int unsigned);
Query OK, 0 rows affected (0.45 sec)
mysql> desc t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into t5 values(11111111111111111111111111111111111111111);
Query OK, 1 row affected, 2 warnings (0.05 sec)
mysql> select * from t5;
+------------+
| id |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)
#强调：整型的宽度指的是显示宽度，并不是存储宽度
#浮点类型：FLOAT,DOUBLE,DECIMAL
#作用：体重，薪资，价格
约束
设计表结构、构建表与表之间的关系（多对一，一对一，多对多）：
mysql> create table t6(weight float(256,56) unsigned);
ERROR 1425 (42000): Too big scale 56 specified for column 'weight'. Maximum is 30.
mysql> create table t6(weight float(256,30) unsigned);
ERROR 1439 (42000): Display width out of range for column 'weight' (max = 255)
mysql> create table t6(weight float(255,30) unsigned);
Query OK, 0 rows affected (0.37 sec)
mysql> desc t6;
+--------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------------+------+-----+---------+-------+
| weight | float(255,30) unsigned | YES | | NULL | |
+--------+------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> create table t7(weight double(256,33) unsigned);
ERROR 1425 (42000): Too big scale 33 specified for column 'weight'. Maximum is 30
mysql> create table t7(weight double(256,30) unsigned);
ERROR 1439 (42000): Display width out of range for column 'weight' (max = 255)
mysql> create table t7(weight double(255,30) unsigned);
Query OK, 0 rows affected (0.36 sec)
mysql> create table t8(weight decimal(66,33) unsigned);
ERROR 1425 (42000): Too big scale 33 specified for column 'weight'. Maximum is 30.
mysql> create table t8(weight decimal(66,30) unsigned);
ERROR 1426 (42000): Too big precision 66 specified for column 'weight'. Maximum is 65.
mysql> create table t8(weight decimal(65,30) unsigned);
Query OK, 0 rows affected (0.39 sec)
#对比三种类型的精度
insert into t6 values(1.1111111111111111111111111111111111111111111111111111111111111111);
insert into t7 values(1.1111111111111111111111111111111111111111111111111111111111111111);
insert into t8 values(1.1111111111111111111111111111111111111111111111111111111111111111);
mysql> select * from t6;
+----------------------------------+
| weight |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t7;
+----------------------------------+
| weight |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select * from t8;
+----------------------------------+
| weight |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)
#了解：BIT
mysql> create table t9(x bit(1));
Query OK, 0 rows affected (0.34 sec)
mysql> insert into t9 values(2);
Query OK, 1 row affected, 1 warning (0.04 sec)
mysql> select * from t9;
+------+
| x |
+------+
| |
+------+
1 row in set (0.00 sec)
mysql> select bin(x) from t9;
+--------+
| bin(x) |
+--------+
| 1 |
+--------+
1 row in set (0.02 sec)
mysql> select hex(x) from t9;
+--------+
| hex(x) |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
日期类型
DATE：2017-11-11
TIME: 10：14：11
DATETIME:2017-11-11 10：14：11
TIMESTAMP:2017-11-11 10：14：11
YEAR:1970
可以直接调用mysql的函数，能按照类型直接获取时间数据
了解：timestamp与datatime
create table t10(
born_date date,
class_time time,
reg_time datetime,
born_year year
);
insert into t10 values
('1999-11-11','08:30:00','2017-11-11 11:11:11',2011);
insert into t10 values
(now(),now(),now(),now());
#了解：datetime与timestamp
create table t11(
x datetime,
y timestamp
);
desc t11;
mysql> desc t11;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| x | datetime | YES | | NULL | |
| y | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)
mysql> insert into t11 values(null,null);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t11;
+------+---------------------+
| x | y |
+------+---------------------+
| NULL | 2017-10-23 10:25:07 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> insert into t11 values('1011-11-11','1011-11-11');
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> select * from t11;
+---------------------+---------------------+
| x | y |
+---------------------+---------------------+
| NULL | 2017-10-23 10:25:07 |
| 1011-11-11 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+
字符类型
#char与varchar
select char_length(name) from t16;#统计字符长度4
字符串类型作用：名字，密码，职位，地址
char类型：
范围：0-255
特点：
定长，简单粗暴，浪费空间（待存储的数据长度<宽度限制），存取速度快
varchar类型：
范围：0-21844
特点：
变长，精准，节省空间（待存储的数据长度<宽度限制），存取速度慢
1、范围
mysql> create table t12(x char(256));
ERROR 1074 (42000): Column length too big for column 'x' (max = 255); use BLOB or TEXT instead
mysql> create table t12(x varchar(21845));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 6553
ns to TEXT or BLOBs
mysql> create table t12(x varchar(21844));
Query OK, 0 rows affected (0.41 sec)
mysql> desc t12;
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| x | varchar(21844) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> create table t13(x varchar(65534));
Query OK, 0 rows affected, 1 warning (0.38 sec)
mysql> desc t13;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| x | mediumtext | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
2、宽度的限制
mysql> create table t14(x char(3));
Query OK, 0 rows affected (0.67 sec)
mysql> create table t15(x varchar(3));
Query OK, 0 rows affected (0.40 sec)
mysql>
mysql> insert t14 values('xxxxxxxxxx');
Query OK, 1 row affected, 1 warning (0.10 sec)
mysql> insert t15 values('xxxxxxxxxx');
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> select * from t14;
+------+
| x |
+------+
| xxx |
+------+
1 row in set (0.00 sec)
mysql> select * from t15;
+------+
| x |
+------+
| xxx |
+------+
1 row in set (0.00 sec)
mysql> insert t14 values('你好啊啊啊');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> select * from t14;
+-----------+
| x |
+-----------+
| xxx |
| 你好啊 |
+-----------+
2 rows in set (0.00 sec)
create table t16(name char(5));
create table t17(name varchar(5));
alex |e |wupei|yh |
标记alex|标记e|标记wupei|标记yh|
验证定长与变长
create table t16(name char(5));
create table t17(name varchar(5));
insert into t16 values('a'); #'a '
insert into t17 values('a'); #'a'
select * from t16;
select * from t17;
SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
select char_length(name) from t16;
select char_length(name) from t17;
枚举类型
#enum set
enum()括号内可以规定要传的是什么不是就是空白值，可以设置默认值
set()set类型里面的值可以在insert时候选择里面的多个传进去，例如多个爱好
create table t18(
id int,
name char(10),
sex enum('male','female','None')
);
alter table t18 modify sex enum('male','female','None') not null default 'male';
insert into t18 values(1,'egon','xxxxx');
insert into t18(id,name) values(1,'egon');
create table t19(
id int,
name char(10),
hobbies set('music','read','basketball','football','eat','sleep')
);
insert into t19 values(1,'egon','music,read,eat');
完整性约束
key：
primay key
unique
foreign key
primay key（）可以放两个，联合主键，还是一个主键但是有两个条件
差错了自增ID也走 所以得truncate 表名
KEY:
primay key
unique
foreign key
mysql> create table t20(id int auto_increment,name char(10));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
create table t20(id int primary key auto_increment,name char(10));
create table t21(id int not null unique auto_increment,name char(10));
create table t22(
id int primary key,
name char(10)
);
create table t23(
id int,
name char(10),
constraint pri_id primary key(id)
);
create table t24(
id int,
name char(10),
primary key(id)
);
create table t25(
id int,
name char(10),
constraint uni_id unique(id)
);
create table t26(
id int,
name char(10),
unique(id)
);
#只能有一个主建，但是可以有多个not null unique
create table t27(
id int,
name char(10),
primary key(id),
primary key(name)
);
create table t28(
id int not null unique,
name char(10) not null unique
);
联合唯一
create table t29(
id int,
ip char(15),
port int,
primary key(ip,port)
);
insert into t29 values
(1,'1.1.1.1',3306),
(2,'1.1.1.2',3306),
(3,'1.1.1.1',8080)
;
create table t30(
id int primary key auto_increment,
ip char(15) not null,
port int not null,
unique(ip,port)
);
insert into t30(ip,port) values
('1.1.1.1',3306),
('1.1.1.1',3307),
('1.1.1.2',3307)
;
自增ID
auto_increment
show variables like '%incre%' 查看mysql跟increment有关的配置信息
外键
foreign key() reference dep(id)
#先创建被关联的表
create table dep(
id int primary key auto_increment,
dep_name char(20) not null unique,
dep_comment varchar(50)
)auto_increment=200;
insert into dep(dep_name,dep_comment) values
('IT','xxxxxxxxxx'),
('Sale','yhyyyyyyy'),
('Operation','asdfadfadsf'),
('HR','asfasdfasdfasdfasdf')
;
#再创表去关联上面的表
create table emp(
id int primary key auto_increment,
name char(6) not null,
sex enum('male','female') not null default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
);
insert into emp(name,sex,dep_id) values
('egon','male',200),
('alex','male',200),
('yh','female',203),
('evia','female',200),
('wpq','male',202)
;
insert into emp(name,sex,dep_id) values
('alex1','male',250);
#解散一个部门
#未指定同步更新、同步删除的参数时，需要这么删除
delete from emp where dep_id=200;
delete from dep where id=200;
#指定后
mysql> select * from dep;
+-----+-----------+---------------------+
| id | dep_name | dep_comment |
+-----+-----------+---------------------+
| 200 | IT | xxxxxxxxxx |
| 201 | Sale | yhyyyyyyy |
| 202 | Operation | asdfadfadsf |
| 203 | HR | asfasdfasdfasdfasdf |
+-----+-----------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex | dep_id |
+----+------+--------+--------+
| 1 | egon | male | 200 |
| 2 | alex | male | 200 |
| 3 | yh | female | 203 |
| 4 | evia | female | 200 |
| 5 | wpq | male | 202 |
+----+------+--------+--------+
5 rows in set (0.00 sec)
mysql> delete from dep where id=200;
Query OK, 1 row affected (0.06 sec)
mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex | dep_id |
+----+------+--------+--------+
| 3 | yh | female | 203 |
| 5 | wpq | male | 202 |
+----+------+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from dep;
+-----+-----------+---------------------+
| id | dep_name | dep_comment |
+-----+-----------+---------------------+
| 201 | Sale | yhyyyyyyy |
| 202 | Operation | asdfadfadsf |
| 203 | HR | asfasdfasdfasdfasdf |
+-----+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex | dep_id |
+----+------+--------+--------+
| 3 | yh | female | 203 |
| 5 | wpq | male | 202 |
+----+------+--------+--------+
2 rows in set (0.00 sec)
mysql> update dep set id=2002 where id=202;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dep;
+------+-----------+---------------------+
| id | dep_name | dep_comment |
+------+-----------+---------------------+
| 201 | Sale | yhyyyyyyy |
| 203 | HR | asfasdfasdfasdfasdf |
| 2002 | Operation | asdfadfadsf |
+------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> select * from emp;
+----+------+--------+--------+
| id | name | sex | dep_id |
+----+------+--------+--------+
| 3 | yh | female | 203 |
| 5 | wpq | male | 2002 |
+----+------+--------+--------+
2 rows in set (0.00 sec)
查询操作：
select * from mysql.userG这样的话竖起来显示
distinct查的时候去重复