Getting started with MySQL

First, the basic operation

#mysql-H localhost-u root-p;//link MySQL

#mysql-u root-p password 123456;//Modify the root user password to 123456

>show databases;//View data exists

>create database db1;//Creating the Databases DB1

>use db1;//into the database db1

>drop database db1;//Delete the databases DB1

>show tables;//View Table exists

>exit;//quit MySQL

>create table person (id int (4), name Char (ten), age int);//CREATE TABLE person

>create table P (id int,name char) charset=gbk;//create tables that support inserting Chinese (GBK/UTF8) records p

>create table p (id int auto_increment primary key,name char (TEN) not null,age int default 100);//Column integrity constraint creation

>desc person;//View Table structure description

>insert into person (id,name,age) VALUES (1, ' AAA ', 22);//Inserting records into table person

>insert into person set id=1,name= ' aaa ', age=22;//another way to insert

>select * from person;//querying all records

>update person set age=20 where name= ' AAA ';//Update data record

Where Condition **********

1.where followed by a logical expression;

2. The logical expression can use the comparison symbol inside, such as =,<,>,<=,>=,<>, as well as between...and,in and other operators;

3. The logical expression can be operated by And,or;

***************************

>delete from the person where id=1;//deletes a record

>truncate person;//Empty table

>drop table person;//Deleting tables person

Modifier *****************

1. Primary key: After all fields have been written, add a primary key (Col1,col2,....)

2. Self-increment: auto_increment

3. Non-empty: NOT NULL

4. Unique: Unique

5. Defaults: Default value

*****************************

Second, query simple query

SELECT * is the least efficient way to query.

Syntax format: Select column 1, column 2 ... from table name other conditions

>select name,age from the person where id=1;

>select name as name, age as ages from person;//specify alias

Statistics: Count (column name), sum (column name), max (column name), min (column name), AVG (column name)

>select now ();//Current time

>select 8*7*9;//Calculator

>select distinct id,age from person;//de-weight

>select Id,name from the person where ID between 3 and 7;//query ID records between 3 and 7

>select name from the person where ID is not in (3,4,5);//record with Query ID not 3,4,5

>select name from the person where the age was not null;//query for records that are not empty

Not can be paired with between,in,is, which means reverse.

>select name from the person where the name is like '%a_ ';//Query The record with the second-to-last character of the first name

>select name from the person where name regexp ' large ';//Regular expression query with large records

>select count (name) from people where group by age;//statistics by age group

>select count (name) from the person where the group by age has count (name) >1;//filter out records with a number of 1

>select count (name) from the person where the group by-age has age!=40;//ages 40 does not participate in grouping

	where	Having
Order	Before the group	After the group
Available fields	All	Group,select, outside query
Habit	Common	With GROUP by

>select * FROM person limit 0,3;//top three records

>select * from person ORDER by name Asc,age desc;//query results sorted by name ascending age Descending

>select * FROM P1 Union SELECT * from p2;//Union, de-weight

>select * from P1 UNION ALL SELECT * FROM p2;//Union, not heavy

Sub-query

Nesting a query statement into another query, insert, modify, DELETE statement, the query is a subquery.

Instance:

>create table P1 (id int,name char (TEN), age int) Charset=utf8;

>create table P2 (id int,name char (TEN), age int) Charset=utf8;

>select id,name,age from P1 where id>= (select Max (id) from P2);//where type subquery (single scalar)

>select id,name,age from P1 where ID in (select ID from p2);//Query

>select * FROM (SELECT * from P1 where Id<=floor (Ran ())) as P3 where name regexp ' large ';//from type subquery

>select * from P1 where exists (select * from P2 where p1.age=p2.age);//exists type subquery

Connection Query

A connection query is a multi-table query.

Outer connection: Left JOIN, right connection, full connection (MySQL does not support full connection)

>select * from P1 ieft join P2 on p1.id=p2.id;//left connection

>select * from P1 right joins P2 on p1.id=p2.id;//

Cross-linking: Cartesian product between tables

>select * from P1,P2;

Inner connection: The connection conditions for each record are the same

>select * from P1 inner joins P2 on p1.id=p2.id;//inner connection 1

>select * from P1 INNER join p2 using (ID);//Internal Connection 2

Natural connections:

>select * from P1 natural join P2;

Getting started with MySQL