mysql学习

最新推荐文章于 2023-01-23 21:33:04 发布

Zoe Zhao

最新推荐文章于 2023-01-23 21:33:04 发布

阅读量355

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_41724761/article/details/106612991

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

mysql

基础操作

1.登陆

mysql -u root -p

D:\Program Files\mysql-8.0.20-winx64\bin>  mysql -u root -p

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

2.退出

mysql> exit;
Bye

3.SQL 包含以下 4 部分：

1）数据定义语言（Data Definition Language，DDL）
用来创建或删除数据库以及表等对象，主要包含以下几种命令：
DROP：删除数据库和表等对象
CREATE：创建数据库和表等对象
ALTER：修改数据库和表等对象的结构

2）数据操作语言（Data Manipulation Language，DML）
用来变更表中的记录，主要包含以下几种命令：
SELECT：查询表中的数据
INSERT：向表中插入新数据
UPDATE：更新表中的数据
DELETE：删除表中的数据

3）数据查询语言（Data Query Language，DQL）

表中的数据。

4）数据控制语言（Data Control Language，DCL）
用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：
GRANT：赋予用户操作权限
REVOKE：取消用户的操作权限
COMMIT：确认对数据库中的数据进行的变更
ROLLBACK：取消对数据库中的数据进行的变更

4.查看创建数据库
1)查看所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
| zzm                |
+--------------------+
6 rows in set (0.00 sec)

2)创建数据库

mysql> create database test_db;
Query OK, 1 row affected (0.64 sec)

3)查看类似的数据库

mysql> SHOW DATABASES LIKE 'test_db';
+--------------------+
| Database (test_db) |
+--------------------+
| test_db            |
+--------------------+
1 row in set (0.03 sec)

mysql> SHOW DATABASES LIKE '%test%';
+--------------------+
| Database (%test%)  |
+--------------------+
| db_test            |
+--------------------+
| db_test_db         |
+--------------------+
| test_db            |
+--------------------+
3 row in set (0.03 sec)

5.修改数据库

mysql> CREATE DATABASE test_db
    -> DEFAULT CHARACTER SET gb2312
    -> DEFAULT COLLATE gb2312_chinese_ci;
mysql> SHOW CREATE DATABASE test_db;
+----------+--------------------------------------------------------+
| Database | Create Database                                        |
+----------+--------------------------------------------------------+
| test_db  | CREATE DATABASE `test_db` /*!40100 DEFAULT CHARACTER SET gb2312 */|
+----------+--------------------------------------------------------+
1 row in set (0.00 sec)

6.删除数据库

mysql> drop database test_drop;
Query OK, 0 rows affected (0.79 sec)

7.选择数据库

mysql> use test_db;
Database changed

数据类型

1.数值类型
2.日期类型
3.字符串类型

表的基本操作

1.创建表
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

mysql> create table school(id int(11),name varchar(25),deptId int(11),salary float);
Query OK, 0 rows affected, 2 warnings (1.59 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| school            |
+-------------------+
1 row in set (0.02 sec)

2.查看表结构

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| school            |
+-------------------+
1 row in set (0.00 sec)


mysql> show create table school\G;
*************************** 1. row ***************************
       Table: school
Create Table: CREATE TABLE `school` (
  `id` int DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.38 sec)

mysql> describe school;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int         | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.10 sec)

3.修改数据表

