用户操作
创建数据库用户
mysql> create user 'tengjia'@'%' identified by 'Passw0rd.';
Query OK, 0 rows affected (3.36 sec)
使用新创建的账户密码登录
[root@localhost ~]# mysql -utengjia -pPassw0rd.;
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 17
Server version: 8.0.24 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> drop user 'tengjia'@'%';
Query OK, 0 rows affected (0.13 sec)
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| binary | Binary pseudo charset | binary | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
查看当前数据库支持的所有存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.14 sec)
查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hanao |
| information_schema |
| mysql |
| performance_schema |
| sys |
| tengjia |
+--------------------+
6 rows in set (1.65 sec)
查看表结构
mysql> desc tengjia.student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(100) | NO | | NULL | |
| age | tinyint | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.34 sec)
查看表的创建命令
mysql> show create table tengjia.student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` tinyint DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.49 sec)
查看表的状态```
mysql> show table status like 'student'\G;
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2021-04-29 07:36:42
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in 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]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
DML语法
mysql> insert student(name,age) values('tengjia',18),('ha',18);
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | tengjia | 18 |
| 2 | ha | 18 |
+----+---------+------+
2 rows in set (0.00 sec)
select查询语句
字段表示法
|表示符| 含义 |
|-*-|-所有字段-|
| as | 字段别名 |
条件判断语句where
操作符
|>,<,>=,<=,=,!=
between [column] and [column]
like:模糊匹配
rlike:基于正则表达式进行模式匹配
is not null:非空
is null:空
条件逻辑操作
and or not
order by:排序,默认为升序(ASC)
order by [column]
根据字段进行升序排序
order by [column]
根据字段进行降序排序
order by [column] limit 2
根据字段进行升序排序并只取前两行
order by [column] limit 1,2
更加字段进行升序排序并且略过第1个结果取后面的2个结果
mysql> select * from student where age between 5 and 30 order by age desc limit 1,7;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | ha | 18 |
+----+------+------+
1 row in set (0.00 sec)
update语法
mysql> update student set age = 25 where name = 'ha';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | tengjia | 18 |
| 2 | ha | 25 |
+----+---------+------+
2 rows in set (0.01 sec)
delete语法
mysql> delete from student where name = 'ha' and id = 5;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | tengjia | 18 |
| 2 | ha | 25 |
+----+---------+------+
2 rows in set (0.00 sec)
truncate语法
truncate与delete的区别:
delete
delete删除表内容时仅删除内容,但会保留表结构
delete语句每次删除一行
并在事务日志中为所删除的每行记录一项
可以通过回滚事务日志恢复数据
非常占用空间
truncate
删除表中所有数据,且无法恢复
表结构、约束和索引等保持不变,新添加的行计数值重置为初始值
执行速度比delete快,且使用的系统和事务日志资源少
通过释放存储表数据所用的数据页来删除数据
只在事务日志中记录页的释放
对于有外键约束引用的表,不能使用truncate table 删除数据
不能用于加入了索引的视图的表
mysql> select * from student;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | tengjia | 18 |
| 2 | ha | 25 |
+----+---------+------+
2 rows in set (0.00 sec)
mysql> select * from student;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 1 | tengjia | 18 |
| 2 | ha | 25 |
+----+---------+------+
2 rows in set (0.00 sec)
授权root用户在所有位置上远程登录访问所有数据库,并设置登录密码
权限类型 含义
all 所有权限
select 读取内容的权限
insert 插入内容的权限
update 修改内容的权限
delete 删除内容的权限
mysql> grant all on *.* to 'root'@'%' identified by 'Passw0rd.';
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看当前用户的授权信息
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)
查看指定用户(tengjia)的授权信息
mysql> show grants for 'tengjia'@'%';
+-------------------------------------+
| Grants for tengjia@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO `tengjia`@`%` |
+-------------------------------------+
1 row in set (0.00 sec)
取消用户(tengjia)的授权REVOKE
mysql> revoke all on *.* from 'tengjia'@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.22 sec)
mysql服务进程启动时会读取mysql库中的所有授权表到内存中
grant或revoke等执行权限操作会保存于表中
mysql服务进程会自动重读授权表,并更新到内存中