mysql -DML数据操纵语言(草稿)

select : 查询表中数据
insert : 向表中插入新数据
update : 更新表中数据
delete : 删除表中数据

*********mysql没有撤销,没有撤销,没有撤销**********

#创建库
CREATE DATABASE exercise;

#使用库
USE exercise;

#删除库
DROP exercise;

#创建列表
CREATE TABLE student
(sld VARCHAR(4) NOT NULL,
sname VARCHAR(8),
sage DATE,
ssex VARCHAR(4)
);

CREATE TABLE course
(cld VARCHAR(4),
cname VARCHAR(8),
tid VARCHAR(8));

CREATE TABLE teacher
(tid VARCHAR(4),
tname VARCHAR(16));

CREATE TABLE sc
(sld CHAR(8),
cld CHAR(16),
score INT);

#添加student表内数据
INSERT INTO student VALUES('s1','找1','1990-9-1','男');
INSERT INTO student VALUES('s2','钱2','1992-1-2','男');
INSERT INTO student VALUES('s3','孙3','1991-2-3','女');
INSERT INTO student VALUES('s4','李4','1991-3-3','女');
INSERT INTO student VALUES('s5','周5','1994-2-1','男');

#更新表中数据



#查看是否添加数据成功
SELECT * FROM student;

#添加course表内数据
INSERT INTO course VALUES('c1','python','t1');
INSERT INTO course VALUES('c2','java','t2');
INSERT INTO course VALUES('c3','cpp','t3');
INSERT INTO course VALUES('c4','c#','t4');

#查看是否成功成功添加数据数据
SELECT * FROM course;

#添加teacher表内数据
INSERT INTO teacher VALUES('t1','齐桓公');
INSERT INTO teacher VALUES('t2','秦穆公');
INSERT INTO teacher VALUES('t3','楚庄王');
INSERT INTO teacher VALUES('t4','夫差');

SELECT * FROM teacher;
DROP TABLE teacher;
#添加sc表内数据
INSERT INTO sc VALUES('s1','c1','59');
INSERT INTO sc VALUES('s1','c2','61');
INSERT INTO sc VALUES('s1','c3','99');
INSERT INTO sc VALUES('s1','c4','100');
INSERT INTO sc VALUES('s2','c1','99');
INSERT INTO sc VALUES('s2','c2','58');
INSERT INTO sc VALUES('s2','c3','100');
INSERT INTO sc VALUES('s2','c4','62');
INSERT INTO sc VALUES('s3','c1','18');
INSERT INTO sc VALUES('s3','c2','91');
INSERT INTO sc VALUES('s3','c3','45');
INSERT INTO sc VALUES('s3','c4','12');
INSERT INTO sc VALUES('s4','c1','89');
INSERT INTO sc VALUES('s4','c2','78');
INSERT INTO sc VALUES('s4','c3','59');
INSERT INTO sc VALUES('s4','c4','99');

#查看数据是否添加成功,模糊查询是否有遗漏

SELECT * FROM sc
WHERE sld LIKE 's1';

SELECT * FROM sc
WHERE sld LIKE 's2';


SELECT * FROM sc
WHERE sld LIKE 's3';


SELECT * FROM sc
WHERE sld LIKE 's4';

#一. 插入语句
/*
#方式一:经典插入
语法:
	insert into 表名(列名,...) values(值1,...)
	#1.插入的值的类型要与列的类型一致或兼容
INSERT INTO 
#2.不可以null的列必须插入值,可以为null的列要么列名写着值为空,要么列名直接不写

#3.列的顺序可以调换,列数和值的个数必须一致

#4.可以省略列名字,默认所有列,而且烈的顺序和表中列的顺序一致

#方式二:
语法:
	insert into 表名
	set 列名=值,列名=值....

***方式一支持插入多行,方式二只支持插入单行

*/

#eg1:方式一 - 插入完整行
INSERT INTO student(sld,sname,sage,ssex) VALUES('s32','ta1','1999-12-1','male');
INSERT INTO student VALUES('s12','tom','199-1-2','male');

#eg2:方式一 - 插入部分行
INSERT INTO student(sld,sname) VALUES('s21','sa');

#eg3:方式一 - 插入多行
INSERT INTO student VALUES('s21','t2m','199-2-2','male'),('s34','tam','1999-10-1','male');

#eg4:方式一 - 插入检索出来的语句
INSERT INTO student(sld) SELECT sld FROM sc;

#eg5:方式二 -
INSERT INTO student SET sld='s212';



#二 更新语句

