数据库-MySQL

已于 2023-01-14 12:12:37 修改
阅读量103 收藏 1
点赞数
分类专栏： 计算机基础 文章标签： 数据库 mysql sql
于 2023-01-13 17:14:03 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/fengthon/article/details/128676350
版权

文章目录

表的基本操作

创建表

# 创建一张表
CREATE TABLE users(
	id INT,
	name VARCHAR(255),
	password VARCHAR(255),
	birthday DATETIME
	 )
 -- 设置字符集
	CHARACTER SET utf8 COLLATE utf8_bin;

表的插入

#创建表的练习
CREATE TABLE employee ( 
	id INT, 
	NAME VARCHAR ( 32 ), 
	sex CHAR ( 1 ), 
	job VARCHAR ( 32 ), 
	salary DOUBLE, 
	resume TINYTEXT );

INSERT INTO employee
VALUES
	( 1, "fwj", "男", "学生", 10000, "我是一名小学生~" );

SELECT* FROM employee;

表的复制和去重

复制

-- 复制表结构
CREATE TABLE my_tab LIKE emp

-- 将emp复制到my_tab中
INSERT INTO my_tab
	SELECT *FROM emp

去重

-- 创建临时表
CREATE TABLE temp LIKE my_tab
-- 去重后插入临时表
INSERT INTO temp 
	SELECT DISTINCT *FROM my_tab
-- 删除原表中的记录
DELETE FROM my_tab
-- 将临时表复制到原表
INSERT INTO my_tab 
	SELECT *FROM temp 
-- 删除临时表
DROP TABLE  temp

表的修改

-- 增加一列
ALTER TABLE employee 
	ADD image VARCHAR(32) NOT NULL DEFAULT ''
	AFTER RESUME

--删除image列
ALTER TABLE emp
	DROP image

-- 改变长度
ALTER TABLE employee
	MODIFY job VARCHAR(60)

-- 修改表名
RENAME TABLE employee to emp

-- 改变列名
ALTER TABLE emp
	CHANGE name user_name VARCHAR(32)NOT NULL DEFAULT ''

-- 修改字符集
ALTER TABLE emp CHARACTER SET utf8

-- 显示表结构（单独执行）
DESC emp

CRUD操作

delete

-- 删除整条记录
DELETE FROM emp
	where user_name="小明"

-- DELETE FROM emp 删除所有记录
-- DROP TABLE emp 删除整张表

insert

CREATE TABLE t6(
	id INT,
	name VARCHAR(32),
	price DOUBLE NOT NULL DEFAULT 2000);
	
-- 默认值
INSERT INTO t6(id,name)
	VALUES(1,"vivo手机"),(2,'华为手机')

select

创建学生成绩表单

CREATE TABLE student(
	id INT NOT NULL DEFAULT 1,
	name VARCHAR(32) not null default '',
	chinese float not null default 0.0,
	english float not null default 0.0,
	math float not null default 0.0
);
insert into student (id,name,chinese,english,math)
	values(1,'韩顺平',89,78,90);
insert into student (id,name,chinese,english,math)
	values(2,'张飞',67,98,56);
insert into student (id,name,chinese,english,math)
	values(3,'宋江',87,78,77);
insert into student (id,name,chinese,english,math)
	values(4,'关羽',88,98,90);
insert into student (id,name,chinese,english,math)
	values(5,'赵云',82,84,67);
insert into student (id,name,chinese,english,math)
	values(6,'欧阳锋',55,85,45);
insert into student (id,name,chinese,english,math)
	values(7,'黄容',75,65,30);
insert into student (id,name,chinese,english,math)
	values(8,'李白',70,65,30);
insert into student (id,name,chinese,english,math)
	values(9,'李发',75,90,80);

查询

-- 查询姓名和英语成绩
SELECT name,english from student

-- 英语成绩去重
SELECT DISTINCT english from studen

-- 查询所有学生信息
select * from student

-- 统计每个学生总分
SELECT name,(chinese+english+math) from student
-- 列取别名
SELECT name as "姓名",(chinese+english+math) as total_score from student

-- 查询赵云的成绩
SELECT *FROM student
	where name="赵云"

-- 查询英语成绩大于90
SELECT *FROM student
	where english > 90

-- 查询math>60 && 英语>90
SELECT *FROM student
	where math>60 and english>90

-- 查询英语大于语文
SELECT *FROM student
	where english > chinese
	
-- 总分大于200且数学小于语文姓赵的学生
SELECT *FROM student
	where (chinese+math+english)>200 and 
	math<chinese AND name LIKE "赵%" -- 模糊查询
	
-- 英语成绩在80~90
SELECT *FROM student
	where english BETWEEN 80 and 90

-- 数学成绩为89,90,91
SELECT *FROM student
	where math in(89,90,91)

-- 数学成绩排序
SELECT *FROM student
	ORDER BY math; -- 升序
	ORDER BY math DESC;-- 降序
	
