mysql的基本操作

mysql的基本操作

配置文件

[mysqld]
tmpdir=/mysql/tmp/
basedir=/mysql
datadir=/mysql/data
symbolic-links=0
character_set_server=utf8mb4
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
'''
sql_mode中的两个参数说明：
在sql_mode中不包涵no_engine_subtitution 且create table 中engine子句指定的存储引擎不被支持时，mysql会把表的引擎改为innodb
当sql_mode中包涵no_engine_subtitution时，如果create table 时指定的engine项不被支持，这个时候mysql会支持报错。
'''
[mysqld_safe]
default-character-set=utf8mb4
log-error=/mysql/logs/mysqld.log
pid-file=/mysql/run/mysqld/mysqld.pid

[client]
default-character-set=utf8mb4
socket=/tmp/mysql.sock

[mysql]
default-character-set=utf8mb4

存储引擎

# 查看数据库中所有的存储引擎
SHOW ENGINES;
# 查看数据库当前默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%';
# 四个存储引擎了解
MyISAM
MySQL 5.5之前默认的存储引擎
存取数据较快，但不支持事务，安全性较低
'''
底层文件个数
.frm 存储表结构
.MYD 存储表数据
.MYI 存储表索引
'''
InnoDB
MySQL 5.5之后默认的存储引擎
支持事务、行锁、外键等操作，存取速度没有MyISAM快，但安全性更高（更像oracle）
'''
底层文件个数
.frm 表结构
.ibd 表数据与表索引
'''
MEMORY
基于内存存取数据，仅用于临时表数据存取
'''
底层文件个数
.frm 表结构
'''
BLACKHOLE
相当于操作系统的/dev/null，任务写入进去的数据都会丢失
'''
底层文件个数
.frm 表结构
'''

登录

mysql -u用户名 -p密码

基于数据库的基本操作

# 创建数据库
CREATE DATABASE db1;
# 切换数据库
USE database_name;
# 查看数据库信息
SHOW DATABASES;
SHOW CREATE DATABASE database_name;
# 修改数据库字符集
ALTER DATABASE database_name CHARSET='utf8';
# 删除库
DROP DATABASE database_name;

整数型

注意正负号会占用1个比特位，所以在加入正负号后，支持的范围就会缩小。
取消正负号，使用unsigned约束条件：
CREATE TABLE table_name(id tinyint unsigned);
# int(5) 注意，扩号中的数字并不能限制长度
create table t1(id int(5));
insert into t1 values(12345678);
mysql> select * from t1;
+----------+
| id |
+----------+
| 12345678 |
+----------+
1 row in set (0.00 sec)
# int(5) 扩号中的数字如果要有实际意义，需要加上约束条件zerofill，此时再插入数据时，如果不够5位，则使用0填充，超过5位则直接插入数据。
create table t1 (id int(5) zerofill);
insert into t1 values(123);
select * from t1;
+-------+
| id |
+-------+
| 00123 |
+-------+
1 row in set (0.00 sec)
'int不指定长度的话，默认是int(11)，所以不需要指定长度'

浮点型

float(20, 10)
总共存储20位数字，小数位占10位
double(20, 10)
总共存储20位数字，小数位占10位
decimal(20, 10)
总共存储20位数字，小数位占10位
三者的区别：精确度不同
如下：
CREATE TABLE t1(id_t1 float(60, 20));
CREATE TABLE t2(id_t2 double(60, 20));
CREATE TABLE t3(id_t3 decimal(60, 20));
INSERT INTO t1 values(1.11111111111111111111);
INSERT INTO t2 values(1.11111111111111111111);
INSERT INTO t3 values(1.11111111111111111111);
select * from t1, t2, t3;
+------------------------+------------------------+------------------------+
| id_t1 | id_t2 | id_t3 |
+------------------------+------------------------+------------------------+
| 1.11111116409301760000 | 1.11111111111111120000 | 1.11111111111111111111 |
+------------------------+------------------------+------------------------+
1 row in set (0.00 sec)

