mysql创建数据库

时间:2013-10-04 04:10:07      作者:admin      标签:      分类: Mysql

mysql> use test     --使用test数据库，以后的操作都在这个数据库下进行
Database changed
mysql> ls
    -> \c           --\c表示取消运行，重新开始输入
mysql> show tables;      ---显示当前数据库表
Empty set (0.00 sec)
----------如下是创建数据表语句，表名为pet，创建name,owner,species,sex,birth,death列
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.04 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |     ---已经创建成功
+----------------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

数据库和表已经创建完毕，现在开始向数据库插入数据。

mysql> INSERT INTO pet (name,owner,species,sex,birth,death)VALUES('xixi','liuzhen','dog','m','2013-10-04',null);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM pet;    
+------+---------+---------+------+------------+-------+
| name | owner   | species | sex  | birth      | death |
+------+---------+---------+------+------------+-------+
| xixi | liuzhen | dog     | m    | 2013-10-04 | NULL  |
+------+---------+---------+------+------------+-------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

下面介绍另一种方法插入数据LOAD DATA 或LOAD DATA LOCAL

mysql> LOAD DATA INFILE '/root/Desktop/pet.sql' INTO TABLE pet LINES TERMINATED BY '\n'; 
Query OK, 9 rows affected (0.05 sec)Records: 9  Deleted: 0  Skipped: 0  Warnings: 0
Records: 9  Deleted: 0  Skipped: 0  Warnings: 0

LOAD DATA:是直接读取数据库服务器的文件

LOAD DATA LOCAL：是读取客户端本地文件，所以效率没有load data高。

LINES TERMINATED BY '\n' 表示每一行以\n结束，如果是windows下面需要设置为'\r\n'

那么列分隔符该怎么写？FIELDS TERMINATED BY          ---设定列分隔字符

想了下，还是把刚才load的文件显示一下

[root@localhost Desktop]# cat pet.sql 
Fluffy  Harold  cat     F       1993-02-04      \N
Claws   Gwen    cat     M       1994-03-17      \N
Buffy   Harold  dog     F       1989-05-13      \N
Chirpy  Gwen    bird    F       1998-09-11      \N
Fang    Benny   dog     M       1990-08-27      \N
Bowser  Diane   dog     M       1990-08-31      1995-07-29
Whistler        Gwen    bird    \N      1997-12-09      \N
Slim    Benny   snake   M       1996-04-29      \NPuffball        Diane   hamster F       1999-03-30      \N
Puffball        Diane   hamster F       1999-03-30      \N

其中\N表示为空

现在已经插入了数据，开始查询数据吧。

mysql> SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat' AND death is NULL ORDER BY name ;  
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Bowser | dog     | 1990-08-31 |
| Buffy  | dog     | 1989-05-13 |
| Claws  | cat     | 1994-03-17 |
| Fang   | dog     | 1990-08-27 |
| Fluffy | cat     | 1993-02-04 |
| xixi   | dog     | 2013-10-04 |
+--------+---------+------------+6 rows in set (0.00 sec)
6 rows in set (0.00 sec)

如上查询需要注意

1、or和and的优先级，具体的优先级是：not>and>or

2、需要排序可以用order by

3、NULL是特殊的值，不能使用普通比较符来比较，需要用is或is not

 

例如：mysql> SELECT * FROM pet WHERE name LIKE 'b%';