前提:在客户现场,mysql数据库已安装并启动,目前不清楚启动路径,但我要新建表并使用。
连接数据库mysql
ps -ef|grep mysql
根据启动进程查看mysql相关目录路径。
–defaults-file
–basedir
–datadir
–plugin-dir
–log-error
–pid-file
–socket
mysqld启动目录,找到bin路径。/bin/mysqld
以下指令输入一条按回车,mysql.sock我这里是跟bin在同路径下,如果不是请根据实际路径写。
cd /mysql/bin
./mysql -u用户名 -p密码 -S ../mysql.sock
use db_vmdb;
show databases;
show tables;
新建表+插入数据
我掌握两种方法,一个是直接命令行新建,另一个是通过执行脚本的方式。
目前已在mysql的bin目录下:
(1)命令行新建表,添加表头,插入数据。
新建表test0703,添加表头name和age,格式如下,表头后空格加类型,用逗号隔开表头名。
create table test0703(name varchar(100),age int);
show columns from test0703;
insert into test0703(name,age) values(ming,24);
select * from test0703;
(2)执行脚本
将上述命令写到notepad++或者记事本中,保存命名为test.sql。
source test.sql;
select * from test0703;
CREATE TABLE `test20240402`(
`id` int(16) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`count_date` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '统计日期yyyy-MM-dd',
`start_time` decimal(16,0) DEFAULT NULL COMMENT '时间:yyyyMMdd',
`end_time` decimal(16,0) DEFAULT NULL COMMENT '时间:yyyyMMdd',
`region_size` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '换算单位后的region大小',
`region_count` decimal(16,2) DEFAULT NULL COMMENT 'region个数',
PRIMARY KET(`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='region表';
删除表+清除数据
刚建立的表不要了,或者表还要但数据得清掉。
先删除数据,表头还在,删除表,查询无。
delete from test0703;
show columns from test0703;
drop table test0703;
show tables likes 'test0703';
查询表
show columns from test0703;
修改表
1、修改字段类型
需要根据目前已有数据的规则修改,如果原先已是char,改为int就困难,但可以改为varchar。
mysql> select * from t1;
+-----------------+-------------+------+-----------------+
| SYSTEM_NAME_OLD | SYSTEM_CODE | NUM | SYSTEM_NAME_BAK |
+-----------------+-------------+------+-----------------+
| 应用系统1 | AAAA | 1 | NULL |
| 应用系统3 | AAAA | 3 | NULL |
+-----------------+-------------+------+-----------------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE t1 MODIFY SYSTEM_CODE int;
ERROR 1366 (HY000): Incorrect integer value: 'AAAA' for column 'SYSTEM_CODE' at row 1
mysql> ALTER TABLE t1 MODIFY SYSTEM_CODE varchar;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> ALTER TABLE t1 MODIFY SYSTEM_CODE varchar(255);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+-----------------+-------------+------+-----------------+
| SYSTEM_NAME_OLD | SYSTEM_CODE | NUM | SYSTEM_NAME_BAK |
+-----------------+-------------+------+-----------------+
| 应用系统1 | AAAA | 1 | NULL |
| 应用系统3 | AAAA | 3 | NULL |
+-----------------+-------------+------+-----------------+
2 rows in set (0.00 sec)
mysql> desc t1;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| SYSTEM_NAME_OLD | char(180) | YES | | NULL | |
| SYSTEM_CODE | varchar(255) | YES | | NULL | |
| NUM | int(11) | YES | | NULL | |
| SYSTEM_NAME_BAK | varchar(128) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
2、修改字段长度
目前看可以顺利延长,缩短字段长度会有问题。
mysql> select * from t1;
+-----------------+-------------+------+-------------+
| SYSTEM_NAME_OLD | SYSTEM_CODE | NUM | SYSTEM_NAME |
+-----------------+-------------+------+-------------+
| 应用系统1 | AAAA | 1 | NULL |
| 应用系统3 | AAAA | 3 | NULL |
+-----------------+-------------+------+-------------+
2 rows in set (0.00 sec)
mysql> alter table t1 change SYSTEM_CODE SYSTEM_CODE VARCHAR(3);
ERROR 1406 (22001): Data too long for column 'SYSTEM_CODE' at row 1
mysql> alter table t1 change SYSTEM_CODE SYSTEM_CODE VARCHAR(4);
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DESC T1;
+-----------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+------------+------+-----+---------+-------+
| SYSTEM_NAME_OLD | char(180) | YES | | NULL | |
| SYSTEM_CODE | varchar(4) | YES | | NULL | |
| NUM | int(11) | YES | | NULL | |
| SYSTEM_NAME | varchar(3) | YES | | NULL | |
+-----------------+------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
问题总结
问题1:新建表名时误用中划线“-”,导致查询结果有误,且表名无法删除一直报错。
解决方案:用反引号将表名括起来,比如表名为:test-2023,命令使用:
drop table `test-2023`;