MySQL初识阶段二
纯属个人自总结,若有错误,还请各位大佬提出宝贵建议
表数据增删改查
如果是字符类型(char,varchar)或日期类型,使用单引号’
如果是是数值类型,直接写数字
插入一条数据
格式:
insert into 表名(列名1, 列名2…列名n) values (值1,值2…值n);
如:
insert into t_student(t_id,t_name,t_age,t_sex,t_birthday)
values('01','小明',20,'M','1995-01-01');
如果表中所有列都要插入数据,可以不用在表名后面列出所有的字段,直接写值,但是顺序一定不能错。这种方法不规范,不要用。
insert into t_student values('02','lisi',20,'F','1996-01-01') ;
注意事项
- 列名的顺序和表创建时列的顺序必须相同。
- 值的顺序和列名的顺序必须相同,数据类型也必须相符。值的顺序和列名的顺序必须相同,数据类型也必须相符。
- 如果要插入空值,可以以null代替。如果要插入空值,可以以null代替。
- 如果字段列表没有写全,未设置值的列默认为null如果字段列表没有写全,未设置值的列默认为null
Mysql特有的批量插入(测试时比较有用)
insert into t_student(t_id,t_name,t_age,t_sex,t_birthday)
values ('03','teacherchen',20,'M','1995-01-01'), ('04','zhaoting',20,'F','1995-01-01');
查询表中所有数据
select * from t_student;
select t_name,t_id,t_age,t_birthday from t_student;
使用where和> < =比较运算符控制查询结果,下面会重点讲
修改数据
格式:
update 表名 set 列=值,列=值…
如:
--修改性别都为男
update t_student set t_sex = 'M';
--修改性别都为女 同时将年龄都改成21
update t_student set t_sex = 'F', t_age = 21;
使用where和> < =比较运算符控制修改结果
--工资sal大于3000的工资增加500
update t_student set t_sal=t_sal+500 where t_sal>3000;
--将年龄大于23的学生的工资增加300
update t_student set t_sal=t_sal+300 where t_age > 23;
--将学号为1,11,21,31,41学生,年龄增长一岁,工资增长25%
update t_student set t_age=t_age+1, t_sal=t_sal*1.25 where t_id in (1, 11, 21, 31, 41);
删除表中所有数据
delete from 表名;
注意:仅仅是删除数据,表结构存在。
删除t_student表中的所有数据另外一种方法
truncate table t_student ;
delete和truncate 区别
- delete删除的数据可以恢复。写日志,dba可以根据日志恢复数据。
- truncate删除数据不能恢复。直接删,不记录日志。truncate删除数据不能恢复。直接删,不记录日志。
- delete删除慢,truncate删除快delete删除慢,truncate删除快
- delete可以有条件的删除,truncate默认所有的表内容都删除delete可以有条件的删除,truncate默认所有的表内容都删除
- 工作中使用delete,truncate在测试时可以用工作中使用delete,truncate在测试时可以用
根据条件删除数据
delete from t_student where t_name = '小明';
比较运算符
=、!=/<>、>、>=、<、<=
--删除姓名为xiaoMing学生信息
delete from t_student where name='xiaoMing';
--删除性别不为F的所有学生信息
delete from t_student where t_sex != 'F';
--删除年龄大于25的所有学生信息
delete from t_student where t_age > 25;
null
--删除电话为null的学生信息
delete from t_student where tel = null;
delete from t_student where tel is null;
and or
and连接两个条件,表示两个条件必须同时满足,结果才满足。
or 连接两个条件,表示其中任意一个条件成立,结果就成立。
--删除性别为M且年龄在25岁以上的所有学生信息
delete from t_student where t_sex='M' and t_age > 25;
--删除分数在80到90之间所有学生的信息
delete from t_student where t_score >= 80 and t_score <= 90;
--删除性别为M或者分数小于60分所有学生的信息
delete from t_student where t_sex='M' or t_score < 60;
--删除学号为1, 2, 3的学生信息,t_id此处是int类型
delete from t_student where t_id = 1 or t_id = 2 or t_id = 3;
between and
--删除分数在80到90之间所有学生的信息
delete from t_student where score between 80 and 90;
delete from t_student where score >= 80 and score <= 90;
not
--删除分数不在80到90之间所有学生的信息
delete from t_student where score not between 80 and 90;
not in
--删除学号不为1, 2, 3的所有学生信息
delete from t_student where sno not in (1, 2, 3);
--删除电话不为null的所有学生信息
delete from t_student where tel is not null;
用户操作
创建一个用户,并指定密码
格式:
create user 用户名@IP地址 identified by '密码';
如:
--创建一个用户,用户名为ming,密码为123456
create user [email protected]'%' identified by '123456';
为用户赋予操作权限
格式:
grant create,alter, drop, insert, update, delete, select on 数据库名字.表名 to 用户名@IP地址;
如:
--赋予ming用户 create, alter, drop, insert, update, delete权限在njwangbo中的t_studnet表上
grant create,alter, drop, insert, update, delete on njwangbo.t_studnet to [email protected]'%';
回收权限
格式:
revoke 权限 on 数据库.*|表 from 用户名@IP地址;
如:
--回收ming用户在t_studnet表上的select权限
revoke select on njwangbo.t_studnet from [email protected]'%';
--回收ming在njwangbo数据库所有数据对象上的所有权限
revoke all on *.* from [email protected]'%';
查看用户权限
show grants for 用户名@IP地址
删除某个用户
drop user 用户名@IP地址;
修改密码
使用create user创建用户时,相当于在mysql数据库的user表中插入一行数据。如果修改某个用户的信息(权限,密码),则可以直接修改user表中的列即可。
--修改某用户的密码
use mysql;
update user set password=password('root') where user = 'root';
--刷新mysql的系统权限相关表
flush privileges;
mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表,否则会出现拒绝访问
基本查询语句
--查询表中所有语句
语法:select * from
--查询表中特定的语句
语法:select * from 表名 where 列名=列名值;
--查询表中特定的列
语法:select 列名1,列名2…. from 表名;
--给查询出来的列起别名
语法:select 列名1 as 列1的别名 , 列名2 as 列2的别名 …. from 表名;
给查询出来的列起别名,并不是做修改操作;
模糊查询
--按照name模糊查询
select * from 表名 where 列名 like’%列名%’;
--查询以某字段开头的数据
select * from 表名 where 列名 like ‘%列名’;
--精确查询 下划线
select * from 表名 where 列名 like ‘_列名’;
一个下划线表示一个字符,两个下划线表示两个字符
函数
连接字符串 concat(,)
select concat('hello','world');
插入字符串 insert(str,pos,len,newStr) 下标从1开始
select insert('helloworld',1,2,'abcd');
转小写 lower(str)
select lower('AbCd');
转大写upper(str)
select lower('AbCd');
计算长度length(str)
select length('hello')
去前后空格trim(str)
select length(trim(' aa '))
全部替换replace(一段字符串,把这里的值,替换成这里的值)
select replace('xx And xx','xx','pp');
取子串substring(str,pos,len)
select substring('helloworld',2,4);
数据函数
向上取整 ceil(8.1);
向下取整 floor(8.9);
取余数 mod(10,3);
四舍五入 round(10.572,1);
截断 truncate(7.123456,2);
日期函数
(mysql专用)
当前日期 年月日 curdate()
当前的时间 时分秒 curtime()
当前时间日期 年月日时分秒 now()
流程函数
(mysql专用)
if(表达式,结果1,结果2)
表达式成立,取结果1;不成立,取结果2。
ifnull(结果1,结果2)
如果结果1为null,取结果2;不为null,取结果1自己。
系统函数
返回当前正在使用的数据库select database();
聚集函数
max: 最大值
语法:
select max(列名) from 表名;
min:最小值
语法:
select min(列名) from 表名;
sum:求和
语法:
select sum(列名) from 表名;
avg: 求平均数
语法:
select avg(列名) from 表名;
count:求当前表中有多少条数据,求总数
select count(列名) from t_user
distinct:去掉重复的数据
--去掉name相同的数据
select distinct name from user;
-- 去掉name和id都相同的数据
select distinct name,id from user;
注意:select id,distinct name from user,这样mysql会报错,因为distinct必须放在要查询字段的开头。
查询-单表查询
准备数据
create table t_student (
t_id char(2),
t_name varchar(20),
t_age int,
t_sex char(2),
t_birthday date,
t_salary double,
t_award double
);
insert into t_student(t_id,t_name,t_age,t_sex,t_birthday,t_salary,t_award)
values('03','ming',24,'M','1991-01-01',8000,null),
('04','hong',23,'F','1992-02-01',6000,4000),
('07','gang',22,'M','1993-03-01',10000,null),
('07','gang',28,'M','1993-06-06',60000,6600),
('11','LiGang',21,'F','1994-04-01',120000,8000);
查询表中所有字段
select * from 表名; *表示表中所有的列,且按默认列顺序排列。
select * from t_student;
查询部分字段,列顺序可以自定义
select 列名1, 列名2…列名n from 表名;
select t_id,t_name,t_age,t_sex,t_birthday from t_student;
mysql的运算符+ - * /
--查看每个学生工资增长1000以后的信息(只要显示编号 + 姓名 + 工资)
select t_id,t_name, t_salary + 1000 from t_student;(结果是临时表,保存在内存中)
给t_salary + 1000 起别名
select t_id,t_name, t_salary + 1000 as money from t_student;
select t_id,t_name, (t_salary + 1000) as money from t_student;
姓名是ming且t_salary > 6000
select * from t_student where t_name='ming' and t_salary > 6000;
查找工资t_salary在3500到6000之间所有学生的信息
select * from t_student where t_salary >=3500 and t_salary <=6000;
select * from t_student where t_salary between 3500 and 6000;//推荐使用,连续区间
查询1996年出生的学生信息
select * from t_student where t_birthday between '1996-01-01' and '1996-12-31';
查找学生编号为 03 04 07 的信息
select * from t_student where t_id ='03'or t_id='04' or t_id = '07';
select * from t_student where t_id in('03','04','07');//推荐使用,离散的区间
查询t_award为null的学生信息
select * from t_student where t_award is null;
查询t_award不为null的学生信息
select * from t_student where t_award is not null;
查看每个人年龄,如果null,用0表示
select t_name, ifnull(t_age, 0) from t_student;
任何值与null做算术运算时结果都为null,想办法将null转换成0再计算。
select t_name, 12*sal + ifnull(award, 0) from t_student;
查看每个学生的年薪
select t_id,t_name, t_salary*12 as money from t_student;
select t_id,t_name, (t_salary+t_award)* 12 as money from t_student;//有问题
select t_id,t_name, (t_salary+ifnull(t_award,0))* 12 as money from t_student;
查询年薪超过10万的同学
方法一:子查询,在一个查询语句中嵌套另外一个查询,执行过程是先执行里面的查询,再执行外面的查询。
select * from
( select
t_id,t_name, (t_salary+ifnull(t_award,0))* 12 as money
from t_student
) as ts
where ts.money >=100000;
方法二:having关键字:只能出现在select语句中,对前面的结果进行过滤。
select
t_id,t_name, (t_salary+ifnull(t_award,0))* 12 as money
from t_student
having money >=100000;
月工资前显示货币符号
select
t_id,t_name, concat('$',t_salary+ifnull(t_award,0) ) as money
from t_student;
合并字符串列输出
select t_name+t_sex from t_student;
上面字符串相加,显示为0,表示字符串不能相加。
字符串连接用concat方法.concat(列1|字符串,列2|字符串…);
select concat(t_name, t_sex) from t_student;
查询所有的姓名且去除重复的名字
select distinct(t_name) from t_student;
查询总共有多少名同学
select count(0) as cnt from t_student;
select count(t_id) as cnt from t_student;
like子句:查找姓名中包含Gang的所有学生信息
select * from t_student where t_name like '%Gang%';
显示所有的学生信息,结果按照t_salary 降序排列
select * from t_student order by t_salary desc
显示所有的学生信息,结果按照t_salary 升序排列
select * from t_student order by t_salary asc
select * from t_student order by t_salary;(默认是升序)
显示所有的学生信息,结果按照t_salary 升序排列 ,如果t_salary相同,则按照t_name降序排列
select * from t_student order by t_salary asc,t_name desc;
查询基本工资最大值
select max(t_salary) as max from t_student;
查询基本工资最小值
select min(t_salary) as min from t_student;
查询基本工资总和
select sum(t_salary) as sum from t_student;
查询平均工资
select sum(t_salary)/count(0) as avg from t_student;
select avg(t_salary) as avg from t_student//推荐
按照性别计算最大工资 男生:最大工资 女生:最大工资
select max(t_salary),t_sex from t_student group by t_sex
分组查询
group by 表示分组的意思
语法:
select coun(函数自选 (列名) , 列名2 from 表名 group by 需要分组的列名;
having //必须只用在分组中,也可以使用在分组外(少用)
where //只能分组外使用,不能使用在分组中
order by //排序 可以使用在分组中和分组外
desc 从大到小 , 降序排序
asc 从小到大,升序
如果按照某列或多列进行分组,则会按照表中指定的分组列,列值相同的看作一组信息。在分组中,如果使用聚合函数
则每个函数不是对整个表进行统计分析了,而是对每个组进行统计分析。
分组之后,所有的操作是对一组进行操作的。不是对一行进行操作。一组包含1行或多行数据。
在分组中,只有分组的列,或与分组列结构相同的列才能在SELECT之后出现。
select 查询语句语法
select 列1,列2,...
from 表
where 条件
group by 分组条件
having 分组过滤条件
order by 列 asc[默认] desc[降序]
查询-多表连接查询
内连接 学生表和班级表中都有数据时才显示
select
c.t_name,s.t_name
from t_class c
inner join t_student s
on c.t_id = s.t_classid;
左连接:获取左表所有记录,即使右表没有对应匹配的记录。
select
c.t_name,s.t_name
from t_class c
left join t_student s
on c.t_id = s.t_classid;
右连接:获取右表所有记录,即使左表没有对应匹配的记录。
select
c.t_name,s.t_name
from t_class c
right join t_student s
on c.t_id = s.t_classid;
子查询
子查询:将sql语句查询出来的结果,在作为一次查询条件;
In :包括
Not in:不包括
>=、<=、!=
= :大于等于
<= :小于等于
!= :不等于
exists --》boolean ——》true真 / false假
如果子查询中结果成立则执行外部的sql语句;
相反查询中结果不成立则不会执行外部的sql语句;
语法:
select * from 表名 where exists (sql语句)---》true或者false;