查询所有列
SELECT * FROM 表名; {其中“*”表示查询所有列}
去重复查询
select distinct deptno from emp; 给部门编号去重
查询指定列
SELECT 列1 [, 列2, ... 列N] FROM 表名;
Select ename,job,sal,comm from emp;
列运算
数据类型的列可以做加、减、乘、除运算
给员工涨工资【倍数1.5】:SELECT sal*1.5 FROM emp;
sleect *,sal+ifnull(comm,0) from emp;
字符串类型可以做连续运算
concat()连接函数
SELECT CONCAT('$', sal) FROM emp;
给列起别名
SELECT IFNULL(comm, 0)+1000 奖金 FROM emp;
条件控制
Where: delete from user where id = 2;
模糊查询
关键字: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'%二'
排序查询
升序:其中ASC是可以省略的
SELECT * FROM emp WHERE sal ORDER BY sal ASC;
DESC 【降序排序】
降序:SELECT * FROM emp WHERE comm ORDER BY comm DESC;
使用多列作为排序条件
order by 列名1 asc/desc,列名2 asc desc
SELECT * FROM emp WHERE sal ORDER BY sal ASC, comm DESC;
排序规则:
先按照列名1进行排序,当列名1中的值相同时,按照列名2进行排序(只拍列名1中列值相同的部分)
select * from emp where sal order by sal asc, COMM desc;
where sal 或者 where comm
指的是以那一列值为主进行排序,如果有重复的值,在按照第二
聚合函数
COUNT 求总量
select count(*) from emp;
select count(1) from emp; 【* 和数字的意义完全相同】
select count(comm) from emp; 【只会统计有效值,null是不会进行统计的】
select count(mgr) from emp;
MAX 求最大值
select max(sal) from emp;
MIN 求最小值
select min(sal) from emp;
SUM 求和
select sum(comm) from emp;
AVG 求平均数
select avg(sal) from emp;
分组查询
select count(*) from emp group by deptno;
例如:查询 每一个部门中工资大于10000的人数
select count(*) from emp where sal>10000 group by deptno
select deptno,count(*) from emp where sal>10000 group by deptno;
例如:查询 每一个部门中工资大于20000的人数,显示 人数大于2人的部门
Select deptno count(*) from emp where sal > 20000【分组前的条件
group by deptno having count(*) >2【分组后的条件】;
多列分组:
查询每个班下的男生和女生人数
select classid,sex,count(*) as 总数 from stu group by classid,sex
select 列名 from 表名
where 限定条件
group by 分组的列
[having 分组之后的筛选条件]
order by 排序的列 desc/asc
1)当查询语句中出现group by的话,select后面能添加的只有被分组的列和聚合函数
2)having必须和group by一起出现
sql语句关键字使用位置
select * from 表名
where 筛选条件
geoup by --> 分组
having --> 分组后的筛选条件
order by 排序的列 asc/desc
limit 开始位置,显示条数;
limit 0,5
Mysql 方言
limit子句(方言)
SELECT * FROM emp LIMIT 4, 3;
--> 其中4表示从第5行开始,其中3表示一共查询3行。即第5、
例如:我要查询第三页的信息【已知每一页会显示10条数据】
select * from emp limit 20,10;
数据库约束
数据约束是用来保证数据的完整性:就是保证数据库中的数据的有效性和准确性
主键约束
关键字:primary key 【pk】
添加主键约束的方式:
.直接写在某一列的后面
CREATE TABLE stu(
sid CHAR(6) PRIMARY KEY,
snameVARCHAR(20),age INT,gender VARCHAR(10) );
2.专门去指定某一列为主键列
CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(20),
age INT,gender VARCHAR(10),PRIMARY KEY(sid) );
创建表
/*create table student(
stuid int PRIMARY key,
stuname VARCHAR(20),
gender CHAR(2)
);*/
添加数据
1.添加字符超出范围了(字符长度)
2.主键不能为空
3.主键值不能重复
insert into student values(1,'Colin','nv'),(2,'CoCo','nv'),(3,'Abc','nv');
修改表时指定主键:ALTER TABLE stu ADD PRIMARY KEY(sid);
删除主键: ALTER TABLE stu DROP PRIMARY KEY;
指定主键列自增长的方式
关键字: AUTO_INCREMENT 【auto_increment】
非空约束
使用方法:
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
【对sname列设置了非空约束】
唯一约束
使用方法:
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) UNIQUE,【 对sname列设置了唯一约束】
默认值约束
使用方法:
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
外键约束
关键字:foreign key {fk}
语法:
CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表(关联表的主键列名)
创建表时指定外键约束
create talbe emp (
empno int primary key,
…
deptno int,
CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES
emp(empno)
);
修改表时添加外键约束
ALTER TABLE emp
ADD CONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);
修改表时删除外键约束
ALTER TABLE emp
DROP FOREIGN KEY fk_emp_deptno;/约束名称/
外键约束名称是区分大小写的!
子查询出现的位置:
问:得到20号部门的员工姓名,编号,工资,部门号
select e.ename,e.sal,e.empno,e.deptno
from (select * from emp where deptno = 20) e;
●单行单列:
求工资高于平均工资的所有人
select * from emp where sal > (select avg(sal) from emp);
●多行单列:
大于所有20部门员工工资的员工信息
select * from emp where sal >all (select sal from emp where deptno = 20);
●多行多列:
SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件
主要做副表使用【常用】
数据备份和恢复
1. 数据库导出SQL脚本
备份数据库内容,并不是备份数据库!
> mysqldump –u用户名 –p密码 数据库名>生成的脚本文件路径
> 例如:mysqldump -uroot -proot c1810a>C:\c1810a.sql (与mysql.exe和mysqld.exe一样, 都在bin目录下)
> 注意,不要打分号,不要登录mysql,直接 dos下运行
> 注意,生成的脚本文件中不包含create database语句
2. 执行SQL脚本
先创建一个数据库
方式步骤:
> mysql -u用户名 -p密码 新数据库名字<脚本文件路径
> 例如:
* 新创建newmydb1库
* mysql -uroot -proot newmydb1<D:\mydb1.sql
> 注意,不要打分号,不要登录mysql,直接在dos下运行
恢复数据库信息
1.3.2 备份-图形化方式
图1:首先找到我们要备份的数据
图2:点击备份结构和数据【选择好保存文件的路径即可】
图3:点击关闭就可以了
图5:数据恢复(新建数据库)
图7:最后刷新一下即可
数据库高级
关系数据库的几种设计范式介绍
1、第一范式(1NF)【第一范式就是无重复的列。】
2、第二范式(2NF)【主外键关系创建】
3、第三范式(3NF)【应用只能应用主键列 ,不能出现非主键的其他列】
存储过程
create procedure select_student()
begin
select * from student;
end
调用:
Call select_student()
存储过程
输入参数的存储过程
输出参数的存储过程
输入输出参数的存储过程
条件判断的存储过程
带有循环功能的存储过程
使用查询的结果赋值给变量(into
视图
视图本质上就是对查询的封装
定义视图,建议以v_开头
Create view 视图名称 as select语句;
什么是事务
执行演示:
Begin; 【开启事务】
sql语句
commit; 【都成功 手动提交】
rollback; 【任何一部分失败了 回滚 返回最初状态】
修改密码【重要】
.在知道密码的情况下修改密码
使用root登录 修改mysql数据库中user表
使用password()函数进行密码加密
逐一修改完成后需要刷新权限
示例:
User mysql
Update user set password=password(‘123’) where user=’root’
Flush privileges;
忘记密码【重要】
如果是忘记了自己的密码怎么办?
首先找到数据库的总配置文件 my.ini
并找到 【mysqld】
在他的下面我们手动添加一段代码
skip-grant-tables
保存好后,重新启动数据库服务。再次登录数据库 mysql -uroot 不需要添加密码就可以登录了 在使用上面的修改代码指令
Update user set password=password(‘123’) where user=’root’
Flush privileges;
新增密码搞定
最后回到配置文件中将刚刚添加的skip-grant-tables代码删除。并重新启动服务即可。