目录
二、MySQL02操作表中的数据
1、表数据的增删改查
-- 创建数据库时,要选择编码格式为utf-8,否则有中文时,就会报错
-- 创建班级表
create table t_class(
c_id int primary key auto_increment,
c_name varchar(100)
);
-- 创建学生表
create table tab_student(
id int primary key auto_increment,
stuname varchar(100),
sex varchar(2),
begintime date,
gradutetime date,
idcard varchar(18),
cid int,
CONSTRAINT fk_classid FOREIGN key(cid)
REFERENCES t_class(c_id)
);
-- 添加数据 t_class
-- 1.指定列名做新增——有些列名不写
-- a. 主键自增列没有写 直接触发自增
-- b. 普通列如果没有写,默认直接入值为null
-- c. 如果是默认值列,触发默认值的入值
-- d. 如果新增的数据不符合约束,不能。
-- 2. 如果不指定列名代表默认所有列
-- 3. into 可以省略
-- 语法: insert into 表名(字段名字1,字段名字2...) values(值1,值2,...)
insert into t_class(c_id,c_name) values(null,'qy160');
-- 简写 insert into 表名 values (第一列的值,第二列的值...) 这些值要和上面的字段一一对应
insert into t_class values(null,'qy161');
-- 容易出现的错误:当不添加引号(就是不满足字段的约束条件时)
-- [Err] 1054 - Unknown column 'qy161' in 'field list'
insert into t_class values(null,qy161);
-- 容易出现的错误 :表名错误或者要操作的表不存在时
-- [Err] 1146 - Table 'test_exer.t_class1' doesn't exist
insert into t_class1 values(null,'qy161');
-- 先添加163运行,即使164是在163上面再写,再运行也是在163的下面
insert into t_class values(null,'qy164');
-- 语法中的into可以省略
insert t_class values(null,'qy163');
-- 批量添加,一次添加多条数据
-- 语法:insert into 表名(字段1,字段2...) values(值1,值2..)(值1,值2...)...
insert into t_class(c_id,c_name) values(null,'qy165'),(null,'qy166');
-- 添加时,可以不用添加自增主键的字段
insert t_class(c_name) values('qy168');
-- 添加一列age字段
alter table tab_student add age double default '20.0';
-- 添加一段address字段
alter table tab_student add address varchar(200);
-- 查询
-- 语法: select 列名1,列名2,... from 表名;
select * from t_class;
-- 虽然查询时,可以用*查询,但是不推荐,因为效率比较低
-- 查询时,可以给要查询的数据命一个别名,用到as关键字,as可以省略不写
select c_id as 学号 from t_class;
-- 创建一张新表,复制现在表的数据和结构,但不复制主键外键关系
-- 语法:create table 新表名 select * from 旧表名;
create table t_class_bak select * from t_class;
-- 创建一张表 ,复制当前表的结构,不复制数据和主键外键关系
-- 语法:create table 新表名 select * from 旧表名 where 1=2;
create table t_class_bak_1 select * from t_class where 1=2;
-- 要把一张表的数据全部复制到另外一张表:前提是 列名和数据类型都一致
-- 语法: insert into 得到数据的表名 select * from 被复制数据的表名(类似于备份数据)
insert into t_class_bak_1 select * from t_class;
-- 添加一条数据
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,cid,age,address) values(null,'雷文林','2','2022-1-1','2022-12-30','412723xxxxxxxx0813','2','23','郑州市');
-- id主键 可以写成'2',底层会自动判别引号中的字符是不是一个数字,所以这样也可以写
-- 里面的字符可以用双引号,也可以用单引号。
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,cid,age,address) values('2','雷文林22',"男",'2022-1-1','2022-12-30','412723xxxxxxxx0813','2','35','周口市');
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,cid,age,address) values(null,'雷文林33',"女",'2022-1-1','2022-12-30','412723xxxxxxxx0813','2','15','郑州市');
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,cid,age,address) values(null,'雷文林44',"女",'2022-1-1','2022-12-30','412723xxxxxxxx0813','2','30','上海市');
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,cid,age,address) values(null,'雷文林55',"男",'2022-1-1','2022-12-30','412723xxxxxxxx0813','2','60','北京市');
-- 修改
-- 语法:update 表名 set 列名1=值1,列名2=值2 (条件)where 列名=值
-- 修改id为3的对象 的 c_name为测试班
update t_class_bak_1 set c_name='测试班' where c_id=3;
-- 如果后面不加where条件判断,默认是选中该字段下全部的项
update t_class_bak_1 set c_name='测试1班';
select * from t_class_bak_1;-- 查看效果
-- 修改学生表中 id为1的姓名修改为雷文林 性别修改为男
update tab_student set stuname='雷文林',sex='男' where id=1;
select * from tab_student;-- 查看效果
-- 删除
-- delete from 表名 :删除表中所有的数据,速度较慢,会保留日志,可以恢复,能加where条件,删除后自增占位还在,
-- truncate table 表名 : 删除表中的所有数据,速度较快,不会保留日志,不可恢复,不能加where条件,相当于删除重建,自增占位也删除
-- drop table 表名 : 删除整张表
-- 删除该表中所有的数据
delete from t_class_bak_1;
-- 将t_class中的数据复制到t_class_bak_1中
insert into t_class_bak_1 select * from t_class;
-- 删除表中id为1的数据
-- 语法:delete from 表名 条件 列名=值1;
delete from t_class_bak_1 where c_id=1;
select * from t_class_bak_1;-- 查询表看结果
-- 删除表中所有数据,并且不留日志
truncate table t_class_bak_1;
2、表达式及运算符
表达式是指使用运算符将同类型的数据(如常量、变量、函数等)按一定的规则连接起来的、具有特定意义的语句。
MySQL中表达式包括两种:这两种表达式结果只能为TRUE或FALSE
1)条件表达式
2)逻辑表达式
1)比较运算符
运算符 | 含义 |
---|---|
= | 等于 =判断和赋值用的是一个 |
> | 大于 |
< | 小于 |
>= | 大于或等于 |
<= | 小于或等于 |
<> | 不等 |
!= | 不等 |
2)逻辑运算符
运算符 | 含义 |
---|---|
and | 和 |
or | 或者 |
not | 不 |
3、简单查询
查询都是要根据条件进行查询的,需要用到上面所讲的运算符
/*
select 列名1,列名2...列名n(所有列用*)--指定列展示
from 表名
where 条件----过滤行:符合条件的数据展示不符合调的数据不展示。检索
order by 排序列1,排序列2,排序列n -- 第一列数据相同 触发列2 依次类推
limit 从第几行开始查,查询几行 -- limit限制行数
*/
-- exercise查询
-- 语法: select * from 表名 字段名字1,字段名字2... 条件
select * from tab_student;
-- 要求查询年龄在20岁以上的学生信息 age >20
select * from tab_student where age>20;
-- 查询出年龄大于20岁的女生信息 age=20 sex='女'
select * from tab_student where age>20 and sex='女';
-- 查询出家不在郑州的或者年龄不到20岁的学生信息
select * from tab_student where address!='郑州市' or age<20;
-- 排序 关键字 order by asc:升序排列 desc:降序排列
-- 语法:select from 表名 where order by 列名 asc/desc
select * from tab_student where sex!='女' order by id desc;
-- 注:有些字段必须是相应的升降序,如果年龄是降序,那么出生年份一定是升序
-- 分页 关键字:limit
-- 语法: select * from 表名 limit 参数一,参数二;
-- 参数一:代表数据从哪条索引开始 参数二:代表每页显示多少条数据
-- 公式:(当前页-1)*每页显示的条数!!!
select * from tab_student limit 0,3;-- (1-1)*3
select * from tab_student limit 3,3;-- (2-1)*3
-- 显示所有的学生的名字 distinct:区别的,清楚的 (adj)
-- 语法:select distinct 列名 from 表名;
select distinct stuname from tab_student;
4、内置函数
4.1 函数说明
函数名 | 示例 | 函数功能 |
---|---|---|
Concat | concat(s1,s2....sn) concat(‘hello’,‘AAA’)返回 helloAAA | 把传入的参数连接成一个字符串。 如果有值为null,则最终返回null。 忽略null值则使用concat_ws函数。 |
Length | length('hello world')结果为11 | 返回字符串的长度 |
Upper | upper('abcd')返回为ABCD | 将字符串转为大写 |
Ltrim | Ltrim(‘ abc’)返回为’abc’ | 去除字符串左边的空格 |
Rtrim | Rtrim(‘abc ’)返回为’abc’ | 去除字符串右边的空格 |
Replace | Replace('abcccd','c','x') 返回为’abxxxd’ | 将abcccd中c替换为x |
Locate | Locate('a','helloaaa') 返回 6 | 返回子串 a 在字符串 helloaaa 第一个出现的位置,不存在则返回 0 |
Substring | substring('Johnson',5,3) 返回为‘son’ | 从第5个位置开始截取长度为3的字符串 |
Now | now()返回系统当前时间 | 返回系统当前时间 |
TIMESTAMPDIFF | 语法: TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 例: SELECT TIMESTAMPDIFF (MONTH,'2009-09-01','2009-10-01'); 返回 1 | 返回日期或日期时间表达式之间的整数差 unit可以是: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. |
DateAdd | DATE_ADD('1998-01-02', interval 1 YEAR) 返回 1999-01-02 | 想日期指定部分添加数字,其中:YEAR表示是年,month表示月,day表是日 |
Dayofweek | dayofweek('2008-08-08‘)返回 6 dayofweek:(1 = Sunday, 2 = Monday, ..., 7 = Saturday) | 返回一周中的位置 |
DateDiff | select datediff('2008-08-08', '2008-08-01'); 结果返回为 7 | 两个日期相减 date1 - date2,返回天数 |
Date | date('2008-09-10 07:15:30'); 返回 2008-09-10 | 以字符串形式返回某个日期部分。 |
Abs | Abs(-1)返回为”1” | 求绝对值 |
Ceiling | Ceiling(24.1)结果返回25 | 大于24.1的最小整数 |
Floor | Floor(24.1)结果返回为24 | 小于24.1的最大整数 |
Power | Power(2,3)结果返回为“8” | 计算2的3次方 |
Round | Round(68.32,1)结果返回为68.30 | 返回一个数字,舍入到指定的长度或精度 |
Sign | Sign(123)结果返回为1 | 返回数值的符号,正负零分别返回1, -1, 0 |
Sqrt | Sqrt(16)结果返回为“4” | 开平方 |
Convert | convert(1234,char(4)) 结果返回为”1234” | 数据类型转换函数,将1234数字类型转换为char类型 |
Length | length('加油中国')结果返回为12 | 返回任何数据类型的字节数 |
ifnull(expr,value) | select ifnull(city,'未知') from studentInfo | 如果表达式的值非空,则返回表达式的值,否则返回value |
select user,host from mysql.user 返回计算机的名字 | 返回当前用户登录的计算机名字 | |
SELECT Current_User 返回当前用户的登录名 | 返回当前用户的名字 |
-- 系统函数,mysql有很多,而且不同数据库的自带都不一样。
select length(classname) from class;
4.2 函数测试
-- 常用函数测试
-- CONCAT(str1,str2,...)拼接函数 mybatis中也会用到该函数
-- 如果其中有一个字段值为null,最后拼接的结果就是null。
-- 语法:select CONCAT(str1,str2,...) 别名(可以没有,也可以是中文) from 表名
-- 要求:输出 姓名是:xxx 年龄是: xxx 地址是:xxx
select * from tab_student;
select concat(stuname,',年龄是:',age,',地址是:',address) 介绍 from tab_student;
-- ifnull 函数
-- 语法:ifnull(字段名,'值') 如果参数1字段的值为null,则将值设置为参数2,;
select stuname,ifnull(address,'撒哈拉沙漠') 地址 from tab_student;
-- now():获取现在时间的函数
insert into tab_student(id,stuname,sex,begintime,gradutetime,idcard,cid,age,address) values(null,'雷文林22',"男",now(),'2022-12-30','412723xxxxxxxx0813','2','35','周口市');
select * from tab_student;
-- 也可以直接使用获取现在的时间
select now();
-- 通过方法也可以只获得当前的年份/月份/天数/小时/分钟/秒钟
select year(now());
-- 获取所有的学生的名字和实习天数(当前日期月份-入学时间月份)
select stuname,month(now())-month(begintime) 实习时间 from tab_student;
-- 返回日期或日期时间表达式之间的整数差 unit可以是:microsecond(毫秒)
-- 语法:TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
select timestampdiff(microsecond,'2022-08-30','2022-09-01');-- 172800000000
select timestampdiff(day,'2022-08-31','2022-09-01');-- 1
-- datediff函数 两者相差的天数
select stuname,datediff(now(), '2022-2-23')/7 实习周数 from tab_student;
-- floor 向下取整 小于27.1的最大整数
select stuname,floor(datediff(now(), '2022-2-23')/7) 实习周数 from tab_student;
-- round 向上取整 大于27.1的最小整数
select stuname,ceiling(datediff(now(), '2022-2-23')/7) 实习周数 from tab_student;
-- round 返回一个数字,舍入到指定的长度或精度 第一个参数是要处理的值 第二个参数是小数点后的位数
select stuname,round((datediff(now(), '2022-2-23')/7),2) 实习周数 from tab_student;
-- dayofweek函数 每周的第几天,周日默认为第一天
select dayofweek(now());-- 今天周四,所以是第五天
-- date_add()函数 使用interval关键字
select date_add('2022-2-28',interval 1 month);
-- subtring(目标字符串,开始字符,要截取的长度)
select substring('Johnson',5,3);
-- length 返回字符串的长度,空格也算一个字符
select length("hello world");
-- upper 将字符串转成大写
select upper("hello world");
-- ltrim 去除左边的所有空格
-- rtrim 去除右边的所有空格
select ltrim(" abc");
-- replace 替换字符
select replace('aaabbbccc','a','x');
-- locate 返回子字符串在父字符串中第一次出现的位置
select locate('a','helloaaa');-- 这里是从1开始数的 结果是6
-- abs :求绝对值
select abs(-1);
-- power :求次方
select power(2,3);-- 该计算是2的3次方
-- convert 数据类型转换函数 第一个参数是要处理的数据 第二个参数是要转换的类型及长度
select convert(1234,char(3)); -- 123 如果长度没有原数据长,直接截断
-- sqrt 开平方 可以填浮点型数据
select sqrt(15.00); -- 3.872983346207417
-- sign 返回数值的符号 -1代表负数,0代表0.1代表正数
select sign(-123);
-- 返回当前用户登录的计算机名字
-- 返回所有的用户名与计算机名字
select user,host from mysql.user;
-- 返回当前用户的名字
select Current_User;