MySQL建表查询

首先打开服务，连接等等

C:\Windows\system32>e:

E:\>cd mysql

E:\mysql>cd mysql-5.6.22-winx64

E:\mysql\mysql-5.6.22-winx64>cd bin

E:\mysql\mysql-5.6.22-winx64\bin>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。

E:\mysql\mysql-5.6.22-winx64\bin>mysql -h 127.0.0.1 -u root -p
Enter password: ***********

mysql>

创建数据库newbase

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> create database newbase;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| newbase            |
| test               |
+--------------------+
4 rows in set (0.00 sec)

要使用，得切换，可以看到此刻表是空的

mysql> use newbase;
Database changed
mysql> show tables;
Empty set (0.00 sec)

假如需要创建一个咋们几个人今年特性的表，比如出生年月，年龄；通过varchar表示姓名和性别列，变长的，长度也可以不用相等，而且根据长度选择；生日可以用DATE数据类

mysql> create table people (name varchar(10), birth date, sex varchar(10));
Query OK, 0 rows affected (0.43 sec)

mysql> show tables;
+-------------------+
| Tables_in_newbase |
+-------------------+
| people            |
+-------------------+
1 row in set (0.00 sec)

mysql> describe people;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
| sex   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

表中插入数据

如果通过insert命令插入：

mysql> insert into people
    -> values ('lilei', 19841209, 'male');
Query OK, 1 row affected (0.09 sec)

mysql> insert into people
    -> values ('lucy', 19860209, 'female');
Query OK, 1 row affected (0.07 sec)

mysql> insert into people
    -> values ('tom', 19901109, 'male');
Query OK, 1 row affected (0.09 sec)

mysql> select * from people;
+-------+------------+--------+
| name  | birth      | sex    |
+-------+------------+--------+
| lilei | 1984-12-09 | male   |
| lucy  | 1986-02-09 | female |
| tom   | 1990-11-09 | male   |
+-------+------------+--------+
3 rows in set (0.00 sec)

如果直接导入磁盘txt文件：

lihui@2015 /cygdrive/d
$ cat people.txt
lilei   19841209      male
lucy    19860209      female
tom     19901109      male

将磁盘文件里内容插入数据库，比如D盘存放了一个people.txt，分别记录了三个人的信息，中间以TAB键分隔，先将上面插入表里的数据删除，然后直接导入文本

mysql> delete from people;
Query OK, 3 rows affected (0.08 sec)

mysql> select * from people;
Empty set (0.00 sec)

mysql> load data local infile 'd:\people.txt' into table people
    -> lines terminated by '\r\n'
    -> ;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from people;
+-------+------------+--------+
| name  | birth      | sex    |
+-------+------------+--------+
| lilei | 1984-12-09 | male   |
| lucy  | 1986-02-09 | female |
| tom   | 1990-11-09 | male   |
+-------+------------+--------+
3 rows in set (0.00 sec)

这样，最简单的插入查询就OK了~！