-- 总分排序
SELECT name as "姓名",(chinese+math+english) as total_score FROM student
	ORDER BY total_score DESC;

-- 姓李的排序
SELECT name,(chinese+math+english) as total_score FROM student
	where  name LIKE "李%"
	ORDER BY total_score DESC

update

-- 更改所有人的薪水
UPDATE emp SET salary=5000

-- 指定更改
UPDATE emp 
	SET salary=3000
	WHERE user_name="fwj"

-- 增加薪水	
UPDATE emp 
	SET salary=salary+1000,job="上班"
	WHERE user_name="fwj"

查询

# emp表
create table emp (
	empno int primary key, #编号
	ename nvarchar(30), 
	job nvarchar(30), 
	mgr int , # 上级编号
	hiredate datetime, #入职时间
	sal decimal(6.2),
	comm decimal(6.2), #红利
	deptno int , #部门编号
	foreign key(deptno) references dept(deptno)) default character set utf8;

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, null, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-4-19', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7876, 'ADAMS', 'CLERK', 7788, '1987-5-23', 1100, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20);
insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10);


# dept表
create table dept (deptno int primary key, dname nvarchar(30), loc nvarchar(30)) default character set utf8;

insert into dept (deptno, dname, loc) 
values (10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

#工资级别表
CREATE TABLE salgrade (
	grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,-- 工资级别
	losal DECIMAL ( 17, 2 ) NOT NULL,-- 该级别最低工资
	hisal DECIMAL ( 17, 2 ) NOT NULL 
);

INSERT  INTO salgrade VALUES(1,700,1200);
INSERT  INTO salgrade VALUES(2,1201,1400);
INSERT  INTO salgrade VALUES(3,1401,2000);
INSERT  INTO salgrade VALUES(4,2001,3000);
INSERT  INTO salgrade VALUES(5,3001,9999);

增强查询

-- 查询在1985-1-1之后入职的员工
SELECT * FROM emp
	WHERE hiredate>'1985-01-01'
	
-- 模糊查询 %表示任意个字符,_表示单个字符

-- 首字符是s的员工姓名和工资
SELECT ename,sal FROM emp
	WHERE ename LIKE 'S%'
	
-- 第三个字符是O的员工情况
SELECT *FROM emp
	WHERE ename LIKE '__O%'

-- 显示表结构
DESC emp

-- 员工工资升序排列
SELECT *FROM emp
	ORDER BY sal
	
-- 部门升序排序
SELECT *FROM emp
	ORDER BY deptno
	
-- 每个部门工资升序排列
SELECT *FROM emp
	ORDER BY deptno,sal

多表查询

-- 显示员工姓名,工资,所在部门编号和名称
SELECT ename,sal,dname,emp.deptno
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	ORDER BY emp.deptno 
	
	
-- 员工姓名，工资，工资级别
SELECT ename,sal,salgrade.grade
	FROM emp,salgrade
	where sal BETWEEN losal AND hisal
	
-- 自连接，一个表取多个别名拆成多个表
-- 员工姓名和上级姓名
SELECT worker.ename as worker_name,boss.ename as boss_name
	FROM emp worker,emp boss
	where worker.mgr=boss.empno

分页查询

# 分页查询, 按照id升序分页取出
#LIMIT 每页记录数*（第几页-1），每页记录书
-- 第一页
SELECT *FROM emp 
	ORDER BY empno 
	LIMIT 0,3
	
-- 第二页
SELECT *FROM emp 
	ORDER BY empno 
	LIMIT 3,3

分组查询

-- 每种岗位的雇员总数,平均工资
SELECT COUNT(*),job ,AVG(sal)
	FROM emp
	GROUP BY job
DESC emp

-- 获得补助的人数
SELECT COUNT(comm) FROM emp

-- 未获得补助的人数
SELECT COUNT(IF(comm IS NULL,1,NULL))
	FROM emp
	
-- 显示上级人数
SELECT COUNT(DISTINCT mgr)
	FROM emp
	
-- 每种岗位的工资最大差额
SELECT job,MAX(sal)-MIN(sal) 
	FROM emp
	GROUP BY job

-- 平均工资，且大于1000，降序排序，取出前两行
SELECT deptno,AVG(sal) as avg_sal 
	FROM emp
	GROUP BY deptno
	HAVING avg_sal>1000
	ORDER BY avg_sal DESC
	LIMIT 0,2
# 计算部门每个的平均工资,最高工资,最低工资
SELECT AVG(sal),MAX(sal),MIN(sal),deptno ,job
	FROM emp GROUP BY deptno,job; -- 按照部门分组查询

# 平均工资低于2000的部门号和平均工资
SELECT AVG(sal) as aver_sal,deptno 
	FROM emp GROUP BY deptno
		HAVING AVG(sal)<2000;

子查询

-- 与Smith部门相同的员工信息
SELECT *
	FROM emp
	WHERE deptno=(
		SELECT deptno
			FROM emp
			WHERE ename='SMITH'
			)
			
-- 与部门10员工工作相同的员工信息
SELECT ename,sal,deptno,job
	FROM emp
	WHERE job IN(
		SELECT DISTINCT job
			FROM emp
			WHERE deptno=10
			)AND deptno!=10 -- 排除部门10本身的员工

-- 子查询作为临时表
-- 查找ec_shop表中各个类别cat_id的最高价格的商品
SELECT cat_id,MAX(shop_price)
	FROM ec_shop
	GROUP BY cat_id
			
SELECT goods_id,ec_shop.cat_id,goods_name,shop_price
			FROM (
				SELECT cat_id,MAX(shop_price) as max_price
					FROM ec_shop
					GROUP BY cat_id
					)temp ,ec_shop
			WHERE temp.cat_id=ec_shop.cat.id
			AND temp.max_price=ec_shop.price

-- 查询比部门30所有员工的工资高的员工信息	
SELECT ename,sal,deptno
			FROM emp
			WHERE sal>ALL(
				SELECT sal 
					FROM emp
						WHERE deptno=30
						)

-- 查询比部门30其中一个员工的工资高的员工信息	
SELECT ename,sal,deptno
			FROM emp
			WHERE sal>ANY(
				SELECT sal 
					FROM emp
						WHERE deptno=30
						)			

-- 查询与Smith部门和工作相同的员工信息
SELECT * 
	FROM emp
	where (deptno,job)=(
			SELECT deptno,job
			FROM emp
			where ename='SMITH'
			)AND ename!='SMITH'
			
-- 查询每个所在部门工资高于平均工资的员工信息

-- 子查询得到每个部门的平均工资
SELECT deptno,AVG(sal)
	FROM emp
	GROUP BY deptno
-- 子查询作为临时表
SELECT ename,emp.deptno,sal
	FROM emp,(
				SELECT deptno,AVG(sal) as avg_sal
				FROM emp
				GROUP BY deptno
				) temp
	WHERE emp.deptno=temp.deptno
				AND emp.sal>temp.avg_sal

-- 每个部门最高工资的员工信息
SELECT ename,temp.max_sal,emp.deptno
	FROM emp,(
				SELECT deptno,MAX(sal) as max_sal
				FROM emp
				GROUP BY deptno
				) temp
		WHERE temp.max_sal=emp.sal AND temp.deptno=emp.deptno

-- 部门编号，名称，所在地址，人数
SELECT COUNT(*),deptno
	FROM emp
	GROUP BY deptno
	
SELECT dept.deptno,dname,loc,num
	FROM dept,(
				SELECT COUNT(*) as num,deptno
				FROM emp
				GROUP BY deptno
				)temp
		WHERE dept.deptno=temp.deptno 
	
SELECT temp.*,dname,loc
	FROM dept,(
				SELECT COUNT(*) as num,deptno
				FROM emp
				GROUP BY deptno
				)temp
		WHERE dept.deptno=temp.deptno

数据类型

浮点数

# 浮点数
CREATE TABLE t2(
	num1 FLOAT,
	num2 DOUBLE, 
	num3 DECIMAL(10,3)#小数点后3位
	);
# 添加数据
INSERT INTO t2 VALUES(8.153131,8.153131,8.153131);

整型

# 整型
 CREATE TABLE t1(
	id TINYINT);-- BIGINT,MEDIUMINT

#插入值	
insert into t1 values(127);

#查询表
SELECT * FROM t1

字符串

-- char(4)和varchar(4)表示字符数，不是字节数！

-- char(4)固定长度推荐使用,查询速度更快
CREATE TABLE t3(
	name char(4));

INSERT INTO t3 VALUES ("你好你好");

SELECT *FROM t3;

-- varchar(4)变长
CREATE TABLE t4(
	name varchar(4));
	INSERT INTO t4 VALUES("你好");
SELECT *FROM t4;

-- varchar不够用，可以用text
CREATE TABLE t5(
	content1 TINYTEXT,
	content2 MEDIUMTEXT,
	content3 LONGTEXT
	);
insert into t5 VALUES(
	"fengwenjie","fengwenjiefengwenjie","fengwenjie");
select *from t5;

函数

数学函数

-- 数学函数
-- 绝对值
SELECT ABS(-10) FROM DUAL

-- 向零取整
SELECT CEILING(1.1) FROM DUAL
SELECT FLOOR(1.1) FROM DUAL

-- 进制转化
SELECT BIN(8) -- 2进制
SELECT CONV(8,10,2) FROM DUAL
SELECT HEX(16)-- 16进制

-- 保留小数位数
SELECT FORMAT(93.2234,2) FROM DUAL

-- 取最小值
SELECT LEAST(10,5,90) FROM DUAL

-- 取余
SELECT MOD(10,3) FROM DUAL

-- 随机数[0,1]
SELECT RAND() FROM DUAL

-- 统计学生总数
SELECT COUNT(*) FROM student
-- 统计id非空
SELECT COUNT(id) FROM student

-- 数学成绩总和
SELECT SUM(math) from student;

-- 数学，语文，英语各科总和
SELECT SUM(math),SUM(english),SUM(chinese) from student

-- 数学平均分
SELECT AVG(math) FROM student;
-- 总分平均分
SELECT AVG(math+chinese+english) FROM student;

-- 最值
SELECT MAX(math+chinese+english),MIN(math+chinese+english) FROM student

加密函数

-- 查看用户
SELECT USER()FROM DUAL

-- 数据库名称
SELECT DATABASE();

-- MD5密码加密为32位
SELECT MD5('123456') FROM DUAL

-- PASSWORD加密为41位
SELECT LENGTH(PASSWORD('123456')) FROM DUAL

日期函数

-- 日期函数

-- 当前日期
SELECT CURRENT_DATE() FROM DUAL

-- 当前时间
SELECT CURRENT_TIME() FROM DUAL

-- 日期和时间
SELECT CURRENT_TIMESTAMP() FROM DUAL

CREATE TABLE mes(
	id INT,
	content VARCHAR(30),
	send_time datetime);
	
INSERT INTO mes VALUES (1,"北京新闻",CURRENT_TIMESTAMP)
INSERT INTO mes VALUES (2,"上海新闻",NOW())

-- 显示10分钟以内发送的新闻
SELECT *
	FROM mesa
	WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE)+10 >= NOW()

