mysql基础4

本文主要是介绍mysql基础4，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

SELECT USER();
#单行注释（文字说明，不是有效的代码）
#分号表示一行语句的结束
/*
这是
多行注释
*/
#下面创建一个新用户demo_c@localhost,该用户可以在本地登录本机的mysql服务
#INSERT:插入;VALUES:值
INSERT INTO mysql.user(HOST,USER,PASSWORD)
VALUES('localhost','demo_c',PASSWORD('123456'));
#要刷新一下权限(PRIVILEGES)
FLUSH PRIVILEGES;
#将ranzhi数据库的权限授予demo_c@localhost用户
#GRANT：授予;ranzhi.*:ranzhi数据库中所有的对象
GRANT ALL PRIVILEGES ON ranzhi.* TO demo_c@localhost;
#记得刷新权限
FLUSH PRIVILEGES;

#接下来创建一个可以在除本地之外任何计算机能访问mysql的用户
#demo_a@'%':这个用户可以在除本机之外的电脑登录
INSERT INTO mysql.user(HOST,USER,PASSWORD)
VALUES('%','demo_a',PASSWORD('123456'));
#要刷新一下权限(PRIVILEGES)
FLUSH PRIVILEGES;
#将ranzhi数据库的权限授予demo_a@'%'用户
#GRANT：授予;ranzhi.*:ranzhi数据库中所有的对象
GRANT ALL PRIVILEGES ON ranzhi.* TO demo_a@'%';
#记得刷新权限
FLUSH PRIVILEGES;
#查看demo_c@localhost的权限
SHOW GRANTS FOR demo_c@localhost;
#将demo_c的权限回收（ranzhi.*）
#revoke:回收
REVOKE ALL PRIVILEGES ON ranzhi.* FROM demo_c@localhost;
#记得刷新权限
FLUSH PRIVILEGES;
#将ranzhi.sys_product表的查询和新增的权限授予demo_c@localhost
GRANT SELECT,INSERT ON ranzhi.`sys_product`
TO demo_c@localhost;
#记得刷新权限
FLUSH PRIVILEGES;

#将demo_c@localhost用户的密码改为 654321
#将HOST='localhost'并且user='demo_c'的数据行的password改为654321
#update：更新
USE mysql;
UPDATE USER
SET PASSWORD=PASSWORD("654321")
WHERE HOST='localhost' AND USER='demo_c';
#记得刷新权限
FLUSH PRIVILEGES;
#删除一个用户
#drop:删除，通常用于删除一个对象，如：一张表，一个用户
DROP USER demo_c@localhost;

#显示一下当前所有的数据库
SHOW DATABASES;
#将默认数据库切换到ranzhi
USE ranzhi;
#显示一下当前数据库中有多少张表
SHOW TABLES;
#从demo_c@localhost用户中将ranzhi,sys_product表的权限回收
REVOKE SELECT,INSERT ON ranzhi.`sys_product` FROM demo_c@localhost;

#下面开始创建一个数据库
#create：创建
#DEFAULT CHARACTER SET:默认的字符集
#collate:数据排序规则
CREATE DATABASE hrdb
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

