数据库查基本操作

1.1 创建数据库

-- 创建一个名为test的数据库，并制定为utf-8编码；

-- 注意：charset后应该写utf8而不是utf-8。

create database test charset=utf8;

1.2 使用数据库

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

1.3 显示当前使用的数据库

mysql> select database();

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

| database() |

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

| test      |

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

1 row in set (0.00 sec)

1.4 创建两个数据表

mysql> -- 记录学生信息的数据表students

mysql> create table students(

-> id int unsigned primary key auto_increment not null,

-> name varchar(20) default "",

-> age tinyint unsigned default 0,

-> height decimal(5,2),

-> gender enum("男", "女", "中性", "保密") default "保密",

-> cls_id int unsigned default 0,

-> is_delete bit default 0

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> -- 记录班级信息的数据表classes

mysql> create table classes(

-> id int unsigned auto_increment primary key not null,

-> name varchar(30) not null

-> );

Query OK, 0 rows affected (0.01 sec)

在终端输入创建上述两个数据表的命令后，可通过show tables;语句显示是否创建成功。

mysql> show tables;

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

| Tables_in_test |

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

| classes        |

| students      |

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

2 rows in set (0.00 sec)

1.5 向数据表插入数据

-- 向数据表students中插入14条记录

insert into students values

(0, "小明", 18, 188.88, 2, 1, 0),

(0, "小华", 18, 180.00, 2, 2, 0),

(0, "彭于晏", 29, 185.00, 1, 1, 0),

(0, "刘德华", 59, 175.00, 1, 2, 1),

(0, "黄蓉", 38, 160.00, 2, 1, 0),

(0, "凤姐", 28, 150.00, 4, 1, 0),

(0, "王祖贤", 18, 172.00, 2, 1, 1),

(0, "周杰伦", 36, NULL, 1, 1, 0),

(0, "陈坤", 27, 181.00, 1, 2, 0),

(0, "刘亦菲", 25, 166.00, 2, 2, 0),

(0, "金星", 33, 162.00, 3, 3, 1),

(0, "静香", 12, 180.00, 2, 4, 0),

(0, "郭靖", 12, 170.00, 1, 4, 0),

(0, "周杰", 34, 176.00, 2, 5, 0);

-- 向数据表classes中插入两条记录

insert into classes values(0, "金庸武侠班"), (0, "藤子F不二雄班");