SQL language and MySQL column type, sqlmysql Column

SQL language and MySQL column type, sqlmysql Column
Reprinted please indicate Source: http://blog.csdn.net/u012637501 (embedded _ small J of the sky)
I. SQL Introduction1. SQL languageStructured Query Language (Structured Query Language) is a comprehensive, universal, powerful, and easy-to-learn Language. It is a database application Language that uses relational models. The SQL language integrates data query, data manipulation, data definition, and data control functions, fully embodies the characteristics and advantages of Relational Data Language.2. SQL statement Classification(1) DDL (Data manipulation language) Statements: Data Definition language. These statements define Data objects such as Data segments, databases, tables, columns, and indexes, common statement keywords include create, drop, and alter. (2) DML (Data manipulation language) Statements: database operation statements used to add, delete, update, and query database records, and check database integrity. Common statement keywords include insert, delete, update, and select. (3) DCL (Date Control Language) Statement: Database Control statement. You can Control the direct permission and access level statements for different data segments. These statements define the database, table fields, user access permissions, and security level. Key statement keywords include grant and revike. Note: DDL is the abbreviation of the data definition language. In short, it is the language used to create, delete, and modify objects in the database. The biggest difference between DML and DML statements is that DML only operates on internal data, without modifying the table definition and structure, but not other objects. DDL statements are mostly used by database administrators (DBAs) and are rarely used by developers.3. Basic SQL statements(1) Basic Database Operations◆ Database connection (For MySQL) command: mysql-h [IP Address/host name]-u [user name]-p [Password] Description:-h host (Remote MySQL Server IP address) -u username-p Password◆ View database
Command: show databases;◆ Create a database
Command: create database [name of the new database];◆ Select database
Command: use [database name];◆ Delete a database
Command: drop database [delete database name];(2) Basic table operations◆ View tables in the current database
Command: show tables;◆ Create a table
Command: create table [created table name] ([column (field) Name 1] [column type] [constraints] [default value], [column (field) name 2] [column type] [constraints] [default value] ......) engine = storage engine charset = character set; Note: before creating a table, you must enter the database or specify the database in which the table is created.◆ View table Definitions
Command: desc [Table name]; or select * from [Table name];◆ View the table creation process
Command: show create table [table name] \ G;◆ Delete a table
Command: delete [Table name]; ◆ modify a table (field type, field name, add field, delete field, modify table name)
① Modify the field attribute in the table (the Field name cannot be modified) alter table [table name] modify [field name] [field type] [constraints] [fisrt | after column name]; ② modify the field name and attribute in the table alter table [table name] change [Source Field name] [modified field name] [field type] [constraints] [fisrt | after column name]; ③ add the table field alter table [table name] add [field name] [field type] [constraints] [first | after column name]; ④ Delete the table field alter table [table name] drop [field name]. Note: [first | after column name] is used to modify the sorting of fields, after, add the new field to a field. first indicates that the new field is placed in the first column of the table. ◆ Modify Table Name
Command: alter table [table name] rename to [new table name];
Ii. MySQL Introduction
1. MySQL IntroductionMySQL is a small relational database management system. It is a well-known and widely used open-source database software. MySQL has the following features: (1) suitable for small and medium-sized relational database systems; (2) supports multiple operating systems, such as Linux, Unix, and Windows. (3) use C and C ++ for high portability. (4) supports Python, Java, Perl, PHP, and other languages through APIS. A typical application environment is a LAMP Platform combined with Apache HTTP Server or an LNMP Platform combined with Nginx.
2. Default MySQL database(1) The information schema virtual database is used to save the statistical information of the existing databases and tables on the current database server. The data in the database does not occupy the physical disk space and is stored in the system memory. (2) The mysql authorization database saves the user's authorization information and occupies the physical disk space (3) performance schema saves the data server, and runtime parameters occupy the physical disk space (4) after a user in the test public Database connects to the database server, the database has full permissions to occupy physical disk space.
Iii. MySQL column type1. numeric type (integer column, floating point column)(1) integer columns: tinyint, smallint, mediumint, int, bigintA) Storage range and occupied space of integer columns (1 byte = 8 digits) B) Optional attribute of integer columns tinyint (M) unsigned zerofill M: width, an integer between 1 and 255, it indicates the number of characters used to display the column's median. (It makes sense when 0 is filled.) unsigned: unsigned type (non-negative) zerofill: 0 is filled (unsigned by default)(2) floating point and fixed pointA) floating point type: float (M, D) B) fixed point type: decimal (M, D) where M indicates precision (total number of digits, excluding points ); D indicates the scale (decimal scale), and decimal indicates higher precision.2. string typeWhen the character length is certain, the performance of char is better than that of varchar. When the length is not determined, the performance of char fields is slightly worse.(1) differences between char (M) and Varchar (M)★The char length is fixed and the number of characters that can be stored (M <= 255); the length of varchar (M) can be changed, the number of bytes that can be stored (M <= 65535 ). For example, if you store the string "abc", for char (20), it means that the characters you store will occupy 20 bytes (including 17 null characters), and the same varchar (20) it takes up to 3 bytes of length, and 20 is the maximum value. When the character you store is less than 20 characters, the storage is based on the actual length. Char is of a fixed length, so it is much faster than varchar! However, it is a little troublesome for the program to process it. We need to use functions such as trim to remove spaces on both sides!★The efficiency of char is slightly higher than that of varchar. varchar saves space than char, that is, it must sacrifice a certain amount of space for efficiency.★Char (M) occupies M characters, but not M characters. Fill in spaces on the right side to retrieve spaces on the right side. varchar (M) has 1-2 bytes to mark the actual length.(2) TimestampIn our development, the time representation accurate to seconds is not datetime, but int to represent the timestamp. It is convenient to calculate and format the timestamp into different display styles.
3. period time type
Note: The Special NULL type ◇ NULL is neither false nor true, but an "NULL" operator. ◇ when the operator encounters NULL, NULL ◇ null can only be used for NULL, is not null ◇ NULL affects the query speed. Generally, NULL is not allowed.