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

1. Concept

  database, folder

Database tables, files

A line of data in a file.

2. Initial:

Show databases; # check what data is in the current Mysql and what folders are in the root directory

Create database database name; create folder

Use database name; enter the directory using the selected database

Show tables; to see which tables are under the current database.

Create table table name (nid int,name varchar (20)); # create database table

Select * from table name; # View all data in the table

Insert to indicates (nid,name) values.

3. Authorize and create users

Mysql > select Host,User from user

+-+ +

| | Host | User |

+-+ +

| | localhost | mysql.session |

| | localhost | mysql.sys |

| | localhost | root |

+-+ +

3 rows in set (0.00 sec)

Special commands for user management:

Create a user

Create user 'username' @'IP address' identified by 'password'

Delete user

Drop user 'user name' @'IP address'

Modify user

Rename user 'user name' @'IP address'; to 'new user name' @'IP address'

Modify the password

Set password for 'username' @'IP address'= Password ('new password')

PS: user rights-related data is saved in the user table of the mysql database, so it can also be manipulated directly (not recommended)

Create a user

Mysql > create user xiaohu@localhost identified by '123'

Query OK, 0 rows affected (0.01 sec)

Mysql > select Host,User from user

+-+ +

| | Host | User |

+-+ +

| | localhost | mysql.session |

| | localhost | mysql.sys |

| | localhost | root |

| | localhost | xiaohu |

+-+ +

4 rows in set (0.00 sec)

Delete a user

Mysql > drop user xiaohu@localhost

Query OK, 0 rows affected (0.00 sec)

Change the name

Mysql > rename user davide@localhost to eric@127.0.0.1

Query OK, 0 rows affected (0.00 sec)

Mysql > select Host,User from user

+-+ +

| | Host | User |

+-+ +

| | 127.0.0.1 | eric |

| | localhost | mysql.session |

| | localhost | mysql.sys |

| | localhost | root |

+-+ +

4 rows in set (0.00 sec)

Set a password for the user

Mysql > set password for root@localhost = Password ('6666')

Query OK, 0 rows affected, 1 warning (0.00 sec)

Login failure the default login specified is localhost

C:\ Users\ Administrator > mysql-u eric-p

Enter password: *

ERROR 1045 (28000): Access denied for user 'eric'@'localhost' (using password: YES)

Use-h to specify the host to log in

C:\ Users\ Administrator > mysql-u eric-h 127.0.0.1-p

Enter password: *

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 7

Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

5. Permissions,

There is nothing by default.

Show grants for 'user' @'IP address'- View permissions

Grant permissions on database. Table to 'user' @'IP address'- Authorization

Revoke permissions on database. Table from 'user' @'IP address'- revoke permissions

Select, check

Database. Table

  test.tb1

All tables under the test.* database test

  *. *   all libraries and all tables

Tiger @ localhost

Permissions:

All permissions of all privileges except grant

Select check permissions only

Select,insert lookup and insert permissions

Usage has no access permission

Alter uses alter table

Alter routine uses alter procedure and drop procedure

Create uses create table

Create routine uses create procedure

Create temporary tables uses create temporary tables

Create user uses create user, drop user, rename user, and revoke all privileges

Create view uses create view

Delete uses delete

Drop uses drop table

Execute uses call and stored procedures

File uses select into outfile and load data infile

Grant option uses grant and revoke

Index uses index

Insert uses insert

Lock tables uses lock table

Process uses show full processlist

Select uses select

Show databases uses show databases

Show view uses show view

Update uses update

Reload uses flush

Shutdown uses mysqladmin shutdown (turn off MySQL)

Super uses change master, kill, logs, purge, master, and set global. It also allows mysqladmin debugging and login.

Access to replication client server location

Replication slave is used by replication slaves

User name @ IP address

Users can only access it under the changed IP.

User name @ 192.168.1% users can only access it under the changed IP segment (wildcard% indicates any)

User name @% user can be accessed under any IP (default IP address is%)

# clear the contents of the table and forget the password

# start the license-free server

Mysqld-skip-grant-tables

# client

Mysql-u root-p

# change username and password

