MySQL笔记--2、3、4、5

1、目的：设置合适的数据类型给字段

一、整数类型

例如
（1）create table student（xh char(10),nl tinyint unsigned,sr dataime,dz varchar(200)）
（2）score tinyint unsigned

二、小数类型

decimal可以用来表示货币
decimal(5,2)表示小数取值范围-999.99~999.99（表示五位，小数点后面去两位）

三、字符串类型

分析定长和变长如：
xm char(255)
xm varchar(255)
若是姓名为小明，那么第一个占的存储空间是255，varchar的存储空间为2
定长：性能更好，效率高
变长：节省存储空间
比如学号，开头为0的话要用char，所有还要考虑环境

四、日期

（范围要自己实验看看）
1、date
存储数据格式为：YYYY-MM-DD，它支持的范围为’1000-01-01’到’9999-12-31’，并且允许使用字符串或数字为此列复制
2019-12-12
2、datetime
日期和时间的组合，存储格式为：YYYY-MM-DD HH:MM:SS，它支持的范围为’1000-01-01 00:00:00’到’9999-12-31 23:59:59’，并且允许使用字符串或数字进行分配值。
3、time
MySQL以’HH:MM:SS’格式检索和显示TIME值(或对于大的小时值采用’HHH:MM:SS’格式)。TIME值的范围可以从’-838:59:59’到’838:59:59’。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)

4、year
MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。

• 可以指定各种格式的YEAR值：
• 四位字符串，范围为’1901’到’2155’。 四位数字，范围为1901到2155。
• 两位字符串，范围为’00’到’99’。'00’到’69’和’70’到’99’范围的值被转换为2000到2069和1970到1999范围的YEAR值。
• 两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串’0’或’00’或它被解释为0000。
• 函数返回的结果，其值适合YEAR上下文，例如NOW()。 非法YEAR值被转换为0000。

MySQL笔记3----创建和管理数据库

主要讲解字符集和字符序
目的：MySQL默认不是中文，所以会有中文乱码，了解并解决
1、字符集
字符（Character）是人类语言最小的表义符号，例如‘A’、‘B’等。给定一系列字符，对每个字符赋予一个数值，用数值来代表对应的字符，这个数值就是字符的编码（Character Encoding）。
给定一系列字符并赋予对应的编码后，所有这些“字符和编码对”组成的集合就是字符集(Character Set)
2、字符序
字符序(Collation)是指在同一字符集内字符之间的比较规则。一个字符集包含多种字符序，每个字符序唯一对应一种字符集。
MySQL字符序命名规则是：以字符序对应的字符集名称开头，以国家名居中（或以general居中），以ci、cs或bin结尾。
ci表示大小写不敏感，cs表示大小写敏感，bin表示按二进制编码值比较。
3、命令
（1） show character set;
使用MySQL命令即可查看当前MySQL服务实例支持的字符集、字符集默认的字符序以及字符集占用的最大字节长度等信息

• latin1支持西欧字符、希腊字符等
• gbk支持中文简体字符
• big5支持中文繁体字符
• utf8几乎支持世界所有国家的字符。utf8mb3

（2）show variables like ‘character%’;
即可查看当前MySQL服务实例使用的字符集。

• character_set_client：MySQL客户机字符集
• character_set_connection：数据通信链路字符集，当MySQL客户机向服务器发送请求时，请求数据以该字符集进行编码。
• character_set_database：数据库字符集
• character_set_filesystem：MySQL服务器文件系统字符集，该值是固定的binary

修改字符集
方法1：修改my.ini配置文件，可修改MySQL默认的字符集。

方法2：MySQL提供下列MySQL命令可以“临时地”修改MySQL“当前会话的”字符集以及字符序。

方法3：使用MySQL命令 “set names gbk;”可以“临时一次性地”设置character_set_client、character_set_connection以及character_set_results的字符集为gbk

方法4：连接MySQL服务器时指定字符集
mysql --default-character-set=字符集 -h 服务器IP地址 -u 账户名 –p密码
实验（及作题的答案方法）：

