Database foundation

1. Knowledge of character set coding

1.1 how to convert strings into data

  • A one-to-one correspondence is established by specifying a non duplicate table

  • Another computer manufacturer A < --- > 03 is different from the value of A in the above table. In order to eliminate ambiguity, A certain authoritative organization specifies standards, and all manufacturers comply with the standards

  • ASCII code: only 0 ~ 127 characters in total. At that time, it was aimed at countries with British and American as the main language,

  • Unicode encodes characters through the encoding table 0 – > (2 ^ 32 - 1)

  • ASCII is a subset of Unicode encoding, and Unicode is compatible with ASCII encoding

  • Unicode is only an ideal encoding. When it comes to real storage (stored in memory or hard disk), it faces new problems. 2 ^ 32 complete storage requires 4 bytes. Hello, if it is stored directly, it requires 4 * 5 = 20 bytes; In order to save space, different coding formats are designed for real storage

  • As long as the utf-8 encoding format is used, it implies that Unicode encoding is behind it

1.2 why is character set coding troublesome

  • Conductive problems require that people on the whole chain can't make mistakes

  • The standard name of utf-8 is utf-8 (case insensitive). Programming languages are generally not easy to handle - - usually written as utf_8. Utf8, MySQL directly takes utf8 as its standard utf-8 name

  • For the sake of cost, MySQL didn't support all characters when implementing utf-8 coding. At first, it only supported 80% of unicode in order to save space. Later, it introduced utf8mb4 to support all characters

1.3 character set inheritance rules of MySQL

  • The default character set of the table is inherited from the default character set of the library; The default character set of the field is inherited from the default character set of the table

  • As long as the character set of the library is utf8b4, the character set of the table is also utf8mb4. At the same time, the default values of all fields (string types) in the table are also utf8mb4

  • Once the character set of the field is determined, the table and field will not change even after changing the character set of the library

  • It is inherited during creation and not during modification. It is best to delete and rebuild the library

2. About table

2.1 data types in the table

  • Numeric type integer, non integer, signed
    • Integer int / int tinyint < smallint < int < bigint
    • Floating point (decimal with precision loss) float < double
    • decimal, numeric with precision
  • String type / binary data
    • char(4) is 4 characters long (fixed length)
    • varchar (4) can be up to 4 characters long (variable length)
    • Typical examples of using char (less used): ID card (with characters and fixed length), gender (male and female), and varchar is more suitable for the remaining scenarios
    • varchar (there is an upper limit on the length). If the string is longer: text smalltext < text < longtext
    • Binary data: binary objects with large blob (Binary Large OBject) such as pictures and sounds
  • Time type
    • date Date Year Month Day
    • time: hour: minute: Second
    • datetime date + time year month day hour: minute: Second
    • timestamp: approximately equal to datetime year month day hour: minute: second. timestamp refers to the number of seconds since 1970-1-1. Compared with datetime, it has the advantages of small space occupation; Disadvantages: Limited from 1970 to 2038
    • Whenever time is involved, there will be a time zone
    • MySQL database represents A string. Either single quotation marks or double quotation marks can be "A" or "AA"
    • Time in the data: use a string to represent "2021-11-20 10:07:18", and write in strict accordance with this format

2.2 constraints

It is used to specify the data range of this field and what data is supported? What data is not supported?

2.2.1 constraint type


2.2.2 primary key (Primary Key/ PK)

  • Requirements for relational database: a table must have a primary key;
  • Primary key is the unique identifier of a record. It cannot be duplicate
  • A record can be uniquely determined through the primary key; In practice, there are also some records that conform to the primary key. The unique records are determined by the union of several fields
  • In practice, it is recommended to use integer (int, bigint) for the primary key, which will grow automatically by default in combination with the AI (automatic growth) system and will not be repeated

2.2.3 NN Not Null

  • Checked: null is not allowed. Unchecked, null is allowed

  • A field in a record is Null, which generally means that the field is unknown and unrecorded;

  • Allow Null & & if there is no Default value, Default is Null;

  • Null & & is allowed. If there is a Default value, the Default setting shall prevail;

  • Null & & is not allowed. If there is a Default value, the Default setting shall prevail;

  • Null is not allowed & & if there is no default value, there is no default value;

2.2.4 UQ Unique index

  • Unique index. The value of this field cannot be duplicate;

  • If NULL is allowed, null is not a duplicate;

    CREATE TABLE `db_11_20`.`users` (
      `uid` INT NOT NULL AUTO_INCREMENT,
      `username` VARCHAR(45) NULL,
      `password` VARCHAR(45) NULL,
      PRIMARY KEY (`uid`),
      UNIQUE INDEX `username_UNIQUE` (`username` ASC));
      -- First insertion successful
      INSERT INTO users (username,password) VALUES ('A' , '123');  
      -- Second insertion
    -- Error Code: 1062.
    -- Duplicate entry 'A' for key 'username_UNIQUE'	0.000 sec
    -- Duplicate field values'A' 
    -- In the table username Field already exists'A'This value is. It is not allowed to insert a new one'A';
    INSERT INTO users (username,password) VALUES ('A' , '123'); 
    -- because password Field, unchecked UQ Therefore, repetition is allowed 
    -- 5	B	123 It is incremental, and there may be failed inserts id Consume
    INSERT INTO users (username,password) VALUES ('B' , '123'); 
    -- null It is not regarded as a repetition, and multiple inserts are allowed
    INSERT INTO users (username,password) VALUES (null,null); 
    INSERT INTO users (username,password) VALUES (null,null); 
    INSERT INTO users (username,password) VALUES (null,null); 
    

