python web开发 MySQL数据库基础

2022-03-13 05:21:09

文章目录


1. 简介

2. 下载安装

3. 操作 MysQL 数据库

3.1 创建数据库

3.2 选择数据库

3.3 查看数据库

3.4 删除数据库

4. 数据类型

5. 操作 MysQL 数据表

5.1 创建数据表

5.2 查看表的结构

5.3 修改表的结构

5.4 删除数据表

6. 操作 MySQL 数据表记录

6.1 添加数据

6.2 查询、修改、删除

learning from 《python web开发从入门到精通》


1. 简介


MySQL 是目前最为流行的开源数据库，网络化、跨平台的关系型数据库

特点：功能强大（多种引擎），跨平台，速度快（B树磁盘表+索引压缩），免费，支持多种语言，存储容量大（由外部操作系统对文件大小的限制决定）


2. 下载安装


跳过登录，下载 (mysql-installer-community-8.0.27.1.msi) 470MB：

> mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

mysql>


3. 操作 MysQL 数据库


3.1 创建数据库



  • create database DBname;
mysql> create database michaeldata;
Query OK, 1 row affected (0.05 sec)
  • 表名，不能重名
mysql> create database michaeldata;
ERROR 1007 (HY000): Can't create database 'michaeldata'; database exists
  • 可以 是 字母， 阿拉伯数字，下划线，$ ，中的任意字符开头，但不能是纯数字，名称最长64个字符，别名最多256个字符
  • 不能是 内置的关键字
  • 各平台间的表名 大小写 敏感度不一样，建议都用小写表名
mysql> create database 123data;
Query OK, 1 row affected (0.03 sec)

mysql> create database $;
Query OK, 1 row affected (0.04 sec)

mysql> create database 123;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123' at line 1


3.2 选择数据库


  • use DBname;
mysql> use michaeldata;
Database changed

显示当前数据库

select database();

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

3.3 查看数据库


  • show databases;已经存在的数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| $                  |
| 123data            |
| information_schema |
| michaeldata        |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
9 rows in set (0.02 sec)

3.4 删除数据库


  • drop database DBname;(慎重，没有备份 是恢复不了的)
mysql> drop database $;
Query OK, 0 rows affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| 123data            |
| information_schema |
| michaeldata        |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
8 rows in set (0.00 sec)


4. 数据类型


数字


TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT

字符串


普通文本：CHAR，VARCHAR（后者长度可变）

TEXT适合存储长文本，BLOB适合存储二进制数据（文本，声音，图像）

两者也可以带前缀 tiny，medium，long，对应范围不同

ENUM枚举，SET一组值

日期和时间


DATETIME，DATE，TIMESTAMP，TIME，TEAR，如果赋值不合法，会被0代替


5. 操作 MysQL 数据表


5.1 创建数据表


CREATE TABLE table_name (column_name column_type);

mysql> create table users(
    -> id int(8) auto_increment primary key,
    -> username varchar(30) not null,
    -> password varchar(30) not null,
    -> createtime datetime);
Query OK, 0 rows affected, 1 warning (0.08 sec)

python web开发 MySQL数据库基础


5.2 查看表的结构


  • show columns from 数据库.数据表;
mysql> show columns from users;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| username   | varchar(30) | NO   |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
4 rows in set (0.00 sec)
  • describe 表名;可简写为 desc 表名，也可只看某列（后面加列名）
mysql> describe users; 
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| username   | varchar(30) | NO   |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> desc users createtime;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| createtime | datetime | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

5.3 修改表的结构


alter table 表名 alter_spec[,alter_spec]

可参考：https://www.w3cschool.cn/mysql/mysql-alter.html


例如，添加新字段 address，更改 username 为 varchar(50)

ALTER TABLE users
add address varchar(52) not null,
modify username varchar(50)

mysql> desc users;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| username   | varchar(50) | YES  |     | NULL    |                |
| password   | varchar(30) | NO   |     | NULL    |                |
| createtime | datetime    | YES  |     | NULL    |                |
| address    | varchar(52) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

5.4 删除数据表


  • drop table 表名;
mysql> create table temp(id int);
Query OK, 0 rows affected (0.09 sec)

mysql> drop table temp;
Query OK, 0 rows affected (0.05 sec)

避免报错 if exists

mysql> drop table temp;
ERROR 1051 (42S02): Unknown table 'michaeldata.temp'

mysql> drop table if exists temp;
Query OK, 0 rows affected, 1 warning (0.03 sec)


6. 操作 MySQL 数据表记录


6.1 添加数据

mysql> insert into users(id, username, password, createtime, address)
    -> values (1, 'michael', '123', '2021-12-01 00:00:10', 'China');
Query OK, 1 row affected (0.03 sec)

mysql> select * from users;
+----+----------+----------+---------------------+---------+
| id | username | password | createtime          | address |
+----+----------+----------+---------------------+---------+
|  1 | michael  | 123      | 2021-12-01 00:00:10 | China   |
+----+----------+----------+---------------------+---------+
1 row in set (0.00 sec)
  • 如果是全部列的数据都有，可以省略 列名 
mysql> insert into users
    -> values (3, 'happy', '456', '2021-12-01 15:14:10', 'China');
Query OK, 1 row affected (0.03 sec)

mysql> select * from users;
+----+----------+----------+---------------------+---------+
| id | username | password | createtime          | address |
+----+----------+----------+---------------------+---------+
|  1 | michael  | 123      | 2021-12-01 00:00:10 | China   |
|  3 | happy    | 456      | 2021-12-01 15:14:10 | China   |
+----+----------+----------+---------------------+---------+
2 rows in set (0.00 sec)

mysql> insert into users(id, username, password, address)
    -> values (4, 'bird', '789', 'China');
Query OK, 1 row affected (0.04 sec)

mysql> select * from users;
+----+----------+----------+---------------------+---------+
| id | username | password | createtime          | address |
+----+----------+----------+---------------------+---------+
|  1 | michael  | 123      | 2021-12-01 00:00:10 | China   |
|  3 | happy    | 456      | 2021-12-01 15:14:10 | China   |
|  4 | bird     | 789      | NULL                | China   |
+----+----------+----------+---------------------+---------+
3 rows in set (0.00 sec)

6.2 查询、修改、删除


查询 select col from table where...

参看：文中的 前置入门学习

修改和删除，记得带 where 条件，否则对整张表操作

  • where 在 group by 之前，having 在 group by 之后
上一篇：【Mybatis】如何继承Mybatis中的Mapper.xml文件


下一篇：Nginx在windows下常用命令