一、（dos下）数据库的创建、删除、查看和使用
1.1 普通方式登录后再使用数据库
1 Microsoft Windows [版本 6.1.7601]数据库的创建、删除、查看、使用
2 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
3
4 C:\Users\Dell>cd c:\
5
6 c:\>d:
7
8 D:\>cd D:\Program Files\MySQL\MySQL Server 5.0\bin
9
10 D:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -p
11 Enter password: ****
12 Welcome to the MySQL monitor. Commands end with ; or \g.
13 Your MySQL connection id is 3
14 Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
15
16 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
17
18 mysql> --1.创建数据库
19 mysql> create database if not exists hollydata;
20 Query OK, 1 row affected (0.05 sec)
21
22 mysql> --2.查看数据库
23 mysql> show databases;
24 +--------------------+
25 | Database |
26 +--------------------+
27 | information_schema |
28 | hollydata |
29 | mysql |
30 | test |
31 +--------------------+
32 4 rows in set (0.00 sec)
33
34 mysql> --3.删除数据库
35 mysql> drop database if exists hollydata;
36 Query OK, 0 rows affected (0.31 sec)
37
38 mysql> --4.查看数据库
39 mysql> show databases;
40 +--------------------+
41 | Database |
42 +--------------------+
43 | information_schema |
44 | mysql |
45 | test |
46 +--------------------+
47 3 rows in set (0.00 sec)
48
49 mysql> --5.使用数据库(也就是数据的切换)
50 mysql> use test
51 Database changed
52 mysql>
1.2 在登录的时候就选择使用的数据库
1 mysql -uroot -D 数据库名 -p
2 enter password:ok
二、结构化语言分类
三、创建数据库表语法
四、数据库列类型
4.1 数值类型
4.2 字符串类型
4.3 时间类型
4.4 null类型
五、列的属性字段
5.1 创建数据库并使用数据
1 mysql> create database hollydata;创建数据库并使用数据
2 Query OK, 1 row affected (0.00 sec)
3
4 mysql> use hollydata;
5 Database changed
5.2 创建数据库表students
1 mysql> create table students创建数据库表students
2 -> (id int unsigned not null auto_increment primary key,
3 -> name char(8) not null,
4 -> sex char(4) not null,
5 -> age tinyint unsigned not null,
6 -> tel char(13) null default '_'
7 -> );
8 Query OK, 0 rows affected (0.45 sec)
5.3 查看表结构
1 mysql> desc students;查看表结构
2 +-------+---------------------+------+-----+---------+----------------+
3 | Field | Type | Null | Key | Default | Extra |
4 +-------+---------------------+------+-----+---------+----------------+
5 | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
6 | name | char(8) | NO | | | |
7 | sex | char(4) | NO | | | |
8 | age | tinyint(3) unsigned | NO | | | |
9 | tel | char(13) | YES | | _ | |
10 +-------+---------------------+------+-----+---------+----------------+
11 5 rows in set (0.08 sec)
12
13 mysql>
六、修改数据库表
6.1 修改表名
1 mysql> alter table students rename as stu;修改数据库表
2 Query OK, 0 rows affected (0.22 sec)
6.2 查看该数据下所有的数据库表
1 mysql> show tables;显示数据库下的所有数据库表
2 +---------------------+
3 | Tables_in_hollydata |
4 +---------------------+
5 | stu |
6 +---------------------+
7 1 row in set (0.00 sec)
8
9 mysql>
6.3 添加字段
1 D:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -D hollydata -u root -p添加字段
2 Enter password: ****
3 Welcome to the MySQL monitor. Commands end with ; or \g.
4 Your MySQL connection id is 6
5 Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
6
7 Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
8
9 mysql> --添加password密码字段
10 mysql> alter table stu add password char(8);
11 Query OK, 0 rows affected (0.32 sec)
12 Records: 0 Duplicates: 0 Warnings: 0
13
14 mysql> --查看表结构
15 mysql> desc stu;
16 +----------+---------------------+------+-----+---------+----------------+
17 | Field | Type | Null | Key | Default | Extra |
18 +----------+---------------------+------+-----+---------+----------------+
19 | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
20 | name | char(8) | NO | | | |
21 | sex | char(4) | NO | | | |
22 | age | tinyint(3) unsigned | NO | | | |
23 | tel | char(13) | YES | | _ | |
24 | password | char(8) | YES | | NULL | |
25 +----------+---------------------+------+-----+---------+----------------+
26 6 rows in set (0.01 sec)
27
28 mysql>
6.5 修改字段
1 mysql> --修改password字段修改字段
2 mysql> alter table stu modify password char(4);
3 Query OK, 0 rows affected (0.24 sec)
4 Records: 0 Duplicates: 0 Warnings: 0
5
6 mysql> alter table stu change password pwd char(5);
7 Query OK, 0 rows affected (0.23 sec)
8 Records: 0 Duplicates: 0 Warnings: 0
9
10 mysql> --查看表结构
11 mysql> desc stu;
12 +-------+---------------------+------+-----+---------+----------------+
13 | Field | Type | Null | Key | Default | Extra |
14 +-------+---------------------+------+-----+---------+----------------+
15 | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
16 | name | char(8) | NO | | | |
17 | sex | char(4) | NO | | | |
18 | age | tinyint(3) unsigned | NO | | | |
19 | tel | char(13) | YES | | _ | |
20 | pwd | char(5) | YES | | NULL | |
21 +-------+---------------------+------+-----+---------+----------------+
22 6 rows in set (0.01 sec)
23
24 mysql>
6.6 删除字段
1 mysql> alter table stu drop pwd;删除字段
2 Query OK, 0 rows affected (0.24 sec)
3 Records: 0 Duplicates: 0 Warnings: 0
4
5 mysql> desc stu;
6 +-------+---------------------+------+-----+---------+----------------+
7 | Field | Type | Null | Key | Default | Extra |
8 +-------+---------------------+------+-----+---------+----------------+
9 | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
10 | name | char(8) | NO | | | |
11 | sex | char(4) | NO | | | |
12 | age | tinyint(3) unsigned | NO | | | |
13 | tel | char(13) | YES | | _ | |
14 +-------+---------------------+------+-----+---------+----------------+
15 5 rows in set (0.01 sec)
16
17 mysql>
七、删除数据库表
7.1 删除数据库表
1 mysql> drop table if exists stu;删除数据库表
2 Query OK, 0 rows affected (0.07 sec)
3
4 mysql> --查看该库下用户下的所有数据库表
5 mysql> show tables;
6 Empty set (0.00 sec)
7
8 mysql>