--创建库
create database 库名;
--创建库时判断库是否存在,不存在则创建
create database if no exists 库名;
--查看所有数据库
show databases;
--使用指定数据库
use 库名;
--查看当前指定数据库包含的数据表
show tables;
--查看数据库的结构定义信息
show create database 库名;
--删除数据库
drop database 库名;
--修改数据库的字符集为utf8
alter database 库名 character set utf8;
--创建表
create table 表名(
字段1 类型1,
字段2 类型2,
字段3 类型3,
......
);
--查看表结构
desc 表名;
--查看创建表的SQL语句
show create table 表名;
--修改表名
alter table 表名 rename to 新的表名;
--添加一个新的字段
alter table 表名 add 字段;字符类型;
--修改字段名
alter table 表名 rename column 字段名 to 新的字段名;
--修改字段类型(注意类型修改前后数据是否兼容)
alter table 表名 modify column 字段名 新的字段类型;
--删除一个字段
alter table 表名 drop 字段名;
--删除表
drop table 表名;
--删除表时判断表是否存在,若存在则删除
drop table 表名 if exists 表名;
全字段插入数据(有两种方法,推荐第一种方法)
--有多少个字段,就要写多少个值,且是一一对应的
insert into 表名 values(值1,值2...值n);
--此方法要写出所有字段,并一一对应插入值
insert into 表名(字段1,字段2...字段n) values(值1,值2...值n);
部分字段插入数据
--部分字段插入数据,只写需要插入数据的字段名
insert into 表名(字段1,字段2...) values (值1,值2...);
--删除表中所有数据
delete from 表名;
--删除表中指定的数据
delete from 表名 where 字段 = 值;
--删除表中所有数据(先删除整张表,然后创建一张一样的空表,此方法更高效)
truncate table 表名;
--无限制条件的修改,会修改整张表
update 表名 set 字段 = 值;
--有限制条件的修改,只修改特定记录
update 表名 set 字段 = 值 where 条件(字段 = 值);
添加用户
create user '用户名'@'主机名' identified by '密码';
删除用户
drop user '用户名'@'主机名';
查询权限
show grants for '用户名'@'主机名';
授予权限
--语法
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
--授予faker用户所有权限,在任意数据库任意表上
grant all on *.* to 'faker'@'localhost';
撤销权限
--语法
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
--撤销faker用户对test数据库中city数据表的权限
revoke update on test.city from 'faker'@'localhost';
--查询表中所有数据
select * from 表名;
--查询users表中年龄在18~25岁之间的记录
--方式1 between..and..
select * from users where age between 18 and 25;
--方式2 &&
select * from users where age>=18 && age<=25;
--方式3 and
select * from users where age>=18 and age<=25;
单个条件(or / in)
--查询users表中年龄为18,20,25岁的记录
--方式1 or
select * from users where age=18 or age=20 or age=25;
--方式2 in
select * from users where age in (18,20,25);
多个条件(and)
--查询users表中年龄为23,性别为女,名字为小楠的记录
select * from users where age=23 and gender='女' and name='小楠';
--查询users表中序号不为空的记录
select * from users where id is not null;
--查询user表中序号为空的记录
select * form users where id is null;
_:单个任意字符
%:多个任意个字符
--查询users表中姓名第一个字为李的记录
select * from users where name = '李%';
--查询users表中姓名第二个字为李的记录
select * from users where name = '_李%';
--查询users表中姓名含有李字的记录
select * from users where name = '%李%';
--查询users表中姓名是两个字的记录
select * from users where name = '__';
--查询users表中所在城市不相同的记录
--select distinct 字段 from 表名;
select distinct city from users;
单个条件
--查询users表中记录,并以年龄升序排序
select * from users order by age;
--查询users表中记录,并以年龄降序排序
select * from users order by age desc;
多个条件
--查询users表中记录,并体育成绩降序,年龄降序
select * from users order by PE desc, age desc;
计算和(sum)
select sum(字段) (as sumvalue) from 表名;
计算最大值(max)
select max(字段) (as maxvalue) from 表名;
计算最小值(min)
select min(字段) (as minvalue) from 表名;
计算平均值(avg)
select avg(字段) (as avgvalue) from 表名;
计算个数(count)
select count(字段)(as totalcout) from 表名;
--查询users表中的记录,按照性别分组,查询男,女的体育成绩平均分
select gender,avg(PE) from users group by gender;
--查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数
select gender,avg(PE),count(id) from users group by gender;
--查询users表中的记录, 按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人,不参与分组
select gender,avg(PE),count(id) from users where PE>60 group by gender;
--查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数低于60分的人,不参与分组,分组之后,人数要大于2个人
select gender,avg(PE),count(id) from users where PE>60 group by gender having count(id)>2;
--查询users表中的前10行条记录
select * from users limit 10;
--查询users表中第2~11条记录 (从第2条记录开始累加10条记录)
select * from users limit 1,10;
--查询users表中第5~17条记录 (从第5条记录开始累加13条记录)
select * from users limit 4,16;
如果查询数据的来源来自多张表,则必须对这些表进行连接查询,连接是把不同表的记录连到一起的最普遍的方法,通过连接查询可将多个表作为一个表进行处理,连接查询分为内连接和外连接
语法格式
--语法1 (隐式内连接)
select 字段1,字段2...
from 表1,表2...
where 过滤条件;
--语法2 (显式内连接)
select 字段1,字段2...
from 表1 inner join 表2 ...
on 过滤条件;
e.g 有两张表:user表和city表
user表:
id | name |
---|---|
001 | 小红 |
002 | 小蓝 |
003 | 小白 |
004 | 小黄 |
005 | 小绿 |
006 | 小青 |
city表:
id | address |
---|---|
001 | 深圳 |
002 | 广州 |
003 | 北京 |
004 | 上海 |
005 | 汕头 |
006 | 潮州 |
007 | 揭阳 |
重合的部分就叫做内连接查询,例如下面过滤条件指的就是当两个表的id相等时才符合连接查询的条件
隐式内连接
select user.name,city.address
from user,city
where user.id = city.id;
结果为:
name | address |
---|---|
小红 | 深圳 |
小蓝 | 广州 |
小白 | 北京 |
小黄 | 上海 |
效率 | 汕头 |
小青 | 潮州 |
显式内连接
select user.name,city.address
from user inner join city
on user.id = city.id;
外连接查询分为左外连接查询和右外连接查询
语法
--左外连接
select 字段1,字段2..
from 表1 left outer join 表2 on 过滤条件;
--右外连接
select 字段1,字段2..
from 表1 right outer join 表2 on 过滤条件;
e.g: 上面两张表的左外链接结果
select user.name,city.address
from city left outer join user
on user.id = city.id;
结果为:
name | address |
---|---|
小红 | 深圳 |
小蓝 | 广州 |
小白 | 北京 |
小黄 | 上海 |
效率 | 汕头 |
小青 | 潮州 |
NULL | 揭阳 |
简单点说就是求交集之后并上city的其他数据,没有匹配的为NULL
右外连接结果:
select user.name,city.address
from city right outer join user
on user.id = city.id;
结果为:
name | address |
---|---|
小红 | 深圳 |
小蓝 | 广州 |
小白 | 北京 |
小黄 | 上海 |
效率 | 汕头 |
小青 | 潮州 |
简单点说就是求交集之后并上user的其他数据,没有匹配的为NULL
语法
select username
from user
where age =(
select avg(age)
from userInfo
)
例如:要查询工资大于10号部门的平均工资的非10号部门的员工信息
查询10号部门的平均工资
select avg(sal) from emp where deptno = 10;
那么工资大于10号部门的平均工资的非10号部门的员工信息为
select * from emp
where deptno!=10 and sal>(
select avg(sal)
from emp
where deptno = 10;
)
一些子查询的实例
查询在2022年8月9日销售的产品信息
select *
from dbo.product
where pno in (
select pno from dbo.prd
where odate = '2022/'
)