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 ;