SQL Statement Learning (i)----general syntax, DDL,DML Base statement

1.1 SQL Overview 1.1.1 SQL statement Introduction

Structured Query Language (structured query Language) is referred to as SQL, which is the specification that the relational database management system should follow. Different database Manufacturers support SQL statements, but they all have unique content.

　　

1.1.2 SQL Statement Classification

　　

• SQL classification:
  • Data definition language: For short, DDL (data definition Language), used to define database objects: Databases, tables, columns, and so on. Keywords: create,alter,drop, etc.
  • Data manipulation language: for short, DML (data Manipulation Language), which is used to update the records of tables in the database. Keywords: insert,delete,update, etc.
  • Data Control Language: Language, a DCL, used to define access and security levels for a database, and to create user
  • Data Query Language: Short DQL (Language), which is used to query the records of tables in the database. Keywords: select,from,where, etc.

　　

1.1.3 SQL General Syntax

　　

• SQL statements can be written one or more lines, ending with a semicolon
• Use spaces and indents to enhance the readability of statements
• SQL statements for MySQL database are case-insensitive, and keywords are recommended to use uppercase
  • For example, SELECT * from user.
• You can also use/**/to complete annotations
• The types of data that we often use in MySQL are as follows

　　

The detailed data types are as follows (not recommended for detailed reading!) )

Classification	Type name	Description
Integer type	TinyInt	A very small integer.
	smallint	A small integer
	Mediumint	A medium-sized integer
	Int (integer)	An integer of normal size
Decimal type	Float	Single-precision floating-point number
	Double	Double-precision floating-point number
	Decimal (M,D)	Compress strict fixed-point numbers
Date type	Year	YYYY 1901~2155
	Time	hh:mm:ss-838:59:59~838:59:59
	Date	YYYY-MM-DD 1000-01-01~9999-12-3
	Datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
	Timestamp	YYYY-MM-DD HH:MM:SS 1970~01~01 00:00:01 utc~2038-01-19 03:14:07UTC
Text, binary type	CHAR (M)	M is an integer between 0~255
	VARCHAR (M)	M is an integer between 0~65535
	Tinyblob	Allowable length 0~255 bytes
	Blob	Allowable length 0~65535 bytes
	Mediumblob	Allowable length 0~167772150 bytes
	Longblob	Allowable length 0~4294967295 bytes
	Tinytext	Allowable length 0~255 bytes
	TEXT	Allowable length 0~65535 bytes
	Mediumtext	Allowable length 0~167772150 bytes
	Longtext	Allowable length 0~4294967295 bytes
	VARBINARY (M)	A variable-length byte string that allows a length of 0~m bytes
	BINARY (M)	A fixed-length byte string that allows a length of 0~m bytes

1.2 DDL Database operations: database1.2.1 Creating a Database

Format:

* Create database name;

* CREATE database name character set character set;

For example:

The encoding of the data in the #创建数据库 database takes the default encoding specified when the database is installed UTF8

　　

CREATE DATABASE webdb_1;

#创建数据库 and specify the encoding of the data in the database

CREATE DATABASE CHARACTER SET UTF8;

　　

　　

1.2.2 Viewing a database

To view all databases in the database MySQL server:

　　

show databases;

　　

To view information about the definition of a database:

Create  database name;

For example:

Create database webdb_1;

　　

1.2.3 Deleting a database

Drop  database name;

For example:

Drop database webdb_2;

　　

1.2.4 Using the database

• To view the database being used:

Select Database ();

　　

• Additional Database Operations Commands

To switch databases:

 use database name;

For example:

 use webdb_1;

　　

