MySQL数据库管理

原创

Qjc2002 2024-06-24 12:08:03 ©著作权

文章标签 mysql 数据库表名 文章分类 运维

©著作权归作者所有：来自51CTO博客作者Qjc2002的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、使用MySQL数据库

1.查看数据库结构

（1）查看当前服务器中的数据库

SHOW DATABASES 语句:用于査看当前 MySQL服务器中包含的数据库，MySQL 的每一条操作语句都是以分号(;)结束的。

mysql> show databases;

（2）查看当前数据库中有哪些表

SHOW TABLES 语句:用于查看当前所在的数据库中包含的表。在操作之前，需要先使用 USE 语句切换到所使用的数据库。例如，执行以下操作可以显示 mysql 数据库中包含的所有表。

mysql> use mysql
mysql> show tables;

（3）查看表的结构

DESCRIBE语句:用于显示表的结构，即组成表的各字段(列)的信息。需要指定“数据库名.表名”作为参数;若只指定表名参数，则需先通过 USE 语句切换到目标数据库。

mysql> use mysql
mysql> describe user;

SQL语言主要由以下几部分组成：

DDL(Data Definition Language，数据定义语言):用来建立数据库、数据库对象和定义字段，如 CREATE、ALTER、DROP。
DML(Data Manipulation Language，数据操纵语言):用来插入、删除和修改数据库中的数据，如INSERT、UPDATE、DELETE。
DQL(Data Query Language,数据査询语言):用来査询数据库中的数据,如 SELECT。
DCL(Data Control Language，数据控制语言):用来控制数据库组件的存取许可、存取权限等，如 COMMIT、ROLLBACK、GRANT、REVOKE。

2.创建及删除数据库和表

（1）创建新库

CREATE DATABASE 语句:用于创建一个新的数据库，需指定数据库名称作为参数。

mysql> create database auth;

（2）创建新表

CREATE TABLE 语句: 用于在当前数据库中创建新的表,需指定数据表名称作为参数,并定义该表格所使用的各字段，基本格式如下所示：

CREATE TABLE 表名(字段1名称类型,字段 2名称类型,…, PRIMARY KEY(主键名)

mysql> use auth;
mysql> CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));

备注：

DEFAULT ''密码默认为空

注意：

5.7以前的版本的password（）加密函数可以生成16位密文，5.7后的password（）函数可以生成41位的密文，其算法为mysql_native_password；设置密码字段的时候要考虑加密，提供的字符长度要够用。，否则在添加记录时，如果使用了password（）加密函数保存密码字段，会提示字符长度错误。8.0的mysql已经弃用password（）

（3）删除一个数据表

DROP TABLE 语句:用于删除数据库中的表，需要指定“数据库名.表名”作为参数;若只指定表名参数，则需先通过执行“USE"语句切换到目标数据库。

mysql> DROP TABLE auth.users;

（4）删除一个数据库

DROP DATABASE 语句:用于删除指定的数据库，需要指定数据库名作为参数。

mysql> DROP DATABASE auth;

3.管理表中的数据记录

（1）插入数据记录

INSERT INTO 语句:用于向表中插入新的数据记录，语句格式如下所示：

INSERT INTO 表名(字段 1,字段 2....)VALUES(字段1的值，字段2的值,..)；

mysql>INSERT INTO users(user_name,user_passwd) VALUES('zhangsan', PASSWORD ('123456'));
mysql>INSERT INTO users VALUES('lisi', PASSWORD('654321'));

（2）查询数据记录

SELECT语句:用于从指定的表中查找符合条件的数据记录。MySQL数据库支持标准的 SQL 查询语句，语句格式如下所示：

SELECT 字段名 1,字段名 2....FROM 表名 WHERE 条件表达式；

表示所有字段时，可以使用通配符“*”,若要显示所有的数据记录，则可以省略 WHERE条件子句。

mysql>select * from auth.users;
mysql>select user_name,user_passwd FROM auth.users WHERE user_name='zhangsan';

（3）修改数据记录

UPDATE 语句:用于修改、更新表中的数据记录。语句格式如下所示:

UPDATE 表名SET 字段名 1=字段值1L,字段名 2=字段值2]WHERE 条件表达式;

mysql>UPDATE auth.users SET user_passwd=PASSWORD('') WHERE user_name= 'lisi';
mysql>SELECT * FROM auth.users;

mysql>UPDATE mysql.user SET authentication_string=PASSWORD('123457') WHERE user='root';
mysql>FLUSH PRIVILEGES; /

[root@www ~]# mysqladmin -u root -p'123457' password '123456'

（4）删除数据记录

DELETE语句:用于删除表中指定的数据记录，语句格式如下所示:

DELETE FROM 表名 WHERE 条件表达式;

mysql>DELETE FROM auth.users WHERE user_name='lisi';
mysql>SELECT * FROM auth.users;

4.数据库表高级操作

（1）克隆表

mysql>create table user01 like auth.users; //通过LIKE 方法，复制 users 表生成tmp 表，注意，此表为空
mysql>insert into user01 select * from auth.users;

（2）删除表

mysql> delete from user01 where user_name='zhangsan';
mysql>delete from user01;
mysql>insert into user01 select * from auth.users;
mysql>truncate table user01;
mysql>select count(*) from user01;

备注：

truncate和delete的区别：

truncate删除表中的内容，不删除表结构，释放空间；

delete删除表中的内容，不删除表结构，但不释放空间

truncate删除数据后重新写数据会从1开始，

delete删除数据后只会从删除前的最后一行续写；内存空间上，truncate省空间

delete属于DML语句，而truncate和drop都属于DDL语句

delete可以在后续加上where进行针对行的删除

truncate和drop后面只能加上表名，直接删除表，无法where

drop table 表名删除表，内容连带结构一起删除；

（3）临时表

mysql>select * from mytmp; //查看mytmp 表是否存在

mysql>CREATE TEMPORARY TABLE `mytmp` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`NAME` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`level` int(10) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; //创建临时表
mysql> insert into mytmp(name,level) values('aa',10); //插入数据
Query OK, 1 row affected (0.01 sec)
mysql>select * from mytmp;
mysql>quit //退出当前连接
Bye
mysql>select * from mytmp; //重新连接MySQL 之后查看临时表状态

二、数据库用户授权

1.授予权限

GRANT语句:专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT 语句将会创建新的用户;当指定的用户名存在时，GRANT 语句用于修改用户信息。语句格式如下所示：

GRANT 权限列表 ON 数据库名.表名 TO 用户名@来源地址[IDENTIFIED BY'密码' ]；

mysql>GRANT select ON auth.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123456';

使用 GRANT 语句时，需要注意的事项如下。

权限列表:用于列出授权使用的各种数据库操作，以逗号进行分隔，如select，insert，update”。使用“all”表示所有权限，可授权执行任何操作。
数据库名.表名:用于指定授权操作的数据库和表的名称，其中可以使用通配符"”。例如，使用“auth.*”表示授权操作的对象为 auth 数据库中的所有表。
用户名@来源地址:用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%"通配符，表示某个区域或网段内的所有地址，如“%.bdgn.com"“192.168.1.%”等。
IDENTIFIED BY:用于设置用户连接数据库时所使用的密码字符串。在新建用户时,若省略“IDENTIFIED BY"部分，则用户的密码将为空。