MySql基础练习

最新推荐文章于 2024-05-14 23:09:24 发布

毋十三

最新推荐文章于 2024-05-14 23:09:24 发布

阅读量245

点赞数

①修改密码

登录mysql的语法：mysql -uroot -proot
方法一:
 在my.ini的[mysqld]字段加入：
skip-grant-tables
重启mysql服务，这时的mysql不需要密码即可登录数据库
 然后进入mysql
mysql>use mysql;
 mysql>update user set password=password('新密码') WHERE User='root';
mysql>flush privileges;
 运行之后最后去掉my.ini中的skip-grant-tables，重启mysqld即可。
方法二:
 不使用修改my.ini重启服务的方法，通过非服务方式加skip-grant-tables运行mysql来修改mysql密码
 停止mysql服务
 打开命令行窗口，在bin目录下使用mysqld-nt.exe启动，即在命令行窗口执行: mysqld-nt --skip-grant-tables
 然后另外打开一个命令行窗口，登录mysql，此时无需输入mysql密码即可进入。
 按以上方法修改好密码后,关闭命令行运行mysql的那个窗口，此时即关闭了mysql，如果发现mysql仍在运行

的话可以结束掉对应进程来关闭。
 启动mysql服务

②简介

数据库：就是存储数据的仓库，其本质是一种文件系统，数据按照特定的格式将数据存储起来，用户可以通过SQL对数据库中的数据进行操作。

数据库管理系统-数据库-表

数据库管理系统(DBMS):是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一的管理和控制，以保证数据库的安全性和完整性，用户通过数据库管理操作数据库中的数据

Sql语句的分类

|-数据定义语言DDL：定义数据库、表、列。。。

|-数据操作语言DML：增、删、改

|-数据查询语言DQL：查

|-数据控制语言DCL：设置权限。

③常见命令

启动和停止服务

net start mysql
net stop mysql
mysql -uroot -proot

创建一个数据库

create database bos character set utf8

创建一个用户

create user wu identified by '22'

为用户授权(这个用户可以操作mysql者个数据库)

grant all on mysql.* to wu

使用新用户登录

mysql -uwu -p22

查看数据库

show databases

查看某个数据库的定义信息

show create database 数据库名

删除数据库

drop database 数据库名

切换数据库

use 数据库名

查看正在使用的数据库

select database()

创建表

create table 表名(
  字段名  类型  (长度)  [约束] [auto_increment]
);

单表约束

*主键约束：primary key  唯一、非空
*唯一约束：unique  唯一
*非空约束：not null 非空

查看表

show tables

查看表结构

desc 表名

删除表

drop table 表名;

修改表

alter table 表名 add 列名 类型(长度) [约束];    添加列
alter table 表名 modify 列名 类型(长度) [约束]; 修改列长度
alter table 表名 change 旧列名 新列名 类型(长度) [约束]; 修改列名
alter table 表名 drop 列名;  删除列
alter table 表名 to 新表名;  修改表名
alter table 表名 character set 字符集;  修改字符集

插入数据

注：中文乱码问题

方式一：【不建议！】

直接修改数据库安装目录里面的my.ini文件的第57行

方式二：

set 乱码字段 gbk;

insert into 表名(列名…) values(值…);
insert into 表名 values(值…);  向表中插入所有列

更新数据

update 表名 set 字段名=值 where 条件

删除数据

注：delete删除，uid不会重置！而使用truncate操作，uid会重置

delete删除的时候是一条一条的删除记录，它配合事务，可以将删除的数据找回。

truncate删除，它是将整个表摧毁，然后再创建一张一模一样的表。它删除的数据无法找回。

start transaction;
delete from 表名 where 条件;
rollback;        ---->删了的数据又回来了。

delete from 表名 where 条件;
truncate table 表名;  不可恢复的删除

简单查询

select pname from product;  简单查询
select [distinct]* |列名 from 表名 [where 条件];  去除重复的查询
select pname as name from product as p;  别名查询，as可以省略
select pname,price+10 from product;  表达式结果查询

条件查询

select * from product where pname=’’;
select * from product where pname like ‘%%’;
select * from product where pid in (1,5,9);

排序

select * from product order by price;

聚合函数查询