/*
***进行列的更新
1.修改单表语句★
语法:
	update 表名                   1
	set 列=新值...
	where 筛选条件;              2
2.修改多表语句
语法:
	update 表1 inner join 表2 on 连接条件
	sete 列=值,列=值
	where 筛选条件;

***更新数据不要省略where,否则会更新所有行
*/

#1.单表更新
UPDATE student
SET sld='s100'
WHERE sname LIKE '%m';


#三.删除语句
/*
***删除行
方式一:delete
语法:
单表删除
	delete from 表名 where
	
多表删除:
	delete  表1别名,表2别名
	from 表1别名 inner jion 表2别名 on 连接条件
	where 筛选条件
方式二:清空数据
	trunct table 表名;
	
方式一与方式二的区别:
	1.delete 后可以加where条件,truncate后不能加
	2.truncate相当于清空条件
	3.假如删除表中有自增长列
	  delete删除后插入时,从断点值开始
	  truncate从1开始
	
*/
#eg:单表删除 
DELETE FROM student WHERE sld='s1';

#eg:清空表
TRUNCATE TABLE sc;


#一 基础查询

#eg1:查询单个字段
SELECT sld FROM sc;

#eg2:查询多个字段
SELECT sage,sld FROM student;

#eg3:拼接查询
SELECT CONCAT(sage,sld) FROM student;

#eg4:去重查询
SELECT DISTINCT(sld) FROM sc;


#一 条件查询
#注:可使用逻辑表达式连接条件(and , not ,or)

#条件查询1-条件表达式(< = > >= <= != )

#eg5:查询有课程达到满分的学生编号
SELECT DISTINCT(sld) 
FROM sc
WHERE score=100;

#eg6:查询有课程不及格学生的编号
SELECT sld
FROM sc
WHERE NOT score>=60;

#eg7:查询c1课程在60到80内的学生的编号
SELECT * 
FROM sc
WHERE score<=80 AND score>=60;


#条件查询2-模糊查询(like,between and,in,is null)

#eg8:查询姓秦的老师编号 
SELECT tid
FROM teacher
WHERE tname LIKE '秦%';

#eg9:查询有课程及格学生的编号
SELECT * 
FROM sc
WHERE score BETWEEN 60 AND 100;

#eg10:查询分数为空的学生编号的学生  
SELECT * 
FROM sc
WHERE score IS NULL;


SELECT * 
FROM sc
WHERE score IS NOT NULL;

#eg11:查询姓带有公的老师编号

SELECT tid
FROM teacher
WHERE tname LIKE '%公' OR tname LIKE '公%';

#注:"\%"和'\_'序列用于搜索可能解释为通配符的模式匹配环境中的%和_文字实例
SELECT * FROM employees WHERE last_name LIKE 'K\_%';

# 日期型数字
#eg14:查询出生年月在1990到1992的学生编号
SELECT *
FROM student
WHERE sage>('1900-1-1')AND sage<('1999-1-1');


# in的用法 - in里面放查询条件(不是查询范围)

SELECT *
FROM employees
WHERE department_id IN (30,50,100);

#三 排序查询

#升序查询
SELECT salary,employee_id
FROM employees
WHERE salary BETWEEN 5000 AND 50000
ORDER BY salary ASC;

#降序查询
SELECT salry,employee_id
FROM employees
WHERE NOT salary<10000
ORDER BY employee_id DESC;

#四 函数

#字符函数1-length():获取参数值的字节数
SELECT LENGTH(first_name) FROM employees;

#字符函数2-concat():拼接字符
SELECT CONCAT(first_name,last_name) FROM employees;

#字符函数3-upper,lower:改变大小写

SELECT LOWER(first_name),UPPER(last_name) FROM employees;

SELECT * FROM employees;

#字符函数4-substr(s,n,len):返回字符串s从第n个字符长度(索引,sql语言索引从1开始)为len的子字符串

SELECT SUBSTR(first_name,3,2) FROM employees; 
SELECT SUBSTR(first_name,3) FROM employees; 
#eg
SELECT CONCAT(SUBSTR(first_name,3),'_',SUBSTR(last_name,3)) FROM employees; 

#字符函数5-instr(s,sl)  返回子字符串在主字符串中第一次出现的索引,找不到返回0

SELECT INSTR(first_name,'K') FROM employees;

#字符函数6-replace(s,s1,s2)  在主字符串中用s2子字符串替代s1子字符串
#***错误语句:SELECT replace(first_name,Lex,lex)  FROM employees;
SELECT REPLACE(first_name,'Lex','lex')  FROM employees;

#字符函数7-trim(s) -去字符,默认去空格
#          trim(a from s) - 去掉指定字符

