MySql数据库学习笔记

最新推荐文章于 2023-11-03 19:24:19 发布

bbq61898762

最新推荐文章于 2023-11-03 19:24:19 发布

阅读量444

点赞数

分类专栏：开发记录文章标签：数据库 mysql 学习

本文链接：https://blog.csdn.net/bbq61898762/article/details/127982846

版权

开发记录专栏收录该内容

15 篇文章 0 订阅

订阅专栏

多年前的数据库 MySQL学习笔记，找出来记录下

========================
注意，此处有修改数据库密码不成功时强制修改方法：
1.打开mysql.exe和mysqld.exe所在的文件夹,复制路径地址，如：E:\JAVAdev\MySql\bin
2.输入命令 mysqld --skip-grant-tables 回车，此时就跳过了mysql的用户验证；
3.关闭mysqld进程，打开新的cmd窗口直接输入mysql，不需要带任何登录参数直接回车就可以登陆上数据库。
4.输入show databases; 可以看到所有数据库说明成功登陆。
5.其中mysql库就是保存用户名的地方。输入 use mysql; 选择mysql数据库。
6.show tables查看所有表，有个user表，这里存放的就是用户名，密码，权限等等账户信息。
7.输入select user,host,password from user; 来查看账户信息。
8.更改root密码，输入update user set password=password(‘123456’) where user=‘root’ and host=‘localhost’;
9.再次查看账户信息，select user,host,password from user; 可以看到密码已被修改。
10.退出命令行，重启mysql数据库，用新密码尝试登录。

错误指令：
1064 语法错误！

数据库列类型
double：浮点型如：double（5,2）表示最多5位，必须有2位小数
decimal 十进制浮点，适合存储资金钱方面的数据，不会出现精度缺失
char：固定长度字符类型；char（此处最大255）
varchar：可变长度字符串类型；char（最大65535）需要单独拿出空间记录长度
text（clob）：字符串类型 mysql独有的通用的叫clob
blob：二进制字节类型
date：日期类型格式为yyyy-MM-dd
time：时间类型格式为hh:mm:ss
timestamp：时间戳类型

数据库指令cmd：
启动数据库服务 net start mysql
停止数据库服务 net stop mysql
登录数据库 mysql -uroot -p123
查看数据库 show databases;
切换数据库 use test
创建数据库 CREATE DATABASE mydb1；(可以加上IF NOT EXISTS，防止报错
charset=utf8可以不设置，因为安装的时候已经默认了utf8)
删除数据库 DROP DATABASE mydb1;
修改数据库编码 ALTER DATABASE mydb1 CHARACTER SET utf8(不常用)

DDL 操作表
DML 操作数据
DCL 用户权限
DQL 查询数据

DDL=====
查询数据库下所有表 SHOW TABLES;
创建表 CREATE TABLE 表名(列名列类型，列名列类型，列名列类型…);
删除表 DROP TABLE 表名;
查看表结构 DESC 表名;

修改表关键字： ALTER 注意和alert区分
添加列： ALTER TABLE 表名 add(列名列类型，列名列类型，列名列类型…);
修改列类型： ALTER TABLE 表名 modify 列名新列类型; 注意没有括号
修改列名： ALTER TABLE 表名 change 列名新列名列类型；注意没有括号且可以同时修改类型
删除列； ALTER TABLE 表名 drop 列名；
修改表名称： ALTER TABLE 表名 rename to 新表名；

DML=====
INSERT INTO 表名( 指定部分列，未指定列默认为NULL
列名，列名，…
)VALUES(
值，值，…
)；

INSERT INTO 表名不给出插入列时，默认为插入所有列，值的顺序需按表创建顺序相同
VALUES(
值，值，…
)；

UPDATE 表名 SET 列名=列值；修改所有的列值
UPDATE 表名 SET 列名=列值 WHERE 条件；修改指定列值，
条件语句运算符（=,!=,<>,>,<,>=,<=, between…and, in(…),IS NULL, NOT, OR, AND）
注意!=和<>相同, =NULL永远是false需要写成IS NULL才能找出NULL值；

DELETE FROM stu where 条件；一定要加WHERE 否则会全删除；

DCL=====
一个项目一般创建一个用户，一个项目对应的数据库一般只有一个！
一个用户只能对这个数据库有权限，其他数据库就不能操作；
1.创建用户
CREATE USER 用户名@IP地址 IDENTIFIED BY ‘密码’; 用户只能在指定ip登录
CREATE USER 用户名@‘%’ IDENTIFIED BY ‘密码’; 用户可以在任何ip登录
2.给用户授权
GRANT 权限1，… … ,权限n ON 数据库.* TO 用户名@IP地址
给这个用户授权有：create，alter，drop，insert，update，delete，select，all等权限
列：GRANT ALL ON stu.* TO yangchan@localhost; (是所有的表)
3.撤销权限
REVOKE 权限1，… … ,权限n ON 数据库. FROM 用户名@IP地址
4.查看权限
SHOW GRANTS FOR 用户名@IP地址
5.删除用户
DROP USER 用户名@IP地址

