MySQL语句数据查询（增删改查）

最新推荐文章于 2024-08-11 09:30:00 发布

muzi_sww

最新推荐文章于 2024-08-11 09:30:00 发布

阅读量309

点赞数

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_40633490/article/details/106744267

版权

SQL语句中的快捷键

\G 格式化输出（文本式，竖立显示）

\s 查看服务器端信息

\c 结束命令输入操作

\q 退出当前sql命令行模式

\h 查看帮助

MySQL数据库doc命令行操作

连接mysql数据库
- mysql -u 用户名 -p 密码

mysql -u root -p

查看所有数据库

show databases;

创建数据库
- create database 数据库名称;

create database student default charset=utf8;

删除数据库（删库有风险，动手需谨慎）
- drop database 数据库名称;

# 删除库，那么库中的所有数据都将在磁盘中删除
drop database student;

使用指定数据库
- use 数据库名称;

use student;

查看一个数据库的全部表

show tables;

创建数据库表

create table 表名称(

字段名称1 字段类型 [default 默认值 ] [约束],

字段名称2 字符类型 [default 默认值] [约束],

…

字段名称n 字符类型 [default 默认值] [约束]

);

如果表不存在则创建，如果存在就不执行这条命令

create table if not exists 表名(字段1 类型,字段2 类型);

# 以下创建一个student的表
create table student(
    -- 创建ID字段，为正整数，不允许为空 主键，自动递增
	id int auto_increment primary key,
    -- 创建 存储 名字的字段，为字符串类型，最大长度 20个字符，不允许为空
	name varchar(20) not null,
    -- 创建 存储 性别的字段，为字符串类型，最大长度 4个字符，允许为空
    sex varchar(4),
    -- 创建 存储 出生日期的字段，为date类型
    birth date,
    -- 创建 存储 部门名称的字段，为字符串类型，最大长度 20个字符，允许为空
    department varchar(20),
    -- 创建 存储 住址的字段，为字符串类型，最大长度 50个字符，允许为空
    address varchar(50)
);

查看表结构
- desc 表名称;

desc student;

查看建表语句：

show create table student;

修改表结构

alter table 表名 action(更改的选项)

添加字段 alter table 表名 add 添加的字段信息

-- 在 student 表中 追加 一个 num 字段 
alter table users add num int not null; 

-- 在指定字段后面追加字段 
在 student 表中birth字段后面 添加一个 email 字段 
alter table users add email varchar(50) after age; 

-- 在指定字段后面追加字段，在 users 表中name字段后面 添加一个 phone 
alter table users add phone char(11) not null after age;

-- 在表的最前面添加一个字段 
alter table users add aa int first;

删除字段 alter table 表名 change|modify 被修改的字段信息

change：可以修改字段名

modify：不能修改字段名

# 修改表中的 num 字段 类型，使用 modify 不修改字段名
alter table student modify num tinyint not null default 12; 

# 修改表中的 num 字段 为 int并且字段名为 count 
alter table student change num count int;

注意：一般情况下，无特殊要求，不要轻易修改表结构

修改表名：alter table 原表名 rename as 新表名

alter table student rename as user;

更改表中自增的值：在常规情况下默认从1开始自增

alter table student auto_increment=1000;

修改表引擎：推荐在定义表时，表引擎为innodb

# 通过查看建表语句获取当前的表引擎 
mysql> show create table student\G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `Name` varchar(20) NOT NULL COMMENT '姓名',
  `Sex` varchar(4) DEFAULT NULL COMMENT '性别',
  `Birth` year(4) DEFAULT NULL COMMENT '出生年份',
  `Department` varchar(20) DEFAULT NULL COMMENT '院系',
  `Address` varchar(50) DEFAULT NULL COMMENT '家庭住址',
  `classid` int(11) DEFAULT NULL,
  `age` int(23) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

# 直接查看当前表状态信息 
mysql> show table status from student where name='student'\G';
*************************** 1. row ***************************
           Name: student
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7
    Create_time: 2020-06-09 14:57:45
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
        
# 修改表引擎语句
alter table student engine='myisam';

向表中增加数据
- insert into 表名称[(字段1,字段2,字段3,…,字段n)] values(值1,值2,值3,…,值n);

insert into student(id,name,sex,birth,department,address) values(1,'may','女','2005-08-22','产品研发部','杭州');

说明：

在增加数据时，如果是字符串，则一定要使用’'括起来，如果是数字，则不需要。如果是日期，则按照标准的日期格式进行插入。

MySQL数据库中的日期使用YYYY-MM-DD的格式保存，所以在插入数据时，必须按照此种此种格式插入，例如2008-08-08.

删除数据
- delete from 表名称 [删除条件]

在进行删除数据时，最好指定删除的条件，如果没有指定，则表示删除一张表中的全部数据。一般这儿条件都是使用id表示。

delete from student where id=1;