Update mysql.user set authentication_string=password ('666') where user='root'

Flush privileges

# # Summary:

a. Liberate and collect, repeat the operation file, send the command directly to the mysql server, and operate automatically.

b. Database table

c. Create users and authorizations

Password: must be used

Other: recommended

d. Client connection MySQL provides client

1.mysql-u root-h 192.168.1.1-P 3306

4. SQL statement

A, database level

Show databases; view the current database

Create databases database name

CREATE DATABASE database name DEFAULT CHARSET utf8 COLLATE utf8_general_ci

Use database name; enter the database

Drop database database name

B, table level

Show tables; to see which tables are in the current database

Desc table name

# create tables table name (nid int,name varchar2 (20))

# transactions, atomic operations, rollback

A, default value

B, whether it can be empty

C, self-incrementing column (there can be only one table, it must be index-primary key)

D, primary key

A table can have only one primary key, which cannot be repeated and cannot be empty. In general, the self-incrementing column is set as the primary key

Unique index:

Can be null, and a table can have multiple unique columns

-constraint

-Index, speed up search

Create table student (

Name varchar2 (20) not null

Num int not null

Age int

Gender int

Primary key (name,num)

)

Create table tb5 (

Nid int not null auto_increment primary key

Name varchar2 (20)

Age int defualt 19

) engine=innodb default charset=utf8

Constraints:

Name num age

A 88 9

  a 99 9

# Primary key:

Cannot be null

Cannot be repeated

A table has only one primary key (multiple columns can form a primary key)

# General usage:

Nid int auto_increment primary key

Drop table table name; delete table

Delete from table name; # clear the contents of the table

Truncate table table name # clears the table contents with high performance and fast speed, deleting and self-increasing

Select * from table name; view table contents

E: foreign key: foreign key, one to many

Two tables to establish constraints

 -constraint

-foreign key, one to many

Mysql > alter table userinfo add constraint fk_u_p foreign key userinfo (part_nid) references part (nid)

  data row level

Select * from tb1

Increase

Insert into tb1 (name,age) values ('joy',19)

Insert into tb1 (name.age) values ('davide',19), (' jolin',18) # insert multiple pieces of data

Insert into table (column name, column name...) Select (column name, column name...) From table

Insert into tb31 (name,age) select caption,gender from tb32

Delete

Delete from table

Delete from table where id=1 and name='davide'

Change

Update table set name = 'davide' where id > 1

Check

Select * from table

Select * from table where id > 1

Select nid,name,gender as gg from table where id > 1

The data types of MySQL are roughly divided into: numeric, time and string f: data types:

5. Other

Concatenated table operation:

# connecting tables

Select * from a dint b where a.x = b.x

# left join

Select * from a left join b on a.x = b.x

# inner join # NULL will never appear

Select * from part inner join userinfo on userinfo.part_nid = part.nid

A, conditions

Select * from table where id > 1 and name! = 'davide' and num = 12

Select * from table where id between 5 and 16

Select * from table where id in (11 mine22 and 33)

Select * from table where id not in (11 mine22 and 33)

Select * from table where id in (select nid from table)

B, wildcard character

All (multiple strings) at the beginning of the select * from table where name like 'ale%'-ale

All (one character) at the beginning of the select * from table where name like 'ale_'-ale

C. Restrictions

Select * from table limit 5;-first 5 rows

Select * from table limit 4pm 5;-5 lines starting at line 4

Select * from table limit 5 offset 4-5 rows starting at line 4

D, sort

Select * from table order by column asc-arranged from smallest to largest according to "column"

Select * from table order by column desc-sort from largest to smallest according to "column"

Select * from table order by column 1 desc, column 2 asc-sort from largest to smallest according to "column 1" and, if the same, sort by column 2

E, grouping

Select num from table group by num

Mysql > select part_nid,count (nid) from userinfo group by part_nid

+-+ +

| | part_nid | count (nid) |

+-+ +

| | 1 | 2 |

| | 2 | 3 |

| | 4 | 1 |

+-+ +

Mysql > select part_nid as a minute count (nid) as b from userinfo group by part_nid;## has an alias

+-+

