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 resultvalues('张三','数学',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');