mysql命令笔记

1. 数据列类型

数据库中用来存储项目中的数据，按照类型和表格的对应关系，将类型创建的对象数据可以存储到数据库中。
　　由于每个对象的属性~存在不同的类型[字符串、整数、浮点数、布尔值…],对于数据库的要求就是：也要存在对应的数据类型，可以存储指定的数据。

类型名称	类型描述
tinyint	整数类型
smallint	整数类型
mediumint	整数类型
int	整数类型-21亿~+21亿
bigint	整数类型
float	单精度浮点数
double	双精度浮点数
decimal	高进度浮点数
char(num)	固定字节长度字符数据类型
varchar(max)	自由长度匹配类型[max指最大字节长度]
text	长文本
bool	布尔值~mysql中就是一个tinyint类型，0==False,非0==True
date	日期,格式：年-月-日
time	时间，格式：时:分:秒
datetime	日期时间，格式：年-月-日 时:分:秒

2. DDL数据定义

2.1 数据库

查看据库
show databases;

创建数据库

create database ‘数据库名称’ default charset '编码';
默认创建：create database '数据库名称';
默认创建的数据库~使用默认编码，不一定能存放中文
推荐创建：create database ‘数据库名称’ default charset "utf8";
按照指定的utf8编码创建一个数据库。

删除数据库
　　通过drop database 数据库名称 命令，可以直接删除数据库及数据库中的所有数据。
drop database 数据库名称;

使用指定的数据库
指定当前用户正在操作那个数据库。
use 数据库名称;

2.2 表

查看当前数据库中的所有表
show tables;

查看表格的创建信息
1）、 desc 表格名称； 查看表达信息

2）、 show create table 表格名称; 查看表格的创建信息

建表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options] [select_statement]


create table [if not exists] 表格名称 (
列名称  类型名称  comment 注释说明,
列名称2 ……
) [default charset 'utf8'];

示例：

create table Person(
     id int auto_increment primary key comment ‘用户编号’,
     name varchar(20) not null comment ‘用户姓名’,
     age int comment ‘用户年龄’,
     addtime date comment '添加时间'
)

删除表
drop table [if exists] 表名;

表格重命名

# 第一种
alter table tab_name rename to new_tab_name;

#第二种
RENAME TABLE oldtablename TO newtablename;

表格中修改列名称及类型

# 必须指定新列的数据类型
alter table tab_name change column name nickname varchar(50);  

修改字段类型

alter table 表名 modify column 字段名 类型;

注意修改类型应该谨慎，因为类型之间数据需要转换，可能会导致数据出错或者丢失，如果要将同样类型的数据字段增加长度是没问题的，也是经常使用的，但是减小长度可能会导致数据丢失。

表格中删除一列

alter table tab_name
    drop column marriage; # 删除名称为marriage的一列

表格中增加一列

alter table tab_name
    add column phone char(11); # 增加一列

3. DML数据操作

3.1 增

增加数据
INSERT语句 ：用于在表格中增加数据

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


# 增加一条数据
#语法：insert into 表格名称[(属性列表)] values(值列表);

insert into person(id, name, age) values(1, ‘tom’, 18);
insert into person values(2, ‘jerry’, 19);
insert into person(id, name) values(3, “shuke”);


# 添加多条数据
# 语法：
INSERT INTO 表名[(字段名1，字段名2，…)]
　　　VALUES （值1，值2，…），（值1，值2，…）,
　　　…
　　　（值1，值2，…);
# 示例
INSERT INTO person VALUES
　　　　(5，'a',99),
　　　　(6,'b',87),
　　　　(8,'c',76);

3.2 删

语法：
DELETE FROM 表名 [WHERE 条件表达式]
删除部分数据
即删除指定的部分数据，需要使用WHERE子句来指定删除记录的条件。

# 举例：删除student表中的id值为7的记录
DELETE  FROM student
	WHERE id=7;

删除全部数据

若 DELETE 语句中没有使用WHERE语句，则会将表中所有记录都删除。
语法：
DELETE FROM 表名