select sum(price) from product

分组查询

select pid,avg(price) from product group by pid;

组合

select distinct * | 列名
from 表名
where 查询条件
group by 分组字段
having 分组条件
order by 排序字段

表与表之间的关系

|-一对多关系：如客户和订单

|-多对多关系：如学生和课程

|-一对一关系(了解)：通常通过一张表来体现

为表添加外键约束字段

alter table product add constraint p_fk foreign key (外键关联字段) references category cid

多表查询

交叉连接查询，笛卡尔积严重

select * from A,B;

内连接查询 inner join 交集

隐式内连接：select * from A,B where 条件
显式内连接：select * from A inner join B on条件

外连接查询 left | right outer join

左外连接：select * from A left outer join B on 条件  左表全部+交集
右外连接：select * from A right outer join B on 条件  右表全部+交集

子查询

select pname from product where cid=(select cid from category where cname=’类别名’);

创建索引

索引提高了查询的效率，但是降低了增删改的效率。谨慎使用。

索引的使用时机和优化，参考：https://blog.csdn.net/lwl2014100338/article/details/78817296

主键一定是唯一性索引，唯一性索引并不一定就是主键

create（unique）index indexName on table（columnname（length））；
drop index （indexName）on table;
show index from table_name
添加索引：alter TABLE table1_name add （unique）index_name（column_list）

锁机制

表锁（共享读锁，排他写锁）

LOCK TABLES bc_staff READ;   加读锁：不同的线程都可以进行查询
LOCK TABLES bc_staff WRITE;  加写锁：不能查询，只可以修改

UNLOCK TABLES;           参考别人的博客，有的写 UNLOCK 表名 进行解锁的，自己试的不知道为什么解不了锁，只有写这个才有用，后期补充吧。

行级锁：事务开启之后，对一行数据上了锁，事务内只能对其进行相关的操作。

BEGIN TRANSACTION;
SELECT * FROM bc_staff WHERE id="1" FOR READ[UPDATE];
UPDATE bc_staff SET name="tom" WHERE id="1";(ERROR)
COMMIT;

exists 和 in 的区别

Select * from A where id in（select id from B（返回数据））
当B表数据集必须小于A表的数据集时，用in优于exists
Select * from A where exists （select 1 from B where B.id=A.id（返回true或false））
当A表的数据集小于B表的数据集时，用exists优于in

题：mysql行转列，显示每个月的数量统计。

SELECT year,
	MAX(CASE month WHEN "1" THEN amount END) m1,
	MAX(CASE month WHEN "1" THEN amount END) m2,
	MAX(CASE month WHEN "1" THEN amount END) m3,
	MAX(CASE month WHEN "1" THEN amount END) m4
FROM statistical
GROUP BY year;

基础练习

①查询名字由三个字组成的员工

SELECT * FROM emp WHERE ename LIKE '___'

②查询2000年入职的员工

SELECT * FROM emp WHERE hiredate LIKE '2000%';

③显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000

SELECT job,SUM(sal) FROM emp GROUP BY job HAVING SUM(sal)>50000 ORDER BY SUM(sal) ASC

SELECT job,sum(sal) total
FROM emp
GROUP BY job
HAVING job<>"销售员" AND total>50000;

④多表查询

/*1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/

----------------------------------------------
select d.deptno,d.dname,d.loc,e.total
from dept d,(select deptno,count(empno) total from emp group by deptno)e
where d.deptno=e.deptno and e.total>=1;
-----------------------------------------------
多表查询实现
select d.deptno,d.dname,d.loc,count(empno) total
from emp e,dept d
where e.deptno=d.deptno
group by d.deptno
having total>=1;

/**************************************************/

/*2. 列出薪金比关羽高的所有员工。*/

select *
from emp
where sal>(select sal from emp where ename="关羽");

/**************************************************/

/*3. 列出所有员工的姓名及其直接上级的姓名。*/

select e.ename,mgr.ename         ---->可以简单实现，但是没有boss
from emp e,emp mgr
where e.mgr=mgr.empno;
----------------------------------
select e.ename,ifnull(m.ename,'boss') as lead
from emp e left join emp m on e.mgr=m.empno;

/**************************************************/

/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/

