关于mysql数据库命令行的基本实现

最新推荐文章于 2024-06-12 19:31:55 发布

给孩子一口饭吃吧

最新推荐文章于 2024-06-12 19:31:55 发布

阅读量130

点赞数

文章标签：数据库 mysql 服务器

本文链接：https://blog.csdn.net/qq_47875931/article/details/125112096

版权

提示：本文档用于自我整理，对他人不具备较大参考价值。
注意： MySQL 的本体是服务器。而我们使用本机自带的命令行客户端时，就需要依赖 mysql中的配置。如果用其他的客户端就只改服务器设置即可

文章目录

一、数据库
二、表
- 1.对于表与表之间
- 2.对于表内数据
三、连接
四、正则表达式
五、MySQL事务
六、MySQL索引
- 复制表
#获取服务器元数据（所谓元数据就是MySQL作为服务器本身内部的数据，并非配置的数据）
#Mysql处理重复数据

提示：以下是本篇文章正文内容，下面案例可供参考

一、数据库

创建数据库test1

mysql> create database test1;

删除数据库test1

mysql>drop database test1;

选择数据库test

mysql> use test;

展示所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| how2java           |
| mybatis            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

查询当前数据库

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+

二、表

1.对于表与表之间

创建表student

 create table student(id int(10) primary key auto_increment,name varchar(40) not null,sex varchar(40) not null,grade int);

查看表student的结构

desc table student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | NO   |     | NULL    |                |
| sex   | varchar(40) | NO   |     | NULL    |                |
| grade | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

删除表employee

  drop table employee;

查看当前数据库内所有表

  show tables;

2.对于表内数据

1.添加主键
->创建完成表后再添加主键

mysql> alter table hurmen add primary key(id);

->创建表时添加自增auto_increment主键

create table student(id int(10) primary key auto_increment,name varchar(40) not null,sex varchar(40) not null,grade int);

2.插入数据

mysql> insert into hero(id,name) values (null,'alex');

3.删除数据

mysql> delete from hero where id=1;

4.更新数据

mysql> update hero set name='gg' where id=2;

5.查找数据

mysql> select id from teacher where class='network';

6.where条件查询
->关于where的条件查询语句中，如果出现需要查询字符为null 的数据，则可以使用is null，查询非null数据时，则使用is not null。不能使用=或者！=。
->where查询语句时，可以使用binary来设置where后的语句的字符串为区分大小写模式。
(如下语句如果没有使用binary，则当teacher表中出现name为LL和ll两个数据时，数据库的查询会把这两个数据看成值相同的数据）

mysql> select * from teacher where binary name='LL';

7.SQL like，查询获取字段中含有指定字符的所有记录时，使用SQL like语句。
SQL like语句用%来表示任意字符，如果没有写%符号的话，那like和where条件查询中的=一样的。

mysql> select * from hero where name like '%g';
+----+------+
| id | name |
+----+------+
|  2 | gg   |
+----+------+

8.Union语句：将不同表中相同列中的查询数据展示出来，不包括重复数据
Union all语句：将不同表中相同列中查询的数据展示出来，包括重复数据

mysql> select sex from student union select sex from teacher order by sex;
+-------+
| sex   |
+-------+
| men   |
| women |
+-------+

mysql> select sex from student union all select sex from teacher order by sex;
+-------+
| sex   |
+-------+
| men   |
| men   |
| men   |
| men   |
| men   |
| women |
| women |
| women |
| women |
+-------+

9.Order by :根据指定的列对结果进行排序，一般默认为升序ASC进行排序，也可以设置为DESC关键字改变其为降序排序。
->~~关于order by 排序中字符集的设置：~~
如果字符集采用的是gbk则直接在查询语句后面添加order by即可，但是如果字符集采用的是utf8则需要转码才可以进行排序 order by convert(列 using gbk);

–>~~关于MySQL的查询默认排序和order by指定排序：~~
如果在sql语句中不指定order by排序条件，那么得到的结果集的排序顺序是与查询列有关的。因为不同的查询列可能会用到不同的索引，从而导致顺序不同。

SELECT * 
FROM STUDENT
ORDER BY CONVERT(SNAME using gbk);

（升序ASC）

mysql> select * from student order by id asc;
+----+----------+-------+-------+
| id | name     | sex   | grade |
+----+----------+-------+-------+
|  1 | xiaohu   | men   |    90 |
|  2 | xiaoming | men   |    98 |
|  3 | yueyue   | women |   100 |
|  4 | ye       | women |    10 |
+----+----------+-------+-------+

（降序DESC）

mysql> select * from student order by id desc;
+----+----------+-------+-------+
| id | name     | sex   | grade |
+----+----------+-------+-------+
|  4 | ye       | women |    10 |
|  3 | yueyue   | women |   100 |
|  2 | xiaoming | men   |    98 |
|  1 | xiaohu   | men   |    90 |
+----+----------+-------+-------+

10.Group by对一个或者多个列的结果进行分组，一般结合函数使用 function(SUM,COUNT,AVG)

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

mysql> select * from student;
+----+----------+-------+-------+
| id | name     | sex   | grade |
+----+----------+-------+-------+
|  1 | xiaohu   | men   |    90 |
|  2 | xiaoming | men   |    98 |
|  3 | yueyue   | women |   100 |
|  4 | ye       | women |    10 |
+----+----------+-------+-------+
mysql> select sex,COUNT(*) from student group by sex;
//在student表中对性别进行计数count分组
+-------+----------+
| sex   | COUNT(*) |
+-------+----------+
| men   |        2 |
| women |        2 |
+-------+----------+
mysql> select sex,COUNT(*) from student where name like '%e' group by sex;
//在student表中姓名以‘e’结尾的数据对性别进行计数count分组
+-------+----------+
| sex   | COUNT(*) |
+-------+----------+
| women |        2 |
+-------+----------+

11.alter修改数据表名或者数据表字段
删除表student中的字段name
->删除表字段时如果表中字段只剩下一个时则不可以继续进行删除

mysql> alter table student drop name;
//使用alter和drop命令来删除表student的字段name

在表student中添加字段class，默认自动添加到数据表字段的末尾

mysql> alter table student add class varchar(40);

添加字段 i 到表首first

mysql> alter table student add i int first;

添加字段m到指定位置(class字段之后)

mysql> alter table student add m int after  class;

三、连接

使用join对数据库的表进行连接，按功能来说分为内连接inner join、左连接left join、右连接right join。

inner join

在命令行中可以写成join，通过两表的相同字段来建立两表间的连接。
student (id, name, sex, grade); teacher （id, name,sex, class)

