2、数据库和数据库表操作

 

一、（dos下）数据库的创建、删除、查看和使用

1.1 普通方式登录后再使用数据库

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Dell>cd c:\

c:\>d:

D:\>cd D:\Program Files\MySQL\MySQL Server 5.0\bin

D:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> --1.创建数据库
mysql> create database if not exists hollydata;
Query OK, 1 row affected (0.05 sec)

mysql> --2.查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hollydata |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)

mysql> --3.删除数据库
mysql> drop database if exists hollydata;
Query OK, 0 rows affected (0.31 sec)

mysql> --4.查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

mysql> --5.使用数据库(也就是数据的切换)
mysql> use test
Database changed
mysql>

1.2 在登录的时候就选择使用的数据库

mysql -uroot -D 数据库名 -p
enter password:ok

 

二、结构化语言分类

三、创建数据库表语法

四、数据库列类型

4.1 数值类型

4.2 字符串类型

4.3 时间类型

4.4 null类型

五、列的属性字段

5.1 创建数据库并使用数据

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

mysql> use hollydata;
Database changed

5.2 创建数据库表students

mysql> create table students
    -> (id int unsigned not null auto_increment primary key,
    ->  name char(8) not null,
    -> sex char(4) not null,
    -> age tinyint unsigned not null,
    -> tel char(13) null default '_'
    -> );
Query OK, 0 rows affected (0.45 sec)

5.3 查看表结构

mysql> desc students;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | char(8)             | NO   |     |         |                |
| sex   | char(4)             | NO   |     |         |                |
| age   | tinyint(3) unsigned | NO   |     |         |                |
| tel   | char(13)            | YES  |     | _       |                |
+-------+---------------------+------+-----+---------+----------------+
5 rows in set (0.08 sec)

mysql>

 

六、修改数据库表

 

6.1 修改表名

mysql> alter table students rename as stu;
Query OK, 0 rows affected (0.22 sec)

6.2 查看该数据下所有的数据库表

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

mysql>

6.3 添加字段

D:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -D hollydata -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> --添加password密码字段
mysql> alter table stu add password char(8);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> --查看表结构
mysql> desc stu;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name     | char(8)             | NO   |     |         |                |
| sex      | char(4)             | NO   |     |         |                |
| age      | tinyint(3) unsigned | NO   |     |         |                |
| tel      | char(13)            | YES  |     | _       |                |
| password | char(8)             | YES  |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql>

6.5 修改字段

mysql> --修改password字段
mysql> alter table stu modify password char(4);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table stu change password pwd char(5);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> --查看表结构
mysql> desc stu;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | char(8)             | NO   |     |         |                |
| sex   | char(4)             | NO   |     |         |                |
| age   | tinyint(3) unsigned | NO   |     |         |                |
| tel   | char(13)            | YES  |     | _       |                |
| pwd   | char(5)             | YES  |     | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql>

6.6 删除字段

mysql> alter table stu drop pwd;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| name  | char(8)             | NO   |     |         |                |
| sex   | char(4)             | NO   |     |         |                |
| age   | tinyint(3) unsigned | NO   |     |         |                |
| tel   | char(13)            | YES  |     | _       |                |
+-------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql>

七、删除数据库表

7.1 删除数据库表

mysql> drop table if exists stu;
Query OK, 0 rows affected (0.07 sec)

mysql> --查看该库下用户下的所有数据库表
mysql> show tables;
Empty set (0.00 sec)

mysql>