【无标题】

mysql>CREATE TABLE books
->(
->bk_id INT NOT NULL PRIMARY KEY,
->bk_title VARCHAR(50) NOT NULL,
->copyright YEAR NOT NULL
->);

mysql>CREATE TABLE authors
->(
->auth_id INT NOT NULL PRIMARY KEY,
->auth_name VARCHAR(20),
->auth_gender CHAR(1)
->);

mysql>CREATE TABLE authorbook
->(
->auth_id INT NOT NULL,
->bk_id INT NOT NULL
->);

mysql>
mysql> – 插入数据：
mysql> INSERT INTO books
-> VALUES (11078, ‘Learning MySQL’, 2010),
-> (11033, ‘Study Html’, 2011),
-> (11035, ‘How to use php’, 2003),
-> (11072, ‘Teach youself javascript’, 2005),
-> (11028, ‘Learing C++’, 2005),
-> (11069, ‘MySQL professional’, 2009),
-> (11026, ‘Guide to MySQL 5.5’, 2008),
-> (11041, ‘Inside VC++’, 2011);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0

mysql>
mysql> INSERT INTO authors
-> VALUES (1001, ‘WriterX’ ,‘f’),
-> (1002, ‘WriterA’ ,‘f’),
-> (1003, ‘WriterB’ ,‘m’),
-> (1004, ‘WriterC’ ,‘f’),
-> (1011, ‘WriterD’ ,‘f’),
-> (1012, ‘WriterE’ ,‘m’),
-> (1013, ‘WriterF’ ,‘m’),
-> (1014, ‘WriterG’ ,‘f’),
-> (1015, ‘WriterH’ ,‘f’);
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0

mysql>
mysql> INSERT INTO authorbook
-> VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
1：

C:\Windows\System32>cd C:\Program Files\MySQL\MySQL Server 8.0\bin
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -uroot -p123456 booksDB authorbook authors books> d:\booksDB_all_tables.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

C:\Program Files\MySQL\MySQL Server 8.0\bin>

2：
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -uroot -p123456 booksDB books> d:\booksDB_books_table.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
3：
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -uroot -p123456 booksDB test> d:\booksDB_and_test.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
4:
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -p123456 booksDB<d:\booksDB_books_tables.sql
5:
mysql> source d://booksDB_all_tables.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

二：索引

mysql> create table goods(
-> goods_id int primary key auto_increment,
-> goods_name varchar(20) not null,
-> cat_id int not null default 0,
-> brand_id int not null default 0,
-> goods_sn char(12) not null,
-> shop_price float(6,2) not null default 0.00,
-> goods_desc text
-> );
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>
mysql> create table category(
-> cat_id int primary key auto_increment,
-> cate_name varchar(20),
-> parent_id int default 0 );
Query OK, 0 rows affected (0.01 sec)

1:

mysql> alter table goods drop column goods_desc;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table goods drop column goods_sn;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table goods add column click_count int default 0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc goods;
±------------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------±------------±-----±----±--------±---------------+
| goods_id | int | NO | PRI | NULL | auto_increment |
| goods_name | varchar(20) | NO | | NULL | |
| cat_id | int | NO | | 0 | |
| brand_id | int | NO | | 0 | |
| shop_price | float(6,2) | NO | | 0.00 | |
| click_count | int | YES | | 0 | |
±------------±------------±-----±----±--------±---------------+
6 rows in set (0.00 sec)

mysql> desc category
-> ;
±----------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------±------------±-----±----±--------±---------------+
| cat_id | int | NO | PRI | NULL | auto_increment |
| cate_name | varchar(20) | YES | | NULL | |
| parent_id | int | YES | | 0 | |
±----------±------------±-----±----±--------±---------------+
3 rows in set (0.00 sec)

2:

mysql> alter table goods add unique index index_goods_name(goods_name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from goods \G;
*************************** 1. row ***************************
Table: goods
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: goods
Non_unique: 0
Key_name: index_goods_name
Seq_in_index: 1
Column_name: goods_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.01 sec)

3:

mysql> create index index_shop_price on goods(shop_price);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from goods \G;
*************************** 1. row ***************************
Table: goods
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: goods
Non_unique: 0
Key_name: index_goods_name
Seq_in_index: 1
Column_name: goods_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: goods
Non_unique: 1
Key_name: index_shop_price
Seq_in_index: 1
Column_name: shop_price
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.00 sec)

ERROR:
No query specified

4

mysql> create index index_click_count on goods(click_count);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from goods \G;
*************************** 1. row ***************************
Table: goods
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: goods
Non_unique: 0
Key_name: index_goods_name
Seq_in_index: 1
Column_name: goods_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: goods
Non_unique: 1
Key_name: index_shop_price
Seq_in_index: 1
Column_name: shop_price
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 4. row ***************************
Table: goods
Non_unique: 1
Key_name: index_click_count
Seq_in_index: 1
Column_name: click_count
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
4 rows in set (0.00 sec)
删除
mysql> drop index index_click_count on goods;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table goods drop index index_click_count;
ERROR 1091 (42000): Can’t DROP ‘index_click_count’; check that column/key exists
mysql> show index from goods \G;
*************************** 1. row ***************************
Table: goods
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: goods_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: goods
Non_unique: 0
Key_name: index_goods_name
Seq_in_index: 1
Column_name: goods_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
Table: goods
Non_unique: 1
Key_name: index_shop_price
Seq_in_index: 1
Column_name: shop_price
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
3 rows in set (0.00 sec)

原文地址：https://blog.csdn.net/weixin_74819554/article/details/140414836

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！