1、数据库语言的分类
DDL：数据库定义语言 data Definition language
用于创建、修改、和删除数据库内的数据结构，如：
1：创建和删除数据库(CREATE DATABASE || DROP DATABASE)；
2：创建、修改、重命名、删除表(CREATE TABLE || ALTER TABLE|| RENAME TABLE||DROP TABLE，TRUNCATE TABLE)；
3：创建和删除索引(CREATEINDEX || DROP INDEX)
DML：数据操控语言 data Manipulation language
修改数据库中的数据，包括插入(INSERT)、更新(UPDATE)和删除(DELETE)
DCL：数据控制语言 （管理用户权限）data Control language
用于对数据库的访问，主要包括创建用户、给用户授权、对用户撤销授权、查询用户授权和删除用户等
，如：1.创建用户（create user）2：给用户授予访问权限（GRANT）;3：取消用户访问权限（REMOKE），4删除用户（drop user）。
DQL：数据查询语言 data Query language
从数据库中的一个或多个表中查询数据(SELECT)
下面一一来介绍这四种类型的语言
2、DDL：数据库定义语言 data Definition language
2.1、创建数据库，并且以utf8的字符集创建
-- 如果不存在，则创建数据库,并且以utf的字符集创建
CREATE DATABASE IF NOT EXISTS westos DEFAULT CHARACTER SET = 'utf8'
2.2、如果存在，删除数据库
-- 如果存在，则删除数据库
DROP DATABASE IF EXISTS westos
2.3、使用数据库
-- 使用数据库
USE westos
2.4、查看所有的数据库
-- 查看所有的数据库
SHOW DATABASES
2.5、查看所有的数据表
-- 查看所有的数据表
SHOW TABLES
2.6、创建表
-- 创建学生表,如果不存在
CREATE TABLE IF NOT EXISTS student (
`id` INT NOT NULL AUTO_INCREMENT COMMENT '学生表id',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET = 'utf8'
2.7、查看创建数据库和数据表的语句
-- 查看创建数据库的语句
SHOW CREATE DATABASE westos
-- 查看创建数据表的语句
SHOW CREATE TABLE student
2.8、查看表的结构
-- 查看表的具体结构
DESC student
3、DQL：数据查询语言 data Query language
3.1、修改表名
-- 修改表名
ALTER TABLE student RENAME AS teacher
3.2、增加表的字段
-- 增加表的字段
ALTER TABLE teacher ADD age INT
3.3、修改表的字段
-- 修改表的字段（重命名，修改约束）
ALTER TABLE teacher MODIFY age VARCHAR(11) -- 修改约束，不能改字段名
ALTER TABLE teacher CHANGE age is_del INT -- 字段重命名
3.4、删除表的字段
-- 删除表的字段
ALTER TABLE teacher DROP is_del
3.5、删除表
-- 删除表
DROP TABLE IF EXISTS student
3.6、插入语句
-- 插入语句（添加）
INSERT INTO teacher (`name`) VALUES ('xgp')
3.7、修改语句
-- 修改语句
UPDATE teacher SET `name`='xgp',sex='女' WHERE id=1
3.8、设置数据库的时间
-- 设置数据库的时间
UPDATE `teacher` SET `birthday` = CURRENT_TIME WHERE id=1
3.9、删除指定数据
-- 删除指定数据
DELETE FROM teacher WHERE id=1
3.10、清空一个数据表
-- 完全清空一个数据库表，表的结构和索引约束不变,自增会归零
TRUNCATE TABLE teacher
4、DQL：数据查询语言 data Query language
4.1、使用别名查询
-- 查询（使用别名）
SELECT `name` 姓名,pwd 密码 FROM teacher 老师表
4.2、拼接字符串
-- 函数 拼接字符串 Concat(a,b)
SELECT CONCAT('姓名：',`name`) 新名字 FROM teacher
4.3、去重
-- 去重
SELECT DISTINCT `name` FROM teacher
4.4、查看系统版本
-- 查看系统版本
SELECT VERSION()
4.5、计算结果
-- 计算结果
SELECT 100*3-23 计算结果
SELECT pwd+'1' 密码加1 FROM teacher
4.6、模糊查询
-- 模糊查询
-- like结合 %（代表0到任意个字符） _（代表一个字符）
SELECT `name`,pwd FROM teacher
WHERE `name` LIKE '_g_'
-- in
SELECT id,`name`,pwd FROM teacher
WHERE id IN (4,7,10)
4.7、排序查询
-- 排序：升序 ASC 降序 DESC
-- order by 通过哪个字段排序，怎么排
SELECT * FROM teacher ORDER BY id DESC
SELECT * FROM teacher ORDER BY id ASC
4.8、分页查询
-- 分页：
SELECT * FROM teacher ORDER BY id DESC LIMIT 2,10
5、常用函数
5.1、数学函数
-- 常用函数
SELECT ABS(-78) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT SIGN(-9) -- 判断一个数的符号
5.2、字符串函数
-- 字符串函数
SELECT CHAR_LENGTH('哈哈哈') -- 字符串长度
SELECT CONCAT('xgp','aaa') -- 拼接字符串
SELECT INSERT('asa',1,2,'ss') -- 替换字符串
SELECT LOWER('AAAAAAAA') -- 转小写
SELECT UPPER('aaaaaaaa') -- 转大写
SELECT INSTR('aaaaa','a') -- 返回第一次出现子串的索引
SELECT REPLACE('aaaa','a','x') -- 替换字符串
SELECT SUBSTR('asasasxsssas',4,6) -- 截取字符串，截取的位置，截取的长度
SELECT REVERSE('aasss') -- 字符串反转
5.3、时间和日期函数
-- 时间和日期函数（获取当前日期）
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT NOW(); -- 获取当前的时间
SELECT LOCALTIME(); -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间
5.4、系统函数
-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()
5.5、聚合函数
-- 聚合函数
SELECT COUNT(*) FROM teacher
SELECT COUNT(字段) FROM teacher -- 会忽略null值
SELECT COUNT(1) FROM teacher
SELECT COUNT(主键) FROM teacher -- 速度更快
SELECT SUM(pwd) 求和 FROM teacher
SELECT AVG(pwd) 平均 FROM teacher
SELECT MAX(pwd) 最高 FROM teacher
SELECT MIN(pwd) 最低 FROM teacher
6、DCL：数据控制语言 （管理用户权限）data Control
6.1、创建用户
-- 创建用户
CREATE USER xgp123 IDENTIFIED BY '123456'
6.2、修改密码
-- 修改密码（修改当用户密码）
SET PASSWORD = PASSWORD('111111')
-- 修改密码（修改自定的用户的密码）
SET PASSWORD FOR xgp123 = PASSWORD('123456')
6.3、给用户重命名
-- 给用户重命名
RENAME USER xgp123 TO xgp
6.4、用户授权
-- 用户授权(授予全部的权限，库，表)
GRANT ALL PRIVILEGES ON *.* TO xgp
6.5、查看指定用户权限
-- 查看指定用户权限
SHOW GRANTS FOR xgp
6.6、查看管理员权限
-- 查看管理员权限
SHOW GRANTS FOR root@localhost
6.7、撤销权限
-- 撤销权限(撤销全部权限)
REVOKE ALL PRIVILEGES ON *_* FROM xgp
7、事务
7.1、开启/和关闭事务的自动提交，mysql默认是开启的
-- 事务（测试转账）mysql 是默认开启事务自动提交的
SET autocommit = 0; -- 先关闭事务提交
SET autocommit = 1; -- 开启
7.2、进行手动处理事务
-- 事务开启
START TRANSACTION -- 标记一个事务的开始
-- 提交
COMMIT
-- 回滚
ROLLBACK
-- 事务结束
-- 了解
SAVEPOINT -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名，会滚到保存点
7.3、通过转账案例手动测试事务
-- 编写转账案例
CREATE TABLE account (
id INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET = 'utf8'
INSERT INTO account(`name`,money) VALUES ('张三',2000.00),('李四',10000.00)
-- 模拟转账
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开启事务
UPDATE account SET money = money - 500 WHERE `name` = '张三';
UPDATE account SET money = money + 500 WHERE `name` = '李四';
COMMIT; -- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1; -- 恢复默认值
8、其他技术
8.1、对数据库进行备份
命令行执行导出命令 mysqldump
mysqldump -hlocalhost -uroot -p123456 westos > D:/a.sql
8.2、对数据库进行导入
-- 导入
source sql文件路径
8.3、对数据库进行md5加密
-- 数据库级别的MD5加密
UPDATE teacher SET pwd = MD5(pwd)
-- 插入时进行加密
INSERT INTO teacher(`name`,pwd) VALUES ('aaa',MD5('123456'))
-- 如何校验
SELECT * FROM teacher WHERE `name`='aaa' AND pwd = MD5('123456')
9、mysql基础语法的详细语法格式文档
mysql产品的常见命令
1，mysql服务的启动和停止
方式一：计算机——右键管理——服务
方式二：通过管理员身份运行
net start 服务名（启动服务）
net stop 服务名（停止服务）
2，mysql服务的登陆和退出
方式一：通过mysql自带的客户端
只限于root用户
方式二：通过cmd命令行
登陆：
mysql [-h主机名 -P端口号] -u用户名 -p[密码]
退出：
exit 或者 ctrl+c
3，sql语句的基本使用
1.查看当前所有数据库
show databases;
2.打开指定库
user 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名 (
列名 列类型,
列名 列类型,
...
);
6.查看表结构
desc 表名;
7.查看服务器版本
方式一：登陆到mysql服务端
select version();
方式二：没有登陆到mysql服务端
mysql --version 或者 mysql -V
8.注释
单行：#注释文字
-- 注释文字
多行：/*注释文字*/
DQL语言的学习
阶段一：1，基础查询
语法：
select 查询列表 from 表名;
查询列表：
表中的字段，常量值，表达式，函数
1.查询表中的单个字段
select last_name from employees;
2.查询表中的多个字段
select last_name,salary,email from employees;
3.查询表中的所有字段
select * from employees;
2，其他基础查询
1.查询常量值
select 100;
select 'join';
2.查询表达式
select 100*98;
select 100%98;
3.查询函数
select version();
3，为字段起别名
1.方式一：（使用as）
select 100%98 as 结果;
select last_name as 姓 from employees;
2.方式二：（使用空格）
select last_name 姓 from employees;
4，去重(distinct)
1.案例：查询员工表的所有部门的编号
select distinct
department_id
from
employees;
5，+号的作用
1.案例：查询员工名和姓连接成一个字段，并显示为 姓名
错误做法：
select
last_name + first_name as 姓名
from
employees;
正确做法：（使用concat()函数）
select
concat(last_name,first_name) as 姓名
from
employees;
6，ifnull()函数的使用
格式：
ifnull(判断是否为空的参数,若为空返回的值)
举例：
select ifnull(commission_pct,0) as 奖金率;
###阶段二：1，条件查询 语法： select 查询列表 from 表名 where 筛选条件; 筛选条件： 1.按照条件表达式筛选 条件运算符： > < = .... 案例： select * from employees where salary > 12000; 2.按照逻辑运算符刷选 逻辑运算符： && || ！ mysql推荐： and or not 案例： select * from employees where salary > 12000 and salary < 20000; 3.模糊查询 1.关键字：like 特点： 一般和通配符使用 % 任意多个字符，包含0个字符 _ 任意多个字符 案例：查询员工名中包含字符a的员工信息 select * from employees where last_name like '%a%'; 特殊案例（需要转义通配符 escape）：查询员工名中第二个字符为_的员工信息 select * from employees where last_name like '_$_%' escape '$'; 2.关键字：between and 特点： 包含临界值 案例： select * from employees where salary between 12000 and 20000; 3.关键字：in 案例： select * from employees where job_id in ('IT_PROT','AD_VP','AD_PRES'); 4.关键字：is null 注意：=号不能判断null值 案例： select * from employees where commission_pct is null; ###2，两个特殊符号的介绍（<>,<=>） 1.<>相当于!=,不过mysql更推荐改写法 2.<=>安全等于，可以判断null值
###阶段三：1，排序查询 语法： select 查询条件 from 表 [where 筛选条件] order by 排序列表 [asc|desc] 注意： asc：升序 desc：降序 不写：默认升序 案例(从高到低 desc)： select * from employee order by salary desc; 案例(从低到高 asc)： select * from employee order by salary asc; 特殊案列：按姓名的字节长度显示员工的姓名和工资（按函数排序 length(last_name)） select length(last_name) 字节长度，last_name,salary from employees order by length(last_name) desc; ###2，多个字段排序 1.案例：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】 select * from employees order by salary asc,employee_id desc;
###阶段四：常见函数 1.调用：select 函数名(实参列表) [from 表]; 2.分类：单行函数，聚合函数 ###1，字符函数 1.length(字符) 获取参数值的字节个数 select length('join'); select length('哈'); 2.concat(参数1,参数2,...) 拼接字符串 select concat(last_name,'_',first_name) 姓名 from employees; 3.upper(字符) lower(字符) select upper('dadada'); select lower('HJJJI'); 4.substr(字符,位置1,位置2)，substring(字符,位置1,位置2) 截取字符 注意：mysql中索引从1开始 select substr('hduxshdfkjsf',2,6); 5.instr(字符，查找的字符字串) 放回子串第一次出现的索引，如果找不到放回0 select instr('dsfsfd','fs'); 6.trim(字符) 去除前后空格 select length(trim(' 长记性了 ')) as out_put 其他用法：去除前后指定字符 trim(字符 from 字符串); select trim('a' from 'aaaaaaaaa或许是第aaaaaacxhddjkhaaaaaaaaa') as out put; 7.lpad(字符,个数,填充字符) 用指定的字符实现左填充指定的长度 select lpad('xgp',2,'*') as out_put; 8.rpad(字符,个数,填充字符) 用指定的字符实现右填充指定的长度 select rpad('xgp',12,'*') as out_put; 9.replace(原字符串,要替换字符，替换字符) 替换 select replace('xgp123','xgp','123') as oup_put; ###2，数学函数 1.round(数值,保留几位小数) 四舍五入 select round(1.65); select round(1.675,2); 2.ceil(数值) 向上取整 select ceil(1.02); 3.floor(数值) 向下取整 select floor(-9.99); 4.truncate(数值，保留几位小数) 截断 select truncate(1.66,1); 5.mod(被取余数，取余子) 取余 select mod(-10,-3); ###3，日期函数 1.now() 放回当前系统的日期+时间 select now(); 2.curdate() 放回当前系统日期，不包含时间 select curdate(); 3.curtime() 放回当前系统时间，不包含日期 select curtime(); 4.可以获取指定的部分，年、月、日、小时、分钟、秒 select year(now()) 年; 5.str_to_date(格式，字符类型) 将日期格式的字符转换成指定格式的日期 select str_to_date('1998-3-2','%Y-%c-%d'); 6.date_format(格式，字符类型); 将日期转换成字符 select date_format(now(),'%y年%m月%d日'); ###4，其他函数 1.select version(); 查看mysql版本 2.select database(); 查看当前数据库 3.select user(); 查看当前用户 ###5，流程控制函数 1.if(判断条件,if结果,else结果)函数 if else 的效果 select if(10>5,'大','小'); 2.case()函数的使用一 case 要判断的字段或者表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 when 常量3 then 要显示的值3或语句3 ...... else 要显示的值n或者语句n end
案例：查询员工工资，要求
部门号=30，显示工资为1.1倍
部门号=40，显示工资为1.2倍
部门号=50，显示工资为1.3倍
其他部门，显示为原工资
SELECT
salary 原始工资,department_id ,
CASE
department_id = 30
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE
salary
END
AS 新工资
FROM
employees;
3.case()函数的使用二
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
......
else 要显示的值n或语句n
end
案例：查询员工的工资的情况
如果工资>20000,A
如果工资>15000,B
如果工资>10000,C
否则 D
SELECT salary,
CASE
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
6，聚合函数
1.sum(字段) 求和 avg() 平均值 min() 最小值 max() 最大值 count() 计数
select sum(salary) from employees;
2.特点：
1.sum(),avg() 支持数值型
min(),max(),count() 支持任何类型
2.上述聚合函数都排除了null值
3.和distinct搭配使用
select count(distinct salary),count(salary) from employees;
4.count() 函数的详细介绍
select count(*) from employees;
select count(1) from employees;
效率：
MYISAM存储引擎下，count(*) 的效率高
INNODB存储引擎下，count(*)和count(1)的效率差不多，比count(字段)要高一些
5.和聚合函数使用，一般都不会带字段
###阶段五：1，分组查询 1.语法： select 分组函数，列 （要求出现在group by的后面） from 表 [where 筛选条件] group by 分组的列表 [order by 子句] 2.注意: 查询列表必须特殊，要求是分组函数和group by后出现的字段 3.案例1：查询每个工种的最高工资 select max(salary),job_id from employees group by job_id; ###2，添加分组前筛选 案例1：查询邮箱中包含a字符的，每个部门的平均工资 select avg(salary),department_id from employees where email like '%a%' group by department_id; 案例2：查询有奖金的每个领导手下员工的最高工资 select max(salary),manager_id from employees where commission_pct is not null group by manager_id; ###3，添加分组后筛选 1.案例1：查询哪个部门的员工个数>2 1.查询每个部门的员工个数 select count(*),department_id from employees group by deparment_id; 2.根据1的结果，查询哪个部门的员工数>2 select count(*),department_id from employees group by deparment_id; having count(*)>2; 2.案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 select max(salary),job_id from employees where commission_pct is not null group by job_id having max(salary) > 12000; ###4，按多个字段分组 1.案例：查询每个部门每个工种的员工的平均工资 select avg(salary),department_id,job_id from employees group by deparentment_id,job_id; ###5，添加排序 1.案例：查询每个部门各个工种的员工的平均工资，并且按平均工资的高低显示 select avg(salary) a,department_id,job_id from employees where department_id is not null group by job_id,department_id having avg(salary) > 10000 order by avg(salary) desc;
###阶段六：1，连接查询（多表查询） 笛卡尔乘积现象：没有有效的连接条件
如何避免：添加有效的连接条件
举例：
SELECT
NAME,boyName
FROM
boys,beauty
WHERE
beauty.boyfriend_id = boys.id;
分类：
按年代分类：
sql92标准：仅仅只支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外） + 交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
2，sql92标准
1，等值连接
1.案例1：查询员工名和对应的部门名
SELECT
last_name,department_name
FROM
employees,departments
WHERE
employees.department_id = departments.department_id
2.案例2：查询每个工种的工种名和员工的个数，并且按员工个数升序
SELECT
job_title,COUNT(*)
FROM
employees e,jobs j
WHERE
e.job_id = j.job_id
GROUP BY
job_title
ORDER BY
COUNT(*) DESC;
3.案例3：查询员工名、部门名和所在的城市（三表查询）
SELECT
last_name,department_name,city
FROM
employees e,departments d,locations l
WHERE
e.department_id = d.department_id
AND
d.location_id = l.location_id
AND
city LIKE 's%'
ORDER BY
department_name DESC;
2，非等值连接
1.案例1：查询员工的工资和工资级别
SELECT
salary,grade_level
FROM
employees e,job_grades g
WHERE
salary BETWEEN g.lowest_sal AND g.highest_sal;
3，自连接
1.案例：查询 员工名和上级的名称
SELECT
e.employee_id,e.last_name,m.employee_id,m.last_name,m.department_id
FROM
employees e,employees m
WHERE
e.manager_id = m.employee_id;
3，sql99标准
1.语法：
select
查询列表
from
表1 别名 连接类型
join
表2 别名
on
连接条件
[where
筛选条件]
[group by
分组]
[having
筛选条件]
[order by
排序列表]
2.连接类型
内连接(*)：inner
外连接：
左外(*)：left [outer]
右外(*)：right [outer]
全外：full [outer]
交叉连接：cross join
1，内连接
1.语法：
select
查询列表
from
表1 别名
inner join
表2 别名
on
连接条件
1，等值连接
1.案例1：查询哪个部门的员工个数>3的部门名和员工个数，并按照个数降序（添加排序）
1.查询每个部门的员工个数
SELECT
COUNT(*),department_name
FROM
employees e
INNER JOIN
departments d
ON
e.department_id = d.department_id
GROUP BY
department_name
2.在1的结果上筛选员工个数>3的记录，并排序
SELECT
COUNT(*),department_name
FROM
employees e
INNER JOIN
departments d
ON
e.department_id = d.department_id
GROUP BY
department_id
HAVING
COUNT(*) > 3
ORDER BY
COUNT(*) DESC;
2，非等值连接
2.案例1：查询员工的工资级别
SELECT
salary,grade_level
FROM
employees e
JOIN
job_grades g
ON
e.salary
BETWEEN
g.lowest_sal
AND
g.highest_sal;