-- 日期间隔
SELECT DATEDIFF('2023-1-8','2003-10-2')/365 FROM DUAL
SELECT DATEDIFF('2083-10-2','2023-1-8') FROM DUAL

-- 1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL
-- 得到日期
SELECT FROM_UNIXTIME(1673146313,'%Y-%m-%d %H:%i:%s')FROM DUAL

SELECT *FROM mes

字符串函数

-- 字符串函数

-- 返回字符集
SELECT CHARSET(ename) FROM emp

-- 拼接
SELECT CONCAT(ename,' 工作是 ',job) FROM emp

-- 返回substring在string中出现的位置
SELECT INSTR('fengwenjie','jie') FROM DUAL

-- UCASE大写转化
SELECT UCASE(ename) FROM emp

-- LCASE小写
SELECT LCASE(ename) FROM emp

-- 从str左边取几位
SELECT LEFT('feng',2) FROM DUAL

-- 按字节返回长度
SELECT LENGTH('feng') FROM DUAL

-- 在job列中将MANAGER替换为经理
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp

-- 比较
SELECT STRCMP('fwj','f') FROM DUAL

-- 截取一列的前两位
SELECT SUBSTRING(ename,1,2) FROM emp

-- 去除左边空格/右边RTRIM/全部TRIM
SELECT LTRIM('  fwj')

