常用SQL

一、数据库基础

1、数据库概念

1.1 数据库：database，简称为DB

1.2 数据库系统(DBS)：Database System，简称为DBS

1.3 数据库管理系统：Database Management System，简称为DBMS（例如mysql、oracle）

1.4 数据库是一个集合，里面包括多个数据库实例；

1.5 可以对数据库进行创建、修改、删除、查询等操作；

操作对象：

1.2.1 库 -- 对库操作

1.2.2 表 -- 对表操作

1.2.3 数据-- 对表中的数据操作

2、数据库文件介绍

2.1 bin目录

my.ini -- mysql配置文件，可以用于查看和修改端口等操作

mysqld.exe --mysql服务器端程序

mysql.exe -- mysql客户端程序

mysqlimport.exe -- mysql数据导入程序

mysqldump.exe --mysql导出、备份程序

二、可视化窗口中对mysql数据库的操作

在MySQL中操作数据库SQL语句结尾都要带上“;”语法规则。

1、数据库操作

1.1、创建数据库

语法：create database 库名；

举例1：创建一个名称为testa的数据库

sql语句：create database testa;

举例2：创建一个支持中文字符（汉字）的数据库

语法：create database 库名 default character set utf8 collate utf8_general_ci;

参数说明：

default character set：指定默认字符集

utf8：字符集，支持汉字的字符集；

collate：指定校验规则

collate utf8_general_ci：指定数据库是否区分大小写

ci：是单词case insensitive 大小写不敏感，就是不区分大小写

1.2、修改数据库

1.2.1 修改某实例库名

语法：alter database 库名;

还不知道直接修改库名的SQL语句，可以在数据库文件中直接修改库名，然后在SQLyog中刷新。

举例：alter database testaa default character set gbk collate gbk_chinese_ci; #修改数据库testaa 的字符集为gbk（支持汉字）。

1.2.2 修改某表字段名

语法：alter table table_name change old_field_name new_field_name new_field_type(new_field_length);

(更改字段名/字段字符类型/字符长度，把后面new对应的三项改了。)

1.3、删除数据库

语法： drop database 库名；

举例：drop database testa; #删除数据库testa

2、对表结构（表本身）的操作

2.1 创建表

语法：create table 库名.表名(字段名1 字符类型1，字段名2 字符类型2,...)

举例1：在test数据库中，创建表学生表，设定3个字段，分别为（学号 数值型（数值长度5），姓名 可变字符型（字符长度10），分数 浮点型（长度8，小数点后为2位））;

create table test.student
(
no int(5),
name varchar(10),
score float(8,2)
)  

待整理

4、创建表时复制其他表数据

-- 创建一张和HRDB.EMPLOYEES表结构一模一样的表

CREATE TABLE HRDB.EMP_COPY AS SELECT * FROM HRDB.EMPLOYEES;

-- 语法：CREATE TABLE 库名.复制表表名 AS SELECT 字段 FROM 库.原表名;

2.2 修改表

2.2.1 修改表的字段名

语法：alter table 库名.表名 change 原字段名 新字段名 字符类型(字符长度);

    # 将班级表的班主任字段名由classowner修改为classonr;
    alter table testaa.class change classowner classonr char(5);

2.2.2改表名

（1）rename table 旧表名 to 新表名;

（2）alter table 旧表名 rename [as] 新表名;

待整理

1、查看表结构

语法：

SHOW KEYS FROM DBName.TableName；

-- 举例：查看employees表的约束

SHOW KEYS FROM hrdb.employees;

CREATE TABLE hrdb.hometown

(

id CHAR(6) PRIMARY KEY,

city VARCHAR(100) DEFAULT 'shenzhen' NOT NULL,

province VARCHAR(100) DEFAULT 'guangdong'

);

CREATE TABLE hrdb.person

(

id CHAR(18),

NAME VARCHAR(100),

sex CHAR(1),

birthday DATE,

height FLOAT,

weight FLOAT,

age SMALLINT,

hometown CHAR(6)

);

-- 修改字段（添加约束）

-- 语法：

ALTER TABLE 库名.表名

ADD [CONSTRAINT 约束名称] TYPE (COLUMN); -增加

-- 约束名称：说明，约束名称命名规则为：表名+字段名+键的类型的简写（比如：主键简写为pk，外键：fk）

-- 举例1：为person表的id字段添加主键约束

ALTER TABLE hrdb.person

ADD CONSTRAINT person_id_pk PRIMARY KEY (ID);

-- ALTER TABLE：改变表，修改表的意思

-- ADD CONSTRAINT: -- 增加加约束的意思

-- person_id_pk：约束名称，可以自己定义

-- PRIMARY KEY (ID)：-- 设置ID字段为主键

-- 举例2：修改person表约束，使姓名不允许为空

ALTER TABLE hrdb.person MODIFY COLUMN NAME VARCHAR(100) NOT NULL;

-- 说明：MODIFY，修改的意思

-- 举例3、修改表，使性别字段名由sex修改为gender

ALTER TABLE hrdb.`person`

CHANGE sex gender CHAR(1);

-- 修改表的字段名语法为：

ALTER TABLE 库名.表名 CHANGE 被替换字段 替换字段 数据类型（长度）;

-- 举例4：为性别字段设置默认值

ALTER TABLE hrdb.person MODIFY gender CHAR(1) DEFAULT 'F';

-- 说明：使用关键字DEFAULT设置gender字段的默认值为“F”。

