MySQL 常用功能(索引,权限管理)

索引

• 查看索引

  show index from 表名

• 创建索引

  create index 索引名称 on 表名(字段名称(长度))

• 删除索引

  drop index 索引名称 on 表名

• 开启运行时间监测

  set profiling=1

• 查看执行时间

  show profiles

权限

• 查看user表

  desc user

• 查看所有用户

  select host,user,authentication_string from user;

  host主机,user用户名,authentication_string为加密后密码

• 创建账户&授权

  grant 权限列表 on 数据库 to '用户名'@'主机' identified by '密码'

• 查看用户权限

  show grants for 用户名@主机;

• 创建所有表所有ip权限用户

  grant all privileges on 库名.* to '用户名'@'%' identified by '密码';

• 修改权限

  grant 权限名称 on 数据库 to 账户@主机 with grant option;

• 修改密码

  update user set authentication_string=password('新密码') where user='用户名';

• 刷新权限

  flush privileges

• 远程登陆暴力方式(慎用)

  /etc/mysql/mysql.conf.d/mysqld.cnf

  注释掉当前文件中 ping-address = 127.0.0.1

• 删除用户

  drop user '用户名'@'主机';

  delete from user where user='用户名';

配置主从

1. 备份

   mysqldump -u用户名 -p密码 --all-databases --lock-all-tables > ~/master_db.sql

2. 复制(同步之前文件)

   mysql –u用户名 –p密码 < master_db.sql

3. 修改配置文件(主)

   sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

   83和84行(大约位置):

   server-id  = 1
   log_bin  = /var/log/mysql/mysql-bin.log
   

4. 重启

   sudo service mysql restart

5. 创建子账号(参上文)

6. 查看主服务器二进制日志信息

   show master status;

   File 为使用的日志文件名字,Position为使用的文件位置

7. 配置从机

   更改service-id = 2

8. 重启

9. 链接主机

   change master to master_host='x.x.x.x', master_user='', master_password='',master_log_file='第6步File字段中数据', master_log_pos=第6步Position字段数据;
   

10. 开启同步(从)

    start slave;

11. 查看同步状态

    show slave status \G

其他

SQL语句主要分为：

• DQL：数据查询语言，用于对数据进行查询，如select

• DML：数据操作语言，对数据进行增加、修改、删除，如insert、udpate、delete

• TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback

• DCL：数据控制语言，进行授权与权限回收，如grant、revoke
• DDL：数据定义语言，进行数据库、表的管理等，如create、drop
• CCL：指针控制语言，通过控制指针完成表的操作，如declare cursor

常用数据类型

数值类型(常用)

类型	字节大小	有符号范围(Signed)	无符号范围(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

字符串

类型	字节大小	示例
CHAR	0-255	类型:char(3) 输入 ‘ab’, 实际存储为’ab ‘, 输入’abcd’ 实际存储为 ‘abc’
VARCHAR	0-255	类型:varchar(3) 输 ‘ab’,实际存储为’ab’, 输入’abcd’,实际存储为’abc’
TEXT	0-65535	大文本

日期时间类型

类型	字节大小	示例
DATE	4	‘2020-01-01’
TIME	3	‘12:29:59’
DATETIME	8	‘2020-01-01 12:29:59’
YEAR	1	‘2017’
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

三范式

目前大约有8中范式,只需要了解三范式即可

• 第一范式(1NF)

  强调的时列的原子性,即列不能够再分成其他几列

• 第二范式

  首先是1NF,另外包含两部分内容,一是表必须有一个主键,而是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分

• 第三范式

  首先是2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖,即不能存在:非主键列A依赖于非主键列B,非主键列B依赖于主键的情况

事务

• 开启事务

  begin;

  或start transaction;

• 提交事务

  commit;

• 事务回滚

  rollback;

初级防止SQL注入的方式: 构建参数列表,传入查询语句中

# 构造参数列表
params = [find_name]
# 执行select语句，并返回受影响的行数：查询所有数据
count = c.execute('select * from goods where name=%s', params)