-- 首字母小写方式返回emp的姓名
-- 截取第一位转化为小写，再与后面的进行拼接
SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2))
	FROM emp

数据库管理

数据库操作

# 删除数据库
DROP DATABASE fwj_db01

#创建数据库
CREATE DATABASE fwj_db01

#查看所有数据库
SHOW DATABASES

#查看创建数据库的信息
SHOW CREATE DATABASE fwj_db01

#数据库备份(dos界面)
mysqldump -u root -p -B fwj_db01 db01 >e://bak.sql

#恢复数据库(utf-8)
source e:\\bak.sql

数据库权限管理

# 数据库管理

-- 登录
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED by '123456'

-- 删除
DROP USER 'hsp_edu'@'localhost'

-- 修改root密码
SET PASSWORD=PASSWORD('123456')

-- 修改hsp_edu的密码
SET PASSWORD FOR 'hsp_edu'@'localhost'=PASSWORD('abcdef')

-- 授予hsp_edu用户,查询和插入表news的权限
GRANT SELECT,INSERT
		ON fwj_db01.news
		TO 'hsp_edu'

-- 回收权限
REVOKE ALL ON fwj_db01.news FROM 'hsp_ed'

流程控制

-- 查询emp表，如果comm为Null，则显示为0.0
SELECT ename,IF(comm IS NULL,0.0,comm)FROM emp

SELECT ename,IFNULL(comm,0.0) FROM emp

-- 将job显示为中文
SELECT ename,(SELECT CASE 
	WHEN job='CLERK' THEN
		'职员'
	
	WHEN job='MANAGER'THEN
	  '经理'
	WHEN job='SALESMAN'THEN
	  '销售人员'
	ELSE
		job END) as 'job'
FROM emp;

事务

# 事务
CREATE TABLE TRANSACTION_table(
	id INT,
	name VARCHAR(32));

-- 开始事务
START TRANSACTION
-- 执行操作
INSERT INTO TRANSACTION_table
	VALUES(1,'tom');
-- 设置保存点
SAVEPOINT a;

INSERT INTO TRANSACTION_table
	VALUES(2,'jack');