| | a | b | |

+-+

| | 1 | 2 |

| | 2 | 3 |

| | 4 | 1 |

+-+

Mysql > select part_nid as a minute count (nid) as b from userinfo group by part_nid having count (nid) > 1 Chinese characters # filter aggregate functions to adapt to having

+-+

| | a | b | |

+-+

| | 1 | 2 |

| | 2 | 3 |

+-+

2 rows in set (0.00 sec)

Select num,nid from table group by num,nid

Select num,nid from table where nid > 10 group by num,nid order nid desc

Select num,nid,count (*), sum (score), max (score), min (score) from table group by num,nid

Select num from table group by num having max (id) > 10

Special: group by must be after where and before order by

F, connected table

If there is no corresponding relationship, it will not be displayed.

Select A.num, A.name, B.name

From A,B

Where A.nid = B.nid

If there is no corresponding relationship, it will not be displayed.

Select A.num, A.name, B.name

From An inner join B

On A.nid = B.nid

Table An is all displayed. If there is no correspondence in B, the value is null.

Select A.num, A.name, B.name

From A left join B

On A.nid = B.nid

Table B shows all. If there is no corresponding relationship in B, the value is null.

Select A.num, A.name, B.name

From A right join B

On A.nid = B.nid

G, combination

Combination, automatic handling of coincident

Select nickname

From A

Union

Select name

From B

Combination, do not deal with coincidence

Select nickname

From A

Union all

Select name

From B

Bit [(M)]

Binary bit (101001). M represents the length of the binary bit (1-64). The default is msq1.

Tinyint [(m)] [unsigned] [zerofill]

A small integer, a data type used to hold a range of integer values:

Signed:

-128 ~ 127.

Unsigned:

0 ~ 255

Special: no Boolean value in MySQL, constructed using tinyint (1).

Int [(m)] [unsigned] [zerofill]

An integer, a data type used to hold a range of integer values:

Signed:

-2147483648 ~ 2147483647

Unsigned:

0 ~ 4294967295

Special: the m in the integer type is used for display only and there is no limit to the storage range. For example: int (5), when inserting data 2, the data is displayed as: 00002 when select

Bigint [(m)] [unsigned] [zerofill]

Large integers, data types used to hold a range of integer values:

Signed:

-9223372036854775808 ~ 9223372036854775807

Unsigned:

0 ~ 18446744073709551615

Decimal [(m [, d])] [unsigned] [zerofill]

The exact decimal value, m is the total number of numbers (minus sign is not counted), d is the number after the decimal point. The maximum value of m is 65 and the maximum value of d is 30.

Special: this type is required for accurate numerical calculations

The reason why decaimal can store exact values is that it is stored internally as a string.

FLOAT [(MMagar D)] [UNSIGNED] [ZEROFILL]

Single-precision floating point number (inexact decimal value), m is the total number of numbers, d is the number after the decimal point.

Unsigned:

-3.402823466E+38 to-1.175494351E-38

0

1.175494351E-38 to 3.402823466E+38

Signed:

0

1.175494351E-38 to 3.402823466E+38

* the higher the number, the less accurate it is.

DOUBLE [(MMagar D)] [UNSIGNED] [ZEROFILL]

Double-precision floating point number (inexact decimal value), m is the total number of numbers, d is the number after the decimal point.

Unsigned:

-1.7976931348623157E+308 to-2.2250738585072014E-308

0

2.2250738585072014E-308 to 1.7976931348623157E+308

Signed:

0

2.2250738585072014E-308 to 1.7976931348623157E+308

* the higher the number, the less accurate it is.

Char (m)

The char data type is used to represent a fixed-length string that can contain up to 255characters. Where m represents the length of the string.

PS: even if the data is less than m length, it will occupy m length.

Varchar (m)

The varchars data type is used for variable-length strings that can contain up to 255characters. Where m represents the maximum length of the string allowed to be saved by the data type, as long as the length is less than the maximum value, the string can be saved in the data type.

Note: although varchar is flexible to use, the processing speed of the char data type is faster from a system-wide performance perspective, sometimes exceeding 50% of the varchar processing speed. Therefore, users should comprehensively consider all aspects of factors when designing the database in order to achieve the best balance.

