mysql怎么创建Grade表_MySQL 创建数据库 创建表

最近几天开始研究MySQL。那么先来看看最基本的MySQL操作。

1、使用SHOW语句找出在服务器上当前存在什么数据库：

mysql> SHOW DATABASES;

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

| Database |

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

| mysql |

| test |

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

3 rows in set (0.00 sec)

2、创建一个数据库starive

mysql> CREATE DATABASE starive;

注意不同操作系统对大小写的敏感。 3、选择你所创建的数据库

mysql> USE starive

Database changed

此时你已经进入你刚才所建立的数据库starive. 4、 创建一个数据库表 首先看现在你的数据库中存在什么表：

mysql> SHOW TABLES;

Empty set (0.00 sec)

说明刚才建立的数据库中还没有数据库表。下面来创建一个数据库表Student, Course, SC :

CREATE TABLE Student

(Sno CHAR(9) PRIMARY KEY,

Sname CHAR(20) NOT NULL,

Ssex CHAR(4),

Sage SMALLINT,

Sdept CHAR(20)) engine= innodb;

create table course(

cno CHAR(6),

cname varchar(20),

credit int,

primary key(cno)

) engine= innodb;

CREATE TABLE SC

(Sno CHAR(9) NOT NULL,

Cno CHAR(6) NOT NULL,

Grade SMALLINT,

PRIMARY KEY (Sno,Cno),

constraint f1 FOREIGN KEY (Sno) REFERENCES Student(Sno),

FOREIGN KEY (Cno) REFERENCES Course(Cno)

) engine= innodb;

创建了表后，我们可以看看刚才做的结果，用SHOW TABLES显示数据库中有哪些表：

mysql> show tables;

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

| Tables_in_starive |

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

| course |

| sc |

| student |

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

3 rows in set (0.00 sec)

5、显示表的结构：

mysql> describe student;

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

| Field | Type | Null | Key | Default | Extra |

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

| Sno | char(9) | NO | PRI | NULL | |

| Sname | char(20) | NO | | NULL | |

| Ssex | char(4) | YES | | NULL | |

| Sage | smallint(6) | YES | | NULL | |

| Sdept | char(20) | YES | | NULL | |

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

5 rows in set (0.22 sec)

6、 往表中加入记录 我们先用SELECT命令来查看表中的数据：

mysql> select * from student;

Empty set (0.00 sec)

这说明刚才创建的表还没有记录。 加入新记录：

INSERT INTO Student VALUES('0201','赵伟','男',18,'cs');

INSERT INTO Student VALUES('0202','张力虹','男',19,'is');

INSERT INTO Student VALUES('0203','徐秀美','女',21,'is');

INSERT INTO Student VALUES('0204','刘平','男',20,'cs');

INSERT INTO Student VALUES('0205','姚家全','男',19,'cs');

INSERT INTO Student VALUES('0206','上关美云','女',23,'ma');

报错：

mysql> INSERT INTO Student VALUES('0201','赵伟','男',18,'cs');

ERROR 1366 (HY000): Incorrect string value: '\xD5\xD4\xCE\xB0' for column 'Snam

' at row 1

mysql> INSERT INTO Student VALUES('0202','张力虹','男',19,'is');

ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC1\xA6\xBA\xE7' for colu

n 'Sname' at row 1

mysql> INSERT INTO Student VALUES('0203','徐秀美','女',21,'is');

ERROR 1366 (HY000): Incorrect string value: '\xD0\xEC\xD0\xE3\xC3\xC0' for colu

n 'Sname' at row 1

mysql> INSERT INTO Student VALUES('0204','刘平','男',20,'cs');

ERROR 1366 (HY000): Incorrect string value: '\xC1\xF5\xC6\xBD' for column 'Snam

' at row 1

mysql> INSERT INTO Student VALUES('0205','姚家全','男',19,'cs');

ERROR 1366 (HY000): Incorrect string value: '\xBC\xD2\xC8\xAB' for column 'Snam

' at row 1

mysql> INSERT INTO Student VALUES('0206','上关美云','女',23,'ma');

ERROR 1366 (HY000): Incorrect string value: '\xC9\xCF\xB9\xD8\xC3\xC0...' for c

lumn 'Sname' at row 1

mysql> select * from student;

Empty set (0.02 sec)

解决方案是：

在装MYSQL的时候选择的是utf8，所以在插入数据的时候出现编码的错误，解决方法是找到Mysql的安装目录下的my.ini文件，把里面的default-character-set=gbk。 重新启动MySQL.

再次插入数据：

INSERT INTO Student VALUES('0201','赵伟','男',18,'cs');

INSERT INTO Student VALUES('0202','张力虹','男',19,'is');

INSERT INTO Student VALUES('0203','徐秀美','女',21,'is');

INSERT INTO Student VALUES('0204','刘平','男',20,'cs');

INSERT INTO Student VALUES('0205','姚家全','男',19,'cs');

INSERT INTO Student VALUES('0206','上关美云','女',23,'ma');

再用上面的SELECT命令看：

mysql> select * from student;

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

| Sno | sname | Ssex | Sage | Sdept |

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

| 0201 | 赵伟 | 男 | 18 | cs |

| 0202 | 张力虹 | 男 | 19 | is |

| 0203 | 徐秀美 | 女 | 21 | is |

| 0204 | 刘平 | 男 | 20 | cs |

| 0205 | 姚家全 | 男 | 19 | cs |

| 0206 | 上关美云 | 女 | 23 | ma |

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

6 rows in set (0.00 sec)