更新数据
- update 表名称 set 字段1=值1,…,字段n=值n [where 更新条件];

与删除语句一样，修改也需要指定修改条件，否则数据表的全部记录都将被修改。一般条件都使用id表示。

update student set name='Tom',age=5,birthday='2000=06-06' where id=1;

查询数据

基础查询

# 查询表中所有数据
select * from student;
#指定字段列表进行查询
select name,birthday from student;

where子句查询

可以在where子句中指定任何条件

可以使用 and 或者 or 指定一个或多个条件

where条件也可以运用在update和delete语句的后面

where子句类似程序语言中if条件，根据mysql表中的字段值来进行数据的过滤

-- 查询users表中 age > 22的数据 
select * from users where age > 22; 

-- 查询 users 表中 name=某个条件值 的数据 
select * from users where name = '王五'; 

-- 查询 users 表中 年龄在22到25之间的数据
select * from users where age >= 22 and age <= 25; select * from users where age between 22 and 25;

-- 查询 users 表中 年龄不在22到25之间的数据
select * from users where age < 22 or age > 25; select * from users where age not between 22 and 25; 

-- 查询 users 表中 年龄在22到25之间的女生信息
select * from users where age >= 22 and age <= 25 and sex = '女';

and和or使用时注意

假设要求查询 users 表中年龄为22或者25 的女生信息

select * from users where age=22 or age=25 and sex=‘女’;

思考上面的语句能否返回符合条件的数据？

实际查询结果并不符合要求？

select * from users where age=22 or age = 25 and sex = '女'; 
+------+--------+------+-------+-------+------+------+ 
| id | name | age | phone | email | sex | mm | 
+------+--------+------+-------+-------+------+------+ 
| 1 | 章三 | 22 | |   |NULL | 男 | 0   |
| 2 | cc  | 25 | 123 | NULL | 女 | NULL |
+------+--------+------+-------+-------+------+------+ 

-- 上面的查询结果并不符合 查询条件的要求。 
-- 问题出在 sql 计算的顺序上，sql会优先处理and条件
-- 查询变成了为年龄22的不管性别，或者年龄为 25的女生 
-- 如何改造sql符合我们的查询条件呢？ 
-- 使用小括号来关联相同的条件 

select * from users where (age=22 or age = 25) and sex = '女';
+------+------+------+-------+-------+------+------+
| id | name | age | phone | email | sex | mm |
+------+------+------+-------+-------+------+------+
| 2 | cc | 25 | 123 | NULL | 女 | NULL |
+------+------+------+-------+-------+------+------+

模糊查询|like子句

-- like 语句 like某个确定的值 和where name = '王五' 是一样 
select * from users where name like '王五';
+----+--------+------+-------+-----------+------+------+
| id | name | age | phone | email | sex | mm |
+----+--------+------+-------+-----------+------+------+
| 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 |
+----+--------+------+-------+-----------+------+------+ 

-- 使用 % 模糊搜索。%代表任意个任意字符
-- 查询name字段中包含五的
select * from users where name like '%五%';

-- 查询name字段中最后一个字符 为 五的 
select * from users where name like '%五'; 

-- 查询name字段中第一个字符 为 王 的 
select * from users where name like '王%';

-- 使用 _ 单个的下划线。表示一个任意字符，使用和%类似
-- 查询表中 name 字段为两个字符的数据 
select * from users where name like '__'; 

-- 查询 name 字段最后为五的两个字符的数据 
select * from users where name like '_五';

注意：

where子句中的like在使用%或者_进行模糊搜索时，效率不高。

使用时注意：

尽可能的不去使用%或者_

如果需要使用，也尽可能不要把通配符放在开头处

退出mysql

exit;或者quit;

表的字段约束

unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)
字段类型后面加括号限制宽度

char(5). varchar(7) 在字符类型后面加限制表示字符串的长度
int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10)
int(4) unsigned zerofill只有当给int类型设置有前导零时，设置int的宽度才有意义。

not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错
default 设置默认值
primary key 主键不能为空,且唯一，一般和自动递增一起配合使用。
auto_increment 定义列为自增属性，一般用于主键，数值会自动加1
unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

MySQL中的统计函数（聚合函数）

max(),min(),count(),sum(),avg()

# 计算 users 表中 最大年龄，最小年龄，年龄和及平均年龄 
select max(age),min(age),sum(age),avg(age) from users; 
+----------+----------+----------+----------+ 
| max(age) | min(age) | sum(age) | avg(age) | 
+----------+----------+----------+----------+ 
| 28 | 20 | 202 | 22.4444 |
+----------+----------+----------+----------+ 

-- 上面数据中的列都是在查询时使用的函数名，不方便阅读和后期的调用，可以通过别名方式 美化
select max(age) as max_age, min(age) min_age,sum(age) as sum_age, avg(age) as avg_age from users;
+---------+---------+---------+---------+
| max_age | min_age | sum_age | avg_age | 
+---------+---------+---------+---------+ 
| 28 | 20 | 202 | 22.4444 | 
+---------+---------+---------+---------+ 

