MySQL学习笔记

最新推荐文章于 2022-12-11 19:27:27 发布

千寻千梦

最新推荐文章于 2022-12-11 19:27:27 发布

阅读量775

点赞数

分类专栏： sql 数据库相关

本文链接：https://blog.csdn.net/qq_34531825/article/details/52468007

版权

数据库相关同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

sql

2 篇文章 0 订阅

订阅专栏

MySQL在线帮助文档：http://dev.mysql.com/doc/refman/5.7/en/
MySQL完整中文教程：http://www.runoob.com/mysql/mysql-tutorial.html

MySQL数据类型

完整数据类型请参考：http://www.runoob.com/mysql/mysql-data-types.html
数值型

Name	字节	有符号	无符号	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值

字符串

Name	范围	说明
CHAR	0-255字节	定长字符串
TEXT	0-65 535字节	长文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

从文件中导入表

更完整的信息请参考http://www.jb51.net/article/58093.htm

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE] #使用IGNORE对于冲突的数据丢弃掉。
    INTO TABLE tbl_name #会保留原有所有记录，不管重复与否
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

mysql> create table wiki2(id BIGINT,name Char(255));

mysql> LOAD DATA LOCAL INFILE "E:/data/graphx-wiki-vertices.txt" 
    -> IGNORE #使用IGNORE对于冲突的数据丢弃掉。
    -> into table wiki2 #会保留原有所有记录，不管重复与否
    -> fields terminated by " "
    -> LINES terminated by "\n";

复制表

create table select 会将原表中的数据完整复制一份，但表结构中的索引会丢失。
create table like 只会完整复制原表的建表语句，但不会复制数据

mysql> CREATE table LargeAreaCountry SELECT * from myselect;

mysql> CREATE table LargeAreaCountry2 LIKE myselect;

建立索引

主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。外键：外键用于关联两个表。复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
查看表中已经存在 index：show index from table_name;
索引的创建可以在CREATE TABLE语句中进行，也可以单独用CREATE INDEX或ALTER TABLE来给表增加索引。删除索引可以利用ALTER TABLE或DROP INDEX语句来实现。
（1）使用ALTER TABLE语句创建索引。
alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;
其中包括普通索引、UNIQUE索引和PRIMARY KEY索引3种创建索引的格式，table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以同时创建多个索引。

mysql> alter table tpsc add index shili (tpmc ) ;

（2）使用CREATE INDEX语句对表增加索引。
能够增加普通索引和UNIQUE索引两种。其格式如下：
create index index_name on table_name (column_list) ;
create unique index index_name on table_name (column_list) ;

（3）删除索引。
删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

SELECT


#查询所有记录条数COUNT
mysql> select count(*) from students;

#查询数据表中有多少条内容不重复记录；DISTINCT
mysql> select count(distinct(name)) from students;

#也可以进行一些计算；
mysql> select count(*)/count(distinct(name)) from students;

#限制查询结果中数据列的个数
mysql> select id,name from students;

#限制查询结果中数据记录的个数
mysql> select id,name from students limit 2;

Join

原始表

students表：
+------+--------+-----+
| id   | name   | age |
+------+--------+-----+
| 2510 | wangli |  19 |
| 2514 | legity |  45 |
| 2521 | lesssa |  21 |
| 2530 | Jane   |  50 |
| 2531 | Kitty  |  30 |
| 2535 | 李伟   |  28 |
| 3300 | Sam    |  30 |
+------+--------+-----+

class 表：
+-------+--------+
| class | name   |
+-------+--------+
| 23001 | Jane   |
| 23001 | Kitty  |
| 23001 | 李伟   |
| 23002 | Sam    |
| 23002 | lesss2 |
| 23002 | lessdd |
+-------+--------+

左连接，以左表为准

select * from students left join class on students.name=class.name;

+------+--------+-----+-------+--------+
| id   | name   | age | class | name   |
+------+--------+-----+-------+--------+
| 2530 | Jane   |  50 | 23001 | Jane   |
| 2531 | Kitty  |  30 | 23001 | Kitty  |
| 2535 | 李伟   |  28 | 23001 | 李伟    |
| 3300 | Sam    |  30 | 23002 | Sam    |
| 2510 | wangli |  19 |  NULL | NULL   |
| 2514 | legity |  45 |  NULL | NULL   |
| 2521 | lesssa |  21 |  NULL | NULL   |
+------+--------+-----+-------+--------+

右连接，以右表为准

select * from students  right join class on students.name=class.name;