DQL=====
1.查询所有列
SELECT * FROM 表名；

2.查询指定列
SELECT 列名， … ，列名 FROM 表名；

3.去重查询关键字 distinct
SELECT DISTINCT * FROM 表名；
SELECT DISTINCT 列名， … ，列名 FROM 表名；

4.列运算查询
（1）SELECT 列名运算符值 FROM 表名；注意：
（2） mysql不能用+连接字符串也不能用||连接而用concat 列入concat（列名，列名 … … ）；
更复杂如：select concat(‘我叫’,ename,‘，我的工作是’,job) from emp;
（3） ifnull（列名，值）将null值转换为其它值；
（4）给列起别名 select ename as ‘姓名’ ,job as ‘工作’ from emp; 其中as可以省略

条件查询
select * from emp where sal > 值；条件运算符同修改数据；

模糊查询
select * from emp where ename like ‘%刚’；刚字在后的；
select * from emp where ename like ‘王%’；姓王字的；
select * from emp where ename like ‘%小%’；只要有小字；
select * from emp where ename like ‘张_’；如果没有百分号加下划线，一个下划线代表一个字符；
select * from emp where ename like ‘___’；三个下划线查询名字等于三个字的员工；

排序查询
select * from emp ORDER BY sal；升序（默认）
select * from emp ORDER BY sal asc；升序
select * from emp ORDER BY sal desc；降序

select * from emp ORDER BY sal asc ,comm desc；多列排序最多三个优先条件

聚合查询
select count（）from emp；查询有效行数，整行全null不计入
select count（comm）from emp； comm为null不统计
select count（1）from emp；和一样的效果

select sum（sal）from emp； sal列所有的值求和
select max（sal）from emp； sal列最高值
select min（sal）from emp； sal列最低值
select avg（sal）from emp； sal列平均值

select count（*）人数，sum（sal）总和，avg（sal）平均，from emp；别名显示

分组查询
select job , sum（sal）from emp group by job;
select job , count() from emp group by job;
select deptno , count() from emp where sal >10000 group by deptno; 前置条件
用having 后置条件如：
select deptno , count() from emp where sal >10000 group by deptno having count() >=2
执行顺序
select
form
where
group by
having
order by

limit查询 mysql方言
select * from emp limit 0,5；第0行起 5条记录

查询数据库编码方式
show variables like ‘char%’;

备份数据库类容
mysqldump -uroot -p123 mydb>c:/a.sql

恢复数据库
mysql -uroot -p123 mydb<c:/a.sql 注意恢复前先要自己创建数据库 CREATE DATABASE mydb；
也可登录之后在mydb数据库，使用 source c:/a.sql 语句恢复

设置主键
方法一：创建表时
在主键后面加 PRIMARY KEY,如：
CREATE TABLE 表名(列名列类型 PRIMARY KEY，列名列类型，列名列类型…);
最后用 PRIMARY KEY(列名) 表示主键，如：
CREATE TABLE 表名(列名列类型，列名列类型，列名列类型… PRIMARY KEY(列名));

方法二：修改表时
ALTER TABLE 表名 ADD PRIMARY KEY(列名)；

删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

主键特征：
唯一
非空

外键特征：
外键要引用主键
可以为空
可以为复
一张表可以有多个外键

CREATE TABLE emp(
sid INT PRIMARY KEY AUTO_INCREMENT，
tid INT,
CONSTRAINT fk_emp_dno FOREIGN KEY(tid) REFERENCES dno(tid);
);
修改外键
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dno FOREIGN KEY(tid) REFERENCES dno(tid);
解除外键
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dno;
删除外键
LTER TABLE emp tid;

合并查询：
前提–>结果集的列数类型完全相同
select * from ab union all select * from cd;
不带all，完全相同的行会被去除
select * from ab union select * from cd;

连接查询：
分类：
1.内连接
2.外连接

左外连接
右外连接
全外连接
3.自然连接

1.内连接：
笛卡尔积查询
SELECT * FROM emp,dept; 结果会数据为两个表相乘
去笛卡尔积查询

方言：SELECT * FROM emp,dept where emp.deptno=dept.deptno;
标准：SELECT * FROM emp inner join dept on emp.deptno=dept.deptno;
自然：SELECT * FROM emp natrural join dept

2.外连接：
左外连接就是说左边表为主表，那么主表中的所有记录无论满足不满足条件，都要打印出来，当不满足条件右表NULL补位

左外连接：SELECT emp.ename,emp.sal,IFNULL(dept.dname,‘无部门’) AS dname FROM emp left outer join dept on emp.deptno=dept.deptno;
右外连接：SELECT emp FROM emp right outer join dept on emp.deptno=dept.deptno;
全外连接：左右连接加上union 合并结果集就可以了

子查询(表位置和条件位置可以进行子查询)
SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE DEPTNO=20);
SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE DEPTNO=20);
SELECT * FROM emp WHERE (job,deptno) IN (SELECT job deptno FROM emp WHERE ename=‘某某’);

