mariadb命令练习：管理表记录（增删改查）

mariadb命令练习：管理表记录（增删改查）

修改数据库的提示符：

MariaDB [db1]> prompt [u@h d]

PROMPT set to '[u@h d]'

[root@localhost db1]

一、增：建库，建表，插入数据

建库

库的命名规则：

可以使用数字，字母 _ 且不允许是纯数字

具有唯一性

区分字母大小写

不要使用特殊字符和sql命令关键字

create database 库名；

建库：

MariaDB [student]> create database db1;

MariaDB [db1]> create database if not exists db2 charset utf8;

建表：

MariaDB [db1]> create table t1(id int(10) auto_increment primary key,name varchar(20),job varchar(10));

插入数据：

MariaDB [db1]> insert into t1 values(1,"xiaoming","it");

MariaDB [db1]> insert into t1(name,job) values(1,"xiaowu","student");

MariaDB [db1]> insert t1 set name="xiaoli" job="teacher";

删：

删除表中的某一行数据

MariaDB [db1]> delete from t1 where id=2;

整表删除

MariaDB [db1]> delete from t1;

删除表

MariaDB [db1]> drop table db1.t1;

删除库

MariaDB [db1]> drop database db1;

MariaDB [db1]> drop database if exists db1;

改：

修改数据库的字符集

MariaDB [(none)]> alter database db1 default character set utf8;

修改表的字段名：

MariaDB [db1]> alter table t1 change id series int(5);

在表中加入字段：

MariaDB [db1]> alter table t1 add job2 varchar(20) after job;

在表中加入字段，并排在某字段的后面

MariaDB [db1]> alter table t1 add job4 varchar(20) after name;

删除表中的某字段：

MariaDB [db1]> alter table t1 drop job2;

修改表中的数据（字段，值）

　　update库名.表名 set 字段=值;

　　update库名.表名 set 字段1= 值1 where 字段2='值2';

MariaDB [db1]> update db1.t1 set name="xiaohong" where id=1;

查：

查看警告：

MariaDB [(none)]> show warnings;

查看字符集：

MariaDB [(none)]> show character set;

MariaDB [db1]> show databases;

查看数据库的创建语句：

MariaDB [(none)]> show create database db1;

+----------+----------------------------------------------------------------+

| Database | Create Database |

+----------+----------------------------------------------------------------+

| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+----------------------------------------------------------------+

查看库中的所有表

MariaDB [(none)]> use db1;

MariaDB [db1]> show tables;

+---------------+

| Tables_in_db1 |

+---------------+

| t1 |

| t2 |

+---------------+

2 rows in set (0.00 sec)

查看数据库表的创建语句

MariaDB [(none)]> show create table db1.t1;

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| t1 | CREATE TABLE `t1` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`job` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 |

查看表结构：

MariaDB [db1]> desc db1.t1;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int(10) | NO | PRI | NULL | auto_increment |

| job | varchar(10) | YES | | NULL | |

+-------+-------------+------+-----+---------+----------------+

查看所有表的详细信息：

MariaDB [db1]> show table status;

MariaDB [db1]> show table statusG

MariaDB [db1]> show table status from db1;

MariaDB [db1]> show table status from db1G

查看某张表的详细信息

MariaDB [(none)]> use db1;

MariaDB [db1]> show table status like "t1"G

MariaDB [db1]> show table status from db1 like "t1"G

　　查看表记录 select

select 字段名列表 from 库名.表名 where=条件；

1、查看表中所有记录所有字段的值

MariaDB [db1]> select * from t1;

+----+----------+-----------+

| id | name | job |

+----+----------+-----------+

| 1 | xiaohong | education |

| 2 | xiaohua | education |

| 3 | xiaoming | it |

| 4 | xiaoli | it |

| 5 | xiaoli | worker |

+----+----------+-----------+

2、查看表中所有记录指定字段的值

MariaDB [db1]> select id,name from t1;

+----+----------+

| id | name |

+----+----------+

| 1 | xiaohong |

| 2 | xiaohua |

| 3 | xiaoming |

| 4 | xiaoli |

| 5 | xiaoli |

+----+----------+

3、查看表中符合条件的记录，所有字段的值。

条件的表示方式？

数值比较

字段名符号数值

= ！= > >= < <=

MariaDB [db1]> select * from t1 where id=2;

+----+---------+-----------+

| id | name | job |

+----+---------+-----------+

| 2 | xiaohua | education |

+----+---------+-----------+

1 row in set (0.00 sec)

MariaDB [db1]> select * from t1 where id>=3;

+----+----------+--------+

| id | name | job |

+----+----------+--------+

| 3 | xiaoming | it |

| 4 | xiaoli | it |

| 5 | xiaoli | worker |

+----+----------+--------+

字符比较

字段名符号 “值”

= ！=

MariaDB [db1]> select * from t1 where name="xiaoli";

+----+--------+--------+

| id | name | job |

+----+--------+--------+

| 4 | xiaoli | it |

| 5 | xiaoli | worker |

+----+--------+--------+

2 rows in set (0.00 sec)

