mysql 2 -- 数据库基本操作、数据表的操作、mysql查询操作

一、数据库基本操作

1、数据库的登录及退出

连接数据库：

mysql -u用户名 -h主机地址(省略代表本机) -p 密码（格式为'123...'）;

注：

刚下载安装的时候需要通过管理员进入

退出数据库,以下三种方式都可以：

exit
quit
ctrl+d

示例：

mysql>
mysql> exit
Bye
stu@stu-virtual-machine:~$

2、查看所有数据库

命令：show databases;
要注意所有sql语句结尾都有 ；分号

示例：

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testlg |
+--------------------+
6 rows in set (0.01 sec)
mysql>

3、显示数据库版本

命令：select version();

示例：

mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 8.0.25-0ubuntu0.20.04.1 |
+-------------------------+
1 row in set (0.00 sec)
mysql>

4、显示时间

命令： select now();

示例：

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-06-29 21:29:49 |
+---------------------+
1 row in set (0.00 sec)
mysql>

5、创建数据库

命令：

create database 数据库名
create database 数据库名 charset=utf8;

示例：

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database testdb charset=utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql>

6、查看创建数据库的语句

命令： show create database 数据库名

示例：

mysql> show create database testdb;
+----------+--------------------------------------------------------------------
---------
| Database | Create Database
|
+----------+--------------------------------------------------------------------
---------
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */
+----------+--------------------------------------------------------------------
---------
1 row in set (0.00 sec)
mysql>

7、查看当前使用的数据库

命令 ：select database();
如下图为null代表没有选择使用的数据库。

mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)
mysql>

如果选择了某个数据库，则显示如下：

mysql> select database();
+------------+
| database() |
+------------+
| testdb |
+------------+
1 row in set (0.00 sec)
mysql>

8、查看当前用户

查看当前登录的是哪个用户
命令：select user();

示例：

mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

9、使用某个数据库

登录到mysql后，里面创建有很多数据库，
选择要使用的某一个数据库： use 数据库名；

mysql> use testdb;
Database changed
mysql>

10、删除数据库

命令： drop database 数据库名；

示例：

mysql> drop database testdb;
Query OK, 0 rows affected (0.00 sec)
mysql>

二、数据表的操作

1、查看当前数据库中所有表

命令： show tables;

示例：

mysql> show tables;
Empty set (0.00 sec)
mysql>

2、创建表

创建表时，需要指定各个字段的类型，常见类型如下：
数值类型(部分）：

字符串类型（部分）：

日期时间类型

约束

主键 primary key : 物理上存储的顺序
非空 not null : 此字段不允许填写空值
唯一unique: 此字段的值不允许重复
默认default: 当不填写此值时，会使用默认值。如果填写时，以填写的值为准
外键foreign key : 对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则写成功，如果不存在则写失败。 虽然外键约束可以保证数据的有效性，但是在进行数据的crud(增加，修改，删除，查询)时，都会降低数据库的性能。
auto_increment 表示自动增长

创建表的命令 : create table 数据库表名字 （ 字段 类型 约束 [, 字段，类型 约束] ); 中括弧中的可以省
未使用约束 示例：

create table student1(id int, name varchar(30)); 

对id 字段使用约束 示例：

create table student2(id int primary key not null auto_increment, name varchar(30));

执行以上示例，创建 student1,student2表。

mysql> create table student1(id int, name varchar(30));
Query OK, 0 rows affected (0.04 sec)
mysql> create table student2(id int primary key not null auto_increment, name
varchar(30));
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student1 |
| student2 |
+------------------+
2 rows in set (0.00 sec)
mysql>

创建一个students的表

