文章目录
mysql 的程序组成
-
客户端
- mysql:CLI 交互式客户端程序
- mysql_secure_installation:安全初始化,强烈建议安装完以后执行此命令
- mysqldump:mysql 备份工具
- mysqladmin
-
服务器端
- mysqld
[root@localhost ~]# mysql
mysql mysqld-debug mysql_secure_installation
mysqladmin mysqld_pre_systemd mysqlshow
mysqlbinlog mysqldump mysqlslap
mysqlcheck mysqldumpslow mysql_ssl_rsa_setup
mysql_config mysqlimport mysql_tzinfo_to_sql
mysql_config-64 mysql_install_db mysql_upgrade
mysql_config_editor mysql_plugin
mysqld mysqlpump
mysql 工具使用
语法:mysql [OPTIONS] [database]
-uUSERNAME:指定用户名,默认为 root
-hHOST:指定服务器主机,默认为 localhost,推荐使用 ip 地址
-pPASSWORD:指定用户的密码
-P#:指定数据库监听的端口,这里的 # 需用实际的端口号代替,如 -P3307
-V:查看当前使用的 mysql 版本
-e:不登录 mysql 执行 sql 语句后退出,常用于脚本
[root@localhost ~]# mysql -uroot -pskye123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
服务器监听的两种 socket 地址
socket 类型 | 说明 |
---|---|
ip socket | 默认监听在 tcp 的 3306 端口,支持远程通信 |
unix sock | 监听在 sock 文件上(/tmp/mysql.sock,/var/lib/mysql/mysql.sock) 仅支持本地通信 server 地址只能是:localhost,127.0.0.1 |
[root@localhost ~]# mysql --help | grep socket
--protocol=name The protocol to use for connection (tcp, socket, pipe,
-S, --socket=name The socket file to use for connection.
The buffer size for TCP/IP and socket communication.
socket (No default value)
mysql 数据库操作
数值:
数值类型 | 大小(bytes) | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
tinyint | 1 | (-128,127) | (0,255) | 小整数值 |
smallint | 2 | (-32768,32767) | (0,65535) | 大整数值 |
mediumint | 3 | (-8388608,8388607) | (0,16777215) | 大整数值 |
int 或 integer | 4 | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
bigint | 8 | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
float | 4 | (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)) | 0,(1.175 494 351 E-38,3.402 823 466 E+38) | 单精度,浮点数值 |
double | 8 | (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) | 双精度,浮点数值 |
decimal | 对 decimal(M,D) ,如果 M>D ,为 M+2 否则为 D+2 | 依赖于 M 和 D 的值 | 依赖于 M 和 D 的值 | 小数值 |
日期和时间:
数值类型 | 大小(bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
date | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
time | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
year | 1 | 1901/2155 | YYYY | 年份值 |
datetime | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
timestamp | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值, 时间戳 |
字符串:
数值类型 | 大小(bytes) | 用途 |
---|---|---|
char | 0~255 | 定长字符串 |
varchar | 0~65535 | 变长字符串 |
tinyblob | 0~255 | 不超过 255 个字符的二进制字符串 |
tinytext | 0~255 | 短文本字符串 |
blob | 0~65535 | 二进制形式的长文本数据 |
text | 0~65535 | 长文本数据 |
mediumblob | 0~16777215 | 二进制形式的中等长度文本数据 |
mediumtext | 0~16777215 | 中等长度文本数据 |
longblob | 0~4294967295 | 二进制形式的极大文本数据 |
longtext | 0~4294967295 | 极大文本数据 |
DDL 操作
数据库操作
- create database [if not exists] DB_NAME; :创建数据库
- show databases; :查看当前实例有哪些数据库
- use DATABASES; :进入数据库
- drop database [if exists] DB_NAME; :删除数据库
表操作
- create table TABLE_NAME (COL1 DATATYPE 修饰符,COL2 DATATYPE 修饰符) engine=‘存储引擎类型’; :创建表
- show tables; :查看当前数据库有哪些表
- desc [DB_NAME.] TABLE_NAME; :查看表的结构信息
- drop table [if exists] TABLE_NAME; :删除表
用户操作
mysql 用户帐号由两部分组成,如 ‘USERNAME’@‘HOST’ ,表示此 USERNAME 只能从此 HOST 上远程登录
这里(‘USERNAME’@‘HOST’)的 HOST 用于限制此用户可通过哪些主机远程连接 mysql 程序,其值可为:
-
IP 地址,如:172.16.12.129
-
通配符
- %:匹配任意长度的任意字符,常用于设置允许从任何主机登录
- _:匹配任意单个字符
-
create user ‘USERNAME’@‘主机’ [identified by ‘PASSWORD’];:数据库用户创建
-
drop user ‘USERNAME’@‘主机’; :删除数据库用户
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'haha'@'localhost' identified by 'skye123!';
Query OK, 0 rows affected (0.00 sec)
show:查看
- show character set; :查看支持的所有字符集
- show engines; :查看当前数据库支持的所有存储引擎
- show tables from TABLE_NAME; :不进入某数据库而列出其包含的所有表
- show create table TABLE_NAME; :查看某表的创建命令
- show table status like ‘TABLE_NAME’ \G:查看表的状态
mysql> show table status like 'skye' \G
*************************** 1. row ***************************
Name: skye
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: NULL
Create_time: 2022-07-25 15:24:25
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
help KEYWORD; :获取帮助
//获取创建表的帮助
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
......
DML 操作
DML 操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT),均属针对表的操作。
INSERT 语句
insert [into] TABLE\_NAME [(COLUMN_NAME, ...)] { value | values } (VALUE1, ...), (...), ...;
可一次插入一条记录,也可一次插入多条记录。
SELECT 语句
select COLUMN1,COLUMN2, ... from TABLE_NAME [where CLAUSE] [order by 'COLUMN_NAME' [desc]] [limit [M,]N];
字段 COLUMN 表示法
表示符 | 代表 |
---|---|
* | 所有字段 |
as | 字段别名,如 COL1 as ALIAS1 当表名很长时用别名代替 |
条件判断语句 where
操作类型 | 常用操作符 |
---|---|
操作符 | >,<,>=,<=,=,!= between COLUMN# and COLUMN# like:模糊匹配 rlike:基于正则表达式进行模式匹配 is not null:非空 is null:空 |
条件逻辑操作 | and or not |
… where not CLAUSE …
… where COLUMN_NAME not in (NUM1, NUM2, …) …
… where COLUMN_NAME not like …
… where COLUMN_NAME not between NUM1 and NUM2 …
… where not exists …
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 个结果 |
UPDATE 语句
update TABLE\_NAME set COLUMN1 = NEW_VALUE1[,COLUMN2 = NEW_VALUE2, ...] [where CLAUSE] [order by 'COLUMN_NAME' [desc]] [limit [M,]N];
DELETE 语句
delete from TABLE_NAME [where CLAUSE] [order by 'COLUMN_NAME' [desc]] [limit [M,]N];
mysql> insert skye(id,name,age) values(1,'zhangsan',20);
Query OK, 1 row affected (0.00 sec)
mysql> insert skye(id,name,age) values(2,'lisi',18);
Query OK, 1 row affected (0.00 sec)
mysql> insert skye(id,name,age) values(3,'zhaowu',25);
Query OK, 1 row affected (0.00 sec)
mysql> select name from skye;
+----------+
| name |
+----------+
| zhangsan |
| lisi |
| zhaowu |
+----------+
3 rows in set (0.00 sec)
mysql> select * from skye order by age;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | lisi | 18 |
| 1 | zhangsan | 20 |
| 3 | zhaowu | 25 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> update skye set age = 30 where name = 'lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from skye where name = 'lisi';
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | lisi | 30 |
+----+------+------+
1 row in set (0.00 sec)
mysql> delete from skye where id = 1; //删除某条记录
Query OK, 1 row affected (0.00 sec)
mysql> select * from skye;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 2 | lisi | 30 |
| 3 | zhaowu | 25 |
+----+--------+------+
2 rows in set (0.00 sec)
mysql> delete from skye; //删除整张表的内容
Query OK, 2 rows affected (0.00 sec)
mysql> desc skye;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
truncate TABLE_NAME
truncate 与 delete 的区别
语句类型 | 特点 |
---|---|
delete | delete 删除表内容时仅删除内容,但会保留表结构 delete 语句每次删除一行,并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间 |
truncate | 删除表中所有数据,且无法恢复 表结构、约束和索引等保持不变,新添加的行计数值重置为初始值 执行速度比 delete 快,且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放 对于有外键约束引用的表,不能使用 truncate TABLE 删除数据 不能用于加入了索引视图的表 |
DCL 操作
GRANT
grant PRIV\_TYPE, ... on [OBJECT\_TYPE] DB\_NAME.TABLE_NAME to 'USERNAME'@'主机' [identified by 'PASSWORD'] [with grant option];
:创建授权
授权类型 (PRIV_TYPE)
权限类型 | 代表 |
---|---|
ALL | 所有权限 |
SELECT | 读取内容的权限 |
INSERT | 插入内容的权限 |
UPDATE | 更新内容的权限 |
DELETE | 删除内容的权限 |
指定要操作的对象 DB_NAME.TABLE_NAME
表示方式 | 意义 |
---|---|
*.* | 所有库的所有表 |
DB_NAME | 指定库的所有表 |
DB_NAME.TABLE_NAME | 指定库的指定表 |
with grant option:被授权的用户可将自己的权限副本转赠给其他用户,说白点就是将自己的权限完全复制给另一个用户。不建议使用。
查看授权
- show grants; :查看当前登录用户的授权信息
- show grants for USERNAME;
- show grants for ‘USERNAME’@‘主机(NAME 或 IP)’;
REVOKE
revoke PRIV_TYPE, … on DB_NAME.TABLE.NAME from ‘USERNAME’@‘主机’; :取消授权
注意:mysql 服务进程启动时会读取 mysql 库中的所有授权表至内存中:
- grant 或 revoke 等执行权限操作会保存于表中,mysql 的服务进程会自动重读授权表,并更新至内存中
- 对于不能够或不能及时重读授权表的命令,可手动让 mysql 的服务进程重读授权表
flush privileges;