-- 语法：ALTER TABLE 库名.表名 MODIFY 字段名 数据类型（长度） DEFAULT "默认值";

-- 举例5：修改表，使生日不允许为空

ALTER TABLE hrdb.person MODIFY birthday DATE NOT NULL;

-- 语法：

ALTER TABLE 库名.表名 MODIFY 字段名 数据类型 NOT NULL;

-- 举例6：修改身高和体重的数据格式，使其满足999.9

ALTER TABLE hrdb.person MODIFY COLUMN height FLOAT(4,1); -- modify column和modify等效

ALTER TABLE hrdb.person MODIFY COLUMN weight FLOAT(4,1); -- modify column和modify等效

-- 语法：

ALTER TABLE 库名.表名 MODIFY COLUMN 字段名 FLOAT(4,1);

-- 举例7：添加外键约束

-- 添加外键，使得person的hometown字段参考hometown这个表的id字段

ALTER TABLE hrdb.person

ADD CONSTRAINT person_hometown_fk FOREIGN KEY (hometown) -- 为person表hometown字段增加外键约束

REFERENCES hrdb.hometown (id); -- 参考hometown表的主键id字段

-- person_hometown_fk：外键约束名

-- 语法：

ALTER TABLE 库名.表名

ADD CONSTRAINT 表名_字段名_外键简写 FOREIGN KEY (设置为外键的字段) -- 为person表hometown字段增加外键约束

REFERENCES 库名.外键表名 (外键表主键字段);

-- 删除外键约束

-- 删除外键约束语法：

ALTER TABLE 库名.表名

DROP FOREIGN KEY 键名

-- 举例8：删除departments表的外键DEPT_MGR_FK

ALTER TABLE hrdb1.departments DROP FOREIGN KEY DEPT_MGR_FK;

-- FOREIGN KEY：说明，是外键的意思

DROP INDEX DEPT_MGR_FK ON hrdb.`departments`; -- 删除外键索引

-- 说明：INDEX是索引的意思，DEPT_MGR_FK是外键索引名称

-- 删除字段

-- 语法：

ALTER TABLE 库名.表名

DROP COLUMN 字段名;

-- 举例7：删除年龄字段

ALTER TABLE hrdb.person DROP COLUMN age;

-- 添加字段

-- 语法：

ALTER TABLE 库名.表名

ADD COLUMN 字段名 数据类型（长度） 唯一性约束;

-- 举例8：添加字段，手机号，并设置为唯一性约束

ALTER TABLE hrdb.person ADD COLUMN phone CHAR(11) UNIQUE;

DESC hrdb.`person`; -- 查看操作结果

3、对表中数据的操作

3.1 数据的查询

语法：select 字段名1，字段名2，... from 库名.表名 where 查询条件；

    # 举例1：查询hrdb库，雇员表中所有员工的编号,查询结果显示为员工编号和员工姓名
    SELECT department_id,first_name,last_name FROM hrdb.employees;
    # 举例2：查询hrdb库，雇员表中所有员工的所有信息
    select * from hrdb.employees;
    # 举例3： 查询员工编号为101的员工的所有信息
    SELECT * FROM hrdb.employees WHERE employee_id=101;
    # 举例4： 查询工资大于10000的员工的所有信息
    SELECT * FROM hrdb.employees WHERE salary>10000;  -- 大于号“>”的用法

3.2 表中插入数据

语法：insert into 库名.表名(字段名1，字段名2，...) values(字段值1，字段值2，...);

说明：没有写明插入数据的表中其它字段，插入“NULL”。

# 举例：往班级表中插入一个班级，班级名编号001，班主任为陈道明，属于4系。
# 再插入字符型字段值时要加英文双引号，如"陈道明"；
insert into testaa.class(classno,classonr,depno) values(001,"陈道明",4);

3.2.1插入系统时间

INSERT INTO hrdb.`employees`(hire_date) VALUES(SYSDATE());

3.2.2插入其他表的数据

语法：

INSERT INTO 库名.表名（字段名1，字段名2）select 字段名1,字段名2 FROM 库名.表名 where条件;

-- 举例： 插入数据（employee_id,first_name,salary,commission_pct）
INSERT INTO hrdb.`SALES_REPS`(id,NAME,salary,commission_pct)
SELECT employee_id,first_name,salary,commission_pct FROM hrdb.`employees`
WHERE JOB_ID LIKE '%REP%';

3.3 修改表中的数据

语法：update 数据库名.表名 set 字段1=字段值1 where 字段2=字段值2；

说明：不加where条件会修改表中多有数据该字段的值。

# 修改编号为1的学生的年龄为20
UPDATE test.`student` SET sage=20 WHERE s=1;
# 让小红的年龄增加1岁
UPDATE test.`student` SET sage=sage+1 WHERE sname="小红";
# 让所有人的年龄增加1岁
UPDATE test.`student` SET sage=sage+1;
# 将编号为5，年龄为12的人的姓名修改为小周周
UPDATE test.`student` SET sname="小周周" WHERE s=5 AND sage=12;

3.3.1在UPDATE语句中使用子查询

 UPDATE HRDB.EMP_COPY
   SET JOB_ID =
       (SELECT JOB_ID FROM HRDB.EMPLOYEES WHERE EMPLOYEE_ID = 205),
       SALARY =
       (SELECT SALARY FROM HRDB.EMPLOYEES WHERE EMPLOYEE_ID = 205)
 WHERE EMPLOYEE_ID = 114;
