数据库操作之——查询

PS：

mysql> select * from mysql.user \G;
*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y

ERROR:
No query specified

二 插入表格

　　两种操作的对比：

　　create操作，表格先前不存在

mysql> create table t1(x char(60),y char(16)) select host,user from mysql.user;
Query OK, 11 rows affected (0.57 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> create table t2(host char(60),user char(16)) select host,user from mysql.user;
Query OK, 11 rows affected (0.58 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| x     | char(60) | YES  |     | NULL    |       |
| y     | char(16) | YES  |     | NULL    |       |
| host  | char(60) | NO   |     |         |       |
| user  | char(16) | NO   |     |         |       |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> desc t2;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| host  | char(60) | YES  |     | NULL    |       |
| user  | char(16) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> create table t1(id int ,name char(10));
Query OK, 0 rows affected (0.30 sec)

mysql> create table t2(id int ,name char(10));
Query OK, 0 rows affected (0.29 sec)

mysql> insert t2 values
    -> (1,‘alex‘),
    -> (2,‘egon‘);
Query OK, 2 rows affected (0.29 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert t1 values
    -> (1,‘wupeiqi‘);
Query OK, 1 row affected (0.30 sec)

mysql> insert t1 select id,name from t2;
Query OK, 2 rows affected (0.32 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+---------+
| id   | name    |
+------+---------+
|    1 | wupeiqi |
|    1 | alex    |
|    2 | egon    |
+------+---------+
3 rows in set (0.00 sec)

　　select  as  用法

mysql> create table t1(主机地址 char(40),用户名 char(20)) select host as 主机地址,user as 用户名 from mysql.user;
Query OK, 11 rows affected (0.57 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+--------------+-----------+
| 主机地址     | 用户名    |
+--------------+-----------+
| %            | alex      |
| %            | egon      |
| %            | egon1     |
| %            | yuanhao   |
| %            | zuo       |
| %            | zuo1      |
| 127.0.0.1    | root      |
| ::1          | root      |
| localhost    |           |
| localhost    | root      |
| localhost    | zuo       |
+--------------+-----------+
11 rows in set (0.00 sec)

　　insert 操作  表格先前存在

mysql> create table t1(id int ,name char(10));
Query OK, 0 rows affected (0.30 sec)

mysql> create table t2(id int ,name char(10));
Query OK, 0 rows affected (0.29 sec)

mysql> insert t2 values
    -> (1,‘alex‘),
    -> (2,‘egon‘);
Query OK, 2 rows affected (0.29 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert t1 values
    -> (1,‘wupeiqi‘);
Query OK, 1 row affected (0.30 sec)

mysql> insert t1 select id,name from t2;
Query OK, 2 rows affected (0.32 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+---------+
| id   | name    |
+------+---------+
|    1 | wupeiqi |
|    1 | alex    |
|    2 | egon    |
+------+---------+
3 rows in set (0.00 sec)

数据库操作之——查询

标签：dac   update   select   log   drop   roo   改变   code   ror