# 举例：删除student表中的所有记录
DELETE FROM student；

删除全部数据的另一种方法——TRUNCATE

语法：
TRUNCTE [TABLE ] 表名

举例：删除student表
TRUNCATE TABLE student；

注意：
（1）DELETE 后面可以跟WHERE子句指定删除部分记录，TRUNCATE只能删除整个表的所有记录
（2）使用TRUNCATE语句删除记录后，新添加的记录时，自动增长字段会默认从1开始，而使用DELETE删除记录后，新添加记录时，自动增长字段会从删除之前该字段的的最大值加1开始计算（即原来的id最大为5，则会从6开始计算）。所以如果是想彻底删除一个表的记录而且不会影响到重新添加记录，最好使用TRUNCATE来删除整个表的记录。

3.3 改

更新数据指对表中现存的数据进行修改。

# 语法：
UPDATE 表名
	SET 字段名1=值1，[ ，字段名2=值2，…]
	[ WHERE 条件表达式 ]；

# 示例
UPDATE student SET name='a',grade=50 WHERE id=1;

3.4 查

查询数据
SELECT语法：用于查询数据表的中的数据。

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr, ...
    [INTO OUTFILE 'file_name' export_options
      | INTO DUMPFILE 'file_name']
    [FROM table_references
    [WHERE where_definition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_definition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

# 示例：查询student表并显示所有字段信息
select * from student;

关键词顺序
出现的大量的关键词，什么关键词应该写在前面，什么关键词应该写在后面！格式如下：

SELECT
    指定查询列
    [FROM 表名称
    (1)[WHERE 条件]
    (2)[GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    (3)[HAVING 分组条件]
    (4)[ORDER BY {col_name | expr | position}
      [ASC | DESC] , ...]
    (5)[LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

查询结果排序

select 字段名 from 表名 
	order by 字段名 [[desc | asc],字段名 [desc | asc],... ]

默认升序，desc降序,asc升序，多个字段用逗号隔开。

带数字的字符串类型排序

# 默认排序
mysql> select id from test order by id;
+------+
| id   |
+------+
| 1    |
| 10   |
| 11   |
| 2    |
| 3    |
+------+
5 rows in set (0.00 sec)

# 降序
mysql> select id from test order by id desc;
+------+
| id   |
+------+
| 3    |
| 2    |
| 11   |
| 10   |
| 1    |
+------+
5 rows in set (0.00 sec)
# 由此可以发现上面的排序是仅仅是按照要排序字段的第一位来排序的。

# 解决方法可以通过 "order by 字段名+0" 的方法解决，转化为int类型，
# 将得到的int类型数据进行排序。
mysql> select id from test order by id+0 desc;
+------+
| id   |
+------+
| 105  |
| 11   |
| 11aa |
| 11bb |
| 10   |
| 3    |
| 2    |
| 1    |
+------+
8 rows in set, 2 warnings (0.00 sec)

如果是中文则按照笔画排序。

** 时间函数 **

• 加：DATE_ADD(date,INTERVAL expr type)
• 减：DATE_SUB(date,INTERVAL expr type)

expr指定数量；
type可以是year、month、day。。。

例：

SELECT * FROM 表名 WHERE 时间字段>=DATE_SUB('2019-04-02',interval 30 day);  
SELECT * FROM 表名 where date(时间字段名)>=DATE_SUB(CURDATE(), INTERVAL 7 DAY);  -- 最近7天数据

NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间

4.导入数据

文本文件

load data local infile “c:/data.tsv” 
into table capice_snvs(chr, pos, ref, alt, score) 
IGNORE 1 LINES 
character set utf-8 
fields terminated by '\t' 
lines terminated by'\n';

IGNORE 1 LINES 忽略第一行数据
fields terminated by '\t’指定数据列分隔符
lines terminated by '\n’指定数据行分隔符

5. 注释

• 单行注释

SQL语句中的单行注释使用"--"

create database database_x    --创建数据库database_x

• 多行注释

SQL语句中的多行注释采用"/*…*/"

create database a
/*
多行注释
创建一个数据库a
*/

---