-- 1 row updated.
-- 更新 114号员工的工作职位和工资使其与205号员工相同
SELECT job_id,salary FROM hrdb.`EMP_COPY`
WHERE EMPLOYEE_ID = 114;

3.4 删除表中的数据

语法：delete from 库名.表名 where 字段名=字段值 ;

说明：对一个表中的数据进行删除操作时，记得一定要加条件，否则会删除所有数据；

# 删除姓名叫小蓝的数据
DELETE FROM test.`student` WHERE sname="小蓝";
# 删除姓名叫小白和小紫的数据（同时删除两条数据）
DELETE FROM test.`student` WHERE sname="小白" OR sname="小紫";

3.4.1在 DELETE 中使用子查询

在 DELETE 中使用子查询，使删除基于另一个表中的数据。

DELETE FROM HRDB.EMP_COPY
WHERE  department_id =
                       (SELECT department_id
                        FROM   HRDB.departments
                        WHERE  department_name LIKE '%Public%');
--验证：
SELECT department_id
FROM HRDB.EMP_COPY
WHERE department_id=70;

4、mysql用户

4.1 mysql用户的创建

语法：insert into mysql.user(host,user,password) values("主机IP",“用户名”,password("密码"))；

mysql：数据库管理系统的管理数据库。

user：表里面存放数据库用户的信息。

host：字段的值可以分别为locahost、具体IP地址、%，他们之间的区别为：

localhost：所创建的用户只能在本机登录mysql。

具体IP地址：所创建的用户只能在相应的IP地址所对应的主机上远程登录mysql。

%：所创建的用户可以在任何远程主机（非本地主机：ip地址用localhost不行（本机不能登录），ip地址用172.31.87.72（我的主机ip）可以，大概是ip格式问题（计算机把它识别成其它计算机的ip）。

# 创建用户test，主机iP为localhost，密码123456
INSERT INTO mysql.`user`(HOST,USER,PASSWORD) VALUES("localhost","test",PASSWORD("123456"));

# 创建用户test1，主机iP为172.31.87.67，密码123456
INSERT INTO mysql.`user`(HOST,USER,PASSWORD) VALUES("172.31.87.67","test1",PASSWORD("123456"));

SELECT HOST,USER,PASSWORD FROM mysql.`user`;
# 创建用户test2，主机iP为%，密码123456;
INSERT INTO mysql.`user`(HOST,USER,PASSWORD) VALUES("%","test2",PASSWORD("123456"));

4.2 mysql用户的删除

4.2.1 用drop删除用户（同时删除用户权限）

语法：drop user 用户名@主机IP

说明：使用“drop user 用户名@主机IP”删除用户，是删除用户的数据库+表+权限信息；

# 删除用户test6（主机IP为localhost）
drop user test6@localhost;
# 删除用户test2（主机IP为%）
drop user test2@"%";
# 特殊情况：删除主机IP为%的用户，SQL语句中可以不用带%
DROP USER test13;

4.2.2在user中删除（不删除用户权限）

语法：delete from mysql.user where user="用户名" and host="主机IP";

说明：使用delete语句，删除user表中的用户，只是在user表中删除，如果以后再创建一个同名用户，新创建的用户会继承原先用户的权限。

#在user表中，删除对应主机IP 172.31.87.67的用户test1
delete from mysql.user where user="test1" and host="172.31.87.67";

4.3 mysql用户权限管理

确保登录用户有授权权限

（1）root 超级管理员

（2）具有为别人授权权限的人。WITH GRANT OPTION - 具有为别的用户授予权限的权限

-- 查看当前登陆数据库的用户身份：

SELECT USER();

-- 查看告警信息

SHOW WARNINGS;

4.3.1为新创建用户授权

语法

grant 权限列表 on 库名.表名 to 用户名@授权IP identified by "密码";

flush privileges；

说明：权限必须刷新后才生效

grant 权限列表：给用户授予哪些权限。

all privileges：表示所有权限。

select：查询权限。

insert ：插入数据权限。

update：更新权限。

on 库名.表名：给用户那些数据库实例，那些表的权限。

*.*：表示所有实例所有表。

test1.*：数据库实例test1所有表的权限。

to 用户名@授权IP：给哪个用户授权在那个IP访问。

授权IP：可以为locahost、具体IP地址、%。

identified by "密码"：指定登录密码

#给user中test用户授予所有权限（对所有库中的所有表都有操作权限）
#主机IP为localhost，密码是123456。
grant all privileges on *.* to test@localhost identified by "123456";
flush privileges；

4.3.2 查看用户权限

（1）查看当前登录用户权限语法：show grants；

（2） 查看指定用户的权限语法：show grants for 用户名@主机IP;

操作角色：超级用户（root具有授权权限的用户）

举例：查看用户test9在本地主机上的权限

show grants for test9@localhost;

4.3.3 收回用户权限

语法：REVOKE 权限列表 ON 库名.表名 FROM 用户名@主机IP;

REVOKE 权限列表：收回权限列表包含权限。可以是all privileges,select,insert,update。

ON 库名.表名：收回用户对那些数据库实例的那些表的权限。

FROM 用户名@主机IP：收回用户在那个IP访问的权限

举例：

-- 回收用户test5在本地数据库主机上的所有权限

REVOKE ALL PRIVILEGES ON *.* FROM test5@localhost;

5.高级查询

5.1 单条件查询

