Shulou(Shulou.com)06/01 Report--

The reason for the creation will not be explained in detail, but go straight to the topic.

Make a note of the commands you need to know.

The first step is to understand the concept:

MariaDB is based on mysql, so command the lingua franca mysql.

The process is roughly as follows:

As shown in the picture

After logging in to the database, you can use SHOW DATABASES; to query the database.

You can use use database 1; enter the database

Show tables; # queries all tables of this data.

Select * from Table name # query all data in the table

Command line shortcut editing

Ctrl+a: quickly move the cursor to the beginning of the line

Ctrl+e: quickly move the cursor to the end of the line

Ctrl+w: delete the word before the cursor

Ctrl+u: delete everything from the beginning of the line to the cursor

Ctrl+y: paste content deleted using Ctrl+w or Ctrl+u

There's too much nonsense, let's get started!

Create a database named rexhome

Now we know the orders for the following two days:

CREATE DATABASE database name

DROP DATABASE database name

Option description

AUTO_INCREMENT defines self-increasing sequence

Varchar (#) defines variable length characters

Char (#) defines the character length and occupies a fixed amount.

DEFAULT 'default value' defines the default value of the column

INDEX definition index

[NOT] NULL allows / disables null values

PRIMARY KEY defines the column primary key

Uniqueness of UNIQUE definition

CHECK defines the range / options for which values can be entered

1. The simplest:

CREATE TABLE T1 (

Id int not null

Name char (20)

);

2. With primary key:

A:

CREATE TABLE T1 (

Id int not null primary key

Name char (20)

);

B: compound primary key

CREATE TABLE T1 (

Id int not null

Name char (20)

Primary key (id,name)

);

3. With default value:

CREATE TABLE T1 (

Id int not null default 0 primary key

Name char (20) default'1'

);

CREATE TABLE MYHOME (

Id int not null default 1

Name varchar (20) not null

Sex char (50)

);

DESC MYHOME; # query the table

CREATE TABLE `test`.`table1` (

# # creating table1 Table in test Database

`id`INT (3) NOT NULL

# Line 1 is an id integer (3 characters), not null is not empty

`name` VARCHAR (20) NOT NULL

# Line 2 is name varchar variable length characters (20) non-empty

`sex` SET ('man','women') NOT NULL

# Line 3 is sex,set ('man','women'). Only these two types of text are allowed.

PRIMARY KEY (`id`)

# the primary key is id line

INDEX `name` (`name`)

# Index is name row

) ENGINE = InnoDB

# Storage caused by innodb

This is the table data created from phpmyadmin, we can follow the command to learn the opposite mysql statement, which is commonly used here.

Insert data

INSERT INTO `test`.`table1` (`id`, `name`, `sex`) VALUES ('1th,' rex', 'man')

# # values (1, rex, man) corresponding to (id, name, sex) in the table1 table of test database

SELECT * FROM `table1` WHERE `id` = 1

# # query the data with id number 1 in all types of table1 table

ALTER TABLE `table1` DROP PRIMARY KEY, ADD PRIMARY KEY (`sex`)

# # Delete the table1 host and update the sex field as the new primary key

INSERT INTO `test`.`table1` (`id`, `name`, `sex`) VALUES (ASCII ('2'), ASCII ('111'),' women')

# # insert id,name,sex corresponding data into test database (the ASCII type is 2, 1, 1, 11, women)

UPDATE `test`.`table1` SET `id` ='1' WHERE `table1`.`sex` = 'women'

# # in the test database to find the table1 table, sex is the row of women, and update id to 1

DELETE FROM `test`.`table1` WHERE `table1`.`sex` =\ 'man\'

# # delete the row of sex=man in the table1 table of test database (note that because the sex field is the primary key, the deleted row will be found according to the primary key)

A primary key is a type of unique index. A table can have only one primary key, but can have multiple unique indexes. The unique index is easy to understand, that is, uniqueness. You can be found through × × ×, and that × × × is the primary key.

But in addition to finding you, you can also be found by which job number in which department of your company. This is the only index.

The unique index content is not repeatable, my age, and your age.

The general index has no restrictions, but it is very helpful for big data query.

For example, if you check your credit card spending records in November, there are tens of millions of records in the bank's database. If you do not build an index, it may take 1 minute or 2 minutes to query. The efficiency is too slow. Building an index database according to the index will improve the efficiency a lot. We can find out in milliseconds.

Foreign keys are not very practical, that is, the relationship between some fields in the two tables,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.