SQL 常用基础操作

1， 获取数据库名称列表

show databases;

2, 创建数据库/删除数据库

create database test_database; # 创建数据库
drop database test_database; # 删除数据

3, 切换至指定数据库

use test_database;

4， 查看当前所使用的数据库

select database()
show tables； Table_in_XXX, 这里XXX就是数据库名称
status;

5, 新建表格/删除表格

# 新建表格
create table table_name {
column_name datatype,
column_name2 datatype,
......
column_name3 datatype,
primary key('column_nameX')
}

CREATE TABLE example2 (
	col1 INT PRIMARY KEY,
	col2 VARCHAR(20),
	col3 VARCHAR(20),
	col4 VARCHAR(20), INDEX (col2, col3)
	);  # 创建表时，同时为表添加索引和主键


# 删除表格
drop table table_name;

6, 查看表结构

desc (or describe ) table_name;
show create table table_name;

7， 修改表的某一列

alter table table_name modify  MicroSecondsSinceEpoch bigint unsigned not null; # 更新列字段类型
alter table table_name change old_column_name new_column_name char(32); # 更新列名字
alter table table_name drop delete_column_name; # 删除列
alter table table_name add MS_status char(32); # 新增列

8, 更新表名字

在mysql中，可以通过“ALTER TABLE 旧表名 RENAME 新表名;”语句来修改表名，通过“ALTER TABLE 表名 CHANGE 旧字段名/列名 新字段名/列名 新数据类型;”语句来修改列名。

alter table old_table_name rename new_table_name;

9, 清空表/或者删除部分数数据

truncate table table_name; # 清空表
delete from table_name where Subject!='MongoDB'; # 删除表中的某些行

10, 批量插入（python实现）

sql = "insert into table_name(name, age, desc) values(%s, %s, %s)"
data_list = (('a', 1, '22'), ('b', 2, '33'))
conn.executemany(sql, data_list )

11, 条件查找

select * from table_name where 条件1 and 条件2 and ( 条件3 or 条件四);
select * from yfa_monitor_port_table where timestamp > t1 and status = 'Normal' limit 1;

select `student name`,`student age` from student_table; # 注意， 为 '`'符号

12, 获取当前时间戳

mysql > select CURRENT_TIMESTAMP(); # 秒
mysql > select REPLACE(unix_timestamp(current_timestamp(3)), '.', '')

13, 查看表容量

（1） 查看全部表容量

select table_schema as 'databases', table_name as 'table_name', table_rows as 'row_nunber', truncate(data_length/1024/1024, 2) as 'table_size(MB)', truncate(index_length/1024/1024, 2) as 'index size(MB)' from information_schema.tables order by data_length desc, index_length desc;

（2）查看指定database的容量

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='target_db_name';　

（3）查看总消耗的存储容量
 

mysql>> use information_schema;
mysql>> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;

14， 复制表

(1) 创建表不复制数据

create table new_table_name like old_table_name; # 创建表new_table_name 只复制表的结构，不复制数据

(2) 创建表并复制数据

# 复制部分数据
create table B select * from A where Id<6;
# 复制全部数据
create table B select * from A; # 如果表数据过大，慎重操作

(3) 复制A表数据到B表之中

insert into B select * from A where id <=3;

（4）复制A表部分列值，填充到B表部分列值

insert into B(column1,column2,column3) select column1,column2,column3 from A where id <=3;

15，调整列位置

（1）调制到首位

alter table table_name modify city_name char(32) first;

(2) 调制到某列后面

alter table table_name modify number_persion bigint unsigned after city_name;

16， 读取table数据到文件
方法一：

mysql> SELECT * INTO OUTFILE '/tmp/output.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

方法二（如果mysql 已经正确配置了secure-file-priv）

mysql >> select count(1) from table  into outfile '/tmp/test.xls';

方法三 (推荐使用这种方法）

也可以使用命令行模式：

(A:注意-p 后面的 XXXX 是db_name B: -e 指定的可执行语句后没有';' 号）

[root@SHNHDX63-146 ~]# mysql -h 127.0.0.1 -u root -p XXXX -P 3306 -e "select * from table"  > /tmp/test/tx

17，命令行插入数据

insert into table_name (MicroSecondSinceEpoch,Id,IdType) values (1234, 0, "OperationId");

18, 删除符合条件的数据

delete from table where Ts>1234234;

19. Mysql 使用当前User修改User密码

使用 mysqladmin 命令修改 MySQL 的 root 用户密码格式为

mysqladmin -u用户名 -p旧密码 password 新密码。 注意：下图修改密码的命令中 -uroot 和 -proot 是整体，不要写成 -u root -p root，-u 和 root 间可以加空格，但是会有警告出现，所以就不要加空格了。

20. 命令行交互模式

mysql -h localhost -uroot -proot_pwd -e “show database;”

21. 查看一个表或者database 的创建use

use  information_schema;
SELECT CREATE_TIME FROM TABLES WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名';

22， 创建用户，并添加一个database 权限

例如，如果想要创建一个名为 john 的用户，他将从任何地方连接，并且密码是 password123，可以使用以下命令：

CREATE USER 'john'@'%' IDENTIFIED BY 'password123';

这个命令只创建了用户，没有给予任何权限。如果想要给刚刚创建的用户放权，需要使用 GRANT 命令。例如，以下命令会给 john 用户在 database_name 数据库上的所有权限：

GRANT ALL PRIVILEGES ON database_name.* TO 'john'@'%';

记住执行如下命令，来让更改生效。

FLUSH PRIVILEGES;

23， 查看某个用户的权限信息

SHOW GRANTS FOR 'username'@'host' [ON database];