MySQL (a)--basic syntax and common statements

Storing large amounts of data, a computer-processed collection of data that can be accessed efficiently is called a database (DATABASE,DB).

Save your name, address, phone number, email address, hobbies, and family composition to a database so you can get the information you want quickly and easily. The computer system used to administer the database is called the Database management system (Management System,dbms).

DBMS has data preservation format (the type of database) to classify, at this stage there are mainly five types: hierarchical database (Hierarchical DATABASE,HDB), relational database (relational Database,rdb), Object-oriented database (oriented DATABASE,OODB), XML Database (XML DATABASE,XMLDB), key-value storage System (Key-value STORE,KVS).

The DBMS is called a relational database management system (relational Management System,rdbms). Compare representative RDBMS with Oracle Database: Oracle; SQL Server: Microsoft; db2:ibm Company; PostgreSQL: Open source; MySQL: Open source.

MySQL is one of the best RDBMS applications, and the usage rate is upup. Because of laziness, the operation is only validated on MySQL5.7.

• 0. Preparation
  • 1. Install MySQL
  • 2. Service-side start-up
  • 3. Client Connection
  • 4. SQL Statement Classification
• First, the database operation
  • 1. Display Database
  • 2. Create a database
  • 3. Using the Database
  • 4. User Management
  • 5. Authorization Management
• Second, data table operation
  • 1. Create a table
  • 2. Delete a table
  • 3. Clear the Table
  • 4, modify the table
• Third, table content operation
  • 1, increase
  • 2. By deleting
  • 3, change
  • 4. Check
  • 5. In-depth search

0, prepare 1, install MySQL

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

2. Service-side start-up

mysql.server start

?

3. Client Connection

mysql -u username -p--退出QUIT 或者 Control+D

?
?

4. SQL Statement Classification

• The DDL (data definition Language) is used to create or delete objects such as databases that store data and tables in the database. The DDL contains several directives.

DDL (data definition language)
Create: Creating objects such as databases and tables
Drop: Delete objects such as databases and tables
Alter: Modify the structure of objects such as databases and tables

• DML (data manipulation Language, manipulation language) is used to query or alter records in a table. DML contains the following directives.

DML (Data Manipulation language)
SELECT: Querying data in a table
Insert: Inserting new data into a table
Update: Updating data in a table
Delete: Deleting data from a table

• The DCL (Data Control Language) is used to confirm or cancel changes to the data in the database. In addition to this, it is possible to set up an RDBMS user with permission to manipulate objects in the database (database tables, etc.). The DCL contains the following directives.

DCL (Data Control Language)
COMMIT: Confirm the changes made to the data in the database
ROLLBACK: Canceling changes to data in the database
Grant: Give user permission to operate
REVOKE: Canceling the user's operation rights

First, database operation 1, display the database

SHOW DATABASES;

The default database has the following:
MySQL-User rights-related data
Test-for user testing data
Information_schema-mysql itself schema-related data

2. Create a database

CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

It is generally utf8. Subsequent searches and various gadgets are available.

3. Using the Database

--使用数据库USE 数据库名称;--显示当前使用的数据库中所有表SHOW TABLES;  

4. User Management

--创建用户create user '用户名'@'IP地址' identified by '密码';--删除用户drop user '用户名'@'IP地址';--修改用户rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';--修改密码set password for '用户名'@'IP地址' = Password('新密码');

User rights-related data is stored in the user table in the MySQL database, but it is not recommended to manipulate it directly.

5. Authorization Management

-- 查看权限show grants for '用户'@'IP地址';-- 授权grant  权限 on 数据库.表 to   '用户'@'IP地址' ;-- 取消权限revoke 权限 on 数据库.表 from '用户'@'IP地址';

Frequently used permissions:
All privileges-all permissions except Grant
Select-Check Permissions only
Select,insert-Check and insert permissions

Use * to match database name and table name:
test.*-test Database All tables
*. *-All databases all tables

Use% to match IP address

--举个例子grant all privileges on *.*TO '用户名'@'%';

Second, data table Operation 1, create a table

