每次想要插入数据时:
要先设置一下字体:set names gbk;
原因: set names gbk;
set names gbk;(这个命令是用在cmd窗口中)用来通知数据库服务器, 当前cmd窗口发送给服务器的数据的GBK的,那么服务器就会按照GBK编码来接收 cmd窗口发送过来的数据, 再将GBK的数据转换成utf8编码的数据存入数据库中!
这个命令只能用在cmd窗口! 而且是每次新开一个cmd窗口都需要重新设置一次!
像Navicat/SQLYog等工具不需要设置该命令,因为这些工具底层已经设置过编码了!
SQL语言可以操作的有:
1)查看库、创建库、删除库、修改库（了解）
查看已进入的库 select database();
1.查看库:show databases
2.创建库:create database 库名 charset utf8;
3.删除库:drop database 库名
2)创建表、删除表、修改表(扩展内容中)、查看表------------(一个类)
操作表首先等进入一个数据库才能看表内容
:1.首先得进入指定的库: use 库名;
1.查看表 2.show tables;
2.创建表:create table 表名(列名 数据类型,列名 数据类型,列名 数据类型 ....);
3.删除表: drop table 表名;
4.查看表结构: desc 表名
3)新增表记录、删除表记录、修改表记录、查询表记录(数据)-------(一个对象)
1.查询表记录(数据):
1)select * from 表名; ----- *是通配符,查询所有的列
2)select 列名,列名,列名 from 表明; ----- 查询指定的列(只会显示查询的列)
2.增加记录(在已存在的表格中插入数据):
1) insert into 表名(列名1,列名2,列名3...) value(值1,值2,值3...); -----插入单个
2) insert into 表名 values(),(),(); -----插入多个
3.修改记录: update 表名 set 列=值,列=值,列=值...[where子句];
4.删除表记录:delete from 表名 [where子句];
特殊的一些指令
exists :存在(判断是否存在,存在才显示)
if exists 如果存在
if not exists 如果不存在
select(查询表格记录是用到的一些指令)
1)distinct :用于去除重复记录,只保留一行!
eg: select distinct dept,job from emp;
2)ifnull
在查询时将null值看做0来处理(这种方式对表中的数据不产生任何影响)
-- ifnull(列名, 值)函数: 判断指定的列是否包含null值，如果有null值，用第二个值替换null值
ifnull(bonus, 0) -- 对bonus列中的null进行处理,将null值用第二个参数0进行替换
3)as ：为表头起别名(外号)(另外as可以省略-->简写版) 简写版:汉字----不带单引号
专业版: select name as '姓名',sal+ifnull(bonus,0) as '总薪资' from emp where sal+ifnull(bonus,0) > 3500;
简写版: select name 姓名,sal+ifnull(bonus,0) 总薪资 from emp where sal+ifnull(bonus,0) > 3500;
where
提示：where子句用于对记录进行筛选过滤，保留符合条件的记录，将不符合条件的记录剔除。
1)and ==> between 值1 and 值2(不建议用)
2)or ==> in(值1,值2,值3)
3) ! 取反 ===>not
4)处理null值 ----->用is
select * from emp where dept=null; ×错误的写法
select * from emp where dept is null;
5)模糊查询(like)[搭配where]
eg:查询emp表中姓名中包含"涛"字的员工，显示员工姓名。
select name from emp where name like '%涛%';
6)获取日期的年月日时分秒某一个返回值----[搭配where]
year('2020-8-10'): 返回日期中的年份, 2020
month('2020-8-10'): 返回日期中的月份, 8
day('2020-8-10'): 返回日期中的天数, 10
hour('2020-8-10 12:34:56'): 返回时间中的小时, 12
minute('2020-8-10 12:34:56'): 返回时间中的分钟数, 34
second('2020-8-10 12:34:56'): 返回时间中的秒值, 56
eg:查询emp表中本月过生日的所有员工
-- 首先通过month函数从当前日期中获取本月是几月: month( now() )
-- 再通过month函数从员工的birthday中获取出生月份: month( birthday )
select * from emp where month( now() ) = month( birthday );
7)多表查询--连接查询
select * from dept,emp where dept_id=dept.id
having -- having子句可以使用多行函数 和 列别名 以及 表别名!
eg: 列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资
求出有哪些职位的最低薪资是大于1500的
select job, min(sal) from emp group by job where min(sal)>1500; -- 错误写法!
1)where应在放在from子句后, group by子句前
2)where中不能使用多行函数(列别名也不能用在where中)
3)where是在分组之前之前,先过滤掉一些记录,再基于剩余的记录进行分组,
而本地是先分组,再过滤,所以不能使用where,应该用having
*/
select job, min(sal) from emp group by job having min(sal)>1500;
对列进行查询
1)模糊查询(like) [搭配where]
%:是通配符,可以表示0个或者任意个字符
_:是通配符,只能表示1个字符
eg:查询emp表中姓名中包含"涛"字的员工，显示员工姓名。
select name from emp where name like '%涛%';
2)多行函数查询(单列查询)
易错: ------->不能拿这个函数直接使用得, 先select查看之后,才能使用
错例: select name,sal from emp having sal>avg(sal); × 错
select name,sal from emp having sal>(select avg(sal) from emp); √
注意: (多行函数会默认过滤null值,即不统计null值)
常见的多行函数有:
count(列名 | *):
count(列名): 表示统计当前列的值有多少个(不统计null值)
count(*): 以行为单位,统计查询结果中有多少行记录
max(只能是列名): 表示统计当前这一列中所有值中的最大值
min(只能是列名): 表示统计当前这一列中所有值中的最小值
sum(只能是列名): 表示统计当前这一列中所有值的和(也就是说会将这一列中所有的值加在一起返回)
avg(只能是列名): 表示统计当前这一列中所有值的平均值(这一列中所有值的和 / 不是null值的个数 )
eg:求emp表中的最高薪资
select max(sal) from emp; select min(sal) from emp;
3)分组查询 group by 列(需要指定的列进行分组查询)
select 列 | * from 表名 [where子句] group by 列;
eg:对emp表按照职位进行分组，并统计每个职位的人数，显示职位和对应人数
select id,name,job from emp group by job;
4)排序查询 asc(升序)|desc(降序)
select 列名 from 表名 [where子句] [group by 列] order by 列名 [asc|desc]; -- 默认是asc，就是升序
select name, sal from emp order by sal; -- 默认是asc，就是升序
select name, sal from emp order by sal asc; -- 默认是asc，就是升序
ASC(默认)升序，即从低到高；DESC 降序，即从高到低。
eg: 按照奖金降序排序,如果奖金相同,再按照薪资降序排序
select name, bonus, sal from emp order by bonus desc, sal desc;
5)分页查询
查询公式为：limit (页码-1)*每页显示记录数, 每页显示记录数
eg: 1.查询emp表中的记录,每页3条,查询第1页
select * from emp limit 0, 3;
2.根据薪资降序排序, 每页显示3条, 查询第一页就是薪资最高的前3名
select * from emp order by sal desc limit 0,3;
6)其他函数
(1)
curdate() -- 获取当前日期: 年月日
curtime() -- 获取当前时间: 时分秒
sysdate()/now() -- 获取当前日期+时间, 年月日 时分秒
eg:获取当前时间:
select now();
(2)----[搭配where]
year('2020-8-10'): 返回日期中的年份, 2020
month('2020-8-10'): 返回日期中的月份, 8
day('2020-8-10'): 返回日期中的天数, 10
hour('2020-8-10 12:34:56'): 返回时间中的小时, 12
minute('2020-8-10 12:34:56'): 返回时间中的分钟数, 34
second('2020-8-10 12:34:56'): 返回时间中的秒值, 56
eg:查询emp表中所有【在1993和1995年之间出生】的员工，显示姓名、出生日期。
将birthday中的年份用year函数提取出来,再和1993以及1995进行比较
select name, birthday from emp where year(birthday) between 1993 and 1995;
7)字符拼接
concat(s1,s2,...sn): 将 s1、s2、...sn 拼接在一起返回
eg:select concat('王海涛', '1995-03-25', 2450);
concat_ws(x,s1,s2,...sn): 将 s1、s2、...sn 拼接在一起,并且每两个拼接时会通过x作为分隔符进行拼接,再返回
eg:select concat_ws(',' ,'王海涛', '1995-03-25', 2450);
数据类型
1)数值类型
tinyint、smallint、int、bigint、float、double、decimal等
其中较为常用的就是 int、double
2)字符串类型
char类型: 定长字符串, char(n), n的范围是: 0~255个字符
char类型之所以叫做定长字符串,是因为一旦确定了n的最大字符数,不管存的数据是多少,该数据占用的空间就是n个字符。
例如：name char(10), 存入'张三丰', 存入了3个字符,剩余的空间会用空格补全.
因此char类型可能会浪费空间!
char类型相比varchar类型速度要快一些,因为char类型只需要判断一个数据是否能存入该列中,而不需要将剩余的空间留给别的数据使用!
varchar类型: 变长字符串, varchar(n), n的范围是: 0~?个字符
varchar类型之所以叫变长字符串,是因为n只是限制该列中最多能存的字符数, 如果你实际存的数据量小于n,剩余的空间还可以留给别的数据使用。
例如：name char(10), 存入'张三丰', 存入了3个字符,剩余的7个空间会留给别的数据使用!
因此varchar类型不会浪费空间!
所以varchar类型适合存储长度不固定的数据(长度固定的数据我们会使用char类型)
varchar类型最大能存的数据量是 0~65535个[字节]
3)日期类型
date: 日期类型, 格式是: 年月日
time: 时间类型, 格式是: 时分秒
datetime: 日期+时间,格式是: 年月日 时分秒
timestamp: 时间戳, 格式和datetime相同, 也是: 年月日 时分秒, 和datetime不同的是:
1) 范围上: datetime范围是: 1000~9999(年份)
timestamp范围是: 1970到2038年
2) 实际存的数据: datetime实际存的就是一个`年月日 时分秒`格式的日期+时间
而timestamp实际存储的是这个从1970年1月1日到这个日期+时间的时间毫秒值
create_time timestamp, 2018-2-3 14:45:56, 实际存储的是 1970年1月1日到2018-2-3 14:45:56时间的时间毫秒值
3) 在使用上: timestamp可以设置自动获取当前时间作为值插入到表中, 而datetime不可以.
2018-2-3 14:45:56
列约束
1)主键约束 (这个列的值必须是非空的且不能重复)
添加主键约束的格式:
create table stu(
id int primary key
);
2)主键的自增 auto_increment
如果id是主键并且是数值类型,为了方便维护,可以设置主键自增策略,设置方法:
create table stu(
id int primary key auto_increment,
);
在设置完主键自增之后,如果插入主键时没有给主键赋值,就会从1开始自增
如果给主键设置了值,则自增是从插入值的最大值开始的
eg: 先默认自增10次 然后插入1000 然后在插入50 再下一次不是:51,而是 1001(始终是插入的最大值+1)
3) 非空约束 -->这个列的值就不能为空(null), 但可以重复
添加非空约束的格式:
create table stu(
gender varchar(10) not null,
);
4)唯一约束 --> 这个列的值就不能重复, 但可以为空(null)
唯一约束可以好几个值都为:null,因为null != null (null与null不相等)
唯一约束的格式:
create table stu(
...
email varchar(50) unique,
...
);
5)主键约束 和 (非空+唯一约束) 有什么区别:
1.主键约束 和 非空+唯一 特点是相同的, 都是不能为空且不能重复
2.主键约束除了非空且不能重复之外, 还可以表示唯一一行表记录, 即作为表记录的唯一标识。
6)外键约束
增强数据库中数据的完整性和一致性,建立外键约束,数据库会自动化分析数据,不能随性删除[有关联]的表记录
7)表关系 (一般讲一的这一列设置为主键)
1.一对一
2.一对多
3.多对多
多表查询---->1),2)不能显示表中的null <----> 3),4)能显示表中的null
1)连接查询,2)内连接查询中------>如果表中某一列的值,有null,不会显示
1)连接查询 [where 判断语句]
笛卡尔查询:(错的,没有实际意义)
select * from id,emp,job,name; ×[错]
where
select * from dept,emp where dept_id=dept.id
2)内连接查询 inner join ... on
select 表名,表名 from where..... ==> select 表名 inner join 表名 from on.......
select * from dept inner join emp on dept_id=dept.id
3),4)左,右外连接查询,全外连接查询中------>能够显示表中的null列
3)左外连接查询 left join ... on
select 表名 inner join 表名 from on.......==> select 表名 left join 表名 from on.......
4)右外连接查询 right join ... on
select 表名 inner join 表名 from on.......==> select 表名 right join 表名 from on.......
5)全外连接查询 union
1.左外连接 union 右外连接;
select * from dept left join emp on dept_id=dept.id
union
select * from dept right join emp on dept_id=dept.id;
2.能够使用union和union all合并结果的查询语句,必须符合:
1)两条SQL语句查询的结果列数必须相同
2)两条SQL语句查询的结果列名必须相同(低版本mysql要求)
多表关联查询(>=两张表) -----重点--->两个表建立关系: A.B_id=B.id;
查询的列: select A表的列名,B表的列名
查询的表: from A表,B表;
筛选条件:where A.B_id=B.id and 另一个条件;
eg: 列出在'培优部'任职的员工，假定不知道'培优部'的部门编号，显示部门名称，员工名称。
查询的列: select dept.name,emp.name
查询的表: from dept,emp
筛选条件:where dept_id=dept.id and dept.name='培优部';
1)方式一:where
select dept.name,emp.name from dept,emp where dept_id=dept.id and dept.name='培优部';
2)方式二 :inner join ...on
select dept.name,emp.name from dept inner join emp on dept_id=dept.id and dept.name='培优部';
在已经建好的表中修改表(增删改:列名\类型\或者外加一些约束)
语法: alter table 表名 add增/drop删/modify改 + …;
1)修改表—新增列 -->add
1)新增列:
eg:往stu表中添加score列，double类型
alter table stu add score double;
2)添加外键
alter table emp add constraint fk_dept_id foreign key(dept_id) references dept(id);
2)修改表—修改列 -->modify
eg: 1.修改id列，将id设置为主键
alter table stu modify id int primary key;
2.修改id列，将id主键设置为自动增长
alter table stu modify id int auto_increment;
3.修改id列，将id设置为主键,并且设置为自动增长
alter table student modify id int primary key auto_increment;
3)修改表—删除列 --> drop
语法: alter table 表名 drop 列名
1.删除列:
eg:删除stu表中的score列
alter table stu drop score;
2.如果要删除主键和自增,先删除自增
alter table student modify id int;-----删除自增
alter table student drop primary key; -----删除主键
3.删除外键约束
语法:alter table 表名 drop foreign + … ;
eg: alter table emp drop foreign key emp_name; -----给外键起个名字 emp_name,删除这个名字即可
数据库的备份和恢复
1)备份数据库
1、备份单个数据库
语法:mysqldump -u用户名 -p密码 库名 > 备份文件的位置
eg: 对db40库中的数据(表,表记录)进行备份,备份到 d:/db40.sql 文件中
mysqldump -uroot -proot db40 > d:/db40.sql
2、备份多个数据库（比如备份两个数据库）
语法: mysqldump -u用户名 -p密码 --databases 库名1 库名2 ... > 备份文件的位置
eg:对db20 和 db40 库中的数据进行备份,备份到 d:/db2040.sql 文件中
mysqldump -uroot -proot --databases db20 db40 > d:/db2040.sql
备份所有数据库:
mysqldump -uroot -proot -A > d:/dball.sql
2)恢复数据库
1、恢复数据库方式一(单个数据库)：
语法:mysql -u用户名 -p密码 库名 < 备份文件的位置
eg: 将 d:/db40.sql 文件中的数据恢复到 db60 库中
1) 先在cmd窗口中(已登录的状态下), 先创建db60库
create database db60 charset utf8;
2) 再回到cmd窗口中(未登录的状态下), 执行下面恢复的命令
mysql -uroot -proot db60 < d:/db40.sql
2、恢复数据库方式二(多个数据库)
语法:source sql文件的位置
eg:将 d:/db40.sql 文件中的数据恢复到 db80 库中
1) 先创建db80库, 并进入到db80库
create database db80 charset utf8;
use db80;
2) 再通过source命令执行 d:/db40.sql 文件中的sql语句
source d:/db40.sql
914
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
