最近几天开始研究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)