数据库（2）_以user2身份登录,执行相应的查询语句,插入和更新语句。-CSDN博客

本文链接：https://blog.csdn.net/m0_57229804/article/details/131885812

一、基础语法

二、CRUD操作

一、基础语法

1.创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

用户名：将创建的用户名

主机名：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%

密码：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

例子：

‐‐ user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@'localhost' IDENTIFIED BY '1234';
‐‐ user2用户可以在任何电脑上登录mysql服务器
CREATE USER 'user2'@'%' IDENTIFIED BY '1234';

2.授权

GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

授予用户的权限，如SELECT，INSERT，UPDATE等。如果要授予所有的权限则使用ALL

数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*

'用户名'@'主机名': 给哪个用户授权

撤销授权：

REVOKE 权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';

2.数据表

创建表：

CREATE TABLE t AS select * from dept;

删除表：

DROP TABLE table_name

增加列：

ALTER TABLE d ADD id INT;

修改列属性：

ALTER TABLE d MODIFY id VARCHAR(20);

修改列名：

ALTER TABLE d CHANGE id ss VARCHAR(20);

删除列：

ALTER TABLE d DROP ss;

重命名表：

RENAME TABLE d TO dd;

二、CRUD操作

对数据表中的数据操作通常有添加(Create)、查询(Retrieve)、修改(Update)、删除(Delete)、简称为CRUD。

1.添加数据

INSERT INTO table_name VALUES(值列表)