2.2.5 Default

  • When a new field is inserted into the table, either a value is inserted for the field or the field has a default value;

2.2.6 comment

  • The library, table and field all support comment. In order to explain the purpose, it is generally required that each table and field have comments;

    CREATE TABLE `db_11_20`.`users` (
      `id` INT NOT NULL COMMENT 'User unique ID',
      `username` VARCHAR(45) NOT NULL COMMENT 'User name, and duplicate is not allowed',
      `password` VARCHAR(45) NOT NULL COMMENT 'password',
      PRIMARY KEY (`id`),
      UNIQUE INDEX `username_UNIQUE` (`username` ASC))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4
    COMMENT = 'User table';
    

2.3 table structure

  • Field composition: Name + data type (int, varchar, text, blob, datetime, time) + constraint (pk, ai, nn, uq, default, comment);

3. Examples

Example: there is a store's data, which records customers and shopping. It is composed of the following three tables:

  • Goods (commodity number goods_id, commodity name goods_name, unit price, commodity category, supplier);
  • Customer (customer No., customer_id, name, address, email, sex, ID card_id);
  • Purchase (purchase order No. order_id, customer_id, commodity No. goods_id, purchase quantity nums);

analysis:

  • -

    SQL:

    CREATE TABLE `db_11_20`.`goods` (
      `gid` INT NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(45) NOT NULL,
      `price` INT NOT NULL COMMENT 'In minutes',
      `category` VARCHAR(45) NOT NULL COMMENT 'category',
      `provider` VARCHAR(45) NOT NULL COMMENT 'supplier',
      PRIMARY KEY (`gid`))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4;
    

  • -

    SQL:

    CREATE TABLE `db_11_20`.`customers` (
      `cid` INT NOT NULL AUTO_INCREMENT,
      `cname` VARCHAR(45) NOT NULL,
      `address` VARCHAR(200) NOT NULL,
      `email` VARCHAR(200) NOT NULL,
      `sex` CHAR(1) NOT NULL,
      `idcard` CHAR(18) NOT NULL,
      PRIMARY KEY (`cid`),
      UNIQUE INDEX `idcard_UNIQUE` (`idcard` ASC))
    ENGINE = InnoDB
    DEFAULT CHARACTER SET = utf8mb4;
    
  • -

SQL:

CREATE TABLE `db_11_20`.`purchase` (
  `pid` INT NOT NULL AUTO_INCREMENT COMMENT 'Purchase order No',
  `cid` INT NOT NULL COMMENT 'purchaser',
  `gid` INT NOT NULL COMMENT 'What did you buy',
  `bought_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When to buy',
  `price` INT NOT NULL COMMENT 'Price',
  `num` INT NOT NULL COMMENT 'quantity',
  PRIMARY KEY (`pid`))
COMMENT = 'Purchase record';
  • insert data

    --  Prices are in cents '130000' ---->1300 element
    INSERT INTO goods (name, price, category, provider) VALUES 
    		('Huawei mobile phone', '130000', 'Household Electric Appliances', 'Huawei');
    INSERT INTO goods (name, price, category, provider) VALUES 
    		('Mi TV', '231258', 'Household Electric Appliances', 'millet');
    INSERT INTO goods (name, price, category, provider) VALUES 
    		('Sliced Beef in Hot Chili Oil', '5830', 'video', 'Master Wang');
            
    INSERT INTO customers (cname, address, email, sex, idcard) VALUES 
    		('Small A', 'xx province xx city xx residential quarters', 'a@qq.com', 'female', '0101199910130018');
    INSERT INTO customers (cname, address, email, sex, idcard) VALUES 
    		('Small B', 'yy province yy city yy residential quarters', 'b@qq.com', 'male', '0101199910130017');
    INSERT INTO customers (cname, address, email, sex, idcard) VALUES 
    		('Small C', 'zz province zz city zz residential quarters', 'c@qq.com', 'female', '0101199910130019');
            
    -- be careful bought_at Default value for CURRENT_TIMESTAMP Usage of ---->Insert current time
    INSERT INTO purchase (cid, gid, price, num) VALUES 
    		(1, 3, 5830, 2);
    INSERT INTO purchase (cid, gid, price, num) VALUES 
    		(2, 3, 5830, 1);
    INSERT INTO purchase (cid, gid, price, num) VALUES 
    		(1, 1, 130000, 1);
    INSERT INTO purchase (cid, gid, price, num) VALUES 
    		(2, 2, 231258, 1);
    

Tags: Java Back-end

Posted on Sat, 20 Nov 2021 23:21:01 -0500 by ozace