select * from 库名.表名 where 查询条件；

查询条件说明：

>：大于。

<：小于。

=：等于。

>=：大于等于。

<=：小于等于。

<>：不等于。

!=：不等于。

举例：-- 查询部门编号不等于60的员工的所有信息：

SELECT * FROM hrdb.employees WHERE department_id <>60;

5.2 多条件查询

select * from 库名.表名 where 条件1 or 条件2；

条件关系说明：

or：或。

and：与

举例：-- 查询薪资在5000以上、10000以下的员工信息

SELECT * FROM hrdb.`employees` WHERE salary <10000 AND salary >5000;

5.3 关键字

5.3.1 limit返回行数

（1）返回前n行

select * from 库名.表名 where 查询条件 limit n;

举例：

--查询部门编号为60，工资在10000以下的员工的名称、薪资、部门编号，显示前3条数据

SELECT * FROM hrdb.`employees` WHERE department_id=60 AND salary<10000 LIMIT 3;

（2）跳过n行后，按顺序取m个

select * from 库名.表名 where 查询条件 limit n,m;

select * from 库名.表名 where 查询条件 limit m offset n;

-- 查询员工表中所有员工的信息，显示结果跳过8行后，按顺序取5条数据。
mysql> select * from hrdb.employees limit 8,5;

5.3.2 distinct过滤重复数据

语法：SELECT DISTINCT 字段名 FROM 库名.表名；

--  查询员工表中，所有员工的职位编号，查询结果过滤掉重复数据
SELECT DISTINCT e.job_id AS 职位编号 FROM hrdb.employees e;
--  查询所有员工的个数，查询结果不统计重复数据
SELECT COUNT(DISTINCT employee_id) FROM hrdb.`employees`;

5.3.3 group分组查询

语法：select 字段名 from 库名.表名 where 查询条件 group by 字段名；

说明：分组结果忽略空值，可以根据多个字段分组。

--查询不同部门的员工人数
SELECT department_id,COUNT(*) FROM hrdb.`employees` GROUP BY department_id;
--多字段分组。查询不同部门中，不同职位的员工的薪水之和
SELECT department_id,job_id,SUM(salary) FROM hrdb.employees
GROUP BY department_id,job_id;

说明：不加分组查询函数是针对整张表，加分组是针对每个分组。

--举例：按照部门求出每个部门的总人数，总月薪，平均月薪，最大月薪
SELECT COUNT(*),SUM(salary),AVG(salary),MAX(salary)
FROM hrdb.employees
GROUP BY department_id;

5.3.4 having分组查询结果过滤

语法：

select 字段名 from 库名.表名 where 查询条件

group by 字段名

having 过滤条件;

说明：HAVING关键字对GROUP BY分组后的数据进行过滤。

-- 查询不同部门的员工人数，查询结果显示部门人数在10人以上的部门信息
SELECT department_id,COUNT(*) AS ct FROM hrdb.`employees`
GROUP BY department_id
HAVING ct >10; 

5.3.5 order by分组查询结果排序

5.3.5.1 正常排序

语法

SELECT 字段名 FROM 库名.表名 WHERE 查询条件

GROUP BY 字段名1，字段名2

order by 字段名 排序关键字;

排序关键字：

ASC：升序排列，未给出排序关键字，默认按照升序排列。

DESC：降序排列。

-- 举例：查询所有员工的姓名、薪水信息，查询结果按照薪水升序排列

-- 不给出排序关键字，默认按照升序排列
SELECT first_name,last_name,salary FROM hrdb.`employees`
ORDER BY salary;
-- 给出排序关键字ASC，按照升序排列
SELECT first_name,last_name,salary FROM hrdb.`employees`
ORDER BY salary ASC;  

5.3.5.2字段别名排序

字段的别名排序，是将字段重新命名以后，按照新的名称进行排序。

-- 查询月薪高于5000的员工，并按照年薪从小到大升序排序
select first_name,last_name,salary,salary*12 年薪
from hrdb.employees
order by 年薪;

5.3.5.3 多字段排序

多字段排序的效果是，按照字段的优先级进行，若当前字段无法分出高低，则依靠后续的字段进行排序确认。

-- 查询员工所有信息，查询结果按照职位降序，工资降序依次进行排序
select * from hrdb.employees
order by job_id desc,salary desc;-- 先后按照职位编号和薪水，进行降序排列

特别说明：最后确认一点，排序是可以使用不在SELECT 列表中的列排序的。

5.3.6 like模糊查询

语法：

select 字段名 from 库名.表名

where 字段名 like 字符;

字符说明：%和_可以单独使用，也可以同时使用

%：可以替换0个，1个或者多个字符

_:可以替换1个字符

-- 举例：查询员工编号第二个数字为2的员工信息
select * from hrdb.employees
where employee_id like "_2%";
-- 查询职位编号中包含public字符串的所有员工的信息
SELECT * FROM hrdb.`employees`
WHERE job_id LIKE "%public%";

5.3.7 between...and...关键字

--查询薪资在4000和8000之间的员工信息
select * from hrdb.employees
where salary between 4000 and 8000;

5.3.8 IN关键字

说明：IN,查询字段在字段值列表中给出的明确值之中

举例：查询薪资为(4000,5000,6000,7000,8000)之中任意值的员工信息
SELECT * FROM hrdb.`employees`
WHERE salary IN(4000,5000,6000,7000,8000); 

25、空值NULL

说明：

