MySQL 常用语句以及基础知识

非法入侵

　　MySQL 常用语句以及基础知识
　　RHEL7安装MariaDB
　　Yum install –y mariadb mariadb-server
　　mysql_secure_installation  //初始化数据库
　　如果不初始化数据库，mysql可以匿名登陆，会生成test数据库
　　.
　　.
　　.
　　.
　　.
　　一、对数据库修改
　　1.删除数据库
　　DROP DATABASE 数据库名字;
　　二、对一张表修改
　　1.重命名一张表
　　RENAME TABLE 原名 TO 新名字;
　　ALTER TABLE 原名 RENAME (TO) 新名;
　　2.删除一张表
　　DROP TABLE 表名字;
　　三、对一列修改
　　1.增加一列数据
　　ALTER TABLE 表名字 ADD (COLUMN) 列名字 数据类型 约束;

　　ALTER TABLE employee ADD>　　ALTER TABLE employee ADD weight INT(4) DEFAULT 120 AFTER age;
　　ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;
　　2.删除一列数据
　　ALTER TABLE 表名字 DROP (COLUMN) 列名字;
　　3.重命名一列
　　ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
　　4.改变数据类型
　　ALTER TABLE 表名字 MODIFY 列名字 新数据类型;
　　四、对表的内容修改
　　1.修改表中某个值
　　UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
　　2.删除一行记录
　　DELETE FROM 表名字 WHERE 条件;
　　.
　　.
　　.
　　.
　　Mysqld_safe & //平滑启动数据库  &是后台运行的意思
　　Systemctl start mariadb //启动数据库
　　Mysqladmin –uroot –p123 shutdown //关闭数据库
　　Systemctl stop mariadb //停止数据库
　　.
　　.
　　.
　　.
　　.
　　.
　　Mysql –uusername –ppassword //指定用户和密码登陆数据库 密码可不输入（单独输入）
　　Mysql –uusername –ppassword –D mysql  //-D 指定进入数据库
　　Mysql –S /var/lib/mysql/mysql.sock //sock套接字文件  指定套接字文件sock来连接数据库
　　Mysql –h (ip地址)  //指定登陆一台主机上的数据库  前提是指定的主机开启并运行你连接
　　Mysql –e “use mysql;select user,host,password from user;” //-e 通过shell进行直接控制连接数据库
　　-P 指定服务端端口
　　-C  压缩数据传输
　　-E 查询结果纵向显示 （库外操作）
　　.
　　.
　　.
　　.
　　.
　　数据库的创建：
　　Use mysql //切换数据库
　　Select user,host,password from user; //查询user表中user,host,password列
　　Create database if not exists testdb; //如果testdb库存在就不创建
　　.
　　.
　　.
　　密码管理
　　在库外设置数据库密码
　　mysqladmin –uroot password 123 //设置密码
　　Mysqladmin –uroot –p123 password linux //修改密码
　　在库内设置密码
　　Set password for ‘root’@’localhost’=password(‘123’)；
　　.
　　.
　　.
　　MariaDB [(none)]> use mysql
　　MariaDB [mysql]> Insert into mysql.user(user,host,password) values(‘zhang’,’localhost’,password(‘123’)); //在mysql库中的user表中的user,host,password字段中插入数据(必须在库内！)

　　Grant all on mysql.* to ‘zhangsan’@’localhost’>
　　Create user ‘xiaoquan’@’localhost’>　　.
　　.
　　删除用户：
　　1）drop user ‘zhang’@’localhost’; //建议使用这一种删除方法！
　　Flush privileges //删除和创建后都需要刷新！！！
　　2）Delete from mysql.user where user=’zhangsan’ and password=’123’;
　　Flush privileges //删除和创建后都需要刷新！！！
　　重命名：
　　Rename user ‘xiaoquan’@’localhost’ to ‘zhang’@’localhost’;
　　3)5.7版本不可以使用！
　　Update mysql.user set password = password(‘xiaoquan’) where user=’root’ and host=’localhost’;
　　.
　　.
　　.
　　Mysql 密码破解

• 停止mysql　　Mysqladmin –uroot –p123 shutdown
  
• 使用—skip-grant-tables启动mysql　　Mysqld_safe –skip-grant-tables &
  