create table 表名(    列名  类型  NULL，    列名  类型  NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8

Basic data types
MySQL data types are broadly categorized as: digital, character, date.

• INT: Specifies the data type (numeric) of the column that stores the integer, and cannot store decimals.
• Char type: The data type (character type) used to specify the column where the string is stored. You can specify the length (maximum length) of the string that the column can store in parentheses, like CHAR (200). The portion of the string that exceeds the maximum length cannot be entered into the column. When the string stored in the column does not reach the maximum length, a half-width space is used to complement it.
• VARCHAR Type: You can also specify the maximum length of a string (character type) by the number in parentheses. However, columns of this type hold strings in a variable-length string. Variable-length strings are not filled with half-width spaces, even if the number of characters does not reach the maximum length.
• Date type: The data type (date type) of the column used to specify the date (month and day) to store.

More data types

Default value
You can specify the default when you create a column.

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

Self-increment
If you set the self-increment column for a column, you do not have to set this column when inserting data, and the default will be self-increment (only one self-increment 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))

Attention:
1, for the self-increment column, must be an index (including the primary key).
2, for self-increment can set the step and start value
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 that does not allow null values, and 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)create table tb1(nid int not null,num int not null,primary key(nid,num))

FOREIGN key
A special index, only the 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 表名

3. Clear the Table

delete from 表名truncate table 表名  

4, modify the table

--添加列alter table 表名 add column 列名 类型--删除列alter table 表名 drop column 列名--修改列-- 类型alter table 表名 modify column 列名 类型; -- 列名，类型alter table 表名 change 原列名 新列名 类型;  --添加主键alter table 表名 add primary key(列名);--删除主键alter table 表名 drop primary key;alter table 表名  modify  列名 int, drop primary key;  --添加外键alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);--删除外键alter table 表名 drop foreign key 外键名称  --修改默认值ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;--删除默认值ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

Third, table content operation 1, increase

insert into 表 (列名,列名...) values (值,DEFAULT,值...)insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)insert into 表A (列名,列名...) select (列名,列名...) from 表B

2. By deleting

--保留数据表，删除全部行delete from 表delete from 表 where id＝1 and name＝'dyan';truncate 表

3, change

update 表 set name ＝ 'dyan' where id>1;

4. Check

5. In-depth search

1) Condition select * FROM table where ID > 1 and name! = ' Dyan ' and num = 12;     SELECT * FROM table where ID between 5 and 16;    SELECT * FROM table where ID in (11,22,33);    SELECT * FROM table where ID not in (11,22,33);    SELECT * FROM table where ID in (select Nid from table);        2) Aggregate count: Count the number of records in the table (number of rows) SUM: Calculates the total value of the data in a numeric column in a table avg: Calculates the average of the data in a numeric column in a table max: The maximum value of the data in any column in the table min: Find the minimum value of the data in any column in the table --the latter will get the number of data rows other than NULL SELECT COUNT (*), COUNT (< column name >) from table name; 3) wildcard select * FROM table where name like ' ale% '-ale begins with all (multiple strings) select * FROM table where name like ' Ale_ '-ale begins with all (a            characters) 4) limit select * from table limit 5;          -First 5 lines select * FROM table limit 4, 5;   -5 lines starting from line 4th select * FROM table limit 5 offset 4; -5 lines starting from 4th Line 5) group Select Num FROM table GROUP by NUM Select Num,nid from table group by Num,nid Select Num,nid from table whe  Re nid > 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 have max (ID) > 10 Special: Group by must be in where, before order by 6) No correspondence between the tables does not show select A.num, A.name, B.name     From A A, Where A.nid = B.nid No correspondence does not show select A.num, A.name, b.name from A inner join B on a.nid = B.nid A table All shows, if there is no correspondence in B, then the value is NULL select A.num, A.name, b.name from A left join B on a.nid = B.nid B All display, if no corresponding off in B                   System, the value is null select A.num, A.name, b.name from A right join B on a.nid = B.nid 7) sort select * FROM table ORDER by column -Rank from small to large according to "column", default ASC Ascending SELECT * FROM table ORDER BY column desc-rank from large to small from "column" SELECT * FROM Table order BY column 1 desc, column 2 ASC-According to "column 1" from large to small arrangement, if the same column 2 from small to large sort Select column name 1,count (*) from table Group By column name 1 order by COUNT (*)     8) combination, Automatic processing coincident select nickname from a union select name from B combination, do not process coincident select nickname from a     UNION ALL select name from B

MySQL (i)--basic syntax and common statements