一、（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 -p11 Enter password: ****12 Welcome to the MySQL monitor. Commands end with ; or \g.13 Your MySQL connection id is 314 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 test51 Database changed52 mysql>
1.2 在登录的时候就选择使用的数据库
1 mysql -uroot -D 数据库名 -p2 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 students2 -> (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: 013 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>
