什么是mysql

MySQL是一种开源的关系型数据库管理系统，使用“结构化查询语言(sql)”进行数据库管理。
MySQL由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。
MySQL是最流行的关系型数据库管理系统之一。

MySQL是一种关系型数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的SQL结构化查询语言是用于访问数据库的最常用标准化语言。
MySQL软件采用了双授权政策，分为社区版和企业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

虽然我们可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。
使用关系型数据库管理系统来存储和管理大数据量，会让这一切变得更简单。

mysql的标志是一只海豚，如下图 
复制代码

mysql的配置文件

1/如果是linux系统

 配置文件是/etc目录下的my.cnf文件，在[mysqld]段或者mysql的server配置段进行修改
   
复制代码

2/如果是windows系统

 配置文件是my.ini文件，在[mysqld]段或者mysql的server配置段进行修改
复制代码

3/查询mysql系统配置文件中的参数

 我们可以到具体的配置文件中去看，当然也有一些命令，使得我们可以直接看到我们想知道的参数
 show variable like '%xxxxssss%';
 例如：
    show variables like '%secure%'; 
    查看所有含有secure的参数，例如 secure-file-priv 当前的值是什么
     
复制代码

4/修改配置文件中的参数

  set global 参数名=xxx
  修改完配置文件之后要重启mysql服务，这样才能生效
复制代码

mysql服务的重启/关闭

 service mysql start     # 启动
 service mysql stop      # 停止
 service mysql restart   # 重启
 service mysql status   #  查看mysql服务的状态
 
 注意：上面4条命令中，到底是mysql还是mysqld，这个取决于你配置的service的名字，不要太纠结名字。
 cp support-files/mysql.server /etc/init.d/mysql (mysql 这个是以后服务启动时的名称)
复制代码

如何登陆mysql数据库

mysql -h host -u user -ppassword -p database -P port
需要注意的是：
  # -h参数可以不写，因为默认就是本地的数据库,也就是localhost，-h和host之间必须有空格
  # -u参数，指的是用户名，-u与用户名之间必须有空格
  # -p参数，指的是密码，与password之间不能有空格，否则报错：Access denied for user xxxx to database
  # -P参数，大写的字母P，指的是端口号port，可以不写
  # -p参数后面如果有空格，则该参数代表的是数据库名，可以不写
    如果同时存在 -p密码 -p 数据库名 需要注意先后顺序 测试发现
    正确：
       mysql -u root -p mysql -p123456  # 数据库名称在前面，密码在后面
    错误：
       mysql -u root -p123456 -p mysql
       
复制代码

修改mysql的登陆密码

<1>首先要知道mysql的密码制定策略，通过 show variables like 'validate_password%'命令查看
复制代码

<2>从上图中可以知道:
     密码要求长度是8位，(默认长度就是8位)
     密码的验证强度等级是medium(一共有3个等级low,medium,strong)；
     
<3>从上图可以看到，当前要求密码长度是8位，如果不介意就可以不用修改了。
     如果想修改，用set global validate_password_length=6
复制代码

<4>可以修改密码的验证强度
    通过set global validate_password_policy=LOW; 命令进行修改

<5>最后可以修改mysql的登录密码了
    通过ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';命令进行修改。

<6>关于mysql密码策略相关参数的介绍；
    validate_password_length: 固定密码的总长度
    validate_password_dictionary_file: 指定密码验证的文件路径,可不填写
    validate_password_mixed_case_count: 整个密码中至少要包含大/小写字母的总个数；
    validate_password_number_count: 整个密码中至少要包含阿拉伯数字的个数；
    validate_password_policy: 指定密码的强度验证等级，默认为 MEDIUM；
    validate_password_special_char_count: 整个密码中至少要包含特殊字符的个数；
       
<7> 关于validate_password_policy的取值：
    0/LOW：只验证长度；只要长度跟validate_password_length要求的一样即可。
    1/MEDIUM：验证长度、数字、大小写、特殊字符；
    2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；
    
复制代码

mysql查看用户

查看所有用户

在MySQL中有一个内置名为mysql的数据库，
这个数据库中存储的是MySQL的一些数据，比如用户、权限信息、存储过程等。
所以我们可以通过如下查询语句来查看所有用户。

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
mysql> SELECT User, Host, Password FROM mysql.user;
mysql> SELECT DISTINCT User FROM mysql.user;
复制代码