查询出部门编号为30的所有员工
所有销售员的姓名、编号和部门编号。
找出奖金高于工资的员工。
找出奖金高于工资60%的员工。
找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
无奖金或奖金低于1000的员工。
查询名字由三个字组成的员工。
10.查询2000年入职的员工。
查询所有员工详细信息，用编号升序排序
查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
查询每个部门的平均工资
查询每个部门的雇员数量。
查询每种工作的最高工资、最低工资、人数
*/

/1. 查询出部门编号为30的所有员工/
SELECT *
FROM emp
WHERE deptno=30;

/2. 查询所有销售员的姓名、编号和部门编号。/
SELECT ename, empno, deptno
FROM emp
WHERE job=‘销售员’;

/3. 找出奖金高于工资的员工。/
SELECT *
FROM emp
WHERE comm > sal

/4. 找出奖金高于工资60%的员工。/
SELECT *
FROM emp
WHERE comm > sal*0.6;

/5. 找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。/
SELECT *
FROM emp
WHERE (deptno=10 AND job=‘经理’) OR (deptno=20 AND job=‘销售员’)

/6. 找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。/
SELECT *
FROM emp
WHERE (deptno=10 AND job=‘经理’) OR (deptno=20 AND job=‘销售员’) OR (job NOT IN (‘经理’, ‘销售员’) AND sal >= 20000)

/8. 无奖金或奖金低于1000的员工。/
SELECT *
FROM emp
WHERE comm IS NULL OR comm < 1000

/9. 查询名字由三个字组成的员工。/
SELECT *
FROM emp
WHERE ename LIKE ‘___’

/10.查询2000年入职的员工。/
SELECT *
FROM emp
WHERE hiredate LIKE ‘2000-%’

/11. 查询所有员工详细信息，用编号升序排序/
SELECT *
FROM emp
ORDER BY empno

/12. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序/
SELECT *
FROM emp
ORDER BY sal DESC, hiredate ASC

查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
列：d.deptno, d.dname, d.loc, 部门人数
表：dept d, emp e
条件：e.deptno=d.deptno
/
SELECT d., z1.cnt
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z1
WHERE d.deptno = z1.deptno

/*
3. 列出所有员工的姓名及其直接上级的姓名。
列：员工姓名、上级姓名
表：emp e, emp m
条件：员工的mgr = 上级的empno
*/
SELECT *
FROM emp e, emp m
WHERE e.mgr=m.empno

SELECT e.ename, IFNULL(m.ename, ‘BOSS’) 领导
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno

/*
4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
列：e.empno, e.ename, d.dname
表：emp e, emp m, dept d
条件：e.hiredate<m.hiredate
思路：

先不查部门名称，只查部门编号!
列：e.empno, e.ename, e.deptno
表：emp e, emp m
条件：e.mgr=m.empno, e.hiredate<m.hireadate
*/
SELECT e.empno, e.ename, e.deptno
FROM emp e, emp m
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate

SELECT e.empno, e.ename, d.dname
FROM emp e, emp m, dept d
WHERE e.mgr=m.empno AND e.hiredate<m.hiredate AND e.deptno=d.deptno

/*
5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
列：*
表：emp e, dept d
条件：e.deptno=d.deptno
*/
SELECT *
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno

/*
7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
列：job, count(*)
表：emp e
条件：min(sal) > 15000
分组：job
/
SELECT job, COUNT()
FROM emp e
GROUP BY job
HAVING MIN(sal) > 15000

/*
8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
列：e.ename
表：emp
条件：e.deptno=(select deptno from dept where dname=‘销售部’)
*/

SELECT *
FROM emp e
WHERE e.deptno=(SELECT deptno FROM dept WHERE dname=‘销售部’)

/*
9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。
列：*
表：emp e
条件：sal>(查询出公司的平均工资)
/
SELECT e., d.dname, m.ename, s.grade
FROM emp e, dept d, emp m, salgrade s
WHERE e.sal>(SELECT AVG(sal) FROM emp) AND e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal

SELECT e.*, d.dname, m.ename, s.grade
FROM
emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno
LEFT OUTER JOIN emp m ON e.mgr=m.empno
LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.sal>(SELECT AVG(sal) FROM emp)

SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

/*
10.列出与庞统从事相同工作的所有员工及部门名称。
列：e.*, d.dname
表：emp e, dept d
条件：job=(查询出庞统的工作)
*/

SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename=‘庞统’)

/*
11.列出薪金高于在部门30工作的所有员工的薪金　的员工姓名和薪金、部门名称。
列：e.ename, e.sal, d.dname
表：emp e, dept d
条件；sal>all (30部门薪金)
*/
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal > ALL (SELECT sal FROM emp WHERE deptno=30)

/*
13.查出年份、利润、年度增长比
/
SELECT y1., IFNULL(CONCAT((y1.zz-y2.zz)/y2.zz*100, ‘%’), ‘0%’) 增长比
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ON y1.year=y2.year+1;

select * from emp e right outer join dept d on e.deptno = d.deptno