//修改表名
mysql> alter table school RENAME TO school_changed;
Query OK, 0 rows affected (0.86 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| school_changed    |
+-------------------+
1 row in set (0.00 sec)

//修改表字符集
mysql> ALTER TABLE school_changed DEFAULT CHARACTER SET gb2312;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

//修改字段
mysql> ALTER TABLE school_changed CHANGE deptid depthid int(11);
Query OK, 0 rows affected, 1 warning (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc school_changed;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(25) | YES  |     | NULL    |       |
| depthid | int         | YES  |     | NULL    |       |
| salary  | float       | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

//修改字段数据类型
mysql> ALTER TABLE school_changed MODIFY depthid float;
Query OK, 0 rows affected (1.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc school_changed;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int         | YES  |     | NULL    |       |
| name    | varchar(25) | YES  |     | NULL    |       |
| depthid | float       | YES  |     | NULL    |       |
| salary  | float       | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

//删除字段
mysql> ALTER TABLE tb_emp1
    -> DROP col2;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

//添加字段
mysql> ALTER TABLE student ADD age INT(4);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| col1   | int(11)     | YES  |     | NULL    |       |
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

4.删除数据表

mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected (0.64 sec)

有外键约束的情况下如果直接删除父表，会破坏数据表的完整性，也会删除失败。

删除父表有以下两种方法：
先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

mysql> create table t1(id int(11) primary key,name varchar(22),location varchar(50));
Query OK, 0 rows affected, 1 warning (1.02 sec)

mysql> create table t2(id int(11)primary key,name varchar(22),deptid int(11),
    -> constraint t1 foreign key (deptid) references t1(id)
    -> );
Query OK, 0 rows affected, 2 warnings (1.52 sec)

mysql> alter table t2 drop foreign key t1;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table t1;
Query OK, 0 rows affected (0.46 sec)

5.总结

– 查看数据表结构
– describe pet;
desc pet;

– 查询表
SELECT * from pet;

– 插入数据
INSERT INTO pet VALUES (‘puffball’, ‘Diane’, ‘hamster’, ‘f’, ‘1990-03-30’, NULL);

– 修改数据
UPDATE pet SET name = ‘squirrel’ where owner = ‘Diane’;

– 删除数据
DELETE FROM pet where name = ‘squirrel’;

– 删除表
DROP TABLE myorder;

约束

主键约束

直接定义或者是在定义完所有字段之后指定主键，语法格式如下：
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

//单字段主键
mysql> create table t3(
    -> id int(11) primary key,
    -> name varchar(25),
    -> deptd int(11)
    -> );
Query OK, 0 rows affected, 2 warnings (1.24 sec)

mysql> desc t3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
| deptd | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//联合主键
mysql> create table t4(id int(11),name varchar(25),salary float, primary key(id,name));
Query OK, 0 rows affected, 1 warning (1.30 sec)

mysql> desc t4;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | NO   | PRI | NULL    |       |
| name   | varchar(25) | NO   | PRI | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//添加主键约束
//ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);
mysql> create table t5(id int(11),name varchar(25)
    -> );
Query OK, 0 rows affected, 1 warning (1.20 sec)

mysql> alter table t5 add primary key(id);
Query OK, 0 rows affected (1.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

//删除主键约束
//ALTER TABLE <数据表名> DROP PRIMARY KEY;

//自增约束
mysql> CREATE TABLE user (
    ->     id INT PRIMARY KEY AUTO_INCREMENT,
    ->     name VARCHAR(20)
    -> );
Query OK, 0 rows affected (1.15 sec)

总结

-- 主键约束
-- 使某个字段不重复且不得为空，确保表内所有数据的唯一性。
CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 联合主键
-- 联合主键中的每个字段都不能为空，并且加起来不能和已设置的联合主键重复。
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    password VARCHAR(20),
    PRIMARY KEY(id, name)
);

-- 自增约束
-- 自增约束的主键由系统自动递增分配。
CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

-- 添加主键约束
-- 如果忘记设置主键，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD PRIMARY KEY(id);
ALTER TABLE user MODIFY id INT PRIMARY KEY;

-- 删除主键
ALTER TABLE user drop PRIMARY KEY;

唯一主键

-- 建表时创建唯一主键
CREATE TABLE user (
    id INT,
    name VARCHAR(20),
    UNIQUE(name)
);

-- 添加唯一主键
-- 如果建表时没有设置唯一建，还可以通过SQL语句设置（两种方式）：
ALTER TABLE user ADD UNIQUE(name);
ALTER TABLE user MODIFY name VARCHAR(20) UNIQUE;

-- 删除唯一主键
ALTER TABLE user DROP INDEX name;

非空主键

-- 建表时添加非空约束
-- 约束某个字段不能为空
CREATE TABLE user (
    id INT,
    name VARCHAR(20) NOT NULL
);

-- 移除非空约束
ALTER TABLE user MODIFY name VARCHAR(20);

默认约束

-- 建表时添加默认约束
-- 约束某个字段的默认值
CREATE TABLE user2 (
    id INT,
    name VARCHAR(20),
    age INT DEFAULT 10
);

-- 移除非空约束
ALTER TABLE user MODIFY age INT;

外键约束

-- 班级
CREATE TABLE classes (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

-- 学生表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    -- 这里的 class_id 要和 classes 中的 id 字段相关联
    class_id INT,
    -- 表示 class_id 的值必须来自于 classes 中的 id 字段值
    FOREIGN KEY(class_id) REFERENCES classes(id)
);

-- 1. 主表（父表）classes 中没有的数据值，在副表（子表）students 中，是不可以使用的；
-- 2. 主表中的记录被副表引用时，主表不可以被删除。

表操作

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY
[HAVING [{ }…]]
[ORDER BY ]
[LIMIT[,] ]
]

select * from table;

去重 distinct

SELECT DISTINCT age FROM	table;

设置别名 as

SELECT stu.name,stu.height FROM tb_students_info AS stu;
SELECT name AS student_name, age AS student_age FROM tb_students_info;

限制查询结果条数 limit

LIMIT 初始位置，记录数

 SELECT * FROM tb_students_info LIMIT 3,5;

LIMIT 记录数

 SELECT * FROM tb_students_info LIMIT 4;

LIMIT 记录数 OFFSET 初始位置

mysql> SELECT * FROM tb_students_info LIMIT 5 OFFSET 3;
+----+-------+---------+------+------+--------+------------+
| id | name  | dept_id | age  | sex  | height | login_date |
+----+-------+---------+------+------+--------+------------+
|  4 | Jane  |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim   |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily  |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 |
+----+-------+---------+------+------+--------+------------+
5 rows in set (0.00 sec)

查询结果排序 order by

SELECT * FROM tb_students_info ORDER BY height;
SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC;
asc 升序
desc 降序

条件查询 where

mysql> SELECT name,age,height FROM tb_students_info 
    -> WHERE age>21 XOR height>=175;

分组查询 group by

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。

mysql> SELECT `sex`, GROUP_CONCAT(name) FROM tb_students_info 
    -> GROUP BY sex;
+------+----------------------------+
| sex  | GROUP_CONCAT(name)         |
+------+----------------------------+
| 女   | Henry,Jim,John,Thomas,Tom  |
| 男   | Dany,Green,Jane,Lily,Susan |
+------+----------------------------+
2 rows in set (0.00 sec)

COUNT()，SUM()，AVG()，MAX() 和 MIN()
WITH ROLLUP 显示记录的总和

mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info 
   ->GROUP BY sex WITH ROLLUP;
+------+------------------------------------------------------+
| sex  | GROUP_CONCAT(name)                                   |
+------+------------------------------------------------------+
| 女   | Henry,Jim,John,Thomas,Tom                            |
| 男   | Dany,Green,Jane,Lily,Susan                           |
| NULL | Henry,Jim,John,Thomas,Tom,Dany,Green,Jane,Lily,Susan |
+------+------------------------------------------------------+
3 rows in set (0.00 sec)

交叉连接 cross join

笛卡尔积（Cartesian product）是指两个集合 X 和 Y 的乘积。

例如，有 A 和 B 两个集合，它们的值如下：
A = {1,2}
B = {3,4,5}

集合 A×B 和 B×A 的结果集分别表示为：
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。

并且，从以上结果我们可以看出：
两个集合相乘，不满足交换率，即 A×B≠B×A。
A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。


mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info 
    -> WHERE tb_students_info.course_id = tb_course.id;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name   | age  | sex  | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | MySQL       |  2 | Green  |   23 | 男   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | 女   |    185 |         1 |
|  3 | Python      |  4 | Jane   |   22 | 男   |    162 |         3 |
|  2 | MySQL       |  5 | Jim    |   24 | 女   |    175 |         2 |
|  4 | Go          |  6 | John   |   21 | 女   |    172 |         4 |
|  4 | Go          |  7 | Lily   |   22 | 男   |    165 |         4 |
|  5 | C++         |  8 | Susan  |   23 | 男   |    170 |         5 |
|  5 | C++         |  9 | Thomas |   22 | 女   |    178 |         5 |
|  5 | C++         | 10 | Tom    |   23 | 女   |    165 |         5 |
+----+-------------+----+--------+------+------+--------+-----------+
10 rows in set (0.01 sec)

插入数据

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

INSERT INTO <表名>
SET <列名1> = <值1>,
<列名2> = <值2>,
…

mysql> INSERT INTO tb_courses
    -> VLAUES(3,'Java',4,'Java EE');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
+-----------+-------------+--------------+------------------+
3 rows in set (0.00 sec)

修改数据 update

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

语法说明如下：
<表名>：用于指定要更新的表名称。
SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
LIMIT 子句：可选项。用于限定被修改的行数。
注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

mysql> UPDATE tb_courses_new
    -> SET course_grade=4;
Query OK, 3 rows affected (0.11 sec)
Rows matched: 4  Changed: 3  Warnings: 0
mysql> SELECT * FROM tb_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            4 | Computer Network |
|         2 | Database    |            4 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            4 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)

删除 delete

mysql> DELETE FROM tb_courses_new;
Query OK, 3 rows affected (0.12 sec)
mysql> SELECT * FROM tb_courses_new;
Empty set (0.00 sec)

视图

mysql> CREATE VIEW v_students_info
    -> (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
    -> AS SELECT id,name,dept_id,age,sex,height,login_date
    -> FROM tb_students_info;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM v_students_info;
+------+--------+------+-------+-------+----------+------------+
| s_id | s_name | d_id | s_age | s_sex | s_height | s_date     |
+------+--------+------+-------+-------+----------+------------+
|    1 | Dany   |    1 |    24 | F     |      160 | 2015-09-10 |
|    2 | Green  |    3 |    23 | F     |      158 | 2016-10-22 |
|    3 | Henry  |    2 |    23 | M     |      185 | 2015-05-31 |
|    4 | Jane   |    1 |    22 | F     |      162 | 2016-12-20 |
|    5 | Jim    |    1 |    24 | M     |      175 | 2016-01-15 |
|    6 | John   |    2 |    21 | M     |      172 | 2015-11-11 |
|    7 | Lily   |    6 |    22 | F     |      165 | 2016-02-26 |
|    8 | Susan  |    4 |    23 | F     |      170 | 2015-10-01 |
|    9 | Thomas |    3 |    22 | M     |      178 | 2016-06-07 |
|   10 | Tom    |    4 |    23 | M     |      165 | 2016-08-05 |
+------+--------+------+-------+-------+----------+------------+
10 rows in set (0.01 sec)