mysql> select a.grade,b.class from student a join teacher b on a.sex=b.sex;
//通过命名为a的student表和命名为b的teacher表中的sex连接，查找a.grade和b.class

以上等价于where语句的

select a.grade,b.class from student a,teacher b where a.sex = b.sex;

left join

以左表为准，将右表对应连接到左表上。

mysql> select * from student a left join teacher b on a.id=b.id;
+----+----------+-------+-------+------+-------+-------+---------+
| id | name     | sex   | grade | id   | name  | sex   | class   |
+----+----------+-------+-------+------+-------+-------+---------+
|  1 | xiaohu   | men   |    90 |    1 | dage  | women | network |
|  2 | xiaoming | men   |    98 |    2 | huang | women | math    |
|  3 | yueyue   | women |   100 |    3 | lil   | men   | read    |
|  4 | ye       | women |    10 |    4 | LL    | men   | PE      |
+----+----------+-------+-------+------+-------+-------+---------+

right join

以右表为准，将左表对应连接到右表上。

mysql> select * from student a right join teacher b on a.id=b.id;
+------+----------+-------+-------+----+-------+-------+---------+
| id   | name     | sex   | grade | id | name  | sex   | class   |
+------+----------+-------+-------+----+-------+-------+---------+
|    1 | xiaohu   | men   |    90 |  1 | dage  | women | network |
|    2 | xiaoming | men   |    98 |  2 | huang | women | math    |
|    3 | yueyue   | women |   100 |  3 | lil   | men   | read    |
|    4 | ye       | women |    10 |  4 | LL    | men   | PE      |
| NULL | NULL     | NULL  |  NULL |  5 | ll    | men   | pe      |
+------+----------+-------+-------+----+-------+-------+---------+

四、正则表达式

mysql使用RegExp操作费来对正则表达式进行匹配。
正则表达式符号作用说明
查找name字段中以‘xiao’开头的所有数据

mysql> select * from student where name regexp '^xiao';

查找name字段中以’e’结尾的所有数据

mysql> select * from student where name regexp 'e$';

查找name字段中含有’u’字符的所有数据

mysql> select * from student where name regexp 'u';

查找以元音开头的或者’e’结尾的所有数据（或者| 、且&）

mysql> select * from student where name regexp '^[aeiou]|e$';

五、MySQL事务

在MySQL中只有使用了InnDB数据库引擎才能够支持事务。
事务含有四个特性：原子性、一致性、隔离性、持久性。
1.原子性：就是事务中所包含的操作要么全部完成，要么都不完成，不存在完成一半的情况；
2.一致性：事务开始前和结束后，写入的数据没有破坏数据库的完整性，则完全符合数据库的预设规则；
3.隔离性：数据库允许多个并发事务进行数据库的读写和修改数据，隔离性可以防止并发事务由于交叉执行而导致修改数据不一致，事务的隔离性分为四个级别：读取已提交、读取未提交、可重复读、串行化；
4.持久性：就是数据库的数据一旦经过修改就是永久性的，不会自己变回去。

事务处理的两种方法：1.使用begin启动-rollback回滚-commit提交来实现。2.使用set来改变mysql的自动提交模式。
-> Savepoint：数据库事务处理中实现的子事务，也是嵌套事务的方法。Rollback回滚方法可以设置保留点savepoint，执行多余操作时，回滚到想要返回的语句前，可以使用savepoint进行回滚，事务处理完成后再进行savepoint释放。

SAVEPOINT savepoint_name; //声明一个savepoint
ROLLBACK TO savepoint_name; //回滚到savepoint
RELEASE SAVEPOINT savepoint_name; //删除指定保留点

六、MySQL索引

使用alter命令来添加和删除索引
添加索引

mysql> alter table student add index(id);

删除索引

mysql>alter table student drop index id;

显示索引信息

mysql>show index from student;
//展示student表中的相关索引信息

复制表

第一、只复制表结构到新表
-> create table 新表 select * from 旧表 where 1=2
或者create table 新表 like 旧表。
第二、复制表结构及数据到新表
-> create table新表 select * from 旧表

#获取服务器元数据（所谓元数据就是MySQL作为服务器本身内部的数据，并非配置的数据）

元数据

#Mysql处理重复数据

防止MySQL数据重复的方法：在MySQL数据表中设置指定字段为主键primary key或者唯一unique来确保数据的唯一性。

添加了unique索引后，即使添了多条一样的数据也不会导致数据重复。
使用insert ignore into插入数据时，在设置了记录的唯一性后，如果插入与主键和唯一键相同的数据，则会先将之前的数据删除掉然后再插入新纪录。