数据库MariaDB的基本命令(1)

登录数据库

#mysql -hIP -u用户-p密码
本地可以直接mysql –uroot -p

忘记数据库root用户密码

#vim /etc/my.conf.d/server.conf  //centos6
#vim /etc/my.cnf.d/server.cnf  //centos7

添加skip-grant-tables,重启服务service mysqld restart或systemctl restart mysqld

查看数据库信息

show databases;　　#查看数据库
show create database mysql;　#查看创建数据库状态

创建数据库

create database 数据库名; #创建时数据库
create database db_name default character SET utf8 collate utf8_general_ci; #创建数据库并设置中文字符集 

collate:校对集。制定数据集如何排列，按照二进制编码比较排序,主要是对字符集之间的比较和排序,collate为数据表的校对规则可以通过 show collation查看所有的校对集

修改数据库默认字符集

alter database 库名 default character set=utf8; 

删除数据库

drop database 数据库名;

用户管理

select user();　#查看当前用户
select host,user from mysql.user;  #查看所有用户
delete from mysql.user where user= '';  #删除匿名用户 

注意：有时赋予用户所有权限,但是却无法查看全部数据库,是因为匿名登录的原因，需要删掉匿名用户并重启服务。

修改密码

use mysql;
select * from user \G;  #最常用
select host,user,password from user;  #查看用户的登陆位置、用户名、密码
update user set password=password('密码') where user='root';  #修改密码
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass'); #原密码为空
#mysqladmin -uroot -p原密码 password 新密码
#shell下修改数据库用户密码

允许root用户远程登陆（必须删掉匿名用户）

update user set host='%' where user='root';

新建用户

create user 'username'@'host' identified by 'password'
说明：username：要创建的用户名
     host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
     password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

权限管理

grant all privileges on *.* to dog@'%';
  #给dog赋予所有权限；第一个*数据库（所有）；第二个*表名（所有表） @:分隔符；%:本地和第三方连接都可以，也可以写localhost 
grant select,create,update,delete on test03.grade to 'dog'@'%';
  #给dog用户添加对名为test03数据库的grade表查询，创建，更新，删除的权限（在任意主机上） 　　　　　　
flush privileges; #刷新权限
grant all privileges on test03.grade to 'aaa'@'%';
  #给aaa赋予test03数据库的grade表最高权限（在任意主机上）

查看用户的权限

show grants for 'username';
#查看username用户的权限（root用户）

收回username在任意数据库的任意表的最高权限

revoke all on *.* from 'username'@'%';

显示当前时间

select now();

常用的数据类型

整数:int, bit
小数:decimal 　　 #decimal(5,2)
字符串:varchar, char　　
日期时间:date, time, datetime
枚举类型(enum)

约束

1. 主键primary key：物理上存储的顺序
2. 非空not null：此字段不能为空
3. 唯一unique：此字段不允许重复
4. 默认default：当不填写此值时会使用默认值，如果填写则已填写为准
5. 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常

创建表

create table 表名(字段1 类型，字段2 类型，···);
create table zzzz(
	id int primary key not null auto_increment,
	name varchar(20)，
	age int 
);

查看表的结构

desc table_name;

查看表的创建

show create table table_name;

修改表

向表中插入数据

insert into table_name values('字段1数据'，'字段2数据'，···);

修改表-添加字段

alter taber 表名 add 列名 类型;

修改表-修改字段：不重命名版

alter table 表名 modify 列名 类型及约束;

修改表-修改字段：重命名版

alter table 表名 change 原名 新名 类型及约束;

修改表-删除字段

alter table 表名 drop 列名;

删除表

drop table 表名;

增删改查

增加-全列插入

insert into 表名 values(..);  #主键字段可以用0 null default 来站位

部分插入

insert into students(name, gender) values ('小乔', 2);

部分插入(多条记录)

	insert into students(name, gender) values ('大乔', 2),('貂蝉', 2);

修改

update 表名 set 列1=值1, 列2=值2... where 条件;

删除

物理删除

delete from 表名 where 条件;
delete from 表名;   #删除表内所有内容

逻辑删除，假删除（打个标记）

alter table 表名 add is_delete bit default 0;

查询

查询所有列

select * from 表名;

一定条件查询

select * from students where name='小李飞刀';
select * from students where id>3;

查询制定列

