MySQL基础

1、DB：数据库，存储数据的容器

1、数据存放到表中，然后表再放到库中
2、一个库中可以有多张表，每张表具有唯一的表名用来标识自己
3、表中有一个或多个列，列又称为“字段”，相当于java中“属性”
4、表中的每一行数据，相当于java中“对象”

mysql、oracle、db2、sqlserver

1、开源、免费、成本低
2、性能高、移植性也好
3、体积小，便于安装

select 查询列表
from 表名;

1、查询列表可以是字段、常量、表达式、函数，也可以是多个
2、查询结果是一个虚拟表

select 字段名 from 表名;

SELECT `last_name` FROM `employees`;

select 字段名，字段名 from 表名;

SELECT `last_name`,`email`,`employee_id`
FROM employees;

select * from 表名

SELECT * FROM `employees`;

select 常量值;
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要

SELECT 100 ;

select 函数名(实参列表);

SELECT DATABASE();
SELECT VERSION();
SELECT USER();

select 100/1234;

select 100/1234;

①as
②空格

#方式一：使用as关键字

SELECT USER() AS 用户名;
SELECT USER() AS "用户名";
SELECT USER() AS '用户名';

SELECT last_name AS "姓 名" FROM employees;


#方式二：使用空格


SELECT USER()   用户名;
SELECT USER()   "用户名";
SELECT USER()   '用户名';

SELECT last_name   "姓 名" FROM employees;

select distinct 字段名 from 表名;

#需求：查询员工涉及到的部门编号有哪些


SELECT DISTINCT department_id FROM employees;

作用：做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值，如果转换成功，则继续运算；否则转换成0，再做运算
select null+值;结果都为null

-- 需求：查询 first_name 和last_name 拼接成的全名，最终起别名为：姓 名

#方案1：使用+    pass×
SELECT first_name+last_name AS "姓 名"
FROM employees;



#方案2：使用concat拼接函数

SELECT CONCAT(first_name,last_name) AS "姓 名"
FROM employees;

功能：拼接字符
select concat(字符1，字符2，字符3,...);

SELECT CONCAT(first_name,last_name) AS "姓 名"
FROM employees;

功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值

select ifnull(commission_pct,0) from employees;

功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0

select 查询列表
from 表名
where 筛选条件

#案例：查询工资<15000的姓名、工资
SELECT last_name,salary
FROM employees
WHERE salary<15000;

> < = <> != >= <=  <=>安全等于

#案例：查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id <> 100;

&& and
|| or
!  not

#案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
#方式1：
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;
#方式2：


SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);

like:一般搭配通配符使用，可以判断字符型或数值型
通配符：%任意多个字符，_任意单个字符

#案例1：查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';

 功能：判断某个字段的值是否介于xx之间

#案例1：查询部门编号是30-90之间的部门编号、员工姓名

#方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

功能：查询某字段的值是否属于指定的列表之内
a  in(常量值1,常量值2,常量值3,...)
a not in(常量值1,常量值2,常量值3,...)
in/not in
is null /is not null：用于判断null值

#案例1：查询部门编号是30/50/90的员工名、部门编号


#方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

is null PK <=>
              普通类型的数值         null值               可读性
is null              ×                    √                 √
<=>                  √                    √                 ×

#案例1：查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;

#案例2：查询有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

=		只能判断普通的内容

IS      只能判断NULL值

<=>     安全等于，既能判断普通内容，又能判断NULL值

SELECT *
FROM employees
WHERE salary <=> 10000;

SELECT *
FROM employees
WHERE commission_pct <=> NULL;

select 查询列表
from 表
where 筛选条件
order by 排序列表 【asc}desc】

1、asc ：升序，如果不写默认升序
   desc：降序

2、排序列表 支持 单个字段、多个字段、函数、表达式、别名

3、order by的位置一般放在查询语句的最后（除limit语句之外）

#案例1：将员工编号>120的员工信息进行工资的升序
SELECT *
FROM employees

ORDER BY salary ;

#案例1：将员工编号>120的员工信息进行工资的降序
SELECT *
FROM employees
WHERE employee_id>120
ORDER BY salary DESC;

#案例1：对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#案例1：对有奖金的员工，按年薪降序

SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees

ORDER BY 年薪 DESC;

#案例1：按姓名的字数长度进行升序
SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

#案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;

SELECT * FROM employees
ORDER BY 2 DESC;

SELECT * FROM employees
ORDER BY first_name;

SELECT CONCAT('hello', first_name,last_name) 备注
FROM employees;

SELECT LENGTH('hello');

SELECT CHAR_LENGTH('hell,xiaoming');

SELECT SUBSTR('hellowoorld',1,3);
SELECT SUBSTR('hellowoorld',2);

SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');
SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');

SELECT TRIM('  xiaoming  ') AS a;

SELECT LPAD('小明',10,'a');

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) AS 'OUTPUT'
FROM employees;

SELECT STRCMP('aec','aec');
SELECT STRCMP('adc','cfg');

