Detailed notes on getting started with MySQL database

ps:

The contents in brackets [] can be omitted and added as required

Database operations

Create database

Syntax: create database [if not exists] data name charset=gbk;
create database if not exists tow charset=gbk;
Query OK, 1 row affected
  • If not exists - > judge whether it is repeated

  • Charset = GBK - > simplified Chinese

  • Simplified Chinese

  • Utf8 - > universal character encoding

Display current time, user name, database version

select now(), user(), version();
+---------------------+----------------+-----------+
| now()               | user()         | version() |
+---------------------+----------------+-----------+
| 2020-04-23 16:58:06 | root@localhost | 5.5.30    |
+---------------------+----------------+-----------+
1 row in set

View database information

Syntax: show create database database name;
mysql> show create database tow;
+----------+-------------------------------------------------------------+
| Database | Create Database                                             |
+----------+-------------------------------------------------------------+
| tow      | CREATE DATABASE `tow` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set

Modify database code

Syntax 1: alter database database name default character set gb2312;
Syntax 2: alter database database name character set gbk;

Both methods can change all kinds of codes

Example 1:

alter database tow default character set gb2312;
Query OK, 1 row affected

Example 2:

alter database tow character set gbk;
Query OK, 1 row affected

Query which databases

show databases;#
+--------------------+
| Database           |
+--------------------+
| information_schema |
| frist              |
| mysql              |
| performance_schema |
| tow                |
+--------------------+
5 rows in set

Delete database

Syntax: drop database database name;
drop database tow;
Query OK, 0 rows affected

Select database

Syntax: use database name;
 use frist;
Database changed

Table operation

Create table

create table table name(
    Field name field type [not null] [primary key] [null] [auto increment] [comment],
    No semicolon is required at the end of field name field type [default] ×
)[engine=innodb];

  • [engine = InnoDB] - > select the storage engine; the menu engine stores the data in memory. After restarting the service, the data will be lost, but the reading speed is fast.

  • [not null] - > cannot be null

  • [null] - > null allowed

  • [auto increment] - > auto increment for example, the default is 1. If there is an input, it will start from the number you input.

  • [primary key] - > primary key

  • [comment] - > comment

  • [default] - > Default

  • set nams gbk;

Modification table

Add field

Syntax: alter table table name add field name field type;
 alter table teacher add addr varchar(50) default'Address unknown'  comment'address';
Query OK, 0 rows affected

Add fields in the first location

Syntax: alter table table name add field name field type first;
 alter table teacher add number int comment'number' first;
Query OK, 0 rows affected

Add a field after the specified field

Syntax: alter table name add field name field type after the specified field name;
alter table teacher add phone varchar(11) not null after name;
Query OK, 0 rows affected

Delete field

Syntax: alter table table name drop field name;
alter table teacher drop number;
Query OK, 0 rows affected

Modify field name and type

Syntax: alter table name change original field name new field name data type;
alter table teacher change addr dizhi char(100);
Query OK, 0 rows affected
alter table test change id id int auto_increment;
Query OK, 3 rows affected

Modify field type without changing field name

Syntax: alter table table name modify field name field type;
alter table teacher modify dizhi varchar(50);
Query OK, 0 rows affected

Modify table name

alter table name rename to new table name;
alter table teacher rename to techer;
Query OK, 0 rows affected

Query which tables

show tables;
+-----------------+
| Tables_in_frist |
+-----------------+
| stu             |
| sudent          |
| teacher         |
+-----------------+
3 rows in set

Delete table

Syntax: drop table [if exists] table name;
drop table if exists stu;
Query OK, 0 rows affected

No stu table without if exists

drop table stu;
1051 - Unknown table 'stu'

No stu table, but with if exists

drop table if exists stu;
Query OK, 0 rows affected

Copy table

Copy the table structure and data; cannot copy the primary key of the table, but can copy the data of the table

Syntax 1: create table new table name select * from table name to be copied;
		create table the table name stored after copying as(select * from the table name to be copied); ා the effect is exactly the same as the above
mysql> create table stu1 select * from stu;
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from stu1;
+----+--------+------+-------+
| id | name   | addr | score |
+----+--------+------+-------+
|  1 | Jack Ma   | Hangzhou |    77 |
|  2 | pony  | Shenzhen |    66 |
|  3 | Master | U.S.A |    60 |
+----+--------+------+-------+
3 rows in set

mysql> desc stu1;	#Primary key cannot be copied
+-------+-------------+------+-----+----------+-------+
| Field | Type        | Null | Key | Default  | Extra |
+-------+-------------+------+-----+----------+-------+
| id    | int(11)     | NO   |     | 0        |       |
| name  | varchar(20) | NO   |     | NULL     |       |
| addr  | varchar(50) | YES  |     | Address unknown |       |
| score | int(11)     | YES  |     | NULL     |       |
+-------+-------------+------+-----+----------+-------+
4 rows in set

Copy table structure; only table structure can be copied, not table data

Syntax 2: create table new table name like the table name to be copied;
mysql> create table stu2 like stu;
Query OK, 0 rows affected

mysql> select * from stu2;
Empty set