1、打开MySQL 6.0命令行客户端，使用MySQL命令查看当前MySQL服务实例支持的字符集、字符集默认的字符序以及字符集占用的最大字节长度等信息
答：show character set;
2、查看当前MySQL服务实例使用的字符集
答：show variables like ‘character%’;
3、查看my.ini 文件
答：在MySQL的目录下，版本不同可能会不同
4、打开MySQL 6.0命令行客户端，执行以下语句，“临时地”修改MySQL“当前会话的”字符集以及字符序
答：

5、打开MySQL 6.0命令行客户端，创建两个数据库，一个命名为（名字第一个拼音字母，都设为小明）+test，另一个命名为schooltest
答：
（1）create database xmtest;
（2）create database schooltest;
6、写出命令及执行命令查看所有数据库名称
答：show databases;
7、使用命令查看新建数据库
答：show create database xmtest;
8、删除数据库schooltest
答：drop database schooltest;
9、使用命令指定当前数据库为（名字第一个拼音字母）+test
答：use xmtest;
10、使用命令查看数据库schooltest所在文件夹路径
答：show global variables like “%datadir%”
11、修改数据库schooltest，使得数据库支持中文字符
答：alter database xmtest default character set = gbk;

MySQL笔记4----mysql数据表管理

路线：
1、存储引擎
2、设置默认的存储引擎
3、数据表操作
4、InnoDB表空间

1、 MySQL提供了插件式（Pluggable）的存储引擎，存储引擎是基于表的，同一个数据库，不同的表，存储引擎可以不同。甚至同一个数据库表，在不同的场合可以应用不同的存储引擎
show engines;
即可查看MySQL服务实例支持的存储引擎

（1）InnoDB存储引擎的特点

• 支持外键（Foreign Key）
• 支持事务（Transaction）：如果某张表主要提供OLTP支持，需要执行大量的增、删、改操作（insert、delete、update语句），出于事务安全方面的考虑，InnoDB存储引擎是更好的选择。
  最新版本的MySQL已经开始支持全文检索

（2）MyISAM存储引擎的特点
MyISAM具有检查和修复表的大多数工具。

• MyISAM表可以被压缩
• MyISAM表最早支持全文索引
• 但MyISAM表不支持事务
• 但MyISAM表不支持外键（Foreign Key）。
  如果需要执行大量的select语句，出于性能方面的考虑，MyISAM存储引擎是更好的选择
  （常用来查询银行的历史账单，比如：淘宝查询一年的账单）
  2、 MySQL5.6默认的默认的存储引擎是InnoDB。
  使用MySQL命令
  set default_storage_engine=MyISAM;[8.0版本]
  set storage_engine=MyISAM；[6.0/5.5/5.7版本]
  可以“临时地”将MySQL“当前会话的”存储引擎设置为MyISAM，使用MySQL命令“show engines;”可以查看当前MySQL服务实例默认的存储引擎
  3、MySQL数据表操作

create [temporary] table [IF NOT EXISTS] tbl_name [([column_definition],…|[index_definition])]
[table_option] [select_statement];

（1）TEMPORARY：temporary创建临时表（不加TEMPORARY创建的表称为持久表，持久表一旦创建将一直存在，多个用户或者多个应用程序可以同时使用持久表），临时表的生命周期短，而且只能对创建它的用户可见，当断开与该数据库的连接时，MySQL会自动删除临时表
（2）IF NOT EXISTS：建表前先判断，用以避免出现表已存在无法再新建错误
（3）table_option：用于描述表的选项
（4）table_name：要创建的表的表名。该表名必须符合标志符规则，如果表名中包含MySQL保留字，则必须用单引号括起来。
（5）column_definition：列定义，包括列名、数据类型，可能还有一个空值声明和一个完整性约束。
（6）index_definition：表索引项定义，主要定义表的索引、主键、外键等，具体定义将在第5模块中讨论。
（7）select_statement：可以在CREATE TABLE语句的末尾添加一个SELECT语句，在一个表的基础上创建表

column_definition：用于列定义，其格式如下：
col_name type [NOT NULL | NULL] （提醒：默认空、非空）[DEFAULT default_value]（默认值，比如默认男的）
[AUTO_INCREMENT] （自增，比如整形的主键）
[UNIQUE [KEY] （唯一）
| [PRIMARY] KEY]（主键就=为空加唯一）
[COMMENT ‘string’]（注释）
[reference_definition]（外键）

