重拾SQL——从无到有
2016.10.22
因为工作需要，在这里提前重拾sql。
0.创建并选择数据库
mysql> SHOW DATABASES;
+--------------------+ | Database | +--------------------+ | information_schema | | menagerie | | mysql | | performance_schema | | phpmyadmin | | test | +--------------------+ 6 rows in set (0.00 sec)
1.创建并选择数据库
mysql> CREATE DATABASE tianyuan;
Query OK, 1 row affected (0.00 sec)
2.查看创建后的数据库
mysql> SHOW DATABASES;
+--------------------+ | Database | +--------------------+ | information_schema | | menagerie | | mysql | | performance_schema | | phpmyadmin | | test | | tianyuan | +--------------------+ 7 rows in set (0.00 sec)
3.访问它
mysql> USE tianyuan
Database changed
4.指定数据库中表的布局
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.14 sec)
5.显示表格
mysql> SHOW TABLES;
+--------------------+ | Tables_in_tianyuan | +--------------------+ | pet | +--------------------+ 1 row in set (0.00 sec)
6.验证表是按期望的方式创建（如果你忘记表中的列的名称或类型时）
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)
7.选择表格所有列的内容（无内容时）
mysql> SELECT * FROM pet;
Empty set (0.00 sec)
8.将数据装入表中
-
LOAD DATA
mysql> LOAD DATA LOCAL INFILE '/Users/v/Desktop/1.txt' INTO TABLE pet;'/Users/v/Desktop/1.txt':
Fluffy Harold cat f 1993-02-04 Claws Gwen cat m 1994-03-17 Buffy Harold dog f 1989-05-13
加载后
Query OK, 3 rows affected, 3 warnings (0.07 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 3
查看
mysql> SELECT * FROM pet;+--------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | +--------+--------+---------+------+------------+-------+ 3 rows in set (0.00 sec)
-
INSERT
mysql> INSERT INTO pet -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);Query OK, 1 row affected (0.05 sec)
查看
mysql> SELECT * FROM pet;+----------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+-------+ | Fluffy | Harold | cat | f | 1993-02-04 | NULL | | Claws | Gwen | cat | m | 1994-03-17 | NULL | | Buffy | Harold | dog | f | 1989-05-13 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | +----------+--------+---------+------+------------+-------+ 4 rows in set (0.00 sec)
9.删除数据库
mysql> SHOW DATABASES;
+--------------------+ | Database | +--------------------+ | information_schema | | menagerie | | mysql | | performance_schema | | phpmyadmin | | test | | tianyuan | +--------------------+ 7 rows in set (0.00 sec)
删除
mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.02 sec)
查看删除效果
mysql> SHOW DATABASES;
+--------------------+ | Database | +--------------------+ | information_schema | | menagerie | | mysql | | performance_schema | | phpmyadmin | | tianyuan | +--------------------+ 6 rows in set (0.00 sec)