SAVEPOINT b;

SELECT *FROM TRANSACTION_table
-- 回退到保持点
ROLLBACK to a;

ROLLBACK to b;
-- 提交后保持点删除
COMMIT

#隔离
-- 查看当前隔离级别
SELECT @tx_isolation
-- 系统隔离级别
SELECT @global.tx_isolation
-- 设置当前隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

视图

# 视图
-- 创建
CREATE VIEW emp_view
		AS 
		SELECT empno,ename,job,deptno FROM emp;

-- 显示视图
SHOW CREATE VIEW emp_view

-- 查看字段
SELECT * FROM emp_view

-- 删除
DROP VIEW emp_view

#多表视图
#雇员编号，姓名，部门名称，薪水级别

CREATE VIEW muti_view 
		AS SELECT empno,ename,dname,grade
				FROM emp,dept,salgrade
				WHERE emp.deptno=dept.deptno AND
				sal BETWEEN losal AND hisal

SHOW CREATE VIEW muti_view

SELECT *FROM muti_view

索引

# 索引
CREATE TABLE index_table(
	id INT PRIMARY KEY,
	name VARCHAR(32));

-- 添加unique索引
CREATE UNIQUE INDEX id_index ON index_table(id);
-- 添加普通索引
CREATE INDEX id_index ON index_table(id)
ALTER TABLE index_table ADD INDEX id_index(id)
-- 添加主键索引
ALTER TABLE index_table ADD PRIMARY KEY (id)

-- 查询索引
SHOW INDEX FROM index_table

-- 删除索引
DROP INDEX id_index ON index_table
-- 删除主键索引
ALTER TABLE index_table DROP PRIMARY KEY

外连接

# 外连接

-- 列出所有员工的姓名，工作，所在部门名称
SELECT ename,job,dname 
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	
-- 右连接显示包括没有员工的部门
SELECT ename,job,dname
	FROM emp RIGHT JOIN dept
	ON emp.deptno=dept.deptno

-- 左连接显示包括没有员工的部门
SELECT ename,job,dname
	FROM dept LEFT JOIN emp
	ON emp.deptno=dept.deptno

约束

# 约束
-- PRIMARY KEY只能有一个，UNIQUE可以有多个
CREATE TABLE table
	( id INT PRIMARY KEY, -- 主键，不能重复
		name VARCHAR(32),
		grade INT UNIQUE, -- 该列不能重复
		PRIMARY KEY(id,name) -- 复合主键
		);
	
-- 外键-绑定关系
CREATE TABLE my_class(
  id INT PRIMARY KEY,
	class_name VARCHAR(32) NOT NULL DEFAULT ''
	);		
INSERT INTO my_class
	VALUES (100,'java'),(200,'c++')
SELECT *FROM my_class 

CREATE TABLE my_stu(
	id INT PRIMARY KEY,
	stu_name VARCHAR(32) NOT NULL DEFAULT '',
  class_id  INT,
	-- 绑定外键,学生班级编号与班级id绑定
 FOREIGN KEY(class_id) REFERENCES my_class(id)); 

INSERT INTO my_stu
	-- javk绑定java课,tom绑定c++课程,不存在的课无法绑定
	VALUES (1,'jack',100),(2,'tom',200)

补充练习

# 查看dept emp表结构
DESC dept
DESC emp

#显示所有部门名称
SELECT dname 
	FROM dept

#显示所有员工的姓名，年收入(工资+补助)
SELECT ename,12*sal+IFNULL(0,comm) AS '年收入' 
	FROM emp

#显示工资超过2850的员工姓名和工资
SELECT ename,sal
		FROM emp
		HAVING sal>2850
		
#显示工资不在1500~2850的员工姓名和工资
SELECT ename,sal
	FROM emp
	HAVING sal<1500 OR sal>2850
	
# 显示7566编号的员工姓名和所在部门编号
SELECT ename,deptno
	FROM emp
	WHERE empno=7566
	
#显示10和30部门工资超过1500的员工姓名和工资
SELECT ename,sal,deptno
	FROM emp
	WHERE (deptno=10 OR deptno=30) AND
	sal>1500
	
#显示无管理者的员工姓名和岗位
SELECT ename,job
	FROM emp
	WHERE mgr IS NULL
	
#显示在1980.2.1~1992.5.1入职的员工姓名，岗位，日期，并
#将日期排序
SELECT ename,job,hiredate 
	FROM emp
	WHERE hiredate BETWEEN '1980-02-01' AND '1992-05-01'
	ORDER BY hiredate

#显示获得补助的员工姓名，工资，补助，将工资降序排列
SELECT ename,sal,comm
	FROM emp
	WHERE comm IS NOT NULL
	ORDER BY sal DESC
	
#部门30的所有员工
SELECT ename,deptno 
	FROM emp
	WHERE deptno=30
	
#job为CLERK的员工
SELECT ename,empno,deptno
	FROM emp
	WHERE job='CLERK'
	
