SQL/MySQL/Table Index/Copy Table
Содержание
Copying a Table
/*create a copy of a table so that you
can alter it or experiment with it without endangering the original
data
*/
CREATE TABLE new_table_name
SELECT * FROM old_table_name;
/* Differences: their names,
if the old table had an index (or key), it
does not transfer to the new table.
only two columns chosen
*/
Copy Table Demo
/*
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name | varchar(50) | | PRI | | |
| PhoneNo | varchar(15) | YES | | Unknown Phone | |
| Age | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.01 sec)
mysql> Select * from Employee;
+----------+---------------+------+
| Name | PhoneNo | Age |
+----------+---------------+------+
| John Doe | Unknown Phone | 31 |
+----------+---------------+------+
1 row in set (0.00 sec)
mysql> /* Now copy the table */
mysql> CREATE TABLE MyEmployee SELECT * FROM Employee ;
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from MyEmployee;
+----------+---------------+------+
| Name | PhoneNo | Age |
+----------+---------------+------+
| John Doe | Unknown Phone | 31 |
+----------+---------------+------+
1 row in set (0.01 sec)
*/
Drop TABLE Employee;
Drop TABLE MyEmployee;
CREATE TABLE Employee (
Name VARCHAR(50) PRIMARY KEY NOT NULL,
Phone VARCHAR(15) DEFAULT "Unknown Phone",
Age INT CHECK (Age BETWEEN 20 and 30));
Describe Employee;
INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26);
INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31);
INSERT INTO Employee (Name, Phone) VALUES ("Joe Wang", NULL);
Select * from Employee;
/* Now copy the table */
CREATE TABLE MyEmployee SELECT * FROM Employee ;
select * from MyEmployee;
Copy Table with Condition
/*
mysql> Select * from Employee;
+----------+---------------+------+
| Name | PhoneNo | Age |
+----------+---------------+------+
| John Doe | Unknown Phone | 31 |
+----------+---------------+------+
1 row in set (0.00 sec)
mysql> /* Now copy the table */
mysql> CREATE TABLE MyEmployee SELECT * FROM Employee
-> where Age > 30;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> Describe MyEmployee;
+---------+-------------+------+-----+---------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name | varchar(50) | | | | |
| PhoneNo | varchar(15) | YES | | Unknown Phone | |
| Age | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.00 sec)
mysql> select * from MyEmployee;
+----------+---------------+------+
| Name | PhoneNo | Age |
+----------+---------------+------+
| John Doe | Unknown Phone | 31 |
+----------+---------------+------+
1 row in set (0.00 sec)
*/
Drop TABLE Employee;
Drop TABLE MyEmployee;
CREATE TABLE Employee (
Name VARCHAR(50) PRIMARY KEY NOT NULL,
PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
Age INT CHECK (Age BETWEEN 20 and 30));
Describe Employee;
INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26);
INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31);
Select * from Employee;
/* Now copy the table */
CREATE TABLE MyEmployee SELECT * FROM Employee
where Age > 30;
Describe MyEmployee;
select * from MyEmployee;
Copy table with conditions
Drop table CDs;
Drop table CDs2;
CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY,
CDName VARCHAR(50) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL,
Category VARCHAR(20)
);
INSERT INTO CDs
VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"),
(103, "Class", 9, "Classical"),
(104, "Violin", 24, NULL),
(105, "Blues", 2, "Blues"),
(106, "Tires", 12, "Country"),
(107, "Essence", 5, "New Age"),
(108, "Magic", 42, "Classical"),
(109, "Name", 20, "Opera"),
(110, "Fire", 23, "Country"),
(111, "Live", 18, "Jazz"),
(112, "Blues", 22, "Blues"),
(113, "Stages", 42, "Blues");
CREATE TABLE CDs2 (
CDID SMALLINT NOT NULL PRIMARY KEY,
CDName VARCHAR(50) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL
)
SELECT CDID, CDName, InStock
FROM CDs
WHERE Category="Blues" OR Category="Jazz";
select * from CDs2;
Only copy records
Drop table CDs;
Drop table CDs2;
CREATE TABLE CDs (
CDID SMALLINT NOT NULL PRIMARY KEY,
CDName VARCHAR(50) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL,
Category VARCHAR(20)
);
INSERT INTO CDs
VALUES (101, "Blood", 10, "Rock"),
(102, "Jazz", 17, "Jazz"),
(103, "Class", 9, "Classical"),
(104, "Violin", 24, NULL),
(105, "Blues", 2, "Blues"),
(106, "Tires", 12, "Country"),
(107, "Essence", 5, "New Age"),
(108, "Magic", 42, "Classical"),
(109, "Name", 20, "Opera"),
(110, "Fire", 23, "Country"),
(111, "Live", 18, "Jazz"),
(112, "Blues", 22, "Blues"),
(113, "Stages", 42, "Blues");
CREATE TABLE CDs2
(
CDID SMALLINT NOT NULL PRIMARY KEY,
CDName VARCHAR(5) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL
)
SELECT CDID, CDName, InStock
FROM CDs
WHERE Category="Blues" OR Category="Jazz";
select * from CDs2;
select * from CDs;
Use NULL in where clause
/*
mysql> Drop table Sale;
mysql> CREATE TABLE Sale
-> (
-> ID 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)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Cate
gory)
-> VALUES ("Bloodshot", 11, 6, 1, "Popular", "Ro
ck"),
-> ("Opera", 12, 5, 2, "Classical","Op
era"),
-> ("Jazz", 13, 4, 3, "Popular", "Ja
zz"),
-> ("Music", 4, 3, 4, "Classical","Da
nce"),
-> ("Violin", 25, 2, 5, "Classical", NU
LL),
-> ("Toscana", 16, 1, 6, "Classical", NU
LL),
-> ("Blues", 7, 22, 7, "Popular", "B
lues"),
-> ("Pure", 38, 5, 11, "Popular", NU
LL),
-> ("Mud", 19, 11, 12, "Popular", "Co
untry"),
-> ("Essence", 5, 23, 12, "Popular", "New
Age"),
-> ("Embrace", 21, 12, 14, "Popular", "New
Age"),
-> ("Satie", 42, 17, 15, "Classical", NU
LL),
-> ("Lake", 23, 47, 28, "Classical", "D
ance"),
-> ("Favorites", 34, 15, 12, "Classical", "G
eneral"),
-> ("Boheme", 25, 12, 5, "Classical", "O
pera"),
-> ("Cantatas", 26, 13, 8, "Classical", "G
eneral"),
-> ("Road", 27, 13, 17, "Popular", "Cou
ntry"),
-> ("Paris", 18, 25, 10, "Popular", "Jaz
z"),
-> ("Woman", 29, 4, 7, "Popular", "Blu
es"),
-> ("Bach", 21, 13, 16, "Classical", "G
eneral"),
-> ("Opera", 12, 32, 12, "Classical", "O
pera"),
-> ("Soul", 13, 30, 14, "Popular", NULL
),
-> ("Stages", 44, 0, 8, "Popular", "Blu
es"),
-> ("Bach", 15, 1, 8, "Classical", "G
eneral");
Query OK, 24 rows affected (0.00 sec)
Records: 24 Duplicates: 0 Warnings: 0
mysql> select * from Sale;
+----+-----------+---------+---------+----------+------------+----------+
| ID | Name | InStock | OnOrder | Reserved | Department | Category |
+----+-----------+---------+---------+----------+------------+----------+
| 1 | Bloodshot | 11 | 6 | 1 | Popular | Rock |
| 2 | Opera | 12 | 5 | 2 | Classical | Opera |
| 3 | Jazz | 13 | 4 | 3 | Popular | Jazz |
| 4 | Music | 4 | 3 | 4 | Classical | Dance |
| 5 | Violin | 25 | 2 | 5 | Classical | NULL |
| 6 | Toscana | 16 | 1 | 6 | Classical | NULL |
| 7 | Blues | 7 | 22 | 7 | Popular | Blues |
| 8 | Pure | 38 | 5 | 11 | Popular | NULL |
| 9 | Mud | 19 | 11 | 12 | Popular | Country |
| 10 | Essence | 5 | 23 | 12 | Popular | New Age |
| 11 | Embrace | 21 | 12 | 14 | Popular | New Age |
| 12 | Satie | 42 | 17 | 15 | Classical | NULL |
| 13 | Lake | 23 | 47 | 28 | Classical | Dance |
| 14 | Favorites | 34 | 15 | 12 | Classical | General |
| 15 | Boheme | 25 | 12 | 5 | Classical | Opera |
| 16 | Cantatas | 26 | 13 | 8 | Classical | General |
| 17 | Road | 27 | 13 | 17 | Popular | Country |
| 18 | Paris | 18 | 25 | 10 | Popular | Jazz |
| 19 | Woman | 29 | 4 | 7 | Popular | Blues |
| 20 | Bach | 21 | 13 | 16 | Classical | General |
| 21 | Opera | 12 | 32 | 12 | Classical | Opera |
| 22 | Soul | 13 | 30 | 14 | Popular | NULL |
| 23 | Stages | 44 | 0 | 8 | Popular | Blues |
| 24 | Bach | 15 | 1 | 8 | Classical | General |
+----+-----------+---------+---------+----------+------------+----------+
24 rows in set (0.00 sec)
mysql> SELECT Name, Department, Category
-> FROM Sale
-> WHERE Category=NULL
-> ORDER BY Name;
Empty set (0.00 sec)
*/
Drop table Sale;
CREATE TABLE Sale
(
ID 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)
);
INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Category)
VALUES ("Bloodshot", 11, 6, 1, "Popular", "Rock"),
("Opera", 12, 5, 2, "Classical","Opera"),
("Jazz", 13, 4, 3, "Popular", "Jazz"),
("Music", 4, 3, 4, "Classical","Dance"),
("Violin", 25, 2, 5, "Classical", NULL),
("Toscana", 16, 1, 6, "Classical", NULL),
("Blues", 7, 22, 7, "Popular", "Blues"),
("Pure", 38, 5, 11, "Popular", NULL),
("Mud", 19, 11, 12, "Popular", "Country"),
("Essence", 5, 23, 12, "Popular", "New Age"),
("Embrace", 21, 12, 14, "Popular", "New Age"),
("Satie", 42, 17, 15, "Classical", NULL),
("Lake", 23, 47, 28, "Classical", "Dance"),
("Favorites", 34, 15, 12, "Classical", "General"),
("Boheme", 25, 12, 5, "Classical", "Opera"),
("Cantatas", 26, 13, 8, "Classical", "General"),
("Road", 27, 13, 17, "Popular", "Country"),
("Paris", 18, 25, 10, "Popular", "Jazz"),
("Woman", 29, 4, 7, "Popular", "Blues"),
("Bach", 21, 13, 16, "Classical", "General"),
("Opera", 12, 32, 12, "Classical", "Opera"),
("Soul", 13, 30, 14, "Popular", NULL),
("Stages", 44, 0, 8, "Popular", "Blues"),
("Bach", 15, 1, 8, "Classical", "General");
select * from Sale;
SELECT Name, Department, Category
FROM Sale
WHERE Category=NULL
ORDER BY Name;