Mysql第四章 MySQL数据库和表

第四章 MySQL数据库和表

1.数据库（DATABASE）操作

（1）创建数据库

mysql> create database stusys;

Query OK, 1 row affected (0.02 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sakila |

| stusys |

| sys |

| world |

+--------------------+

7 rows in set (0.00 sec)

mysql>

（2）查看数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

+--------------------+

6 rows in set (0.01 sec)

（3）选择数据库

mysql> use stusys;

Database changed

mysql>

（4）修改数据库

mysql> use stusys;

Database changed

mysql> use syusys;

ERROR 1049 (42000): Unknown database 'syusys'

mysql> use stusys;

Database changed

mysql> alter database stusys

-> default character set gb2312

-> default collate gb2312_chinese_ci;

Query OK, 1 row affected (0.01 sec)

mysql>

（5）删除数据库

mysql> drop database stusys;

Query OK, 0 rows affected (0.04 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sakila |

| sys |

| world |

+--------------------+

6 rows in set (0.00 sec)

mysql>

2.数据表（TABLE）操作

（1）表结构

行：记录

列：字段

(2)

mysql> create database stusys;

Query OK, 1 row affected (0.01 sec)

mysql> create table student

-> (

-> sno char(6) not null primary key ,

-> sname char(8) not null ,

-> ssex char(2) not null default '男',

-> sbirthday date not null ,

-> speciality char(12) null ,

-> tc tinyint null

-> );

ERROR 1046 (3D000): No database selected

mysql> use stusys;

Database changed

mysql> create table student

-> (

-> sno char(6) not null primary key ,

-> sname char(8) not null ,

-> ssex char(2) not null default '男',

-> sbirthday date not null ,

-> speciality char(12) null ,

-> tc tinyint null

-> );

Query OK, 0 rows affected (0.06 sec)