MariaDB [db1]> select * from t1 where name!="xiaoli";

+----+----------+-----------+

| id | name | job |

+----+----------+-----------+

| 1 | xiaohong | education |

| 2 | xiaohua | education |

| 3 | xiaoming | it |

+----+----------+-----------+

范围匹配

Between …and… 在…之间

MariaDB [db1]> select * from t1 where id between 3 and 5;

+----+----------+--------+

| id | name | job |

+----+----------+--------+

| 3 | xiaoming | it |

| 4 | xiaoli | it |

| 5 | xiaoli | worker |

+----+----------+--------+

in (值列表) 在…里面

MariaDB [db1]> select * from t1 where id in (2,3,4);

+----+----------+-----------+

| id | name | job |

+----+----------+-----------+

| 2 | xiaohua | education |

| 3 | xiaoming | it |

| 4 | xiaoli | it |

+----+----------+-----------+

MariaDB [db1]> select * from t1 where name in ("xiaoming");

+----+----------+------+

| id | name | job |

+----+----------+------+

| 3 | xiaoming | it |

+----+----------+------+

not in (值列表) 不在…里面

MariaDB [db1]> select * from t1 where id not in (2,3,4);

+----+----------+-----------+

| id | name | job |

+----+----------+-----------+

| 1 | xiaohong | education |

| 5 | xiaoli | worker |

+----+----------+-----------+

MariaDB [db1]> select * from t1 where name not in ("xiaoming");

+----+----------+-----------+

| id | name | job |

+----+----------+-----------+

| 1 | xiaohong | education |

| 2 | xiaohua | education |

| 4 | xiaoli | it |

| 5 | xiaoli | worker |

+----+----------+-----------+

逻辑匹配（多个查询条件时使用）

逻辑与 and 多个条件同时匹配

逻辑或 or 多个条件时,匹配某一个条件就可以

逻辑非！取反

Not

MariaDB [db1]> select series,name from t1 where name="xiaoli" and series=4;

+--------+--------+

| series | name |

+--------+--------+

| 4 | xiaoli |

+--------+--------+

MariaDB [db1]> select series,name from t1 where name="xiaoli" or series=4;

+--------+--------+

| series | name |

+--------+--------+

| 4 | xiaoli |

| 5 | xiaoli |

+--------+--------+

MariaDB [db1]> select series,name from t1 where name="xiaoli" or series=4 and job="it";

+--------+--------+

| series | name |

+--------+--------+

| 4 | xiaoli |

| 5 | xiaoli |

+--------+--------+

2 rows in set (0.00 sec)

MariaDB [db1]> select series,name from t1 where (name="xiaoli" or series=4) and job="it";

+--------+--------+

| series | name |

+--------+--------+

| 4 | xiaoli |

+--------+--------+

MariaDB [db1]> select series,name from t1 where (name="xiaoli" or series=4) and job!="it";

+--------+--------+

| series | name |

+--------+--------+

| 5 | xiaoli |

+--------+--------+

匹配空 is null

MariaDB [db1]> select * from t1 where job is null;

+--------+----------+------+------+------+

| series | name | job4 | job | job3 |

+--------+----------+------+------+------+

+--------+----------+------+------+------+

匹配非空 is not null

MariaDB [db1]> select * from t1 where job is not null;

+--------+----------+------+-----------+------+

| series | name | job4 | job | job3 |

+--------+----------+------+-----------+------+

+--------+----------+------+-----------+------+Select name,uid from datebase.user where name is not null;

Select name,uid from datebase.user where name=”null”;

Select name,uid from datebase.user where name=””;

模糊查询 like

Where 字段名 like ‘表达式’；

% 匹配零个或者多个字符

_匹配任意一个字符

MariaDB [db1]> select * from t1 where name like "%li";

+--------+--------+------+--------+------+

| series | name | job4 | job | job3 |

+--------+--------+------+--------+------+

+--------+--------+------+--------+------+

2 rows in set (0.00 sec)

MariaDB [db1]> select * from t1 where name like "xiao__";

+--------+--------+------+---------+------+

| series | name | job4 | job | job3 |

+--------+--------+------+---------+------+

+--------+--------+------+---------+------+

在查询结果里过滤数据 having 条件

MariaDB [db1]> Select series,name from db1.t1 where name like "xiao%" having name="xiaomi";

+--------+--------+

| series | name |

+--------+--------+

| 9 | xiaomi |

+--------+--------+

1 row in set (0.00 sec)

MariaDB [db1]> Select series,name from db1.t1 where name like "xiao%" having series=4;

+--------+--------+

| series | name |

+--------+--------+

| 4 | xiaoli |

+--------+--------+

聚合查询

MariaDB [db1]> select max(series) from t1;

+-------------+

| max(series) |

+-------------+

| 10 |

+-------------+

MariaDB [db1]> select min(series) from t1;

+-------------+

| min(series) |

+-------------+

| 0 |

+-------------+

MariaDB [db1]> select count(*) from t1 where isdelete=0;

+----------+

| count(*) |

+----------+

| 6 |

+----------+

排序

MariaDB [db1]> select * from t1 order by series asc;

