Linux 链接 MySQL 及 基础操作

# 连接mysql
　　mysql -h主机地址 -P端口 -u用户名 -p
# 显示数据库 
　　show databases;
# 使用某个数据库
　　use xxx;
# 查看该数据库中的表
　　show tables; 
# 显示数据表的结构
　　describe 表名;
　　desc 表名;
# 显示表中的记录（注意表名大小写）
　　select * from 表名; 
# 创建数据库
　　create database 数据库名;
# 建表 
　　use 库名
　　create table 表名 (字段列表);
　　# 示例
　　use blog;
　　create table user (id int(3) AUTO_INCREMENT NOT NULL PRIMARY KEY, 
　　　　　　　　　　　　　　name CHAR(8),
　　　　　　　　　　　　　　gender CHAR(2) DEFAULT '未知',
　　　　　　　　　　　　　　birthday DATE
　　　　　　　　);
　　describe name;
# 增加一个字段
　　ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [约束条件]; # 在末尾
　　　　# 示例
　　　　　　ALTER TABLE `user` ADD COLUMN `province` varchar(25) NOT NULL DEFAULT '上海' COMMENT '省份';
　　ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [约束条件] FIRST; # 在开头添加字段
　　　　# 示例
　　　　　　ALTER TABLE `user` ADD COLUMN `city` varchar(25) NULL COMMENT '城市' FIRST;
　　ALTER TABLE 表名 ADD COLUMN 新字段名 数据类型 [约束条件] AFTER 已经存在的字段名;# 在中间位置添加字段
　　　　# 示例
　　　　　　ALTER TABLE `user` ADD COLUMN `area` varchar(25) NULL COMMENT '区域' AFTER city;# 增加在 city 字段后
　　ps
　　　　ADD COLUMN　　  添加字段
　　　　DEFAULT　　　　  默认值
　　　　COMMENT　　　　  注释、注解、备注
　　　　FIRST　　　　　  关键字使字段放在首位
　　　　AFTER 　　　　   关键字使字段放在已有字段后
　　　　DEFAULT 　　　　 默认值
　　　　DEFAULT NULL　　默认为空
# 修改字段数据类型及默认值空/非空
　　ALTER TABLE 数据表名 MODIFY 字段名 数据类型 DEFAULT 默认值;# 修改数据类型及默认值
　　　　# 示例
　　　　　　ALTER TABLE user MODIFY province varchar(250) DEFAULT '北京';
　　　　　　ALTER TABLE user MODIFY province varchar(250) DEFAULT NULL;
　　ALTER TABLE 表名 ALERT COLUMN 字段名 SET DEFULT 默认值;# 修改默认值
　　　　# 示例
　　　　　　ALTER TABLE user ALTER COLUMN province SET DEFULT 'beijing';
　　ps：由于不同类型的数据的存储方式及长度不相同，修改数据类型可能会影响数据表中已有的数据记录，数据表中已经有数据时，谨慎修改
# 删除字段默认值
　　ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT;
　　# 示例
　　　　ALTER TABLE user ALTER COLUMN province DROP DEFAULT;
# 修改字段名
　　ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型;
　　# 示例
　　　　ALTER TABLE user CHANGE new_province new_province2 VARCHAR(250);
# 增加数据
　　INSERT INTO user VALUES ('','张三','男','2019-01-01');# 按字段顺序依次增加数据
　　# 示例
　　　　INSERT INTO user ('name','gender','birthday') VALUE ('李四','女','2020-06-01');# 可控制增加字段，字段对应数据
　　　　INSERT INTO user SET name = '王五',gender = '男',birthday = '2018-05-16';# 前后不需对应，易调
　　　　SELECT * FROM name;# 查询可见三条数据
# 修改数据
　　UPDATE 表名 SET 字段='',... [WHERE 条件]
　　# 示例
　　　　UPDATE user SET birthday='2019-01-01' WHERE name='张三'; # 将张三的出生年月改为2019-06-01
　　# 将 B 表的字段值赋值给 A 表中的字段
   　　UPDATE tableA a,tableB b SET a.column=b.column [WHERE 条件];
　　　 UPDATE tableA a LEFT JOIN tableB b ON a.id=b.cs_id SET b.column=a.column [WHERE 条件];
　　　 UPDATE tableA a SET a.column1 = (SELECT b.column1 FROM tableB b WHERE a.id = b.aid)[WHERE 条件]
　　　　# 示例
　　　　　　UPDATE test a,test2 b SET a.info=b.cs_id WHERE a.id = 49536;
　　　　　　UPDATE test a LEFT JOIN test2 b ON a.id = b.cs_id SET a.info=b.id WHERE a.id = 24;
　　　　　　UPDATE test a SET a.info = (SELECT b.id FROM test2 b WHERE a.id = b.cs_id),
　　　　　　　　WHERE EXISTS (SELECT c_id FROM test3 c WHERE a.id = c.aid);

