mysql的增删改查操作-CSDN博客

本文链接：https://blog.csdn.net/weixin_41860426/article/details/132393811

数据库的操作

查看数据库：mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
注：
1：information_schema这数据库保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型不访问权限等。
2：performance_schema 这是MySQL5.5新增的一个性能优化的引擎：命名PERFORMANCE_SCHEMA
主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表
3：mysql库是系统库，里面保存有账户信息，权限信息等。
4：mysql5.7增加了sys 系统数据库，通过这个库可以快速的了解系统的元数据信息
元数据是关于数据信息的数据，如数据库名或表名，列的数据类型，或访问权限等。

mysql> show databases \G #以行的方式显示
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
*************************** 3. row ***************************
Database: performance_schema

创建数据库：语法：create database 数据库名;
例：mysql> create database HA; #创建一个名为HA的数据库
mysql> create database HA-test;
Query OK, 1 row affected (0.00 sec)

[root@localhost mysql]# ls ##查看数据库存放目录, ha@002dtest就是刚创建的库
auto.cnf ha@002dtest ibdata1 ib_logfile1 mysql mysql.pid mysql.socket.lock sys
error.log ib_buffer_pool ib_logfile0 ibtmp1 mysql.log mysql.socket performance_schema test
[root@localhost mysql]# pwd
/data/mysql

选择要操作的数据库：使用USE语句将会选择一个数据库成为当前数据库。后面的操作默认都在被选择的数据库中操作。
mysql> use HA-test;
Database changed

查看自己所处的位置及默认所在的位置
mysql> select database();
±-----------+
| database() |
±-----------+
| HA-test |
±-----------+
1 row in set (0.00 sec)

删除数据库： #要慎重考虑之后再执行
mysql> drop database HA-test;
删除没有任何提示，要慎重操作

方法2：直接到数据库存放目录移出就行，不建议使用此方法
cd /usr/local/mysql/data/
mv HA@002dtest /tmp
mysql> show databases;