MariaDB [db1]> select * from t1 order by series desc;

分组

MariaDB [db1]> select count(*),name from t1 group by name;

+----------+----------+

| count(*) | name |

+----------+----------+

| 1 | xiaofan |

| 1 | xiaohei |

| 2 | xiaohong |

| 1 | xiaohua |

| 2 | xiaoli |

| 1 | xiaomi |

| 1 | xiaoming |

| 1 | xiaowu |

| 1 | xiaozhan |

+----------+----------+

限制

MariaDB [db1]> select * from t1 limit 3;

+--------+----------+------+-----------+------+----------+

| series | name | job4 | job | job3 | isdelete |

+--------+----------+------+-----------+------+----------+

+--------+----------+------+-----------+------+----------+

3 rows in set (0.00 sec)

MariaDB [db1]> select * from t1 limit 3,3;

+--------+----------+------+--------+------+----------+

| series | name | job4 | job | job3 | isdelete |

+--------+----------+------+--------+------+----------+

+--------+----------+------+--------+------+----------+

3 rows in set (0.00 sec)

MariaDB [db1]> select * from t1 limit 4 offset 3;

+--------+----------+------+--------+------+----------+

| series | name | job4 | job | job3 | isdelete |

+--------+----------+------+--------+------+----------+

+--------+----------+------+--------+------+----------+

外键查询

创建老师表

MariaDB [db1]> create table teacher (

-> tid int auto_increment primary key,

-> name varchar(10),

-> age tinyint unsigned,

-> class varchar(10)

-> );

Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc teacher;

+-------+---------------------+------+-----+---------+----------------+

+-------+---------------------+------+-----+---------+----------------+

| tid | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | YES | | NULL | |

+-------+---------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

MariaDB [db1]> insert into teacher values (1,"zhulaoshi",18,"linux");

MariaDB [db1]> insert into teacher values (2,"wulaoshi",20,"linux");

MariaDB [db1]> select * from teacher;

+-----+-----------+------+-------+

| tid | name | age | class |

+-----+-----------+------+-------+

| 1 | zhulaoshi | 18 | linux |

| 2 | wulaoshi | 20 | linux |

+-----+-----------+------+-------+

创建学生表

MariaDB [db1]> create table student (

-> sid int auto_increment primary key,

-> name varchar(10),

-> age tinyint unsigned,

-> tid int,

-> foreign key (tid) references teacher(tid));

Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> select * from teacher;

Empty set (0.00 sec)

MariaDB [db1]> desc student;

+-------+---------------------+------+-----+---------+----------------+

+-------+---------------------+------+-----+---------+----------------+

| sid | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | YES | | NULL | |

| tid | int(11) | YES | MUL | NULL | |

+-------+---------------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

MariaDB [db1]> insert into student values (1,"xiaoming",17,1);

MariaDB [db1]> insert into student values (2,"xiaoming",18,2);

MariaDB [db1]> select * from student;

+-----+----------+------+------+

| sid | name | age | tid |

+-----+----------+------+------+

| 1 | xiaoming | 17 | 1 |

| 2 | xiaoming | 18 | 2 |

+-----+----------+------+------+

查询学生对应的老师与课程：

MariaDB [db1]> select student.name,teacher.name,teacher.class from student,teacher where student.tid=teacher.tid;

+----------+-----------+-------+

| name | name | class |

+----------+-----------+-------+

| xiaoming | zhulaoshi | linux |

| xiaoming | wulaoshi | linux |

+----------+-----------+-------+

视图的相关操作

1、创建视图：

1.1先创建表

MariaDB [db1]> insert t3 set name="xiaoming",job="it";

MariaDB [db1]> insert t3 set name="xiaowang",job="it";

MariaDB [db1]> insert t3 set name="xiaohong",job="it";

MariaDB [db1]> select * from t3;

+----+----------+------+

| id | name | job |

+----+----------+------+

| 1 | xiaoming | it |

| 2 | xiaowang | it |

| 3 | xiaohong | it |

+----+----------+------+

3 rows in set (0.00 sec)

1.2创建视图

MariaDB [db1]> create view v1 as select name,job from t3;

MariaDB [db1]> create view v2 as select * from t3 where id >= 2;

2、查看视图：

2.1查看所有视图

MariaDB [db1]> select * from information_schema.viewsG

2.2查看某个视图结构

MariaDB [db1]> desc v2;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int(10) | NO | | 0 | |

| job | varchar(10) | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2.3查看视图内容

MariaDB [db1]> select * from v2;

+----+----------+------+

| id | name | job |

+----+----------+------+

| 2 | xiaowang | it |

| 3 | xiaohong | it |

+----+----------+------+

3、删除视图：

MariaDB [db1]> drop view v2;

或者

MariaDB [db1]> drop view if exists v2;

4、修改视图

MariaDB [db1]> alter view v1 as select id,job from t3;

本文摘自：https://www.cnblogs.com/

mariadb命令练习：管理表记录（增删改查）2021-09-17 16:23:07

mariadb命令练习：管理表记录（增删改查）
2021-09-17 16:23:07