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 '',

  brand_id smallint(5) unsigned not null default '',

  goods_sn char(15) not null default '',

  goods_number smallint(5) unsigned not null default '',

  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 ''

) 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的索引并不会产生效果.

巴特西

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

最新文章

热门文章