[sql]mysql管理手头手册,多对多sql逻辑
各类dbms排名
cs模型
mysql字符集设置
查看存储引擎,字符集
show variables like '%storage_engine%';
show VARIABLES like '%max_allowed_packet%';
show variables like '%storage_engine%';
show variables like 'collation_%';
show variables like 'character_set_%';
临时设置字符集
SET NAMES 'utf8';
SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_database = utf8;
SET character_set_results = utf8;
SET character_set_server = utf8;
SET collation_connection = utf8_general_ci;
SET collation_database = utf8_general_ci;
SET collation_server = utf8_general_ci;
缺陷是: 登出后设置的就失效了.
永久设置字符集
参考: http://blog.csdn.net/u013474104/article/details/52486880
D:\Program Files\mysql-5.7.19-winx64\my.ini
[mysqld]
default-storage-engine = innodb
innodb_file_per_table = 1
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
[mysql]
default-character-set = utf8
[mysql.server]
default-character-set = utf8
[mysqld_safe]
default-character-set = utf8
[client]
default-character-set = utf8
mysql配置文件
- 配置文件查找先后顺序
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
C:\windows\my.ini
C:\my.ini
E:\dev\mysql57\my.ini
- 通过配置文件设置字符集
default-storage-engine = innodb
innodb_file_per_table = 1
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
mysql crud语句
- 建表
create table log(ip varchar(40), status int, count int);
- 插入记录
insert into log values ('1','1',1)
- 删除某条记录
delete from mysql.user where user='';
- 查看建表语句
show create database test;
- 查看权限
show grants
- 创建用户并授权
create database bbs;
grant all on bbs.* to dev@localhost identified by '123456';
grant all on bbs.* to dev@'%' identified by '123456';
er图与实体关系映射
不得不说,这个鬼东西是上大学老师讲的图,类似. 坑了我不少年. 当初就不想学这玩意. 后来自己弄,发现并不是那回事,大学里老师讲的类似隔靴搔痒,泯灭了我对sql的兴趣. 这里我重拾sql,不用看这部分了.
数据库设计(一对一、一对多、多对多）
关联映射：一对多/多对一
存在最普遍的映射关系，简单来讲就如球员与球队的关系；
一对多：从球队角度来说一个球队拥有多个球员 即为一对多
多对一：从球员角度来说多个球员属于一个球队 即为多对一数据表间一对多关系如下图：
直观上给人的感觉是, 建一个表时候,觉得一个字段冗余了,独立出去,通过外链方式来访问
这里可以使用navicat操作: 链接：https://pan.baidu.com/s/1hr83YCG 密码：qmi7
- 建库
create database learn;
- 创建球队表
create table qiudui(
id int auto_increment primary key not null,
name varchar(20) not null,
localtion varchar(50) not null
);
- 给球队表插入数据
insert into qiudui(name,localtion) values('qd1', 'xian');
insert into qiudui values(2, 'qd2', 'beijing');
insert into qiudui values(3, 'qd3', 'shanghai');
- 创建球员表, 外键到球队
create table qiuyuan(
id int auto_increment primary key not null,
name varchar(20),
qiudui_id int,
FOREIGN KEY (qiudui_id) REFERENCES qiudui(id)
);
- 给球队表插入数据
insert into qiuyuan values(1,'qy1',1);
insert into qiuyuan values(2,'qy2',2);
insert into qiuyuan values(3,'qy3',3);
- 默认外键策略: 球队表里的记录不能删除,除非与之关联的球员没了
delete from qiudui where id=3;
关联映射：一对一
一对一关系就如球队与球队所在地址之间的关系，一支球队仅有一个地址，而一个地址区也仅有一支球队。
数据表间一对一关系的表现有两种，一种是外键关联，一种是主键关联。
一对一外键关联，图示如下：
一对一主键关联：要求两个表的主键必须完全一致，通过两个表的主键建立关联关系。图示如下：
关联映射：多对多
多对多关系也很常见，例如学生与选修课之间的关系，一个学生可以选择多门选修课，而每个选修课又可以被多名学生选择。
数据库中的多对多关联关系一般需采用中间表的方式处理，将多对多转化为两个一对多。
数据表间多对多关系如下图：
[django]主次表如何取出对方数据[主表obj.子表__set()]
navicat
链接：https://pan.baidu.com/s/1hr83YCG 密码：qmi7
使用navicat导出表内容为execl或者json
使用navicat查看ER图(即一对一一对多关系图)
mysql时间字段
mysql运维
总体指引
mysql指引
1,mysql基本安装
2,mysql多实例安装与维护
3,备份恢复
备份数据库
分备数据库
分备表
恢复数据库
阿里云sql某个表的导入导出
msyqldump -h 192.168.1.1 -uroot -p --set-gtid-purged=off --skip-tz-utc bbs bbs_tb1 > bbs_tb1.sql
mysql -uroot -p < bbs_tb1.sql
centos7 安装mysql
yum install mariadb-server mariadb -y
修改my.conf
[mysqld]
default-storage-engine = innodb
innodb_file_per_table
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
systemctl start mariadb && systemctl enable mariadb
linux下查看nginx，apache，mysql，php的编译参数
1、查看linux的内核版本，系统信息，常用的有四种办法：
uname -a
more /etc/issue
cat /proc/version
more /etc/redhat-release
2、nginx编译参数：
#/usr/local/nginx/sbin/nginx -V
3、apache查看版本编译参数
apache版本：
httpd -v
apache编译参数：
# cat /usr/local/apache2/build/config.nice
4、查看php版本及编译参数
php编译参数：
# /usr/local/php/bin/php -i |grep configure
php -v
5、查看mysql版本及参数
查看mysql版本：
mysql -v 或者是--help|grep Distrib来查看；
进入数据库
select version（）； 或者status;或者/s;
mysql编译参数：
# cat "/usr/local/mysql/bin/mysqlbug"|grep configure
mysql初始化安装完成后优化
mysql安装方式: 不管哪种方式装完成数据库,都需要对数据库做一些优化.
- 1,yum
- 2,源码
- 3,二进制
- 4,源码+yum
mysql> select host,user from mysql.user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1 | root |
| localhost | |
| localhost | root |
| www | |
| www | root |
+-----------+------+
6 rows in set (0.00 sec)
- 删掉用户名为null的
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)
mysql> select host,user from mysql.user;
+-----------+------+
| host | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1 | root |
| localhost | root |
| www | root |
+-----------+------+
4 rows in set (0.00 sec)
drop user ''@'localhost';
drop user ''@'::1';
drop user 'root'@'lanny';
drop user ''@'lanny';
mysql> select user,host from mysql.user;
mysql>
# 有时候drop删不掉(可能包含特殊字符),才会用delete命令
- 删掉ipv6的:
mysql> delete from mysql.user where host='::1';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
| root | www |
+------+-----------+
3 rows in set (0.00 sec)
- 删掉www的
mysql> delete from mysql.user where host='www';
Query OK, 1 row affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
2 rows in set (0.00 sec)
mysql>
- 删掉test库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
查看库/表大小和修改时间
1.查看数据库的大小
use 数据库名
SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';
得到的结果是以字节为单位，除/1024为K，除/1024/1024为M。
2.查看表的最后mysql修改时间
select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='数据库名';
可以通过查看数据库中表的mysql修改时间，来确定mysql数据库是否已经长期不再使用。
3.查看数据库中各个表占用的空间大小
如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话，可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表，这个表主要字段分别是：
TABLE_SCHEMA : 数据库名
TABLE_NAME ：表名
ENGINE ：所使用的存储引擎
TABLES_ROWS ：记录数
DATA_LENGTH ：数据大小
INDEX_LENGTH ：索引大小
其他字段请参考MySQL的手册，我们只需要了解这几个就足够了。
所以要知道一个表占用空间的大小，那就相当于是 数据大小 + 索引大小 即可。
SQL:
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'
4.查看表的最后mysql修改时间
select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='数据库名';
可以通过查看数据库中表的mysql修改时间，来确定mysql数据库是否已经长期不再使用。