字符类型

'''
char
定长
char(4) 最多存储4个字符，超出则报错，不足4字符则使用空格填充至4个
如：存储的字符为1字节不够4字节，则使用空格填充至4个字节为止，最后占用4字节。
varchar
变长
varchar(4) 最多存储4个字符，超出则报错，存储数据不足4字符则只占用实际空间大小
如：存储的字符为1字节，存储的数据也只会占用1字节。

默认情况下，MySQL会针对char的存储会自动填充空格和删除空格
可以修改MySQL的参数取消MySQL的这种机制：
'''
set global sql_mode='strict_trans_tables,pad_char_to_full_length';

枚举与集合

枚举(enum)
多选一
create table t1(
gender enum('male', 'femal', 'others'));
desc t1;
+--------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+-------+
| gender | enum('male','femal','others') | YES | | NULL | |
+--------+-------------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

insert into t1 values('男'); # 报错
insert into t1 values('male'); # 可以插入
集合（set）
多选多（或多选一）
create table t1(
hobbies set('chouyan','hejiu','tangtou')
);

desc t1;
+---------+----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------------------------------+------+-----+---------+-------+
| hobbies | set('chouyan','hejiu','tangtou') | YES | | NULL | |
+---------+----------------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

insert into t1 values('chouyan,hejiu,tangtou');
insert into t1 values('chouyan');
insert into t1 values('study'); # 报错

日期类型

常见的日期类型：
datatime 年月日时分秒
date 年月日
time 时分秒
year 年

PS:日期字段一般是系统自动获取，无需自己操作。

create table t1(
a datetime,
b date,
c time,
d year
);
desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | datetime | YES | | NULL | |
| b | date | YES | | NULL | |
| c | time | YES | | NULL | |
| d | year(4) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

insert into t1 values('2022-12-27 17:17:17','2022-11-11','11:11:11','2022');

select * from t1;
+---------------------+------------+----------+------+
| a | b | c | d |
+---------------------+------------+----------+------+
| 2022-12-27 17:17:17 | 2022-11-11 | 11:11:11 | 2022 |
+---------------------+------------+----------+------+
1 row in set (0.01 sec)

表操作

# 查看表
SHOW TABLES;
# 创建表
CREATE TABLE table_name(
字段名 字段类型 约束条件,
字段名 字段类型 约束条件,
字段名 字段类型 约束条件
...
...
)
# 查看指定表信息
SHOW CREATE TABLE table_name;
# 查看表结构
DESC table_name;
# 查看其它库里面的表
DESC database_name.table_name;
# 如： DESC mysql.user;
# 修改表名
ALTER TABLE table_nme RENAME new_table_name;
# 删除表
DROP TABLE table_name;
# 增加字段的几种方法
# 方法一 默认在末尾追加字段
ALTER TABLE table_name ADD 字段名 字段类型 约束条件;
# 方法二 在指定字段后面增加字段
ALTER TABLE table_name ADD 字段名 字段类型 约束条件 AFTER 已有字段;
# 方法三 在开头增加字段
ALTER TABLE table_name ADD 字段名 字段类型 约束条件 FIRST;

# 修改字段名以及字段类型
ALTER TABLE table_name CHANGE 旧字段名 新字段名 字段类型 约束条件;
# 修改字段类型（与change不同的是它只能修改字段类型）
ALTER TABLE table_name MODIFY 字段名 新字段类型 约束条件;

# 删除表名
ALTER TABLE table_name DROP column_name;

表中记录的基本操作

# 增
INSERT INTO table_name VALUES(value1 value_type, value2 value_type....);
# 删
DELETE FROM table_name WHERE condition1 AND condition2...;
# 改
UPDATA table_name SET 字段名=new_value WHERE condition1 AND condition2...;
# 查
SELECT * FROM table_name WHERE condition1 AND condition2...;