Initial Knowledge of Mysql Library Table Operation
Terminal login mysql
Here only demonstrate win, cmd terminal. As for how to install Linux and MAC under win, it feels like an entry level search problem. Installation is uncertain. It's a bit awkward and embarrassing.
-- win + R input cmd rails c C:\Users\chenjie> mysql -u root -p -- Enter Enter password: ******** -- Enter -- Successful entry mysql Client, If login fails, Either the password is incorrect., Or the service is not open. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, 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. -- Printing "hello,world!" test mysql> select "hello, world!"; +---------------+ | hello, world! | +---------------+ | hello, world! | +---------------+ 1 row in set (0.00 sec) -- test mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-10-06 14:11:41 | +---------------------+ 1 row in set (0.00 sec) mysql> select curdate() as "Today's date"; +--------------+ | Today's date | +--------------+ | 2019-10-06 | +--------------+ 1 row in set (0.00 sec) mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.05 sec) -- View all databases mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cj | | flask_db | | from_pdm | | mysql | | new_house | | performance_schema | | python_test_1 | | sql_advance | | stock_db | | sys | | test1 | +--------------------+ 19 rows in set (0.04 sec) mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.10 sec) -- View database encoding mysql> show variables like "character%"; +--------------------------+----------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\AppServ\MySQL\share\charsets\ | +--------------------------+----------------------------------+ 8 rows in set (0.05 sec) -- View the permissions of a user mysql> show grants for 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.10 sec) -- View the current number of connections to the database, Concurrency number mysql> show status like 'Threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 1 | -- Number of idle threads in this thread | Threads_connected | 4 | | Threads_created | 5 | -- Last Start-up Service,Number of threads created | Threads_running | 1 | -- Number of currently activated threads +-------------------+-------+ 4 rows in set (0.05 sec) -- View Data File Storage Path mysql> show variables like "%datadir%"; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | datadir | C:\AppServ\MySQL\data\ | +---------------+------------------------+ 1 row in set (0.05 sec) -- View the maximum number of connections in a database mysql> show variables like '%max_connections%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.03 sec) ... -- Exit the database mysql> quit; Bye C:\Users\chenjie>
Database operation
Add, delete, and check. Note that the database here refers to schema ha.
-- The premise is that you have logged in,Access terminal -- Look at what's there first mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | cj | | flask_db | | from_pdm | | mysql | | new_house | | performance_schema | | python_test_1 | | sql_advance | | stock_db | | sys | | test1 | +--------------------+ 19 rows in set (0.04 sec) -- Add a new database test mysql> create database test charset=utf8; Query OK, 1 row affected (0.05 sec) -- Choose to use test mysql> use test; Database changed -- View the current database mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.05 sec) mysql> show create database test; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.08 sec) -- Look at the tables in the library mysql> show tables; Empty set -- Delete the database(Be careful!!!, And don't give permission., If you delete it, it's really gone.) mysql> drop database test; Query OK, 0 rows affected (0.20 sec) -- Physical deletion, It's too dangerous., Delete library and run away... mysql> use test; 1049 - Unknown database 'test'
Table structure operation
Including creating, modifying, deleting tables, adding, deleting fields, or taking the classic student table as an example.
Create table
-- First build test library
drop database if exist test;
create database test charset=utf8;
use test;
show tables
-- View table
mysql> show tables;
Empty set
-- Creating Student Table, Tell the truth, I see the student form now.,StudentCourse, Course selection..I also want to vomit....
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default "",
age tinyint unsigned default 0,
height decimal(5,2),
gender enum('male', 'female', "gg", "?"),
class_id tinyint unsigned default 1
);
-- Creating Class Schedule
create table classes(
id tinyint unsigned primary key auto_increment not null,
name varchar(20) default ''
);
-- out
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| classes |
| students |
+----------------+
2 rows in set (0.06 sec)
-- View table fields and constraints
mysql> desc students;
+----------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | | |
| age | tinyint(3) unsigned | YES | | 0 | |
| height | decimal(5,2) | YES | | NULL | |
| gender | enum('male','female','neutral','Unknown') | YES | | NULL | |
| class_id | tinyint(3) unsigned | YES | | 1 | |
+----------+--------------------------+------+-----+---------+----------------+
6 rows in set (0.06 sec)
-- View Table Creation Statement
mysql> show create table classes;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| classes | CREATE TABLE `classes` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)Modify table-field-add, delete
-- Practice alter tabe ... -- add, modify, mysql> desc classes; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | | | +-------+---------------------+------+-----+---------+----------------+ 2 rows in set (0.05 sec) -- Table Addition Fields add mysql> alter table classes add slogan varchar(30); Query OK, 0 rows affected (0.54 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc classes; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | | | | slogan | varchar(30) | YES | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.03 sec) -- Table modification field modify(No rename); change(rename) mysql> alter table classes modify slogan int; Query OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc classes; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | | | | slogan | int(11) | YES | | NULL | | +--------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.04 sec) -- Modify table fields-rename change Type constraints on old and new names mysql> alter table classes change slogan yg_slogan varchar(50); Query OK, 0 rows affected (0.52 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc classes; +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | | | | yg_slogan | varchar(50) | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.05 sec) -- Delete field: drop Column names mysql> alter table classes drop yg_slogan, drop name; Query OK, 0 rows affected (0.52 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc classes; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment | +-------+---------------------+------+-----+---------+----------------+ 1 row in set (0.04 sec) -- Delete table: drop table tb_name1, tb_name2... mysql> drop table classes, students; Query OK, 0 rows affected (0.22 sec) mysql> show tables; Empty set
Table modification - add, delete and modify data
-- Still use student surface, Rewrite Create Back
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) default "",
gender enum("male", "female", "neutral", "Unknown"),
age tinyint unsigned default 0,
height decimal(5,2),
class_id int unsigned default 1
);New data inset
-- Full field insertion
insert into students values (null, "youge", "male", 23, 174.5, 1);
select * from students;
Query OK, 1 row affected (0.08 sec)
+----+-------+--------+-----+--------+----------+
| id | name | gender | age | height | class_id |
+----+-------+--------+-----+--------+----------+
| 1 | youge | male | 23 | 174.50 | 1 |
+----+-------+--------+-----+--------+----------+
1 row in set (0.05 sec)
-- Partial column insertion
insert into students(name, gender, age) values ("Guo Jing", 1, 28);
mysql> select * from students;
+----+-------+--------+-----+--------+----------+
| id | name | gender | age | height | class_id |
+----+-------+--------+-----+--------+----------+
| 1 | youge | male | 23 | 174.50 | 1 |
| 2 | Guo Jing | male | 28 | NULL | 1 |
+----+-------+--------+-----+--------+----------+
2 rows in set (0.07 sec)
-- Multi line insertion
insert into students values
(null, "Huang Rong", 2, 24, 165.32, 2),
(null, "Yang Kang", 1, 25, 180, 1),
(123, "Hua Zheng", 2, 23, 162.3, 2);
mysql> select * from students;
+-----+-------+--------+-----+--------+----------+
| id | name | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
| 1 | youge | male | 23 | 174.50 | 1 |
| 2 | Guo Jing | male | 28 | NULL | 1 |
| 3 | Huang Rong | female | 24 | 165.32 | 2 |
| 4 | Yang Kang | male | 25 | 180.00 | 1 |
| 123 | Hua Zheng | female | 23 | 162.30 | 2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.07 sec)Modify data update
-- All modifications (It's just as dangerous as deleting tables and databases., Update not added where,It's all changed.)
update students set
age := 24, -- := actually is mysql Of"assignment", "=" stay update, set Same time
class_id = 2;
mysql> select * from students;
+-----+-------+--------+-----+--------+----------+
| id | name | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
| 1 | youge | male | 24 | 174.50 | 2 |
| 2 | Guo Jing | male | 24 | NULL | 2 |
| 3 | Huang Rong | female | 24 | 165.32 | 2 |
| 4 | Yang Kang | male | 24 | 180.00 | 2 |
| 123 | Hua Zheng | female | 24 | 162.30 | 2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.07 sec)
-- Conditional modification where
update students set age:=26 where id=4;
mysql>select * from students;
+-----+-------+--------+-----+--------+----------+
| id | name | gender | age | height | class_id |
+-----+-------+--------+-----+--------+----------+
| 1 | youge | male | 24 | 174.50 | 2 |
| 2 | Guo Jing | male | 24 | NULL | 2 |
| 3 | Huang Rong | female | 24 | 165.32 | 2 |
| 4 | Yang Kang | male | 26 | 180.00 | 2 |
| 123 | Hua Zheng | female | 24 | 162.30 | 2 |
+-----+-------+--------+-----+--------+----------+
5 rows in set (0.08 sec)Delete data delete, logical delete and physical delete
-- Logical deletion: Add a new logo column, By default I do not delete table 0, 1 Table deletion alter table students add is_delete bit default 0; -- update Mark data to be deleted update students set is_delete := 1 where id in(1,2); mysql> select * from students; +-----+-------+--------+-----+--------+----------+-----------+ | id | name | gender | age | height | class_id | is_delete | +-----+-------+--------+-----+--------+----------+-----------+ | 1 | youge | male | 24 | 174.50 | 2 | 1 | | 2 | Guo Jing | male | 24 | NULL | 2 | 1 | | 3 | Huang Rong | female | 24 | 165.32 | 2 | 0 | | 4 | Yang Kang | male | 26 | 180.00 | 2 | 0 | | 123 | Hua Zheng | female | 24 | 162.30 | 2 | 0 | +-----+-------+--------+-----+--------+----------+-----------+ 5 rows in set (0.11 sec) -- Actually, it's just filtering.,Not really deleted mysql> select * from students where is_delete = 0; +-----+------+--------+-----+--------+----------+-----------+ | id | name | gender | age | height | class_id | is_delete | +-----+------+--------+-----+--------+----------+-----------+ | 3 | Huang Rong | female | 24 | 165.32 | 2 | 0 | | 4 | Yang Kang | male | 26 | 180.00 | 2 | 0 | | 123 | Hua Zheng | female | 24 | 162.30 | 2 | 0 | +-----+------+--------+-----+--------+----------+-----------+ 3 rows in set (0.06 sec) -- Physical deletion: delete ...; truncate tb_anme, Retain table structure delete from students where id in (1,2,3,666); mysql> select * from students; +-----+------+--------+-----+--------+----------+-----------+ | id | name | gender | age | height | class_id | is_delete | +-----+------+--------+-----+--------+----------+-----------+ | 4 | Yang Kang | male | 26 | 180.00 | 2 | 0 | | 123 | Hua Zheng | female | 24 | 162.30 | 2 | 0 | +-----+------+--------+-----+--------+----------+-----------+ 2 rows in set (0.07 sec) -- Full deletion, Retain table structure truncate students; mysql> truncate students; Query OK, 0 rows affected (0.24 sec) mysql> select * from students; Empty set
As for the query select, the next section is a complete one.
Summary
- The installation of mysql on different platforms (win, linux) is familiar with it manually.
- Terminal login: mysql-u username-p password; exit: quit;
- Frequently used commands
- select database();
- shwo databases;
- use database name;
- show tables;
- desc table name;
- Commonly used in Library exercises
- create database library name charset=utf8;
- use library name;
- show create database library name;
- drop database library name; (delete library run)
- Additions and deletions
- create table table table name (field name, type, constraint,....);
- Type: Number, String, Time and Date, Enumeration...
- Constraints: primary key; auto_increment; not null; default; unique; foreign key...
- Primary key constraint: id int unsigned primary key auto_increment not null;
- desc table name; show create table table table table name;
- drop table table table name;
- Add, delete and change fields
- alter table table name add field name type [constraint];
- alter table table name modify field name type [constraint];
- alter table table name change current field name new field name type [constraint];
- alter table table name DROP field 1, drop field 2...;
- Table data - add
- insert into table names values (col1, co12...), (...), (...);
- insert into table name (col1, col2) values (val1, val2)...;
- Table data - change
- update table name set field 1 = value 1, field 2 = value 2....
- update table name set field 1 = value 1, field 2 = value 2.... Where.....
- Table data - deletion
- delete from table name where.....
- truncate table name; (retains table structure)
- alter table table name add isDelete bit default ();
- update table name set isDelete: = 1 where...;