APP
mariadb命令練習(xí)：管理表記錄（增刪改查）
?
修改數(shù)據(jù)庫的提示符：
MariaDB [db1]> prompt [u@h d]
PROMPT set to '[u@h d]'
[root@localhost db1]
?
一、增：建庫，建表，插入數(shù)據(jù)
??? 建庫
??? 庫的命名規(guī)則：
??? 可以使用數(shù)字，字母 _ 且不允許是純數(shù)字
??? 具有唯一性
??? 區(qū)分字母大小寫
??? 不要使用特殊字符和sql命令關(guān)鍵字
?? 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));
插入數(shù)據(jù)：
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";
?
?
刪：
刪除表中的某一行數(shù)據(jù)
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;
?
改：
修改數(shù)據(jù)庫的字符集
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;
修改表中的數(shù)據(jù)（字段，值）
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;
查看數(shù)據(jù)庫的創(chuàng)建語句：
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)
?
?
查看數(shù)據(jù)庫表的創(chuàng)建語句
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 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
?
?
查看表結(jié)構(gòu)：
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??? |??????????????? |
+-------+-------------+------+-----+---------+----------------+
?
?
查看所有表的詳細(xì)信息：
MariaDB [db1]> show table status;
MariaDB [db1]> show table statusG
MariaDB [db1]> show table status from db1;
MariaDB [db1]> show table status from db1G
查看某張表的詳細(xì)信息
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、查看表中符合條件的記錄，所有字段的值。
條件的表示方式？
數(shù)值比較
字段名? 符號(hào)? 數(shù)值
=? ！=? >?? >=? <?? <=
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 |
+----+----------+--------+
?
?
字符比較
字段名? 符號(hào)? “值”
=? ！=
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??? |
+----+----------+-----------+
邏輯匹配（多個(gè)查詢條件時(shí)使用）
邏輯與 and 多個(gè)條件同時(shí)匹配
邏輯或 or 多個(gè)條件時(shí),匹配某一個(gè)條件就可以
邏輯非 ！ 取反
?????? 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 ‘表達(dá)式’；
% 匹配零個(gè)或者多個(gè)字符
_匹配任意一個(gè)字符
?
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 |
+--------+--------+------+---------+------+
?
在查詢結(jié)果里過濾數(shù)據(jù) 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 |???????? |
+--------+----------+------+--------+------+----------+
?
?
?
外鍵查詢
創(chuàng)建老師表
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 |
+-----+-----------+------+-------+
?
?
?
創(chuàng)建學(xué)生表
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 |
+-----+----------+------+------+
?
查詢學(xué)生對(duì)應(yīng)的老師與課程：
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 |
+----------+-----------+-------+
?
視圖的相關(guān)操作
?
1、創(chuàng)建視圖：
1.1先創(chuàng)建表
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創(chuàng)建視圖
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查看某個(gè)視圖結(jié)構(gòu)
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查看視圖內(nèi)容
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/
