MySQL学习笔记

来源：互联网发布：ubuntu 安装无线网卡编辑：程序博客网时间：2024/06/06 23:18

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

#查询所有记录条数COUNTmysql> select count(*) from students;#查询数据表中有多少条内容不重复记录；DISTINCTmysql> 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