+------+--------+------+-------+--------+
| id   | name   | age  | class | name   |
+------+--------+------+-------+--------+
| 2530 | Jane   |   50 | 23001 | Jane   |
| 2531 | Kitty  |   30 | 23001 | Kitty  |
| 2535 | 李伟   |   28 | 23001 | 李伟    |
| 3300 | Sam    |   30 | 23002 | Sam    |
| NULL | NULL   | NULL | 23002 | lesss2 |
| NULL | NULL   | NULL | 23002 | lessdd |
+------+--------+------+-------+--------+

默认为内连接 innner，两表交叉（同时存在时才显示）

select * from students join class on students.name=class.name;
+------+--------+-----+-------+--------+
| id   | name   | age | class | name   |
+------+--------+-----+-------+--------+
| 2530 | Jane   |  50 | 23001 | Jane   |
| 2531 | Kitty  |  30 | 23001 | Kitty  |
| 2535 | 李伟   |  28 | 23001 | 李伟    |
| 3300 | Sam    |  30 | 23002 | Sam    |
+------+--------+-----+-------+--------+

删除表

mysql> drop table myselect; #删除整个表

mysql> delete from temp_wiki;#删除表的内容，但是仍然保留表的结构

数据去重

下面测试数据约9万行数据，4倍的重复数据。

#distinc：下面是当所有字段一样时，才进行去重，如果数据量大，不进行优化，将会非常非常慢。
mysql> create table temp_wiki select distinct * from wiki;
Query OK, 22424 rows affected (5 min 6.19 sec)
Records: 22424  Duplicates: 0  Warnings: 0


#group by对某个字段去重，还是非常慢，稍微快一点，还是远远达不到要求
# MySQL中 使用 GROUP BY 关键字用于对某个或某些字段查询分组，并返回重复记录的第一条。
mysql> create table temp_wiki2 select * from wiki group by id;
Query OK, 22424 rows affected (4 min 16.14 sec)
Records: 22424  Duplicates: 0  Warnings: 0

#建立索引后再操作
mysql> create index index_id on wiki(id);#index_id 是自定义的索引名
mysql> create table temp_wiki4 select * from wiki group by id;
Query OK, 22424 rows affected (6 min 41.90 sec)
Records: 22424  Duplicates: 0  Warnings: 0

#插入到一个定义好的空表中，似乎更慢一些。
mysql> insert into temp_wiki select * from wiki group by id;
Query OK, 22424 rows affected (6 min 2.21 sec)
Records: 22424  Duplicates: 0  Warnings: 0

下面测试是25697行数据，有3273行数据重复


mysql> create table temp select distinct * from wiki;
Query OK, 22424 rows affected (6.03 sec)
Records: 22424  Duplicates: 0  Warnings: 0

应该还有更好的方法在MySQL中去重，对于大数据，不要直接在MySQL中操作去重，还是应用Spark-SQL,Hive,HBase等工具吧。对小数据集，处理速度还是可以的。

聚合函数

所有聚合函数请参考：http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

Name	Description
AVG()	返回指定列的平均值
COUNT()	返回指定列中非NULL值的个数
COUNT(DISTINCT)	返回指定列中非重复值非NULL值的个数
MAX()	返回指定列的最大值
MIN()	返回指定列的最小值
SUM()	返回指定列的所有值之和
STD()	返回总体标准差
STDDEV()	返回总体标准偏差（同上）
STDDEV_POP()	返回总体标准偏差（同上）
STDDEV_SAMP()	返回样本标偏差
GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果
VAR_POP()	返回总体方差
VARIANCE()	返回总体方差（同上）
VAR_SAMP()	返回样本方差

mysql> select count(1),min(id),max(id),avg(id) from wiki;

Count(1)和Count()实际上的意思是，评估Count（）中的表达式是否为NULL，如果为NULL则不计数，而非NULL则会计数。建议不使用Count( )而是使用Count（1）。

mysql> select count(1) from wiki where id>5000000000000000000;

+----------+
| count(1) |
+----------+
|     9440 |
+----------+
1 row in set (0.01 sec)

mysql> select std(id),stddev(id),stddev_pop(id) from city;
+--------------------+--------------------+--------------------+
| std(id)            | stddev(id)         | stddev_pop(id)     |
+--------------------+--------------------+--------------------+
| 1177.5058386267121 | 1177.5058386267121 | 1177.5058386267121 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> select var_pop(id),variance(id) from city;
+--------------------+--------------------+
| var_pop(id)        | variance(id)       |
+--------------------+--------------------+
| 1386519.9999999965 | 1386519.9999999965 |
+--------------------+--------------------+

 var_pop(id)是stddev_pop(id)的平方。
1 row in set (0.00 sec)

千寻千梦

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习笔记

MySQL完整教程：http://www.runoob.com/mysql/mysql-tutorial.htmlMySQL数据类型：从文件中导入表更完整的信息请参考http://www.jb51.net/article/58093.htmLOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLA
复制链接

扫一扫

专栏目录