• Update mysql.user set password=password(‘newpassword’) where user=’root’ ;// 修改失败添加 and host=’localhost’;　　重启数据库
  　　.
  　　.
  　　.
  　　.
  　　.
  　　授权管理
  
  　　授权语法：grant all [privileges] on db.tbl ‘username’@’host’>
  　　Grant all on .  to ‘xiaoquan’@’localhost’>
  　　Grant all on .  to ‘xiaoquan’@’%’  >
  　　Grant select on mysql.user to ‘zhang’@’localhost’>　　Show grants for ‘zhang’@’localhost’\G   //查看权限
  　　Revoke select on mysql.user from ‘zhang’@’localhost’;  //删除权限
  　　.
  　　.
  　　.
  　　.
  　　.
  　　.
  　　数据库变量：
  　　Show global variables\G //查看所有全局变量
  　　Show global variables like ‘%version%\G  //查看与版本version有关的全局变量
  　　Like 关键字匹配 % 通配符相当于*
  　　Show global variables where variable_name like ‘charavter%’; //global 全局 variables 变量 where 指定条件  variable_name 变量名 like 匹配 //查看全局与字符集相关的变量
  　　Show global variables where variable_name like ‘%log%’ and value=’off’; //查看日志关闭的全局参数
  　　Show session variables\G  //查看回话参数
  　　Show variables \G    //不知名变量参数  默认的为session 回话参数
  　　.
  　　.
  　　Select @@[global.|session.] system_var_name //查看单个变量的语法
  　　Select @@global.pid_file;  //查看pid_file变量参数
  　　.
  　　.
  　　MariaDB [(none)]> select @@session.autocommit; //查询autocommit变量
  　　修改语法：(不指定类型，默认修改会话变量)
  　　MariaDB [(none)]> SET GLOBAL var_name = value;
  　　MariaDB [(none)]> SET @@GLOBAL.var_name = value;
  　　MariaDB [(none)]> SET SESSION var_name = value;
  　　MariaDB [(none)]> SET @@SESSION.var_name = value;
  　　Set  @@session.autoconmmit=0;  //修改autocommit参数
  　　运行时修改的变量并非永久有效，重启后将会失效；
  　　运行时修改global级别的变量：对修改之前建立的会话没有影响，仅对修改后新建立的会话有效；
  　　运行时修改session级别的变量：仅对当前会话有效，且立即生效；
  　　如果想要使设定永久有效，需要设置配置文件。
  　　但是在不确定的情况下，不要随便修改配置文件，否则将可能会出现无法预计的问题
  　　.
  　　.
  　　.
  　　MYSQL help参数：
  　　清除（\c）清除当前输入语句。
  　　连接（\r）重新连接到服务器。可选的参数是DB和主机。
  　　定界符（\d）SET语句定界符。
  　　用编辑器编辑（\e）编辑命令。
  　　（\g）向MySQL服务器发送命令，垂直显示结果。
  　　退出（\q）退出mysql。与退出相同。
  　　转到（\g）发送命令到MySQL服务器。
  　　打印（\p）打印当前命令。
  　　提示（\r）更改MySQL提示符。
  　　退出（\q）退出mysql。
  　　源代码（.）执行SQL脚本文件。将文件名作为参数。
  　　状态（S）从服务器获取状态信息。
  　　系统（！）执行系统外壳命令。
  　　使用（\u）使用另一个数据库。将数据库名称作为参数。
  　　字符集（\c）切换到另一字符集。可能需要用多字节字符集来处理BILCONG。
  　　警告（\w）在每个语句之后显示警告。
  　　（\W）在每个语句之后不显示警告。
  　　.
  　　.
  　　.
  　　.
  　　.
  　　MySQL的概念
  　　1）什么是数据库
  　　按照数据的关系，组织、管理、存储的系统
  　　2）数据库的分类
  　　关系型：Oracle 、 MySQL 、 SQL-server
  　　数据都是按照表状结构存储，二维关系，表与表之间可以建立联系，多表查询，可以跨越版本、跨越不同软件，将数据导入导出
  　　都是基于SQL（结构化查询语句）
  　　非关系型：Redis memache mongodb
  　　NoSQL = not only SQL ，对关系型数据库的一个补充，大大提高的查询检索效率，把所有数据都同意存储，都有自己的,不存在数据的链接
  　　当前企业使用MySQL的版本分布：
  　　5.1 ----- 20%
  　　5.5 ----- 60%
  　　5.6或以上 ----- 20%
  　　Oracle 和 MySQL
  　　Oracle： 应用传统行业（金融、证券、大学等），臃肿，闭源（收费）
  　　MySQL： 应用于互联网行业（BAT 58 jd等等），我们都是根据系统进行二次开发使用，开源的（RHEL6.x 默认5.1系统 -- RHEL7.x 默认mariadb5.5）
  　　2008年左右 MySQL被SUN公司收购，2012年SUN公司又被Oracle收购（JDK也是Oracle），MySQL很有可能闭源
  　　MySQL公司有开发一款和MySQL完全相同的数据库MariaDB
  　　a）安装方式：
  　　yum/rpm 安装
  　　源码安装（cmake安装） --- 运维，针对设备少时
  　　二进制安装 --- 开发，针对集群架构较大时
  　　b）MySQL版本：
  　　商业版： 收取lisence费用 服务费用 -- 针对大公司
  　　社区版： 免费版，个人用、小公司
  　　四大发行版本：
  　　Alpha---预览版（内部测试版本），开发人员使用，完全不对外，测试大的bug
  　　Beta---测试版（公开测试版），加入很多新功能，邀请比较不错的用户尝试使用   ---BAT公司使用
  　　RC----发行版本，可能会有小bug ，但是能满足较大的需求
  　　GA----稳定发行版本（没有任何的后缀，就是这个版本）
  