select name, gender from students;

可以使用as制定列或表制定别名;

select name as 姓名, gender as 性别 from students;

字段的顺序

select id as 序号, gender as 性别, name as 姓名 from students;

查询的基本使用

举个栗子

创建学生表

--创建学生表
create table students (
	id int unsigned not null auto_increment primary key,
	name varchar(20) default '',
	age tinyint unsigned default 0,
	high decimal(5,2),
	gender enum('男', '女', '中性', '保密') default '保密',
	cls_id int unsigned default 0,
	is_delete bit default 0
);

往students表里插入数据

insert into students values
(0,'黄蓉',18,180.00,2,1,0),
(0,'王语嫣',19,180.00,2,2,0),
(0,'郭靖',28,185.00,1,1,0),
(0,'乔峰',58,175.00,1,2,0),
(0,'灭绝师太',108,160.00,2,1,0),
(0,'东方不败',44,150.00,4,2,1),
(0,'岳不群',59,135.00,3,2,1);

查询所有列

select * from students;

一定条件查询

select * from students where name='东方不败';
select * from students where id>3;

查询制定列

select name,gender from students;

使用as给字段起别名

select name as 姓名, age as 年龄 from students;

通过表名字查询

select students.name, students.age from students;

给表起别名查询

select s.name, s.age from students as s;

消除重复行-distinct

select distinct gender from students;

条件查询：

比较运算符

-- 查询年纪大于18岁的信息
select * from students where age > 18;
select id, name, gender from students where age > 18;
--18岁到28岁之间(and)
select * from students where age>18 and age<28;
--在18岁以上或者身高180以上的人(or)
select * from students where age>18 or high>=180;

模糊查询

-- like
-- % 替代1个或者多个甚至是没有
select * from students where name like '郭%';
		
-- 查询姓名中有‘语’的所有名字
select * from students where name like '%语%';
		
-- 查询有两个字的名字，一个下划线代表一个字符。
select * from students where name like '__';
		
-- 查询至少有2个字的名字
select * from students where name like '__%';
		
-- rlike 正则
-- 查询以黄开始的名字
select * from students where name rlike '^黄.*';

--查询以东开始，以败结尾的名字
select * from students where name rlike '^东.*败$';

范围查询

--students表里查询年龄为12、18、34的人
select * from students where age=18 or age=34 or age=12;
select * from students where age in (12,18,34);

--查询 年龄在17岁到34岁之间的信息
select * from students where age between 18 and 34;

--查询 年纪不在18到34岁的信息
select * from students where age not between 18 and 34;

空判断

-- 判断is null
-- 查询身高为空的信息
select * from students where high is null;
		
-- 判断非空is not null
select * from students where high is not null;

排序

• order by 字段
• asc 从小到大排列，即升序
• desc 从大到小排列，即降序

-- 查询年纪在18到34岁之间的男性，按照年纪从小到大
select * from students where age between 18 and 34 and gender=1;
select * from students where age between 18 and 34 and gender=1 order by age;
select * from students where age between 18 and 34 and gender=1 order by age asc;
select * from students where (age between 18 and 34) and gender=1 order by age desc;
		
-- 查询年纪在18到34岁之间的女性，身高从高到矮
select * from students where (age between 18 and 34) and gender=2 order by high desc;
		
-- order by 多字段
-- 查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序
select * from students where (age between 18 and 34) and gender=2 order by high desc, age desc;
		
-- 查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序，如果年龄也相等那么按照id从小到大排序；
select * from students where (age between 18 and 34) order by high desc, age,id;
		
-- 按照年纪从小到大、身高从高到矮的排序
select * from students order by age asc, high desc;

聚合函数

-- 总数,个数
-- count 
-- 查询男性有多少人
select * from students where gender=1;
select count(*) from students where gender=1;
	
select count(*) as '男性' from students where gender=1;
	
-- 最大值
-- max
-- 查询最大的年纪
select age from students;
select max(age) from students;
	
-- 查询女性的最高 身高
select gender,max(high) from students where gender=2;
	
-- 最小值
-- min
select min(age) from students;
	
-- 求和
-- sum
-- 计算所有人的年龄总和
select sum(age) from students;
	
-- 平均值
-- avg
-- 计算平均年纪
select avg(age) from students;
	
