大家好，欢迎来到IT知识分享网。
数据库
常见的数据库
当前主流的数据库系统包括关系型数据库管理系统（RDBMS）和一些主要的NoSQL数据库。以下是几个主流的数据库系统：
关系型数据库管理系统 (RDBMS)
NoSQL非关系型数据库
ubuntu2404安装mariadb数据库
apt install -y mariadb-server systemctl enable --now mariadb 查看当前用户及其权限
查看用户语法
SELECT user, host FROM mysql.user; 查看用户权限语法
SHOW GRANTS FOR 'username'@'host'; MariaDB [(none)]> SELECT user, host FROM mysql.user; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+ 4 rows in set (0.001 sec) MariaDB [(none)]> SHOW GRANTS FOR 'mysql'@'localhost'; +------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for mysql@localhost | +------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `mysql`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'mysql'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) 创建用户
语法格式
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; MariaDB [(none)]> CREATE USER 'huhy'@'localhost' IDENTIFIED BY '000000'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SELECT user, host FROM mysql.user; +-------------+-----------+ | User | Host | +-------------+-----------+ | huhy | localhost | | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+ 4 rows in set (0.001 sec) 授权用户访问数据库
语法格式
GRANT privileges ON database_name.table_name TO 'username'@'host'; 例：
GRANT SELECT, INSERT ON database1.* TO 'user1'@'localhost'; GRANT ALL PRIVILEGES ON database2.* TO 'user1'@'%'; GRANT DELETE ON database1.table1 TO 'user1'@'192.168.1.100'; 赋予huhy用户所有权限
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'huhy'@'localhost'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SHOW GRANTS FOR 'huhy'@'localhost'; +----------------------------------------------------------------------------------------------------------------------+ | Grants for huhy@localhost | +----------------------------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO `huhy`@`localhost` IDENTIFIED BY PASSWORD '*032197AE5731DA6CCAC7CFCE6A0' | +----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) 撤销用户权限
语法格式
REVOKE privileges ON database_name.table_name FROM 'username'@'host'; 撤销huhy所有权限
MariaDB [(none)]> REVOKE ALL PRIVILEGES ON *.* FROM 'huhy'@'localhost'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SHOW GRANTS FOR 'huhy'@'localhost'; +-------------------------------------------------------------------------------------------------------------+ | Grants for huhy@localhost | +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `huhy`@`localhost` IDENTIFIED BY PASSWORD '*032197AE5731DA6CCAC7CFCE6A0' | +-------------------------------------------------------------------------------------------------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> 如果只需要撤销单个权限；可按照如下
REVOKE SELECT, INSERT ON *.* FROM 'huhy'@'localhost'; 修改用户密码
语法格式
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password'; 修改用户密码为
MariaDB [(none)]> ALTER USER 'huhy'@'localhost' IDENTIFIED BY ''; Query OK, 0 rows affected (0.001 sec) 删除用户
语法格式
DROP USER 'username'@'host'; 删除huhy
MariaDB [(none)]> drop user 'huhy'@'localhost'; Query OK, 0 rows affected (0.001 sec) MariaDB [(none)]> SELECT user, host FROM mysql.user; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | +-------------+-----------+ 3 rows in set (0.001 sec) MariaDB [(none)]> 以下操作使用mysql数据库
增
创建一个数据库
mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | studentdb | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> 创建完后可以用show databases;查看所有数据库，注意databases是复数形式的
创建表
创建表时注意要指定在那个数据库下创建表，用use来选择
primary key表示该字段不为空且值不能重复
mysql> use test; Database changed mysql> create table info(id int primary key,name varchar(255),age varchar(100)); Query OK, 0 rows affected (0.00 sec) mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> 表中插入数据
mysql> insert into info(id,name,age) values ("1","张三","19"); Query OK, 1 row affected (0.00 sec) mysql> select * from info; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 张三 | 19 | +----+--------+------+ 1 row in set (0.00 sec) mysql> 这里先提前学习一个查询所有信息命令select * from info
表中添加字段（三种方式）
mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table info add sex varchar(50); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table info add num int(10) first; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | num | int(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 after 关键字
mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | num | int(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> alter table info add sno varchar(50) after name; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | num | int(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> 删
删除表记录
注；如果不加上where条件语句的话，就会把整张表给删除了
mysql> delete from info where id = 1; Query OK, 1 row affected (0.00 sec) mysql> select * from info; Empty set (0.00 sec) mysql> 删除表字段
mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table info drop column age; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> 删除表（三种方式）
删除内容和定义，删除的是整个表（结构和数据），将表所占用的空间全释放掉。无法回滚，所以删除是不能恢复的，如果再次使用的话需要新建表
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | info | +----------------+ 1 row in set (0.00 sec) mysql> select * from info; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 张三 | 19 | +----+--------+------+ 1 row in set (0.00 sec) mysql> drop table info; Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.00 sec) mysql> 只是清空表，删除内容，释放空间，但不删除定义（保留表的数据结构）。且不会把删除操作记录记入日志保存，无法回滚，所以删除是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | info | +----------------+ 1 row in set (0.00 sec) mysql> select * from info; +----+--------+------+ | id | name | age | +----+--------+------+ | 1 | 张三 | 19 | +----+--------+------+ 1 row in set (0.00 sec) mysql> truncate table info; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | info | +----------------+ 1 row in set (0.00 sec) mysql> select * from info; Empty set (0.00 sec) mysql> 删除表中的行，不删除表的结构。执行删除的过程是每次从表中删除一行，并且将该行的删除操作作为事务在日志中保存，以便进行进行回滚操作。delete会根据指定的条件删除表中满足条件的数据，where就是条件判断。如果不指定where子句，那么删除表中所有记录。delete操作不会减少表或索引所占用的空间,不推荐此方法删除表
删除数据库
数据库删除之后，原来分配的空间将被收回。需要注意的是，数据库删除之后该数据库中所有的表和数据都将被删除。因此删除数据库要特别小心
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | studentdb | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> drop database test; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | studentdb | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> 改
修改表名
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | info | +----------------+ 1 row in set (0.00 sec) mysql> alter table info rename new_info; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | new_info | +----------------+ 1 row in set (0.00 sec) mysql> 修改表数据
注意判断的条件是否锁定为修改修改的字段
mysql> select * from info; +------+----+--------+------+------+------+ | num | id | name | sno | age | sex | +------+----+--------+------+------+------+ | NULL | 1 | 张三 | NULL | 19 | NULL | +------+----+--------+------+------+------+ 1 row in set (0.00 sec) mysql> update info set sex = "男" where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from info; +------+----+--------+------+------+------+ | num | id | name | sno | age | sex | +------+----+--------+------+------+------+ | NULL | 1 | 张三 | NULL | 19 | 男 | +------+----+--------+------+------+------+ 1 row in set (0.00 sec) mysql> 修改表字段名
注；此方式可以修改字段名字的同时也可以修改字段数据类型，也可以指定为原来的字段类型
mysql> desc info; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | num | int(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | name | varchar(255) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table info change name new_name varchar(100); Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | num | int(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> 修改表字段数据类型
mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | num | int(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(100) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table info modify age varchar(10); Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | num | int(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> 修改字段排列段顺序
mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | num | int(10) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table info modify id int(11) first; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | num | int(10) | YES | | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | num | int(10) | YES | | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table info modify num int(10) after sex; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | | num | int(10) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> 拓展；如果字段的数据类型写错了也是会被修改的，并且还是会排序在指定字段的后面
mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | | num | int(10) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> alter table info modify num int(100) after sex; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc info; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | new_name | varchar(100) | YES | | NULL | | | sno | varchar(50) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | sex | varchar(50) | YES | | NULL | | | num | int(100) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 查
通配符和条件表达式
可参考菜鸟教程
order by排序
mysql> select * from student; +-----------+-----------+------+------+-------+------+ | sno | sname | ssex | sage | sdept | sloc | +-----------+-----------+------+------+-------+------+ | | 李勇 | 男 | 22 | cs | NULL | | | 刘晨 | 女 | 20 | cs | NULL | | | 王敏 | 女 | 18 | ma | NULL | | | 张月琳 | 女 | 20 | cs | NULL | | | 张立 | 男 | 19 | is | NULL | | | 李晚 | 男 | 21 | is | NULL | | | 林方成 | 男 | 19 | cs | NULL | | | 赵立何 | 男 | 21 | ma | NULL | | | 赵城 | 男 | 20 | ma | NULL | | | 张浩 | 男 | 20 | is | NULL | | | 王信韵 | 女 | 19 | cs | NULL | | | 孙思 | 女 | 21 | ma | NULL | | | 陈信 | 女 | 22 | cs | NULL | +-----------+-----------+------+------+-------+------+ 13 rows in set (0.00 sec) mysql> select sno,sname from student order by sno desc; +-----------+-----------+ | sno | sname | +-----------+-----------+ | | 陈信 | | | 孙思 | | | 王信韵 | | | 张浩 | | | 赵城 | | | 赵立何 | | | 林方成 | | | 李晚 | | | 张立 | | | 张月琳 | | | 王敏 | | | 刘晨 | | | 李勇 | +-----------+-----------+ 13 rows in set (0.00 sec) mysql> mysql> select * from student; +-----------+-----------+------+------+-------+------+ | sno | sname | ssex | sage | sdept | sloc | +-----------+-----------+------+------+-------+------+ | | 李勇 | 男 | 22 | cs | NULL | | | 刘晨 | 女 | 20 | cs | NULL | | | 王敏 | 女 | 18 | ma | NULL | | | 张月琳 | 女 | 20 | cs | NULL | | | 张立 | 男 | 19 | is | NULL | | | 李晚 | 男 | 21 | is | NULL | | | 林方成 | 男 | 19 | cs | NULL | | | 赵立何 | 男 | 21 | ma | NULL | | | 赵城 | 男 | 20 | ma | NULL | | | 张浩 | 男 | 20 | is | NULL | | | 王信韵 | 女 | 19 | cs | NULL | | | 孙思 | 女 | 21 | ma | NULL | | | 陈信 | 女 | 22 | cs | NULL | +-----------+-----------+------+------+-------+------+ 13 rows in set (0.00 sec) mysql> select sno,sname from student order by sno asc; +-----------+-----------+ | sno | sname | +-----------+-----------+ | | 李勇 | | | 刘晨 | | | 王敏 | | | 张月琳 | | | 张立 | | | 李晚 | | | 林方成 | | | 赵立何 | | | 赵城 | | | 张浩 | | | 王信韵 | | | 孙思 | | | 陈信 | +-----------+-----------+ 13 rows in set (0.00 sec) mysql> 免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://haidsoft.com/117994.html
