Mysql基本操作

free
174 阅读6分钟

数据库操作

创建数据库

create database free;
create database free character set utf8;

使用数据库

use free;

删除数据库

drop database free;

修改数据库的定义信息

alter database free charset set utf8;

查看当前选择的数据库

select database();

显示所有数据库

show databases;

退出数据库

exit;

表操作

查看所有数据表

show tables;

常用参数

  1. NOT NULL 不能为空

  2. PRIMARY KEY 主键 数据唯一且不能重复 且不能为null

  3. auto_increment 自动增长

  4. comment 注释

  5. UNIQUE 唯一约束

  6. DEFAULT 默认值

创建表

create table free(
	`id` int(11) primary key auto_increment not null comment "主键id",
	`name` varchar(255) not null unique comment "名称",
	`create_time` int(11) not null comment "创建时间"
)engine=innodb default charset=utf8;

查看表字段信息

desc free;

查看表中细节

show create table free;

修改表名

rename table free to free2;

增加字段

alter table free add `delete_time` int(11) not null comment "删除时间";

修改字段

alter table free modify `delete_time` int(20) comment "删除时间2";

删除字段

alter table free drop `delete_time`;

修改字符集

alter table free character set utf8;

修改字段名

alter table free change `delete_time` `delete_time2` int(11);

删除表

drop table free;

添加数据

单条添加

insert into free (id,name,create_time,delete_time) values(null,'test',1111,2222);

多条添加

insert into free (id,name,create_time,delete_time) values(null,'test2',333,444),(null,'test3',555,666);

不写列名就必须把所有的值都带上

insert into free values(null,'sss',222,222),(null,'eeee',222,2222);

修改数据

修改所有字段的值

update free set name = 'test';

根据条件修改

update free set name = 'test2' where id = 1;

修改多个字段

update free set name='test3',delete_time=555 where id = 1;

删除数据

根据条件删除

delete from free where name = 'test';

删除所有数据

delete from free;#主键不重置
truncate table free;#主键重置

查询操作

AND  并且

#查询user表性别为0并且班级id为1的记录
select * from `user` where `sex` = 0 and `cid` = 1;

OR 或者

#查询user表性别为0或者班级id为1的记录
select * from `user` where `sex` = 0 or `cid` = 1;

IN 

#查询user表班级id为1,2,3的记录
select * from `user` where `cid` in(1,2,3);

NOT IN 

#查询user表班级id不是1,2,3的记录
select * from `user` where `cid` not in(1,2,3);

IS NULL 是否为空

#查询user表城市为空的记录
select * from `user` where `city` is null;

BETWEEN AND 在 ...之间

#查询user表创建时间在1595388727 - 1595388729之间
select * from `ms_dj_user` where `created_at` BETWEEN 1595388727 and 1595388729;

!= 不等于

#查询user表性别不为0
select * from `ms_dj_user` where sex != 0;

<> 取反

#查询user表性别不为0
select * from `ms_dj_user` where sex <> 0;

IS NOT NULL 不为空

#查询user表城市字段不为空的记录
select * from `user` where `city` is not null;

LIKE 模糊查询

#查询由5个字母构成的用户
select * from `user` where `username` like '_____';
#查询由5个字母构成,且第五个字母为y的学生记录
select * from `user` where `username` like '____y';
#查询姓名以t开头
select * from `user` where `username` like 't%';
#查询姓名第二个字母为i的记录
select * from `user` where `username` like '_i%';
#查询姓名包含a的字母的记录
select * from `user` where `username` like '%a%';

DISTINCT 去除重复

#去除重复名称
select distinct `username` from `user`;

ORDER BY 排序

#查询所有用户年龄升序由小到大
select * from `user` order by `age`;
select * from `user` order by `age` asc;
#查询所有用户年龄升序由大到小
select * from `user` order by `age` desc;
#查询用户,按年龄降序排序,如果年龄一致,按编号升序排序
select * from `user` order by `age` desc,`no` asc;

聚合函数

COUNT 统计

#查询用户表总记录数
select count(*) from `user`;
#查询用户表有邮箱的记录
select count(email) from `user`;
#查询用户表月薪大于2500的人数
select count(*) from `user` where `sal` > 2500;

AVG 平均值

#统计所有用户的平均工资
select avg(sal) from `user`;

MAX MIN 最大最小值

#查询最高和最低的工资
select max(sal),min(sal) from `user`;

SUM 总和

#所有用户的工资和
select sum(sal) from `user`;

GROUP BY 分组查询

#用户表根据编号分组
select * from `user` group by `no`;
#根据性别查看工资和
select `sex`,sum(sal) from `user` group by `sex`;
#根据性别以及工资小于1500的人数
select `sex`,count(*) from `user` where `sal` < 1500 group by `sex`;

HAVING 分组后过滤

where是分组前对数据过滤

having后可以使用聚合函数

where后不可以使用聚合函数

#根据性别以及总和大于9000
select `sex`,sum(sal) from `user` group by `sex` having sum(sal) > 9000;

LIMIT 分页

select * from `user` limit 0,5;#第一页 取5条数据
select * from `user` limit 5,5;#第二页 取5条数据

多表查询

UNION 去除重复记录  / UNION ALL  不去除重复记录

要求:被合并的两个结果集 列数 类型必须要一样

select * from `china` union select * from `usa`;#可以去除重复的记录
select * from `china` union all select * from `usa`;#不去除重复的记录

连表查询

#根据用户id查询详情表uid关联数据
select * from `user` u,`user_info` i where `u`.id = `i`.uid;
#如果指定字段会提升查询性能
select u.id,u.username,i.compay from `user` u,`user_info` i where `u`.id = `i`.uid;

内连接

#根据用户id查询详情表uid关联数据
select * from `user` u inner join `user_info` i on u.id = i.uid;
#指定字段提升查询性能
select u.id,u.username,i.company from `user` u inner join `user_info` i on u.id = i.uid;

外连接

左连接 以左表为主，如果右边查询的结果有的列没有数据 就显示为null

select u.username,u.email,i.company from `user` u left outer join `user_info` i on u.id = i.uid;

右连接 以右表为主 左表关联的数据没有数据时 就为null

select u.username,u.email,i.company from `user` u right outer join `user_info` i on u.id = i.uid;

自连接

CREATE TABLE emp(
	empno INT,
	ename VARCHAR(50),
	job VARCHAR(50),
	mgr INT,
	hiredate DATE,
	sal DECIMAL(7,2),
	comm DECIMAL(7,2),
	deptno INT 
);
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7902,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);

#查询7369员工编号 姓名 经理编号和经理姓名
SELECT e1.empno '员工编号',e1.ename '员工名称',e1.mgr '经理编号',e2.ename '经理名称'  
FROM emp e1,emp e2 WHERE e1.mgr = e2.empno AND e1.empno=7369;

子查询

在select 语句中嵌套一句 select

出现位置

     where后作为查询一条的一部分

     from后 当作表

  • 工资高于jones的员工

    select * from emp where sal > (select sal from emp where ename = 'jones');

  • 查询与scott同一部门的员工

    select * from emp where empno = (select empno from emp where ename = 'scott');

  • 查询工作和工资与martin相同的员工信息

    select job,sal from emp where (job,sal) in (select job,sal from emp where ename = 'martin');

avatar
文章
阅读
粉丝