-- 计算平均年纪 sum(age)/count(*)
select sum(age)/count(*) from students;
-- 保留2位小数
select round(avg(age), 2) from students;

分组

• group by 字段

-- 按照性别分组，查询所有的性别
select gender from students group by gender;
	
-- 计算每组性别的人数
select gender, count(*) from students group by gender;
	
-- 计算男性人数
select gender, count(*) from students where gender=1 group by gender;
	
--把查出来的内容拼接成一个字符串concat 
select concat(id,name,age) from students;
select concat(id,' ',name,' ',age) from students;
	
-- 查询男性组中的姓名 group_concat
select gender, group_concat(name) from students where gender=1 group by gender;
select gender, group_concat(name,age) from students where gender=1 group by gender;
select gender, group_concat(name,age,id) from students where gender=1 group by gender;
select gender, group_concat(name,age," ",id) from students where gender=1 group by gender;
	
-- having
-- 查询平均年纪超过30岁的性别，以及姓名 having avg(avg) > 30
select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30;
- 查询每种性别中的人数多于4个组的信息
select gender, group_concat(name) from students group by gender having count(*)>4;

分页

-- 分页显示，每页显示2条数据
select * from students limit 0, 2;
-- 按照身高从高到矮排序，查找出所有女性，并且分页显示，每页显示2条数据
select * from students where gender=2 order by high desc limit 0, 2;

• 在创建一个班级表

create table classes(
	id int unsigned auto_increment primary key not null,
	name varchar(20) not null
);

--向classes表里插入数据
insert into classes values (0, '1班'),(0, '2班');

连接查询(前面包含后面，前为父，后为子)

--inner join ...on 
--查询对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
-- 按照要求显示姓名，班级
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
	
-- 给数据表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
	
-- 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;
	
-- 在以上查询中将班级姓名显示在第一列
select classes.name,students.* from students inner join classes on students.cls_id=classes.id;
	
-- 查询有能够对应班级的学生以及班级信息，按照班级进行排序
select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id;
	
-- 当是同一个班级的时候，按照学生的id从小到大
select classes.id, students.* from students inner join classes on students.cls_id=classes.id order by classes.id, students.id;
	
--左连接（以左表为基准）
select * from students left join classes on students.cls_id=classes.id;

--右连接(以右表为基准)
select * from students right join classes on students.cls_id=classes.id;

自关联

• 栗子

--创建一个地区表
create table areas(
		aid int primary key auto_increment,
		name varchar(20),
		pid int
	);

 --省份
insert into areas(aid,name,pid) values(1,'北京市',null);  
insert into areas(aid,name,pid) values(0,'天津市',null);  
insert into areas(aid,name,pid) values(0,'河北省',null);  
insert into areas(aid,name,pid) values(0,'山西省',null);  

--地级市
insert into areas(aid,name,pid) values(0,'海淀区',1);  
insert into areas(aid,name,pid) values(0,'滨海区',2);  
insert into areas(aid,name,pid) values(0,'沧州市',3);  
insert into areas(aid,name,pid) values(0,'大同市',4);

insert into areas(aid,name,pid) values(0,'朝阳区',1);  
insert into areas(aid,name,pid) values(0,'武清区',2);  
insert into areas(aid,name,pid) values(0,'石家庄',3);  
insert into areas(aid,name,pid) values(0,'太原市',4);


--县级市
insert into areas(aid,name,pid) values(0,'西二旗',5);  
insert into areas(aid,name,pid) values(0,'大港',6);  
insert into areas(aid,name,pid) values(0,'任丘市',7);  
insert into areas(aid,name,pid) values(0,'清徐',8);    

insert into areas(aid,name,pid) values(0,'中关村',5);  
insert into areas(aid,name,pid) values(0,'汉沽',6);  
insert into areas(aid,name,pid) values(0,'河间市',7);  
insert into areas(aid,name,pid) values(0,'阳曲',8);   

查询北京市所有区

select * from areas as province inner join areas as city on province.aid=city.pid having province.name='北京市';
select province.name,city.name from areas as province inner join areas as city on province.aid=city.pid having province.name='北京市';

子查询

-- 标量子查询
-- 查询出北京市所有区的信息
select * from areas where pid=(select aid from areas where name='北京市');
select * from areas where pid in (select aid from areas where name='北京市');

下一篇：数据库MariaDB的基本命令(2)