mysql 基础
03-mysql数据类型
1.整形类型
| 整数类型 | 字节 | 取值范围（最小） | 取值范围（最大） |
| tinyint | 1 |
有符号 -128 无符号0 |
有符号 127 无符号 255 |
| smallint | 2 |
有符号-32768 无符号0 |
有符号 32767 无符号 65535 |
| mediumint | 3 |
有符号 -8388608 无符号 0 |
有符号 8838607 无符号 1677215 |
| int,integer | 4 |
有符号 -2147483648 无符号 0 |
有符号 2147483647 无符号 4294967295 |
| bigint | 8 |
有符号 -263 无符号 0 |
有符号 2**63 -1 无符号2**64 -1 |
整型分无符号（unsigned）和有符号（signed）两种类型；
在默认情况下声明的整型变量都是有符号的类型（char有点特别），如果需声明无符号类型的话就需要在类型前加上unsigned。
无符号数只表示大小，有符号数最高位(二进制情况下最高位表示符号位)，在同一操作系统下，有符号数和无符号数的最大数值在大小上的关系是2x+1(x表示有符号数的最大值，2x+1表示无符号数最大值)。
二者表示的数据范围大小是相同的，但是范围不同。
无符号整型和有符号整型的区别就是无符号类型可以存放的正数范围比有符号整型中的范围大一倍，因为有符号类型将最高位储存符号，而无符号类型全都储存数字。
引入知识：关于无符号整数的一些坑
总结下来的结论是：尽量不要使用UNSIGNED，因为可能会带来一些意想不到的效果。另外，对于INT类型可能存放不了的数据，INT UNSIGNED同样可能存放不了，与其如此，还不如在数据库设计阶段将INT类型提升为BIGINT类型。
常用整形类型为int类型
2.浮点类型与定点类型
| 浮点数类型 | 字节 | 负数取值范围 | 非负数取值范围 |
| float单精度 | 4 |
-3.402823466E+38 -1.175494351E-38 |
0和1.175494351E-38 3.402823466E+38 |
| double双精度 | 8 |
-1.7976931348623157E+308 -2.2250738585072014E-308 |
0和2.2250738585072014E-308~1 7976931348623157E |
| 定点类型 | 字节 | 描述 | |
| decimal(m,d) | M+2 | 最大取值范围与double相同，给定decimal的有效值范围由M和D决定 | |
举例：
decimal(M,D) M为精度，D为标度
decimal(6,2)数据长度为6，小数点保留2位
float也可以通过M,D设置一个宽度指示器和一个小数点指示器。
double类型要比float精度要高，如果需要精确到小数点10位以上，那么我们就用double类型，普通情况下float就够用了
在mysql中，定点数的精度要比浮点要高，而且，浮点数会出现误差，如果对数据的精度要求较高的话，应该选择定点数。
3.日期与时间类型
| 日期和时间类型 | 字节 | 最小值 | 最大值 |
| Date 年月日 | 4 | 1000-01-01 | 9999-12-31 |
| Datetime 年月日时分秒 | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
| Timestamp 时区对应时间 | 4 | 19700101080001 | 20380119111407 |
| Time 单独表示时间 | 3 | -838:59:59 | 838:59:59 |
| Year | 1 | 1901 | 2155 |
create table yy(aa date,bb datetime,cc timestamp,dd time,ff year);
可以看出timestamp默认是不可为空，当不给设置值时，会默认填充当前的时间
INSERT INTO yy(aa,bb,dd,ff) VALUES('2018-10-13','2018-10-13 20:01:30','20:02:10','2018');
4.字符串类型
| 字符串类型 | 字节 | 描述 |
| char(m) | M | M为0-255之间 |
| varchar(m) | M为0-65535之间的整数，值的长度为+1个字节 | |
| tinytext | 允许长度为0-255字节，值为长度+2字节 | |
| text | 允许长度为0-65536字节，值为长度+3个字节 | |
| mediumtext | 允许长度0-167772150字节 值为长度+3个字节 | |
| longtext | 允许长度0-4294967295字节，值为长度+4个字节 |
char(5)比如这行实际只用了3个字节，但是他还是占用5个字节的空间
varchar(5)比如这行实际值只用了3个字节，它就只占用4个字节的存储空间。
text类型是一种特殊的字符串类型，text只保存字符数据，比如：新闻内容
5.二进制类型
| 二进制类型 | 取值范围 |
| BINARY(M) | 字节书为M，允许长度为0-M的定长二进制字符串 |
| VARBINARY(M) | 允许长度为0-M的变长二进制字符串，字节数为值的长度+1 |
| TINYBLOB | 可变长度二进制数据，最多255字节 |
| BLOB | 可变长度二进制数据，最多（2**16-1）个字节 |
| MEDIUMBLOB | 可变长度二进制数据，最多（2**24-1）个字节 |
| LONGBLOB | 可变长的而机智数据，最多（2**32-1）个字节 |
binary(10)
varbinary(20)
都是存储普通二进制字符串型，两者的区别和char 和 varchar一样，一个占用实际字节，一个占用分配对固定字节。
text类型和blob类型很相似，text只能存储字符数据，纯文本之类的。选择text类型。
blob类型可以存储二进制数据，可以存储图片pdf等二进制数据。
6.枚举类型
| 枚举类型 | 取值范围 | 描述 |
| enum | 0～65535 | 多个值中只能选取一个 |
| set | 0~64 | 多个值中可选取多个 |
CREATE TABLE userinfo( gender enum('man','woman'), hobby set('youyong','pashan','lanqiu') );
INSERT INTO cc(aa,bb) VALUES('man', 'youyong,lanqiu');
enum类型可以有65535个成员，而set类型最多只能包含64个成员，两者取之范围只能在成员列表中选取，enum类型只能从成员中选取一个，而set类型可以选取多个，
enum用法：对于多个值中选取一个时，可以选择enum类型，比如，性别（男女）二选一
set用法：比如个人爱好，可以选择多个，这时可以使用set类型
7.练习
请根据以下条件合理设计表的字段。用户量为100W左右。表名为new_sxkj
字段1: 用户ID，ID为数字
字段2: 用户性别
字段3: 用户个人爱好
字段4: 用户家庭地址
字段5: 用户年龄
字段6: 用户个人简介
字段7: 用户个人照片
CREATE TABLE new_sxkj( idx INT, name varchar(60), gender enum('man','women'), hobby set('swim','football','song','dance'), address varchar(200), age TINYINT, introduce text, photo BLOB );
04-Mysql数据库操作
1.创建数据库
语法：create database 数据库名; //创建数据库
命令：show databases; //查看已经存在的数据库
语法：use database; //进入数据库
命令：select database(); //查看当前所在数据库
create database db2; show databases;
use db1;
select database();
语法：\! shell-command //调用shell命令
\! clear //清屏
2.删除数据库
语法：drop database 数据库名字; (慎用此命令)
drop database db2; show databases;
3.存储引擎简介
1.innoDB 引擎(引擎是针对表的)
支持主键自动增长
支持外键
innodb引擎中，创建表的表结构存储在*.frm文件中的。
数据文件：*.myd
索引文件：*myi
表定义：*.frm
优势：在于提供了良好的事务管理，崩溃修复能力和并发控制，
缺点：读写效率稍差，占用数据空间相对较大
2.MyISAM引擎
MyISAM存储表分为3个文件，文件与表明相同，扩展包括frm,MYD,MYI ，文件结构跟innoDB一样
*.frm //表结构文件
*.MYD //数据文件
*.MYi //索引文件
优势：占用空间小，处理速度快
缺点：不支持事务日志的完整性和并发性
3.MEMORY引擎
特殊的引擎，所有数据都存放在内存当中，在企业环境当中，几乎不用（有redis或memcache替代）。
优点：存储数据快
缺点：缺乏稳定性和完整性
4.存储引擎查看
mysql可以选择多种存储引擎及不同的存储方式，是否进行事务处理等。
1.查询mysql支持的引擎
命令：show engines;
show engines\G; +G更改显示方式
mysql> mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
2.查询mysql支持引擎信息
命令：show variables like '%have%';
mysql> show variables like '%have%'; +------------------------+----------+ | Variable_name | Value | +------------------------+----------+ | have_compress | YES | | have_crypt | YES | | have_dynamic_loading | YES | | have_geometry | YES | | have_openssl | DISABLED | | have_profiling | YES | | have_query_cache | YES | | have_rtree_keys | YES | | have_ssl | DISABLED | | have_statement_timeout | YES | | have_symlink | YES | +------------------------+----------+
3.查询当前所使用的存储引擎
命令：show variables like '%storage%'
mysql> show variables like '%storage%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+
也可以查看配置文件中的default-storage-engine对应的值，如果想换成其他引擎，直接修改值即可。
windows下为my.ini文件，Linux下为my.cnf文件
default-storage-engine=INNODB
5.如何选择存储引擎
目前常用的mysql引擎，几乎都是innoDB。
05-创建，修改，删除表
1.创建表
语法：create table 表名(
字段名 数据类型 完整的约束条件,
字段名 数据类型 完整的约束条件,
...
字段名 数据类型
);
create table user_info( user_idx int, name varchar(30), age int,gender boolean); mysql> desc user_info; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | user_idx | int(11) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | | age | int(11) | YES | | NULL | | | gender | tinyint(1) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+
2.表的完整性约束
| 约束条件 | 说明 |
| primary key | 该字段为表的主键，具备唯一性 |
| foreign key | 该字段为表的外键，与某表的主键联系 |
| not null | 该字段不能为空 |
| unique | 该字段的值是唯一的 |
| auto_increment | 该字段的属性值自动增加 |
| default | 为该字段设置一个默认值 |
1.primary key 主键
主键是一个表的特殊字段，这个字段是表中每条信息的唯一标示，主键和记录的关系，跟人和身份证的关系一样的，主键用来标示每条记录，每条记录的主键值都不同。
主键可以帮助mysql以最快的速度查找列表中的某一条信息，主键必须满足的条件，就是它必须是唯一的。
主键不能为空，可以是单一的字段，也可以是多个字段的组合。
create table user_info2(user_idx int primary key,name varchar(30)); mysql> desc user_info2; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | user_idx | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | +----------+-------------+------+-----+---------+-------+
命令: show create table user_info2; //查看表的具体信息
mysql> show create table user_info2; +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user_info2 | CREATE TABLE `user_info2` ( `user_idx` int(11) NOT NULL, `name` varchar(30) DEFAULT NULL, PRIMARY KEY (`user_idx`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
2.foriegn key 外键
外键也是表的一个特殊的字段，如果aa是B表的一个属性且依赖于A表的主键，那么A表被称为父表，B表被称为子表
创建多主键表：
mysql> create table more_pri(user_idx int,name varchar(30),age int(3),primary key(user_idx,age)); mysql> desc more_pri; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | user_idx | int(11) | NO | PRI | NULL | | | name | varchar(30) | YES | | NULL | | | age | int(3) | NO | PRI | NULL | | +----------+-------------+------+-----+---------+-------+
创建外键表
create table foriegn_tab( user_idx int primary key, salary float, department varchar(20), constraint c_fk foreign key(user_idx) references more_pri(user_idx)on delete cascade on update cascade);
插入数据
insert into more_pri values(110,'张进','28'); insert into foriegn_tab values(110,20000,'IT');
修改主表user_idx
update more_pri set user_idx = 10 where user_idx = 110; mysql> select * from more_pri; +----------+--------+-----+ | user_idx | name | age | +----------+--------+-----+ | 10 | 张进 | 28 | +----------+--------+-----+ mysql> select * from foriegn_tab; +----------+--------+------------+ | user_idx | salary | department | +----------+--------+------------+ | 10 | 20000 | IT | +----------+--------+------------+ 1 row in set (0.00 sec)
删除主表数据：
mysql> delete from more_pri where user_idx=10; Query OK, 1 row affected (0.01 sec) mysql> select * from more_pri; Empty set (0.00 sec) mysql> select * from foriegn_tab; Empty set (0.00 sec)
