Mysql数据库的基本操作大全
1. 登录和连接数据库
格式: mysql -h 主机地址 -u 用户名 -p 用户密码
连接本地库
如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了
mysql -u root -p
连接远程库
连接到远程主机上的MYSQL. 假设远程主机的IP为:192.168.110.110, 用户名为root, 密码为123。
mysql -h192.168.110.110 -u root -p 123;
(注:u与root之间可以不用加空格,其它也一样)
退出mysql
exit
2. 库和表的基本操作
2.1 库基本操作
2.1.1 查库
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
2.1.2 开库
use + 数据库名
use mysql;
2.1.3 建库
create database 库名 default charset=utf8;
create database test default charset=utf8;
-- Query OK, 1 row affected (0.01 sec)
-- 查看所有库
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
-- 进入库
use test;
2.1.4 删库
删库有风险,动手需谨慎
# 删除库,那么库中的所有数据都将在磁盘中删除。
drop database 库名
2.2 表的基本操作
2.2.1 查表
show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
2.2.2 建表
create table 表名( 字段名 类型 字段约束,
字段名 类型 字段约束,
字段名 类型 字段约束
)engine=innodb default charset=utf8;
# 以下创建一个 users 的表
create table users(
-- 创建ID字段,为正整数,不允许为空 主键,自动递增
id int unsigned not null primary key auto_increment,
-- 创建 存储 名字的字段,为字符串类型,最大长度 5个字符,不允许为空
username varchar(5) not null,
-- 创建存储 密码 的字段,固定长度 32位字符, 不允许为空
password char(32) not null,
-- 创建 年龄 字段,不允许为空,默认值为 20
age tinyint not null default 20
)engine=innodb default charset=utf8;
# 查看表结构
desc users;
#查看建表语句
show create table users;
创建表的基本原则:
表明和字段名 尽可能的符合命名规范,并且最好能够‘见名之意’
表中数据必须有唯一标示,即主键定义。无特殊情况,主键都为数字并自增即可
表中字段所对应的类型设置合理,并限制合理长度
表引擎推荐使用innodb,并无特殊情况都要求为utf8或者utf8mb4的字符编码
2.2.3 查看表结构
desc 表名
desc users;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(5) | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| age | tinyint | NO | | 20 | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
2.2.4 查看建表语句
show create table 表名;
show create table users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(5) NOT NULL,
`password` char(32) NOT NULL,
`age` tinyint NOT NULL DEFAULT '20',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.2.5 修改表结构
语法格式:alter table 表名 action (更改的选项)
添加字段:
# 语法:alter table 表名 add 添加的字段信息
-- 在 users 表中 追加 一个 num 字段
alter table users add num int not null;
-- 在指定字段后面追加字段 在 users 表中 age字段后面 添加一个 email 字段
alter table users add email varchar(50) after age;
-- 在指定字段后面追加字段,在 users 表中 age字段后面 添加一个 phone
alter table users add phone char(11) not null after age;
-- 在表的最前面添加一个字段
alter table users add aa int first;
删除字段
# 删除字段 alter table 表名 drop 被删除的字段名
alter table users drop aa;
修改字段
语法格式: alter table 表名 change|modify 被修改的字段信息
change: 可以修改字段名,
modify: 不能修改字段名。
# 修改表中的 num 字段 类型,使用 modify 不修改表名
alter table users modify num tinyint not null default 12;
# 修改表中的 num 字段 为 int并且字段名为 nn
alter table users change num mm int;
# 注意:一般情况下,无特殊要求,不要轻易修改表结构
2.2.6 改表名
语法:alter table 原表名 rename as 新表名
2.2.7 改表自增值
# 在常规情况下,auto_increment 默认从1开始继续递增
alter table users auto_increment = 1000;
2.2.8 改表引擎
# 推荐在定义表时,表引擎为 innodb。
# 通过查看建表语句获取当前的表引擎
mysql> show create table users\G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 直接查看当前表状态信息
mysql> show table status from test where name = 'users'\G;
*************************** 1. row ***************************
Name: users
Engine: InnoDB
# 修改表引擎语句
alter table users engine = 'myisam';
2.2.9 删表
drop table 表名;
3. 表的字段类型和约束
3.1 字段类型
3.1.1 字符串数据类
char 定长串
- 接受长度固定的字符串,其长度是在创建表时指定的。
定长列不允许存储多于指定长度字符的数据。 - 指定长度后,就会分配固定的存储空间用于存放数据
char(7) 不管实际插入多少字符,它都会占用7个字符位置
varchar 变长串
存储可变长度的字符串 varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7个字符
串数据类型表
既然变长数据类型这样灵活,为什么还要使用定长数据类型?
因为性能,MySQL处理定长列远比处理变长列快得多。
3.1.2 数值类型
有符号或无符号
所有数值数据类型(除BIT和BOOLEAN外)都可以有符号或无符号
有符号数值列可以存储正或负的数值
无符号数值列只能存储正数。
默认情况为有符号,但如果你知道自己不需要存储负值,可以使用UNSIGNED关键字
3.1.3 日期类型
MySQL使用专门的数据类型来存储日期和时间值
datetime 8字节1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
3.1.4 二进制类型
二进制数据类型可存储任何数据(甚至包括二进制信息),如图像、多媒体、字处理文档等
3.2. 表的字段约束
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 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度
4. DML和DQL(数据的操作和查询)
4.1 DML-MySQL 数据操作
数据的DML操作:添加数据,修改数据,删除数据
4.1.1添加数据
格式: insert into 表名[(字段列表)] values(值列表…);
--标准添加(指定所有字段,给定所有的值)
mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lamp138');
Query OK, 1 row affected (0.13 sec)
mysql>
--指定部分字段添加值
mysql> insert into stu(name,classid) value('lisi','lamp138');
Query OK, 1 row affected (0.11 sec)
-- 不指定字段添加值
mysql> insert into stu value(null,'wangwu',21,'w','lamp138');
Query OK, 1 row affected (0.22 sec)
-- 批量添加值
mysql> insert into stu values
-> (null,'zhaoliu',25,'w','lamp94'),
-> (null,'uu01',26,'m','lamp94'),
-> (null,'uu02',28,'w','lamp92'),
-> (null,'qq02',24,'m','lamp92'),
-> (null,'uu03',32,'m','lamp138'),
-> (null,'qq03',23,'w','lamp94'),
-> (null,'aa',19,'m','lamp138');
Query OK, 7 rows affected (0.27 sec)
Records: 7 Duplicates: 0 Warnings: 0
4.1.2修改数据
格式:update 表名 set 字段1=值1,字段2=值2,字段n=值n… where 条件
-- 将id为11的age改为35,sex改为m值
mysql> update stu set age=35,sex='m' where id=11;
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 将id值为12和14的数据值sex改为m,classid改为lamp92
mysql> update stu set sex='m',classid='lamp92' where id=12 or id=14 --等价于下面
mysql> update stu set sex='m',classid='lamp92' where id in(12,14);
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0
4.1.3 删除数据
格式:delete from 表名 [where 条件]
-- 删除stu表中id值为100的数据
mysql> delete from stu where id=100;
Query OK, 0 rows affected (0.00 sec)
-- 删除stu表中id值为20到30的数据
mysql> delete from stu where id>=20 and id<=30;
Query OK, 0 rows affected (0.00 sec)
-- 删除stu表中id值为20到30的数据(等级于上面写法)
mysql> delete from stu where id between 20 and 30;
Query OK, 0 rows affected (0.00 sec)
-- 删除stu表中id值大于200的数据
mysql> delete from stu where id>200;
Query OK, 0 rows affected (0.00 sec)
4.2 DQL-MySQL数据查询SQL
语法格式:
select 字段列表/* from 表名
[where 搜索条件]
[group by 分组字段 [having 分组条件]]
[order by 排序字段 排序规则]
[limit 分页参数]
4.2.1 基础查询
# 查询表中所有列 所有数据
select * from users;
# 查询指定字段
select id,name,phone from users;
4.2.2 条件查询
可以在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 = '女';
注意: sql会优先处理and条件,在某些情况下需要同时用到and和or时使用小括号来关联相同的条件。
4.2.3 模糊查询
可以使用like语句进行某个字段的模糊搜索,
例如: 查询 name字段中包含五的数据
-- like 语句 like某个确定的值 和。where name = '王五' 是一样
select * from users where name like '王五';
+----+--------+------+-------+-----------+------+------+
| id | name | age | phone | email | sex | mm |
+----+--------+------+-------+-----------+------+------+
| 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 |
+----+--------+------+-------+-----------+------+------+
1 row in set (0.00 sec)
-- 使用 % 模糊搜索。%代表任意个任意字符
-- 查询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 '_五';
4.2.4 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 rows in set (0.00 sec)
-- 如果按照sex这一列进行统计,结果就是8个而不是9个,因为sex这一列中有NULL值存在
mysql> select count(sex) from users;
+------------+
| count(sex) |
+------------+
| 8 |
+------------+
4.2.5 分组
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;
4.2.6 排序
我们在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;
4.2.7 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;
5. 多表查询
具体情况具体分析灵活使用表连接子查询等数据库进阶技术