mysql数据库表的创建以及字段的增删改查操作及一些常用的查询命令介绍

1.数据库表的创建以及字段的增删改查

1.由于创建表必须是在数据库中进行,所以要进入数据库(若没有,首先创建数据库)

(1)查看数据库

show databases;(当前存在的数据库)
示例
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | //mysql数据的元数据
| mysql              | //存储了用户的表user和对应的数据
| performance_schema | // mysql数据库的配置信息
| test               | // 测试数据库
+--------------------+

(2)创建数据库 删除数据库

(2)创建数据库
mysql> create database mysql_01;
Query OK, 1 row affected (0.13 sec)
 删除数据库
mysql> drop database mysql_01;
Query OK, 0 rows affected (0.00 sec)

(3)进入数据库

进入数据库
mysql> use mysql_01;
Database changed

(4)创建表及其对表的操作

创建表
mysql> create table student(id int,name varchar(20),gender varchar(2));
//语法:creat table 表名称(字段名称1 字段类型,字段名称2 字段类型..........)
Query OK, 0 rows affected (0.17 sec)

mysql> show tables;
+--------------------+
| Tables_in_mysql_01 |
+--------------------+
| student            |
+--------------------+

修改表的名称
mysql> alter table student rename to teacher;
Query OK, 0 rows affected (0.03 sec)

查看表中的字段

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(2)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

(5)表中字段的增删改查

表中字段的增删改查
给表中添加字段
mysql> alter table student add column age int;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(2)  | YES  |     | NULL    |       |
| age    | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
删除表中某个字段
mysql> alter table student drop column age ;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(2)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
修改字段类型:
mysql> alter table student modify column gender varchar(1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表中字段的名称
mysql> alter table student change column age age varchar(20);
Query OK, 0 rows affected (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(2)  | YES  |     | NULL    |       |
| age    | varchar(20) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

2.数据库中常用的一些查询命令

1.分页查询
– 起始行数是从0开始
– 语法结构:select * from 表名 limit 起始行数,每页显示的条数
– select * from 表名 limit 起始行(当前页码数-1)*每页显示的条数,每页显示条数

示例代码:
SELECT * FROM student LIMIT 2,2; 

2.聚合函数查询
– max()筛选出最大的
–min() 筛选出最小的
–avg() 求平均值
– count()函数 计数等等

示例代码
SELECT MAX(id) FROM student;
SELECT AVG(id) FROM student;
SELECT MIN(id) FROM student;
SELECT  COUNT(*) FROM student;

3.查询后排序
– desc :降序排序:数值是从大到小,字母是从z-a
– asc :升序排序:数值是从小到大,字母是a-z

SELECT * FROM student ;

4.分组查询
– 1)先进行分组
– 2)在当前分组之后,进行查询指定字段并且统计每个组有多个人
– 分组查询又存在条件查询where, where一定放在group by之前

SELECT address ,COUNT(*) FROM student GROUP BY address ;

5.分组后进行筛选
– 1)进行分组查询 2)根据需求进行筛选 统计人数大于2个
– having一定要放在group by 之后

SELECT address,COUNT(*) FROM student GROUP BY address HAVING COUNT(*) >2 ;