这篇博客介绍了如何在MySQL中创建数据库、使用数据库、创建表以及进行数据插入、查询和操作。内容包括创建students和classes表，向表中插入数据，使用SELECT语句进行各种查询，如指定字段、别名、消除重复行、范围查询、排序等。
数据库环境准备:
创建数据库
create database mysql_demo1 charset=utf8;
MariaDB [(none)]> create database mysql_demo1 charset=utf8;
Query OK, 1 row affected (0.00 sec)
使用数据库
use mysql_demo1;
MariaDB [(none)]> use mysql_demo1;
Database changed
创建表参数
unsigned 整型无符号
signed 整型有符号
primary key 详解参考:http://c.biancheng.net/view/2440.html
foreign key 详解参考:http://c.biancheng.net/view/2441.html
unique key 详解参考:http://c.biancheng.net/view/2445.html
auto_increment 自增字段
not null 不为空
default 默认值
int int类型
char 存储字符串
varchar 存储字符串（char是属于固定长度的字符类型，而varchar是属于可变长度的字符类型。）
tinyint 型的字段如果不设置UNSIGNED类型,存储-128到127的整数。 （tinyint(1)，tinyint(２),tinyint(3),tinyint(4)）(稍后详解)
DECIMAL (5,2)规定了存储的值将不会超过5位数字，开且小数点后面有2位数字。
enum 枚举类型 添加新的数据不为这四个值则会报错
bit 利用它可定义一个位变量，但不能定义位指针，也不能定义位数组。它的值是一个二进制位，不是0就是1，类似Boolean类型中的True和False。
创建students表
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
);
MariaDB [mysql_demo1]> 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.01 sec)
创建classes表
create table classes(
id int unsigned auto_increment primary key not null,
name varchar(30) not null
);
MariaDB [mysql_demo1]> create table classes(
-> id int unsigned auto_increment primary key not null,
-> name varchar(30) not null
-> );
Query OK, 0 rows affected (0.00 sec)
查看表是否创建成功
show tables;
MariaDB [mysql_demo1]> show tables;
+-----------------------+
| Tables_in_mysql_demo1 |
+-----------------------+
| classes |
| students |
+-----------------------+
2 rows in set (0.00 sec)
准备数据:
向students表中插入数据
insert into students values
(0,‘小明’,18,180.00,1,1,0),
(0,‘小张’,18,160.00,1,2,0),
(0,‘小红’,20,170.00,2,1,0),
(0,‘周杰’,38,175.00,1,1,0),
(0,‘彭玉宴’,56,150.00,1,2,0),
(0,‘刘德化’,29,150.00,1,2,0),
(0,‘张学友’,36,180.00,1,1,0),
(0,‘周杰伦’,25,166.00,1,1,0),
(0,‘风姐’,16,169.00,2,1,0),
(0,‘王小明’,57,162.00,1,2,1),
(0,‘张小华’,46,173.00,2,1,0),
(0,‘金星’,29,175.00,2,1,1),
(0,‘黄蓉’,66,185.00,3,2,0),
(0,‘古天乐’,15,186.00,4,2,0),
(0,‘刘小海’,31,175.00,4,1,0),
(0,‘小月月’,57,163.00,2,1,0);
MariaDB [mysql_demo1]> insert into students values
-> (0,'小明',18,180.00,1,1,0),
-> (0,'小张',18,160.00,1,2,0),
-> (0,'小红',20,170.00,2,1,0),
-> (0,'周杰',38,175.00,1,1,0),
-> (0,'彭玉宴',56,150.00,1,2,0),
-> (0,'刘德化',29,150.00,1,2,0),
-> (0,'张学友',36,180.00,1,1,0),
-> (0,'周杰伦',25,166.00,1,1,0),
-> (0,'风姐',16,169.00,2,1,0),
-> (0,'王小明',57,162.00,1,2,1),
-> (0,'张小华',46,173.00,2,1,0),
-> (0,'金星',29,175.00,2,1,1),
-> (0,'黄蓉',66,185.00,3,2,0),
-> (0,'古天乐',15,186.00,4,2,0),
-> (0,'刘小海',31,175.00,4,1,0),
-> (0,'小月月',57,163.00,2,1,0);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
向classes表插入数据
insert into classes values
(0, “python_01期”),
(0, “python_02期”);
MariaDB [mysql_demo1]> insert into classes values
-> (0, "python_01期"),
-> (0, "python_02期");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
查询所有字段
select * from 表名; (也验证一下上面数据是否插入成功)
select * from students;
select * from classes;
MariaDB [mysql_demo1]> select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 2 | 小张 | 18 | 160.00 | 男 | 2 | |
| 3 | 小红 | 20 | 170.00 | 女 | 1 | |
| 4 | 周杰 | 38 | 175.00 | 男 | 1 | |
| 5 | 彭玉宴 | 56 | 150.00 | 男 | 2 | |
| 6 | 刘德化 | 29 | 150.00 | 男 | 2 | |
| 7 | 张学友 | 36 | 180.00 | 男 | 1 | |
| 8 | 周杰伦 | 25 | 166.00 | 男 | 1 | |
| 9 | 风姐 | 16 | 169.00 | 女 | 1 | |
| 10 | 王小明 | 57 | 162.00 | 男 | 2 | |
| 11 | 张小华 | 46 | 173.00 | 女 | 1 | |
| 12 | 金星 | 29 | 175.00 | 女 | 1 | |
| 13 | 黄蓉 | 66 | 185.00 | 中性 | 2 | |
| 14 | 古天乐 | 15 | 186.00 | 保密 | 2 | |
| 15 | 刘小海 | 31 | 175.00 | 保密 | 1 | |
| 16 | 小月月 | 57 | 163.00 | 女 | 1 | |
+----+-----------+------+--------+--------+--------+-----------+
16 rows in set (0.00 sec)
MariaDB [mysql_demo1]> select * from classes;
+----+--------------+
| id | name |
+----+--------------+
| 1 | python_01期 |
| 2 | python_02期 |
+----+--------------+
2 rows in set (0.00 sec)
查询指定字段
select 列1,列2,… from 表名;
select name,age from students;
MariaDB [mysql_demo1]> select name,age from students;
+-----------+------+
| name | age |
+-----------+------+
| 小明 | 18 |
| 小张 | 18 |
| 小红 | 20 |
| 周杰 | 38 |
| 彭玉宴 | 56 |
| 刘德化 | 29 |
| 张学友 | 36 |
| 周杰伦 | 25 |
| 风姐 | 16 |
| 王小明 | 57 |
| 张小华 | 46 |
| 金星 | 29 |
| 黄蓉 | 66 |
| 古天乐 | 15 |
| 刘小海 | 31 |
| 小月月 | 57 |
+-----------+------+
16 rows in set (0.00 sec)
使用 as 给字段起别名 作用于当前语句
select 字段 as 名字… from 表名;
select name as 名字,age as 年龄 from students;
MariaDB [mysql_demo1]> select name as 名字,age as 年龄 from students;
+-----------+-------------+
| 名字 | as 年龄 |
+-----------+-------------+
| 小明 | 18 |
| 小张 | 18 |
| 小红 | 20 |
| 周杰 | 38 |
| 彭玉宴 | 56 |
| 刘德化 | 29 |
| 张学友 | 36 |
| 周杰伦 | 25 |
| 风姐 | 16 |
| 王小明 | 57 |
| 张小华 | 46 |
| 金星 | 29 |
| 黄蓉 | 66 |
| 古天乐 | 15 |
| 刘小海 | 31 |
| 小月月 | 57 |
+-----------+-------------+
16 rows in set (0.00 sec)
使用 as 给表名起别名 作用于当前语句
select 表别名.字段1,表别名.字段2 from 表 as 表别名;
select s.name,s.age from students as s;
MariaDB [mysql_demo1]> select s.name,s.age from students as s;
+-----------+------+
| name | age |
+-----------+------+
| 小明 | 18 |
| 小张 | 18 |
| 小红 | 20 |
| 周杰 | 38 |
| 彭玉宴 | 56 |
| 刘德化 | 29 |
| 张学友 | 36 |
| 周杰伦 | 25 |
| 风姐 | 16 |
| 王小明 | 57 |
| 张小华 | 46 |
| 金星 | 29 |
| 黄蓉 | 66 |
| 古天乐 | 15 |
| 刘小海 | 31 |
| 小月月 | 57 |
+-----------+------+
16 rows in set (0.00 sec)
跨表查询 这种查不准不常用
select students.name,classes.name from students,classes;
MariaDB [mysql_demo1]> select students.name,classes.name from students,classes;
+-----------+--------------+
| name | name |
+-----------+--------------+
| 小明 | python_01期 |
| 小明 | python_02期 |
| 小张 | python_01期 |
| 小张 | python_02期 |
| 小红 | python_01期 |
| 小红 | python_02期 |
| 周杰 | python_01期 |
| 周杰 | python_02期 |
| 彭玉宴 | python_01期 |
| 彭玉宴 | python_02期 |
| 刘德化 | python_01期 |
| 刘德化 | python_02期 |
| 张学友 | python_01期 |
| 张学友 | python_02期 |
| 周杰伦 | python_01期 |
| 周杰伦 | python_02期 |
| 风姐 | python_01期 |
| 风姐 | python_02期 |
| 王小明 | python_01期 |
| 王小明 | python_02期 |
| 张小华 | python_01期 |
| 张小华 | python_02期 |
| 金星 | python_01期 |
| 金星 | python_02期 |
| 黄蓉 | python_01期 |
| 黄蓉 | python_02期 |
| 古天乐 | python_01期 |
| 古天乐 | python_02期 |
| 刘小海 | python_01期 |
| 刘小海 | python_02期 |
| 小月月 | python_01期 |
| 小月月 | python_02期 |
+-----------+--------------+
32 rows in set (0.00 sec)
distinct 消除重复行 也就是指定列的不重复的值
以students为例 里面有四个性别 我们查询出来
select distinct gender from students;
MariaDB [mysql_demo1]> select distinct gender from students;
+--------+
| gender |
+--------+
| 男 |
| 女 |
| 中性 |
| 保密 |
+--------+
4 rows in set (0.00 sec)
如果distinct后有多个字段, 只有当查询的多列的查询结果完全相同才能去重
查询age 大于18岁的所有数据
select * from students where age > 18;
MariaDB [mysql_demo1]> select * from students where age > 18;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 3 | 小红 | 20 | 170.00 | 女 | 1 | |
| 4 | 周杰 | 38 | 175.00 | 男 | 1 | |
| 5 | 彭玉宴 | 56 | 150.00 | 男 | 2 | |
| 6 | 刘德化 | 29 | 150.00 | 男 | 2 | |
| 7 | 张学友 | 36 | 180.00 | 男 | 1 | |
| 8 | 周杰伦 | 25 | 166.00 | 男 | 1 | |
| 10 | 王小明 | 57 | 162.00 | 男 | 2 | |
| 11 | 张小华 | 46 | 173.00 | 女 | 1 | |
| 12 | 金星 | 29 | 175.00 | 女 | 1 | |
| 13 | 黄蓉 | 66 | 185.00 | 中性 | 2 | |
| 15 | 刘小海 | 31 | 175.00 | 保密 | 1 | |
| 16 | 小月月 | 57 | 163.00 | 女 | 1 | |
+----+-----------+------+--------+--------+--------+-----------+
查询age 大于18 小于40的所有数据 有两种方法
select * from students where age > 18 and age < 40;
select * from students where age between 18 and 40;
注意两种不同之处 between是并等于条件
MariaDB [mysql_demo1]> select * from students where age > 18 and age < 40;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 3 | 小红 | 20 | 170.00 | 女 | 1 | |
| 4 | 周杰 | 38 | 175.00 | 男 | 1 | |
| 6 | 刘德化 | 29 | 150.00 | 男 | 2 | |
| 7 | 张学友 | 36 | 180.00 | 男 | 1 | |
| 8 | 周杰伦 | 25 | 166.00 | 男 | 1 | |
| 12 | 金星 | 29 | 175.00 | 女 | 1 | |
| 15 | 刘小海 | 31 | 175.00 | 保密 | 1 | |
+----+-----------+------+--------+--------+--------+-----------+
7 rows in set (0.00 sec)
MariaDB [mysql_demo1]> select * from students where age between 18 and 40;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 2 | 小张 | 18 | 160.00 | 男 | 2 | |
| 3 | 小红 | 20 | 170.00 | 女 | 1 | |
| 4 | 周杰 | 38 | 175.00 | 男 | 1 | |
| 6 | 刘德化 | 29 | 150.00 | 男 | 2 | |
| 7 | 张学友 | 36 | 180.00 | 男 | 1 | |
| 8 | 周杰伦 | 25 | 166.00 | 男 | 1 | |
| 12 | 金星 | 29 | 175.00 | 女 | 1 | |
| 15 | 刘小海 | 31 | 175.00 | 保密 | 1 | |
+----+-----------+------+--------+--------+--------+-----------+
9 rows in set (0.00 sec)
查询age 大于18小于40的所有女性的数据
select * from students where age between 18 and 40 and gender=‘女’;
MariaDB [mysql_demo1]> select * from students where age between 18 and 40 and gender='女';
+----+--------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+--------+------+--------+--------+--------+-----------+
| 3 | 小红 | 20 | 170.00 | 女 | 1 | |
| 12 | 金星 | 29 | 175.00 | 女 | 1 | |
+----+--------+------+--------+--------+--------+-----------+
2 rows in set (0.00 sec)
order by 排序 asc正序 desc倒序 查询男性身高从高到底查询
select * from students where gender=‘男’ order by height desc;
MariaDB [mysql_demo1]> select * from students where gender='男' order by height desc;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 7 | 张学友 | 36 | 180.00 | 男 | 1 | |
| 4 | 周杰 | 38 | 175.00 | 男 | 1 | |
| 8 | 周杰伦 | 25 | 166.00 | 男 | 1 | |
| 10 | 王小明 | 57 | 162.00 | 男 | 2 | |
| 2 | 小张 | 18 | 160.00 | 男 | 2 | |
| 5 | 彭玉宴 | 56 | 150.00 | 男 | 2 | |
| 6 | 刘德化 | 29 | 150.00 | 男 | 2 | |
+----+-----------+------+--------+--------+--------+-----------+
8 rows in set (0.00 sec)
查询男性身高从高到底查询 如果身高一样则年龄从高到底来显示 依次类推
select * from students where gender=‘男’ order by height desc,age desc;
MariaDB [mysql_demo1]> select * from students where gender='男' order by height desc,age desc;
+----+-----------+------+--------+--------+--------+-----------+
| id | name | age | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
| 7 | 张学友 | 36 | 180.00 | 男 | 1 | |
| 1 | 小明 | 18 | 180.00 | 男 | 1 | |
| 4 | 周杰 | 38 | 175.00 | 男 | 1 | |
| 8 | 周杰伦 | 25 | 166.00 | 男 | 1 | |
| 10 | 王小明 | 57 | 162.00 | 男 | 2 | |
| 2 | 小张 | 18 | 160.00 | 男 | 2 | |
| 5 | 彭玉宴 | 56 | 150.00 | 男 | 2 | |
| 6 | 刘德化 | 29 | 150.00 | 男 | 2 | |
+----+-----------+------+--------+--------+--------+-----------+
8 rows in set (0.01 sec)
扫一扫
527
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
