数据库

mysql 基础知识(4) – 用户和权限

创建用户

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';

授权

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'%';

其中的 % 代表这个用户可以在任意主机登录.

SHOW GRANTS FOR newuser

用来显示一个用户的当前授权

set password for USERNAME = password('xxx')

更改用户密码

重置密码

sudo mysqld_safe --skip-grant-tables --skip-networking &
 
use mysql;
update user set authentication_string=PASSWORD("") where User='root';
update user set plugin="mysql_native_password"; # THIS LINE
flush privileges;
quit;

其他问题

EXPLAIN, slow-log

解决小内存机器 MySQL 总是 OOM 的问题

有一台512M内存的小机器总是报数据库错误, 查看了下日志是OOM了

解决方案:

一 Add swap file to cloud instance

http://www.prowebdev.us/2012/05/amazon-ec2-linux-micro-swap-space.html

  1. Run dd if=/dev/zero of=/swapfile bs=1M count=1024
  2. Run mkswap /swapfile
  3. Run swapon /swapfile
  4. Add this line /swapfile swap swap defaults 0 0 to /etc/fstab  

Some useful command related to SWAP space:

$ swapon -s   
$ free -k
$ swapoff -a
$ swapon  -a

二 limit mysql buffersize

innodb_buffer_pool_size = 8M

三 limit apache memory cosumption,editing /etc/apache2/mods-enabled/mpm_prefork.conf

<IfModule mpm_prefork_module>
    StartServers        3
    MinSpareServers     3
    MaxSpareServers     5
    MaxRequestWorkers   25
    MaxConnectionsPerChild  0
</IfModule>

mysql 基础知识(3) – 创建修改表和权限

创建数据库

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

创建表?

CREATE TABLE table_name (
    field_name type is_null default options,
    ...
    PRIMAR KEY (id),
    INDEX/KEY index_name (field_name),
) ENGINE=InnoDB;
 
// 注意:KEY is normally a synonym for INDEX

设定 auto_increment

注意 mysql 的关键字是 auto_increment, 而 sqlite 的是 autoincrement

CREATE TABLE(...) AUTO_INCREMENT=xxx;

更改已经存在的表

ALTER TABLE SET AUTO_INCREMENT=xxx;

 

数据类型

字符串

字符串分两种,定长和变长,MySQL处理定长数据比变长数据快得多。CHAR属于定长类型,VARCHAR和TEXT属于变长类型。

  • CHAR的长度为1-255,默认为1,使用CHAR(n)指定长度
  • VARCHAR为0-255,使用VARCHAR(n)指定长度
  • TEXT为65536,MEDIUMTEXT为16k,LONGTEXT为4GB

数字

注意数字后面跟的数字,例如INT(5),并不是限制数字的存储长度,而是限制数字的展示长度(显示时填充0)!可以使用UNSIGNED指定为非负值,默认为signed

日期

使用DATETIME,不要使用TIMESTAMP,防止2038年溢出

tips

创建 modify_time/update_time 字段时使用自动更新时间