select e.empno,e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.hiredate<(select hiredate from emp m where m.empno=e.mgr);
--------------------------------------------------------
select e.empno,e.ename,d.dname
from emp e left join emp m on e.mgr=m.empno 
left join dept d on d.deptno=e.deptno
where e.hiredate<m.hiredate;
/**************************************************/

/*5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/

select d.dname,e.*
from dept d left join emp e on d.deptno=e.deptno;
-------------想要那张表显示全部信息用left或right指定表即可---------------
select d.dname,e.*
from emp e right join dept d on e.deptno=d.deptno;

/**************************************************/

/*6. 列出所有文员的姓名及其部门名称，部门的人数。*/

------连接查询主要针对的是左右两表之间有一表为空，还想显示其信息。--------
select d.dname,e.ename,e.job,dcount.s
from emp e,dept d,(select deptno,count(empno) s from emp group by deptno)dcount
where e.deptno=d.deptno and e.job="文员" and dcount.deptno=d.deptno;

/**************************************************/

/*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/

select job,count(empno)
from emp e 
group by job
having min(e.sal)>15000;

/**************************************************/

/*8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/

select ename
from emp 
where deptno=(select deptno from dept where dname="销售部");

/**************************************************/

/*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/

select d.dname,e.*,m.ename,s.grade,a.avg
from emp e,emp m,dept d,salgrade s,(select avg(sal) avg from emp) a
where e.deptno=d.deptno and e.mgr=m.empno and e.sal between s.losal and s.hisal and e.sal>a.avg;
-----------------------注意所有员工-------------------------------
select d.dname,e.*,m.ename,s.grade
from emp e
natural left join dept d
left join emp m on e.mgr=m.empno
left join salgrade s on e.sal between s.losal and hisal
where e.sal>(select avg(sal) from emp);
/**************补一个知识点***************/
设有表A和B，它们都具有一系列的列属性
1、自然连接 natural join               ！因为自动投影所以可以不加条件了！
select <选择列>
from 表A natural join 表B
将表A和表B进行自然连接：在A和B所有共同属性上做等值连接，然后再投影去掉重复属性。

2、内连接 inner join
select <选择列>
frome 表A inner join 表B on <连接条件>
将表A和表B进行内连接：当至少有一个满足连接条件时，返回满足条件的行。

3、外连接 outer join        右外连接和这个语义差不多。
select <选择列>
from 表A left join 表B on <连接条件>
将表A和表B进行左外连接：在自然连接的结果上，如果t是A中不满足连接条件的元组，则将t添加到结果中，并且t本身不存在的属性取NULL
全外连接 full join
select <选择列>
from 表A full join 表B on <连接条件>

/**************************************************/

/*10.列出与庞统从事相同工作的所有员工及部门名称。*/

select e.*,d.dname
from emp e
natural left join dept d
where e.job=(select job from emp where ename="庞统") and e.ename<>"庞统";
--------------------------------------------------------
SELECT e.*, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');

/**************************************************/

/*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/

select e.ename,e.sal,d.dname
from emp e
natural left join dept d
where e.sal>all(select sal from emp where deptno=30);
-------------觉得只要出现所有的关键字使用natural就特别的顺---------------
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)

/**************************************************/
SELECT ename,sal,dname
FROM emp NATURAL JOIN dept
WHERE sal> (SELECT MAX(sal) FROM emp WHERE deptno=30);
/**************************************************/

/*12.列出在每个部门工作的员工数量、平均工资。*/

select dname,count(empno),avg(sal)
from emp
natural right join dept
group by deptno;
------------------------------------------------
SELECT d.dname, e.cnt, e.avgsal
FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
WHERE e.deptno=d.deptno;


------这里边第一个参数是下标从0开始的，4就是第五条记录开始显示，显示4条-------
SELECT * FROM emp;
SELECT * 
FROM emp
LIMIT 4,4;

毋十三

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql基础练习

①修改密码登录mysql的语法：mysql -uroot -proot方法一: 在my.ini的[mysqld]字段加入：skip-grant-tables重启mysql服务，这时的mysql不需要密码即可登录数据库然后进入mysqlmysql&gt;use mysql; mysql&gt;update user set password=password('新密码') WH...
复制链接

扫一扫