MySQL传智播客--Lession4-Lession12笔记

create database php_1;
alter database php_1 character set gbk;


varchar(20) // 表示20个字符，即无论是数字，字母，汉字都是可以存20个
// 字符：人们使用的记号，抽象意义上的一个符号。
// “字符”分别用一个字节还是多个字节存储，用哪些字节来存储，这个规定就叫做“编码”。
// “编码”的概念就是把“字符”转化成“字节”
============================ 04讲==表创建=============================
create table php_one.php_class(
class_no varchar(20),           
date_start date
);


// 遇到数据库和表名都所特殊符号时要分开用
// 反引号包裹，若不分开一起包裹会被认为是一个表的名字
// 如下：


create table `1234`.`itcast_class`(
class_no varchar(20),
date_start date
);
=============================== 05讲====crud=====2016-6-23==================
// 创建学生管理
create table info_student(
name varchar(20),
stu_no varchar(20)
);


// 创建在线考试
create table exam_student(
name varchar(20),
stu_no varchar(20),
score int
);
create table exam_question(
content varchar(100),
answer varchar(20)
);


// 显示前缀为 exam_ 的表
show tables like 'exam_%'; // % 为通配符，任意字符任意个数的组合叫通配符


// 某表的创建信息
show create table table_name;
show create table table_name \G
show create table exam_student;
show create table exam_student \G


// 描述表结构
describe table_name;
desc table_name; // 简写
desc exam_student;


// 删除表
drop table table_name;
// 完整的删除表的表述
drop table if exists table_name;
// 适用与数据库database
drop database if exists db_name;


// 修改表
rename table oldname to newname; 
// 支持同时修改多个表
rename table t1 to tx, t2 to ty;


// 延伸：交换两个表的名称，只要两个数据库在同一个磁盘上，
// 也可以从一个数据库更名到另一个数据库
// 创建一个新的数据库，旧数据库内到表，都可以rename到新的数据库内，删除旧的数据库
// 交换2个表old_table和new_table的表名
rename table old_table to backup_table, 
->new_table to old_table, 
->backup_talbe to new_table;


// 修改列定义
// 修改表结构，上面的是子命令，上级是，alter table table_name add | drop | change | modify
// 增加一个新的列
Add
alter table exam_student add height int;
// 删除一个列
Drop
alter table exam_student drop height;
// 修改一个列的定义
Modify
alter table exam_student modify stu_no varchar(40); // 原来stu_no varchar(20)
// 重命名一个列
Change (注意和modify的对比)
alter table exam_student change score fenshu int; // 将列名score改为fenshu


修改表选项
alter table tbl_name新的表选项
alter table exam_student character set utf8; // 同样适用与数据库


============================== 06讲=简单操作=====2016-6-24==================
数据操作
创建数据(插入数据)，获得数据(查询数据)，删数据，修改数据


创建数据：
Insert into 表名(字段列表)values(值列表)
insert into exam_student(name,stu_no)values('xiaowang','php0331_001');
insert into exam_student values('xiaowu','php0331_002',98);
insert into exam_student values('xiaoqiang','php0331_003',58);


获得数据：
select字段列表from表名 查询条件。
// 字段列表，可以使用*代替，表示所有到字段，
select * from exam_student;
// 查询条件可以省略，表示所有的记录都获得，相当于where 1;
select name, stu_no from exam_student;
// 与上一条语句的效果等价
select name, stu_no from exam_student where 1;
// 有条件的查询
select * from exam_student where fenshu >= 72;


删除数据：
Delete from表名 条件
delete from exam_student where fenshu < 72;
// 删除需要在逻辑上给条件，否则容易造成数据误操作。导致损失。
// 语法上可以没有where


// 可以：如果需要删除所有数据，使用where 1;


修改数据：
Update 表名 set 字段 = 新值，... 条件
update exam_student set fenshu=100 where fenshu >= 72;
============================== 07讲=练习=====2016-6-25==================
//交换两个表的操作
rename table old_table to backup_table, 
->new_table to old_table, 
->backup_talbe to new_table;


