Linux SQL语言—— 数据查询语言DQL、数据操纵语言DML、数据定义语言DDL和数据语言DCL详解

@[TOC]SQL

数据查询语言DQL

创建业务数据库     CREATE DATABASE 数据库名;  

创建数据库要求（区分大小写，唯一性，不能使用命令作为数据库命名 create select，不能单独使用数字和特殊符号“如-"；正常的：用拼音或单词即可。

查看所有数据库        SHOW DATABASES;
选择/进入数据库       USE 数据库名
调用函数，查询当前库   SELECT database();
删除数据库           DROP DATABASE 数据库名;
系统中的位置         /var/lib/mysql/

数据操纵语言DML

可以通过SQL语句中的DML语言来实现数据的操作，如实现数据的插入、删除、更新。

INSERT INTO 表名 VALUES (值1,值2,值3…值n); 完整插入数据
INSERT INTO表名（列名，列名) VALUES (值1,值2); 插入部分数据

UPDATE 表名 SET 列名=值 WHERE CONDITION; 更新数据

>create table t1(id int, name varchar(10));     创建表
>insert into t1 values (1,'vc');                写入
>insert into t1 values (2,'cv');                写入
>update   t1  set   name='cc' where id=2;       更新
>select * from t1;                              查看

> update mysql.user set authentication_string=password("Vc@163") where user="root";             更新root密码

DELETE FROM 表名 WHERE CONDITION;

> delete from t1 where id=2;     删除表t1中id=2的数据

数据定义语言DDL

在MySQL管理软件中，可以通过SQL语句中的DQL语言来实现数据的SELECT 查询操作，如查询商品，账户，地址等等。

>create   table   t3   (id   int,name   varchar(10),age  int);
>insert   into   t3   values  (1,"zhangsansan",25);
>insert   into   t3   values  (2,"lisisi",24);
>insert   into   t3   values  (3,"wangwuwu",23);

>select name  from t3;          查询表t3name列

mysql> create database company;
mysql> CREATE TABLE company.employee5(
     id int primary key AUTO_INCREMENT not null,
    name varchar(30) not null,
    sex enum('male','female') default 'male' not null,
     hire_date date not null,
     post varchar(50) not null,
     job_description varchar(100),
     salary double(15,2) not null,
     office int,
     dep_id int
     );

mysql>desc employee5      查看表结构

插入数据

mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values 
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('aofa','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);

mysql>SELECT * FROM employee5;      查看表所有列

• 简单查询

mysql>SELECT name, salary, salary*15 as pkg  FROM employee5;     查看，将salary*15定义为pkg

• 条件查询

单条件查询where

SELECT name,post FROM employee5  WHERE post='hr';    查询hr部门的员工姓名

多条件查询AND/OR

SELECT name,salary  FROM employee5  WHERE post='hr' AND salary>1000;              查询hr部门的员工，并且工资大于1000

SELECT name, salary FROM employee5  WHERE salary=6000 OR salary=8000               查询所有部门的员工，并且工资是6000或者8000的员工

关键字BETWEEN AND 在什么之间

SELECT name,salary FROM employee5  WHERE salary NOT BETWEEN 5000 AND 15000;          查询不在5000~15000工资员工

关键字IN集合查询

SELECT name, salary FROM employee5  WHERE salary IN (4000,5000,6000) ;             查询工资未4000,5000,6000的员工

关键字IS NULL

SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;         查询非空项

关键字LIKE模糊查询

SELECT * FROM employee5  WHERE name LIKE 'al%';      查询名字为al开头的人（通配符’%’代表多个任意字符）
SELECT * FROM employee5  WHERE name LIKE 'al___';   查询名字为al并且后面有三个字符的人（通配符’_’代表1个任意字符）

• 查询排序

SELECT    *     FROM     表名   ORDER       BY     工资的列名     ASC;    以工资升序排列
SELECT    *     FROM     表名   ORDER BY    工资的列名     DESC;         以工资降序排列
SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3;	 工资最高的前三名

数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
数据库操纵事务发生的时间及效果，对数据库实行监视等

1. Global level 所有库，所有表的权限。
2. Database level 某个数据库中的所有表的权限。
3. Table level 某个数据库中的所有表的权限。
4. Column level 表中的某个字段，的权限。

• mysql用户管理

CREATE  USER  user1@'localhost'   IDENTIFIED BY  'Vc@163';                               创建用户
DROP USER 'user1'@'localhost';                    删除用户
select user from mysql.user;                      查看用户
mysqladmin -uroot -p'旧密码'  password  '新密码'    修改用户密码
UPDATE mysql.user SET authentication_string=password(‘new_password’)  WHERE user=’root’ AND host=’localhost’;           修改用户密码
FLUSH PRIVILEGES;                                 刷新权限（改密码后）

• 丢失root用户MySQL密码

vim /etc/my.cnf                  修改mysql启动设置
    [mysqld]
    skip-grant-tables

 systemctl    restart   mysqld                 重启mysql程序 
 mysql -uroot                                  无密码登录 
 mysql>UPDATE mysql.user SET authentication_string=password('Vc@163')  where  user='root'  and host='localhost';            修改登陆密码 
mysql> FLUSH PRIVILEGES;                      刷新权限

修改完改回配置文件（注，修改后配置文件为无密码访问，任何人都可以登陆，谨慎操作）

• mysql用户管理

mysql   -P 3306 -u root -p'Vc@163' mysql -e ‘show tables’

-h	指定主机名                       【默认为localhost】
-P	MySQL服务器端口                  【默认3306】    大P
-u	指定用户名                       【默认root】
-p	指定登录密码                     【默认为空密码】
-e	接SQL语句

mysql -uroot -p'Vc@163' -hlocalhost -P 3306 mysql -e 'show tables'          免登录查询数据库

• mysql权限原理

grant 权限列表  on 库名.表名 to '用户名'@'客户端主机'  （identified by '密码'  with option参数）;

all： 所有权限（不包括授权权限）
select,update 查询更新

*.*                                 所有库下的所有表
web.*                            web库下的所有表
web.stu_info                 web库下的stu_info表

grant SELECT (id), INSERT (name,age) ON mydb.mytbl to 'user1'@'localhost' identified by 'Vc@123'; 

 %                      所有主机
192.168.2.%		        192.168.2.0网段的所有主机
192.168.2.168		    指定主机
localhost               指定主机

with_option参数        GRANT OPTION授权选项
grant all  on  *.* to 'user1'@'localhost' identified by 'Vc@163' with  grant option;       赋予授权选项（user1也可以给其他用户授权）

SHOW GRANTS\G                              查看自己的权限
SHOW GRANTS FOR user1@'%'\G     查看其他人权限

REVOKE 权限列表 ON 数据库名 FROM 用户名@‘客户端主机’

REVOKE ALL PRIVILEGES ON bbs.* FROM user1@’%’;  回收所有权限

drop user1    删除用户（mysql5.7之后，5.6前需要先revoke all privilege）