（1）NULL是数据库中特有的数据类型，当一条记录的某个列为NULL，则表示这个列的值是未知的、是不确定的。

（2）判断一个字段是否为NULL，应该用`IS NULL`或`IS NOT NULL`，而不能用‘=’，对NULL的任何操作的结果还是NULL。

举例：--查询所有的没有确定经理的员工；
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, E.MANAGER_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.MANAGER_ID IS NULL;
-- 查询所有的有确定经理的员工；
 SELECT * FROM hrdb.`employees` e
 WHERE e.`manager_id` IS NOT NULL;

5.4 别名

5.4.1 表的别名

语法:

select 表的别名.字段名 from 表名 as 表的别名；# 使用as

select 表的别名.字段名 from 表名 表的别名； # 加空格后直接写

-- 查询雇员表中所有员工的员工编号和薪资信息
 select e.employee_id,e.salary from hrdb.employees as e;

5.4.2字段的别名

语法：select 字段名 as 字段的别名 from 库名.表名；（as可加可不加）

-- 查询员工表中，所有人员的姓名、薪资、职位信息；
SELECT first_name AS 名,last_name AS 姓,salary AS 工资, job_id AS 职位编号 FROM hrdb.`employees`;

5.5 sql函数

5.5.1 数据个数统计count

--统计查询员工表中，所有员工的个数；
SELECT COUNT(*) FROM hrdb.employees;  
SELECT COUNT(employee_id) FROM hrdb.employees;
说明： COUNT(*)是求记录总数，COUNT(employee_id)是求SALARY不为NULL的记录数。

5.5.2 求和函数sum

--统计查询员工表中，所有员工的薪水之和；
select sum(salary) from hrdb.employees;

5.5.3 平均函数AVG

-- 统计查询员工表中，所有员工的薪水的平均值；
SELECT AVG(salary) FROM hrdb.employees; 

5.5.4 最大值max与最小值min

-- 统计查询员工表中，所有员工中的最高薪水；
SELECT MAX(salary) FROM hrdb.`employees`;
-- 统计查询员工表中，所有员工中的最低薪水；
SELECT MIN(salary) FROM hrdb.`employees`;

5.5.5 查询结果转换显示

5.5.5.1 UPPER/LOWER查询结果转大写/小写显示

语法：

SELECT UPPER('SmitH') from 库名.表名; -- SmitH列值全部转大写显示

SELECT LOWER('SmitH') from 库名.表名; -- SmitH列值全部转小写显示。

5.5.5.2 查询结果拼接显示

语法：

SELECT CONCAT('SmitH','.', 'LEE') from 库名.表名; -- 字符串拼接在一起

 -- 查询所有员工信息，查询结果将名转换为大写，姓转换为小写，姓名拼接在一起，以别名显示拼接后的结果
SELECT UPPER(first_name),LOWER(last_name),CONCAT(first_name,".",last_name) 全称 
FROM hrdb.`employees`;

5.5.5.3 不常用

SELECT SUBSTR('HelloWorld',2,5); -- 在给定的字符串Hellworld中，从第二个字符开始，取5个字符

SELECT LENGTH('HelloWorld'); -- 获取字符串HelloWorld的长度

SELECT INSTR('HelloWorld', 'Wor'); -- 给定字符串HelloWorld，获取给定的另一个字符串Wor在前者之中的位置

SELECT LPAD('salary',10,'*'); -- 指定salary以10位显示，长度不够长，在左侧补上4位*号；

SELECT RPAD('salary',10,'*'); -- 指定salary以10位显示，长度不够长，在右侧补上4位*号；

SELECT TRIM(' john '); -- 去除字符串john前后的空格

-- 举例：去除字符串"aajohnaa"前后的字符a
 SELECT TRIM(BOTH "a" FROM "aajohnaa");   -- both 意思是两个都....  
-- 举例：去除字符串"aajohnaa"前面的字符a
SELECT TRIM(LEADING "a" FROM "aajohnaa");  -- 使用关键字leading和from，去除前面的字符a
-- 举例：去除字符串"aajohnaa"后面的字符a
SELECT TRIM(TRAILING "a" FROM "aajohnaa"); -- 使用关键字trailing和from，去除后面的字符a

SELECT ROUND(45.926, 2); -- 对数字45.926进行四舍五入处理,小数点后保留2位小数。

SELECT TRUNCATE(45.926, 2); -- 要求小数点后保留两位小数，多余位数，做直接舍弃处理。

SELECT MOD(1600, 300); -- 求余数

-- 特别说明：

TRUNCATE(x,y)函数返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分;若y设为负数，则截去x这个数小数点左起第y位开始后面所有低位的值。

TRUNCATE(45.923,0): 返回值为45 -- 0意味着小数点右侧从0位起的数字全部被舍去，返回值为45

TRUNCATE(45.923,-1):返回值为40 -- -1意味着小数点左侧一位数字被截取（置为0），返回值为40

TRUNCATE(45.923,-2):返回值0 -- -2意味着小数点左侧两位数字均需要截取，所以返回值为0

5.5.6 日期处理函数

语法：

SELECT SYSDATE(); -- 获取当前日期时间

SELECT NOW(); -- 获取当前日期时间

SELECT CURTIME(); -- 获取当前时间

SELECT MONTHNAME(SYSDATE()); -- 获取当前给定日期时间所属于的月份名称

SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:%s'); -- 获取当前系统日期时间，以指定格式（年:月:日 时：分：秒的格式显示）；

SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND); -- 将指定的日期时间增加1秒

SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 DAY); -- 将指定的日期时间增加1天

SELECT DATE_ADD("1998-01-01 00:00:00", INTERVAL "-1 10" DAY_HOUR); -- 将指定的日期时间倒退1天10小时

5.6 多表查询

5.6.1内连接

语法：

select 别名1.字段1，别名2.字段2

from 库名.表1 别名1

inner join 库名.表2 别名2

on 别名1.字段3=别名2.字段3

where 其他查询条件；

说明：内连接只返回满足连接条件的数据

--查询每个员工的员工编号、姓名、工资、和部门编号、部门名称
--方法一（普通查询）：
SELECT e.employee_id,e.first_name,e.last_name,e.salary,d.department_id,d.department_name
FROM hrdb.`employees` e,hrdb.`departments` d
WHERE e.`department_id`=d.`department_id`; -- 部门表和员工表之间共同的字段进行匹配
--方法二（内连接）：
SELECT e.employee_id,e.first_name,e.last_name,e.salary,d.department_id,d.department_name
FROM hrdb.`employees` e
INNER JOIN hrdb.`departments` d  -- INNER JOIN内连接查询关键字
ON e.`department_id`=d.`department_id`; -- 部门表和员工表之间共同的字段进行匹配

三个表关联

-- 查询员工编号为100的员工所在的城市的名称(三个表关联：员工表，部门表，位置表)
SELECT e.employee_id,e.department_id,l.location_id,l.city
FROM hrdb.employees e
INNER JOIN hrdb.`departments` d
ON e.`department_id`=d.`department_id`
INNER JOIN hrdb.`locations` l
ON d.`location_id`=l.`location_id`
WHERE e.`employee_id`=100;

5.6.2 外连接

外连接包括：左连接，右连接和全连接。

5.6.2.1 左外连接（简称左连接）

说明：左连接，就是在查询结果中，显示左表中的数据，以及右表中符合连接条件的数据，不符合连接条件的数据，以NULL值填充。

语法

SELECT 表1.字段名1，表2.字段名2

FROM 库名.表1 别名1

LEFT JOIN 库名.表2 别名2

ON 别名1.字段名3=别名2.字段名3

WHERE 其他查询条件

-- 举例：求所有学生的学号、姓名、课程号、成绩，没有成绩的学生信息也显示出来
SELECT s.s_id,s.s_name,g.c_id,g.score
FROM test.`student` s
LEFT JOIN test.`grade` g
ON s.s_id=g.s_id

5.6.2.2 右外连接（简称右连接）

说明：右连接，就是在查询结果中，显示右表中的数据，以及左表中符合连接条件的数据，不符合连接条件的数据，以NULL值填充。

语法：

SELECT 表1.字段名1，表2.字段名2

FROM 库名.表1 别名1

RIGHT JOIN 库名.表2 别名2

ON 别名1.字段名3=别名2.字段名3

WHERE 其他查询条件;

-- 举例：查询所有成绩及对应学生姓名，试卷上忘记写姓名的成绩也显示出来
SELECT s.s_name,g.score 
FROM test.`student` s
RIGHT JOIN test.grade g
ON s.s_id=g.s_id;

5.6.2.3 全外连接

将左连接和右连接使用union关键连接起来。

语法

SELECT 表1.字段名1，表2.字段名2

FROM 库名.表1 别名1

LEFT JOIN 库名.表2 别名2

ON 别名1.字段名3=别名2.字段名3

WHERE 其他查询条件

union; --联合查询

oracle语法：

SELECT 表1.字段名1，表2.字段名2

FROM 库名.表1 别名1

FULL JOIN 库名.表2 别名2

ON 别名1.字段名3=别名2.字段名3

WHERE 其他查询条件;

5.7子查询（嵌套查询）

说明：

（1）子查询要包含在括号内。

（2）将子查询放在比较条件（类似于大于、小于。。。）的右侧。

（3） 一般情况下不要在子查询中使用ORDER BY 子句。

（4） 单行操作符对应单行子查询，多行操作符对应多行子查询。

5.7.1 子查询

语法

SELECT字段1，字段2

FROM库名.表名

WHERE 字段 比较运算符

(SELECT字段3

FROM库名.表名);

-- 举例：查询工资高于“Abel”的所有员工的名字和工资
select employee_id,first_name,last_name,salary
from hrdb.employees
where salary >(select salary from hrdb.employees where first_name="Abel");

5.7.2 having 子查询

-- 查询最低工资高于部门编号为50的部门中最低工资的员工的部门编号和最低工资
 SELECT DEPARTMENT_ID, MIN(SALARY)
 FROM HRDB.EMPLOYEES
 GROUP BY DEPARTMENT_ID
 HAVING MIN(SALARY) > (SELECT MIN(SALARY)
                         FROM HRDB.EMPLOYEES
                        WHERE DEPARTMENT_ID = 50);

5.7.3 ANY & ALL 子查询

语法：

select 字段1，字段2 from 库名.表名

where 字段名3 比较运算符any(select 字段名4 from 库名.表名);

select 字段1，字段2 from 库名.表名

where 字段名3 比较运算符all(select 字段名4 from 库名.表名);