#佣金高于薪金
SELECT *FROM emp
	WHERE IFNULL(comm,0)>sal

#部门10中的所有经理和部门20的所有办事员
SELECT *FROM emp
	WHERE (deptno=10 AND job='MANAGER')
	OR (job='CLERK'AND deptno=20) 

# 既不是经理又不是办事员但是薪金>=2000?
SELECT *FROM emp
	WHERE (job !='MANAGER' AND job!='CLERK')
	AND sal>=2000

#收取佣金的员工的不同工作
SELECT DISTINCT job FROM emp
	WHERE comm IS NOT NULL

#各月倒数第三天受雇的员工
SELECT *FROM emp
	WHERE LAST_DAY(hiredate)-2=hiredate	

#工作时间超过12年的员工
SELECT * FROM emp
	WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR)<NOW()
	
#首字母小写显示姓名
SELECT 
	CONCAT(LCASE(SUBSTRING(ename,1,1)),SUBSTRING(ename,2))
		FROM emp
		
# 不带有R的员工姓名？
SELECT *FROM emp
	WHERE ename NOT LIKE '%R%'

# 显示所有员工姓名前三个字符
SELECT SUBSTRING(ename,1,3) FROM emp		

#用a替换所有A?
SELECT REPLACE(ename,'A','a') FROM emp

#工作超过10年的员工信息
SELECT ename,hiredate FROM emp
	WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR)<NOW()
	
#按照姓名排序
SELECT *FROM emp
	ORDER BY ename

#工作时间长短降序排列
SELECT ename,hiredate FROM emp
	ORDER BY DATEDIFF(NOW(),hiredate) DESC

#按照工作排序，相同工作按工资排序?
SELECT ename,job,sal FROM emp
	ORDER BY job,sal


#显示入职的年份和月份，按照月份排序，相同按照年份排序？
SELECT ename,CONCAT(YEAR(hiredate),'-',MONTH(hiredate))
	FROM emp
	ORDER BY MONTH(hiredate),YEAR(hiredate)


#显示日薪（30天）
SELECT ename,FLOOR(sal/30) as '日薪' FROM emp

#显示2月份受聘的员工？
SELECT ename,hiredate
	FROM emp
	WHERE MONTH(hiredate)=2

#显示每个员工入职的天数
SELECT ename,DATEDIFF(NOW(),hiredate) 
	FROM emp

#姓名中包含A的员工？
SELECT ename FROM emp
	WHERE ename LIKE '%A%'

#服务年限
SELECT ename,FLOOR(DATEDIFF(NOW(),hiredate)/365) as '工作年份',
			 FLOOR((DATEDIFF(NOW(),hiredate)%365)/31) AS '工作月份',
			 DATEDIFF(NOW(),hiredate)%31 AS '工作天数'
			 FROM emp

#至少有一个员工的部门?
SELECT count(*),deptno 
	FROM emp
	GROUP BY deptno
	HAVING count(*)>1
		
#薪金高于Smith的所有员工
SELECT ename,sal FROM emp
	WHERE sal>(
		SELECT sal FROM emp
			WHERE ename='SMITH'
			)
	
#入职晚于上级的员工?
SELECT ename,hiredate FROM emp emp1
	WHERE hiredate >(
		SELECT hiredate FROM emp emp2
			WHERE emp1.mgr=emp2.empno
			)
		
SELECT worker.ename as '员工',
			 worker.hiredate AS '入职时间',
			 leader.ename as '上级',
			 leader.hiredate AS '入职时间'
	FROM emp worker,emp leader
	WHERE worker.hiredate>leader.hiredate
	AND worker.mgr = leader.empno

#部门名称和部门的员工信息，和没有员工的部门?
SELECT dname,ename 
	FROM dept 
	LEFT JOIN emp ON dept.deptno=emp.deptno

#列出所有CLERK的员工姓名和部门名称
SELECT ename,dname,job
	FROM emp,dept
	WHERE job='CLERK' AND
	(emp.deptno=dept.deptno)
	
#列出最低工资大于1500的各种工作
SELECT min(sal),job
	FROM emp
	GROUP BY job
	HAVING min(sal)>1500

#列出'SALES'部门的员工信息
SELECT ename,dname
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
				AND dname='SALES'
				
#工资高于平均的员工
SELECT ename,sal 
	FROM emp 
	WHERE sal>(
			SELECT avg(sal) 
			FROM emp
			)

#与‘SCOTT’工作相同的员工
SELECT ename,job
			FROM emp
			WHERE job=(
				SELECT job FROM emp
					WHERE ename='SCOTT')

#工资高于部门30所有员工工资的员工
SELECT ename,sal 
		FROM emp
		WHERE sal>ALL(
			SELECT sal FROM emp
				WHERE deptno=30)

SELECT ename,sal 
		FROM emp
		WHERE sal>(
			SELECT max(sal) 
				FROM emp
				WHERE deptno=30)