SELECT TRIM(first_name)  FROM employees;
SELECT TRIM('L' FROM first_name)  FROM employees;

#字符函数 - lpad(s,2,'*')  -左填充函数,用指定字符实现左填充指定长度,若填充数少于字符数则返回填充长度的字符数

SELECT LPAD(first_name,10,'*')  FROM employees;
SELECT LPAD(first_name,2,'*')  FROM employees;

#字符函数9 - rpad() -右填充函数,作用同右填充

SELECT RPAD(first_name,10,'*')  FROM employees;
SELECT RPAD(first_name,2,'*')  FROM employees;

#填充函数填充长度少于字节长度时,都是从左到右返回填充长度的字符数


#数学函数1-round(x,d):四舍五入

SELECT ROUND(salary) FROM employees;
SELECT ROUND(salary/1000,2) FROM employees;

#数学函数2-mode(x,m):取余(m为被除数)

SELECT MOD(salary,1000) FROM employees; 

#数学函数3-ceil(x):向上取整
SELECT CEIL(salary%1000) FROM employees;

#数学函数4-floor(x):向下取整
SELECT FLOOR(salary%1000) FROM employees;

SELECT * FROM employees;

#数学函数5-truncate(x,d):截断函数-小数点后保留d位
SELECT TRUNCATE(phone_number,4) FROM employees;

#日期函数1-查询现在时间datetime(),curdate,curtime()
SELECT NOW();

SELECT CURDATE();

SELECT CURTIME();

#日期函数2-将字符转换为指定格式的日期
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') AS now_time;

#分组函数:max,min,sum,avg,count
SELECT MAX(salary),MIN(salary),COUNT(salary),AVG(salary),SUM(salary) FROM employees;

#五 分组查询

#1 简单分组查询 - 查询每个工种的平均工资
SELECT job_id,AVG(salary) FROM employees GROUP BY job_id;

#2 分组前筛选 - 查询工资邮箱中包含a的各个部门的平均工资

SELECT department_id,AVG(salary) FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

#3 分组后筛选 - 查询哪个部门的员工数>2,并对结果进行升序排序

SELECT department_id,COUNT(*) 
FROM employees  
GROUP BY department_id 
HAVING COUNT(*)>2 
ORDER BY COUNT(*) ASC;

#4 多条件筛选查询 - 查询每个部门,每个工种的平均工资
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id
ORDER BY department_id ASC;

#count(*):返回检索行的数目,不论是否包含NULL值,

#六 连接查询 - 查询的字段来自多个表时
/*
sql199语法:
	select 查询列表
	from 表1 别名 
	[连接类型] join 表2 on 连接条件
	where 筛选条件
	grouup by
	having 筛选条件
	order by 排序列表	
*/

#1 内连接 - 返回交集

SELECT 查询列表
FROM1
INNER JOIN2 ON 连接条件

#1.1等值连接  

#eg1.查询员工名和部门名

SELECT first_name,department_name 
FROM employees AS e INNER
JOIN departments AS d
ON e.`department_id`=d.`department_id`;

#eg2.查询名字中包含e的员工名和工种名

SELECT first_name,job_title 
FROM employees AS e INNER
JOIN jobs AS j
ON e.`job_id`=j.`job_id`
WHERE first_name LIKE '%e%';

#eg3.查询部门个数>3的城市名及部门个数

SELECT city,COUNT(*) 
FROM departments AS d INNER
JOIN locations AS l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

#eg4.三表连接 - 查询员工名,部门名,工种名,并按部门人数进行排序

SELECT first_name,department_name,job_title
FROM employees AS e 
INNER JOIN departments AS d
ON e.`department_id` = d.`department_id`
INNER JOIN jobs AS j 
ON e.`job_id` = j.`job_id`;

SELECT first_name,department_name,job_title
FROM departments AS d
INNER JOIN employees AS e ON d.`department_id`=e.`department_id`
INNER JOIN jobs AS j ON e.`job_id` =j.`job_id`;

#多表查询与表的链接顺序无关


#1.2 内连接 - 非等值连接

#eg:查询员工工资级别
SELECT *
FROM1
INNER JOIN2
ON1.列名 BETWEEN2.列名 AND2.列名2

#1.3 内连接 - 自连接

SELECT e.first_name 
FROM employees AS e
INNER JOIN  employees AS p ON e.`department_id`=p.`department_id`;

#制定多个键时,可以使用and,or连接条件
#规范语法:使用连接时select子句中按照“表别名.列名”格式书写。

#2 外连接 

#外连接分主从表,左外连接左边为主表,右外连接右边为主表,左外和右外交换两个表的顺序可以实现同样的结果。
#

