MySQL基础四：表的删除、插入、更新、查找

于 2016-04-12 17:50:40 发布
阅读量588 收藏 1
点赞数
分类专栏： MySQL基础
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shawncheer/article/details/51134379
版权

插入记录：

mysql> use world;
Database changed
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password VARCHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
    -> sex BOOLEAN
    -> );
Query OK, 0 rows affected (0.20 sec)

mysql> #为自动编号的字段赋值；
mysql> INSERT users VALUES(NULL,'Tom','123',25,1);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT users VALUES(NULL,'John','456',25,1);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT users VALUES(DEFAULT,'John','456',25,1);
Query OK, 1 row affected (0.06 sec)

mysql> #可以输入表达式；
mysql> INSERT users VALUES(DEFAULT,'John','4852',3*7-5,1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Tom      | 123      |  25 |    1 |
|  2 | John     | 456      |  25 |    1 |
|  3 | John     | 456      |  25 |    1 |
|  4 | John     | 4852     |  16 |    1 |
+----+----------+----------+-----+------+
4 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM users;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | tinyint(1)           | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> INSERT users VALUES(DEFAULT,'John','4852',DEFAULT,1);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Tom      | 123      |  25 |    1 |
|  2 | John     | 456      |  25 |    1 |
|  3 | John     | 456      |  25 |    1 |
|  4 | John     | 4852     |  16 |    1 |
|  5 | John     | 4852     |  10 |    1 |
+----+----------+----------+-----+------+
5 rows in set (0.00 sec)

mysql> #输入多个记录
mysql> INSERT users VALUES(DEFAULT,'John','4852',DEFAULT,1),(NULL,'Rose',md5('123'),DEFAULT,0);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> #md5是求哈希值；
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  25 |    1 |
|  2 | John     | 456                              |  25 |    1 |
|  3 | John     | 456                              |  25 |    1 |
|  4 | John     | 4852                             |  16 |    1 |
|  5 | John     | 4852                             |  10 |    1 |
|  6 | John     | 4852                             |  10 |    1 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |  10 |    0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
mysql> INSERT users SET username='Ben',password='456';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  25 |    1 |
|  2 | John     | 456                              |  25 |    1 |
|  3 | John     | 456                              |  25 |    1 |
|  4 | John     | 4852                             |  16 |    1 |
|  5 | John     | 4852                             |  10 |    1 |
|  6 | John     | 4852                             |  10 |    1 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |  10 |    0 |
|  8 | Ben      | 456                              |  10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)


更新记录：