-- 举例：查出非IT_PROG职位的员工中薪水少于IT_PROG职位任一员工薪水的信息
select first_name,salary from hrdb.employees
where salary <any (select salary from hrdb.employees where job_id="IT_PROG")
and job_id<>"IT_PROG";
-- 举例：查出少于“IT_PROG职位”最低薪水的“非IT_PROG职位”员工信息
select * from hrdb.employees
where salary <all (select salary from hrdb.employees where job_id="IT_PROG")
and job_id !="IT_PROG";

5.7.4 EXISTS操作符的使用

EXISTS：存在的意思

NOT EXISTS:不存在

语法：

select 字段1，字段2 from 库名.表名

where [not]exists (子查询语句)；

-- 举例：找出员工表中职位是经理的员工(经理也是员工经理id就是他的员工id，员工编号在企业内唯一)
select employee_id,first_name,last_name
from hrdb.employees e
where exists(select * from hrdb.employees where manager_id=e.employee_id);
-- 举例：找出部门表中没有员工的部门
SELECT department_id,department_name 
FROM hrdb.`departments` d
WHERE NOT EXISTS (SELECT department_id FROM hrdb.employees WHERE department_id= d.`department_id`);
-- 在子查询中，在员工表中查询部门编号，将与部门表中可以匹配上的部门编号筛选出来，
-- 结果再用not exists处理，处理结果作为查询条件

5.8高级函数处理（case when用法）

-- 示例：查询员工表中，查询字段包括姓，职位编号，薪水，并且，在查询结果中显示将职位编号分别为IT_PROG、ST_CLERK、SA_REP的员工的薪资分别上调1.1、1.15、1.2倍的相关数据。
SELECT last_name,job_id,salary,
CASE job_id
WHEN "IT_PROG" THEN salary*1.1
WHEN "ST_CLERK" THEN salary*1.15
WHEN "SA_REP" THEN salary*1.2
ELSE salary
END 调整后薪水
FROM hrdb.`employees`;

6.存储过程

6.1存储过程概念说明：

--存储过程概念

>1、存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集；

>2、一个存储过程是一个可编程的函数，它在数据库中创建并保存；

>3、存储过程可以通过存储过程名被调用,多次调用；

--存储过程的作用

>1、存储过程封装的SQL语句已经过预编绎，执行速度比较快；

>2、存储过程可以接收参数、输出参数、并给出返回值；

>3、存储过程可以简化复杂操作；

6.2 创建存储过程

语法：

delimiter //

create procedure 库名.存储过程名

begin

SQL语句；

end //

delimiter ;

举例：创建存储过程，往表中批量插入数据
DELIMITER //
CREATE PROCEDURE test02()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i<50
DO
INSERT INTO test.`course`(c_id,c_no,c_name) VALUES(i,CONCAT("zhoujielun",i),CONCAT("01",i));
SET i=i+1 ;
END WHILE ;
END //
DELIMITER ;

6.3调用存储过程语法：

语法

call 库名.存储过程名

--举例：
CALL hrdb.`test02`;  -- 调用存储过程test02

6.3删除存储过程

语法：

drop procedure 库名.存储过程名

--举例：删除存储过程test01
drop procedure test.p5;

7.视图

7.1 视图概念说明：

--视图概念

1） 视图是从一个或几个基本表（或视图）导出的表,它与基本表不同，是一个虚表；

2）数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中;

3）视图包含一系列带有名称的列和行数据（类似于真实表）；

4）基本表中的数据发生变化，从视图中查询出的数据也就随之改变；

5）视图是存储在数据库中可用于查询的SQL语句。

--视图的作用

1）使数据更加安全，视图可以隐藏一些数据；

2）在视图中可以封装复杂的SQL语句，可使复杂的查询易于理解和使用（只需要将视图当做表一样从中查询数据，不用理会其复杂查询语句）；

3）简化操作；

4）视图使用户能以多种角度看待同一数据；

5、避免重复访问相同的数据。

7.2创建视图

语法：

create view 库名.视图名

AS

sql语句;

--举例：创建一个视图，查询员工表中所有员工的信息。
create view hrdb.view_01
AS
select * from hrdb.employees;

7.3 从视图中查询数据

语法：

SELECT 字段名1,字段名2 FROM 库名.`视图名`;

--举例：
SELECT employee_id,salary FROM hrdb.`view_01`;

7.4 删除视图

语法：

drop view 库名.视图名；

 --举例： 
 DROP VIEW hrdb.`view_01`;

FAQ

1. MySQL数据类型

1.1数值型

数据类型	字节大小	范围（有符号）	范围（无符号）	用途	备注
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值	如：float（7，3）表示总长度7位，整数部分4位，小数部分3位。
						DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数	double（7，3）表示总长度7位，整数部分4位，小数部分3位。
DECIMAL	对DECIMAL(M,D) ，依赖于M和D的值，M值为整数部分加小数部分的总位数，D值是小数部分的位数。			小数值	DECIMAL(4,1)表示，总长度为4位，整数部分为3位，小数部分为1位。
科学计数法说明： 1.23E+10，表示1.23乘以10的10次方。

1.2字符串型