设计几个表：
学生：
教室：
讲师：
班级：
（体现出来它们之间的一些对应关系）
============================== 08讲=校对规则=====2016-6-25==================
show variables;
show variables like 'character_set%';
校对规则：
每一套，编码字符集都有与之相关的校对规则。
什么是校对规则，在当前编码下，字符之间的比较顺序是什么？


每个字符集都支持不定数量到校对规则，可以通过如下指令：
show collation;
可以看到：
...
latin1_bin
utf8_unicode_ci
latin7_general_cs 
...
字符集_地区名_比较规则(ci,cs,bin)//ci不区分大小写,cs区分大小写，bin字节比较


可以使用like:
show collation like 'utf8%';
show collation like 'gbk%';


alter database test1 character set utf8;
在设置字符集的时候，可以设置当前字符集所采用的校对规则，如果不设置校对规则，
每一个字符集，都有一个默认的校对规则。
// 以gbk为例，比较chinese_ci ,bin之间的区别


create table tb13(
name varchar(10)
) character set gbk collate gbk_chinese_ci;


create table tb14(
name varchar(10)
) character set gbk collate gbk_bin;


insert into tb13 values('a');
insert into tb13 values('B');
insert into tb13 values('c');


insert into tb14 values('a');
insert into tb14 values('B');
insert into tb14 values('c');


// Order by 关键字，可以在获得数据时，将数据进行按照某个字段的排序。


select * from tb13 order by name;
============================== 09讲=数值类型=====2016-6-27==================
《一》整型
tinyint: 
1个字节：
-128 -- 127有正负（有符号）
0--255（无符号）


//是否有符号，可以在定义时，使用 unigned标识，没有符号，不写就认为有符号


create table tbl_int(
a tinyint unsigned,
b tinyint
);
insert into tbl_int values(255, 127);


insert into tbl_int values(256, 128);
//定义显示宽度
通过规定数据的显示宽度，达到统一显示的目的 
类型(M)M表示显示的最小宽度是多少。


需要使用前导零填充达到目的。称之为zerofill


alter table tbl_int add c tinyint(2) zerofill;


insert into tbl_int values(0, 127, 1);
insert into tbl_int values(0, 127, 9);
insert into tbl_int values(0, 127, 10);
insert into tbl_int values(0, 127, 123);


注意：
1.不影响数的范围
2.宽度大的不影响，不会截取
也存在布尔bool类型，但是就是tinyint(1)的别名


《二》小数：
// 1.浮点：小数位数可以变化。
float单精度:默认精度位数6位左右
double双精度:默认精度位数15位左右
create table num_2(
a float,
b double);


insert into num_2 values(123456789.012345679,1234567890.123456789);
mysql> select * from num_2;
+-------------+------------------+
| a           | b                |
+-------------+------------------+
| 1.23457e+08 | 1234567890.12346 | 
+-------------+------------------+


支持，控制数值的范围。
Type(M,D)
M，表示所有的数值位数（不包括小数点，和符号）
D，表示允许的小数位数
如：
M,D
5,2
-999.99 to 999.99
8,3
-99999.999 to 99999.999
create table num_3(
a float(5,2),
b double(8,3)
);
insert into num_3 values(567.12,1234.589);
insert into num_3 values(567.12,123456.5);


浮点数支持科学计数法
insert into num_3 values(0.23E3,456.7E2);
结果如下：
| 230.00 | 45670.000 | 
+--------+-----------+


// 2.定点数：
decimal(M,D)
M总位数
D小数位数。（范围存在）
M默认为10，D默认为0
create table num_4(
send_money decimal(10,2)
);
insert into num_4 values(1234.56);
insert into num_4 values(1234.567);
insert into num_4 values(12345678.56);


小数也支持zerofill
alter table num_4 add money decimal(10, 2) zerofill;
insert into num_4 values(1234.56, 1234.56);
支持无符号


========================MySQL_10讲=时间类型===============================
日期时间：


年月日时分秒: Datetime