　　数据库总结：
　　desc 排序显示（表）
　　create 创建
　　update 更新
　　delete | drop删除
　　grant  授权
　　revoke  删除权限
　　select  查询

　　set  |>　　show  显示
　　rename 重命名
　　...............................................
　　...
　　.........
　　..................
　　...............
　　............
　　........
　　.....
　　...
　　..
　　.
　　MYsql进阶：
　　show character set; 命令查看所有可用字符集
　　mysql 默认字符集是拉丁字符集  中文字符集为GB2312  通用字符集是 UTF-8
　　show collation;命令可以查看所有可用的排序方式，以及排序方式对应的字符集
　　同一种字符集下可能有多种排序规则，但是一种排序规则只能对应于一种字符集，也就是说，当你指定了一种排序规则，则不用再指定对应的字符集，因为这种排序规则肯定是针对某个字符集制定的排序规则，所以，如果给定了排序规则，则不要同时指明字符集，每一个字符集都有默认的排序规则，你可以只指定字符集，不指定排序规则，代表使用字符集默认的排序规则。
　　一个表中只能有一个主键，一个主键个一有多个字段，一个表中只有一个字段能被设置为自动增长，而且这个字段必须被定义为key（或者索引），mysql默认也会认为"自动增长的键字段"为主键字段。所以，结合着一个表中只能有一个主键的定义，auto_increment往往只针对于主键字段进行设置，因为一个表中如果已经存在主键，当我们对非主键的键 字段定义auto_increment时会报错
　　表管理语句
　　#查看表（必须在库内）
　　MariaDB [mysql]> show tables;
　　#查看表格所有属性
　　MariaDB [mysql]> show table status\G
　　#查看表结构 语法
　　desc Table_Name;
　　MariaDB [mysql]> desc user;
　　#查看创建表格的属性
　　MariaDB [mysql]> show create table user;
　　创建表 语法：
　　CREATE TABLE tbl_name [IF NOT EXISTS] | (create_definition,...)
　　create_definition包括什么： 字段定义、表级别约束定义、索引定义
　　要求：两个字段，id和name，id为int类型。name为varchar类型，长度60
　　方法一：（完全新建表格）