# 删除字段
　　ALTER TABLE 表名 DROP column 列名;
# 删除数据
　　DELETE FROM 表名 [WHERE 条件]
　　# 示例
　　　　DELETE FROM user WHERE name='张三'; # 删除名字是张三的数据
　　# 删除关联表数据
　　　　# 删除 test_new 表数据，条件是 a 表info字段值为 2022
　　　　DELETE a FROM test_new a, test b WHERE a.id=b.id and a.info = 2022;
# 删表
　　DROP TABLE 表名;# 直接删除表信息，速度最快，无法找回数据
　　TRUNCATE TABLE 表名;# 删除表数据，不删除表的结构，速度排第二，不能与 where 一起使用
　　DELETE FROM 表名 (WHERE 条件);# 删除表中的数据，不删除表结构，速度最慢，可以与 where 连用，可以删除指定的行
　　　　# 语句类型：delete 语句是数据库操作语言（DML），truncate，drop是数据库定义语言（DDL）；
　　　　# 效率：一般来说 drop > truncate> delete；
# 增加索引
　　A、ALTER TABLE 表名 ADD 索引 索引名(字段名)
　　B、CREATE 索引 索引名 ON 表名 (字段名)
　　C、ALTER TABLE 表名 ADD 索引 索引名 (字段名,字段名,...)# 添加多列索引
　　# 示例
　　　　ALTER TABLE `user` ADD INDEX province(province) # 添加普通索引
　　ps
　　　　UNIQU  　　　      唯一索引，不可以出现相同的值，可以有 NULL 值
　　　　INDEX　　　　　　　 普通索引，允许出现相同的索引内容
　　　　PROMARY KEY　　　　主键索引，不允许出现相同的值
　　　　FULLTEXT　　　 　  全文索引，可以针对值中的某个单词，但效率确实不敢恭维
　　　　组合索引：　　　　　 实质上是将多个字段建到一个索引里，列值的组合必须唯一
# 修改索引
　　ps：MySQL 中没有真正意义上的修改索引
　　　　只有先删除之后在创建新的索引才可以达到修改的目的
　　　　原因是mysql在创建索引时会对字段建立关系长度等
　　　　只有删除之后创建新的索引才能创建新的关系保证索引的正确性；
# 删除索引
　　DROP INDEX 索引名 ON 表名
# 查看索引
　　SHOW INDEX FROM 表名;
# 删库
　　DROP DATEBASE库名;
# 重命名表
　　ALTER TABLE 表原名 rename 新表名;
# 数据库某表的备份
　　mysqldump -u root -p 数据库名 表名 > 备份文件名
# 数据库备份
　　mysqldump -u root -p mysql > /home/mysql.sql# 把数据库 mysql 备份到home目录下，命名为 mysql.sql
　　mysqldump -u账号 -p密码 数据库 | gzip > 脚本路径
　　# 示例
　　　　#!/bin/bash
　　　　mysqldump -uroot -proot test > /home/backup/test_$(date +%Y%m%d_%H%M%S).sql
　　# 备份进行压缩
　　　　#!/bin/bash
　　　　mysqldump -uroot -proot test | gzip > /home/backup/test_$(date +%Y%m%d_%H%M%S).sql.gz
　　# 备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库
　　mysqldump -–add-drop-table -u账号 -p密码 数据库 > test_$(date +%Y%m%d_%H%M%S).sql
　　# 仅备份数据库结构
　　mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
　　# 同时备份多个数据库
　　mysqldump -u账号 -p密码 –databases databasename1 databasename2 databasename3 > multibackupfile.sql
　　# 备份服务器上所有数据库
　　mysqldump –all-databases > 脚本路径及名称(ls:/home/test.sql)
# 数据库还原
　　# 还原全部数据库
　　source 备份路径及名称 # source方法
　　mysql -u账号 -p密码 数据库名< 脚本路径及名称(ls:test.sql)
　　# 还原压缩的MySQL数据库
　　gunzip < 脚本路径及名称(ls:test.sql.gz) | mysql -uusername -ppassword databasename 
# 导出数据
　　# 导出结构不导出数据
　　mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
　　# 导出数据和表结构
　　mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql
　　# 到处特定表
　　mysqldump -u用户名 -p密码 数据库名 --table 表名 > 数据库名.sql
　　# 导出数据不导出表结构
　　mysqldump -u用户名 -p密码 数据库名 表名 > filename.sql
# 导入数据
　　mysql -u账号 -p密码 数据库<sql脚本
　　# 示例
　　　　mysql -uroot -proot test</home/test20211220.sql
# 将数据库转移到新服务器
　　ysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename
# 将两个表的数据拼接后插入到另一个表
　　mysqldump -u用户名 -p密码 数据库名 表名 --where="筛选条件" > 导出文件路径
　　# 示例
　　　　mysqldump -uroot -p123456 test user --where=" id=11" > /home/xyx/Temp.sql
# 退出 mysql
　　exit

 

A