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;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| job | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
查看所有表的详细信息：
MariaDB [db1]> show table status;
MariaDB [db1]> show table status\G
MariaDB [db1]> show table status from db1;
MariaDB [db1]> show table status from db1\G
查看某张表的详细信息
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 |
+--------+----------+------+------+------+
| 0 | xiaohong | NULL | NULL | NULL |
| 9 | xiaomi | NULL | NULL | NULL |
| 10 | xiaofan | NULL | NULL | NULL |
+--------+----------+------+------+------+
匹配非空 is not null
MariaDB [db1]> select * from t1 where job is not null;
+--------+----------+------+-----------+------+
| series | name | job4 | job | job3 |
+--------+----------+------+-----------+------+
| 1 | xiaohong | NULL | education | NULL |
| 2 | xiaohua | NULL | education | NULL |
| 3 | xiaoming | NULL | it | NULL |
| 4 | xiaoli | NULL | it | NULL |
| 5 | xiaoli | NULL | worker | NULL |
| 6 | xiaozhan | NULL | | NULL |
| 7 | xiaohei | NULL | | NULL |
| 8 | xiaowu | NULL | student | NULL |
+--------+----------+------+-----------+------+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 |
+--------+--------+------+--------+------+
| 4 | xiaoli | NULL | it | NULL |
| 5 | xiaoli | NULL | worker | NULL |
+--------+--------+------+--------+------+
2 rows in set (0.00 sec)
MariaDB [db1]> select * from t1 where name like "xiao__";
+--------+--------+------+---------+------+
| series | name | job4 | job | job3 |
+--------+--------+------+---------+------+
| 4 | xiaoli | NULL | it | NULL |
| 5 | xiaoli | NULL | worker | NULL |
| 8 | xiaowu | NULL | student | NULL |
| 9 | xiaomi | NULL | NULL | NULL |
+--------+--------+------+---------+------+
在查询结果里过滤数据 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 |
+--------+----------+------+-----------+------+----------+
| 0 | xiaohong | NULL | NULL | NULL | |
| 1 | xiaohong | NULL | education | NULL | |
| 2 | xiaohua | NULL | education | NULL | |
+--------+----------+------+-----------+------+----------+
3 rows in set (0.00 sec)
MariaDB [db1]> select * from t1 limit 3,3;
+--------+----------+------+--------+------+----------+
| series | name | job4 | job | job3 | isdelete |
+--------+----------+------+--------+------+----------+
| 3 | xiaoming | NULL | it | NULL | |
| 4 | xiaoli | NULL | it | NULL | |
| 5 | xiaoli | NULL | worker | NULL | |
+--------+----------+------+--------+------+----------+
3 rows in set (0.00 sec)
MariaDB [db1]> select * from t1 limit 4 offset 3;
+--------+----------+------+--------+------+----------+
| series | name | job4 | job | job3 | isdelete |
+--------+----------+------+--------+------+----------+
| 3 | xiaoming | NULL | it | NULL | |
| 4 | xiaoli | NULL | it | NULL | |
| 5 | xiaoli | NULL | worker | NULL | |
| 6 | xiaozhan | NULL | | NULL | |
+--------+----------+------+--------+------+----------+
外键查询
创建老师表
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;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| tid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| class | varchar(10) | 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;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| sid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
| 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.views\G
2.2查看某个视图结构
MariaDB [db1]> desc v2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(10) | NO | | 0 | |
| name | varchar(20) | YES | | NULL | |
| 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;
