MySQL增删改查

1.创建数据库： 使用CREATE DATABASE语句

CREATE DATABASE school;

show databases;    列出MySQL数据库管理系统的数据库列表

2.切换数据库： 使用USE语句选择要操作的数据库 

USE school；

select database ();   当前所在库

mysql> select version ();     查看版本号
+------------+
| version () |
+------------+
| 8.0.20     |
+------------+
1 row in set (0.00 sec)


show variables like 'datadir';  在数据库中查看数据库目录存储位置

mysql> show variables like 'datadir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+
1 row in set (0.01 sec)

如果使用的是InnoDB存储引擎.frm表结构文件.ibd表内数据（表数据和索引文件）一起存放在data目录下。
MySQL 8.0开始，.frm文件不再单独存在，而是被整合到了.ibd文件中。

3.创建表： 使用CREATE TABLE语句创建表 

1-create table 表名 (id int, name char(10));

2-CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(10),
    gender VARCHAR(10),
    age INT
);

3-create table student2 (
    id int primary  KEY AUTO_INCREMENT, 整数类型，主键，自动递增
    name varchar(50) not null, 可变长度字符串类型，最大长度 50，不能为空
    gender varchar(5) not null, 可变长度字符串类型，最大长度 5，不能为空
    age int not null 整数类型，不能为空
);

4-mysql> create table employee2 (
    -> id int primary  KEY AUTO_INCREMENT, id: 整数类型，主键，自动递增
    -> name varchar(50) not null,          name: 可变长度字符串类型，最大长度 50，不能为空
    -> gender varchar(5) not null,         gender: 可变长度字符串类型，最大长度 5，不能为空
    -> age int not null,                   age: 整数类型，不能为空
    -> date date,                          date: 日期类型
    -> post varchar(10),                   post: 可变长度字符串类型，最大长度 10
    -> job varchar(10),                    job: 可变长度字符串类型，最大长度 10
    -> salary double(10,2),                 salary: 双精度浮点数类型，总长度 10，小数部分长度 2
    -> office int,                         office: 整数类型
    -> depid int(11)                       depid: 整数类型，最大长度 11
    -> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc employee2;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(50) | NO   |     | NULL    |                |
| gender | varchar(5)  | NO   |     | NULL    |                |
| age    | int(11)     | NO   |     | NULL    |                |
| date   | date        | YES  |     | NULL    |                |
| post   | varchar(10) | YES  |     | NULL    |                |
| job    | varchar(10) | YES  |     | NULL    |                |
| salary | double(10,2) | YES  |     | NULL    |                |
| office | int(11)     | YES  |     | NULL    |                |
| depid  | int(11)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)



drop table  表名；  删除所在库中的数据表

delete from 表名；  清除表内容不删除结构

show tables;      查看所在库的表有哪些

select * from student；  查看指定表

 4.查看当前表结构

DESCRIBE 表名;    查看当前表结构     
desc student2;      显示数据表的结构

mysql> DESCRIBE student2;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10) | NO   |     | NULL    |                |
| gender | varchar(10) | NO   |     | NULL    |                |
| age    | int         | NO   |     | NULL    |                |
| phone  | varchar(20) | YES  |     | 未知    |                |
+--------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

5.表结构 增加删除指定列

在表中增加电话列及数据类型 默认为null
alter table student2 add column  phone varchar(20);  

mysql> select * from student;
+----+------+--------+-----+-------+
| id | name | gender | age | phone |
+----+------+--------+-----+-------+
|  1 | jack | male   |  22 | NULL  |
|  2 | mak  | male   |  17 | NULL  |
|  4 | lily | male   |  18 | NULL  |
+----+------+--------+-----+-------+
3 rows in set (0.00 sec)

在表中增加电话列及数据类型 列的默认值为‘未知’
ALTER TABLE student ADD COLUMN phone VARCHAR(20) DEFAULT '未知';

mysql> select * from student2;
+----+------+--------+-----+--------+
| id | name | gender | age | phone  |
+----+------+--------+-----+--------+
|  1 | tom  | male   |  21 | 未知   |
|  2 | lily | Female |  21 | 未知   |
|  3 | lucy | Female |  17 | 未知   |
|  4 | bibo | male   |  17 | 未知   |
+----+------+--------+-----+--------+
4 rows in set (0.00 sec)

alter table student2 add column  address varchar(255);  增加student2表地址列
                           列      地址
