MySQL Learning notes

MySQL is a database management system, mainly to the database to increase, modify, delete, query, with case-insensitive characteristics (input in uppercase, lowercase can be)

1. Basic commands

MySQL can log in directly through CMD, login command line: Mysql-u user name-p password/mysql-uroot-proot

1-1. DDL (data definition Language)

1-1-1, basic statement

View all databases: show databases;

Creating database: Create database name;/create;

Create the database and set the encoding for the database: Create database name default charset= ' UTF8 ';/create db name default charset= ' UTF8 ';

View the creation information for the database: show create database name;/show.

Delete database: drop database name;/drop;

Use a database (the database needs to be used later to manipulate the data in the database): using the database name;/use name;

View all tables (need to use database first): Show tables;

View the table creation information: Show create table table name;/show CREATE table name;

To create a table:

CREATE TABLE Table name (

Field Name 1 data type,

Field Name 2 data type,

Constraint fk_ field name foreign key field name 2 references table name 1 (field name 1)

); (Note: ")" before the last sentence can not have "," end)

CREATE TABLE name (

A int primary key auto_increment,

b varchar () NOT NULL,

C double (3,2) default 0,

D char (1) Check (d= ' male ' or d= ' female '),

Constraint Fk_b foreign key (b) References name1 (b)-the B in the name table is constrained by B in the name1 table, assuming that B in the Name1 table is the primary key and the data type is the same as B in the name table

) auto_increment=100;

Modify column type: ALTER TABLE name modify column name new type/alter table name modify B int;

Add column: ALTER TABLE name add column name type/alter table name add e int;

Delete column: ALTER TABLE name drop Column name/alter table Name Drop e;

Modify column name: ALTER TABLE name change old column name new column name type/alter table name b x int;

Modified table name: ALTER TABLE name rename new table name/alter table name rename name1; or rename table table name to new name/rename tables name to Name1

1-1-2, constraint statements

Constraint statements are used to constrain information such as the type, value, and so on data.

Non-empty (data cannot be empty): +not null after field;

Unique: Field after +unique (note: can have multiple null values);

Primary key: Field +primary key (note: Only one primary key in a table, primary key field is not empty and unique);

Auto Growth: Primary key column +auto_increment, if you need to start from a number of increments can be in the Create Table name () after +auto_increment= the starting value, If you do not set a starting value, the default starts at 1 (note: Only fields of type int can be used, with up to one self-increment in a table);

Set default value: +default+ default value After field;

Check constraint: field after +check+ condition (note: MySQL is ignored for check, that is, write is also white, MySQL is to maintain consistency with other databases to set the check constraints, other database check constraints are useful);

FOREIGN KEY constraint: foreign key establishes links in two or more tables to ensure the integrity of the data. The foreign key Reference column of a child table can only be a foreign key column of the primary table or a column with a unique constraint. The value of the column for the foreign KEY constraint in the child table must be within the value of the referenced column of the primary table, with 1, 2 in the even if primary table, and only 1 or 2 in the child table, and no other value. If a value is referenced in the primary table, the corresponding record of the primary table cannot be deleted, and the value in the primary table cannot be deleted until the corresponding delete in the child table is deleted.

1-2. DML (Data manipulation Language-control language)

Add data: INSERT into table name (column name 1, column Name 2 ...) Values (value 1, value 2 ...). )/insert into name (a,b,c,d) VALUES (1,2,3,4). If the value entered is all of the values in the table, you can save the parentheses after the token name, insert into name values (A,B,C,D);

Delete data: Delete from table name where column name = condition value/delete from name where A=1. If you need to delete all the data in the table, you can omit the where statement. In addition, you can use the TRUNCATE table name, which cannot use the Where condition restriction, can only delete all the data in the table directly, it is said that the deletion efficiency is higher than the DELETE statement;

Modify data: Update table name set column name = new value where column name = condition value and column name like '% condition value ';/update name set a=1 where b= ' 2 ' and like ' _ Flower% '; where and lie statements according to the actual situation With, like after the '% ' represents a number of indeterminate values, ' _ ' denotes an indeterminate character, the above as conditions can be found such as ' fig ', ' Plum blossom a flower ' and other content;

Query data: SELECT * FROM table name

where column name = condition value and/or like column name = condition value/select * from name where name.a=1 and name.b like ' 2 ', the statement is used to query all values in the table that satisfy the criteria, if only a few columns of content can be queried: s Elect column name 1, column name 2 from where column name = condition value and/or column name = condition value/select name.a,name.b from where name.a=1. Because the table name is longer can be abbreviated, the queried column name can also be abbreviated: SELECT column name as Alias 1, column name as alias 2 from table name alias 3 where Column name = condition value/select x3.a as ' number ', x3.b as ' content ' from name x3 Where X3.a=1, where as can be omitted.

1-3. DCL (Data Control Language language)

Standard SQL statements:

Select Display Columns (* denotes all columns)

From table name

Where/like conditions

Group By Column name (group)

Having condition (must have GROUP BY statement to be used)

Order BY column name (sort, default ascending, to descending need to add desc at the end of the statement)

The Limit Condition value (the number of rows returned by the limit start line number. If the start line number is not written, the default starts at 0, and the start line number is the actual line number-1)

Specify the Display column: Select column name 1, column name 2 from table name/select name.a,name.b from name.

Column name removal duplicates: SELECT DISTINCT column name 1, column name 2 from table name/select distinct name.a,name.b from name;

List name: Because the file name is not easy to use, you can simplify or change the column name display, select column name as Alias 1, column name as alias 2 from table name alias 3/select x3.a as ' number ', x3.b as ' content ' from name X3, Where as can be omitted;

Between A and B statements: Data between A and B: select * from name where Name.a between 1 and 10;

The query results can be nested using: Copy table CREATE TABLE newname select * from name; copy empty table CREATE TABLE newname select * from name where 1=2 or create Ta ble newname like name;

Aggregation functions:

COUNT (1) Total number of statistics

COUNT (distinct column name) statistics column name how many kinds

SUM (column name) sums the contents of a column name

AVG (column name) averages the contents of column names

The result of the column name is used for arithmetic calculation, i.e. can use + 、-、 *,/,%, etc.

Max (column name) find the maximum value in the column name

Min (column name) find the minimum value in the column name

Left (column name, number) to find out the ' number ' value in the column name

Right (column name, number) find out the ' number ' value in the column name

Full table Links: Link Two or more tables select * FROM table name 1, table name 2, table name 3 where table name 1. column name 1= table name 2. Column Name 2 and table name 2. column name 3= table name 3. Column Name 3 or select * FROM table name 1 inner JOIN table name 2 on table Name 1. column name 1= table name 2. Column Name 2 inner JOIN table name 3 on table Name 2. column name 3= table name 3. Column Name 3

External links: Left join/left outer joins, right outer links to the starboard join-outer JOIN. Left outer link is the left table all display, the right table has no corresponding information is filled with null, right outer link is the right table all display, the left table does not have the corresponding information is filled with null.

Important statement case When...then...else...end equivalent to If ... else ...

MySQL Learning notes