一、数据库基础
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
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