#下面建一张表：person :建表语句要自己会写
#ENGINE：引擎
CREATE TABLE hrdb.person
(
id CHAR(18),
NAME VARCHAR(100),
sex CHAR(1),
birthday DATE,
height FLOAT,
weight FLOAT,
age SMALLINT,
hometown CHAR(6)
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

#将person表的我ID字段设置为非空
#ALTER：修改
ALTER TABLE `hrdb`.`person`
CHANGE `id` `id` CHAR(18) NOT NULL;
#将ID字段设置为person表的主键（PRIMARY KEY ）
ALTER TABLE `hrdb`.`person`
ADD PRIMARY KEY (`id`);
#往person表中插入ID=NULL的数据
INSERT INTO hrdb.`person`(ID) VALUES(NULL);
#往person表中插入ID=''(空串)的数据
INSERT INTO hrdb.`person`(ID) VALUES('');#成功
INSERT INTO hrdb.`person`(ID) VALUES('');#不允许重复
#往person表中增加一个phone字段（手机号码）
#COLUMN：列，字段
ALTER TABLE `hrdb`.`person`
ADD COLUMN `phone` CHAR(11) NULL AFTER `hometown`;
#将phone字段设置为唯一键（UNIQUE）
ALTER TABLE `hrdb`.`person`
ADD UNIQUE INDEX `phone_uk` (`phone`);

#下面再来创建一张家乡表
#DEFAULT：默认
CREATE TABLE hrdb.hometown
(
id CHAR(6) PRIMARY KEY,
city VARCHAR(100) DEFAULT '深圳' NOT NULL,
province VARCHAR(100) DEFAULT '广东'
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

#将person表的hometown字段外键关联hometown表的ID
#FOREIGN KEY:外键；CONSTRAINT：约束；REFERENCES：参考；CASCADE：级联
ALTER TABLE `hrdb`.`person`
ADD CONSTRAINT `hometown_fk` FOREIGN KEY (`hometown`)
REFERENCES `hrdb`.`hometown`(`id`) ON DELETE CASCADE;
#将sex字段改为gender
ALTER TABLE `hrdb`.`person`
CHANGE `sex` `gender` CHAR(1) NULL;
#将weight,height改为：整数3位，小数2位精度
ALTER TABLE `hrdb`.`person`
CHANGE `height` `height` FLOAT(5,2) NULL,
CHANGE `weight` `weight` FLOAT(5,2) NULL;
#删除age列，和birthday重复了
ALTER TABLE `hrdb`.`person`
DROP COLUMN `age`;

#完整的一次性建表语句（很重要）
CREATE TABLE hrdb.person2
(
ID CHAR(18) NOT NULL,
NAME VARCHAR(100) NOT NULL,
gender CHAR(1) DEFAULT 'F',
birthday DATE NOT NULL,
height FLOAT(4,1),
weight FLOAT(4,1),
hometown CHAR(6),
phone CHAR(11) UNIQUE,
PRIMARY KEY(id),
CONSTRAINT `fk_hometown_id2` FOREIGN KEY (hometown) REFERENCES hometown(ID)
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

#删除表
DROP TABLE person;
DROP TABLE person2;
DROP TABLE hometown;

#开始做查询操作：
#一张表的查询（重要，要掌握），从员工表中查询出所有员工
SELECT * #*表示所有字段（列）
FROM hrdb.`EMPLOYEES`;

#请查询出员工ID=200的员工姓名
SELECT e.`employee_id`,
e.`first_name` AS 名,#字段也可以取别名
e.`last_name` 姓
FROM hrdb.`EMPLOYEES` e #表可以取别名
WHERE e.`employee_id` = 200; #where表示过滤条件，满足这个条件的行才会显示

#请查询出薪水在8000~10000之间的员工姓名和薪水
SELECT e.`first_name`, e.`last_name`,e.`salary`
FROM hrdb.`EMPLOYEES` e
WHERE e.`salary`>=8000 AND e.`salary`<=10000;
#where中字段和值之间的数值比较符号可以有:
#等于：=, 不等于：<>, 大于：>,小于<,大于等于：>=,小于等于: <=
#请查询出薪水在10000~12000（假设包含边界值）之间的员工姓名和薪水
#逻辑表达式符号：or(或者), and(并且)，not(非)，优先级是 非与或
#请查询出员工ID=200 或者员工ID=201的员工
SELECT *
FROM hrdb.`EMPLOYEES` e
WHERE e.`employee_id` = 200 OR e.`employee_id` =201;
#请查询出入职日期在1996-01-01~1997-01-01之间 或者 工资不在8000~10000的员工
SELECT *
FROM hrdb.`EMPLOYEES` e
WHERE (e.`hire_date`>='1996-01-01' AND e.`hire_date`<='1997-01-01')
OR NOT(e.`salary`>=8000 AND e.`salary`<=10000);
/*查询语句格式：
select 字段名（列名）,... from 表名 where 条件（[not]列名=值 [or/and] ...）
*/

#请查询出(job_id(职位ID)不是IT_PROG(程序员)并且薪水小于5000)
#或者 (job_id=IT_PROG并且入职日期(hire_date)
#小于1997-01-01)的所有员工姓名和薪水和入职日期)
SELECT e.`first_name`, e.`last_name`, e.`salary`, e.`hire_date`,e.`job_id`
FROM hrdb.`EMPLOYEES` e
WHERE (e.job_id<>'IT_PROG' AND e.`salary` < 5000)
OR (job_id='IT_PROG' AND e.`hire_date` < '1997-01-01');

#between子句
#请查询出薪水在5000~10000之间的员工姓名和薪水
#BETWEEN：在...之间
SELECT e.`first_name`,e.`last_name`, e.`salary`
FROM hrdb.`employees` e
WHERE e.`salary` BETWEEN 5000 AND 10000; #包含5000和10000
#in 子句：
#请查询出员工ID为：200,201,202,203,204,205,206的员工所有信息
SELECT *
FROM hrdb.`employees` e
WHERE e.`employee_id` IN(200,201,202,203,204,205,206);

#NULL的用法，不能用：字段=NULL 这种写法，只能:字段 is NULL
#请查询出所有员工中没有直属经理的员工（职位最高的人，manager_id为空）
SELECT * FROM hrdb.`employees` e WHERE e.`manager_id` IS NULL;
#查出所有除总经理外的员工，manager_id不为空
SELECT * FROM hrdb.`employees` e WHERE e.`manager_id` IS NOT NULL;

#排序：
#请将公司中按照薪水由低到高排序，order：排序
SELECT * FROM hrdb.`EMPLOYEES` e ORDER BY e.`salary` ASC;
#由低到高呢？
SELECT * FROM hrdb.`EMPLOYEES` e ORDER BY e.`salary` DESC;
#格式：order by 排序字段 asc(升序，默认的，可以省掉)，desc（降序）

#请将job_id='IT_PROG'的所有人工资由高到低的顺序排列
SELECT e.*
FROM hrdb.`EMPLOYEES` e
WHERE e.`job_id` = 'IT_PROG'
ORDER BY e.`salary` DESC; #排序要写在where后面

#请将job_id='IT_PROG'的所有人入职日期由低到高的顺序排列
SELECT e.*
FROM hrdb.`EMPLOYEES` e
WHERE e.`job_id` = 'IT_PROG'
ORDER BY e.`salary` ASC;

#求前几名
#请查询出IT_PROG职位的薪水前三名
#LIMIT：限制
SELECT e.*
FROM hrdb.`EMPLOYEES` e
WHERE e.`job_id` = 'IT_PROG'
ORDER BY e.`salary` DESC
LIMIT 0,3; #从0行开始，取3行；limit 3,2 (4~5行)

#模糊查询
#模糊查询，根据条件中的某个关键字来查询（很重要）
#如:淘宝搜索：华为 ，将会模糊搜索出淘宝中所有包含‘华为’两个字的产品
#通配符：%： 代表匹配 0~无穷多 个字符
# _:下划线，代表唯一且必须匹配一个字符
#查询出：员工first_name中包含“on”字符的员工
SELECT *
FROM hrdb.`EMPLOYEES` e
WHERE e.`first_name` LIKE '%on%';
#请查询出员工编号以2开头以0结尾，编号只有三位的员工
SELECT * FROM hrdb.`employees` e WHERE e.`employee_id` LIKE '2_0';
#请查询出公司中“名”第二个字母是‘o’，最后一个字母是‘e’ 的所有员工
SELECT * FROM hrdb.`employees` e WHERE e.`first_name` LIKE '_o%e';
#请查询出公司中first_name是四个字母的员工
SELECT * FROM hrdb.`employees` e WHERE e.`first_name` LIKE '____';

#请查询出薪水在5000~10000之间，或者job_id包含'MAN'或job_id以PU开头的员工
SELECT e.*
FROM hrdb.`EMPLOYEES` e
WHERE (e.`salary`>=5000 AND e.`salary`<=10000)
OR e.`job_id` LIKE '%MAN%'
OR e.`job_id` LIKE 'PU%';

#字符串处理函数，CONCAT（重要）
SELECT e.`employee_id`,
e.`first_name`,
e.`last_name`,
CONCAT(e.`first_name`,".",e.`last_name`) AS NAME, #concat(x,y,z)= xyz,拼接若干信息
UPPER(CONCAT(E.FIRST_NAME, ".", E.LAST_NAME)) "大写的全名", #upper(x)：将x字串转换成大写
LOWER(CONCAT(E.FIRST_NAME, ".", E.LAST_NAME)) "小写的全名", #lower(x)：将x字串转换成小写
E.JOB_ID AS 职位ID,
TRIM(E.first_name) 左右修剪FirstName, #TRIM(x):修剪掉x前后空格，Trim：修剪的意思
TRIM(' 你好 ') 左右修剪空格,
TRIM('j' FROM 'jjjjjjjjojhnjjjjj') 左右修剪字母J,#也可以指定修剪某些字符
LTRIM(' 你好 ') 左修剪空格, #Left Trim,左修剪
RTRIM(' 你好 ') 右修剪空格, #Right Trim，右修剪
LENGTH(E.LAST_NAME) 求长度, #计算字符串长度,length(x),求x字符串的字符数（长度）
LPAD(E.SALARY, 11, ' ') 左补齐, #左补齐,右对齐
RPAD(E.SALARY, 12, '$') 右补齐, #右补齐
REPLACE(E.`salary`,'0','零') AS 薪水, #将salary中的0用中文'零'来替换
SUBSTR(JOB_ID, 4, 3) 截取子串 # sub:子； str:字符串
FROM HRDB.EMPLOYEES E
WHERE SUBSTR(JOB_ID, 4, 3) = 'MAN';#截取子串，找出job_id中第4个字符后三个字符等于'MAN'的行

#日期函数
#当前服务器的日期和时间（重要）
SELECT SYSDATE(), NOW(), CURTIME(), CURDATE(); #current:当前的
#当前的星期号（0~6）
SELECT WEEKDAY(SYSDATE());
SELECT WEEKDAY('2017-7-24');#星期一是0
#返回当前的月份
SELECT MONTHNAME(SYSDATE());
#返回指定的日期，FORMAT：格式
SELECT DATE_FORMAT(SYSDATE(),'%Y年%m月%d日 %H:%i:%s %p') AS 当前日期;
#日期增加函数
SELECT DATE_ADD("1997-12-31 23:59:59",INTERVAL 1 SECOND); #INTERVAL:时间间隔,此句加1秒钟
#员工表的入职日期每人都减一天10小时，单位关键词：YEAR/MONTH/DAY/HOUR/MINUTE/SECOND
SELECT e.`first_name`, e.`last_name`, e.hire_date,
DATE_ADD(e.hire_date,INTERVAL '-1 10' DAY_HOUR) AS AddHireDate
FROM hrdb.`employees` e;

#下面开始联表查询（两张表的内连接 重要）
SELECT * FROM hrdb.`employees`; #107行数据
SELECT * FROM hrdb.`departments`; #27行数据

#两张表放在一起查询（重要）
#求有部门的员工所在的部门名称
SELECT e.`first_name`, e.`last_name`,e.`department_id`,
d.`department_id`,d.`department_name`
FROM hrdb.`EMPLOYEES` e, hrdb.`DEPARTMENTS` d
WHERE e.`department_id` = d.`department_id`;
#求员工对应的职位名称（员工表employees、职位表jobs）
SELECT e.`first_name`,e.`last_name`,e.`job_id`,
j.`job_id`, j.`job_title`
FROM hrdb.`EMPLOYEES` e, hrdb.`JOBS` j
WHERE e.`job_id` = j.`job_id`;
#求部门对应的部门经理姓名
SELECT d.`department_id`,d.`department_name`,d.`manager_id`,
e.`employee_id`,e.`first_name`,e.`last_name`
FROM hrdb.`DEPARTMENTS` d, hrdb.`EMPLOYEES` e
WHERE d.`manager_id` = e.`employee_id`;
#求出在Toronto工作的员工
SELECT e.*,l.`city`
FROM hrdb.`EMPLOYEES` e,
hrdb.`DEPARTMENTS` d,
hrdb.`LOCATIONS` l
WHERE e.`department_id` =d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`city` = 'Toronto';

#求有部门的员工所在的部门名称(另外一种写法)
SELECT e.*,d.*
FROM hrdb.`EMPLOYEES` e, hrdb.`DEPARTMENTS` d
WHERE e.`department_id` = d.`department_id`;
#这个我们叫内连接，另外一种写法：
SELECT e.*, d.*
FROM hrdb.`EMPLOYEES` e
INNER JOIN hrdb.`DEPARTMENTS` d
ON e.`department_id` = d.`department_id`;

#外连接：分为左外、右外、全外
#求出所有员工对应的部门名称
SELECT e.*, d.*
FROM hrdb.`EMPLOYEES` e
LEFT JOIN hrdb.`DEPARTMENTS` d #左外连接，保证左表是全的
ON e.`department_id` = d.`department_id`;

#求出所有部门对应的员工
SELECT e.*, d.*
FROM hrdb.`EMPLOYEES` e
RIGHT JOIN hrdb.`DEPARTMENTS` d #右外连接，保证右表是全的
ON e.`department_id` = d.`department_id`;
#全外连接，用union（联合）将左连接和右连接的数据合并即可
#union（联合）：可以将两条SQL语句的结果融合在一起，其中完全相同的数据会变成一条
SELECT e.*, d.*
FROM hrdb.`EMPLOYEES` e
LEFT JOIN hrdb.`DEPARTMENTS` d #左外连接，保证左表是全的
ON e.`department_id` = d.`department_id`
UNION
SELECT e.*, d.*
FROM hrdb.`EMPLOYEES` e
RIGHT JOIN hrdb.`DEPARTMENTS` d #右外连接，保证右表是全的
ON e.`department_id` = d.`department_id`;

#接下来开始做分组统计
#请求出公司所有员工总数(COUNT)、工资总数(sum)、平均(avg)工资数、
#最高（max）工资数、最低（min）工资数
SELECT COUNT(*) AS 员工总数,
SUM(e.`salary`) AS 工资总数,
AVG(e.`salary`) AS 平均工资,
MAX(e.`salary`) AS 最高工资,
MIN(e.`salary`) AS 最低工资
FROM hrdb.`EMPLOYEES` e;
#请按照部门分组(GROUP)，求出每个部门的上述信息
SELECT e.`department_id`,
COUNT(*) AS 员工总数,
SUM(e.`salary`) AS 工资总数,
AVG(e.`salary`) AS 平均工资,
MAX(e.`salary`) AS 最高工资,
MIN(e.`salary`) AS 最低工资
FROM hrdb.`EMPLOYEES` e
WHERE e.`salary`>=3000 #先过滤后再来统计
GROUP BY e.`department_id` #按照部门来分组统计
HAVING 平均工资>=5000 #统计完后过滤掉不符合要求的数据
ORDER BY 平均工资 DESC; #按照平均工资的降序排列

#子查询：
#子查询意思就是说在查询中再嵌套一个查询
#子查询可以嵌套到任何位置子句中
#1、select子句中用子查询，查出每个员工的部门名称
SELECT e.`first_name`,
e.`last_name`,
e.department_id,
(SELECT d.department_name FROM hrdb.`departments` d
WHERE d.department_id = e.`department_id`) AS 部门名称
FROM hrdb.`employees` e;
#2、在from子句中使用子查询
#查询部门编号=50的员工姓名
SELECT e.`first_name`,e.`last_name`,e.`department_id`
FROM (SELECT ee.* FROM hrdb.`employees` ee
WHERE ee.`department_id` = 50) e;
#3、where子句中用子查询
#找到与员工201相同部门的人员信息
SELECT e.*
FROM hrdb.`employees` e
WHERE e.department_id =
(SELECT e2.department_id
FROM hrdb.`employees` e2
WHERE e2.employee_id=201);

#习题：
#基础练习：
#1、请求出(员工名包含"h"字母且第一个以"j"开头) 或者 (倒数第二个以"h"结尾的员工)
SELECT e.*
FROM hrdb.`employees` e
WHERE e.`first_name` LIKE 'j%h%' OR e.`first_name` LIKE '%h_';
#2、请求出公司（薪水高于10000且职位不是‘IT_PROG’） 或者 薪水低于4000的员工
SELECT e.*
FROM hrdb.`employees` e
WHERE (e.`salary`>10000 AND e.`job_id`<>'IT_PROG')
OR e.`salary`<4000;
#3、请求出部门经理中薪水高于10000的员工姓名
SELECT e.`employee_id`,e.`first_name`,e.`last_name`
FROM hrdb.`employees` e, hrdb.`departments` d
WHERE d.`manager_id` = e.`employee_id`
AND e.`salary`>10000;
#4、请求出每位员工的直属领导姓名
SELECT CONCAT(e.`first_name`,'.', e.`last_name`) 我,
CONCAT(m.`first_name`,'.',m.`last_name`) 我的经理
FROM hrdb.`employees` e, hrdb.`employees` m
WHERE e.`manager_id` = m.`employee_id`;

#示例1：出几个练习题：
#出几个练习题：
#1、请列出在美国的部门经理姓名
SELECT c.`country_name`,
l.`city`,
d.`department_name`,
e.`first_name`,
e.`last_name`
FROM hrdb.`countries` c,
hrdb.`locations` l,
hrdb.`departments` d,
hrdb.`employees` e
WHERE d.`manager_id` = e.`employee_id`
AND d.`location_id` = l.`location_id`
AND l.`country_id` = c.`country_id`
AND c.`country_name` = 'United States of America';
#2、请列出工资收入在1万以上的员工姓名以及职位名和他们所在国家
SELECT e.`first_name`,e.`last_name`,e.`salary`,
j.`job_title`, c.`country_name`
FROM hrdb.`employees` e,
hrdb.`departments` d,
hrdb.`locations` l,
hrdb.`jobs` j,
hrdb.`countries` c
WHERE e.`job_id` = j.`job_id`
AND e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`country_id` = c.`country_id`
AND e.`salary`>=10000;

#3、请统计出每个区域（美洲区、欧洲区、亚洲区、中东和非洲）
# 的工资总数和员工总数、平均工资
#先查询出一张表：区域ID、员工ID、员工薪水
#然后再分组统计这张表
SELECT r.region_id, COUNT(*), AVG(r.salary),SUM(r.salary)
FROM
(SELECT c.`region_id`, e.`employee_id`,e.`salary`
FROM hrdb.`employees` e,
hrdb.`departments` d,
hrdb.`locations` l,
hrdb.`countries` c
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND l.`country_id` = c.`country_id`) r
GROUP BY r.region_id;

#4、请求出员工中同名的人以及人数
SELECT e.`first_name`,COUNT(*)
FROM hrdb.`employees` e
GROUP BY e.`first_name`
HAVING COUNT(*)>1;
#5、用内连接、外连接、全连接的方式查询出每位员工所对应的职位头衔
#内连接：
SELECT e.`employee_id`, j.`job_title`
FROM hrdb.`employees` e
INNER JOIN hrdb.`jobs` j
ON e.`job_id` = j.`job_id`;
#左连接
SELECT e.`employee_id`, j.`job_title`
FROM hrdb.`employees` e
LEFT JOIN hrdb.`jobs` j
ON e.`job_id` = j.`job_id`;
#右连接
SELECT e.`employee_id`, j.`job_title`
FROM hrdb.`employees` e
RIGHT JOIN hrdb.`jobs` j
ON e.`job_id` = j.`job_id`;
#全连接
SELECT e.`employee_id`, j.`job_title`
FROM hrdb.`employees` e
LEFT JOIN hrdb.`jobs` j
ON e.`job_id` = j.`job_id`
UNION
SELECT e.`employee_id`, j.`job_title`
FROM hrdb.`employees` e
RIGHT JOIN hrdb.`jobs` j
ON e.`job_id` = j.`job_id`;

#示例2：某学校系统中的“学分表Records”包含：学号SID、课程CID、分数SCORE三个字段
#1、请创建表及插入数据
CREATE TABLE hrdb.records(
sid INT,
cid INT,
score FLOAT(4,1),
PRIMARY KEY(sid,cid)
)ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;
#插入数据:cid: 1: 语文；2：数学；3:英语
INSERT INTO hrdb.`records` VALUES(1,1,89.5);
INSERT INTO hrdb.`records` VALUES(1,2,90);
INSERT INTO hrdb.`records` VALUES(1,3,87);

INSERT INTO hrdb.`records` VALUES(2,1,98);
INSERT INTO hrdb.`records` VALUES(2,2,95);
INSERT INTO hrdb.`records` VALUES(2,3,30);

INSERT INTO hrdb.`records` VALUES(3,1,66);
INSERT INTO hrdb.`records` VALUES(3,2,56);
INSERT INTO hrdb.`records` VALUES(3,3,99);

INSERT INTO hrdb.`records` VALUES(4,1,88);
INSERT INTO hrdb.`records` VALUES(4,2,83);
INSERT INTO hrdb.`records` VALUES(4,3,80);

INSERT INTO hrdb.`records` VALUES(5,1,78);
INSERT INTO hrdb.`records` VALUES(5,2,76);
INSERT INTO hrdb.`records` VALUES(5,3,87);

#2、请查询出总分排名前三的学生的学号及总分
SELECT r.`sid`,SUM(r.score) AS 总分
FROM hrdb.`records` r
GROUP BY r.`sid`
ORDER BY 总分 DESC
LIMIT 0,3;

#3、请查询出每门课程得到最高分的学生的学号和课程号、分数
#首先按照课程分组求每门课的最高分

SELECT r.*
FROM
(SELECT r.`cid`,MAX(r.`score`) AS 最高分
FROM hrdb.`records` r
GROUP BY r.`cid`) ms,
hrdb.`records` r
WHERE r.cid = ms.cid
AND r.score = ms.最高分;

#下面开始做一张表的增、删、改（很重要，一定要会默写）
#新增表数据，往部门表中添加一个部门：
#方法1：插入的时候指定字段，则值必须与字段一一对应
#INSERT INTO:插入
INSERT INTO hrdb.`departments`(department_id, department_name)
VALUES(8888, "融资部");
#方法2：插入的时候不指定字段，则值按照表字段的定义顺序一一对应
INSERT INTO hrdb.`departments` VALUES(8889, "融资二部", NULL, NULL);
#检查一下数据是否插入成功
SELECT * FROM hrdb.`departments` WHERE department_name LIKE '融资%';

#删除语句
#创建一张跟员工表 表结构 和 数据一模一样的表：emp_copy
CREATE TABLE hrdb.emp_copy SELECT * FROM hrdb.`employees`;
#删除员工ID以2开头的,并且部门ID=20的所有数据
DELETE FROM hrdb.`emp_copy` WHERE employee_id LIKE '2%' AND department_id=20;
#删除之前可以用select语句查出来检查是否是需要删除的数据
SELECT * FROM hrdb.`emp_copy` WHERE employee_id LIKE '2%' AND department_id=20;
#不带where条件，相当于数据全部删除，一次性的把emp_copy表的数据全部删除，危险系数*****
#删除全部表数据：第一种：通过数据库的日志还可以恢复
DELETE FROM hrdb.`emp_copy`;
#删除全部表数据：第2种：TRUNCATE:截断，删完不可恢复，危险系数******
TRUNCATE hrdb.`emp_copy`;
#把这张表也删除掉
DROP TABLE hrdb.emp_copy;

#创建一张和员工表结构和数据一模一样的表：emp_copy
CREATE TABLE hrdb.emp_copy SELECT * FROM hrdb.`employees`;
#带条件的更新：请将所有销售部的员工提成比率改为90%,并且将薪水调为300000
UPDATE hrdb.`emp_copy`
SET commission_pct = 0.9, salary=300000
WHERE department_id=80;
#写个查询语句查一下
SELECT * FROM hrdb.`emp_copy`
WHERE department_id=80;
#不带where条件，相当于全部更新，将所有员工的姓名都改为：牛人.com，危险系数*****
UPDATE hrdb.`emp_copy`
SET first_name='牛人', last_name='com';

#视图VIEW：将一条查询语句定义为一个视图
#将销售部的所有员工定义为一个视图
CREATE OR REPLACE VIEW hrdb.v_emp_sales AS
SELECT * FROM hrdb.employees e WHERE e.department_id=80;

#创建一个视图，显示员工信息和对应的部门名称
CREATE OR REPLACE VIEW hrdb.v_emp_dept AS
SELECT e.`employee_id`,
e.`department_id` AS emp_d_id,
d.`department_id` AS dept_d_id,
d.`department_name`
FROM hrdb.`employees` e, hrdb.`departments` d
WHERE e.`department_id` = d.`department_id`;

#可以当做表一样的操作视图
#查询视图
SELECT * FROM hrdb.`v_emp_sales` v WHERE v.`salary`>5000;
#修改视图数据
UPDATE hrdb.`v_emp_sales` SET first_name='john_3434' WHERE first_name='John';

#数据的备份操作（备份到文件）
#创建一张空的员工表
CREATE TABLE emp_copy2 SELECT * FROM hrdb.`employees` WHERE 1=2;
#表数据导出：
#选中要导出数据的表后 右键菜单中选中 备份/导出--> 导出表数据作为
#表数据导入：
#选中要插入数据的表后 右键菜单中选中 导入 --> 导入使用本地加载的csv数据

#数据库的导出
#选中要导出的数据库后 右键菜单中选中 备份/导出--> 备份数据库，转储成SQL
#数据库的导入
#查看一下转储的SQL文件，理解里面的语句，
#将SQL文件的内容放入 询问 中全部执行即可导入

这篇关于mysql基础4的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！