MYSQL复习小记（二）——摘录笔记

于 2018-02-08 22:01:04 发布
阅读量254 收藏
点赞数
分类专栏： MYSQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/y649014081/article/details/79280639
版权

ALTER字段：

用法：

1、删除，添加或修改表字段

ALTER TABLE testalter_tbl  DROP i; //testatlter_tb1:表名 i:字段名，属性名

只剩余一个字段则无法使用DROP来删除字段。

ALTER TABLE testalter_tbl ADD i INT; //添加字段与类型

i 字段会自动添加到数据表字段的末尾。

可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

手写例子：

alter table demotb drop demo_name;
alter table demotb add demo_n char(45) first;    //添加字段在第一个位置上
alter table demotb add demo_t char(40) after demo_n;   //添加这字段在demo_n字段后面

想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

2、修改字段类型及名称

修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句。

alter table demotb modify demo_name char(100);//将demo_name字段类型由char（45）改为char（100）
alter table demotb change demo_id demo_I bigint;//将demo_id改名为demo_I 类型变为bigint

还可以使用modify字段修改字段not null 以及默认值

alter table demotb modify demo_name char(100) not null default '无名'
                    修改     属性      类型     设置不空   设置默认值

modify字段修改字段位置：

alter table tableName modify name1 type1 first|after name2; //后面添加first或者after xx

alter字段与set和drop设置及删除默认值

alter table demotb alter demo_name set default '无名';//设置默认值
alter table demotb alter demo_name drop default;//删除默认值

修改数据表类型：

ALTER TABLE testalter_tbl ENGINE = MYISAM; //修改数据表类型
SHOW TABLE STATUS LIKE 'testalter_tbl'\G;  //查看数据表类型

修改表名：

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

MYSQL索引：


索引分单列索引和组合索引。

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。

普通索引/单指索引：

创建索引：

CREATE INDEX indexName ON tablename(demo_name(length));

添加索引：

ALTER table tableName ADD INDEX indexName(columnName)

上面的创建索引create与添加索引alter效用相同，都已经成功创建了索引，而

1、CREATE INDEX必须提供索引名，对于ALTER TABLE，将会自动创建，如果你不提供；
2、CREATE INDEX一个语句一次只能建立一个索引，ALTER TABLE可以在一个语句建立多个，如：
      ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
3、只有ALTER TABLE 才能创建主键，ADD INDEX 不能；

创建表时创建索引：

CREATE TABLE tablename(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);

删除索引：

DROP INDEX [indexName] ON tablename;

唯一索引：

创建索引：

create unique index indexname on tablename(username(length));

添加索引：

alter table tablename add unique indexname(username(length));

上面的创建索引create与添加索引alter效用相同，都已经成功创建了索引，而

1、CREATE INDEX必须提供索引名，对于ALTER TABLE，将会自动创建，如果你不提供；
2、CREATE INDEX一个语句一次只能建立一个索引，ALTER TABLE可以在一个语句建立多个，如：
      ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
3、只有ALTER TABLE 才能创建主键，ADD INDEX 不能；

创建表时指定索引：

CREATE TABLE tablename(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
UNIQUE [indexName] (username(length))  
 
);

添加索引有四种方式：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

使用alter修改和删除主键：

ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;//确保主键不为空
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);//指定i为主键
ALTER TABLE testalter_tbl DROP PRIMARY KEY;//删除主键时不需要指出主键

显示索引信息：

SHOW INDEX FROM tablename; \G


ps:索引建立完毕，索引什么时候使用

如果你关心某条SQL查询是否用了索引, 可以用EXPLAIN SELECT * FROM xxx WHERE yyy看一下查询计划, 它会告诉你用了什么索引, 这里你就会看到你的索引名了

还有一个场合你会用到索引名, 就是如果你希望强制使用某个索引来查询, 你可以用SELECT * FROM xxx FORCE INDEX(yyy) WHERE zzz, 相反如果希望强制忽略某个索引, 可以用SELECT * FROM xxx IGNORE INDEX(yyy) WHERE zzz


mysql临时表：

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist



mysql复制表：
方法一：
1、show create table 'tablename' 得到create语句， 
SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
       Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
  `runoob_id` int(11) NOT NULL auto_increment,
  `runoob_title` varchar(100) NOT NULL default '',
  `runoob_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`runoob_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB 
