数据库配置
2021/4/30 2:25:41
本文主要是介绍数据库配置,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
//创建数据库 //语法:CREATE DATABASE [IF NOT EXISTS] 'DB_NAME'; [root@localhost ~]# mysql -uroot -phuangfang123! mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.34 MySQL Community Server (GPL) Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE DATABASE IF NOT EXISTS teng; //创建teng数据库 Query OK, 1 row affected (0.00 sec) mysql> SHOW DATABASES; //查看数据库 +--------------------+ | Database | +--------------------+ | information_schema | | huang | | mysql | | performance_schema | | sys | | teng | +--------------------+ 6 rows in set (0.01 sec) mysql> DROP DATABASE IF EXISTS huang //删除huang数据库 -> ; Query OK, 1 row affected (0.01 sec) mysql> SHOW DATABASES //查看数据库 -> ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | teng | +--------------------+ 5 rows in set (0.01 sec)
表操作
//创建表 //语法:CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE='存储引擎类型'; mysql> use teng; //进入teng数据库 Database changed mysql> CREATE TABLE jia(id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint); //创建jia表 Query OK, 0 rows affected (0.01 sec) //查看当前数据库有哪些表 mysql> SHOW TABLES; +----------------+ | Tables_in_teng | +----------------+ | jia | +----------------+ 1 row in set (0.00 sec) //删除表 //语法:DROP TABLE [ IF EXISTS ] 'table_name'; mysql> DROP TABLE jia //删除jia表 -> ; Query OK, 0 rows affected (0.00 sec) mysql> SHOW TABLES; Empty set (0.00 sec)
用户操作
mysql用户帐号由两部分组成,如’USERNAME’@‘HOST’,表示此USERNAME只能从此HOST上远程登录
这里(‘USERNAME’@‘HOST’)的HOST用于限制此用户可通过哪些主机远程连接mysql程序,其值可为:
IP地址,如:172.16.12.129
通配符
%:匹配任意长度的任意字符,常用于设置允许从任何主机登录
_:匹配任意单个字符
//数据库用户创建 //语法:CREATE USER 'username'@'host' [IDENTIFIED BY 'password']; mysql> CREATE USER 'teng'@'192.168.11.135' IDENTIFIED BY 'P@ssword123!'; //创建用户teng Query OK, 0 rows affected (0.00 sec) //删除数据库用户 mysql> DROP USER 'teng'@'192.168.11.135'; Query OK, 0 rows affected (0.01 sec)
查看命令SHOW
mysql> show character set //查看支持的所有字符集 -> ; +----------+---------------------------------+---------------------+--------+ | Charset | Description | Default collation | Maxlen | +----------+---------------------------------+---------------------+--------+ | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish | swe7_swedish_ci | 1 | | ascii | US ASCII | ascii_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | | cp1250 | Windows Central European | cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866 | DOS Russian | cp866_general_ci | 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce | Mac Central European | macce_general_ci | 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852 | DOS Central European | cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic | cp1251_general_ci | 1 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | | gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 | +----------+---------------------------------+---------------------+--------+ 41 rows in set (0.00 sec) mysql> show engines; //查看当前数据库支持的所有存储引擎 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) //查看数据库信息 mysql> show databases -> ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) //不进入某数据库而列出其包含的所有表 mysql> SHOW TABLES FROM huang; +-----------------+ | Tables_in_huang | +-----------------+ | student | +-----------------+ 1 row in set (0.00 sec) //查看表结构 //语法:DESC [db_name.]table_name; mysql> desc huang.student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) //查看某表的创建命令 //语法:SHOW CREATE TABLE table_name; mysql> SHOW CREATE TABLE huang.student; +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student | CREATE TABLE `student` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `age` tinyint(4) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 | +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) //查看某表的状态 //语法:SHOW TABLE STATUS LIKE 'table_name'\G mysql> use huang Database changed mysql> SHOW TABLE STATUS LIKE 'huang'\G Empty set (0.00 sec)
获取帮助
mysql> HELP CREATE TABLE; Name: 'CREATE TABLE' Description: Syntax: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE | REPLACE]
DML操作
DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT),均属针对表的操作。
INSERT语句
mysql> use huang Database changed mysql> INSERT INTO student (id,name,age) VALUE (1,'tom',20); ERROR 1146 (42S02): Table 'huang.student' doesn't exist mysql> create table student(id int(11) not null auto_increment primary key,name varchar(100) not null,age tinyint(4)); Query OK, 0 rows affected (0.02 sec) mysql> desc student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)
SELECT语句
字段column表示法
表示符 代表什么?
- 所有字段
as 字段别名,如col1 AS alias1
当表名很长时用别名代替
条件判断语句WHERE
操作类型 常用操作符
操作符 >,<,>=,<=,=,!=
BETWEEN column# AND column#
LIKE:模糊匹配
RLIKE:基于正则表达式进行模式匹配
IS NOT NULL:非空
IS NULL:空
条件逻辑操作 AND
OR
NOT
ORDER BY:排序,默认为升序(ASC)
ORDER BY语句 意义
ORDER BY ‘column_name’ 根据column_name进行升序排序
ORDER BY ‘column_name’ DESC 根据column_name进行降序排序
ORDER BY ’column_name’ LIMIT 2 根据column_name进行升序排序
并只取前2个结果
ORDER BY ‘column_name’ LIMIT 1,2 根据column_name进行升序排序
并且略过第1个结果取后面的2个结果
//DML操作之查操作select //语法:SELECT column1,column2,... FROM table_name [WHERE clause] [ORDER BY 'column_name' [DESC]] [LIMIT [m,]n]; mysql> desc student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec) mysql> select * from student; Empty set (0.00 sec) mysql> select name from student; +-------------+ | name | +-------------+ | tom | | jerry | | wangqing | | sean | | zhangshan | | zhangshan | | lisi | | chengshuo | | wangwu | | qiuyi | | qiuxiaotian | +-------------+ 11 rows in set (0.00 sec) mysql> SELECT * FROM student ORDER BY age; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 7 | lisi | NULL | | 9 | wangwu | 3 | | 8 | chengshuo | 10 | | 10 | qiuyi | 15 | | 1 | tom | 20 | | 6 | zhangshan | 20 | | 11 | qiuxiaotian | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 5 | zhangshan | 26 | | 4 | sean | 28 | +----+-------------+------+ 11 rows in set (0.00 sec) mysql> SELECT * FROM student ORDER BY age DESC; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 4 | sean | 28 | | 5 | zhangshan | 26 | | 3 | wangqing | 25 | | 2 | jerry | 23 | | 1 | tom | 20 | | 6 | zhangshan | 20 | | 11 | qiuxiaotian | 20 | | 10 | qiuyi | 15 | | 8 | chengshuo | 10 | | 9 | wangwu | 3 | | 7 | lisi | NULL | +----+-------------+------+ 11 rows in set (0.00 sec) mysql> SELECT * FROM student ORDER BY age limit 2; +----+--------+------+ | id | name | age | +----+--------+------+ | 7 | lisi | NULL | | 9 | wangwu | 3 | +----+--------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM student WHERE age >= 25; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | +----+-----------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM student WHERE age >= 25 AND name = 'zhangshan'; +----+-----------+------+ | id | name | age | +----+-----------+------+ | 5 | zhangshan | 26 | +----+-----------+------+ 1 row in set (0.00 sec) mysql> select * from student where age is not null; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 8 | chengshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 10 rows in set (0.00 sec)
update语句
//DML操作之改操作update
//语法:UPDATE table_name SET column1 = new_value1[,column2 = new_value2,…] [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];
mysql> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 7 | lisi | NULL | | 8 | chengshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 11 rows in set (0.00 sec) 片
delete语句
//DML操作之删操作delete
//语法:DELETE FROM table_name [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];
mysql> select * from student; +----+-------------+------+ | id | name | age | +----+-------------+------+ | 1 | tom | 20 | | 2 | jerry | 23 | | 3 | wangqing | 25 | | 4 | sean | 28 | | 5 | zhangshan | 26 | | 6 | zhangshan | 20 | | 8 | chengshuo | 10 | | 9 | wangwu | 3 | | 10 | qiuyi | 15 | | 11 | qiuxiaotian | 20 | +----+-------------+------+ 10 rows in set (0.00 sec) mysql> delete from student; Query OK, 10 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> desc student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
truncate语句
truncate与delete的区别:
语句类型 特点
delete DELETE删除表内容时仅删除内容,但会保留表结构
DELETE语句每次删除一行,并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间
truncate 删除表中所有数据,且无法恢复
表结构、约束和索引等保持不变,新添加的行计数值重置为初始值
执行速度比DELETE快,且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放
对于有外键约束引用的表,不能使用TRUNCATE TABLE删除数据
不能用于加入了索引视图的表
//语法:TRUNCATE table_name;
mysql> truncate student; Query OK, 0 rows affected (0.00 sec) mysql> select * from student; Empty set (0.00 sec) mysql> desc student; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | age | tinyint(4) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
DCL操作
创建授权grant
权限类型(priv_type)
权限类型 代表什么?
ALL 所有权限
SELECT 读取内容的权限
INSERT 插入内容的权限
UPDATE 更新内容的权限
DELETE 删除内容的权限
指定要操作的对象db_name.table_name
表示方式 意义
. 所有库的所有表
db_name 指定库的所有表
db_name.table_name 指定库的指定表
WITH GRANT OPTION:被授权的用户可将自己的权限副本转赠给其他用户,说白点就是将自己的权限完全复制给另一个用户。不建议使用。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | huang | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) //授权teng用户在数据库本机上登录访问所有数据库 mysql> GRANT ALL ON *.* TO 'teng'@'localhost' IDENTIFIED BY 'P@ssword123!'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT ALL ON *.* TO 'teng'@'127.0.0.1' IDENTIFIED BY 'P@ssword123!'; Query OK, 0 rows affected, 1 warning (0.00 sec) //授权teng用户在192.168.11.134上远程登录访问wangqingge数据库 mysql> GRANT ALL ON teng.* TO 'teng'@'192.168.11.134' IDENTIFIED BY 'P@ssword123!'; Query OK, 0 rows affected, 1 warning (0.00 sec) //授权teng用户在所有位置上远程登录访问wangqingge数据库 mysql> GRANT ALL ON *.* TO 'teng'@'%' IDENTIFIED BY 'P@ssword123!'; Query OK, 0 rows affected, 1 warning (0.00 sec)
4.3.2 查看授权
//查看当前登录用户的授权信息
mysql> SHOW GRANTS; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) //查看指定用户teng的授权信息 mysql> SHOW GRANTS FOR teng; +-----------------------------------------------+ | Grants for teng@% | +-----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'teng'@'%' | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR 'teng'@'localhost'; +---------------------------------------------------+ | Grants for teng@localhost | +---------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'teng'@'localhost' | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW GRANTS FOR 'teng'@'127.0.0.1'; +---------------------------------------------------+ | Grants for teng@127.0.0.1 | +---------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'teng'@'127.0.0.1' | +---------------------------------------------------+ 1 row in set (0.00 sec)
取消授权REVOKE
mysql> REVOKE ALL ON *.* FROM 'teng'@'192.168.11.134'; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
注意:mysql服务进程启动时会读取mysql库中的所有授权表至内存中:
GRANT或REVOKE等执行权限操作会保存于表中,mysql的服务进程会自动重读授权表,并更新至内存中
对于不能够或不能及时重读授权表的命令,可手动让mysql的服务进程重读授权表
mysql> FLUSH PRIVILEGES;
这篇关于数据库配置的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-26结对编程到底难不难?答案在这里
- 2024-06-19《2023版Java工程师》课程升级公告
- 2024-06-15matplotlib作图不显示3D图,怎么办?
- 2024-06-1503-Loki 日志监控
- 2024-06-1504-让LLM理解知识 -Prompt
- 2024-06-05做软件测试需要懂代码吗?
- 2024-06-0514-ShardingSphere的分布式主键实现
- 2024-06-03为什么以及如何要进行架构设计权衡?
- 2024-05-31全网首发第二弹!软考2024年5月《软件设计师》真题+解析+答案!(11-20题)
- 2024-05-31全网首发!软考2024年5月《软件设计师》真题+解析+答案!(21-30题)