Article catalog
learning from python web development from introduction to mastery
1. Introduction
- MySQL is the most popular open source database, a networked and cross platform relational database
- Features: powerful (multiple engines), cross platform, fast (B-tree disk table + index compression), free, support for multiple languages, large storage capacity (determined by the file size limit of the external operating system)
2. Download and install
Skip login and download (mysql-installer-community-8.0.27.1.msi) 470MB: https://dev.mysql.com/downloads/
Slow download? Use Tsinghua source https://mirrors.tuna.tsinghua.edu.cn/ Click it: https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQLInstaller/mysql-installer-community-8.0.27.1.msi
I select Full installation, and add the path environment variable C:\Program Files\MySQL\MySQL Server 8.0\bin
- mysql -u root -p, and then enter the password set during installation
> 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. Operate MysQL database
3.1 creating database
- create database DBname;
mysql> create database michaeldata; Query OK, 1 row affected (0.05 sec)
- Table name, cannot be duplicate
mysql> create database michaeldata; ERROR 1007 (HY000): Can't create database 'michaeldata'; database exists
- It can start with any character in letters, Arabic numerals, underscores, $, but cannot be a pure number. The name can be up to 64 characters and the alias can be up to 256 characters
- Cannot be a built-in keyword
- The case sensitivity of table names is different between platforms. It is recommended to use lower case table names
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
# window case insensitive mysql> create database michaeldatA; ERROR 1007 (HY000): Can't create database 'michaeldata'; database exists
3.2 database selection
- use DBname;
mysql> use michaeldata; Database changed
Displays the current database
select database();
mysql> select database(); +-------------+ | database() | +-------------+ | michaeldata | +-------------+ 1 row in set (0.00 sec)
3.3 viewing database
- show databases; Existing database
mysql> show databases; +--------------------+ | Database | +--------------------+ | $ | | 123data | | information_schema | | michaeldata | | mysql | | performance_schema | | sakila | | sys | | world | +--------------------+ 9 rows in set (0.02 sec)
3.4 delete database
- drop database DBname; (be careful, you can't recover without backup)
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. Data type
number
- TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
character string
- Plain text: CHAR, VARCHAR (the latter is variable in length)
- TEXT is suitable for storing long TEXT, and BLOB is suitable for storing binary data (TEXT, sound, image) They can also be prefixed with tiny, medium and long, with different corresponding ranges
- ENUM enumeration, SET a SET of values
Date and time
- DATETIME, DATE, TIMESTAMP, TIME, team, if the assignment is illegal, it will be replaced by 0
5. Operate MysQL data table
5.1 creating data tables
- 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)
5.2 view table structure
- show columns from database data sheet;
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 table name; It can be abbreviated as desc table name, or just look at a column (followed by column name)
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 modify the structure of the table
- alter table name_ spec[,alter_spec]
Refer to: https://www.w3cschool.cn/mysql/mysql-alter.html
For example, add a new field, address, and change username to 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 delete data table
- drop table name;
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)
Avoid reporting errors 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. Operate MySQL data table records
6.1 adding data
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)
- If all columns have data, the column name can be omitted
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 query, modify and delete
Query select col from table where see: Pre introduction learning in the article
When modifying or deleting, remember to bring the where condition, otherwise the whole table will be operated
- where before group by, having after group by