时间戳:Timestamp //存储时，整型，但是表示时，日期时间 只能从1970.1.1 00:00:00 到
//2038-01-19 03:14:07 


create table dt_1(
a datetime,
b timestamp
);


insert into dt_1 values('2016-7-1 20:06:20', '2016-7-1 20:06:22');
insert into dt_1 values('2039-01-19 03:14:08', '2039-01-19 03:14:08');


检索列时，+0 可以检索当前的时间戳
检索不加 0：
mysql> select * from dt_1;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2016-07-01 20:06:20 | 0000-00-00 00:00:00 | 
| 2016-07-01 20:06:20 | 2016-07-01 20:06:22 | 
| 2038-01-19 03:14:08 | 2038-01-19 03:14:08 | 
| 2039-01-19 03:14:08 | 0000-00-00 00:00:00 | 
+---------------------+---------------------+
检索加 0：
mysql> select a,b+0 from dt_1;
+---------------------+----------------+
| a                   | b+0            |
+---------------------+----------------+
| 2016-07-01 20:06:20 |              0 | 
| 2016-07-01 20:06:20 | 20160701200622 | 
| 2038-01-19 03:14:08 | 20380119031408 | 
| 2039-01-19 03:14:08 |              0 | 
+---------------------+----------------+


支持任意分割符的日期
insert into dt_1 values('2016:01:19 03:14:08', '2016:01:19 03:14:08');
mysql> select * from dt_1;
+---------------------+---------------------+
| a                   | b                   |
+---------------------+---------------------+
| 2016-07-01 20:06:20 | 0000-00-00 00:00:00 | 
| 2016-07-01 20:06:20 | 2016-07-01 20:06:22 | 
| 2038-01-19 03:14:08 | 2038-01-19 03:14:08 | 
| 2039-01-19 03:14:08 | 0000-00-00 00:00:00 | 
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 
| 2016-01-19 03:14:08 | 2016-01-19 03:14:08 | 
+---------------------+---------------------+
支持2位年份：
70-69 1970-2069
70-99 19xx
0-99  20xx
不建议使用
insert into dt_1 values('20:01:19 03:14:08', '2016:01:19 03:14:08');
insert into dt_1 values('89:01:19 03:14:08', '2016:01:19 03:14:08');


mysql> select * from dt_1;
| 2020-01-19 03:14:08 | 2016-01-19 03:14:08 | 
| 1989-01-19 03:14:08 | 2016-01-19 03:14:08 | 
+---------------------+---------------------+


支持0值：
表示当前是没有规定：
2014-04-0 表示4月整个月。（逻辑想法）
====================MySQL_11讲=时间类型+字符串类型===========================
Time类型：
表示意义：
1.一天中的时间
2.表示时间间隔，在表示间隔时，可以使用天来表示。
格式：
D HH：MM：SS
D 天
create table t_1(ago time);
insert into t_1 values('23:12:11');
insert into t_1 values('231212');
insert into t_1 values('23,12,13');
insert into t_1 values('23 12 14');
mysql> select * from t_1;
+-----------+
| ago       |
+-----------+
| 23:12:11  | 
| 23:12:12  | 
| 00:00:23  | 
| 564:00:00 | 
+-----------+


insert into t_1  values('5 12:34:11');
| 132:34:11 | 
+-----------+


Year类型：（日期范围：1901-2155一个字节表示）
create table y_1(`begin` year);
insert into y_1 values(1234);
insert into y_1 values(1985);
mysql> select * from y_1;
+-------+
| begin |
+-------+
|  0000 | 
|  1985 | 
+-------+
如果做一个PHP项目，通常，保存时间都是时间戳，是unix时间戳，直接以整型的形式保存在数
据库内，不是mysql提供timestamp类型


--------------字符串类型：--------------------------
char(M)
varchar(M)
M表示允许的字符串长度。
M在varchar中的表示，是允许的最大长度。
char内M表示严格限定的长度


varchar对存储空间的控制要比char好（存长度不固定的数据）
char   存储操作速度快（一般存固定的长度的如身份证号，学号，手机号码）


