数据库Mysql、NavicatPremium

<>

  Mac 安装 mysql (链接&＃x1f517;https://dev.mysql.com/downloads/mysql/)&＃xff0c;安装过程中记住自己设置的 mysql 密码。安装完成后可在系统偏好设置里看到 mysql 服务&＃xff0c;点击 mysql 服务&＃xff0c;可以设置启动、关闭&＃xff0c;是否开机自启动 和 重置密码等。
  配置环境变量。
  终端里输入 open ~/.bash_profile 执行。vim 里&＃xff0c;按 i 编辑&＃xff0c;光标移到最后&＃xff0c;粘贴 PATH&＃61;$PATH:/usr/local/mysql/bin&＃xff0c;按键盘 esc 退出编辑&＃xff0c;按 :wq 保存并退出 vim。
  在终端使用 mysql。
  终端输入 mysql -u root -p&＃xff0c;输入安装时设置的sql 密码。 进入 mysql 命令模式。注意 mysql 语句后要输入 ‘ ; ’ 号才能回车执行。

# 显示当前MySQL中包含的所有数据库&＃xff0c;非自建数据库不要删除
mysql> SHOW DATABASES; # 显示 MySQL 安装目录
show variables like &＃39;datadir&＃39;# 创建mydb数据库
CREATE DATABASE mydb1; #创建数据库并设置编码格式为gbk
mysql> CREATE DATABASE mydb2 CHARACTER SET gbk; #如果mydb4数据库不存在&＃xff0c;则创建&＃xff1b;如果存在&＃xff0c;则不创建。
mysql> CREATE DATABASE IF NOT EXISTS mydb4; #删除数据库mydb1
DROP DATABASE mydb1; #使用mydb1数据库
USE mydb1; #查看当前使用的数据库
select database();

  mysql 的 data 目录是隐藏的&＃xff0c;如果要查看。& sudo ls /usr/local/mysql/data 。

  一般使用可视化工具操作数据库&＃xff0c;我使用的是 Navicat Premium&＃xff0c;网上找的破解版。
   点击 Navicat Premium 的连接 -> mysql&＃xff0c;连接名 随便取&＃xff0c;主机 如果是本地填 localhost&＃xff0c;端口 3306&＃xff0c;用户名 root&＃xff0c;密码 自己设置的。然后点击测试连接&＃xff0c;保存。
   测试连接前需要打开 mysql 服务。如果测试连接不上或者连上后&＃xff0c;关闭 navicat 再重新打开连不上&＃xff0c;需要重设密码等级。在系统偏好设置 -> mysql -> Initialize databse&＃xff0c;设置为 Use Legacy Password Encryption。
  右键连接名&＃xff0c;新建数据库 mydb1&＃xff0c;然后再右键连接名&＃xff0c;运行 SQL 文件&＃xff0c;可以导入外部 sql 表 到 mydb1 中。这里我用的表就不链出来了&＃xff0c;只说一下增删查改代码。
  点开 mysql1 -> 查询 -> 新建查询&＃xff0c;可以写 mysql 代码。选中&＃xff0c; 右键&＃xff0c;可以只运行选中的代码。
  查&＃xff0c;select。


# 查询员工表的所有信息&＃xff1a; * 代表查所有字段
select * from t_employees;# 查询员工表中所有员工的编号、名字、邮箱
select employee_id,first_name,email from t_employees;# 年薪 对列中的数据进行运算
SELECT employee_id,first_name,salary*12 from t_employees;# as 别名
select employee_id as &＃39;编号&＃39;,FIRST_NAME AS &＃39;名字&＃39;,salary*12 as &＃39;年薪&＃39; from t_employees;# DISTINCT 查询结果去重
select DISTINCT manager_id from t_employees;# ORDER BY 排序查询
#查询员工的编号&＃xff0c;名字&＃xff0c;薪资。按照工资高低进行降序排序。
select employee_id,first_name,salary from t_employees ORDER BY SALARY DESC;#查询员工的编号&＃xff0c;名字&＃xff0c;薪资。按照工资高低进行升序排序&＃xff08;薪资相同时&＃xff0c;按照编号进行降序序排序&＃xff09;。
SELECT employee_id,first_name,salary from t_employees ORDER BY SALARY ASC,EMPLOYEE_ID DESC;#匹配查询条件
#查询薪资是11000的员工信息&＃xff08;编号、名字、薪资&＃xff09;
select employee_id,first_name,salary from t_employees WHERE salary&＃61;11000;#查询薪资是11000并且提成是0.30的员工信息&＃xff08;编号、名字、薪资&＃xff09;
select employee_id,first_name,salary,COMMISSION_PCT from t_employees WHERE salary&＃61;11000 and COMMISSION_PCT&＃61;0.3;#查询员工的薪资在6000~10000之间的员工信息&＃xff08;编号&＃xff0c;名字&＃xff0c;薪资&＃xff09;
select employee_id,first_name,salary from t_employees WHERE salary >&＃61;6000 and salary <&＃61;10000;#查询员工的薪资在6000~10000之间的员工信息&＃xff08;编号&＃xff0c;名字&＃xff0c;薪资&＃xff09; beetween and
select employee_id,first_name,salary from t_employees where SALARY BETWEEN 6000 and 10000;#查询没有提成的员工信息&＃xff08;编号&＃xff0c;名字&＃xff0c;薪资 , 提成&＃xff09;
select employee_id,first_name,salary,COMMISSION_PCT from t_employees WHERE COMMISSION_PCT is NULL;#查询部门编号为70、80、90的员工信息&＃xff08;编号&＃xff0c;名字&＃xff0c;薪资 , 部门编号&＃xff09;
select employee_id,first_name,salary,DEPARTMENT_ID from t_employees WHERE DEPARTMENT_ID in (70,80,90);#查询名字以"L"开头的员工信息&＃xff08;编号&＃xff0c;名字&＃xff0c;薪资 , 部门编号&＃xff09;
select employee_id,first_name,salary from t_employees WHERE FIRST_NAME LIKE &＃39;L%&＃39;;#查询名字以"L"开头并且长度为4的员工信息&＃xff08;编号&＃xff0c;名字&＃xff0c;薪资 , 部门编号&＃xff09;
select employee_id,first_name,salary from t_employees WHERE FIRST_NAME LIKE &＃39;L___&＃39;;#分支结构查询
#查询员工信息&＃xff08;编号&＃xff0c;名字&＃xff0c;薪资 , 薪资级别<对应条件表达式生成>&＃xff09;
select employee_id,first_name,salary,
CASE WHEN salary>&＃61;10000 THEN &＃39;A&＃39;WHEN salary>&＃61;8000 THEN &＃39;B&＃39;WHEN salary>&＃61;6000 THEN &＃39;c&＃39;ELSE &＃39;D&＃39;
END as &＃39;LEVEL&＃39;
from t_employees;#系统时间查询---系统日期函数
#当前系统时间&＃xff08;日、月、年、时、分、秒&＃xff09;
SELECT SYSDATE();
SELECT NOW();SELECT CURDATE();
SELECT CURTIME();# 获取指定日期为一年中的第几周
SELECT WEEK(NOW());
# 获取指定日期的年份
SELECT YEAR(NOW());#两个时间的差值
SELECT DATEDIFF(&＃39;2020-12-10&＃39;,&＃39;2020-12-07&＃39;); #指定日期加天数
SELECT ADDDATE(&＃39;2020-12-07&＃39;,6); #字符串函数&＃xff1a;
# 连接
select CONCAT(&＃39;hello&＃39;,&＃39;world&＃39;,&＃39;java&＃39;);
# 插入 下标从1开始
SELECT INSERT("hello",3,2,"aaa");
#转小写输出
SELECT LOWER(&＃39;Hello&＃39;);
#转大写输出
SELECT UPPER(&＃39;Hello&＃39;);
# 截取
SELECT SUBSTRING(&＃39;helloworld&＃39;,5,4); #聚合函数&＃xff1a; 求总和、平均值、最大值、最小值、总条数等
SELECT sum(salary) from t_employees;SELECT AVG(salary) from t_employees;SELECT max(salary) from t_employees;SELECT min(salary) from t_employees;# 注意聚合函数自动忽略null值&＃xff0c;不进行统计
SELECT count(salary) from t_employees;#所有记录条数&＃xff0c;count(*)&＃xff1b;指定的标记字段的条数&＃xff0c;count(1)
SELECT count(*) from t_employees; # GROUP BY 分组查询往往需要与聚合函数结合#查询各部门的总人数
#1.按照部门编号进行分组&＃xff08;分组依据是 department_id&＃xff09;
#2.再针对各部门的人数进行统计&＃xff08;count&＃xff09;
select DEPARTMENT_ID,count(1) from t_employees GROUP BY DEPARTMENT_ID;#查询各部门的平均工资
SELECT department_id,avg(salary) from t_employees group by DEPARTMENT_ID;#查询各个部门、各个岗位的人数
SELECT department_id,job_id,count(1) from t_employees group by DEPARTMENT_ID,JOB_ID;#[注&＃xff1a;分组查询中&＃xff0c;select显示的列只能是分组依据列&＃xff0c;或者聚合函数列&＃xff0c;不能出现其他列。]()
SELECT department_id , COUNT(*) , first_name
FROM t_employees
GROUP BY department_id; #error# 分组过滤查询
# SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则
#统计60、70、90号部门的最高工资
#1). 确定分组依据&＃xff08;department_id&＃xff09;
#2). 对分组后的数据&＃xff0c;过滤出部门编号是60、70、90信息
#3). max()函数处理select DEPARTMENT_ID,max(salary) from t_employees GROUP BY DEPARTMENT_ID HAVING DEPARTMENT_ID in (60,70,90);# 限定查询 limit 下标从 0 开始
#查询表中前五名员工的所有信息
select * from t_employees LIMIT 0,5; #limit 起始下标&＃xff0c;显示的条数#查询表中从第四条开始&＃xff0c;查询 10 行
SELECT * from t_employees LIMIT 3,10;#LIMIT典型应用
#分页查询&＃xff1a;一页显示 3 条&＃xff0c;一共查询三页
#第一页&＃xff1a; 起始下标&＃61;&＃xff08;当前页-1&＃xff09;*每页条数
select * from t_employees limit 0,3;
#第二页&＃xff1a;
select * from t_employees limit 3,3;
#第三页&＃xff1a;
select * from t_employees limit 6,3;#子查询&＃xff08;作为条件判断&＃xff09;
#查询工资大于Bruce 的员工信息#1.先查询到 Bruce 的工资&＃xff08;一行一列&＃xff09;
select salary from t_employees WHERE FIRST_NAME&＃61;&＃39;bruce&＃39;;
#2.查询工资大于 Bruce 的员工信息
select * from t_employees where salary>6000;
#3.将 1、2 两条语句整合 子查询
select * from t_employees where salary>(select salary from t_employees WHERE FIRST_NAME&＃61;&＃39;bruce&＃39;);#子查询&＃xff08;作为枚举查询条件&＃xff09;&＃xff08;多行单列&＃xff09;#查询与名为&＃39;King&＃39;同一部门的员工信息
#1. 先查询 &＃39;King&＃39; 所在的部门编号(多行单列)
SELECT department_id from t_employees where LAST_NAME&＃61;&＃39;King&＃39;;
#2. 再查询80、90号部门的员工信息
SELECT * from t_employees WHERE DEPARTMENT_ID in (80,90);
#3.SQL&＃xff1a;合并
SELECT * from t_employees WHERE DEPARTMENT_ID in (SELECT department_id from t_employees where LAST_NAME&＃61;&＃39;King&＃39;);#工资高于60部门所有人的信息
#1.查询 60 部门所有人的工资&＃xff08;多行单列&＃xff09;
SELECT salary from t_employees where DEPARTMENT_ID&＃61;60;
#2.查询高于 60 部门所有人的工资的员工信息&＃xff08;高于所有&＃xff09;
SELECT * from t_employees WHERE SALARY > ALL(SELECT salary from t_employees where DEPARTMENT_ID&＃61;60);
#。查询高于 60 部门的工资的员工信息&＃xff08;高于部分&＃xff09;
SELECT * from t_employees WHERE SALARY > ANY(SELECT salary from t_employees where DEPARTMENT_ID&＃61;60);#子查询&＃xff08;作为一张表&＃xff09; 结果为多行多列
#查询员工表中工资排名前 5 名的员工信息
#1. 先对所有员工的薪资进行排序&＃xff08;排序后的临时表&＃xff09;
select * from t_employees ORDER BY SALARY DESC;
#2. 再查询临时表中前5行员工信息
select * from t_employees LIMIT 0,5;
#SQL&＃xff1a;合并 注意 要起别名 as ss
select * from (select * from t_employees ORDER BY SALARY DESC) as ss LIMIT 0,5;# 合并查询 UNION: 去重 UNION ALL :不去重
# 合并结果的两张表&＃xff0c;列数必须相同&＃xff0c;列的数据类型可以不同
SELECT * from t1 UNION select * from t2;SELECT * from t1 UNION ALL select * from t2;# 内连接查询&＃xff08;INNER JOIN ON&＃xff09;: 两张表中都能匹配上的记录&＃xff0c;才能显示出来#1.查询所有有部门的员工信息&＃xff08;不包括没有部门的员工&＃xff09; SQL 标准
select * from t_employees INNER JOIN t_jobs ON t_employees.JOB_ID&＃61;t_jobs.job_id;#2.查询所有有部门的员工信息&＃xff08;不包括没有部门的员工&＃xff09; MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID&＃61;t_jobs.JOB_ID;# 三表连接查询
SELECT * FROM t_employees e
INNER JOIN t_departments d
on e.department_id &＃61; d.department_id
INNER JOIN t_locations l
ON d.location_id &＃61; l.location_id#查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT e.EMPLOYEE_ID,e.LAST_NAME,d.DEPARTMENT_NAME,l.LOCATION_ID from t_employees e INNER JOIN t_departments d ON e.DEPARTMENT_ID&＃61;d.DEPARTMENT_ID
INNER JOIN t_locations l ON d.LOCATION_ID&＃61;l.LOCATION_ID;# 左外连接&＃xff08;LEFT JOIN ON&＃xff09;
select * from t_employees e LEFT JOIN t_departments d ON e.DEPARTMENT_ID&＃61;d.DEPARTMENT_ID;# 右外连接&＃xff08;RIGHT JOIN ON&＃xff09; 是以右表为主表&＃xff0c;匹配不到 返回 null
select * from t_employees e RIGHT JOIN t_departments d ON e.DEPARTMENT_ID&＃61;d.DEPARTMENT_ID;

  新增&＃xff0c;INSERT INTO。修改&＃xff0c;UPDATE SET。删除&＃xff0c;DELETE。


#添加一条工作岗位信息
select * from t_employees;
insert into t_jobs(job_id,job_title,min_salary) values(&＃39;yyy&＃39;,&＃39;666&＃39;,6000);#修改编号为100 的员工的工资为 25000
UPDATE t_employees set salary&＃61;25000 where EMPLOYEE_ID&＃61;100;#修改编号为135 的员工信息岗位编号为 ST_MAN&＃xff0c;工资为3500
UPDATE t_employees set JOB_ID&＃61;&＃39;st_man&＃39;,salary&＃61;3500 where EMPLOYEE_ID&＃61;135;#删除信息
#删除编号为135 的员工
delete FROM t_employees where employee_id&＃61;135;
#删除姓Peter&＃xff0c;并且名为 Hall 的员工
delete from t_employees where first_name&＃61;&＃39;Peter&＃39; and last_name&＃61;&＃39;Hall&＃39;;# 清空整张表
# 与 DELETE 不加 WHERE 删除整表数据不同&＃xff0c;TRUNCATE 是把表销毁&＃xff0c;再按照原表的格式创建一张新表
TRUNCATE table t1;

  数据表操作。
  常用数值类型。

  常用时间类型。

  常用字符串类型。


# 创建表
create table t_sub(id int(4),name VARCHAR(20),age int(4)
)select * from t_sub;
insert into t_sub(id,name,age) values(1,&＃39;zs&＃39;,30);#添加表字段
alter table t_sub add sex VARCHAR(20);#修改表中字段类型
alter table t_sub MODIFY sex VARCHAR(30);#修改表中的列
alter table t_sub CHANGE age maxage int(4);# 修改表名 修改课程表的subject 为 sub
ALTER TABLE t_sub rename sub;# 删除表中的列
alter table t_sub drop sex;# 删除数据表
DROP TABLE t_sub;

  约束。


# PRIMARY KEY 主键唯一&＃xff0c;标识表中的一行数据&＃xff0c;此列的值不可重复&＃xff0c;且不能为 NULL
CREATE TABLE subject(subjectId INT PRIMARY KEY,subjectName VARCHAR(20),subjectHours INT
)charset&＃61;utf8;# UNIQUE 唯一&＃xff0c;标识表中的一行数据&＃xff0c;不可重复&＃xff0c;可以为 NULL
#为表中列值不允许重复的列添加唯一约束
CREATE TABLE subject(subjectId INT PRIMARY KEY,subjectName VARCHAR(20) UNIQUE,#课程名称唯一&＃xff01;subjectHours INT
)charset&＃61;utf8;# AUTO_INCREMENT 自动增长&＃xff0c;给主键数值列添加自动增长。从 1 开始&＃xff0c;每次加 1
#为表中主键列添加自动增长&＃xff0c;避免忘记主键 ID 序号
CREATE TABLE subject(subjectId INT PRIMARY KEY AUTO_INCREMENT,subjectName VARCHAR(20) UNIQUE,subjectHours INT
)charset&＃61;utf8;# NOT NULL 非空&＃xff0c;此列必须有值。
#课程名称虽然添加了唯一约束&＃xff0c;但是有 NULL 值存在的可能,要避免课程名称为NULL
CREATE TABLE subject(subjectId INT PRIMARY KEY AUTO_INCREMENT,subjectName VARCHAR(20) UNIQUE NOT NULL,subjectHours INT
)charset&＃61;utf8;# DEFAULT 值 为列赋予默认值
#当存储课程信息时&＃xff0c;若课程时长没有指定值&＃xff0c;则以默认课时 20 填充
CREATE TABLE subject(subjectId INT PRIMARY KEY AUTO_INCREMENT,subjectName VARCHAR(20) UNIQUE NOT NULL,subjectHours INT DEFAULT 20
)charset&＃61;utf8;