mysql--DML--视图，索引，事务，字符集，函数

Mysql中,表/列可以改名,database不能改名.
phpMyAdmin似乎有这功能? 他是建新库,把所有表复制到新库,再删旧库完成的.

删除一个数据库: drop database 数据库名;

创建一个数据库: create database 数据库名 [charset 字符集]

查看一下所有的库,怎么办?
Mysql>Show databases;
选库语句: Use 库名

删除表: drop table stu;



mysql> create table t1(id int,name varchar(10));
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> show create table t1;
+-------+--------------------------+
|Table  | CreateTable               |
+-------+--------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------+

//表的列，3大类，
数值型（全数字）：
    整型：int，存储数字1占据4个字节，32位00000000 00000000 00000000 00000001，最大2^32-1(42多亿),有符号的-2^31-----2^31-1,
    TINYINT:一个字节8位，0-255，-128-127，
    SMALLINT:2个字节，0-65535，
    MEDIUMINT:3个字节
    INT:4个字节
    BIGINT：8个字节
    zerofill M : zerofill适合于学号编码等固定宽度的数字，可以用0填充至固定宽度，M是填充值多宽。
    
    浮点型：float(M,D)[UNSIGNED][ZEROFILL],M总位数，D是小数点后几位 。  double大小不一样而已。
    
    定点型decimal：更精确。

字符串：char(定长),varchar（变长）,text,
    char(10)最大存10个字符，只给1个字符还是给10个字符长度，内部用空格补充，取出时右侧空格删除。
    varchar(10)最大10个字符，只给1个字符,还有一个额外数字标注varchar的长度。
    20个字符以下用char，以上用varchar.速度上定长会快些。char最大255，varchar最大6万多。
    text，mediumtext,longtext:可以存60000多字符，
    blob，mediumblob,longblob:二进制的，不存图像几乎不用blob,不用考虑字符集了，
    ENUM枚举：性别，
    set(集合)：
    
日期时间：2012-11-11，14：36：12
    YEAR（1985）一个字节，Date(1998-12-11), Time(13:13:55)，datetime(1998-12-13 13:56:09), 时间戳timestamp，

mysql> alter table t1 add unum tinyint unsigned;
mysql> insert into t1 values (1,'dvdfv',255);
mysql> alter table t1 add number tinyint(6) zerofill;//6位并且0填充，并且是无符号的。
mysql> alter table t1 add gender enum('一','二','三');
mysql> insert into t1 values ('ffr','一');
//NULL查询不方便，避免值为null，
create table t2 (id int not null default 0,name char(10) not null default '');

//主键：
mysql> create table t1 (id int not null primary key,name char(10) not null default '');
create table t1 (id int primary key auto_increment,name char(10) not null default '');

//表设计：定长不定长分离，常用不常用分离。

mysql> alter table temp drop column name;
mysql> alter table temp add name2 int not null default 0 after subject;
mysql> alter table temp change name11 name111 varchar(3) default '---';

----------------------------------------------------------------------------------------------------

//视图 : 虚拟表也是sql的查询结果，可以隐藏数据，视图存储的是单纯的语句，把建视图的语句和查视图的条件合并成查表的语句。也有可能视图语句比较复杂，很难和查询视图的语句合并，mysql可以先执行视图的创建语句，在内存中形成临时表，然后去查询临时表。
mysql> create view good_view as select goods_id,goods_name ,(market_price-shop_price) as less from goods limit 1 , 10;
mysql> select * from good_view;

mysql> show tables;//可以把视图查出来，视图和表对人来说没有区别，表改变视图也改变，
视图能不能更新删除添加：视图的每一行与物理表一一对应则可以。视图的行是由表多行经过计算的结果则不行。

create algorithm = merge view v7 as select * from goods;//这样每次查视图的时候就是去拼接语句然后查表

create algorithm = temp view v7 as select * from goods;//这样视图就是一个临时表

create view v7 as select * from goods;//让数据库自己判断方式

drop table t1;//删除视图
drop view goods_v;//删除视图
mysql> desc good_view;//查看视图的表结构
mysql> show table status;//查看表的详细信息
mysql> show table status where name = 'temp';//查看哪张表的详细信息
mysql> rename table temp to temptemp;//改表的名字
mysql> delete from temptemp where score = 90;//删除数据