字符串类型	字节大小	描述及存储需求	备注
CHAR	0-255字节	定长字符串，如CHAR(10)，定长10占位，不足补空格	char(4)存储4个字符，根据编码方式的不同占用不同的字节数
VARCHAR	0-255字节	变长字符串 ，如VARCHAR（10），最长10个字节，存储长度按照实际输入长度为准	varchar保存可变长度的字符串，使用额外的一个或两个字节存储字符串长度，varchar(10),除了需要存储10个字符，还需要1个字节存储长度信息(10),超过255的长度需要2个字节来存储
TINYBLOB	0-255字节	不超过255个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据
MEDIUMBLOB	0-16777215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LOGNGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
VARBINARY(M)	M	允许长度0-M个字节的定长字节符串，值的长度+1个字节	varbinary保存变长的字符串
BINARY(M)	M	允许长度0-M个字节的定长字节符串	binary保存二进制字符串，它保存的是字节而不是字符，没有字符集限制；binary(8)可以保存8个字符，每个字符占1个字节，共占8个字节

1.3日期时间型

类型	字节大小	范围	格式	用途
DATE	4字节	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3字节	-838:59:59/838:59:59	HH:MM:SS	时间值
YEAR	1字节	1901/2155	YYYY	年份值
DATETIME	8字节	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4字节	1970/1/1 0:00	YYYYMMDD HHMMSS	混合日期和时间值，即时间戳，用于自动存储INSERT或UPDATE操作时记录日期和时间

1.4混合型

数据类型	数据说明	数据定义举例
ENUM类型	（1）ENUM类型是一个允许你输入可能值的列表；	ENUM(x,y,z,etc.)、ENUM('X','Y','Z')
	（2）在处理相互排拆的数据时容易让人理解，比如人类的性别；
	（3）ENUM类型在系统内部可以存储为数字；
	（4）一个ENUM 类型最多可以包含65536个元素，其中一个元素被MySQL保留；
SET类型	（1）SET数据是一个字符串对象，可以有零或多个值；	SET('a','b','c','d')
	（2）指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开；
	（3）SET最多可以有64个不同的成员。

3、连接mysql数据库

3.1 使用SQLyog登录远程linux服务器上的mysql

step1 明确linux服务器的机器IP

ifconfig

step2 明确sqlyog所在机器的IP（windows机器）

ipconfig

step3 确保两台机器能够互相ping通对方（保证网络畅通）；

ping 172.31.95.41 (本机ping虚拟机)

ping 172.31.95.89 （虚拟机ping本机）

step4 关闭linux服务器的防火墙；

service iptables stop

step5 查看linux服务器上的mysql服务处于运行状态

service mysqld status （查看mysql服务器的状态）

service mysqld start

step6 在linux服务器上的mysql中，创建一个远程登录用户（主机IP设为%）；

-- 新建用户test，主机IP设置为%，并为其授予所有权限，用户密码为123456；

INSERT INTO mysql.`user`(HOST,USER,PASSWORD) VALUES("%","test",PASSWORD("123456"));

-- 2、为用户test授权

GRANT ALL PRIVILEGES ON *.* TO test@"%" IDENTIFIED BY "123456";

-- 3、刷新权限表，使授权生效

FLUSH PRIVILEGES;

step7 在sqlyog所在机器上，通过sqlyog使用远程用户登录linux服务器上mysql；

IP：输入虚拟机的IP地址：

用户名：test

密码：123456

3.2 cmd命令行模式下连接登录mysql数据库

3.2.1语法：myql -参数1 用户名 -参数2

mysql -uroot -p；

u:user（用户）的简写

root：mysql数据库的超级管理员

p:password:密码的简写

举例：

root用户在cmd模式下连接mysql数据库，密码是123456.

sql语句：

mysql -uroot -p（或者mysql -u root -p）

问题：语句“mysql -uroot -p”在目录

“C:\Documents and Settings\we>”下运行，出现错误：

'mysql' 不是内部或外部命令，也不是可运行的程序

或批处理文件。

解决办法：

# 进入到mysql安装路径bin目录下再执行即可
C:\Documents and Settings\we>cd ..
C:\Documents and Settings>cd ..
C:\>
C:\>cd xampp
C:\xampp>cd mysql
C:\xampp\mysql>cd bin
C:\xampp\mysql\bin>mysql -uroot -p
Enter password(提示输入密码):这里的密码为空，直接回车即可；

3.2 常用操作

3.2.1 查看数据库实例

show databases；

说明：登录进数据库以后，查看有哪些数据库，此句查询结果为数据库中的实例信息。

3.2.2 选中打开一个数据库实例

use 数据库实例；

说明：选中打开一个数据库。

例如：use mysql； # 打开名为mysql数据库实例，出现Database changed，即表示打开指定的数据库成功。

3.2.3 查看数据库实例中有那些表

show tables；

3.2.4 查看一个表中有哪些字段：

4.6.1 方式一：describe 库名.表名；

举例：mysql>describe mysql.user; #查看mysql数据库，user表中有哪些字段

4.6.2方法二：desc 库名.表名；

举例：mysql>desc zentao.zt_usertpl; # 查看zentao数据库， zt_usertpl表中有哪些字段

字段显示结果：

+---------+-----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------+-----------------------+------+-----+---------+----------------+

| id | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |

| account | char(30) | NO | MUL | NULL | |

| type | char(30) | NO | | NULL | |

| title | varchar(150) | NO | | NULL | |

| content | text | NO | | NULL | |

| public | enum('0','1') | NO | | 0 | |

+---------+-----------------------+------+-----+---------+----------------+

解释：

Field字段的意思；

Type数据类型的意思

Null 为空的意思

Key：关键字的意思，可能为主键，也可能为外键

Extra：额外的，auto_increment意思是字段长度自动增加。

3.3退出数据库

方法一：mysql>quit

方法二：mysql>exit