MySQL (1)-basic syntax and common statements, mysql basic syntax

MySQL (1)-basic syntax and common statements, mysql basic syntax

A collection of data that is processed by a computer and can be accessed efficiently is called a Database (DB ).
Save the data such as name, address, phone number, email address, hobbies, and family to the database to quickly obtain the desired information. The computer System used to manage databases is called the Database Management System (DBMS ).
DBMS has been classified by data storage formats (Database types). There are currently five types: Hierarchical Database and HDB, Relational Database (RDB ), object-Oriented Database (OODB), XML Database (XMLDB), and Key-Value storage system (KVS ).
DBMS is called Relational Database Management System (RDBMS ). Representative RDBMS include Oracle Database: Oracle, SQL Server: Microsoft, DB2: IBM, PostgreSQL: Open Source, MySQL: Open Source.
MySQL is one of the good RDBMS application software, and the usage is also upup. Because of laziness, the operations in this article are verified only on MySQL5.7.

• Preparation
  • 1. Install MySQL
  • 2. Start the server
  • 3. Client Connection
  • 4. SQL statement Classification
• I. Database Operations
  • 1. display the database
  • 2. Create a database
  • 3. Use a database
  • 4. User Management
  • 5. authorization management
• Ii. Data Table operations
  • 1. Create a table
  • 2. delete a table
  • 3. Clear the table
  • 4. modify a table
• Iii. Table content operations
  • 1. Add
  • 2. Delete
  • 3. Change
  • 4. Query
  • 5. Deep Query

0. Prepare 1. Install MySQL

Http://www.cnblogs.com/lidyan/p/6587718.html

2. Start the server

mysql.server start

Bytes

3. Client Connection

Mysql-u username-p -- exit QUIT or Control + D

Bytes
Bytes

4. SQL statement Classification

• DDL (Data Definition Language) is used to create or delete databases for storing Data and tables in databases. DDL includes the following commands.

DDL (Data Definition Language)
CREATE: Creates databases, tables, and other objects.
DROP: deletes objects such as databases and tables.
ALTER: modify the structure of objects such as databases and tables

• DML (Data Manipulation Language) is used to query or change records in a table. DML contains the following commands.

DML (data manipulation language)
SELECT: Query table data
INSERT: INSERT new data into the table
UPDATE: UPDATE table data.
DELETE: DELETE table data

• DCL (Data Control Language) is used to confirm or cancel changes to Data in the database. In addition, you can also set whether RDBMS users have the permission to operate on objects in the database (database tables, etc. DCL contains the following commands.

DCL (Data Control Language)
COMMIT: confirm the changes made to the data in the database
ROLLBACK: cancels changes to data in the database.
GRANT: GRANT the user operation permissions.
REVOKE: cancels the user's operation permission.

I. Database Operations 1. display the database

SHOW DATABASES;

Default databases include the following:
Mysql-user permission-related data
Test-used for user test data
Information_schema-MySQL architecture data

2. Create a database

Create database name default charset utf8 COLLATE utf8_general_ci;

Generally, it is utf8. Subsequent searches and various gadgets can be used.

3. Use a database

-- USE the database USE name; -- display show tables for all TABLES in the currently used database;

4. User Management

-- Create user 'username' @ 'IP address' identified by 'Password'; -- delete user drop user 'username' @ 'IP address '; -- modify the user rename user 'username' @ 'IP address'; to 'new username' @ 'IP address '; -- change the password set Password for 'username' @ 'IP address' = password ('new password ');

User permission-related data is stored in the user table of the mysql database, but it is not recommended to directly operate on it.

5. authorization management

-- View the permission show grants for 'user' @ 'IP address'; -- grant the permission on the database. table to 'user' @ 'IP address'; -- revoke the revoke permission on the database. table from 'user' @ 'IP address ';

Frequently Used permissions:
All privileges-all permissions except grant
Select-query permissions only
Select, insert-query, and insert Permissions

Use * to match the Database Name and Table Name:
Test. *-test all tables in the database
*. *-All tables in all databases

Use % to match IP addresses

-- For example, grant all privileges on *. * TO 'username' @ '% ';

Ii. Data Table operation 1. Create a table

Create table Name (column name type NULL, column name type not null) ENGINE = InnoDB default charset = utf8

Basic Data Type
MySQL data types include numeric, numeric, and date.

• INT type: Specifies the data type (number type) of the columns that store integers. decimals cannot be stored.
• CHAR type: Specifies the data type (Bytes type) of the columns that store strings ). You can specify the length (maximum length) of the string that can be stored in this column in parentheses like CHAR (200 ). The portion of the string that exceeds the maximum length cannot be entered in this column. When the length of the string stored in the column cannot reach the maximum length, use a half-width space.
• VARCHAR: You can also specify the maximum length of a string (character type) by using numbers in parentheses ). However, this type of column stores strings in the form of a variable-length string. A variable-length string is not supplemented with spaces even if the number of characters does not reach the maximum length.
• DATE type: Specifies the data type (DATE type) of the column that stores the DATE (year, month, and day ).

