cool hit counter A Beginner's Guide to Mysql Written for Beginners (I)_Intefrankly

A Beginner's Guide to Mysql Written for Beginners (I)


1.data type

Use the right data for the actual needs of the project types, This is also the operation of data optimization

  • integers

types

Storage Range

byte count

TINYINT

Signed values: -128 to 127 (-27 to 27-1) Unsigned values: 0 to 255 (0 to 28-1)

1

SMALLINT

Signed values: -32768 to 32767 (-215 to 215-1) Unsigned values: 0 to 65535 (0 to 216-1)

2

MEDIUMINT

Signed values: -8388608 to 8388607 (-223 to 223-1) Unsigned values: 0 to 16777215 (0 to 224-1)

3

INT

Signed values: -2147483648 to 2147483647 (-231 to 231-1) Unsigned values: 0 to 4294967295 (0 to 232-1)

4

BIGINT

Signed values: -9223372036854775808 to 9223372036854775807 (-263 to 263-1) Unsigned values: 0 to 18446744073709551615 (0 to 264-1)

8

  • floating point

types

Storage Range

byte count

FLOAT[(M,D)]

-3.402823466E+38 to -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38. M is the total number of digits in the number and D is the number of digits after the decimal point. If M and D are omitted, the values are saved according to the limits allowed by the hardware. Single precision floating point numbers are accurate to approximately 7 decimal places.

4

DOUBLE[(M,D)]

-1.7976931348623157E+308 up to -2.2250738585072014E-308、 0 harmony 2.2250738585072014E-308 up to 1.7976931348623157E+308

8

  • character type

types

Storage requirements

use

CHAR(M)

M byte,0 <= M <= 255

Define string

VARCHAR(M)

L+1 byte, among othersL+1 <= M yet 0 <= M <= 65535

variable length string

TINYTEXT

L+1 byte, among othersL < 28

Short text text string

TEXT

L+2 byte, among othersL < 216

Long text data

MEDIUMTEXT

L+3 byte, among othersL < 224

Medium-length text data in binary form

LONGTEXT

L+4 byte, among othersL < 232

Extremely large text data

ENUM('value1','value2',...)

1 or 2 bytes, depending on the number of enumeration values (up to 65535 values)

SET('value1','value2',...)

1, 2, 3, 4 or 8 bytes, depending on the number of set members (up to 64 members)

  • Time Type

types

Storage Range

byte count

format

use

DATE

1000-01-01 / 9999-12-31

3

YYYY-MM-DD

Full date value

TIME

'-838:59:59' / '838:59:59'

3

HH:MM:SS

Time value or duration

YEAR

1901 / 2155

1

YYYY

Year value

DATETIME

1000-01-01 00:00:00 / 9999-12-31 23:59:59

8

YYYY-MM-DD HH:MM:SS

Mixing date and time values

TIMESTAMP

1970-01-01 00:00:00 / Sometime in 2037

8

YYYY-MM-DD HH:MM:SS

Mixed date and time values, timestamp

2.Operation of data tables

It is best to capitalize key words so that they are easy to read. You can use the windows cmd utility to operate on the database, provided that the path to the bin subdirectory of the mysql installation directory is added to the system variable PATH.mysql -v You can view the database version. Login: mysql -u username -p password, e.g. mysql -uroot -p123456

  • View all databases SHOW DATABASES;
  • Using the database USE database name
  • View the currently used database SELECT database();
  • View current users SELECT user();
  • Check the current time SELECT now();
  • View the data table of the current database SHOW TABLES;
  • View data tables from other databases SHOW TABLES FROM database name;
  • Creating a Data Table
 CREATE TABLE data table name (
 Field Type Set,
 Field Type Set
);
  • View the structure of the data table SHOW COLUMNS FROM data table name equivalent command DESCRIBE Data table name
  • View all data in the data table SELECT * FROM data table name

3.Data logging operations

  • Create a simple data table
CREATE TABLE t1(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT,
    -> salary FLOAT(8,2)
    -> );
  • See the structure of the common data table just now SHOW COLUMNS FROM t1;

Field

Type

Null

Key

Default

Extra

username

