Getting started with MySQL

Getting started with MySQLIntroduction to the database:

• Purpose: Used to store almost all the data of life, such as: identity, housing, ticket, website 、...

• Concept: Database server, database, data table, row of data (one), one column of data (field)

• Classification:

  • relational database: MySQL, Oracle, SQL Server 、...

  • Non-relational database: Redis, MongoDB 、...

• Development: Oral

• sql:structured query Language, structured querying language

• SQL classification:

  • Data definition language: DDL

  • Data manipulation Language: DML

  • Data Query Language: DQL

  • Data Control Language: DCL

  • Data Transaction language: DTL

MySQL (Ubuntu) Installation:

• Installation:sudo apt-get install mysql-server

  • If the installation of a system service problem, it is likely to add the system service problems, remove

• Security Configuration:sudo mysql_secure_installation

  • Follow the instructions, go all the way.

  • Set up support for remote connections, prepare for the back

• Connection test:mysql -h host -u user -p

  • -H: Specifies the host, localhost/127.0.0.1 represents the machine, the machine can omit

  • -u: Specify user, default to root user only

  • -P: Specify password, enter password to separate from login, safekeeping

  • Example: and mysql [-hlocalhost] -uroot -p then follow the prompts to enter the password

    • Port: 3306

    • Exit: \h or help;

    • Description: All commands are in the '; ' End

    ?

Data Definition language (DDL)

• View Library: show databases; Displays all databases on the database server

• Create library: create database test; , create DATABASE test

• Delete Library: drop database test; , delete database test

• Select library: use test; , select Database Test

  • To view the currently selected database:

    • Show tables;

    • Select Database ();

• View tables: show tables; to view the data tables in the currently selected database

• To create a table:create table user(username varchar(20), password char(32));

• To view the table structure:desc user;

• To delete a table:drop table user;

• View how to create:

  • Library:show create database test;

  • Table:show create table user;

• To modify a character set:

  • Specify when creating: Specify a character set after the statement that created the table.

  • Modify the configuration file: sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf , add after [mysqld]

    • character-set-server=utf8

    • Restart Service:sudo service mysql restart

    • Description: The default character set for tables in libraries created later is UTF8

• Modify field: alter table user modify username char(30); , cannot modify name

• To add a field:

  • Default (last) Add:alter table user add age int(3);

  • After specifying a field, add:alter table user add email varchar(60) after password;

  • At the beginning of the add:alter table user add id int(11) first;

• To delete a field:alter table user drop age;

• Modify Field Name:alter table user change email em char(32);

• Modify location and name: Add/change/modify

  • alter table user modify em char(32) first;

• Modify Table Name:alter table user rename new_user;

Data Type

• Integer: Tinyint (1 bytes), smallint (2 bytes), Mediumint (3 bytes), int (4 bytes), bigint (8 bytes)

• Floating-point types:

  • Float (M, d), single-precision floating-point number, 4 bytes, m for total digits, d for decimal digits

  • Double (M, d), dual-precision floating-point number, 8 bytes, m for total digits, d for decimal digits

  • Decimal (M, d), storing floating-point numbers as strings for demanding scenarios such as the financial sector

• Character type:

  • Char: fixed-length string, 0~255 bytes

  • VARCHAR: variable length string, 0~65535 bytes

• Time and Date:

  • Date: Day, Format: 2018-04-16

  • Time: Date, Format: 15:36:30

  • DateTime: Date Time, format: 2018-04-16 15:37:38

  • Timestamp: Time stamp

  • Year: Years, only 1 bytes, year range: 1901~2155

• Conformance Type:

  • Set: Collection type, Format: Set (S1, S2, ..., S63), up to 63 possible

  • Enum: Enum type, Format: Enum (E1,e2, ..., e65535), maximum of 65535 possible

• field decoration:

  • unsigned: unsigned number

  • Zerofill: High 0 padding to prevent negative numbers

  • auto_increment: auto-increment (1) for integers, often in conjunction with primary keys

  • default: Set defaults

  • NOT NULL: cannot be null

• Character Set and storage engine

  • View supported character sets: show character set; We usually only use UTF8 and cannot write into Utf-8

  • To view the supported storage engines:show engines;

    • Common storage engines: MyISAM and InnoDB

• Index:

  • Description: Simple understanding, is a book in the front of the table of contents, although can improve the efficiency of reading, not more than the better.

  • Category:

    • Span class= "Md-line md-end-block" > normal index: most basic index

    • unique index (unique): Decorated fields cannot be duplicated

    • primary key index (primary key): is a special unique index that can only have one field set in a table

    • full-text index (FULLTEXT): Multi-global Data add index.

  • example:

      ALTER TABLE user add index (EM);     # Add normal index to EM field 
      al ter table User add unique (username);   # Add a unique index to the username field 
      ALTER TABLE user add primary key (ID);   # Set the ID as the primary key index 
      ? 
      ALTER TABLE user drop index em;     # Delete the normal index of the EM field   /span>               
     

    ALTER TABLE name add fulltext (field);

• Many options can be specified when a table is created, such as:

  CREATE TABLE User (
  ID int auto_increment,
  Name varchar (20),
  Primary key (ID),
  Unique (name)
  ) Engine=innodb default Charset=utf8;

　　

Getting started with MySQL