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 ;
希望与广大网友互动??
点此进行留言吧!