关于表的操作：
创建表：
语法：create table 表名 (字段名类型, 字段名类型, 字段名类型);
mysql> create table student(id int(20),name char(40),age int(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
±---------------+
| Tables_in_test |
±---------------+
| student |
| test2 |
±---------------+
2 rows in set (0.00 sec)
student这张表就已经创建好了。

查看表的结构：
Describe
mysql> desc student;
±------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±---------±-----±----±--------±------+
| id | int(20) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
±------±---------±-----±----±--------±------+
3 rows in set (0.00 sec)

查看创建表执行了哪些命令：
mysql> show create table student \G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE student (
id int(20) DEFAULT NULL,
name char(40) DEFAULT NULL,
age int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ENGINE=InnoDB指表的默认存储引擎， DEFAULT CHARSET=utf8指表的默认字符集，也可以在建表的时候设定
mysql> create table student2(id int(20),name char(40),age int(10))ENGINE=MyISAM DEFAULT CHARSET=utf8;

删除表：
mysql> drop table student2;
Query OK, 0 rows affected (0.01 sec)

修改表名称ALTER：
语法：alter table 表名 rename 新表名;
mysql> alter table student rename students; #studen表名修改为students
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
±---------------+
| Tables_in_test |
±---------------+
| students |
| test2 |
±---------------+
2 rows in set (0.00 sec)

修改表中的字段类型：
语法：alter table 表名 modify 要修改的字段名要修改的类型;
mysql> desc students;
±------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±---------±-----±----±--------±------+
| id | int(20) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
±------±---------±-----±----±--------±------+
3 rows in set (0.00 sec)
mysql> alter table students modify id int(30);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc students;
±------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±---------±-----±----±--------±------+
| id | int(30) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
±------±---------±-----±----±--------±------+
3 rows in set (0.00 sec)

修改表中的字段类型和字段名称：
语法：alter table 表名 change 原字段名新字段名新字段类型;
mysql> desc students;
±------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±---------±-----±----±--------±------+
| id | int(30) | YES | | NULL | |
| name | char(40) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
±------±---------±-----±----±--------±------+
3 rows in set (0.00 sec)

mysql> alter table students change name stname char(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students;
±-------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±---------±-----±----±--------±------+
| id | int(30) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
±-------±---------±-----±----±--------±------+
3 rows in set (0.00 sec)

注：CHANGE 和MODIFY的区别：
CHANGE 对列进行重命名和更改列的类型，需给定旧的列名称和新的列名称、当前的类型。 MODIFY 可以改变列的类型，此时不需要重命名（不需给定新的列名称）

在表中添加字段：
语法：alter table 表名 add 字段名字段类型；
mysql> alter table students add sex enum(‘M’,‘W’);
mysql> alter table students add sex enum(‘M’,‘W’);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc students;
±-------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±--------------±-----±----±--------±------+
| id | int(30) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
| sex | enum(‘M’,‘W’) | YES | | NULL | |
±-------±--------------±-----±----±--------±------+
4 rows in set (0.00 sec)

指定位置添加字段：
在第一列添加一个字段：
mysql> alter table studnets add uid int(10) first;
ERROR 1146 (42S02): Table ‘test.studnets’ doesn’t exist
mysql> alter table students add uid int(10) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc students;
±-------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±--------------±-----±----±--------±------+
| uid | int(10) | YES | | NULL | |
| id | int(30) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
| sex | enum(‘M’,‘W’) | YES | | NULL | |
±-------±--------------±-----±----±--------±------+
5 rows in set (0.00 sec)

在age后面添加一个address字段：
mysql> alter table students add address char(40) after age;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc students;
±--------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------±--------------±-----±----±--------±------+
| uid | int(10) | YES | | NULL | |
| id | int(30) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
| address | char(40) | YES | | NULL | |
| sex | enum(‘M’,‘W’) | YES | | NULL | |
±--------±--------------±-----±----±--------±------+
6 rows in set (0.00 sec)

删除表中字段：
语法：alter table 表名 drop 字段名 ;
mysql> alter table students drop address;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc students;
±-------±--------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-------±--------------±-----±----±--------±------+
| uid | int(10) | YES | | NULL | |
| id | int(30) | YES | | NULL | |
| stname | char(20) | YES | | NULL | |
| age | int(10) | YES | | NULL | |
| sex | enum(‘M’,‘W’) | YES | | NULL | |
±-------±--------------±-----±----±--------±------+
5 rows in set (0.00 sec)

关于记录的操作：
插入字段<记录>INSERT：
语法：insert into 表名 values (字段值1,字段值2, 字段值3);
mysql> insert into student values(2,‘lisi’,24);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 2 | lisi | 24 |
±-----±-----±-----+
1 row in set (0.00 sec)

插入记录时要对应相对的类型
mysql> insert into student values(3,‘zhangsan’,22),(3,‘wangwu’,25);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from student;
±-----±---------±-----+
| id | name | age |
±-----±---------±-----+
| 2 | lisi | 24 |
| 3 | zhangsan | 22 |
| 3 | wangwu | 25 |
±-----±---------±-----+
3 rows in set (0.00 sec)
同时插入多条，使用，分开
mysql> insert into student (id,name) values(5,‘kaka’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
±-----±---------±-----+
| id | name | age |
±-----±---------±-----+
| 2 | lisi | 24 |
| 3 | zhangsan | 22 |
| 3 | wangwu | 25 |
| 5 | kaka | NULL |
±-----±---------±-----+
4 rows in set (0.00 sec)
查询表中记录：
语法：select * from 表名称；
mysql> select * from student;
±-----±---------±-----+
| id | name | age |
±-----±---------±-----+
| 2 | lisi | 24 |
| 3 | zhangsan | 22 |
| 3 | wangwu | 25 |
| 5 | kaka | NULL |
±-----±---------±-----+
4 rows in set (0.00 sec)
当字段比较多的时候我们也可以使用\G
mysql> select * from student \G
*************************** 1. row ***************************
id: 2
name: lisi
age: 24
*************************** 2. row ***************************
id: 3
name: zhangsan
age: 22
*************************** 3. row ***************************
id: 3
name: wangwu
age: 25
*************************** 4. row ***************************
id: 5
name: kaka
age: NULL
4 rows in set (0.00 sec)
只查询表中某个字段的内容：
mysql> select name from student;
±---------+
| name |
±---------+
| lisi |
| zhangsan |
| wangwu |
| kaka |
±---------+
4 rows in set (0.00 sec)
mysql> select id,name from student;
±-----±---------+
| id | name |
±-----±---------+
| 2 | lisi |
| 3 | zhangsan |
| 3 | wangwu |
| 5 | kaka |
±-----±---------+
4 rows in set (0.00 sec)

查看别的数据库的表或者不在本数据库上进行查看：
语法：SELECT 字段 FROM 数据库名.表名;
Database changed
mysql> select * from ha.student; #查看某个数据库下指定的表内容，数据库名.表名

±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 1 | coco | 20 |
±-----±-----±-----+
1 row in set (0.00 sec)

删除记录：
删除id为3的行
mysql> select * from student;
±-----±---------±-----+
| id | name | age |
±-----±---------±-----+
| 2 | lisi | 24 |
| 3 | zhangsan | 22 |
| 3 | wangwu | 25 |
| 5 | kaka | NULL |
±-----±---------±-----+
4 rows in set (0.00 sec)
mysql> select * from student;
±-----±---------±-----+
| id | name | age |
±-----±---------±-----+
| 2 | lisi | 24 |
| 3 | zhangsan | 22 |
| 3 | wangwu | 25 |
| 5 | kaka | NULL |
±-----±---------±-----+
4 rows in set (0.00 sec)

mysql> delete from student where id =3;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from student;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 2 | lisi | 24 |
| 5 | kaka | NULL |
±-----±-----±-----+
2 rows in set (0.00 sec)
删除age为空的行
mysql> select * from student;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 2 | lisi | 24 |
| 5 | kaka | NULL |
±-----±-----±-----+
2 rows in set (0.00 sec)

mysql> delete from student where age is null;
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 2 | lisi | 24 |
±-----±-----±-----+
1 row in set (0.00 sec)
更新记录
mysql> select * from student;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 2 | lisi | 24 |
| 5 | kaka | NULL |
±-----±-----±-----+
2 rows in set (0.00 sec)

mysql> update student set age=22 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 2 | lisi | 24 |
| 5 | kaka | 22 |
±-----±-----±-----+
2 rows in set (0.00 sec)
mysql> update student set id=2; #所有的id都变为2
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0

mysql> select * from student;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 2 | lisi | 24 |
| 2 | kaka | 22 |
±-----±-----±-----+
2 rows in set (0.00 sec)
update student set name=‘zhangsan’,age=21 where id=1;
同时更新多个字段时候用，号隔开
mysql> select * from student;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 1 | lisi | 24 |
| 2 | kaka | 22 |
±-----±-----±-----+
2 rows in set (0.00 sec)

mysql> update student set name=‘zhangsan’,age=21 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from student;
±-----±---------±-----+
| id | name | age |
±-----±---------±-----+
| 1 | zhangsan | 21 |
| 2 | kaka | 22 |
±-----±---------±-----+
2 rows in set (0.00 sec)

SQL基础条件查询语句语法：
select 字段名1，字段名2 from 表名 [where 条件];
mysql> select id,name from student;
±-----±---------+
| id | name |
±-----±---------+
| 1 | zhangsan |
| 2 | kaka |
±-----±---------+
2 rows in set (0.00 sec)
2：去重复查询distinct
mysql> select distinct name,age from students;
±-------±-----+
| name | age |
±-------±-----+
| zhangs | 21 |
| lis | 24 |
| jk | 24 |
| lo | 26 |
| io | 25 |
mysql> select distinct id,name,age from students where id=3;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 3 | jk | 24 |
select distinct * from students; mysql的distinct可以对*使用

3：使用AND和OR进行多条件查询or和and 同时存在时，先算and的两边值,逻辑与先执行
mysql> select id,name,age from students where id>3 and age>25;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 5 | lo | 26 |
±-----±-----±-----+
mysql> select id,name,age from students where id>3 or age>25;
±-----±-----±-----+
| id | name | age |
±-----±-----±-----+
| 5 | lo | 26 |
| 6 | io | 25 |
±-----±-----±-----+
select * from students where stname=‘zhangsan’ and (age=21 or age=24);
注意and和or都是用的时候的逻辑关系

4：MYSQL区分大小写查询：
Mysql查询默认是不区分大小写的
mysql> select name from students where name=‘jk’;
±-----+
| name |
±-----+
| jk |
| jk |
| JK |
±-----+

解决
mysql> select * from students where binary name=‘jk’; [ˈbaɪnəri]
±-----±-----±-----±-----+
| id | name | age | sex |
±-----±-----±-----±-----+
| 3 | jk | 24 | W |
| 3 | jk | 24 | W |
±-----±-----±-----±-----+
BINARY是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写。

5：MYSQL查询排序：语法：select distinct 字段1，字段2 from 表名order by 字段名;
默认为升序 asc
mysql> select distinct id from students order by id asc;
±-----+
| id |
±-----+
| 2 |
| 3 |
| 5 |
| 6 |
| 8 |
±-----+
mysql> select distinct id from students order by id desc;
±-----+
| id |
±-----+
| 8 |
| 6 |
| 5 |
| 3 |
| 2 |
±-----+