`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

设计表要注意每个字段的正交性,不要出现一个字段表示“xx且xx”的状态。

 

更新表

rename table 'old_name' to 'new_name'

添加一列,其中的 COLUMN 关键字是 optional 的。

ALTER TABLE table_name ADD COLUMN field_name type;

更新某个字段的数据类型

Alter TABLE `tableName` MODIFY COLUMN `ColumnName` datatype(length);

比如说:

Alter TABLE `tbl_users` MODIFY COLUMN `dup` VARCHAR(120);

重命名一列

需要注意的是数据类型也需要带上

alter table `user` change `name` `first_name` varchar(128) default null;

添加不同类型的索引

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE index_name (column_list)

ALTER TABLE table_name ADD PRIMARY KEY index_name (column_list)

需要注意的是 mysql 索引的最大长度是 255,也就是在 VARCHAR(255) 以上的列是不能添加索引的,一个改进方法就是另外添加一列存储这一列的 hash 值。

删除字段

删除索引

alter table TABLENAME drop index xxxx

——————之前笔记的分割线————————
 
 

组合索引

 
如果有一个组合索引(col_a,col_b,col_c)
 
下面的情况都会用到这个索引:

col_a = "some value";
col_a = "some value" and col_b = "some value";
col_a = "some value" and col_b = "some value" and col_c = "some value";
col_b = "some value" and col_a = "some value" and col_c = "some value";

对于最后一条语句,mysql会自动优化成第三条的样子
 
下面的情况就不会用到索引:

col_b = "aaaaaa";
col_b = "aaaa" and col_c = "cccccc";

mysql 基础知识(2) – 增删改查

我们还是使用上篇文章定义的例子来说明问题

插入数据

使用 INSERT 语句.

insert into students(**field_names) values(**VALUES), values(**VALUES);

批量插入

如果一次要插入所有数据的话, 可以直接省略前面的字段名. MySQL 可以一次插入多行数据或者一行数据, 但是这并不是SQL标准规定的. 使用批量插入可以大幅度提高性能。

在批量插入的语句中,如果有一行是错的,那么就会导致整个插入失败,可以使用 insert ignore 语句。

如果要在批量插入不同的表,可以一次执行多个语句,而不是只执行一个。

insert or update

如果要实现 insert or update 的功能,可以使用 insert on duplicate update 语句。

更新数据

使用 UPDATE 语句.

update students set math_score = 100 where first_name = 'luke';

删除数据

使用 DELETE 语句.

delete from students where name = 'luke';

参考

  1. http://www.mysqltutorial.org/mysql-insert-ignore/

mysql 基础知识(1) – 查询数据

最全总结

显示操作

show databases;
show create database DB;
show tables;
show columns from TABLE;
show create table TABLE;
show status;
shou grants;

连接数据库

mysql -u root -p -h HOST -P 3306, 其中的参数代表了用户, 机器地址和密码等, 注意其中的密码必须之后输入, 而不能直接带上, 这样是为了避免把密码记录在bash历史中.

另一个更好的工具是Python写的mycli, 谁用谁知道~

使用 use DATABASE来更改指定的数据库.

查询数据库

下面的部分都使用这个表, 方便讨论.

id first_name last_name math_score english_score grade
1 master yoda 100 100 9
2 obiwan kenobi 70 100 8
3 luke skywalker 100 70 7
4 leia skywalker 90 90 7

select field from table, 或者 select * from table. 好多时候往往犯懒直接写星号, 但是检索无关的列会降低数据库的效率.

有一个比较有趣的关键字, 叫做DISTINCT, 顾名思义, 就是不同的意思, 如果使用这个关键字作为前缀的话, MYSQL会做一个去重的操作, 有点类似于uniq命令.

select distinct vendor_id from products

如果需要限定查询的结果, 使用 LIMIT 和 OFFSET 两个关键字. LIMIT 限定了返回的结果数量, OFFSET 指定了从那一条开始返回.

select name from students limit 5 offset 3;  -- 从所有学生中选出从第3个开始的5个学生

排序

一般来说, mysql返回的结果是按照主键排序的, 如果要让结果按照某个键排序, 使用 ORDER BY 关键字来给他们排序

select name from students order by score

默认情况下, mysql是按照升序排列的, 也就是小的在前面, 如果需要大的在前面, 使用 DESC 关键字

select name from students order by score desc;

如果排序的字段相同怎么办呢, 还可以按多个字段排序, 这时候需要注意的是desc只对一个列生效.

select name from students order by score desc, name;

过滤结果

可以使用 WHERE 子句过滤结果, 比如

select * from students where score > 100;

值得注意的是, 如果要比较的是null, 那么不能使用 = 来比较, 需要使用 IS

select * from students where name is null;

还可以使用 AND, OR, NOT 来计算复合表达式.

使用 IN 来表达在某些值之间, 就像在Python中一样.

select * from students where name in ('yoda', 'obiwan', 'luke');

使用 LIKE 来匹配结果, % 表示任意字符出现任意次数, _ 表示任意一个字符出现一次.

select * from students where name like '%walker';

函数与计算字段

MySQL 中有一些常见的字符串处理函数, concat, trim 等等, 这些函数在常见的编程语言中都有, 在MySQL中作用大概也是相同的. 还可以使用 AS 来个计算出的字段来起一个别名, 这样方便输出. 如果是数字的话, 还可以做数学运算.

select concat(first_name, last_name) as name from students;

select math_score + english_score as score from students;

select * from students where year(enroll_time) = 2009;

聚合与分组数据

之前说的一些函数都是把一个值变成了另一个值, 也就是使用或者不使用这些函数得到的都是相同行数的数据. 而使用聚合或者分组函数之后, 会改变得到的数据的大小.

聚集函数常用的一共有五个: AVG(), COUNT(), MAX(), MIN(), SUM(). 这几个函数的意思看名字也应该知道了…直接上例子了.

select avg(math_score) as average_math_score from students;  -- 90, null 会被忽略
select count(*) from students; -- 3, null会被忽略

分组函数

使用 group by 关键字可以让所有的行按照某个值, 聚合成相应的行, 比如在我们的数据中有3个年纪(grade), 那么group by(grade) 之后就会变成3行.

select grade, count(*) as num_students from students group by grade;

需要注意的是SQL的计算顺序是先计算 WHERE 子句, 然后才回去使用 GROUP BY 聚合, 那么如果想要过滤GROUP BY 分组之后的数据呢, 这时候可以使用 HAVING 子句.

select grade, count(*) as num_students from students group by grade having count(*) > 1;

子查询

子查询其实很简单, 就是一个查询的结果是另一个查询的数据基础. 和函数调用有一些相似.

第一种常见用法, 把子查询的结果用在 IN 中.

select * from students where id in (select sudent_id from student_awards);

第二种使用子查询来作为结果列的数值.

select id, (select count(*) from student_awards where students.id = student_awards.student_id) from students;

Join

Join 太复杂了, 未完待续

数据类型

TINYBLOB, TINYTEXT       L + 1 bytes, where L < 2^8    (255 Bytes)
BLOB, TEXT               L + 2 bytes, where L < 2^16   (64 Kibibytes)
MEDIUMBLOB, MEDIUMTEXT   L + 3 bytes, where L < 2^24   (16 Mebibytes)
LONGBLOB, LONGTEXT       L + 4 bytes, where L < 2^32   (4 Gibibytes)

史上最全的 mysql 总结:https://mp.weixin.qq.com/s/87BoE2-0mW_3qALyNSpiTw

MySQL 备份与恢复

基础使用

帮助命令很简单

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

备份

mysqldump -u root --password=xxx DB_NAME [TABLE_NAME] > backup.sql

--password 可以直接在命令中使用密码

可以选择只 dump 一个数据库或者一个表。

恢复

mysql -u root -p DB_NAME < backup.sql

一行操作

mysqldump -u root -pPassword --all-databases | ssh user@new_host.host.com 'cat - | mysql -u root -pPassword'

问题

如果直接备份所有数据库并恢复会更改 root 密码, 并且导致内部数据库不一致, 可以使用如下命令修复:

mysql_upgrade --force -uroot -p

参考:

  1. https://stackoverflow.com/questions/43846950/column-count-of-mysql-user-is-wrong-expected-42-found-44-the-table-is-probabl