-- 统计 users 表中的数据量 
select count(*) from users;
+----------+ 
| count(*) | 
+----------+ 
| 9 |
+----------+ 
select count(id) from users;
+-----------+ 
| count(id) | 
+-----------+
| 9 | 
+-----------+ 

-- 上面的两个统计，分别使用了 count(*) 和 count(id),结果目前都一样，有什么区别？ -- count(*) 是按照 users表中所有的列进行数据的统计，只要其中一列上有数据，就可以计算 -- count(id) 是按照指定的 id 字段进行统计，也可以使用别的字段进行统计，
-- 但是注意，如果指定的列上出现了NULL值，那么为NULL的这个数据不会被统计
-- 假设有下面这样的一张表需要统计
+------+-----------+------+--------+-----------+------+------+ 
| id | name | age | phone | email | sex | mm | 
+------+-----------+------+--------+-----------+------+------+ 
| 1 | 章三 | 22 | | NULL | 男 | 0 |
| 2 | 李四 | 20 | | NULL | 女 | 0 | 
| 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 | 
| 1000 | aa | 20 | 123 | NULL | 女 | NULL |
| 1001 | bb | 20 | 123456 | NULL | 女 | NULL |
| 1002 | cc | 25 | 123 | NULL | 女 | NULL | 
| 1003 | dd | 20 | 456 | NULL | 女 | NULL |
| 1004 | ff | 28 | 789 | NULL | 男 | NULL | 
| 1005 | 王五六 | 23 | 890 | NULL | NULL | NULL |
+------+-----------+------+--------+-----------+------+------+ 9

-- 如果按照sex这一列进行统计，结果就是8个而不是9个，因为sex这一列中有NULL值存在
mysql> select count(sex) from users;
+------------+ 
| count(sex) | 
+------------+ 
| 8 |
+------------+

聚合函数除了以上简单的使用意外，通常情况下都是配合着分组进行数据的统计和计算

Group BY 分组

group by 语句根据一个或多个列对结果集进行分组

一般情况下，是用与数据的统计或计算，配合聚合函数使用

-- 统计 users 表中 男女生人数,
-- 很明显按照上面的需要，可以写出两个语句进行分别统计 
select count(*) from users where sex = '女'; 
select count(*) from users where sex = '男'; 

-- 可以使用分组进行统计，更方便 
select sex,count(*) from users group by sex; 
+------+----------+
| sex | count(*) |
+------+----------+ 
| 男 | 4 | 
| 女 | 5 | 
+------+----------+

-- 统计1班和2班的人数
select classid,count(*) from users group by classid;
+---------+----------+ 
| classid | count(*) |
+---------+----------+ 
| 1 | 5 |
| 2 | 4 |
+---------+----------+

-- 分别统计每个班级的男女生人数
select classid,sex,count(*) as num from users group by classid,sex;
+---------+------+-----+ 
| classid | sex | num | 
+---------+------+-----+
| 1 | 男 | 2 |
| 1 | 女 | 3 | 
| 2 | 男 | 2 | 
| 2 | 女 | 2 |
+---------+------+-----+ 

# 注意，在使用group by分组时，一般除了聚合函数，其它在select后面出现的字段列都需要出现在grouop by 后 面

Having子句

having时在分组聚合计算后，对结果再一次进行过滤，类似于where，

where过滤的是行数据，having过滤的是分组数据

-- 要统计班级人数
select classid,count(*) from users group by classid; 

-- 统计班级人数，并且要人数达到5人及以上 
select classid,count(*) as num from users group by classid having num >=5;

Order by排序

我们在mysql中使用select的语句查询的数据结果是根据数据在底层文件的结构来排序的，

首先不要依赖默认的排序，另外在需要排序时要使用orderby对返回的结果进行排序

Asc 升序，默认

desc降序

-- 按照年龄对结果进行排序，从大到小 
select * from users order by age desc;

-- 从小到大排序 asc 默认就是。可以不写 
select * from users order by age; 

-- 也可以按照多个字段进行排序 
select * from users order by age,id;

# 先按照age进行排序，age相同情况下，按照id进行排序 
select * from users order by age,id desc;

Limit数据分页

limit n 提取n条数据，

limit m,n 跳过m跳数据，提取n条数据

-- 查询users表中的数据，只要3条 
select * from users limit 3;

-- 跳过前4条数据，再取3条数据 
select * from users limit 4,3;

-- limit一般应用在数据分页上面 
-- 例如每页显示10条数据，第三页的 limit应该怎么写？ 
思考 
第一页 limit 0,10 
第二页 limit 10,10 
第三页 limit 20,10 
第四页 limit 30,10 

-- 提取 user表中 年龄最大的三个用户数据 怎么查询？ 
select * from users order by age desc limit 3;