Truncate和delete区别：Truncate是删除一张表再重新建立一张表，自增涨的信息也没了。delete之后自增涨信息还有。
删除表: drop table stu;

goods.frm：声明表的结构，建表时每一列是什么属性
goods.MYD：表内容
goods.MYI:索引信息
这3个文件有的没有，是因为引擎不一样，不指定引擎时现在默认是innodb,

乱码：字符集不一样，例如客户端是GBK的字符集（发送和接收的都是GBK）,DB是utf8的（发送和接收的都是Utf8的），中间就要有一个连接转换器。
mysql> set character_set_results=gbk;//设置返回结果的结果集的字符集
mysql> select * from temp;
+---------+-------+-------+---------+------+
| subject | name2 | score | name111 | name |
+---------+-------+-------+---------+------+
| ����    |     0 |    50 | 0       |    0 |
| Ӣ��     |     0 |    40 | 0       |    0 |
+---------+-------+-------+---------+------+
mysql> set character_set_results=utf8;
mysql> select * from temp;
+---------+-------+-------+---------+------+
| subject | name2 | score | name111 | name |
+---------+-------+-------+---------+------+
| 语文    |     0 |    50 | 0       |    0 |
| 英语    |     0 |    40 | 0       |    0 |
+---------+-------+-------+---------+------+

mysql> set character_set_client=gbk;//设置客户端的字符集，cmd窗口就是客户端，
mysql> set character_set_results=gbk;//设置结果集的字符集
mysql> set character_set_connection=gbk;//设置连接器的字符集

-------------------------------------------------------------------------------------------------

索引；数据的目录，能快速定位行所在的位置。
索引文件：树形结构，加速查找，降低了增删改的速度。一般在查询频繁的列上加，在重复率低的列上加。有可能索引文件比数据文件还要大。

索引的创建原则:
1:不要过度索引
2:在where条件最频繁的列上加.
3:尽量索引散列值,过于集中的值加索引意义不大.

key:普通索引，加快查询速度，
unique:唯一索引，加快查询速度，还能约束数据（数据不能重复）
primary：主键索引， 不能重复，把主键当成索引，唯一索引不一定是主键，主键必唯一,但是一张表上,只能有一个主键, 但是可以用一个或多个唯一索引.
fulltext ：全文索引中文无效，英文环境下可以针对值中的某个单词,一般用第三方解决方案。

mysql> create table index_t(name char(10),email char(20),key name(name),unique key email(email));//建表的最后，给name加普通索引，索引的名字就叫name，给email加唯一索引名字叫email,

mysql> desc index_t;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YE   | MU  | NULL    |       |
| email | char(20) | YE   | UN  | NULL    |       |
+-------+----------+------+-----+---------+-------+
mysql> select * from index_t;//unique key 列不能重复
+------+--------------+
| name | email        |
+------+--------------+
| lisi | loisi@q.com  |
| lisi | liwu@q.com   |
+------+--------------+

mysql> create table t1(id int,name varchar(20),email varchar(20),primary key(id),key name(name),unique key email(email));//主键索引，

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PR  | NULL    |       |
| name  | varchar(20) | YE   | MU  | NULL    |       |
| email | varchar(20) | YE   | UN  | NULL    |       |
+-------+-------------+------+-----+---------+-------+

create table test5 (
    id int,
    username varchar(20),
    school varchar(20),
    intro text,
    primary key (id),
    unique (username),
    index (school),
    fulltext (intro)
) engine myisam charset utf8;
 
 
比如邮箱，只有前几个字符不同，后面都差不多的，可以只截取某一列的前几个字符然后建索引。
mysql> create table t1(id int primary key , email varchar(10), unique key email(email(3)));

多列索引，把多列看成一个整体，建立索引。
create table t1 (xing char(2),ming char(10), key xm(xing,ming));

mysql> select * from  t1 where xing = '姚闻' ;
+------+------+-----+
| xing | ming | zzz |
+------+------+-----+
| 姚闻 | 文文 |     |
+------+------+-----+
mysql> explain select * from  t1 where xing = '姚闻';//查看索引是否发挥作用
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys（有可能使用到的索引） | key （使用的索引） | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const |     xing,xm                           | xing               | 7       | const |    1 |      100 | NULL  |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
mysql> explain select * from t1 where xing='姚闻' and ming='文文';
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | xing,ming,xm  | xing | 7       | const |    1 |      100 | NULL  |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------+