1.3 DDL Table Operations: table1.3.1 Creating tables

 create  table   [  constraint   "  [  constraint  ]   varchar   (n) string  int   shaping  double   floating point date time  timestamp   timestamp constraint: (see Chapter Fourth)  primary  key
        primary key, the data in the field that is modified by the primary key, cannot be duplicated, cannot be null. 

For example:

# # #创建分类表 CREATE TABLE category (  INTprimarykey, #分类ID  VARCHAR(100  ) #分类名称);

　　　　

1.3.2 View Table

• To view all tables in the database:

Format: show tables;

• To view the table structure:

Format:desc  table name; For example:desc category;

1.3.3 Deleting a table

• Format: drop table name;

Example:droptable category;

1.3.4 Modify Table Structure format:

• ALTER TABLE name add column name type (length) [constraint];

Effect: Modifies the table to add columns.

For example:

#1varchar()ALTERTABLEADD 'desc VARCHAR (a);

• ALTER TABLE name modify column name type (length) constraint;

Effect: Modifies the type length and constraints of a table modified column.

For example:

#2, modified for the Description field of the classification table, type varchar(notnullALTERtable category MODIFY 'descVARCHAR(notNULL;

• ALTER TABLE name change old column name new column name type (length) constraint;

Effect: Modifies the table to modify the column name.

For example:

#3varchar(in)ALTERTABLE category change 'desc  VARCHAR(+);

• ALTER TABLE name drop column name;

Effect: Modifies a table to delete a column.

For example:

#4, delete the category table in Description this row of ALTERtableDROP description;

• Rename table name to the new name;

Role: Modify table name

For example:

#5TABLE to Category2;

• ALTER TABLE name character set character set (understanding);

Role: Modifying the character set of a table

For example:

#6, modified to GBKALTERtableCHARACTERSET for the coding table of category Gbk

1.4 DML Data Manipulation language 1.4.1 Insert Table record: Insert

• Grammar:

-- insert some fields into the table Insert  into Values   (value 1, value 2, value 3 ...); -- insert all fields into a table in the order in which they were created Insert  into Values (value 1, value 2, value 3 ...);

• Attention:
  • Values must correspond to fields, same number, same type
  • The data size of the value must be within the length range of the field
  • In addition to numeric types, the values of the other field types must be enclosed in quotation marks. (suggested single quotes)
  • If you want to insert a null value, you can not write a segment, or insert NULL.
• For example:

INSERT  intoCategory (Cid,cname)VALUES('c001','Electrical Appliances');INSERT  intoCategory (Cid,cname)VALUES('c002','Apparel');INSERT  intoCategory (Cid,cname)VALUES('c003','Cosmetics');INSERT  intoCategory (Cid,cname)VALUES('c004','Books');INSERT  intoCategory (CID)VALUES('c005');INSERT  intoCategory (CNAME,CID)VALUES('Consumables','c006');

1.4.2 Update table records: Update

The data that is used to modify the specified condition to modify the specified column of the record that satisfies the condition to the specified value

• Grammar:

-- update the specified fields for all records Update set field name = value, field name = value,...; -- update a specified field that matches a condition record Update set field name = value, field name =where condition;

• Attention:
  • The type of the column name is the same as the modified value.
  • The modification is worth the time not exceeding the maximum length.
  • In addition to numeric types, values for other field types must be enclosed in quotation marks

1.4.3 Deleting records: Delete

• Grammar:

Delete  from [where Condition ] ; or truncate  table name;

• Interview questions:

Delete all records in the table using the delete from table name; Or use the TRUNCATE table name;

Delete method: Delete One piece deletes, does not empty the Auto_increment record number.

Truncate directly delete the table, re-build the table, Auto_increment will be set to zero, new start.

1.5 DOS operation data garbled solution

We will error when we operate Chinese on DOS command line

Insert  into Values (' c010 ', ' Chinese ');     1366 ' \xb7\xfe\xd7\xb0 '  for column ' CNAME ' 1

Error reason: Because MySQL's client setting encoding is UTF8, and the system's CMD window encoding is GBK

1) Check the encoding of the MySQL internal settings

 like ' character% '; View all MySQL codes

　　

2) need to modify the encoding of the client, connection, results (GBK encoding)

Solution 1: Enter the command in the CMD Command window, the current window is valid for the temporary scheme.

  set names GBK;

Solution 2: Modify the My.ini file in the installation directory and restart the service all the places are in effect.

SQL Statement Learning (i)----general syntax, DDL,DML Base statement