mysql> desc stu2;	#Primary key copied
+-------+-------------+------+-----+----------+----------------+
| Field | Type        | Null | Key | Default  | Extra          |
+-------+-------------+------+-----+----------+----------------+
| id    | int(11)     | NO   | PRI | NULL     | auto_increment |
| name  | varchar(20) | NO   |     | NULL     |                |
| addr  | varchar(50) | YES  |     | Address unknown |                |
| score | int(11)     | YES  |     | NULL     |                |
+-------+-------------+------+-----+----------+----------------+
4 rows in set

Show the statement that created the table

Syntax: show create table table name;
 show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

View table structure

Syntax: desc table name; abbreviation
	Description table name;
describe test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set

Modify table code

(solve the problem of Chinese coding)

alter table Table name character set gbk;
alter table Table name convert to character set utf8;
set names gbk;	#The simplest way to modify the entire database

The last one is the code name, which is filled in as needed. The commonly used one is gbk/utf8/gb2312

Data operation

Create test table

create table stu(
    -> id int auto_increment primary key comment'Primary key',
    -> name varchar(20) not null,
    -> addr varchar(50) default'Address unknown',
    -> score int comment'achievement'
    -> );
Query OK, 0 rows affected

Insert a piece of data

Syntax: insert into table name (field name, field name,...) Values (field values , field value);

Example 1:

insert into stu (id,name,addr,score) values(19022100,'tom','Beijing',88);
Query OK, 1 row affected

Example 2: the inserted fields can be inconsistent with the order of the fields in the table as long as they correspond one by one

insert into stu(name,score,addr,id) values('berry',77,'Beijing',2);
Query OK, 1 row affected

Example 3: only some fields can be inserted, but non empty fields must be inserted

insert into stu(id,name,addr) values(19,'ketty','Shanghai');
Query OK, 1 row affected

Example 4: the value of auto growing column can be inserted into null directly or not, and the database will automatically insert the number of growing columns

insert into stu(name,addr) values('rose','Beijing');
Query OK, 1 row affected

Example 5: the order and number of inserted values are consistent with the order and number of table fields. The inserted fields can be omitted

insert into stu values(null,'Sea','Guangxi',100);
Query OK, 1 row affected

Example 7: insert the default value through the default keyword

 insert into stu values(null,'toni',default,50);
Query OK, 1 row affected

Query all data in the table

Syntax: select * from table name;
select * from stu;
+----------+------+------+-------+
| id       | name | addr | score |
+----------+------+------+-------+
| 19022100 | tom  | Beijing |    88 |
+----------+------+------+-------+
1 row in set

Insert multiple data

Syntax: insert into table name values (field value 1,..., field value n),..., (field value 1,... Field value n);
insert into stu values(null,'Jack Ma','Hangzhou',66),(null,'pony ','Shenzhen',55);
Query OK, 2 rows affected

Update data

Syntax: update table name set field value = value [where condition];

Example 1: change the address of student 2 to Hubei

update stu set addr = 'Hubei' where id = 2;
Query OK, 1 row affected

Example 2: change Ma Yun's score to 99

update stu set score = 99 where name = 'Jack Ma';
Query OK, 1 row affected

Example 3: change the address of Ma Huateng to Guangdong and the score to 80

update stu set addr = 'Guangdong',score = 80 where name = 'pony ';
Query OK, 1 row affected

Example 4: change the scores of all students in Beijing to 77

update stu set score = 60 where addr = 'Beijing';
Query OK, 2 rows affected

Example 5: change the scores of two students with student number 1 and 2 to 65

update stu set score = 65 where id = 1 or id = 2;
Query OK, 2 rows affected

Conditions can be omitted, but if omitted, all data is changed

Delete data

Syntax: delete from table name [where condition];

Example 1: delete the student whose student number is 2

delete from stu shere id = 2;

Example 2: delete students whose score is less than or equal to 65

 delete from stu where score <= 65;
Query OK, 4 rows affected

Example 3: delete all records in the table

delete from stu;
Query OK, 3 rows affected

Clear table

Syntax: truncate table table name;
truncate table stu;
Query OK, 0 rows affected
The difference between delete from and truncate table:
-delete from: traverse table records, delete one by one.
-truncate table: destroy the original table and create a new table with the same structure. It's more efficient

Query table

Syntax: select column name from table name;

Example 1:

mysql> select id,name from stu;
+----+--------+
| id | name   |
+----+--------+
|  1 | Jack Ma   |
|  2 | pony  |
|  3 | Master |
+----+--------+
3 rows in set

mysql> select * from stu; # *Number indicates all fields
+----+--------+------+-------+
| id | name   | addr | score |
+----+--------+------+-------+
|  1 | Jack Ma   | Hangzhou |    77 |
|  2 | pony  | Shenzhen |    66 |
|  3 | Master | U.S.A |    60 |
+----+--------+------+-------+
3 rows in set

character set

Character set: character in the save and transfer is the corresponding binary encoding set.

Create test data table

create table test(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected
set names gbk;	#Set the encoding format of server and return value to gbk

Character encoding error

1366 - Incorrect string value: '\xAC\xE5\x8C\x96\xE8\x85...' for column 'name' at row 1

Solution: change the code to match the database

set names encoding name;

Follow up continuous updates focus on the first time I get updates

Tags: MySQL Database encoding less

Posted on Sun, 03 May 2020 09:04:02 -0400 by Farside