#每个部门员工数量，平均工资和平均工作年限
SELECT deptno,count(*),
			 FORMAT(avg(sal),2),
			 FORMAT(avg(DATEDIFF(NOW(),hiredate)/365),2)
	FROM emp
	GROUP BY deptno

#员工姓名，部门名称，工资
SELECT ename,dname,sal
	FROM emp,dept
	WHERE emp.deptno=dept.deptno

# 部门详细信息和人数
SELECT dept.*,tmp.num
	FROM dept,(
							SELECT COUNT(*) as num,deptno 
							FROM emp
							GROUP BY deptno
							)tmp
				WHERE tmp.deptno=dept.deptno
			
#所有工作的最低工资
SELECT min(sal),job 
	FROM emp
	GROUP BY job

CREATE TABLE DEPARTMENT(
	departmentid VARCHAR(32)PRIMARY KEY,
	deptname VARCHAR(32)UNIQUE NOT NULL)
INSERT INTO DEPARTMENT
	VALUES('001','数学'),
				('002','计算机'),
				('003','化学'),
				('004','中文'),
				('005','经济')


CREATE TABLE CLASS(
	classid INT PRIMARY KEY,
	subject VARCHAR(32) NOT NULL DEFAULT '',-- 专业名
	deptname VARCHAR(32),-- 系名
	enrolltime INT,-- 入学年份
	num INT NOT NULL DEFAULT 0,-- 人数
	FOREIGN KEY(deptname)REFERENCES DEPARTMENT(deptname))
INSERT into CLASS
	VALUES
	(101,'软件','计算机','1995',20),
	(102,'微电子','计算机','1996',30),
	(111,'无机化学','化学','1995',29),
	(112,'高分子化学','化学','1996',25),
	(121,'统计数学','数学','1995',20),
	(131,'现代语言','中文','1996',20),
	(141,'国际贸易','经济','1997',30),
	(142,'国际金融','经济','1996',14)


CREATE TABLE STUDENT(
	studentid int PRIMARY KEY,
	name VARCHAR(32),
	age int,
	classid int,
	FOREIGN KEY(classid) REFERENCES CLASS (classid))
insert into STUDENT
	values
	(8101,'张三',18,101),
	(8102,'钱四',16,121),
	(8103,'王玲',17,131),
	(8105,'李飞',19,102),
	(8109,'赵四',18,141),
	(8110,'李可',20,142),
	(8201,'周瑜',16,112),
	(8203,'王亮',17,111),
	(8305,'董庆',19,102),
	(8409,'赵龙',18,101),
	(8510,'李丽',20,142)

#所有姓李的学生
SELECT name FROM student
	WHERE name LIKE '李%'

#开设专业超过1个的系名
SELECT count(*),deptname 
	FROM class
	GROUP BY deptname
	HAVING count(*)>1
	
#人数大于等于30的系

SELECT temp.*,departmentid
	FROM DEPARTMENT,(
							SELECT SUM(num),deptname
							FROM class
							GROUP BY deptname
							HAVING SUM(num)>=30)temp
			WHERE department.deptname=temp.deptname

#张三退学
START transaction
-- 班级人数减一
UPDATE class SET num=num-1
	WHERE classid=(
		SELECT classid FROM student
			WHERE name='张三')
-- 删除
DELETE 
	FROM student
	WHERE name='张三'