mysql> UPDATE users SET age=age+5;
Query OK, 8 rows affected (0.06 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  30 |    1 |
|  2 | John     | 456                              |  30 |    1 |
|  3 | John     | 456                              |  30 |    1 |
|  4 | John     | 4852                             |  21 |    1 |
|  5 | John     | 4852                             |  15 |    1 |
|  6 | John     | 4852                             |  15 |    1 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |  15 |    0 |
|  8 | Ben      | 456                              |  15 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> #更新多列
mysql> UPDATE users SET age=age-id,sex=0;         Query OK, 8 rows affected (0.05 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  29 |    0 |
|  2 | John     | 456                              |  28 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  4 | John     | 4852                             |  17 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  6 | John     | 4852                             |   9 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  8 | Ben      | 456                              |   7 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> #在更新过程中加条件；
mysql> UPDATE users SET age=age+10 WHERE id%2=0;  Query OK, 4 rows affected (0.05 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  29 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  6 | John     | 4852                             |  19 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  8 | Ben      | 456                              |  17 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)


删除记录：

mysql> DELETE FROM users WHERE id=6;
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  29 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  8 | Ben      | 456                              |  17 |    0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

mysql> INSERT users VALUES(NULL,'111','222',33,NULL);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  29 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  8 | Ben      | 456                              |  17 |    0 |
|  9 | 111      | 222                              |  33 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)


更新、赋予别名：

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.10-log |
+------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-04-12 21:43:06 |
+---------------------+
1 row in set (0.04 sec)

mysql> SELECT 3+5;
+-----+
| 3+5 |
+-----+
|   8 |
+-----+
1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM users;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | tinyint(1)           | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> SELECT id,username FROM users;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
|  2 | John     |
|  3 | John     |
|  4 | John     |
|  5 | John     |
|  7 | Rose     |
|  8 | Ben      |
|  9 | 111      |
+----+----------+
8 rows in set (0.00 sec)

mysql> SELECT username,id FROM users;
+----------+----+
| username | id |
+----------+----+
| Tom      |  1 |
| John     |  2 |
| John     |  3 |
| John     |  4 |
| John     |  5 |
| Rose     |  7 |
| Ben      |  8 |
| 111      |  9 |
+----------+----+
8 rows in set (0.00 sec)

mysql> SELECT users.id,users.username FROM users;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
|  2 | John     |
|  3 | John     |
|  4 | John     |
|  5 | John     |
|  7 | Rose     |
|  8 | Ben      |
|  9 | 111      |
+----+----------+
8 rows in set (0.00 sec)

mysql> #赋予别名
mysql> SELECT id AS userId,username AS uname from users;
+--------+-------+
| userId | uname |
+--------+-------+
|      1 | Tom   |
|      2 | John  |
|      3 | John  |
|      4 | John  |
|      5 | John  |
|      7 | Rose  |
|      8 | Ben   |
|      9 | 111   |
+--------+-------+
8 rows in set (0.00 sec)


分组：

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  29 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  8 | Ben      | 456                              |  17 |    0 |
|  9 | 111      | 222                              |  33 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> SELECT sex FROM users GROUP BY sex;
+------+
| sex  |
+------+
| NULL |
|    0 |
+------+
2 rows in set (0.00 sec)

mysql> SELECT sex FROM users GROUP BY 1;
+------+
| sex  |
+------+
| NULL |
|    0 |
+------+
2 rows in set (0.00 sec)

mysql> #分组条件：
mysql> SELECT sex,age FROM users GROUP BY age>35;
+------+-----+
| sex  | age |
+------+-----+
|    0 |  29 |
|    0 |  38 |
+------+-----+
2 rows in set (0.00 sec)

mysql> #聚合函数是指返回值只有一个值的函数；
mysql> SELECT sex FROM users GROUP BY 1 HAVING count(id)>=2;
+------+
| sex  |
+------+
|    0 |
+------+
1 row in set (0.00 sec)
<span id="_xhe_cursor"></span><p>
</p>
<pre class="sql" name="code">mysql> #对分组结果进行排序；
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  29 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  8 | Ben      | 456                              |  17 |    0 |
|  9 | 111      | 222                              |  33 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> #降序排序；
mysql> SELECT * FROM users ORDER BY id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  9 | 111      | 222                              |  33 | NULL |
|  8 | Ben      | 456                              |  17 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  1 | Tom      | 123                              |  29 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> #用两个字段进行排序；
mysql> SELECT * FROM users ORDER BY age,id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  8 | Ben      | 456                              |  17 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  1 | Tom      | 123                              |  29 |    0 |
|  9 | 111      | 222                              |  33 | NULL |
|  2 | John     | 456                              |  38 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

限制记录返回的数量：

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  29 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  8 | Ben      | 456                              |  17 |    0 |
|  9 | 111      | 222                              |  33 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM users LIMIT 2;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Tom      | 123      |  29 |    0 |
|  2 | John     | 456      |  38 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users LIMIT 3,2;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  4 | John     | 4852     |  27 |    0 |
|  5 | John     | 4852     |  10 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql> #记住SQL是从零开始排序的；
mysql> SELECT * FROM users LIMIT 2,2;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  3 | John     | 456      |  27 |    0 |
|  4 | John     | 4852     |  27 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users ORDER BY id DESC;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  9 | 111      | 222                              |  33 | NULL |
|  8 | Ben      | 456                              |  17 |    0 |
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
|  4 | John     | 4852                             |  27 |    0 |
|  3 | John     | 456                              |  27 |    0 |
|  2 | John     | 456                              |  38 |    0 |
|  1 | Tom      | 123                              |  29 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM users ORDER BY id DESC LIMIT 2,2;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  7 | Rose     | 202cb962ac59075b964b07152d234b70 |   8 |    0 |
|  5 | John     | 4852                             |  10 |    0 |
+----+----------+----------------------------------+-----+------+
2 rows in set (0.00 sec)

mysql> #可以把选择的部分放到另一张表里面；
mysql> CREATE TABLE test(
    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> SELECT * FROM test;
Empty set (0.00 sec)

mysql> INSERT test(username) SELECT username FROM users WHERE age>=30;
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test;
+----+----------+
| id | username |
+----+----------+
|  1 | John     |
|  2 | 111      |
+----+----------+
2 rows in set (0.00 sec)


 


 
 

 


 
 


 
 


 
 
 
 
 

 


   


                

        

        

    

  


    

      

        

            

              
                
                  shawncheer
                
              
            

            

                关注
              
            

        

        

          
      

      

            

                专栏目录
          









              

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

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

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

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

              

            

          

      

      余额充值