#2.1 左外连接 - 返回左边表中的所有行和右边表中与之相匹配的列值,若右表没有则用NULL代替

SELECT *
FROM employees AS e
LEFT JOIN departments AS d
ON e.`department_id`=d.`department_id`;

SELECT *
FROM departments AS d
LEFT JOIN employees AS e
ON e.`department_id`=d.`department_id`;

#2.2右外连接

SELECT *
FROM employees AS e
RIGHT JOIN departments AS d
ON e.`department_id`=d.`department_id`

#2.3全外连接 - 返回左右两表的全部行 - mysql不支持需要用union把左右外连接在一起

SELECT *
FROM employees AS e
RIGHT JOIN departments AS d
ON e.`department_id`=d.`department_id`
UNION
SELECT *
FROM employees AS e
LEFT JOIN departments AS d
ON e.`department_id`=d.`department_id`;


#3 交叉连接 - 笛卡尔积

SELECT *
FROM departments
CROSS JOIN  employees;


#七 子查询 
/*
1.定义:
	出现在其他语句的elect语句,被称为子查询或内查询,u要放在括号内
	
2.特定:
	1.子查询以视图为基础,是一张一次性视图。
	2.子查询放在括号内;
	3.子查询的执行优先于主查询;
	4.标量子查询一般配合单行操作符号使用<> = <= >=
	  列子查询一般搭配多行操作符使用。
	  
3.分类:
	标量子查询:返回一行一列的结果集
	行子查询:返回一行一列的结果集
	列子查询:返回一列多行的结果集
	表子查询:返回多行多列的结果集
4.位置:
	select后面 仅仅支持标量子查询
	from后面 支持表子查询
	where或having后面 标量子查询,列子查询,行子查询
*/

#一 where 或 having后面

#1.标量子查询

#eg1 标量子查询 查询谁的工资比Lex的高

SELECT first_name,salary
FROM employees
WHERE salary > (SELECT salary 
		FROM employees
		WHERE first_name LIKE 'Den');


#eg2.标量子查询 - 返回job_id与141号员工相同,salary比143号多的员工,查询员工姓名
SELECT CONCAT(last_name,first_name)
FROM employees 
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id=141) 
	AND salary>(SELECT salary FROM employees WHERE employee_id=143)


#eg3:标量子查询 - 返回公司工资最少的员工last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

#eg4:标量子查询 - 查询最低工资>50号部门最低工资的部门id,及其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id=50);


#2.列子查询

/*
多行操作符 in/not in(等于) 
any/some =max(和子查询返回的某一个查询) 
all =min(和查询的值全部比较)
*/

#eg1:返回location id是1400或1700的部门的所有员工姓名
SELECT CONCAT(last_name,first_name)
FROM employees
WHERE department_id IN(SELECT DISTINCT(department_id) FROM departments WHERE location_id IN(1400,1700));

#eg2:返回其他部门中比job_id为'IT——PROG'部门所有工资低的员工的员工号,姓名,job_id,salary
SELECT employee_id,first_name,job_id,salary
FROM employees
WHERE salary < ANY(SELECT DISTINCT(salary) FROM employees WHERE job_id='IT_PROG') AND job_id<>'IT_PROG';

#3 行子查询
#eg:查询员工编号最小的并且要求工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id = ALL(SELECT MIN(employee_id) FROM employees) 
	AND salary = ANY(SELECT MAX(salary) FROM employees); 

#二 select后面 - 查询和统计并存(返回值)
#eg:查询每个部门的信息及员工个数

#错误代码
SELECT *,(SELECT COUNT(*) FROM employees GROUP BY department_id)
FROM departments;


 SELECT d.*,
  (SELECT COUNT(*)
  FROM employees e
  WHERE d.`department_id` = e.department_id
 )
FROM departments d;

#三 from后面-将查询结果充当一张表,必须起别名
#eg:查询每个部门的平均工资的工资等级




#四 相关子查询 - 是否存在,子查询用到了主查询的信息,子查询的结果是否有值。
#查询员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`);





#八 union查询 - 将多条查询语句的结果合并成一个结果
/*
应用场景:查询的结果来自多个表,且多个表没有直接的连接关系,但查询的部分有一定关系

特点:要求多条查询语句的查询列数
      要求多条查询语句的每一列类型和顺序最好一致
      联合查询会自动去重,不想去重加union all
*/
#引入案例:查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees WHERE department_id>90 OR email LIKE '%a%';

SELECT * FROM employees WHERE email LIKE '%a%'
UNION #all
SELECT * FROM employees WHERE department_id>90;


发布了9 篇原创文章 · 获赞 0 · 访问量 103
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览