//冗余索引，某个列上有多个索引，比如建立了xm(xing,ming)联合索引又建立了ming(ming)索引，那么查询xing ming时xm索引发挥作用，查询xing时xm索引发挥作用，查询ming时ming索引发挥作用。ming这列就有2个索引。

show index from t1；//查看索引
+-------+------------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+
| t1    |          0 | ming     |            1 | ming        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | xm       |            1 | xing        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
| t1    |          1 | xm       |            2 | ming        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+------+
mysql> show create table t1;
+-------+----------------------------------+
|Table    |    CreateTable  |
+-------+----------------------------------+
| t1    | CREATE TABLE `t1` (
  `xing` char(2) DEFAULT NULL,
  `ming` char(10) DEFAULT NULL,
  `zzz` char(20) NOT NULL DEFAULT '',
  UNIQUE KEY `ming` (`ming`),
  KEY `xm` (`xing`,`ming`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------+

mysql> alter table t1 drop index ming;//删除索引，
mysql> alter table t1 add primary key(xing);//增加主键索引，
mysql> alter table t1 drop primary key;//删除主键索引，

-----------------------------------------------------------------------------------------------
函数： 列可以当成变量，可以运算。
mysql> select 3*3;
+-----+
| 3*3 |
+-----+
|   9 |
+-----+
mysql> select floor(shop_price) from goods;//取整
mysql> select rand()*5+5,floor(shop_price) from goods limit 3;
+-------------------+-------------------+
| rand()*5+5        | floor(shop_price) |
+-------------------+-------------------+
| 7.222229537761052 |              1388 |
| 6.174204159690257 |                58 |
| 9.204332338836354 |                68 |
+-------------------+-------------------+
mysql> select left(goods_name,3) from goods limit 3;//左边截3个
+--------------------+
| left(goods_name,3) |
+--------------------+
| kd8                |
| ggg                |
| htc                |
+--------------------+
mysql> select right(goods_name,3) from goods limit 3;//右边截3个
+---------------------+
| right(goods_name,3) |
+---------------------+
| 876                 |
| ttt                 |
| 0耳机               |
+---------------------+
mysql> select concat(goods_name,shop_price) from goods limit 3;
+-------------------------------+
| concat(goods_name,shop_price) |
+-------------------------------+
| kd8761388.00                  |
| gggggttttttt58.00             |
| htc原装5800耳机68.00          |
+-------------------------------+
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-12-27 16:19:31 |
+---------------------+

--------------------------------------------------------------------------------------------------

事务：
start transaction;
update temp set num=num+100 where id='a';
update temp set num=num-100 where id='b';
commit;

start transaction;
update temp set num=num+100 where id='a';
rollback;

事务提交了是改不了的。

create table goods (
  goods_id mediumint(8) unsigned primary key auto_increment,
  goods_name varchar(120) not null default '',
  cat_id smallint(5) unsigned not null default '0',
  brand_id smallint(5) unsigned not null default '0',
  goods_sn char(15) not null default '',
  goods_number smallint(5) unsigned not null default '0',
  shop_price decimal(10,2) unsigned not null default '0.00',
  market_price decimal(10,2) unsigned not null default '0.00',
  click_count int(10) unsigned not null default '0'
) engine=myisam default charset=utf8;

insert into `goods` values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),
(4,'诺基亚n85原装充电器',8,1,'ecs000004',17,58.00,69.60,0),
(3,'诺基亚原装5800耳机',8,1,'ecs000002',24,68.00,81.60,3),
(5,'索爱原装m2卡读卡器',11,7,'ecs000005',8,20.00,24.00,3),
(6,'胜创kingmax内存卡',11,0,'ecs000006',15,42.00,50.40,0),
(7,'诺基亚n85原装立体声耳机hs-82',8,1,'ecs000007',20,100.00,120.00,0),
(8,'飞利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10),
(9,'诺基亚e66',3,1,'ecs000009',4,2298.00,2757.60,20),
(10,'索爱c702c',3,7,'ecs000010',7,1328.00,1593.60,11),
(11,'索爱c702c',3,7,'ecs000011',1,1300.00,0.00,0),
(12,'摩托罗拉a810',3,2,'ecs000012',8,983.00,1179.60,13),
(13,'诺基亚5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),
(14,'诺基亚5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),
(15,'摩托罗拉a810',3,2,'ecs000015',3,788.00,945.60,8),
(16,'恒基伟业g101',2,11,'ecs000016',0,823.33,988.00,3),
(17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),
(18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),
(19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),
(20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),
(21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),
(22,'多普达touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),
(23,'诺基亚n96',5,1,'ecs000023',8,3700.00,4440.00,17),
(24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),
(25,'小灵通/固话50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),
(26,'小灵通/固话20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),
(27,'联通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),
(28,'联通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),
(29,'移动100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),
(30,'移动20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),
(31,'摩托罗拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),
(32,'诺基亚n85',3,1,'ecs000032',4,3010.00,3612.00,9);


 create table category (
 cat_id smallint unsigned auto_increment primary key,
 cat_name varchar(90) not null default '',
 parent_id smallint unsigned
 )engine myisam charset utf8;


INSERT INTO `category` VALUES 
(1,'手机类型',0),
(2,'CDMA手机',1),
(3,'GSM手机',1),
(4,'3G手机',1),
(5,'双模手机',1),
(6,'手机配件',0),
(7,'充电器',6),
(8,'耳机',6),
(9,'电池',6),
(11,'读卡器和内存卡',6),
(12,'充值卡',0),
(13,'小灵通/固话充值卡',12),
(14,'移动手机充值卡',12),
(15,'联通手机充值卡',12);


CREATE TABLE `result` (
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;



insert into result
values
('张三','数学',90),
('张三','语文',50),
('张三','地理',40),
('李四','语文',55),
('李四','政治',45),
('王五','政治',30);


create table a (
id char(1),
num int
)engine myisam charset utf8;

insert into a values ('a',5),('b',10),('c',15),('d',10);

create table b (
id char(1),
num int
)engine myisam charset utf8;

insert into b values ('b',5),('c',15),('d',20),('e',99);



 create table m(
     mid int,
     hid int,
     gid int,
     mres varchar(10),
     matime date
)engine myisam charset utf8;

create table t (
     tid int,
     tname varchar(20)
)engine myisam charset utf8;
 
 
 insert into m
     values
     (1,1,2,'2:0','2006-05-21'),
     (2,2,3,'1:2','2006-06-21'),
     (3,3,1,'2:5','2006-06-25'),
     (4,2,1,'3:2','2006-07-21');

 
 insert into t
     values
     (1,'国安'),
     (2,'申花'),
     (3,'布尔联队');



create table mian ( num int) engine myisam;
insert into mian values 
(3),
(12),
(15),
(25),
(23),
(29),
(34),
(37),
(32);


create table user (
uid int primary key auto_increment,
name varchar(20) not null default '',
age smallint unsigned not null default 0
) engine myisam charset utf8;



 create table boy (
    hid char(1),
     bname varchar(20)
 )engine myisam charset utf8;

 
 insert into boy (bname,hid)
     values
     ('屌丝','A'),
     ('杨过','B'),
     ('陈冠希','C');

 
 create table girl (
    hid char(1),
     gname varchar(20)
     )engine myisam charset utf8;

 
 insert into girl(gname,hid)
     values
     ('小龙女','B'),
     ('张柏芝','C'),
     ('死宅女','D');

1:mysql的函数肯定是要影响查询速度.
应该在建表时,通过合理的表结构减少函数的使用.
比如 email ,按@ 前后拆分.

2:如果确实要用函数,
比如 时间的格式化
在mysql里用date_format,在php里用date可以实现
优先放在业务逻辑层,即php层处理.

3:在查询时使用了函数,最大的一个坏处,
以 date_format(A)为例
则A列的索引将无法使用.

如果你针对某列作操作,而此次查询,又使用的此列的索引.
此时,速度将显著变慢.

例: 
sname, email 两列
email加了索引

Select name,email from table where right(email,6)='qq.com';
将会导致此次查询中, email的索引并不会产生效果.