实验
（1）将teacherInfo表的的存储引擎更改为MyISAM类型
答：set storage_engine=MyISAM；
（2）创建表格student（前面实验时更改存储引擎为MyISAM，注意这个表修改这个表的存储引擎，因为这个表是要给别人用的需要支持外键）
例如：

mysql> USE xsks;
mysql> CREATE TABLE student (
zkzh char(20) not null primary key,
name char(8) not null,
major char(20) null,
birth date not null,
sex tinyint not null default 1,
score tinyint null,
address char(60) not null,
phone char(11) not null,
picture longblob null,
note text null
) Engine=Innodb;

（3）将teacher表的teacher_name字段的数据类型改为VARCHAR(30)
答：alter table teacher change teacher_name teacher_name varchar(30);
（4）将teacher表的teacher_no字段改名为t_id
答：alter table teacher change teacher_no t_id char(10)

（5）将teacher表的的teacher_contact字段删除
答：alter table teacher drop column teacher_contact;
（6）在teacher表的中增加名为teacher_con的字段，数据类型为varchar(50)
答：alter table teacher add column teacher_con varchar(50);
（7）将teacher表的改名为teacherInfo
答：alter table teacher rename to taecherlnfo;

（8）复制表classes为新的表class
答：
create table class like classes;（复制表结构，无值）
create table class1 as (select *from classes);（全复制）
create table class2 as(select zkzh,name,sex from student);（复制选定的列和值）
（9）删除classes表
答：drop table if exists classes;

（10）查看表teacherInfo表的结构
答：desc teacherlnfo;
（11）修改默认值为s
alter table student alter sex set default ‘s’;

加：set names gbk;（设置中文）
create table student（zkzh char(10) primary key,name char(50) not null,sex char(2) default ‘女’,picture longblob,note text）engine=innodb;
MySQL5—表的约束

1、MySQL支持的常用约束条件有6种：

• 主键（primary key）约束
• 外键（foreign key）约束
• 非空(not NULL)约束
• 唯一性( unique )约束
• 默认值( default)约束
• 自增约束（auto_increment）
• 检查约束（check）（不执行）

2、
（1）主键（primary key）约束
一张表中只允许设置一个主键，是一个字段或字段组
设置主键通常有两种方式：

• 表级完整性约束
• 列级完整性约束
  第一种， 列级完整性约束
  
  第二种、表级完整性约束
  
  例如：
  use studentInfo;
  create table SC(
  sno char(10) not null,
  cno char(10) not null,
  grade int not null default 0,
  primary key(sno,cno)
  )engine=MyISAM default charset=utf8 collate=utf8_bin;
  engine=MyISAM （存储引擎）
  default charset=utf8 （字符集）
  collate=utf8_bin（字符序）

（2）外键（foreign key）约束
外键的有两种方式，
在表级完整性下定义外键约束，
在列级完整性下定义外键约束

例如
sc是子表，student是父表
第一个sno是sc表的，后面的是student，数据类型完全一样，名字可以不同
注：外键写在子表的身上、这两个列名字最好相同，数据类型要一样、被引用的主表的列要是主键

create table SC(
sno char(10) not null ,
cno char(10) not null,
grade int not null default 0,
primary key(sno,cno),
foreign key (sno) references student(sno)
)engine=InnoDB default charset=utf8 collate=utf8_bin;
（3）非空(not NULL)约束
语法规则: 字段名 数据类型 not null
（4）唯一性( unique )约束
语法规则: 字段名 数据类型unique
（5）默认值( default)约束
语法规则: 字段名 数据类型[其他约束条件] default默认值
（6）自增约束（auto_increment)
主键+整形才可以用自增

（7）检查约束（check）

实验
（1）在创建数据库前将MySQL的默认存储引擎设为：innodb
set default_storage_engine=innodb;
（2）查看表
show tables;
（3）命令查看student数据表的创建结果
describe student;
（4）创建外键需要

1. 两个表必须是InnoDB表
2. 外键列必须建立了索引
3. 外键关系的两个表的列必须拥有相同的数据类型，或者是可以相互转换类型的列