SELECT LEFT('鸠摩智',1);
SELECT RIGHT('鸠摩智',1);

SELECT ABS(-2.4);

SELECT CEIL(-1.77999);
SELECT CEIL(-1.09);
SELECT CEIL(0.09);

SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);

SELECT ROUND(4,555);
SELECT ROUND(1.8712345,2);

SELECT TRUNCATE(1.8776787,2);

SELECT MOD(10,3); // 1
SELECT MOD(-10,3); // -1
SELECT MOD(10,-3); // 1

SELECT NOW();

SELECT CURDATE();

SELECT DATEDIFF('1998-7-16','2019-7-13');

SELECT DATE_FORMAT('1998-7-19','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期
SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')入职日期

SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');

SELECT IF(100>9,'好','坏');

SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct) 奖金
FROM employees;

SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END newsalary
FROM employees;

SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS '等级'
FROM employees

SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary)
FROM employees

SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

SELECT MAX(salary) 最高工资, job_id
FROM employees
WHERE commission_pct IS NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;

SELECT MIN(salary) 最低工资,job_id,department_id
FROM employees
GROUP BY job_id,department_id

SELECT NAME ,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

SELECT last_name , department_name,commission_pct
FROM employees e , departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

SELECT department_name, d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`department_id`;

SELECT job_title , COUNT(*)
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC

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;

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

SELECT last_name,department_name
FROM departments d
JOIN employees e
ON e.`department_id`=d.department_id;

SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;

SELECT department_name,COUNT(*) 员工个数
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary`AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.`grade`

SELECT e.`last_name`, m.last_name
FROM employees e
JOIN employees  m
ON e.`manager_id`=m.employee_id;

SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN beauty b ON b.`boyfriend_id` = bo.`id`;

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
    );

SELECT last_name,job_id,salary
FROM employees
WHERE job_id =(
      SELECT job_id
      FROM employees
      WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);

SELECT last_name
FROM employees
WHERE department_id IN(
    # distinct 去重
    SELECT distinct department_id
    FROM departments
    WHERE location_id IN(1400,1700)
    );

SELECT (
	SELECT COUNT(*)
	FROM employees
	WHERE department_id = 50
) 个数;

SELECT dep_ag.department_id,dep_ag.ag,g.grade
FROM sal_grade g
JOIN (

	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id

) dep_ag ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

SELECT EXISTS(
	SELECT *
	FROM employees
	WHERE last_name='Abel'
) 有无Able;

SELECT * FROM employees LIMIT 0,5;

SELECT last_name, salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC
LIMIT 0,10

SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10,10;

SELECT * FROM usa WHERE uage > 20 UNION
SELECT * FROM chinese WHERE age >20

SELECT uname,uage FROM usa
UNION
SELECT age,'name' FROM chinese

SELECT 1,'小明'
UNION ALL
SELECT 1,'小明'
UNION  ALL
SELECT 1,'小明'
UNION  ALL
SELECT 1,'小明' ;

CREATE DATABASE stuDB;
CREATE DATABASE IF NOT EXISTS stuDB;

DROP DATABASE stuDB;
DROP DATABASE IF EXISTS stuDB;

CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT ,
	stuname VARCHAR(20),
	stugender CHAR(1),
	email VARCHAR(20),
	borndate DATETIME

);

CREATE TABLE IF NOT EXISTS stuinfo(
	stuid INT PRIMARY KEY,#添加了主键约束
	stuname VARCHAR(20) UNIQUE NOT NULL,#添加了唯一约束+非空
	stugender CHAR(1) DEFAULT '男',#添加了默认约束
	email VARCHAR(20) NOT NULL,
	age INT CHECK( age BETWEEN 0 AND 100),#添加了检查约束，mysql不支持
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id)#添加了外键约束

);

#1.修改表名

ALTER TABLE stuinfo RENAME TO students;

#2.添加字段
ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL;

DESC students;

#3.修改字段名

ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;

#4.修改字段类型

ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;

#5.删除字段

ALTER TABLE students DROP COLUMN birthday;

DESC students;

DROP TABLE IF EXISTS students;

#仅仅复制表的结构

CREATE TABLE newTable2 LIKE major;

#复制表的结构+数据

CREATE TABLE newTable3 SELECT * FROM girls.`beauty`;


#案例：复制employees表中的last_name,department_id,salary字段到新表 emp表，但不复制数据
CREATE TABLE emp
SELECT lat_name,department_id,salary
FROM myemployees.`employees`
WHERE 1=2;

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

INSERT INTO stuinfo values(1,'吴倩','女','wuqian@qq.com','2001.1.2');

UPDATE stuinfo SET majorid = 3,email = 'xx@qq.com'
WHERE age<20;

#案例1：删除姓李所有信息
DELETE FROM stuinfo
WHERE stuname LIKE '李%'；
#案例2：删除表中所有数据
TRUNCATE TABLE stuinfo;

set autocommit = 0;
start transaction：#可以省略