INSERT INTO table_name (列列表) VALUES(值列表）

2.查询数据

-- 查询所有数据
SELECT *FROM dept;
-- 查询某列的数据
SELECT deptname FROM dept;
-- 根据条件查询*
SELECT deptno FROM dept WHERE deptname='销售部';

3.修改数据

--全部修改为6
UPDATE dept SET deptno=6;
--根据条件修改
UPDATE dept SET deptno=1 WHERE deptname='研发部';

4.删除数据

-- 删除数据 ,一定要加 where 条件
DELETE FROM dept WHERE deptno=5;
-- 全部删除
DELETE FROM dept;
-- 清空/截断所有数据(慎用)
TRUNCATE TABLE dept;

delete 是清空表中的数据

truncate 是清空表数据（删除表后重新创建一个一样表）

三、where后条件链接

1.and：

a and b: 表示需要同时满足 a 条件和 b 条件

2.or

a or b: 表示满足 a 条件或 b 条件都可以

3.in

in(a, ... ,b)：表示在 a 及 b 这些值中都可以

4.like

模糊查询, % 表示任意个字符 _ 表示一个字符

四、数据还原和备份

1.备份结构

mysqldump -u root -p -d dbname table1 table2 ... > a.sql

备份所有表：

mysqldump -u root -p -d dbname > b.sql

备份多个数据库的表：

mysqldump -u root -p -d --databases db1 db2... > c.sql

备份所有数据库的表

mysqldump -u root -p -d --all-databases > d.sql

2.备份结构和数据

mysqldump -u root -p dbname table1 table2 ... > a.sql

mysqldump -u root -p dbname > b.sql

mysqldump -u root -p --databases db1 db2 > c.sql

mysqldump -u root -p --all-databases > d.sql

3.还原表结构和数据

mysql -u root -p [dbname] < 目标文件
mysql -h127.0.0.1 -uroot -proot db_name<back.sql

load data infile '/tmp/stud.txt' into table students;
source /backup/all_db_2013-09-08.sql

五.高级查询

1.distinct

在select语句中，可以使用distinct关键字对查询的结果集进行去重。

select distinct 列1, ... , 列n from table_name [其他子句];

2.order by

用于对结果进行排序显示，可以使用ASC / DESC两种方式进行排序，可以有多个排序条件

ASC：表示升序排序，如果不写即为此排序方式

DESC：表示降序排序

select [distinct] 列1, ... , 列n from table_name [其他子句] order by 排序列1 [DESC], 排序列2 [DESC];

3.分页查询limit

select * from emp limit 0,2;

公式：n(n-1)s

4.group by

group by 是对数据进行分组，分组时，表中有相同值的分为一组。分组后可以进行聚合查询。

group by分组后的查询中，select的列不能出现除了group by分组条件以及聚合函数外的其他列。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2;

5.having

having是对group by分组后的结果集进行筛选。

select 列1, 列2, (聚合函数) from table_name group by 列1, 列2 having 分组后条件;

六.聚合函数

Mysql中内置了 5 种聚合函数，分别是：SUM 、max、min、avg、count。

1.sum求和

select sum(列) from table_name [其他子句];

2.max最大值

select max(列) from table_name [其他子句];

3.min最小值

select min(列) from table_name [其他子句];

4.avg平均值

select avg(列) from table_name [其他子句];

5.count求数量

select count(列) from table_name [其他子句];

七、数据类型

数据类型名称描述
SMALLINT 小的整数，带符号的范围是-32768到32767.无符号的范围是0到65535
MEDIUMINT 中等大小整数-8388608到8388607，0到16777215
INT/INTEGER 普通大小的整数，-2147483648到2,147,483,647，0到4294967295
BIGINT 大整数，-9223372036854775808到9223372036854775807，0到18446744073709551615
FLOAT 小(单精度)浮点数，允许的值-3.402823466E+38到-1.175494351E-38,0和1.175494351E-38到3.402823466E+38,这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些
DOUBLE 普通大小(双精度)浮点数，允许的值-1.7976931348623157E+380到-2.2250738585072014E-308,0和2.2250738585072014E-38到 1.7976931348623157E+308.这些事理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些
DATE 日期，支持的范围为‘1000-01-01’到‘9999-12-31’，MySQL以'YYYY-MM-DD'格式显示DATE值，但允许使用字符串或数字为DATE列分配值
DATETIME 日期和时间的组合。支持的范围是‘上面加上00：00：00’到‘上面第二个加上23：59：59’.MySQL以YYYY-MM-DD HH:MM:SS“格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值
TIMESTAMP 时间戳，范围是'1970-01-01 00:00:00'到2037年
TIME 时间，范围是‘-838：59：59’到‘838：59：59’.MySQL以‘HH:MM:SS’格式显示TIME值，但允许使用字符串或数字为TIME列分配值
YEAR 两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000.在两位格式中，允许的值是70到69，表示从1970到2069年。MySQL以yyyy格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值
CHAR(M) 固定长度字符串，当保存时在右侧填充空格以达到指定长度。M表示列长度。M的范围是0到255个字符
VARCHAR(M) 变长字符串。M表示最大列长度。M的范围是0到65535.（VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定。最大有效长度是65355字节）
BLOB[(M)] 最大长度为65535(216-1)字节，=的BLOB列，可以给出该类型的可选长度M。如果给出，则MySQL将列创建为最小的但是足以容纳M字节长度的值的BLOB类型
TEXT[(M)] 长字符串，最大长度为65535(216-1)字符的TEXT列。可以给出可选长度M。则MySQL将列创建为最小的但是足以容纳M字符长度的值的TEXT类型。

数据类型名称	描述
SMALLINT	小的整数，带符号的范围是-32768到32767.无符号的范围是0到65535
MEDIUMINT	中等大小整数-8388608到8388607，0到16777215
INT/INTEGER	普通大小的整数，-2147483648到2,147,483,647，0到4294967295
BIGINT	大整数，-9223372036854775808到9223372036854775807，0到18446744073709551615
FLOAT	小(单精度)浮点数，允许的值-3.402823466E+38到-1.175494351E-38,0和1.175494351E-38到3.402823466E+38,这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些
DOUBLE	普通大小(双精度)浮点数，允许的值-1.7976931348623157E+380到-2.2250738585072014E-308,0和2.2250738585072014E-38到 1.7976931348623157E+308.这些事理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些
DATE	日期，支持的范围为‘1000-01-01’到‘9999-12-31’，MySQL以'YYYY-MM-DD'格式显示DATE值，但允许使用字符串或数字为DATE列分配值
DATETIME	日期和时间的组合。支持的范围是‘上面加上00：00：00’到‘上面第二个加上23：59：59’.MySQL以YYYY-MM-DD HH:MM:SS“格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值
TIMESTAMP	时间戳，范围是'1970-01-01 00:00:00'到2037年
TIME	时间，范围是‘-838：59：59’到‘838：59：59’.MySQL以‘HH:MM:SS’格式显示TIME值，但允许使用字符串或数字为TIME列分配值
YEAR	两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000.在两位格式中，允许的值是70到69，表示从1970到2069年。MySQL以yyyy格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值
CHAR(M)	固定长度字符串，当保存时在右侧填充空格以达到指定长度。M表示列长度。M的范围是0到255个字符
VARCHAR(M)	变长字符串。M表示最大列长度。M的范围是0到65535.（VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定。最大有效长度是65355字节）
BLOB[(M)]	最大长度为65535(216-1)字节，=的BLOB列，可以给出该类型的可选长度M。如果给出，则MySQL将列创建为最小的但是足以容纳M字节长度的值的BLOB类型
TEXT[(M)]	长字符串，最大长度为65535(216-1)字符的TEXT列。可以给出可选长度M。则MySQL将列创建为最小的但是足以容纳M字符长度的值的TEXT类型。