一、数据库基本操作
1.查看数据库操作
查看数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.002 sec)
查看创建数据库状态
MariaDB [(none)]> show create database mysql;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| mysql | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.000 sec)
使用哪个库
MariaDB [(none)]> use mysql;
如果想退出这个库是不能退出的，但可以show databases;查询所有库，再使用use 库名 切换到那个库
退出mariadb数据库
exit
quit
\q
ctrl + c
ctrl + d
查看数据库版本
[root@c3 ~]# mysqladmin --version
mysqladmin Ver 9.1 Distrib 10.3.14-MariaDB, for Linux on x86_64
2.修改数据库
创建数据库
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.001 sec)
删除数据库
MariaDB [(none)]> drop database test;
修改数据库默认字符集
MariaDB [(none)]> alter database 库名 default character set=utf8;
3.用户管理
查看当前用户
MariaDB [(none)]> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)
创建test用户并给test用户授予查，改，更新，删所有库的所有表的权限（在任意主机上登录）
MariaDB [mysql]> grant select,create,update,delete on *.* to test@'%' identified by '123';
创建test1用户并给test用户授予最高权限在所有库的所有表（只能在本地主机登录）
MariaDB [mysql]> grant all on *.* to 'test1'@'localhost' identified by '321';
查看test用户的权限
MariaDB [mysql]> show grants for 'test';
+------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@% |
+------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE, CREATE ON *.* TO 'test'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
查看mysql库的user表
MariaDB [mysql]> select user,host,password from mysql.user;
+-------+-----------+-------------------------------------------+
| user | host | password |
+-------+-----------+-------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | c3 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | ::1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| test | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| test | % | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| test1 | localhost | *7297C3E22DEB91303FC493303A8158AD4231F486 |
+-------+-----------+-------------------------------------------+
7 rows in set (0.001 sec)
查看当前用户的权限
MariaDB [mysql]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
收回test在任意数据库的任意表的最高权限
MariaDB [mysql]> revoke all on *.* from 'test'@'%';
二、数据库表的操作
1.mariadb数据类型
MariaDB数据类型可以分为数字，日期和时间以及字符串值。
使用数据类型的原则：够用就行，尽量使用范围小的，而不用大的
常用的数据类型：
整数：int，bit
小数：decimal
字符串：varchar，char
日期时间：date，time， datetime
枚举类型（enum）
约束：
主键primary key:物理上存储的顺序
非空not null：此字段不能为空
唯一unique：此字段不允许重复
默认default：当不填写此值时会使用默认值，如果填写则已填写为准
外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
2.表的创建，修改，删除，查询
1）显示当前时间
MariaDB [test]> select now();
+---------------------+
| now() |
+---------------------+
| 2019-05-09 20:56:47 |
+---------------------+
1 row in set (0.000 sec)
2）创建classes表（id ,name） ，创建students表（id,name,age,high,gender）
create table 表名（字段1 数据类型，字段2 数据类型…,）;
MariaDB [test]> create table classes (id tinyint unsigned,name varchar(10));
Query OK, 0 rows affected (0.076 sec)
create table students (
id tinyint unsigned not null auto_increment primary key,
name varchar(20),
age tinyint unsigned default 0,
high decimal(5,2),
gender enum('男', '女', '中性', '保密') default '保密',
cls_id int unsigned
);
3）查看表的创建
show create table 表名；
MariaDB [test]> show create table classes;
+---------+------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------+
| classes | CREATE TABLE `classes` (
`id` tinyint(3) unsigned DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
4）查看表信息
desc 表名；
MariaDB [test]> desc students;
+--------+-------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int(10) unsigned | YES | | NULL | |
+--------+-------------------------------------+------+-----+---------+----------------+
6 rows in set (0.001 sec)
show columns from 表名;
MariaDB [test]> show columns from students;
+--------+-------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
+--------+-------------------------------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)
5）修改表-添加字段
alter table 表名 add 列名 类型;
MariaDB [test]> alter table students add birthday datetime;
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
6） 修改表-修改字段:不重名
alter table 表名 modify 列名 类型及约束;
MariaDB [test]> alter table students modify birthday date;
Query OK, 0 rows affected (0.057 sec)
Records: 0 Duplicates: 0 Warnings: 0
7）修改表-修改字段:重命名
alter table 表名 change 原名 新名 类型及约束;
MariaDB [test]> alter table students change birthday birth date;
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
8）修改表-删除字段
alter table 表名 drop 列名;
MariaDB [test]> alter table students drop birth;
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0
9）删除表
drop table 表名；
MariaDB [test]> drop table classes;
Query OK, 0 rows affected (0.004 sec)
三、表数据的增删改查
1.增加-全列插入
insert into 表名 values(…)；
主键字段可以用0，null，default来占位 ，枚举类型的字段可以按创建时的序号来代替枚举的内容
向students表里插入一个学生信息
MariaDB [test]> insert into students values (0,'小明',19,180.999,'男',1);
Query OK, 1 row affected, 1 warning (0.002 sec)
MariaDB [test]> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小明 | 19 | 181.00 | 男 | 1 |
+----+--------+------+--------+--------+--------+
1 row in set (0.000 sec)
以下语句执行效果一样
MariaDB [test]> insert into students values (default,'小明',19,180.999,1,1);
high字段数据类型decimal（5,2）保留5位有效数字并且两位小数
2.增加-部分插入
insert into students(id, name, age) values (0,‘小红’,18);
部分插入多条记录
insert into students(id, name, age) values (0,'小红',18)，（0,'小李'，19）
3.修改-条件修改
update 表名 set 列1=值1，列2=值2… where 条件；
MariaDB [test]> update students set age=100 where id=1;
MariaDB [test]> update students set age=100,cls_id=23 where id=1;
4.删除-物理删除
delete from 表名 where 条件；
MariaDB [test]> delete from students where cls_id=32;
5.删除-逻辑删除
用一条字段来表示，这条信息是否已经不在使用了
给students表添加一个is_delete字段 bit类型
MariaDB [test]> alter table students add is_delete bit default 0;
#将students表id字段为6的设置is_delete=1
MariaDB [test]> update students set is_delete=1 where id=6;
6.表数据的查询（条件，排序，聚合函数，分组，分页）
创建人类表
create table persons(
id tinyint unsigned not null auto_increment primary key,
name varchar(10),
age tinyint unsigned default 0,
high decimal(5,2),
gender enum('男', '女', '中性', '保密') default '保密',
id_delete bit default 0
);
往person表里插数据
insert into persons values
(0,'小明儿',18,180.00,1,0),
(0,'小日',19,180.00,4,0),
(0,'大月',28,185.00,2,0),
(0,'小红',58,175.00,2,0),
(0,'小李',108,160.00,1,0),
(0,'大王',44,150.00,1,1),
(0,'麦克迪尔',52,170.00,3,1),
(0,'鲍勃周',34,null,1,0),
(0,'静香',13,170.00,2,1),
(0,'大雄',13,170.00,1,1);
1）查询-查询所有列
select * from 表名；
MariaDB [test]> select * from persons;
2）查询-一定条件查询（where）
MariaDB [test]> select * from persons where id=5;
3）查询多个字段
MariaDB [test]> select id,name from persons;
4）给字段起别名
MariaDB [test]> select id,name as '姓名',age as '年纪' from persons;
+----+--------------+--------+
| id | 姓名 | 年纪 |
+----+--------------+--------+
| 1 | 小明儿 | 18 |
| 2 | 小日 | 19 |
| 3 | 大月 | 28 |
| 4 | 小红 | 58 |
| 5 | 小李 | 108 |
| 6 | 大王 | 44 |
| 7 | 麦克迪尔 | 52 |
| 8 | 鲍勃周 | 34 |
| 9 | 静香 | 13 |
| 10 | 大雄 | 13 |
+----+--------------+--------+
10 rows in set (0.000 sec)
5）通过表名.字段查询
MariaDB [test]> select persons.name,persons.age from persons;
6）给表起别名查询
MariaDB [test]> select p.name,p.age from persons as p;
7）显示消除重复行后的
MariaDB [test]> select distinct age from persons;
8）条件查询-比较运算符
查询年纪大于18岁的信息
MariaDB [test]> select * from persons where age>18;
18到34岁之间（两种写法）
MariaDB [test]> select * from persons where age>=18 and age<=34;
MariaDB [test]> select * from persons where age between 18 and 34;
年纪在18岁以上或身高180以上的人
MariaDB [test]> select * from persons where age>18 or high>180;
9）模糊查询
where 字段 like ‘匹配符’ 匹配符： _ （一个字符），%（零个，一个或多个）
查询姓名中有’小’的所有名字
MariaDB [test]> select * from persons where name like '%小%';
查询两个字的名字
MariaDB [test]> select * from persons where name like '__';
查询至少有2个字的名字
MariaDB [test]> select * from persons where name like '__%';
10）范围查询
in （1,3,6）表示在一个非连续的范围内
查询年纪为18和34的人
MariaDB [test]> select * from persons where age in (18,34);
not in （1,3,6）表示不在一个非连续的范围内
MariaDB [test]> select * from persons where age not in (18,34);
查询年纪不在18到34的信息
MariaDB [test]> select * from persons where age not between 18 and 34;
11）空判断
判断为空is null
查询身高为空的信息
MariaDB [test]> select * from persons where high is null;
判断非空is not null
查询身高不为空的信息
MariaDB [test]> select * from persons where high is not null;
12）排序
order by 字段 asc/desc
asc:从小到大排序，即升序 ，默认就是asc可省略
desc:从大到小排序，即降序
查询年纪在18到34之间的男性，按照年纪从小到大
MariaDB [test]> select * from persons where gender=1 and age between 18 and 34 order by age;
查询年纪在18到34岁之间的女性，按照身高从高到矮
MariaDB [test]> select * from persons where gender=2 and age between 18 and 34 order by high desc;
order by 多字段：
查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序
MariaDB [test]> select * from persons where gender=2 and age between 18 and 60 and high order by high desc;
13）聚合函数
总数：count
查询男性有多少人
MariaDB [test]> select count(*) from persons where gender=1;
最大值：max
查询最大的年纪
MariaDB [test]> select max(age) from persons;
查询女性的最高身高
MariaDB [test]> select max(high) from persons where gender=2;
最小值：min
查询最小的年纪
MariaDB [test]> select min(age) from persons;
求和：sum
查询计算所有人的年龄总和
MariaDB [test]> select sum(age) from persons;
平均值：avg
计算平均年纪（两种写法）
MariaDB [test]> select sum(age)/count(*) from persons;
MariaDB [test]> select avg(age) from persons;
保留2位小数
MariaDB [test]> select round(avg(age),2) from persons;
14）分组
group by
按照性别分组，查询所有的性别
MariaDB [test]> select gender from persons group by gender;
计算每组性别的人数
MariaDB [test]> select gender,count(*) from persons group by gender;
查询男性组中的姓名 group_concat
MariaDB [test]> select gender,group_concat(name) from persons where gender=1 group by gender;
having
查询每个性别平均年纪超过30岁的性别，以及姓名 having avg(age) > 30
MariaDB [test]> select gender,group_concat(name),avg(age) from persons group by gender having avg(age)>30;
查询每种性别中的人数多于4个的组的信息
MariaDB [test]> select gender,group_concat(name) from persons group by gender having count(*)>4;
15）分页
显示5页
MariaDB [test]> select * from persons limit 5;
分页显示，每页显示2条数据
MariaDB [test]> select * from persons limit 0,2;
查找所有女性，按照身高从高到矮排序，并且分页显示，每页显示2条数据
MariaDB [test]> select * from persons where gender=2 and high order by high desc limit 0,2;