MySQL 服务的启动和停止
命令行
停止服务
net stop mysql
开启服务
net start mysql
操作系统
此电脑 --> 管理 --> 服务和应用程序 --> 服务 --> MySQL --> 启动
连接数据库
mysql -h localhost -P 端口号 -u 用户名 -p密码
如果连接的是本机上端口号为 3006 的数据库可以简写为
mysql -u用户名 -p密码
MySQL 常见命令
show databases;# 显示所有库
use 库名;# 进入某库中
show tables;# 显示所有表
show tables from 库名;# 显示某库中所有表
select database();# 显示当前在哪个库中(MySQL特有)
creat table 表名();# 创建一个表
desc 表名;# 查看当前表的结构
select * from 表名;# 查看当前表的数据
insert into 表名(类型) values(数据);# 向表中插入数据(MySQL特有)
update 表名 set 类型=数据 where 类型=?;# 将?处的数据修改成“数据”
delete from 表名 where 类型=?;# 将?处删除
select version();# 查看数据库版本
单行注释:
#
-- (后面必须有一个空格)
多行注释
/*
*/
DQL语言
Data Query Language:数据查询语言
基础查询
# 查询表(employees)中单个字段(last_name)
SELECT last_name FROM employees;
# 查询表(employees)中多个字段(last_name,salary,email)
SELECT last_name,salary,email FROM employees;
# 查询表(employees)中所有字段
SELECT * FROM employees;
# 查询常量值(100)
SELECT 100;
# 查询表达式(100%98)
SELECT 100%98;
# 查询函数 (VERSION())
SELECT VERSION();
其它操作
# 起别名
SELECT 100 % 98 AS 结果;
SELECT 100 % 98 结果;
# 去重
SELECT DISTINCT department_id FROM employees;# 去掉 employees 表中重复的 department_id
MySQL中+号的作用:
MySQL 中 + 号的作用只有一个,即运算符
如果2个操作数都是数字,则进行加法运算
如果一个是字符,一个是数字,则试图将字符转换为数字
如果转换成功,则继续做加法运算
如果转换失败,则将字符转换为0做加法运算
如果一个是 null,则结果必为 null
条件查询
语法
SELECT 查询列表 FROM 表名 WHERE 筛选条件;
分类
条件表达式
> < =(判断2操作数是否相等) !=(不推荐,推荐使用<>) >= <=
例
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <> 90 ;
逻辑表达式
不推荐:&& || !
推荐:and or not
例
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE
salary >= 10000
AND salary <= 12000 ;
模糊查询
LIKE
/*
* 通常与通配符搭配使用:
* ① '%':匹配任意多个任意字符
* ② '_':匹配单个任意字符
* ③ '\':转义字符
* ④ 任意字符也可以设为转义字符:在后面加上ESCAPE '@';(将'@'设置成转义字符)
*/
例
SELECT
*
FROM
employees
WHERE
last_name LIKE '_@_%' ESCAPE '@';
BETWEEN AND
例
SELECT
*
FROM
employees
WHERE
salary BETWEEN 10000 AND 20000;# 查找 salary 在 [10000, 20000] 中的所有字段
IN
例
SELECT
*
FROM
employees
WHERE
job_id IN('AD_VP','SA_MAN');
注意:IN不支持通配符
IS NULL/ IS NOT NULL
例
SELECT
*
FROM
employees
WHERE
commission_pct IS NULL;
注意:’=’ 不能判断 NULL
<=> 安全等于,可以判断 NULL
SELECT
*
FROM
employees
WHERE
salary <=> 12000;
排序查询
SELECT 查询列表 FROM 表 【where 筛选条件】 ORDER BY 排序列表【ASC|DESC】
默认为 ASC(升序)
例
SELECT
*,salary*12*(IFNULL(commission_pct,0)+1) AS 年薪
FROM
employees
ORDER BY 年薪 DESC;
函数排序(LENGTH 返回字符长度)
SELECT
*
FROM
employees
ORDER BY LENGTH(last_name) DESC;
先按 ?排序,再按 ?排序
# 先按工资升序排序,再按员工编号降序排序
SELECT
*
FROM
employees
ORDER BY salary ASC, employee_id DESC;
ORDER BY 子句一般放在查询语句的最后面,LIMIT 子句除外
字符函数
CONCAT 字符拼接函数
CONCAT(str1, str2...)
例
SELECT
CONCAT(
`first_name`,
',',
`last_name`,
',',
`email`,
',',
`phone_number`
) AS out_put
FROM
employees ;
如果其中有一个字符为 null,则拼接后的字符也为 null
IFNULL 判断是否为 null 函数
例
SELECT
# 如果是 null 返回0,不然返回原来的值
IFNULL(commission_pct, 0) AS 奖金率
FROM
employees ;
ISNULL 判断是否为 null 函数
SELECT ISNULL(?);# ? 是 null 返回1,不是返回0
LENGTH 判断字节
长度
SELECT LENGTH('hello');# 5
SELECT LENGTH('威士忌');# 9,使用utf-8编码,一个汉字占3位
UPPER、LOWER 将字符转换成大、小写
SELECT UPPER('heLLo');
SELECT LOWER('heLLo');
SUBSTRING(SUBSTR) 截取字符
# 截取从指定索引处后面所有字符
SELECT SUBSTRING('李莫愁爱上了陆展元', 7) AS out_put;
# 截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元', 1, 3) AS out_put;
注意索引从1开始
INSTR 返回 str2 在 str1 中首次出现的位置,找不到返回 0
SELECT INSTR(str1, str2) AS out_put;
TRIM 去掉前后内容,内容默认为空格
SELECT TRIM(' 123 ') AS out_put;# (123)前后无空格
SELECT TRIM('a' FROM 'aaa1a1aaa') AS out_put;# 1a1
LPAD 用指定的字符左填充指定长度
SELECT LPAD('123', 5, '*');# **123
SELECT LPAD('123', 2, '*');# 12,当字符长度小于指定长度时,从右边开始截取多余的长度
RPAD 用指定的字符右填充指定长度
SELECT RPAD('123', 5, '*');# 123**
SELECT RPAD('123', 2, '*');# 12,当字符长度小于指定长度时,从右边开始截取多余的长度
REPLACE 替换字符
SELECT REPLACE('1123', '1', '4');# 4423,若存在多个便全部替换
数学函数
ROUND 四舍五入
SELECT ROUND(-3.9);# -4
SELECT ROUND(1.567, 2);# 1.57
CEIL 向上取整
返回大于等于它的最小整数
SELECT CEIL(-1.02);# -1
FLOOR 向下取整
返回小于等于它的最大整数
SELECT FLOOR(-9.5);# -10
TRUNCATE 截取函数
截掉小数点后?位后面的所有数
SELECT TRUNCATE(1.69999, 1)# 1.6
MOD 取余函数
SELECT MOD(-10, 3);# -1
日期函数
NOW 返回当前系统日期和时间
SELECT NOW();
CURDATE 返回当前系统日期
SELECT CURDATE();
CURTIME 返回当前系统时间
SELECT CURTIME();
获取指定的部分
# 年
SELECT YEAR(NOW());
# 月
SELECT MONTH(NOW());
# 月名(英文)
SELECT MONTHNAME(NOW());
字符转换成日期
SELECT STR_TO_DATE('1998-3-2', '%Y-%c-%d');
日期转换成字符串
SELECT DATE_FORMAT(NOW(), '%Y年%c月%d日');
其他函数
SELECT VERSION(); # MySQL 版本
SELECT DATABASE(); # 当前数据库
SELECT USER(); # 当前用户
流程控制函数
IF 函数
# 如果 expr1 返回 true,则 IF 函数返回 expr2,否则返回 expr3
SELECT IF(expr1, expr2, expr3);
CASE 函数
用法一
CASE 值
WHEN 值为?时 THEN 语句或值
...
ELSE 语句或值
END
用法二
CASE
WHEN 条件 THEN 语句或值
...
ELSE 语句或值
END
分组函数
# 求和,只能用于数值型
SELECT SUM(salary) FROM employees;
# 求平均值,只能用于数值型
SELECT AVG(salary) FROM employees;
# 求最大值,可以处理任何数据类型
SELECT MAX(salary) FROM employees;
# 求最小值,可以处理任何数据类型
SELECT MIN(salary) FROM employees;
# 求该字段下所有不为 null 的数量,可以处理任何数据类型
SELECT COUNT(salary) FROM employees;
# 求所有字段不全为 null 的数量
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
DISTINCT 去重
SELECT COUNT(DISTINCT salary) FROM employees;
DATEDIFF 日期相差天数
SELECT DATEDIFF(NOW(), '1949-10-01');
GROUP BY
# 各个部门中邮箱中带有a的员工的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
HAVING
# 员工数大于2的所有部门的人数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;
连接查询
|-- 内连接
|-- 等值连接
|-- 非等值连接
|-- 自连接
|-- 外连接
|-- 左外
|-- 右外
|-- 交叉连接
以下为 SQL92 内连接语法
等值连接
多表连接的结果为多表的交集部分
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;
注意给表起别名之后不能再用原来的表名了
非等值连接
SELECT salary,grade_level
FROM employees,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;
自连接
SELECT e.employee_id 员工id,e.last_name 员工姓名,m.employee_id 领导id,m.last_name 领导姓名
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
以下为 SQL99 内连接语法
等值连接
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN `locations` l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
INNER 可以省略
非等值连接
SELECT COUNT(*),grade_level
FROM employees e
INNER JOIN job_grades j
ON salary BETWEEN j.`lowest_sal` AND j.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
自连接
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;
以下为 SQL99 外连接语法
左连接
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName` IS NULL;
右连接
SELECT b.name
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName` IS NULL;
全外连接
MySQL 不支持
交叉连接
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;
子查询
SELECT 后面:只支持标量子查询
FROM 后面:支持表子查询
WHERE 或 HAVING 后面:支持标量子查询、列子查询、行子查询
EXISTS 后面:支持表子查询
|-- 标量子查询:结果集只有一行一列
|-- 列子查询:结果集只有一列多行
|-- 行子查询:结果集有一行多列、多行多列
|-- 表子查询:结果集一般为多行多列
标量子查询
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
列子查询
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
行子查询
# 查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
SELECT 后面的子查询
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id=d.`department_id`
) AS 员工个数
FROM departments d
FROM 后面的子查询
SELECT ag_table.*,grade_level
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_table
INNER JOIN job_grades
ON ag BETWEEN lowest_sal AND highest_sal;
EXISTS 后面的子查询
# 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
分页查询
注意起始条目索引从0开始
SELECT
employee_id,
salary,
commission_pct
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0, 10;
联合查询
UNION 关键字默认去重,使用 UNION ALL 可以包含重复项
SELECT cid,cname,csex FROM t_ca WHERE csex='男'
UNION
SELECT tid,tname,tsex FROM t_ua WHERE tsex='male';