查看当前登陆用户

mysql> SELECT user();
mysql> SELECT current_user();//括号可以不要
  
  
复制代码

where语句中的and,or,in, not in

 and: and两边的条件必须同时满足
 or：or两边的条件满足其中一个就可以
 in: 必须是()括号，不能使[]括号，，建议能使用in的就不要使用or，因为in性能更好，也方便理解。
 not in : 同上。
 注意：
   在同时有and和or语句存在情况下，mysql是优先处理and语句的。一般用()来确定处理的顺序和消除歧义。
复制代码

mysql常用命令

 <1>登录数据库： 
     mysql -u xxx -pxxx  # -u 用户名 -p 密码
     
 <2>显示所有已经存在的数据库： 
     show databases;
     
 <3>进入到某个数据库
     use db_name；
     
 <4>显示目前所处的数据库中所有已经存在的表：
     show tables；
     
 <4>创建新的数据库，同时规定数据库的编码格式
     CREATE DATABASE dbname DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
     
 <5>创建数据表，同时规定编码
     CREATE TABLE 'author' ('authorid' char(20) NOT NULL,'name' char(20) NOT NULL,'age' char(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1;
     
 <6>查看表的全部数据
     select * from 表名; 
         
 <7>查看某几个字段的数据
     select id,name,age,first_dpt_name from 表名;    
      
 <8>查看某张表共有哪些字段
     show columns from 表名;
         
 <9>在table中插入记录
     insert 表名 (id,name) values (007,'xxx')
         
 <10>退出mysql
      exit
         
 <11>查看表中某一列的数据类型
      SELECT CHARSET(email) FROM tablename;
      
 <11>alter的用法
         alter table 旧的表名 rename 新的表名;         #修改表名
         alter table xxx change column 旧列名 新列名 varchar(30) # 修改列名
         alter table xxx add column name varchar(10);  #添加列
         alter table xxx drop column name;             #删除列
         alter table xxx modify address char(10)       #修改列类型
         
 <12>修改数据库/数据表的编码
       更改数据库编码：ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
       更改表编码：ALTER TABLE tbtest CONVERT TO CHARACTER SET utf8mb4 COLLATEutf8mb4_general_ci; 
       更改表编码：alter table tbtest character set utf8mb4 COLLATE utf8mb4_general_ci;
       修改列编码：ALTER TABLE tbtest MODIFY email VARCHAR(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
       
 <13>删除表数据，保留表结构
      truncate table kuaishou_spider_df_temp；
        
 <14>删除表中的某些满足条件的行数据
      delete from table_name where course_id=4;
        
 <15>修改表中的数据
     update table_name set 字段 = xxx  # 没有条件，也就是把整张表都修改字段为xxx
     update table_name set 字段 = xxx where 字段 = xxx  # 有条件修改，也就是把满足条件的行进行修改
     update table_name set 字段1 = xxx, 字段2 = xxx where 字段 = xxx   # 可以一次性修改多个数据，用逗号隔开

 <16>create table table2 select * from table1;  
     # 创建表2，同时拷贝表1的数据和结构到表2
 
 <17>insert into table2 select * from table1; 
     # 把表1的数据和结构，拷贝到表2，前提是表2是存在的,不然会报错
 
 <18>show columns from table_name;  
     # 显示表中的所有字段名称，也可以使用desc tablename命令

 <19>只修改列的数据类型，不修改列名
      通常可以写成 alter table 表名 modify column 列名 新的列的类型
      例如:student表中列sname的类型是char(20),现在要修改为varchar(20),SQL语句如下
      alter table student modify column sname varchar(20);

 <20>同时修改列名和列的数据类型的方法
       通常可以写成 alter table 表名 change column 旧列名 新列名 新的列数据类型
       例如:basic_df表中列sname的类型是char(20),现在要修改为stuname varchar(20),
       SQL语句如下:
          alter table basic_df change column sname stuname varchar(20);

 <21> drop truncate delete 三者的区别
       drop table 表名   
          删除表结构，删除表数据，即把表所占用的全部空间都释放掉，一点不留，
          是最彻底的删除方式，但是一定要谨慎
          
       truncate table 表名   
          保留表结构，删除表数据，同时也会把index索引内容删除，但是索引名称会保留，等之后往表中插入数据的时候，索引内容会自动添加上，无需再设置索引。
          当表被truncate之后，这个表和索引所占用的空间会恢复到初始大小
          该命令是一次性删除表中所有的数据，不会把删除操作记录在日志中，
          数据是不能恢复的，执行速度快

       delete from 表名   
          保留表结构，删除表数据，不会对index清空，
          DELETE操作不会减少表或索引所占用的空间。
          该命令是一行一行的删除数据，同时会把删除操作记录在日志中，日后可以回滚恢复。
          但是删除速度比较慢，如果是删除大量数据的话，不建议使用该命令

       也就是说：drop是删除表，truncate和delete是删除表中的数据，
       另外truncate和delete的区别是：
          truncate是一下子删除所有的数据，delete是一条一条的删除数据

 <22>desc tablename   # 表结构查询，desc 是Describe的缩写 

 <23> 添加一列，并设置为主键，自增
      # 添加列,并设置为主键自增
      alter table table_name add id bigint primary key auto_increment;

 <24>删除数据库
      drop database 数据库名;

 <25>修改数据库名称
      没有直接修改数据库名称的命令，但是可以间接的实现
      可以先创建一个新的数据库，然后把表复制到新的数据库中，最后把原来的数据库删除掉

 <26>在数据表中，删除满足一定条件的数据
      删除数据是一个需要很谨慎的事情，因此一定要先想清楚之后再操作
      delete from table_name where  xxxx
      如果没有后面的where语句，则整个表的数据都会被删除掉，因此一定要加上where语句

 <27>查看mysql命令
      1/查看mysql正在执行的进程命令
          SELECT * FROM information_schema.PROCESSLIST;
      2/查看正在进行的线程命令
          show processlist;    
      3/杀掉某个进程
         kill thread_id;

 <28>查看表中的索引情况
      show index from table_name 

           
复制代码

把服务器中的文件推到数据库中

 命令如下：
 load data local infile '/root/houzhen03/xxx' into table xxx fields terminated by ','  optionally enclosed by '"' escaped by '"' lines terminated by '\n';
     
 命令中的参数解释：
   <1> fields terminated by ','    # 文件中字段之间用逗号分隔
   <2> optionally enclosed by ‘字段包围符’ # 就是value可能是被某中富豪包围着的
   <3> lines terminated by '\n'    # 行与行之间的分隔符
   
   
复制代码

数据库文件的导入/导出

 <1> secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。因为 secure_file_priv 参数是只读参数，不能使用set global secure_file_priv=''; 命令
 <2> secure-file-priv为NULL时，表示限制mysqld不允许导入或导出。
         例如:secure-file-priv为/var/lib/mysql-files/时，表示限制mysqld只能在/var/lib/mysql-files/目录中执行导入导出，其他目录不行。
 <3> secure-file-priv没有值时，表示不限制mysqld在任意目录的导入导出。
     
     
复制代码

char和varchar的区别

 <1> char类型的长度是固定的，varchar的长度是可变的，是可以缩放的
     varchar比char更加灵活，但是char比varchar更加高效
     
 <2> 存储字符串'abc'，
     使用char(10)，表示存储的字符将占10个字节（包括7个空字符，会自动填充7个空格）
     使用varchar2(10),则表示只占3个字节，10是最大值，当存储的字符<=10时，按照实际的长度存储，如果>10，那么将会被截取
     
 <3> char类型的效率比varchar的效率稍高      
 <4> 从存储空间的角度讲，因为插入类型数据的长度固定，有时候需要用空格进行占位，所以存储数据时占用更大的空间。而varchar却不会。char是以空间换取时间效率，而varchar是以空间效率为首位的。
 
 <6> char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。
 
 <7>在MySQL中，char、varchar和text类型的字段都可以用来存储字符类型的数据，char、varchar都可以指定最大的字符长度，但text不可以(text的最大存储长度是65535，如果value的长度大于这个数，在存储的时候，就会被截取)。数据的检索效率是：char > varchar > text
 
 <8>总结起来，有几点：
    知道固定长度的用char
    经常变化的字段用varchar
         
    尽量用varchar
    超过255字符的只能用varchar或者text
    能用varchar的地方不用text    
    
    
复制代码

bigint和int的区别

     汇总：bigint的取值范围要比int的取值范围大
     使用bigint，会占用更大的磁盘和内存空间，内存空间毕竟有限，无效的占用会导致更多的数据输入输出，额外增加了IO的压力，对性能是不利的。
     因此推荐自增主键时使用int unsigned类型，但不建议使用bigint。
     
     
复制代码

在mysql中，like 怎么用？

     dataframe = calls.get_data_from_db("select xxx, xxx from table where 等级 like 'A%'", "raw_db")
     这里是筛选等级是以A开头的，如果是包含A的，就是‘%A%’,如果是以A结尾的，就是‘%A’

11/遇到问题:sqlalchemy.exc.DataError: (pymysql.err.DataError) (1406, "Data too long for column 'global_network' at row 25")
     在插入MySQL的字符太多，此时将MySQL的模式改为非严格模式。
     MySQL中运行:mysql> SET @@global.sql_mode= ‘’;

12/ distinct 用法
     select distinct 列名 from table;  # 查看某列一共有多少值，去重之后
     select distinct(列名) from table; 
     
13/ drop table if exists table_name;
    # 如果表存在，就删除
    
 
复制代码

MySQL的变量分为以下两种

1/系统变量：

配置MySQL服务器的运行环境
系统变量按其作用域的不同可以分为以下两种：
   <1>会话（SESSION或LOCAL）级：只影响当前会话
   <2>全局（GLOBAL）级：对整个MySQL服务器有效
   <3>量同时拥有以上两个级别
        MySQL将在建立连接时用全局级变量初始化会话级变量，
        但一旦连接建立之后，全局级变量的改变不会影响到会话级变量。
   <4>查看系统变量的值
      可以通过show vairables语句查看系统变量的值：
         show variables like 'log%';  
         show variables where Variable_name like 'log%' and value='ON';  
      注意：show variables优先显示会话级变量的值，如果这个值不存在，则显示全局级变量的值，当然你也可以加上GLOBAL或SESSION关键字区别：
         show session/local variables; 
         show global variables;  
         
   <5>设置和修改系统变量的值
       在MySQL服务器启动时，有以下两种方法设置系统变量的值：
          1）命令行参数，如：mysqld --max_connections=200
          2）选项文件（my.cnf）
       在MySQL服务器启动后，如果需要修改系统变量的值，可以通过SET语句：
          SET GLOBAL var_name = value;  
          SET @@GLOBAL.var_name = value;  
          SET SESSION var_name = value;  
          SET @@SESSION.var_name = value;  
          如果在变量名前没有级别限定符，表示修改会话级变量。
          注意：和启动时不一样的是，在运行时设置的变量不允许使用后缀字母'K'、‘M'等，但可以用表达式来达到相同的效果，如：
          [sql] 
          SET GLOBAL read_buffer_size = 2*1024*1024  # 修改全局变量的值

          这里一个容易把人搞蒙的地方是：
              如果查询时使用的是show variables的话，会发现设置好像并没有生效，
              这是因为单纯使用show variables的话就等同于使用的是show session variables，查询的是会话变量，
              只有使用show global variables，查询的才是全局变量。

          网络上很多人都抱怨说他们set global之后使用show variables查询没有发现改变，原因就在于混淆了会话变量和全局变量，
          如果仅仅想修改会话变量的话，可以使用类似set wait_timeout=10;
          或者set session wait_timeout=10;这样的语法。        
         
复制代码

2/状态变量

监控MySQL服务器的运行状态，可以用show status查看
状态变量可以使我们及时了解MySQL服务器的运行状况，可以使用show status语句查看。
状态变量和系统变量类似，也分为全局级和会话级，
show status也支持like匹配查询，
和系统变量比较大的不同是：
    状态变量只能由MySQL服务器本身设置和修改，
    对于用户来说是只读的，不可以通过SET语句设置和修改它们。


SQL：有一个视频文件播放次数表table1
   dist_id string '区组ID'，fid string '视频文件ID'，vv bigint '播放次数，
   SQL查询各自区组的播放次数排名前十的视频文件ID（分组取前10）
   
   select dist_id,fid,vv from (
          select dist_id,fid,vv,row_number() over(partition by dist_id order by vv desc) as rn from table1) as t where rn <= 10
复制代码