![复制代码]()
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');
![复制代码]()