　　MariaDB [mysql]> create table student (>　　将id设置为主键，限制name不能为空（null），对name增加注释
　　（定义主键，绝对不能为空）两种方法设置主键

　　MariaDB [mysql]> create table student1 (>
　　MariaDB [mysql]> create table student1 (>　　方法二：将表中的内容全部查询出来之后创建一张表（将数据导入到一个新表）
　　只是复制表结构
　　MariaDB [mysql]> create table test select * from student1;
　　#删除表格
　　MariaDB [mysql]> drop table student;
　　Query OK, 0 rows affected (0.00 sec)
　　MariaDB [mysql]> drop table student1,test;
　　Query OK, 0 rows affected (0.00 sec)
　　#修改表格 语法：

　　ALTER TABLE tbl_name [alter_specification [,>　　修改table名：
　　MariaDB [mysql]> create table ttt(id int,name varchar(60));
　　Query OK, 0 rows affected (0.00 sec)

　　MariaDB [mysql]>>　　Query OK, 0 rows affected (0.00 sec)
　　添加字段：

　　MariaDB [mysql]>>
　　MariaDB [mysql]>>　　约束管理、索引管理、键管理语句
　　关系型数据库中的数据约束的概念： 数据约束可以理解为向数据表中插入数据时，要遵守的限制规则
　　mysql中的常用约束有： 主键约束、唯一键约束、外键约束、非空约束等等
　　主键：primary key，表上一个或多个字段的组合，填入主键字段中的数据，必须不同于已经存在的其它行的相同字段上的数据，而且也不能为空；一个表只能存一个主键，一个主键可以由多个字段组成；
　　唯一键：unique key，表上一个或多个字段的组合，填入其中字段中的数据，必须不同于已经存在的其它行的相同字段上的数据，但可以为空；一个表可以有多个唯一键；
　　外键：foreign key，一个表中的外键字段中所能够插入的数据取值范围，取决于引用的另一个表上主键字段上的已经存在数据集合；
　　学生表（id，name） 课程表（id，name） 选课表（id）
　　检查条件约束：check，自定义的逻辑表达式；
　　#非空约束
　　设置非空字段：

　　MariaDB [mysql]>>　　删除非空字段：

　　MariaDB [mysql]>>　　#自动增长（可以使用change语句 也可以用modity 只能为主键）
　　设置：

　　MariaDB [mysql]>>
　　MariaDB [mysql]>>　　取消：

　　MariaDB [mysql]>>　　#主键约束：
　　设置：

　　MariaDB [mysql]>>
　　MariaDB [mysql]>>　　取消

　　MariaDB [mysql]>>　　#唯一键约束：
　　设置：

　　MariaDB [mysql]>>　　设置名称：

　　MariaDB [mysql]>>　　取消：

　　MariaDB [mysql]>>　　#查看约束
　　MariaDB [mysql]> select * from information_schema.key_column_usage where table_name="TTT"\G
　　索引管理：
　　索引--index
　　增加纯粹的索引(在information_schema.KEY_COLUMN_USAGE不会生成的）：

　　MariaDB [mysql]>>　　MariaDB [mysql]> create index ind_name on TTT (name(100));
　　MariaDB [mysql]> show index from TTT\G
　　增加的唯一键/唯一索引： 不仅仅在show，而且在information_schema.KEY_COLUMN_USAGE里面
　　创建联合索引：
　　MariaDB [mysql]> create index ind_id_name on TTT (id,name);
　　重置索引：（默认引擎不支持）
　　MariaDB [mysql]> repair table TTT quick\G
　　 1. row
　　Table: mysql.TTT
　　Op: repair
　　Msg_type: note
　　Msg_text: The storage engine for the table doesn't support repair
　　1 row in set (0.00 sec)
　　删除索引：

　　MariaDB [mysql]>>　　查看索引：
　　MariaDB [mysql]> show index from TTT\G
　　.
　　.
　　.
　　.
　　【索引管理】：
　　索引--index
　　增加纯粹的索引(在information_schema.KEY_COLUMN_USAGE不会生成的）：

　　MariaDB [mysql]>>　　MariaDB [mysql]> create index ind_name on TTT (name(100));
　　MariaDB [mysql]> show index from TTT\G
　　增加的唯一键/唯一索引： 不仅仅在show，而且在information_schema.KEY_COLUMN_USAGE里面
　　创建联合索引：
　　MariaDB [mysql]> create index ind_id_name on TTT (id,name);
　　重置索引：（默认引擎不支持）
　　MariaDB [mysql]> repair table TTT quick\G
　　 1. row
　　Table: mysql.TTT
　　Op: repair
　　Msg_type: note
　　Msg_text: The storage engine for the table doesn't support repair
　　1 row in set (0.00 sec)
　　删除索引：

　　MariaDB [mysql]>>　　查看索引：
　　MariaDB [mysql]> show index from TTT\G
　　.
　　.
　　.
　　.
　　...........................................................................................................................
　　insert语句总结
　　方法一：
　　insert into tablename(字段1名称,字段2名称,...) values(字段1值,字段2值,...)
　　#插入一条数据
　　insertinto tb1(name,age) values('tom',33);
　　#插入多条数据
　　Insert into tb1(name,age) values('tom',33)，（"jerry",30）;
　　#不指定字段（所有字段必须有值）
　　Insert into tb1 values('tom',33)，（"jerry",30）;
　　方法二：
　　insert into tablename set 字段1名称=字段1值,字段2名称=字段2值

　　Insert into tbl2 set>