MySQL_2.1(创建用户、CRUD操作)—超详细讲解

一、创建用户

我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

        1. 创建用户

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

例子：

‐‐ 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   '用户名'@'主机名';

例子：

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';
GRANT ALL ON *.* TO 'user2'@'%';

        3. 撤销授权

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

例子：

# 撤销user1用户对test操作的权限

REVOKE ALL ON test.* FROM 'user1'@'localhost';

        4. 查看权限

SHOW GRANTS FOR '用户名'@'主机名';

例子：

# 查看user1用户的权限

SHOW GRANTS FOR 'user1'@'localhost';

        5. 删除用户

DROP USER '用户名'@'主机名';

例子：

DROP USER 'user2'@'%';

二、修改密码

        1. 修改管理员密码

mysqladmin  -uroot  -p  password  新密码

-- 新密码不需要加上引号

-- 注意：需要在未登陆MySQL的情况下操作。

例子：

mysqladmin ‐uroot ‐p password 123456
输入老密码

        2. 修改普通用户密码

set password for '用户名'@'主机名' = '新密码';

-- 注意：需要在登陆MySQL的情况下操作。

set password for 'user1'@'localhost' = '666666';

以上SQL语句中关键字的解释：

• 用户名：将创建的用户名

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

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

• GRANT 授权关键字

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

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

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

1. 给user1用户分配对test这个数据库操作的权限

2. 给user2用户分配对所有数据库操作的权限

三、关于MySQL中SQL语句的字段类型常见的有以下：

数据类型名称	描述
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类型。

        2. 查看表结构

-- 查看表结构
    DESCRIBE dept;
    DESC dept;

练习：

# 创建一张user表, 可以存储以下内容：姓名，性别，年龄，生日，电话，家庭住址，邮箱

    CREATE TABLE `user`(
        `name` VARCHAR(20) COMMENT '姓名',
        `gender` CHAR(1) COMMENT '性别',
        `age` INT COMMENT '年龄',
        `birthday` DATE COMMENT '生日',
        `phone` VARCHAR(20) COMMENT '电话',
        `address` VARCHAR(50) COMMENT '住址',
        `email` VARCHAR(20) COMMENT '邮箱'
    );

        3. 创建和某表结构一样的表

-- 创建和dept结构一样的表
CREATE TABLE d LIKE dept;
​
-- 创建表
CREATE TABLE t AS select * from dept;

        4. 删除表

DROP TABLE table_name

        5. 添加列

ALTER TABLE d ADD id INT;

        6. 修改列属性

ALTER TABLE d MODIFY id VARCHAR(20);

        7. 修改列名

ALTER TABLE d CHANGE id ss VARCHAR(20); 

        8. 删除列

ALTER TABLE d DROP ss;

        9. 重命名表

RENAME TABLE d TO dd;

        10. Modify

MySQL中的MODIFY关键字用于修改表的结构，包括列的数据类型、长度、约束等。

语法：

ALTER TABLE table_name MODIFY COLUMN column_name column_type [FIRST|AFTER column_name];

其中，table_name是要修改的表名，column_name是要修改的列名，column_type是新的数据类型或约束条件。可选参数FIRST或AFTER column_name表示将该列移动到指定列之前或之后。

示例： 假设我们有一个名为students的表，包含以下列：id, name, age, gender, email。要将email列的数据类型从VARCHAR(50)改为VARCHAR(100),可以使用以下查询：

ALTER TABLE students MODIFY COLUMN email VARCHAR(100);

如果要将age列的数据类型从INT改为BIGINT,并添加一个NOT NULL约束，可以使用以下查询：

ALTER TABLE students MODIFY COLUMN age BIGINT NOT NULL;

四、CRUD操作

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

        1. 添加数据

INSERT INTO table_name VALUES(值列表)

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

-- 不推荐使用
INSERT INTO dept VALUE(1,'研发部');
-- 2
INSERT INTO dept VALUES(2,'销售部');
INSERT INTO dept VALUES(3,'行政部'),(4,'技术部');
-- 3
INSERT INTO dept(deptno,deptname)VALUES(5,'安保部');

区别：

• value和values的区别，values可以同时插入多条数据用逗号隔开；

• dept和dept(列名，列名。。。)区别，如果不写列表必须按照列表创建时的顺序每一列都要添加

• 有列名的按照列名排列顺序添加

        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='研发部';
UPDATE dept SET deptno=2 WHERE deptname='销售部';
UPDATE dept SET deptno=3 WHERE deptname='行政部';
UPDATE dept SET deptno=4 WHERE deptname='技术部';

        4. 删除数据

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

区别：

• delete from dd;

• truncate table dd;

  • delete 是清空表中的数据

  • truncate 是清空表数据，重新创建一个一样表

        5. where条件连接

当sql语句中的条件有多条时，可以将多个条件连接起来。他们之间的关系有一下几种：

and：

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

or：

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

in：

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

like：

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

后续还会继续更新此类理论题型，大家有什么需要，还望留言告知！！！