alter table student2 drop column address;       删除student2表地址列

6.表结构 修改 

mysql> select * from student;
+----+------+--------+-----+-------+
| id | name | gender | age | phone |
+----+------+--------+-----+-------+    电话列修改为地址
|  1 | jack | male   |  22 | NULL  |
|  4 | lily | male   |  18 | NULL  |
+----+------+--------+-----+-------+
2 rows in set (0.01 sec)

mysql> alter table student change phone address varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------+--------+-----+---------+
| id | name | gender | age | address |
+----+------+--------+-----+---------+
|  1 | jack | male   |  22 | NULL    |
|  4 | lily | male   |  18 | NULL    |
+----+------+--------+-----+---------+
2 rows in set (0.00 sec)

mysql> alter table student modify address char(50);   修改字段数据类型
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | NO   |     | NULL    |                |
| gender  | varchar(5)  | NO   |     | NULL    |                |
| age     | int         | NO   |     | NULL    |                |
| address | char(50)    | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

https://www.runoob.com/mysql/mysql-data-types.html      菜鸟教程

7.修改数据表名 

rename table 表名 to 新表名；

show tables;    查看修改结果

8.插入数据： 使用INSERT INTO语句向表中插入数据 

insert into 表名 set id=1, name='lee', age=21;

INSERT INTO student (id, name, gender, age) VALUES (1, 'John', male, 30);

INSERT INTO student (id, name, gender, age) VALUES (1, 'lily', Female, 31);

update 表名 set id=8 where name='lee';    修改lee的id编号为8

9.查询数据表： 使用SELECT语句从表中检索数据

SELECT * FROM student;

select id, name from student where gender ='Female';   查询所有女生的学号和姓名

select id,name from student2 where age >20;     查询年龄大于20的id和姓名

10.更改数据： 使用UPDATE语句更新表中的数据 

UPDATE student SET age = 35 WHERE id = 1;     修改id为1的年龄为35

11.删除数据： 使用DELETE FROM语句从表中删除数据 

DELETE FROM student WHERE id = 1;
delete from 表名 where id=1;              删除id=1行的数据

delete from student where age <18;    删除年龄小于 18 岁的学生

12. 删除表

drop table  表名；  删除所在库中的数据表

delete from 表名；  清除表内容不删除结构

13.查询过滤条件： 使用WHERE子句指定查询的过滤条件 

SELECT * FROM student WHERE age > 25;      查询年龄大于25的

14.排序： 使用ORDER BY子句对查询结果进行排序 

SELECT * FROM student ORDER BY age DESC;     按年龄从大到小

15.聚合函数： 使用聚合函数如SUM、AVG、COUNT等对数据进行统计 

SELECT COUNT(*) FROM student;    统计student表人数

SUM（） 求和
AVG（） 平均值
COUNT（） 统计

16.分组： 使用GROUP BY子句对数据进行分组 

SELECT department, AVG(salary) FROM employees GROUP BY department;

employees   员工   表
department  部门   列
AVG()       函数计算每个部门的平均薪资
salary      工资
GROUP BY    按部门分组数据

从employees表中选择department列。
对salary列使用AVG()聚合函数计算每个部门的平均薪资。
使用GROUP BY department对结果进行分组，确保每个部门只计算一次平均薪资。
执行这个查询后，您将得到一个结果集，其中包含每个部门的名称和对应的平均薪资。

17.查看表结构SQL语句 

