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

This article mainly introduces MySQL internal and external connections and sub-query analysis, hoping to give you some knowledge to supplement and update, if there are other issues to understand, you can continue to pay attention to my updated articles in the industry information.

Log in to mysql:

mysql -u root -p

Enter password:

mysqladmin -u root -p[oldpassword] password newpassword

Old password optional, null if not (understand)

Connect to mysql using Navicat_for_MySQL client tool

If you cannot connect remotely, enter mysql command line in virtual machine and enter the following command:

GRANT ALL PRIVILEGES ON . TO 'USERNAME'@'%' IDENTIFIED BY ' PASSWORD ' WITH GRANT OPTION;

Show all databases

show databases;

Create database fcy

create database fcy default charset=utf8;

delete database

drop database db1

Switch to fcy database

use fcy;

View all tables

show tables;

create tables

create table person_info

(

person_id smallint(5) unsigned auto_increment,

name varchar(50) not null comment 'name',

country varchar(60) default 'China',

salary decimal(10,2) default 0.00 comment 'salary',

primary key (person_id)

）engine=innodb default charset=utf8;

delete table

drop table person_info123

View table structure

desc person_info;

insert data

insert into person_info(name, country, salary) values

('xiaoqiang', 'China', 1000.20),

('xiaowang', 'USA', 800.10),

('xiaozhang', 'UK', 300),

('xiaohu', 'Canada', 600.45);

Modify Table Structure--Add Fields

alter table person_info add sex VARCHAR(4)

Modify Table Structure--Modify Field Type

alter table person_info modify sex INT(4)

alter table person_info change sex sex INT(4)

Modify Table Structure--Modify Field Name

alter table person_info change sex sex_123 INT(4)

Modify Table Structure--Delete Fields

alter table person_info drop sex_123

query

SELECT * from person_info where name = 'xiao' and salary = 20

inserted

insert into person_info(name, salary) VALUES('xiao', 10)

delete

DELETE from person_info where salary = 20

modify

UPDATE person_info set salary = 20 where name = 'xiao'

find

select from person_info where name like '%xiaoqiang%';

select from person_info where name like 'xiao%';

select * from person_info where name like '%qiang';

Sort (default ascending asc, descending desc)

select from person_info order by name;

select from person_info order by country desc, salary ASC;

Count how many records there are

select count(*) as totalcount from person_info;

deduplication

SELECT count(distinct(country)) from person_info;

summing

select sum(salary) as sumvalue from person_info;

average

select avg(salary) as sumvalue from person_info;

largest

select max(salary) as sumvalue from person_info;

minimum

select min(salary) as sumvalue from person_info;

#table join (inner join)

select Table 1. Column 1, Table 2. Column 2 from Table 1, Table 2 where Table 1. Column 3 = Table 2. Column 3;

#off-sheet connections

select Table 1. Column 1, Table 2. Column 2 from Table 1 left join Table 2 on Table 1. Column 3 = Table 2. Column 3;

select Table 1. Column 1, Table 2. Column 2 from Table 1 right join Table 2 on Table 1. Column 3 = Table 2. Column 3;

#Subquery

select * from Table 1 where Column 1 in[=] (select A_id from Table 2 where Column 2 xxx);

#Table Connection Exercise:

New Table A

create table A(

id smallint(5) unsigned auto_increment,

name varchar(50) not null,

primary key(id)

)engine=innodb default charset=utf8;

New Table B

create table B(

id smallint(5) unsigned auto_increment,

address varchar(50) not null,

A_id smallint(5) unsigned,

primary key(id)

)engine=innodb default charset=utf8;

insert data

insert into A (name) values ('zhang'), ('li'), ('wang');

insert into B (address, A_id) values ('beijing',1), ('shanghai',3), ('nanjing',10);

Inner Link Exercise

select A.name, B.address from A, B where A.id = B.A_id;

Left-link exercise, all fields of the left table are displayed

select A.name, B.address from A left join B on A.id = B.A_id;

Right link exercise, all fields of right table are displayed

select A.name, B.address from A right join B on A.id = B.A_id;

Subquery Exercise

select from A where id in (select A_id from B where address='beijing');

select from A where id in (select A_id from B where address='beijing' or address='shanghai');

Internal connections are recommended, which are more efficient than the other three. Inserting four pieces of data once was more efficient than inserting four pieces of data once.

Read the above on MySQL internal and external connections and sub-query analysis, I hope to give you some help in the actual application. Due to the limited space of this article, it is inevitable that there will be deficiencies and needs to be supplemented. If you need more professional answers, you can contact our 24-hour pre-sales service on the official website to help you answer your questions at any time.

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.