国产日产欧洲无码视频无遮挡,日本中文字幕aⅴ高清看片,亚洲一区在线视频在线观看

mariadb命令練習(xí)：管理表記錄（增刪改查）

修改數(shù)據(jù)庫(kù)的提示符：

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

PROMPT set to '[u@h d]'

[root@localhost db1]

一、增：建庫(kù)，建表，插入數(shù)據(jù)

??? 建庫(kù)

??? 庫(kù)的命名規(guī)則：

??? 可以使用數(shù)字，字母 _ 且不允許是純數(shù)字

??? 具有唯一性

??? 區(qū)分字母大小寫

??? 不要使用特殊字符和sql命令關(guān)鍵字

?? create database 庫(kù)名；

建庫(kù)：

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;

刪除庫(kù)

MariaDB [db1]> drop database db1;

MariaDB [db1]> drop database if exists db1;

改：

修改數(shù)據(jù)庫(kù)的字符集

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庫(kù)名.表名 set 字段=值;

　　update庫(kù)名.表名 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ù)庫(kù)的創(chuàng)建語(yǔ)句：

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

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

| Database | Create Database??????????????????????????????????????????????? |

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

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

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

查看庫(kù)中的所有表

MariaDB [(none)]> use db1;

MariaDB [db1]> show tables;

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

| Tables_in_db1 |

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

| t1??????????? |

| t2??????????? |

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

2 rows in set (0.00 sec)

查看數(shù)據(jù)庫(kù)表的創(chuàng)建語(yǔ)句

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;

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

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

| 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 庫(kù)名.表名 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 |

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

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

匹配非空??????? 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 ‘表達(dá)式’；

% 匹配零個(gè)或者多個(gè)字符

_匹配任意一個(gè)字符

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 |

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

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

在查詢結(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 |

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

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

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 |???????? |

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

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 |???????? |

|????? 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;

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

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

| 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;

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

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

| 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;

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

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

| 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/

mariadb命令練習(xí)：管理表記錄（增刪改查）2021-09-17 16:23:07

mariadb命令練習(xí)：管理表記錄（增刪改查）
2021-09-17 16:23:07