mysql> show create table student2;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                         |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student2 | CREATE TABLE `student2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `gender` varchar(10) NOT NULL,
  `age` int NOT NULL,
  `phone` varchar(20) DEFAULT '未知',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci   |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 练习用表

CREATE database company；

mysql> CREATE TABLE company.employee(
     id int primary key AUTO_INCREMENT not null,
     name varchar(30) not null,
     age int，
     sex enum('male','female') default 'male' not null,
     hire_date date not null,
     post varchar(50) not null,
     job_description varchar(100),
     salary double(15,2) not null,
     office int,
     dep_id int
     );

INSERT INTO company.employee(name, age, sex, hire_date, post, job_description, salary, office, dep_id) VALUES 
('jack', 31, 'male', '20180202', 'instructor', 'teach', 5000, 501, 100),
('tom', 32, 'male', '20180203', 'instructor', 'teach', 5500, 501, 100),
('robin', 33, 'male', '20180202', 'instructor', 'teach', 8000, 501, 100),
('alice', 34, 'female', '20180202', 'instructor', 'teach', 7200, 501, 100),
('wing', 35, 'male', '20180202', 'hr', 'hrcc', 600, 502, 101),
('harry', 36, 'male', '20180202', 'hr', NULL, 6000, 502, 101),
('emma', 37, 'female', '20180206', 'sale', 'salecc', 20000, 503, 102),
('christine', 38, 'female', '20180205', 'sale', 'salecc', 2200, 503, 102),
('zhuzhu', 39, 'male', '20180205', 'sale', NULL, 2200, 503, 102),
('gougou', 40, 'male', '20180205', 'sale', '', 2200, 503, 102);

mysql> INSERT INTO employee(name, age, gender, hire_date, post, job_description, salary, office, dep_id) VALUES('kin',32,'male',20240710,'hr','hrcc',5500,2401,719);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO employee(name, age, gender, hire_date, post, job_description, salary, office, dep_id) 
    -> VALUES ('lily',30,'female',20240710,'sale','',5500,2401,719);
Query OK, 1 row affected (0.00 sec)

查看表结构

mysql> desc employee;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| id              | int                   | NO   | PRI | NULL    | auto_increment |
| name            | varchar(30)           | NO   |     | NULL    |                |
| age             | int                   | YES  |     | NULL    |                |
| sex             | enum('male','female') | NO   |     | male    |                |
| hire_date       | date                  | NO   |     | NULL    |                |
| post            | varchar(50)           | NO   |     | NULL    |                |
| job_description | varchar(100)          | YES  |     | NULL    |                |
| salary          | double(15,2)          | NO   |     | NULL    |                |
| office          | int                   | YES  |     | NULL    |                |
| dep_id          | int                   | YES  |     | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

查看表

mysql> mysql> select * from employee;
+----+-----------+------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name      | age  | sex    | hire_date  | post       | job_description | salary   | office | dep_id |
+----+-----------+------+--------+------------+------------+-----------------+----------+--------+--------+
|  1 | jack      |   31 | male   | 2018-02-02 | instructor | teach           |  5000.00 |    501 |    100 |
|  2 | tom       |   32 | male   | 2018-02-03 | instructor | teach           |  5500.00 |    501 |    100 |
|  3 | robin     |   33 | male   | 2018-02-02 | instructor | teach           |  8000.00 |    501 |    100 |
|  4 | alice     |   34 | female | 2018-02-02 | instructor | teach           |  7200.00 |    501 |    100 |
|  5 | wing      |   35 | male   | 2018-02-02 | hr         | hrcc            |   600.00 |    502 |    101 |
|  6 | harry     |   36 | male   | 2018-02-02 | hr         | NULL            |  6000.00 |    502 |    101 |
|  7 | emma      |   37 | female | 2018-02-06 | sale       | salecc          | 20000.00 |    503 |    102 |
|  8 | christine |   38 | female | 2018-02-05 | sale       | salecc          |  2200.00 |    503 |    102 |
|  9 | zhuzhu    |   39 | male   | 2018-02-05 | sale       | NULL            |  2200.00 |    503 |    102 |
| 10 | gougou    |   40 | male   | 2018-02-05 | sale       |                 |  2200.00 |    503 |    102 |
+----+-----------+------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)

mysql> select job_description, avg(salary) from employee group by job_description;
+-----------------+--------------+
| job_description | avg(salary)  |
+-----------------+--------------+
| teach           |  6425.000000 |
| hrcc            |   600.000000 |         不同职位的平均薪资
| NULL            |  4100.000000 |
| salecc          | 11100.000000 |
|                 |  2200.000000 |
+-----------------+--------------+
5 rows in set (0.01 sec)

mysql> select job_description, sum(age) from employee8 group by job_description;
+-----------------+----------+
| job_description | sum(age) |
+-----------------+----------+
| teach           |      130 |
| hrcc            |       35 |       不同部门人员年龄的和
| NULL            |       75 |
| salecc          |       75 |
|                 |       40 |
+-----------------+----------+
5 rows in set (0.01 sec)

mysql> select post, count(*) from employee group by post;
+------------+----------+
| post       | count(*) |
+------------+----------+
| instructor |        4 |    
| hr         |        2 |      统计员工表不同职位的人数 
| sale       |        4 |
+------------+----------+
3 rows in set (0.00 sec)