mysql sql 两张表之间完全复制的sql语句

sql as别名使用方法,as别名使用方法 mysql教程 CREATE TABLE Topic(,样的表back,表没有主键及索引的,IN 操作符允许我们在 WHERE 子句中规定多个值,    BookID SMALLINT NOT NULL PRIMARY KEY,SMALLINT  2Byte    有符号值,-128-127

sql as别名使用办法

在MySql4.0.12中,用” create table bak_name as select  *  from
original_name”语句后

sql IN 与not in的用法
IN 操作符允许大家在 WHERE 子句中明确五个值。

默许端口号3306

mysql教程> CREATE TABLE Topic(
    ->    TopicID     SMALLINT NOT NULL AUTO_INCREMENT

,生成的bak
表未有主键及索引的,用到的时候有个别要注意.能够先建二个和要备份的表结构一

SQL IN 语法
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,…)

CREATE DATABASE

PRIMARY KEY,
    ->    Name        VARCHAR(50) NOT NULL,
    ->    InStock     SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder     SMALLINT UNSIGNED NOT NULL,
    ->    Reserved    SMALLINT UNSIGNED NOT NULL,
    ->    Department  ENUM(‘Classical’, ‘Popular’) NOT

样的表back_name,用以下方法倒入数据

实例

ALTER DATABASE

NULL,
    ->    Category    VARCHAR(20) NOT NULL,
    ->    RowUpdate   TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

insert into bak_table select * from original_name 这几个格局

mysql教程> CREATE TABLE Topic(
    ->    TopicID     SMALLINT NOT NULL AUTO_INCREMENT PRIMARY
KEY,
    ->    Name        VARCHAR(50) NOT NULL,
    ->    InStock     SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder     SMALLINT UNSIGNED NOT NULL,
    ->    Reserved    SMALLINT UNSIGNED NOT NULL,
    ->    Department  ENUM(‘Classical’, ‘Popular’) NOT NULL,
    ->    Category    VARCHAR(20) NOT NULL,
    ->    RowUpdate   TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

DROP DATABASE

mysql>
mysql>
mysql> INSERT INTO Topic (Name,          InStock, OnOrder,

在mysql教程中得以把曾经存在的表直接通过命令复制为另三个表

mysql>
mysql>
mysql> INSERT INTO Topic (Name,          InStock, OnOrder, Reserved,
Department,   Category) VALUES
    ->                   (‘Java’,          10,      5,      
3,        ‘Popular’,    ‘Rock’),
    ->                   (‘JavaScript’,    10,      5,      
3,        ‘Classical’,  ‘Opera’),
    ->                   (‘C Sharp’,       17,      4,      
1,        ‘Popular’,    ‘Jazz’),
    ->                   (‘C’,             9,       4,      
2,        ‘Classical’,  ‘Dance’),
    ->                   (‘C++’,           24,      2,      
5,        ‘Classical’,  ‘General’),
    ->                   (‘Perl’,          16,      6,      
8,        ‘Classical’,  ‘Vocal’),
    ->                   (‘Python’,        2,       25,     
6,        ‘Popular’,    ‘Blues’),
    ->                   (‘Php’,           32,      3,      
10,       ‘Popular’,    ‘Jazz’),
    ->                   (‘ASP.net’,       12,      15,     
13,       ‘Popular’,    ‘Country’),
    ->                   (‘VB.net’,        5,       20,     
10,       ‘Popular’,    ‘New Age’),
    ->                   (‘VC.net’,        24,      11,     
14,       ‘Popular’,    ‘New Age’),
    ->                   (‘UML’,           42,      17,     
17,       ‘Classical’,  ‘General’),
    ->                   (‘www.bKjia.c0m’,25,      44,      28,      
‘Classical’,  ‘Dance’),
    ->                   (‘Oracle’,        32,      15,     
12,       ‘Classical’,  ‘General’),
    ->                   (‘Pl/SQL’,        20,      10,     
5,        ‘Classical’,  ‘Opera’),
    ->                   (‘Sql Server’,    23,      12,     
8,        ‘Classical’,  ‘General’);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

TINYINT  1 Byte  有号子值 : -128-127

Reserved, Department,   Category) VALUES
    ->                   (‘Java’,          10,      5,    

方法1:create table mmm select * from bbb;
注意:那条命令供给mmm那几个表在数据库教程中

mysql>
mysql> select * from Topic;
+———+—————-+———+———+———-+————+———-+———————+
| TopicID | Name           | InStock | OnOrder | Reserved | Department |
Category | RowUpdate           |
+———+—————-+———+———+———-+————+———-+———————+
|       1 | Java           |      10 |       5 |        3 | Popular    |
Rock     | 2007-07-23 19:09:47 |
|       2 | JavaScript     |      10 |       5 |        3 | Classical  |
Opera    | 2007-07-23 19:09:47 |
|       3 | C Sharp        |      17 |       4 |        1 | Popular    |
Jazz     | 2007-07-23 19:09:47 |
|       4 | C              |       9 |       4 |        2 | Classical  |
Dance    | 2007-07-23 19:09:47 |
|       5 | C++            |      24 |       2 |        5 | Classical  |
General  | 2007-07-23 19:09:47 |
|       6 | Perl           |      16 |       6 |        8 | Classical  |
Vocal    | 2007-07-23 19:09:47 |
|       7 | Python         |       2 |      25 |        6 | Popular    |
Blues    | 2007-07-23 19:09:47 |
|       8 | Php            |      32 |       3 |       10 | Popular    |
Jazz     | 2007-07-23 19:09:47 |
|       9 | ASP.net        |      12 |      15 |       13 | Popular    |
Country  | 2007-07-23 19:09:47 |
|      10 | VB.net         |       5 |      20 |       10 | Popular    |
New Age  | 2007-07-23 19:09:47 |
|      11 | VC.net         |      24 |      11 |       14 | Popular    |
New Age  | 2007-07-23 19:09:47 |
|      12 | UML            |      42 |      17 |       17 | Classical  |
General  | 2007-07-23 19:09:47 |
|      13 | www.bKjia.c0m |      25 |      44 |       28 | Classical  |
Dance    | 2007-07-23 19:09:47 |
|      14 | Oracle         |      32 |      15 |       12 | Classical  |
General  | 2007-07-23 19:09:47 |
|      15 | Pl/SQL         |      20 |      10 |        5 | Classical  |
Opera    | 2007-07-23 19:09:47 |
|      16 | Sql Server     |      23 |      12 |        8 | Classical  |
General  | 2007-07-23 19:09:47 |
+———+—————-+———+———+———-+————+———-+———————+
16 rows in set (0.00 sec)

无符号值:0-255

  3,        ‘Popular’,    ‘Rock’),
    ->                   (‘JavaScript’,    10,      5,    

不存在
那条命令能够成立新表mmm并且bbb中的表结构以及数据和mmm完全平等,也得以导出部分字段

mysql>
mysql> SELECT Name, Category, InStock
    -> FROM Topic
    -> WHERE Category IN (‘Blues’, ‘Jazz’)
    -> ORDER BY Name;
+———+———-+———+
| Name    | Category | InStock |
+———+———-+———+
| C Sharp | Jazz     |      17 |
| Php     | Jazz     |      32 |
| Python  | Blues    |       2 |
+———+———-+———+
3 rows in set (0.00 sec)

SMALLINT  2Byte    有暗记值:-32768  32767

  3,        ‘Classical’,  ‘Opera’),
    ->                   (‘C Sharp’,       17,      4,    

create table mmm select 字段1,字段2 from bbb;

使用in多表查询
mysql> CREATE TABLE Books(
    ->    BookID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookTitle VARCHAR(60) NOT NULL,
    ->    Copyright YEAR NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)

无符号值:0-65535

  1,        ‘Popular’,    ‘Jazz’),
    ->                   (‘C’,             9,       4,    

方法2:insert into mmm select * bbb;
那条语句和上一条语句完毕均等的功能,只是供给

mysql>
mysql>
mysql> INSERT INTO Books VALUES (12786, ‘Java’,           1934),
    ->                          (13331, ‘MySQL’,          1919),
    ->                          (14356, ‘PHP’,            1966),
    ->                          (15729, ‘PERL’,           1932),
    ->                          (16284, ‘Oracle’,         1996),
    ->                          (17695, ‘Pl/SQL’,         1980),
    ->                          (19264, ‘JavaScript’,     1992),
    ->                          (19354, ‘www.bKjia.c0m’, 1993);
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

MEDIUMINT 3Byte    有标记位 -2^23 2^23-1

  2,        ‘Classical’,  ‘Dance’),
    ->                   (‘C++’,           24,      2,    

mmm表必须在数据库中设有

mysql>
mysql>
mysql> CREATE TABLE Authors(
    ->    AuthID SMALLINT NOT NULL PRIMARY KEY,
    ->    AuthFN VARCHAR(20),
    ->    AuthMN VARCHAR(20),
    ->    AuthLN VARCHAR(20)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)

无符号位 0- 2^24-1

  5,        ‘Classical’,  ‘General’),
    ->                   (‘Perl’,          16,      6,    

 

mysql>
mysql>
mysql> INSERT INTO Authors VALUES (1006, ‘H’, ‘S.’, ‘T’),
    ->                            (1007, ‘J’, ‘C’,  ‘O’),
    ->                            (1008, ‘B’, NULL, ‘E’),
    ->                            (1009, ‘R’, ‘M’,  ‘R’),
    ->                            (1010, ‘J’, ‘K’,  ‘T’),
    ->                            (1011, ‘J’, ‘G.’, ‘N’),
    ->                            (1012, ‘A’, NULL, ‘P’),
    ->                            (1013, ‘A’, NULL, ‘W’),
    ->                            (1014, ‘N’, NULL, ‘A’);
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0

INT      4Byte            -2^31 2^31-1

  8,        ‘Classical’,  ‘Vocal’),
    ->                   (‘Python’,        2,       25,   

mysql>
mysql> CREATE TABLE Topic(
    ->    TopicID     SMALLINT NOT NULL AUTO_INCREMENT PRIMARY
KEY,
    ->    Name        VARCHAR(50) NOT NULL,
    ->    InStock     SMALLINT UNSIGNED NOT NULL,
    ->    OnOrder     SMALLINT UNSIGNED NOT NULL,
    ->    Reserved    SMALLINT UNSIGNED NOT NULL,
    ->    Department  ENUM(‘Classical’, ‘Popular’) NOT NULL,
    ->    Category    VARCHAR(20) NOT NULL,
    ->    RowUpdate   TIMESTAMP NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql>
mysql> INSERT INTO Topic (Name,          InStock, OnOrder,
Reserved, Department, 

 Category) VALUES
    ->                   (‘Java’,          10,      5,      
3,        ‘Popular’,

   ‘Rock’),
    ->                   (‘JavaScript’,    10,      5,      
3,       

‘Classical’,  ‘Opera’),
    ->                   (‘C Sharp’,       17,      4,      
1,        ‘Popular’,

   ‘Jazz’),
    ->                   (‘C’,             9,       4,      
2,       

‘Classical’,  ‘Dance’),
    ->                   (‘C++’,           24,      2,      
5,       

‘Classical’,  ‘General’),
    ->                   (‘Perl’,          16,      6,      
8,       

‘Classical’,  ‘Vocal’),
    ->                   (‘Python’,        2,       25,     
6,        ‘Popular’,

   ‘Blues’),
    ->                   (‘Php’,           32,      3,      
10,       ‘Popular’,

   ‘Jazz’),
    ->                   (‘ASP.net’,       12,      15,     
13,       ‘Popular’,

   ‘Country’),
    ->                   (‘VB.net’,        5,       20,     
10,       ‘Popular’,

   ‘New Age’),
    ->                   (‘VC.net’,        24,      11,     
14,       ‘Popular’,

   ‘New Age’),
    ->                   (‘UML’,           42,      17,     
17,      

‘Classical’,  ‘General’),
    ->                   (‘www.java2s.com’,25,      44,     
28,      

‘Classical’,  ‘Dance’),
    ->                   (‘Oracle’,        32,      15,     
12,      

‘Classical’,  ‘General’),
    ->                   (‘Pl/SQL’,        20,      10,     
5,       

‘Classical’,  ‘Opera’),
    ->                   (‘Sql Server’,    23,      12,     
8,       

‘Classical’,  ‘General’);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from Topic;
+———+—————-+———+———+———-+————+——–

–+———————+
| TopicID | Name           | InStock | OnOrder | Reserved | Department
| Category

| RowUpdate           |
+———+—————-+———+———+———-+————+——–

–+———————+
|       1 | Java           |      10 |       5 |        3 | Popular   
| Rock    

| 2007-07-23 19:09:52 |
|       2 | JavaScript     |      10 |       5 |        3 | Classical 
| Opera   

| 2007-07-23 19:09:52 |
|       3 | C Sharp        |      17 |       4 |        1 | Popular   
| Jazz    

| 2007-07-23 19:09:52 |
|       4 | C              |       9 |       4 |        2 | Classical 
| Dance   

| 2007-07-23 19:09:52 |
|       5 | C++            |      24 |       2 |        5 | Classical 
| General 

| 2007-07-23 19:09:52 |
|       6 | Perl           |      16 |       6 |        8 | Classical 
| Vocal   

| 2007-07-23 19:09:52 |
|       7 | Python         |       2 |      25 |        6 | Popular   
| Blues   

| 2007-07-23 19:09:52 |
|       8 | Php            |      32 |       3 |       10 | Popular   
| Jazz    

| 2007-07-23 19:09:52 |
|       9 | ASP.net        |      12 |      15 |       13 | Popular   
| Country 

| 2007-07-23 19:09:52 |
|      10 | VB.net         |       5 |      20 |       10 | Popular   
| New Age 

| 2007-07-23 19:09:52 |
|      11 | VC.net         |      24 |      11 |       14 | Popular   
| New Age 

| 2007-07-23 19:09:52 |
|      12 | UML            |      42 |      17 |       17 | Classical 
| General 

| 2007-07-23 19:09:52 |
|      13 | www.java2s.com |      25 |      44 |       28 | Classical 
| Dance   

| 2007-07-23 19:09:52 |
|      14 | Oracle         |      32 |      15 |       12 | Classical 
| General 

| 2007-07-23 19:09:52 |
|      15 | Pl/SQL         |      20 |      10 |        5 | Classical 
| Opera   

| 2007-07-23 19:09:52 |
|      16 | Sql Server     |      23 |      12 |        8 | Classical 
| General 

| 2007-07-23 19:09:52 |
+———+—————-+———+———+———-+————+——–

–+———————+
16 rows in set (0.00 sec)

mysql>
mysql> CREATE TABLE Topic2
    -> (
    ->    TopicID SMALLINT NOT NULL PRIMARY KEY,
    ->    Name VARCHAR(50) NOT NULL,
    ->    InStock SMALLINT UNSIGNED NOT NULL
    -> )
    -> SELECT TopicID, Name, InStock
    -> FROM Topic
    -> WHERE Category=’Blues’ OR Category=’Jazz’;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from Topic2;
+———+———+———+
| TopicID | Name    | InStock |
+———+———+———+
|       3 | C Sharp |      17 |
|       7 | Python  |       2 |
|       8 | Php     |      32 |
+———+———+———+
3 rows in set (0.00 sec)

mysql>
mysql>
mysql> CREATE TABLE AuthorBook(
    ->    AuthID SMALLINT NOT NULL,
    ->    BookID SMALLINT NOT NULL,
    ->    PRIMARY KEY (AuthID, BookID),
    ->    FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
    ->    FOREIGN KEY (BookID) REFERENCES Books (BookID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.09 sec)

0-2^32-1

  6,        ‘Popular’,    ‘Blues’),
    ->                   (‘Php’,           32,      3,    

create table bak_name as
select * from original_name语句后 ,生成的bak
表未有主键及索引的,用到的时候有些要注意.能够先建贰个和要备…

mysql>
mysql>
mysql> INSERT INTO AuthorBook VALUES (1006, 14356),
    ->                               (1008, 15729),
    ->                               (1009, 12786),
    ->                               (1010, 17695),
    ->                               (1011, 15729),
    ->                               (1012, 19264),
    ->                               (1012, 19354),
    ->                               (1014, 16284);
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

BIGINT    8                0-2^64-1

  10,       ‘Popular’,    ‘Jazz’),
    ->                   (‘ASP.net’,       12,      15,   

mysql>
mysql>
mysql> select * from Authors;
+——–+——–+——–+——–+
| AuthID | AuthFN | AuthMN | AuthLN |
+——–+——–+——–+——–+
|   1006 | H      | S.     | T      |
|   1007 | J      | C      | O      |
|   1008 | B      | NULL   | E      |
|   1009 | R      | M      | R      |
|   1010 | J      | K      | T      |
|   1011 | J      | G.     | N      |
|   1012 | A      | NULL   | P      |
|   1013 | A      | NULL   | W      |
|   1014 | N      | NULL   | A      |
+——–+——–+——–+——–+
9 rows in set (0.00 sec)

FLOAT[(M,D)]  M总位数,D小数点后的位数

  13,       ‘Popular’,    ‘Country’),
    ->                   (‘VB.net’,        5,       20,   

mysql> select * from Books;
+——–+—————-+———–+
| BookID | BookTitle      | Copyright |
+——–+—————-+———–+
|  12786 | Java           |      1934 |
|  13331 | MySQL          |      1919 |
|  14356 | PHP            |      1966 |
|  15729 | PERL           |      1932 |
|  16284 | Oracle         |      1996 |
|  17695 | Pl/SQL         |      1980 |
|  19264 | JavaScript     |      1992 |
|  19354 | www.bKjia.c0m |      1993 |
+——–+—————-+———–+
8 rows in set (0.00 sec)

单精度浮点,约7位小数

  10,       ‘Popular’,    ‘New Age’),
    ->                   (‘VC.net’,        24,      11,   

mysql> select * from AuthorBook;
+——–+——–+
| AuthID | BookID |
+——–+——–+
|   1009 |  12786 |
|   1006 |  14356 |
|   1008 |  15729 |
|   1011 |  15729 |
|   1014 |  16284 |
|   1010 |  17695 |
|   1012 |  19264 |
|   1012 |  19354 |
+——–+——–+
8 rows in set (0.01 sec)

DOUBLE[(M,D)] 双精度

  14,       ‘Popular’,    ‘New Age’),
    ->                   (‘UML’,           42,      17,   

mysql>
mysql> SELECT BookTitle, Copyright
    -> FROM Books
    -> WHERE Copyright NOT IN
    ->    (
    ->       SELECT b.copyright
    ->       FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
    ->          JOIN Authors AS a USING (AuthID)
    ->       WHERE AuthLN=’MySQL’
    ->    )
    -> ORDER BY BookTitle;
+—————-+———–+
| BookTitle      | Copyright |
+—————-+———–+
| Java           |      1934 |
| JavaScript     |      1992 |
| MySQL          |      1919 |
| Oracle         |      1996 |
| PERL           |      1932 |
| PHP            |      1966 |
| Pl/SQL         |      1980 |
| www.bKjia.c0m |      1993 |
+—————-+———–+

YEAR          2位或4位

  17,       ‘Classical’,  ‘General’),
    ->                   (‘www.java2s.com’,25,      44,   

IN 与not in的用法 IN 操作符允许大家在 WHERE
子句中规定多少个值。 SQL IN 语法 SELECT column_name(s) FROM table_name
WHERE column_name IN (value1,value2,…)…

TIME

  28,       ‘Classical’,  ‘Dance’),
    ->                   (‘Oracle’,        32,      15,   

DATE

  12,       ‘Classical’,  ‘General’),
    ->                   (‘Pl/SQL’,        20,      10,   

DATETIME

  5,        ‘Classical’,  ‘Opera’),
    ->                   (‘Sql Server’,    23,      12,   

TIMESTAMP    1970-2030

  8,        ‘Classical’,  ‘General’);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

CHARM(M)定长

mysql>
mysql> select * from Topic;
+———+—————-+———+———+———

VARCHAR(M)变长

-+————+———-+———————+
| TopicID | Name           | InStock | OnOrder | Reserved

ENUM(‘VALUE1′,’VALUE2’)枚举

| Department | Category | RowUpdate           |
+———+—————-+———+———+———

SET(‘VALUE1′,’VALUE2’)组合

-+————+———-+———————+
|       1 | Java           |      10 |       5 |        3

CREATE TABLE T1 ( username VARCHAR(20), age  TINYINT UNSIGNED, salary
FLOAT(8,2) UNSIGNED);

| Popular    | Rock     | 2007-07-23 19:09:48 |
|       2 | JavaScript     |      10 |       5 |        3

mysql> SELECT DATABASE

| Classical  | Opera    | 2007-07-23 19:09:48 |
|       3 | C Sharp        |      17 |       4 |        1

-> ();

| Popular    | Jazz     | 2007-07-23 19:09:48 |
|       4 | C              |       9 |       4 |        2

+————-+

| Classical  | Dance    | 2007-07-23 19:09:48 |
|       5 | C++            |      24 |       2 |        5

| DATABASE

| Classical  | General  | 2007-07-23 19:09:48 |
|       6 | Perl           |      16 |       6 |        8

() |

| Classical  | Vocal    | 2007-07-23 19:09:48 |
|       7 | Python         |       2 |      25 |        6

+————-+

| Popular    | Blues    | 2007-07-23 19:09:48 |
|       8 | Php            |      32 |       3 |       10

| test        |

| Popular    | Jazz     | 2007-07-23 19:09:48 |
|       9 | ASP.net        |      12 |      15 |       13

+————-+

| Popular    | Country  | 2007-07-23 19:09:48 |
|      10 | VB.net         |       5 |      20 |       10

1 row in set (0.00 sec)

| Popular    | New Age  | 2007-07-23 19:09:48 |
|      11 | VC.net         |      24 |      11 |       14

mysql>

| Popular    | New Age  | 2007-07-23 19:09:48 |
|      12 | UML            |      42 |      17 |       17

mysql> SHOW COLUMNS FROM T1

| Classical  | General  | 2007-07-23 19:09:48 |
|      13 | www.java2s.com |      25 |      44 |       28

-> ;