varchar(20)

NO

NULL

age

tinyint(4)

YES

NULL

salary

float(8,2)

YES

NULL

  • Insert a row of data INSERT INTO t1 VALUES('Tom',20,56310.54); INSERT t1(username,salary) VALUES('Jon',75654.35);

<font color="blue">INTO Key words can be omitted, Do not follow the field after the table name, Default data to be inserted for all fields, Otherwise it will report an error</font>

  • View all the data that was just inserted into the database SELECT * FROM t1;

username

age

salary

Tom

20

56310.54

Jon

NULL

75654.35

  • Create self-growing data tables with primary key constraints and unique constraints
 CREATE TABLE t2(
    -> id SMALLINT AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> age TINYINT UNSIGNED,
    -> sex ENUM('1','2','3') DEFAULT 3
    -> );

AUTO_INCREMENT Auto numbering, which must be used in combination with the primary key PRIMARY KEY, starts at 1 by default and is incremented by 1 each time. However, when using primary keys, auto-numbering is not necessarily used

  • See the structure of the common data table just now SELECT * FROM t2;

Field

Type

Null

Key

Default

Extra

id

smallint(6)

NO

PRI

NULL

auto_increment

username

varchar(20)

NO

UNI

NULL

age

tinyint(3) unsigned

YES

NULL

sex

enum('1','2','3')

YES

3

Unique constraint UNIQUE means that the data in the field cannot be duplicated, for example, the data table above cannot insert two data with username 'tom', even if the two people just have the same name

Five constraints

  • NOT NULL Non-Null Constraint
  • PRIMARY KEY Primary Key Constraint
  • UNIQUE KEY Unique Constraint
  • DEFAULT default constraint
  • NOT NULL Non-Null Constraint

foreign key constraint

  • 1.The parent and child tables must use the same storage engine, and the use of temporary tables is prohibited.
  • 2.The storage engine for the datasheet can only be InnoDB.
  • 3. foreign key column harmony Reference columns must have similar data types。 where the length of the digits or the signed digits must be the same; And the length of the characters can vary。
  • 4.Indexes must be created for foreign key columns and reference columns. MySQL will automatically create an index if no index exists for the foreign key column.
  • Create a table of provinces
 CREATE TABLE province(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(20) NOT NULL
    -> );
  • View the commands used to create the table

SHOW CREATE TABLE provinceG; Adding a G at the end allows you to filter unnecessary information for easy viewing.

            Table: province
Create Table: CREATE TABLE `province` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

You can see that the foreign key constraint requirement for the storage engine being InnoDB is met

  • Create a sub-table user table
CREATE TABLE user(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL,
    -> pid SMALLINT UNSIGNED,
    -> FOREIGN KEY (pid) REFERENCES province (id)
    -> );

pid As a foreign key column,province as a parent watch, suchid For the reference column, in casepid data types harmony Data in the reference column types Different or different sign bits, Then it will report150 mistakes, Failed to create sub-table。

  • View the index of a data table

SHOW INDEX FROM provinceG;

*************************** 1. row ***************************
        Table: province
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:

SHOW INDEX FROM userG;

*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: user
   Non_unique: 1
     Key_name: pid
 Seq_in_index: 1
  Column_name: pid
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
  • View the user's data table creation command

SHOW CREATE TABLE userG;

*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(10) NOT NULL,
  `pid` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `province` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

By looking at the create command for the user table, you can see that the system automatically creates an index id for the pid of the child table, referencing the id of the (reference) since table.


Recommended>>
1、Extremely boring python algorithm implementation on an interview question
2、2017 FinTech Datadriven Financial Business Fission Value Summit Successfully Concluded Data Monkeys Join Hands with Financial Industry Elite to Explore New Directions in the Industry
3、Golangs go command usage
4、SLIC Super Pixel Segmentation Explained II Key Code Analysis
5、SpringSpringMVCMyBatiseasyUI Integration Fundamentals XII Stage Summary

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号





    Contact US

    Welcome to Intefrankly.

    Intefrankly is a platform that you can show your opnions related to IT technology, and communicates to the people who interested in IT and Artificial Intelligence area.