More data types

Default Value
You can specify the default value when creating a column.

create table tb1(nid int not null default 2,num int not null)   

Auto-Increment
If you set an auto-increment column for a column, you do not need to set this column when inserting data. By default, auto-increment columns are automatically added (only one auto-increment column is allowed in the table ).

create table tb1(nid int not null auto_increment primary key,num int null)create table tb1(nid int not null auto_increment,num int null,index(nid))

Note:
1. the auto-increment column must be an index (including a primary key ).
2. You can set the step size and start value for auto-increment.
Show session variables like 'Auto _ inc % ';
Set session auto_increment_increment = 2;
Set session auto_increment_offset = 10;

Show global variables like 'Auto _ inc % ';
Set global auto_increment_increment = 2;
Set global auto_increment_offset = 10;

Primary Key
A special unique index does not allow null values. If the primary key uses a single column, its value must be unique. If the primary key is multiple columns, its combination must be unique.

create table tb1(nid int not null auto_increment primary key,num int null)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

create 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 a table

Drop table Name

3. Clear the table

Delete from table name truncate table name

4. modify a table

-- Add column alter table name add column type -- delete column alter table Name drop column name -- modify column -- type alter table name modify column name type; -- column name, type: alter table name change original column name new column name type; -- add primary key alter table name add primary key (column name); -- delete primary key alter table Name drop primary key; alter table name modify column name int, drop primary key; -- add the foreign key alter table from the table add constraint foreign key name (such as: FK _ slave table _ master table) foreign key from the table (foreign key field) references main table (primary key field ); -- delete the foreign key alter table TABLE Name drop foreign key name -- modify the DEFAULT value alter table testalter_tbl ALTER I set default 1000; -- delete the DEFAULT value ALTER table testalter_tbl ALTER I DROP DEFAULT;

Iii. Table content operations 1. Add

Insert into table (column name, column name ...) values (value, DEFAULT, value ...) insert into table (column name, column name ...) values (value, value, value ...), (value, value, value ...) insert into Table A (column name, column name ...) select (column name, column name ...) from table B

2. Delete

-- Keep the data table and delete all rows delete from table where id = 1 and name = 'dyance'; truncate table

3. Change

Update table set name = 'dya' where id> 1;

4. Query

Select * from Table select * from table where id> 1 select nid, name, gender as new table name from table where id> 1 subquery operator =, <>,>,> =, <, <= is null, not, and, or,

5. Deep Query

1) conditional select * from table where id> 1 and name! = 'Dya' and num = 12; select * from table where id between 5 and 16; select * from table where id in (, 33 ); select * from table where id not in (11,22, 33); select * from table where id in (select nid from table); 2) aggregate COUNT: calculate the number of records (number of rows) in the table SUM: calculate the total value of the data in the value column in the table AVG: calculate the average value of the data in the value column in the table MAX: MIN: calculates the maximum value of data in any column in the Table. MIN: calculates the minimum value of data in any column in the table. The latter obtains the select count (*), count (<column Name>) for data rows other than NULL) from table name; 3) wildcard select * from table where name like 'ale % '-ale all (multiple strings) select * from table where name like 'ale _ '-All (one character) Starting with 'ale _' 4) limit select * from Table limit 5; -select * from Table limit 4, 5 from the first five rows;-select * from Table limit 5 offset 4 from the first five rows;-5 rows starting from 4th rows 5) group select num from Table group by num 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, before order by 6) select. num,. name, B. name from A, B Where. nid = B. if the nid has no ing relationship, select A is not displayed. num,. name, B. name from A inner join B on. nid = B. all nid A tables are displayed. If B does not have A ing relationship, the value is null select. num,. name, B. name from A left join B on. nid = B. all nid B Tables are displayed. If B has no ing relationship, the value is null select. num,. name, B. name from A right join B on. nid = B. nid 7) Sort select * from Table order by column-sort by column from small to large, the default asc ascending select * from Table order by column desc-Sort select * from Table order by column 1 desc according to "column, column 2 asc-column 1 is arranged in ascending order. If they are the same, column 2 is sorted in ascending order. Column 1, count (*) from table group by column name 1 order by count (*) 8) combination, automatic processing of overlapping select nickname from A union select name from B combination, do not Overlap select nickname from A union all select name from B