Text

The text data type is used to hold long large strings, which can be grouped up to 65535 (2 characters 16 − 1) characters.

Mediumtext

A TEXT column with a maximum length of 16777215 (2 hours 24 − 1) characters.

Longtext

A TEXT column with a maximum length of 4294967295 or 4GB (32 − 1) characters.

Enum

Enumerated type

An ENUM column can have a maximum of 65535 distinct elements. (The practical limit is less than 3000.)

Example:

CREATE TABLE shirts (

Name VARCHAR (40)

Size ENUM ('x medium', 'small',' medium', 'large',' x Mr. Large')

);

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), (' tMurshirtShirtMagna medium`), ('polo shirt','small')

Set

Collection type

A SET column can have a maximum of 64 distinct members.

Example:

CREATE TABLE myset (col SET ('averse,' baked, 'clocked,' d'))

INSERT INTO myset (col) VALUES ('arecory d'), (' drecincea'), ('arecalogical dd'), (' arecalogical dd'), ('drecinced')

DATE

YYYY-MM-DD (1000-01-01 Universe 9999-12-31)

TIME

HH:MM:SS ('- 838 lax 59 race 59)

YEAR

YYYY (1901amp 2155)

DATETIME

YYYY-MM-DD HH:MM:SS (1000-01-01 00-00-00-00-12-31 23:59:59 Y)

TIMESTAMP

YYYYMMDD HHMMSS (1970-01-01 00 VlGO 0000 VOGULAR sometime in 2037

1. Create a table

Create table table name (

Whether the column name type can be empty

Whether the column name type can be empty

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Whether it can be nullable. Null means empty, not a string.

Not null-not nullable

Null-Null

Default value, which can be specified when the column is created, and will be added automatically if it is not actively set when inserting data

Create table tb1 (

Nid int not null defalut 2

Num int not null

)

Self-increment. If you set a self-increment column for a column, you do not need to set this column when inserting data. It will be auto-incremented by default (there can be only one self-increment column in the table).

Create table tb1 (

Nid int not null auto_increment primary key

Num int null

)

Or

Create table tb1 (

Nid int not null auto_increment

Num int null

Index (nid)

)

Note: 1. For self-incrementing columns, it must be an index (including primary keys).

Common indexes in Mysql are:

A, general index

B, unique index

C, primary key index

D, combined index

A. the only function of a general index is to speed up search.

Create table + index

Mysql > create table tb21 (

-> nid int not null auto_increment primary key

-> name varchar (32) not null

-> email varchar (64) not null

-> extra text

-> index ix_name (name)) engine=innodb default charset=utf8

Query OK, 0 rows affected (.53 sec)

Create a normal index

Create unique index index name on table name (column name)

Delete a normal index

Drop index index_name on table_name

View Index

Show index from table_name

B, unique index

A unique index has two functions: accelerated lookup and unique constraints (can include null)

Mysql > create table tb21 (

-> nid int not null auto_increment primary key

-> name varchar (32) not null

-> email varchar (64) not null

-> extra text

-> unique index ix_name (name)) engine=innodb default charset=utf8

Create a unique index

Create unique index index name on table name (column name)

Delete unique index

Drop unique index index_name on table_name

C. The primary key index has two functions: accelerated query and unique constraints (null is not allowed)

Create table and primary key indexes

Mysql > create table tb22 (

-> nid int not null auto_increment primary key

-> name varchar (32) not null

-> email varchar (64) not null

-> extra text

-> index ix_name (name)) engine=innodb default charset=utf8

Create a primary key index

Alter table table name add primary key (column name)

Delete the primary key index (if the primary key index of a primary key cannot be deleted directly, you should first cancel the self-growth and delete the characteristics of the primary key)

Mysql > alter table tb22 modify nid int

Query OK, 0 rows affected (0.73 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table tb22 drop primary key

Query OK, 0 rows affected (0.83 sec)

Records: 0 Duplicates: 0 Warnings: 0

D, combined index

A combined index is a combination of N columns into a single index. (1. Divided into a general composite index, without constraints and unique combined index, two columns of data can not be inserted at the same time, otherwise an error will be reported)

The leftmost matching is performed in the search process, and if the leftmost index value is not selected, the index is not taken.

Create a tabl

Mysql > create table tb22 (

-> nid int not null auto_increment primary key

-> name varchar (32) not null

-> email varchar (64) not null

-> extra text

-> index ix_name (name)) engine=innodb default charset=utf8

Create a composite index

Create index ix_name_email on tb22 (name,email)

2. For self-increment, you can set the step size and starting value.

Show session variables like 'auto_inc%'

Set session auto_increment_increment=2

Set session auto_increment_offset=10

Shwo global variables like 'auto_inc%'

Set global auto_increment_increment=2

Set global auto_increment_offset=10

The primary key, a special unique index, does not allow null values, if the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique.

Create table tb1 (

Nid int not null auto_increment primary key

Num int null

)

Or

Create table tb1 (

Nid int not null

Num int not null

Primary key (nid,num)

)

Foreign key, a special index, can only be specified content

Creat table color (

Nid int not null primary key

Name char (16) not null

)

Create table fruit (

Nid int not null primary key

Smt char (32) null

Color_id int not null

Constraint fk_cc foreign key (color_id) references color (nid)

)

2. Delete the table

Drop table table name

3. Clear the table

Delete from table name

Truncate table table name

4. Modify the table

Add column: alter table table name add column name type

Delete column: alter table table name drop column column name

Modify the column:

Alter table table name modify column column name type;-- type

Alter table table name change original column name new column name type;-- column name, type

Add a primary key:

Alter table table name add primary key (column name)

Delete the primary key:

Alter table table name drop primary key

Alter table table name modify column name int, drop primary key

Add foreign key: alter table slave table add constraint foreign key name (such as: FK_ slave table _ master table) foreign key slave table (foreign key field) references master table (primary key field)

Mysql > alter table userinfo add constraint fk_u_p foreign key userinfo (part_nid) references part (nid)

Delete foreign key: alter table table name drop foreign key foreign key name

Modify the default value: ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000

Delete default: ALTER TABLE testalter_tbl ALTER i DROP DEFAULT

# modify the table with 4 rows from the third row

Add column: alter table table name add column name type

Delete column: alter table table name drop column column name

Modify the column:

Alter table table name modify column column name type;-- type

Alter table table name change original column name new column name type;-- column name, type

Add a primary key:

Alter table table name add primary key (column name)

Delete the primary key:

Alter table table name drop primary key

Alter table table name modify column name int, drop primary key

Add foreign key: alter table slave table add constraint foreign key name (such as: FK_ slave table _ master table) foreign key slave table (foreign key field) references master table (primary key field)

Delete foreign key: alter table table name drop foreign key foreign key name

Modify the default value: ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000

Delete default: ALTER TABLE testalter_tbl ALTER i DROP DEFAULT

# Summary # #

1. Database concept

2. Database, table, row

3. Start the server side and start the client side to connect to the server

User management

Authority management

4. SQL statement

Database operation

Table operation

Whether it can be empty

Default value

Primary key

Foreign key

Self-increasing

  data type

Data row operation

Increase

Delete

Change

  check

Other

Limit offset

Left join

Order by

Group by,having

Aggregate function

Max mix sun count

Pagination

The first few lines, select    .room3

Limit 3pr 4 # the third line starts with 4 lines.

The third line of limit 4 offset starts with 4 lines.

Connected table

Left join on

# Table 1 left join Table 2 on

# if there is more data in Table 1 and less in Table 2, null adds

Right join

Inner join

# if there is more data in Table 1 and less in Table 2, null will not display it

Sort

Desc

Asc Note: when searching n conditions at the same time, the performance of combined indexes is better than that of multiple single index merging.

Nid name

1    davide

2    eric

3 davide

4 davide

From small to small, id from big to small

Select * from tb1 order by name asc,nid desc

Union

Union

Union all

Condition: > < =! =

In (1, 2, 3, 4)

Not in (.)

In (select nid from userinfo)

Connected table, foreign key

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.