alter database test1 character set utf8;
mysql> alter database test1 character set utf8;


create table s_1(
a char(18),
b varchar(18)
);


create table s_2(a varchar(65535));
ERROR 1074 (42000): Column length too big for column 'a' (max = 21845); 
use BLOB or TEXT instead // 因为utf8是3个字节表示一个字符65535=21845*3


create table s_2(a varchar(65535)) character set gbk;
ERROR 1074 (42000): Column length too big for column 'a' (max = 32767); 
use BLOB or TEXT instead// 因gbk是3个字节表示一个字符65535/2=32767


create table s_2(
a varchar(21845)
)character set utf8;
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
// 因为行的最大的长度也是有规定的varchar(21845) character set utf8; 太大了


注意：字段的最大长度，除了类型本身限制之外，记录的总长度也有限制
create table s_2(
a varchar(21844)
)character set utf8; // 则可以
mysql> create table s_3(
    -> a varchar(65532)
    -> )character set latin1;
Query OK, 0 rows affected (0.04 sec)




create table s_3(
a varchar(65532)
)character set latin1; // 可以创建成功


create table s_4(
a varchar(65533)
)character set latin1; // 不可以创建成功


mysql> create table s_4(
    -> a varchar(65533)
    -> )character set latin1;
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




create table s_4(
a varchar(65533) not null
)character set latin1; // 可以创建成功


mysql> create table s_4(
    -> a varchar(65533) not null
    -> )character set latin1;
Query OK, 0 rows affected (0.01 sec)
真是的varchar的长度：
总长度65535
varchar特点，当类型数据超过255个字符时，采用2个字节表示长度。 65535-2=65533
整条记录，需要一个额外的字节，用于保存当前字段的null值。
除非==所有的字段==都不能为null,这个字节才可以省略。
一个记录，不论有多少个字段，存在null，都是使用统一的字节来表示。
而不是每一个字段一个字节


create table s_6(
a varchar(65532) not null,
b tinyint //====
)character set latin1;
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


create table s_6(
a varchar(65532) not null,
b tinyint not null //====
)character set latin1;
Query OK, 0 rows affected (0.08 sec)


Text类型:
2^16
文本，有很多兄弟类型
Tinytext longtext
表示的字符串长度不一样


create table s_7(
a text,
b text
)character set latin1;


enum枚举类型:
create table s_8(
gender enum('female', 'male')
);
insert into s_8 values('female');
insert into s_8 values('male');
insert into s_8 values('other');


mysql> select * from s_8;
+--------+
| gender |
+--------+
| female | 
| male   | 
+--------+
2 rows in set (0.00 sec)


select gender+0 from s_8;
mysql> select gender+0 from s_8;
+----------+
| gender+0 |
+----------+
|        1 | 
|        2 | 
+----------+
2 rows in set (0.03 sec)


mysql> insert into s_8 values('2');
Query OK, 1 row affected (0.00 sec)


mysql> select * from s_8;
+--------+
| gender |
+--------+
| female | 
| male   | 
| male   | 
+--------+


set集合类型 (8个字节，8*8==64个状态)
create  table s_9(
hobby set('basket','football','pingpang')
); //用位或来存储1,10,100


insert into s_9 values('basket,football');
mysql> select * from s_9;
+-----------------+
| hobby           |
+-----------------+
| basket,football | 
+-----------------+


mysql> select hobby+0 from s_9;
+---------+
| hobby+0 |
+---------+
|       3 | // bit:1 | 10 == 11 == 3
+---------+


insert into s_9 values('basket,pingpang');
mysql> select hobby+0 from s_9;
+---------+
| hobby+0 |
+---------+
|       3 | 
|       5 | // bit: 1 | 100 ==  101 == 5
+---------+


================12讲==选择类型=================


列类型-如何选择
参考原因：
选择列类型：
1.尽量精确（如身份证选char(18),人的年龄用tinyint）--最重要的
2.考虑应用语言处理(考虑数据库和语言的特性)
3.考虑兼容性