1 row in set (0.00 sec)
2、用得到的create语句修改表名，建立克隆新表，得到完整表结构，
CREATE TABLE `clone_tbl` ( -> `runoob_id` int(11) NOT NULL auto_increment, -> `runoob_title` varchar(100) NOT NULL default '', -> `runoob_author` varchar(40) NOT NULL default '', -> `submission_date` date default NULL, -> PRIMARY KEY (`runoob_id`), -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)-> ) ENGINE=InnoDB;
3、使用insert into ... select ... from ... 插入原表的数据 
 
   

     INSERT INTO clone_tbl (runoob_id,runoob_title,runoob_author,submission_date)SELECT runoob_id,runoob_title,runoob_author,submission_date 
   
 
   

     FROM runoob_tbl; 
   
 
   

     方法二： 
   
 
   
 
    

      1、先复制完整表结构： 
    
 
    
 
     
CREATE TABLE targetTable LIKE sourceTable;

 
     
2、insert into ... select ... from ... 插入原表数据： 
     
 
     
INSERT INTO targetTable SELECT * FROM sourceTable;
 
     
 
      
 
       
     
 
     
 
      MYSQL元数据： 
      
 
      
SELECT VERSION( )
服务器版本信息
SELECT DATABASE( )
当前数据库名 (或者返回空)
SELECT USER( )
当前用户名
SHOW STATUS
服务器状态
SHOW VARIABLES
服务器配置变量
 
      
 
      mysql序列id号： 
      
 
     
 
    
 
   
 
  
 
 
 

 

 

 

 
  
  
 使用 MySQL AUTO_INCREMENT 来定义列。 

 

 
 创建了数据表insect， insect中id无需指定值可实现自动增长。 
 
 

 

 

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+

 

 


  指定id值为null，最后输出自动从1开始自增； 

 

 
 
 
  

    设置不从1开始自增，设置从100开始自增： 
   
建表时设置： 
  
 
  
 
   
CREATE TABLE insect
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
name VARCHAR(30) NOT NULL, 
date DATE NOT NULL,
origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;
 
   

     建表之后设置： 
   
 
   
 
    
alter table tablename auto_increment = 100;
 
    
 
    
 
   
 
  
 
 
 
 
 
  处理重复数据： 
  
 
 
 

 

设置指定的字段为 PRIMARY KEY（主键）  或者 UNIQUE（唯一） 索引来保证数据的唯一性。
 

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name) //设置双主键来设置数据的唯一性，默认值也不能为空
);
 

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)//添加unique索引
);
 

 

 
 
 
 insert ignore into插入数据时，如果数据不存在则正常插入，若数据已经存在，则将insert ignore into后插入的数据忽略掉 

 

 
 
 
  replace into 插入数据时，如果数据不存在则正常插入，若数据已经存在，则用replace into后的数据替换已经存在的数据
 
  
实例： 
 
 
 
 
  
create table testtb(
  id int not null primary key,
  name varchar(50),
  age int
);
insert into testtb(id,name,age)values(1,"bb",13);
select * from testtb;
insert ignore into testtb(id,name,age)values(1,"aa",13);
select * from testtb;//仍是1，“bb”,13，因为id是主键，出现主键重复但使用了ignore则错误被忽略
replace into testtb(id,name,age)values(1,"aa",12);
select * from testtb; //数据变为1,"aa",12
 
  
 
   
 
   统计重复数据： 
  
 
  
 
    
   
SELECT COUNT(*) as repetitions, last_name, first_name
FROM person_tbl
GROUP BY last_name, first_name
HAVING repetitions > 1;
 
   

     统计last_name, first_name的记录数as为repetitions 
    
 
    
 
    读取不重复数据： 
   
 
   
 
     
    
SELECT DISTINCT last_name, first_name //SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。
FROM person_tbl;
 
    
SELECT last_name, first_name
FROM person_tbl
GROUP BY (last_name, first_name); //用 GROUP BY 来读取数据表中不重复的数据。
 
    
 
    
删除重复数据： 
    
 
   
 
  
 
 
 

 

 

CREATE TABLE tmp SELECT last_name, first_name, sex
FROM person_tbl;
GROUP BY (last_name, first_name, sex);  //先用group by建一个不重复的复制表
DROP TABLE person_tbl;    //然后删除原表
ALTER TABLE tmp RENAME TO person_tbl; //将复制表改名为原表
也可以用添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录 

 

mysql导出数据：
 

 

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
 

select......into outfile '文件路径' 

链接指向runoob.com
 

mysql导入数据：
 

 

 
 

 
 
 

 

 

学习摘录自：http://www.runoob.com/mysql

                

        

    

  



    


            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值