COMMIT;
打篮球的金鱼
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据库 - 初识MySQL
Junds0的博客
01-22 5080
第1关：创建数据库 本关任务：创建数据库。 mysql -uroot -p123123 -h127.0.0.1 create database MyDb; 第2关：创建表 本关任务：在数据库中创建一个表。 mysql -uroot -p123123 -h127.0.0.1 CREATE DATABASE TestDb; use TestDb; CREATE TABLE t_emp( id int, name varchar(32), deptId int, salary f
头歌MySQL数据库 - 初识MySQL 答案
热门推荐
kercii的博客
03-11 2万+
MySQL数据库 - 初识MySQL
如何使用 Workbench 远程连接到 MySQL 数据库 -MySQL Workbench 使用教程
蒋川@卡拉云
11-26 1万+
MySQL Workbench 是一款专门为 MySQL 设计的可视化数据库 GUI 管理工具，我们可以在自己的计算机上，使用图形化界面（GUI）远程管理 MySQL 数据库。 有关 MySQL 远程管理软件，你可以选择 Windows 下的 HeidiSQL，MacOS 下的 Sequel Ace 或者 MySQL 官方推出的跨平台客户端 MySQL Workbench 。如果你需要针对自己的工作流对数据库操作，定制开发，推荐一下卡拉云，详情见文末。 本.
数据库之MySQL
qq_44272681的博客
12-17 871
一、连接池(网络接入层) 主要负责连接管理、授权认证、安全等等。每个客户端连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。这一层并不是MySQL所特有的技术。 二、查询处理器(服务层) 第二层服务层是MySQL的核心，MySQL的核心服务层都在这层。查询解析，SQL执行计划分析，...
高级软件人才培训专家-day08-数据库-MySQL(3)
03-10
高级软件人才培训专家_day08-数据库-MySQL(3)
高级软件人才培训专家-day07-数据库-MySQL(2)
03-10
高级软件人才培训专家_day07-数据库-MySQL(2)
高级软件人才培训专家-day06-数据库-MySQL(1)
03-10
高级软件人才培训专家_day06-数据库-MySQL(1)
Web-数据库-mysql篇
04-15
Web-数据库-mysql篇 Web-数据库-mysql篇 Web-数据库-mysql篇
资料-MySQL数据库、视频-MySQL数据库
02-19
MySQL-运维篇.pdf MySQL-进阶篇.pdf MySQL-基础篇.pdf MySQL存储过程.xmind 文档 运维篇.rar 进阶篇.rar 基础篇.rar 资料-MySQL数据库 视频-MySQL数据库 文档 运维篇.rar 进阶篇.rar 基础篇.rar MySQL-运维篇.pdf ...
基于JSP的高校信息资源共享平台
heye0910032的博客
07-22 697
高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。通过使用JSP技术和MySQL数据库，本系统实现了一个稳定、安全、易用的高校信息资源管理平台。虽然在开发过程中遇到了一些技术挑战，但通过不断学习和实践，最终完成了一个能够满足用户需求的系统。未来，我们将继续优化系统功能，提升用户体验，以期达到更高的应用价值和社会效益。高校信息资源共享平台的开发和实现，为高校信息资源的管理和共享提供了一个有效的解决方案。
秒杀业务: 简单梳理
m0_59925573的博客
07-23 1596
（1）在一人一单优惠券秒杀中，先是解决了因线程安全导致的超卖问题，为了满足一人一单的业务需求需要给查询订单表查询库存表上锁，但是synchronized只能满足单机环境下线程安全，在分布式环境对多个JVM需要使用分布式锁，于是手写分布式锁,但是在极端情况下，例如业务超时，会出现“误删”问题，手写锁无法解决，于是引入redisson,借助看门狗机制可以有效解决这个问题。
家政项目小程序的设计
2401_85476437的博客
07-26 314
管理员账户功能包括：系统首页，个人中心，用户管理，家政人员管理，家政服务管理，咨询信息管理，咨询服务管理，家政预约管理，留言板管理，系统管理。主要技术：Java,Spring,mybatis,mysql,jquery,html。服务器：SpringBoot自带 apache tomcat。微信端账号功能包括：系统首页，家政服务，我的。JDK版本：Java JDK1.8。数据库可视化工具： navicat。数据库版本： mysql5.7。开发工具：IDEA(推荐)开发系统：Windows。
图数据库查询与操作指南-以Nebula图数据库为例
DZSpace，专注于计算机科学与技术领域的技术博主，致力于分享实用知识与技巧，帮助读者快速掌握技术要点，共同探索计算机世界的无限可能。欢迎访问DZSpace的博客，一起学习进步！
07-26 244
图数据库查询与操作指南-以Nebula图数据库为例
AccessLog| 一款开源的日志分析系统
ClkLog的博客
07-26 320
系统采用Java语言、搭配OLAP数据库，涵盖基本web日志分析，同时提供性能分析，帮助高效运维
基于宝塔的 Bytebase 可视化安装、配置、部署教程
最新发布
Bytebase的博客
07-26 328
Bytebase是一款使用Go语言开发的开源数据库管理软件，旨在为DBA（数据库管理员）、开发人员和平台工程师提供一个基于Web的协作工作区，用于管理数据库的整个生命周期。本文将介绍如何在宝塔面板上可视化安装、部署、配置Bytebase教程。
redis常用指令
m0_62289159的博客
07-22 852
redis常用指令
SQL基础入门：解锁数据库管理的钥匙
WayneYalejk的博客
07-26 250
在数字化时代，数据已成为企业最宝贵的资产之一。为了高效地存储、查询和管理这些数据，SQL（Structured Query Language，结构化查询语言）应运而生。SQL不仅是数据库管理员的必备技能，也是数据分析师、开发人员等多个领域从业者的重要工具。本文将带您踏入SQL的世界，从基础概念到实际应用，一步步解锁数据库管理的钥匙。强调SQL在数据处理和分析中的重要性，鼓励读者通过实践不断深化对SQL的理解和应用。
sqlalchemy使用json_unquote函数的mysql like查询
ithongchou的博客
07-23 1237
为您自己的数据库连接信息，并根据实际表结构修改模型类。函数查询MySQL JSON字段可以通过使用。下面是一个示例，假设有一个名为。请确保替换示例代码中的数据库连接字符串(在SQLAlchemy中使用。的表，其中包含一个名为。
mysql数据库 - 初识mysql
06-06
MySQL 是一种关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的开源关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值