mysql> create table students(
-> id int unsigned not null auto_increment primary key,
-> name varchar(30),
-> age tinyint unsigned default 0,
-> high decimal(5,2),
-> gender enum("男","女") default "男",
-> cls_id int unsigned
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>

3、查看表结构

查看表结构也就是的各个字段的信息。

mysql> desc student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql>

mysql> desc student2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql>

mysql> desc students;
+--------+-------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| age | tinyint unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女') | YES | | 男 | |
| cls_id | int unsigned | YES | | NULL | |
+--------+-------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

4、查看创建表的语句

语句： show create table 表名；

mysql> show create table student1;
+----------+---------------------------------------------------------------------
--------
| Table | Create Table
+----------+---------------------------------------------------------------------
--------
| student1 | CREATE TABLE `student1` (
`id` int DEFAULT NULL,
`name` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
+----------+---------------------------------------------------------------------
--------
1 row in set (0.05 sec)
#另一种查看方式以\G结尾，格式更好一些
mysql> show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int unsigned DEFAULT NULL,
`username` varchar(20) DEFAULT NULL,
KEY `in_id` (`id`),
KEY `in_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
mysql>

5、向表中插入，更新，删除数据

（1）插入数据

命令： insert into 表名values(字段1的值，字段2的值...);

mysql> insert into students values(1,"小明",23,162.22,"男",1001);
Query OK, 1 row affected (0.00 sec)

另一种方式：可以调整字段顺序，也可以省略一些可能的字段

mysql> insert into students(name,id) values("小明",101);

（2）更新数据

使用update 更新记录，示例如下：

mysql> select * from student;
+------+--------+
| id | name |
+------+--------+
| 101 | 小明 |
| 102 | 小张 |
+------+--------+
2 rows in set (0.00 sec)
mysql> update student set name='小李' where id=102;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+------+--------+
| id | name |
+------+--------+
| 101 | 小明 |
| 102 | 小李 |
+------+--------+
2 rows in set (0.00 sec)
mysql>

（3）删除数据

使用delete删除表中的行，可以删除指定行，也可以删除所有行

mysql> select * from student;
+------+--------+
| id | name |
+------+--------+
| 102 | 小李 |
| 101 | 小明 |
+------+--------+
2 rows in set (0.00 sec)
mysql> delete student from student where id=101;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小明 | 23 | 162.22 | 男 | 1001 |
+----+--------+------+--------+--------+--------+
1 row in set (0.00 sec)
mysql>
+------+--------+
| id | name |
+------+--------+
| 102 | 小李 |
+------+--------+
1 row in set (0.00 sec)
mysql>

6、查看表中数据

命令： select * from 表名;

mysql> select * from students;
+----+--------+------+--------+--------+--------+
| id | name | age | high | gender | cls_id |
+----+--------+------+--------+--------+--------+
| 1 | 小明 | 23 | 162.22 | 男 | 1001 |
+----+--------+------+--------+--------+--------+
1 row in set (0.00 sec)
mysql>

也可以指定查询某几个字段: select id, name from students;

mysql> select id, name from students;
+----+--------+
| id | name |
+----+--------+
| 1 | 小明 |
+----+--------+
1 row in set (0.00 sec)
mysql>

mysql中注释使用

mysql> -- select id, name from students;
mysql>

7、修改表名字

修改表的名字，使用 alter table 原表名 rename [to] 新表名; 其中to可以省略

ysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student1 |
| student2 |
+------------------+
2 rows in set (0.00 sec)
mysql> alter table student1 rename student4;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| student2 |
| student4 |
+------------------+
2 rows in set (0.01 sec)
mysql>

8、修改表字段信息

1）修改表–添加字段

命令：alter table 表名 add 列名 类型;

示例：

alter table students add birthday datetime;

mysql> alter table students add birthday datetime;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>

2）修改字段-- 重新命名

命令：alter table 表名 change 原字段名 新字段名 类型及约束;
示例：

alter table students change birthday birth datetime;

3）修改字段-- 不改名字

命令：alter table 表名 modify 列名 类型及约束;
示例：

alter table students modify birth date not null;

4）修改表–删除字段

命令：alter table 表名 drop 列名；
示例：

alter table students drop birth;

5）修改字段排列位置

命令：ALTER TABLE 表名 MODIFY 属性名1 数据类型 FIRST | AFTER 属性名2;

6）修改表的存储引擎：

常见引擎：MyISAM, InnoDB

示例：

mysql> alter table student3 engine=myisam;
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>

9 、删除表

命令：drop table 表名;

示例：

drop table students;

三、mysql查询操作

1 、基本查询

查询所有字段

select * from 表名；

查询指定字段

select 列1， 列2，... from 表名；
select 表名.字段 ... from 表名;

使用as给字段起别名

select 字段 as 名字 ... from 表名；

使用as给表起别名

select 别名.字段 .... from 表名 as 别名；

消除重复行

命令：select 行名 from 表名;

示例：

distinct 字段
mysql> select addr from student ;
+--------+
| addr |
+--------+
| 成都 |
| 西安 |
| 沈阳 |
| 西安 |
| 西安 |
+--------+
5 rows in set (0.00 sec)
mysql> select distinct addr from student ;
+--------+
| addr |
+--------+
| 成都 |
| 西安 |
| 沈阳 |
+--------+
3 rows in set (0.00 sec)
mysql>

2 、条件

比较运算：

命令：select ... from 表名 where ...

大于 > , 小于 < , 大于等于 >=, 小于等于 <=, 相等 = , 不相等 !=

示例：

#查询年龄大于18岁的学生信息
select * from student where age > 18;
#查询年龄为18的所有学生，注意等号只有一个
select * from student where age = 18;

逻辑运算： and, or , not

示例：

#查询年龄在18到24岁之间的所有学生
select * from student where age > 18 and age < 24;
#查询18岁以上的所有女性
select * from student where age > 18 and gender = "女";
#查询年龄在18岁以上，或者 身高在180及以上的学生
select * from student where age > 18 or height >= 180;
#不在 18岁以上，并且是男生
select *from student where not age > 18 and gender = "男";
#不在 18岁以上的男生 这个范围的学生
select * from student where not (age > 18 and gender = "男");

模糊查询

命令：like : % 替换一个或多个， _ 替换一个

#查询姓名中以 “小” 开始的名字
select name from student where name like "小%";
#查询姓名中 含有“小”字，的所有名字
select name from student where name like "%小%";
#查询有两个字的名字
select name from student where name like "__";
#查询名字至少有2个字的名字
select name from student where name like "__%";

范围查询

in, not int, 不连续范围
between … and … ， not between … and … 连续范围
空判断： is null
判非空： is not null

示例：

#查询年龄为18,20,24的学生信息
select name,age from student where age = 18 or age=20 or age = 34 ;
select name,age from student where age in (18,20,24);
#查询年龄不是18,20,24的学生信息
select name ,age from student where age not in (18,20,24);
#查询年龄在18到24岁之间的所有学生信息
select name, age from student where age between 18 and 24;
#查询年龄不在18到24岁之间的
select * from student where age not between 18 and 24;
#查询学生住址为空的学生
select * from student where addr is null;

3 、排序

命令：order by 字段；
默认是升序 从小到大 asc, 需要降序从大到小 ，加上 desc 可以对多个字段进行判断

#查询年龄在18岁以上的学生，年龄安从小到大
select * from student where age > 18 order by age;
#查询年龄在18岁以上的学生，年龄安从大到小
select * from student where age > 18 order by age desc;
#查询年龄在18 到24之间的学生，按照年龄从小到大，身高从高到低排序
select * from student where (age between 18 and 34 ) order by age asc, height
desc;

4 、聚合函数

count() 总数， max() 最大值， min() 最小值, sum()求和， avg() 平均值， round()四舍五入

#查询男生有多少人
select count(*) from student where gender='男';
#查询最大年龄
select max(age) from student;
#查询年龄最小值
select min(age) from student;
#计算所有人的年龄总和
select sum(age) from student;
#计算平均年龄
select avg(age) from student;
或 select sum(age) / count(*) from student;
#算平均年龄，设置平均年龄的小数位数
select round(sum(age) / count(*),2) from student;

5 、分组查询

命令：group by 分组；

#安装性别分组
select * from student group by gender;
#计算男生和女生的人数
select gender, count(*) from student group by gender;
#计算每个性别中，年龄最大的
select gender,max(age) from student group by gender;
#查看性别分组中，每个组的人名
select gender,group_concat(name) from student group by gender;

having 在分组后进行筛选
6 、分页
7 、连接查询
8 、自关联
9 、子查询