MySQL Database Basics (iii)--sql language

MySQL Database Basics (iii)--sql Language I. Introduction to SQL 1. Introduction to SQL language

SQL is a Structured Query language (structured Query Language), which is the standard computer language for accessing and working with databases.
The functions of the SQL language are as follows:
A, SQL query for database execution
B. SQL can retrieve data from the database
C. SQL can insert new records in the database
D, SQL can update data in the database
E, SQL can delete records from the database
F, SQL to create a new database
G, SQL to create a new table in the database
H, SQL to create stored procedures in the database
I, SQL can create a view in the database
J, SQL can set permissions for tables, stored procedures, and views
SQL is an ANSI standard computer language used to access and manipulate database systems. SQL statements are used to retrieve and update data in the database. SQL can work with database programs, such as Ms Access, DB2, Informix, MS SQL Server, Oracle, MySQL, Sybase, and other database systems.
Each database has its own version of the SQL language, but in order to be compatible with the ANSI standard, SQL must work together in a similar way to support some key keywords (such as SELECT, UPDATE, DELETE, INSERT, where, and so on).
In addition to the SQL standard, most SQL database programs have their own private extensions.

2. SQL Language classification

SQL language is divided into data definition language, Data Control language, data manipulation language, data query language, respectively, to implement database data operation.

Ii. SQL Language Foundation 1, Data Definition language (DDL)

Ddl:data Definition Language
Used to define and manage data objects, including databases, data tables, functions, views, indexes, triggers, and so on. For example: CREATE, DROP, Alter, and so on.
CREATE TABLE Student
(
Sid INT,
sname varchar (20)
);
ALTER TABLE student add age int default 20;
drop table student;
drop database student;

2. Data Control Language (DCL)

Dcl:data Control Language
The language used to manage the database, which includes authorized user access, deny user access, revoke grant permissions. For example: GRANT, DENY, REVOKE, COMMIT, rollback, and so on.
Create user
Create user ' Wang ' @ ' localhost ' identified by ' a1! ';
Permission settings
Grant SELECT on Db.student to ' Wang ' @ ' localhost ';
Revoke permissions
Revoke select on db.student from ' Wang ' @ ' localhost ';

3. Data manipulation Language (DML)

Dml:data Manipulation Language
Used to manipulate the data contained in database objects, add, delete, change. For example: INSERT, DELETE, UPDATE statement.

4. Data Query Language (DQL)

Dql:data Query Language
It is used to query the data contained in the database objects, can make a single table query, connection query, nested query, and collection query and other different complexity of database query, and return the data to the client display. For example: SELECT statement.

Three, constants and variables 1, constants

A, character constant
String constants use single or double quotation marks, and numeric constants are not quoted.
If the string constant requires a newline, a single quote, a double quote% \
You need to add the escape character in front \
\ nthe line break
\ ' A single quotation mark
\ "A double quote
\ one \ If no escape character is considered \ is an escape character
\% A% if there is no escape character, consider this to be a wildcard
_ A wildcard if no transfer character is considered

Select ' Hanli\ ' gang001 '
Select ' han\nligang001 '
Select ' han\nligang001 '
Select "Han\" ligang001 "

Select "Han Ligang 001"
B, Numeric constants
Numeric constants do not have to add quotes,
Select 100+100+200
C, Boolean constants
Boolean constant evaluates to True and False
Use 1 and 0 in SQL to represent
Select True,false

In an expression
Select 100>200
Select 100<200

2. Variables

User-defined variables use @ To start, assigning values to variables using set.
Set @name = ' Monkey King ';
Select @name;
SELECT * from student;
INSERT into student values (6, ' Monkey King ', 20);
INSERT into student values (8, @name, 20);
Set @sid =9, @nid =10
INSERT into student values (@sid, @name, 20);
Select @[email protected];
Set @[email protected]+1;
Select @sid;
Set @sname3 = (select sname from student where sid=9);
Select @sname3;

3. System Variables

System variables are divided into global system variables and session system variables.
Global System variables: for all default settings
Session System variables: for the current user, user login MySQL uses global system variables, if the variable value is changed in the session, the changed value is used, but only for the current user.
Show variables display session system variables
Show global variables display of Globals system variables
Show session variables display the sessions system variables
Show global variables like ' sql_select_limit '; Use wildcard characters to display matching variable settings
Show session variables like ' sql_select_limit '; system variables use @@ 标识
SELECT @ @global. Sql_select_limit view a global system variable setting
SELECT @ @session. Sql_select_limit view a session system variable setting
SET @ @session. sql_select_limit=2 Setting Session System variables
Global system variables need to be modified in the/ETC/MY.CNF configuration file.

Four, operator good expression 1, arithmetic operators

Arithmetic operators include: plus (+), minus (-), multiply (), divide (/), modulus (%).
Select 4+3,5-3,58,20/4;
Select 17%5;
Select sid,sname,age+2 from S;

2. Comparison operators

Comparison operators include equals (=), greater than (>), less than (<), greater than or equal (>=), less than or equal (<=), not equal to (! = or <>)
Select 10=10,10<10,10<>10;
Select binary ' a ' = ' a ', ' a ' < ' B '; two-level comparison of characters
Select from s where sid<=2;
Select from S where Sname= ' Monkey King ';

3. Logical operators

Logical operators include with (and or &&), or (or or | | | ), non (not OR!).
Examples of application of logical operators
Select from s where sname like ' Yang% ' and age>40;
Select from S where sname like ' sun% ' && age>30;
Select from s where sname like ' sun% ' or sid<2;
Select from S where sname like ' Sun% ' | | sid<2;
Select from s where not sname like ' sun% ';
Select from S where! (Sname like ' sun% ');

4, Operator precedence

Arithmetic operator precedence: first multiplication, plus minus, brackets first
Logical operator Precedence: first with operation, then or operation, parenthesis precedence
Sname like ' Korean ' and age>40 or sid<3;
Sname like ' Korean ' and (age>40 or sid<3);

5. Expression

An expression is a combination of constants, variables, operators, functions, and so on.
1+2;
' A ' < ' a ';
SELECT * from S where age+4>40;

Five, the system built-in function 1, mathematical function

ABS (x)
Returns the absolute value of X
SELECT * FROM student where ABS (AGE-45) <=2
Find users who are not older than two years of age 45

Ceil (x), CEILING (x)
Returns the smallest integer greater than or equal to X (rounding up)
Floor (x)
Returns the largest integer less than or equal to X (rounding down)
Select Ceil (5.4), floor (5.6);
Select Sid,sname,floor (AGE/10) *10 from S;

RAND ()
Returns the random number of 0~1
Select Floor (RAND ()1000);
Generate a random integer of 0-1000
Select , RAND () random from S order by random;
Randomly sorting query results using random functions
RAND (x)
Returns the random number of 0~1, the same as the random number returned by the seed x value.

Sign (x)
Returns the x symbol, x is negative, 0, positive numbers are returned-1, 0, 1, respectively.
PI ()
return pi
TRUNCATE (x, y)
Returns the value of x reserved to the Y-bit after the decimal point
Select sign ( -4), sign (434);
Select PI ();
Select truncate (4.5454,2);
Select Truncate (AVG (age), 2) from S;

ROUND (x)
Returns the nearest integer from X (rounded)
ROUND (x, y)
The value of the Y-bit after the decimal point is preserved, but is rounded when truncated
POW (x, y), POWER (x, y)
Returns the Y-order of X
SQRT (x)
Returns the square root of X
EXP (x)
Returns the X-square of the natural constant e.

2. String functions

Char_length (s)
Returns the number of characters in the string s
LENGTH (s)
Returns the size of the space occupied by the string s.
SELECT * FROM student where length (sname)/char_length (sname)!=3
Find students with English characters in their names

CONCAT (S1,s2,.....)
Combine strings s1,s2 and more into a single string
Concat_ws (X,s1,s2,....)
With Coucat (S1,s2,.....), but the x is added between each string
Select Concat (' Han Li just ', ' River Beijing Normal University ', ' Network engineer ')
Select CONCAT (' Learning number ', sid, ' name ', sname, ' age ', ages) from S
Select Concat_ws (', ', ' study number ', Sid, ' name ', sname, ' age ', ages) from S

INSERT (S1,X,LEN,S2)
Use string s2 to replace S1 's x position starting with Len's string
Select Insert (' Hanligang ', 4,2, ' zhi ')
Select Insert (sname,2,0, ') from S

UPPER (s), UCASE (s)
Turns all characters in the string s to uppercase
LOWER (s), LCASE (s)
Turns all characters in the string s to lowercase letters
Select Upper (' Hanligang ')
Select lower (' Hanligang ')
Select Upper (sname) from S

Left (S,n)
Returns the first n characters of a string s
Right (S,n)
Returns the second n characters of the string s
Select Left (' Monkey King ', 1)
Select Right (' Monkey King ', 2)
Lpad (S1,LEN,S2)
String S2 to fill the beginning of the S1, so that the string length reaches Len
Rpad (S1,LEN,S2)
String S2 to fill the end of the S1 so that the string length reaches Len
Select Lpad (' 1 ', 4, ' 0 ')
Select Lpad (sid,4,0) from S
Select Rpad (' HAN ', ten, ' B ')

LTRIM (s)
Remove the space at the beginning of the string s
RTRIM (s)
Remove the space at the end of the string s
TRIM (s)
Remove the space at the beginning and end of the string s
TRIM (S1 from S)
Removes the string at the beginning and end of the string s S1
Usage examples:
Select Char_length (LTRIM (' Han '))
Select RTRIM (' Han ')
Select Char_length (RTRIM (' Han '))
Select Char_length (TRIM (' Han '))
Insert into S values (10, ' Zhang Zhimin ', 32)
SELECT * FROM S
Update s set Sname=ltrim (sname)
Select TRIM (' H ' from ' hanliganghhhh ')

REPEAT (S,n)
Repeats the string s n times
SPACE (N)
Returns n Spaces
REPLACE (S,S1,S2)
Use string s2 instead of string s in string s1
Usage examples:
Select repeat (' Han ', 4)
Select Sid,concat (Left (sname,1), SPACE (SID), right (sname,2)) from S
Select replace (' Han Li Gang ', ' ', ' _ ')
Select replace (' Han Li Gang ', ', ')

STRCMP (S1,S2)
Returns a value less than 0 if the S1 is less than the S2,STRCMP function. If S1 is greater than S2, the function returns a value greater than 0. If two strings are equal, the function returns zero.
SUBSTRING (S,n,len)
Gets the string from the nth position in the string s, beginning with Len,
POSITION (S1 in)
gets the starting position of the S1 from string s
INSTR (S,S1)
Gets the starting position of the S1 from the string s
Application instance:
Select strcmp (' Han ', ' Wang ')
Select strcmp (' hlg ', ' Han ')
Select strcmp (' Han ', ' an ' )
Select SUBSTRING (' [email protected] ', 11,7)
Select Sid,substring (sname,2,1) from S
Select SUBSTRING ( ' [email protected] ', POSITION (' @ ' in ' [email protected] ') +1,11)
Select Right (' [email protected] ' , Char_length (' [email protected] ')-position (' @ ' in ' [email protected] '))

ALTER TABLE s add email CHAR (40)
SELECT * FROM S
Update s set email= ' [email protected] ' where sid=1
Update s set email= ' [email protected] ' where sid=2
Update s set email= ' [email protected] ' where sid=3; Gets the string after the @ in the email field, first intercepts the length of the mailbox field, and at the location of the @ character of the mailbox, calculates how many characters are behind the mailbox @
SELECT Right (Email,char_length (e-mail)-position (' @ ' in e-mail)) from S
SELECT Right (email,char_length (email)-instr (e-mail, ' @ ')) from S

3. Date and Time functions

Linux command line view:
Clock
Show Hardware Time
Date
Show System time
Date-s 11/03/15
Change the system date
Date-s 9:21:4
Change the system time
Hwclock--SYSTOHC
Overwrite hardware time with system time
Hwclock--hctosys
Overwrite system time with hardware time
MySQL command line view:
Curdate ()
Current_date ()
Get System Current Date
Curtime ()
Current_time ()
Get System Current Time
Current_timestamp ()
LocalTime ()
Now ()
Gets the current date and time of the system

Application Examples:
Adds a column to table s, the data type timestamp The default value is the current time.
ALTER TABLE s add stime TIMESTAMP default now ();
SELECT * FROM S
Insert into S values (11, ' Zhang Dong ', Sid,sname,age,email, ' [email protected] ')
Change the stime column of the previous user to the current time
Update s set Stime=now () where sid<11
Returns the month value in Date D, the range is 1~12
MONTH (d)
Returns the name of the month in Date D, such as January
MONTHNAME (d)
Return Date D is the day of the week, such as Monday
Dayname (d)
Return Date d is day of the week, 1 means Sunday, 2 means Week 1
DAYOFWEEK (d)
Return Date d is day of the week, 0 means Monday, 1 means Week 2
WEEKDAY (d)
Calculated Date d is the week ordinal of this year, the range is 0-53
WEEK (d)

Calculated Date d is the week ordinal of this year, the range is 1-53
WeekOfYear (d)
Calculated Date D is the day ordinal of this year
DayOfYear (d)
Calculated Date D is the day of the month
DayOfMonth (d)
Returns the year value in Date D
Year (d)
Return Date D is the quarter, range 1-4
QUARTER (d)
Returns the hour value in the time t
HOUR (t)
Returns the minute value in the time t
MINUTE (t)
Returns the seconds value in the time t
SECOND (t)

Application Examples:
Select Concat (now ()), ' Years ', Month (now ()), ' months ', Day (now ()), ' days ')
Select CONCAT (Year (stime), ' Years ', month (stime), ' Month ', Day (stime), ' Date ') from S

Calculate the number of days between dates D1 through D2
DATEDIFF (D1,D2)

Calculate start Date d plus n Day's date
Adddate (D,n)

Calculate the start Date D plus the date after a time period
Adddate (d, INTERVAL expr type)

Calculates the start time t plus the time of n seconds
Addtime (T,n)

Application Examples:
Select DateDiff (now (), ' 2015-2-3 ')
Select Adddate (now (),-1050)
Select Adddate (now (), 1050)

Select Adddate (now (), INTERVAL 1000000 SECOND)
Select Adddate (now (), INTERVAL 1000000 Hour)
Select Adddate (now (), INTERVAL-10000 Day)
Select Adddate (now (), INTERVAL ' 3 ' Day_hour)
Select Adddate (now (), INTERVAL 3)

4. System Information function

System information functions are used to query the MySQL database for system information. For example, querying the version of the database, querying the data for the current user, etc.
VERSION ()
Returns the version number of the database
CONNECTION_ID ()
Returns the number of connections to the server, that is, the number of connections to the MySQL service so far
DATABASE (), SCHEMA ()
Returns the current database name
USER ()
Returns the name of the current user
CHARSET (str)
Returns the character set of the string str
COLLATION (str)
Returns the character arrangement of the string str
LAST_INSERT_ID ()
Returns the last generated Auto_increment value

5. Encryption and decryption function

Cryptographic functions are functions that are used to encrypt data in MySQL.
PASSWORD (str)
Encrypt the string str
MD5 (str)
MD5 Encryption of String str
ENCODE (STR,PSWD_STR)
Use string Pswd_str to encrypt the string str, the result of which is a binary number, which must be saved by using the Blob type
DECODE (CRYPT_STR,PSWD_STR)
decryption function, using string pswd_str to decrypt the CRYPT_STR

Application Examples:
CREATE TABLE WebUser
(
Logon varchar (20),
PW VARCHAR (100)
);
INSERT into webuser values (' Hanligang ', MD5 (' abc '));
INSERT into webuser values (' ZHANGQL ', MD5 (' 123 '));
Select from webuser where logon= ' Hanligang ' and PW=MD5 (' abc ');
ALTER TABLE webuser MODIFY COLUMN pw BLOB (1);
INSERT into webuser values (' zhangjing ', encode (' ABCD ', ' 91xueit '));
Select Logon,decode (PW, ' 91xueit ') from WebUser;
Select from WebUser where logon= ' zhangjing ' and decode (PW, ' 91xueit ') = ' ABCD ';

6. Aggregation function

Count ()
Count the number of records that meet the criteria
Select COUNT (*) from S
How many records are in the statistics table
Select COUNT (email) from S
Select COUNT (all emails) from S
How many records in the statistics list have a value, including duplicate values
Select COUNT (Distinct email) from S
How many records in an email column have a value in the statistics table to eliminate duplicate values

SUM () Statistics total
Select SUM (age) from S where e-mail is not NULL

AVG () averaging
Select AVG (age) from S where e-mail is not NULL
Max () to find the maximum value
Min () to find the minimum value
Select AVG (age), MAX (age), MIN from S

Group_concat () displays a row of records that meet the criteria, separated by commas
Select Group_concat (sname) from S where sid<5

7. Conditional Judgment function

if (value,t,f) if value is True T if False returns the value F
Select Sname,if (age>40, ' middle age ', ' youth ') as age from S

Ifnull (value1,value2) If value1 is not empty, return value1, if value1 is empty, return value2
Select Sname,ifnull (Email, ' no mailbox ') as mailbox from S

Case if value1 then result1 else default end
Returns RESULT1 if the value1 value is true, otherwise returns the default value
Select Sname,case when age>40 then ' middle ' else ' youth ' end from S

Case expr when value1 and Result1 ' when value2 then ' result2 ' when value3 then ' result3 ' Else ' result4 ' end
Returns a different value based on the value of the expr expression
Select Sname, Case floor (AGE/10) When 2 Then ' prime ' when 3 then ' youth ' else ' middle aged ' end from S

VI. MySQL Support data types

1. Numeric type
MySQL's numeric data types are divided into integers and floating-point numbers only.

The 5 main integers supported by MySQL are Tinyint,smallint,mediumint,int and BIGINT.
MySQL extends the SQL standard in the form of an optional display width indicator, so that when retrieving a value from the database, it can be extended to a specified length. For example, specifying that a field is of type INT (6) guarantees that values with fewer than 6 contained numbers can be automatically populated with spaces when they are retrieved from a database. It is important to note that using a width indicator does not affect the size of the field and the range of values that it can store.
If a field is stored with a number that is outside the permitted range, MySQL is truncated and then stored based on the closest one to the allowable range. MySQL will automatically be modified to 0 before the non-specified value is inserted into the table.
The UNSIGNED modifier specifies that the field only holds positive values. Because you do not need to save the positive and negative symbols of the numbers, you can save a "bit" space at the time of storage. This increases the range of values that the field can store.
The Zerofill modifier specifies a value of 0 that can be used to really complement the output, which prevents the MySQL database from storing negative values.
Application Examples:
CREATE TABLE tint
(
Tid tinyint,
Tid2 tinyint unsigned,
TID3 Int (5)
)
ALTER TABLE tint MODIFY column TID3 int (5) Zerofill
INSERT into tint values (21,23,4)
INSERT into tint values (11,21,233322)
If the stored data overflow displays a warning
INSERT into tint values (221,-23,4)
Show warnings
View warning messages for the current operation
FLOAT, double, and decimal types
The three floating-point types supported by MySQL are the float, double, and decimal types. Float numeric types are used to represent single-precision floating-point numbers, whereas double numeric types are used to represent double-precision floating-point values.
An additional parameter is a display width indicator and a decimal point indicator. For example, the statement float (7,3) specifies that the value displayed will not exceed 7 digits, followed by a 3-digit number after the decimal point.
For the number of digits after the decimal point beyond the allowable range, MYSQ automatically rounds it to the value closest to it, and then inserts it.
The decimal data type is used in calculations that require very high precision, allowing the precision and counting methods of a specified numeric value as the selection parameter. The precision here refers to the total number of valid digits saved for this value, while the Count method represents the number of digits after the decimal point. For example, the statement decimal (7,3) specifies that the stored value will not exceed 7 digits and no more than 3 digits after the decimal point.
Ignoring the precision and count method modifiers of the decimal data type will cause the MySQL database to set the precision of all fields identified as this data type to 10, and the calculation method set to 0.
The unsigned and Zerofill modifiers can also be used by the float, double, and decimal data types.

2. String type

MySQL provides 8 basic string types that can store ranges from simple one character to huge chunks of text or binary string data.

The char type is used for fixed-length strings and must be defined within parentheses with a size modifier. The size modifier ranges from 0-255. A value larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space.
The char type can use the binary modifier. When used for comparison operations, the binary modifier causes char to participate in the operation in binary mode, rather than in a traditional case-sensitive manner.
A variant of the char type is a varchar type, a variable-length string type, and must also have an indicator with a range of 0-255. The difference between char and Varchgar is the way the MySQL database handles the range indicator: Char treats the range size as a value, and uses a space to complement it in the case of insufficient length Instead, the varchar type treats the range indicator as the maximum value and stores the value using only the length that the string actually needs to be stored (an extra byte is added to store the length of the string itself). A varchar type shorter than the indicator length is not padded with spaces, but the value longer than the indicator will still be truncated.
Because varchar types can dynamically change the length of stored values based on the actual content, the use of varchar type can greatly save disk space and improve storage efficiency when it is not possible to determine how many characters a field requires.
The varchar type is exactly the same as the char type when using the binary modifier.
For cases where the field length requires more than 255, MySQL provides text and blob two types. Depending on the size of the stored data, there are different subtypes. Large data is used to store binary data types such as text blocks or images, sound files, and so on.
The text and BLOB types differ in classification and comparison. Blob types are case-sensitive, while text is not case-sensitive. The size modifier is not used for various blobs and text subtypes. A value that is larger than the maximum range supported by the specified type is automatically truncated.

3. Date and Time type

MySQL has 5 different data types to choose from when working with date and time type values. Divided into simple date, time type, mixed date, time type. Depending on the accuracy required, subtypes can be used in each category, and MySQL has built-in functionality to turn a variety of input formats into a standard format.

MySQL stores simple date values with the date and year types, and time values are stored using the type of times. A date, time type can be described as a string or an integer sequence without delimiters. If the description is a string, the value of the date type should be separated by a hyphen as a delimiter, while the value of the time type is separated by a colon as a delimiter. A time type value without a colon delimiter will be understood by MySQL as a duration, not a timestamp.
MySQL also maximizes the interpreter of the two numbers in the year of the date, or the two numbers entered in the SQL statement for the years type. Because values of all year types must be stored with 4 digits. MySQL attempted to convert a 2-digit year to a value of 4 digits. Converts values within the range of 00-69 to 2000-2069. Converts the value within the 70-99 range to 1970-1979. If the MySQL auto-converted value does not meet your needs, enter a 4-digit year.
In addition to date and time data types, MySQL also supports datetime and timestamp mixed types, which can store dates and times as a single value. Typically used to automatically store timestamps that contain the current date and time, and can play a good role in applications that require a large number of database transactions and audit trails that require a debug and review purpose.
If a field of type timestamp is not explicitly assigned, or is assigned a null value. MySQL will automatically populate it with the current date and time of the system.
CREATE TABLE DD
(
D DATE,
T time,
Y year,
DT DATETIME,
TS Timestamp
)
INSERT into DD values (' 2015-11-05 ', ' 15:48:34 ', ' 2015 ',
' 2015-11-05 15:48:34 ', ' 2015-11-05 15:48:34 ')
Not satisfied with the time format, after inserting becomes
INSERT into DD values (' 201511-05 ', ' 15:78:34 ', ' 15 ',
' 2015-11-05 154834 ', ' 2015-11-05 15:48:34 ')

4. Composite type

MySQL supports two composite data types, enum and set, that are part of the extended SQL specification. An enum type allows only one value to be obtained from a collection, whereas a set type allows any number of values to be taken from a collection.
Enum type
The enum type allows only one value to be obtained in the collection. The Enum type field can take a value from the collection or use a null value, and if entering a value that is not in the collection will cause MySQL to insert an empty string. In addition, if the casing of the inserted value does not match the case of the value in the collection, MySQL automatically converts the casing of the inserted value to a value that is consistent with the case in the collection.
The enum type can be stored as a number inside the system, and it is indexed with numbers starting from 1. An enum type can contain up to 65,536 elements, one of which is retained by MySQL to store the error message, and the error value is represented by index 0 or an empty string.
MySQL considers that the value that appears in the Enum type collection is a valid input, except that any other input will fail. It is easy to find the location of the error record by searching for a row that contains an empty string or a corresponding numeric index of 0.
Set type
A set type can take any number of values from a predefined set, and any attempt to insert a non-predefined value in a set Type field causes MySQL to insert an empty string. If you insert a record that has a valid element and an illegal element, MySQL retains the legitimate element, removing the illegal element.
A set type can contain up to 64 elements. The value in the set element is stored as a separate "bit" sequence, and it is not possible to include two identical elements in a set type.
Finding illegal records from the set Type field simply looks for rows that contain an empty string or binary value of 0.
CREATE TABLE DD
(
Sname Char (10),
Sex enum (' Male ', ' female '),
Hobby set (' Swim ', ' travel ', ' play ')
);
INSERT into DD values (' Han Li ', ' male ', ' swim, play ');
INSERT into DD values (' Zhangjing ', ' hard ', ' watch movies, play ');
Insert a record with error, the sex column is not inserted successfully, the Hobby column can only insert values listed in Set

Vii. data type Conversion functions

Type conversion functions are mainly cast (xxx as type), convert (XXX, type). Types that can be converted are limited, including binary, character, date, time, datetime, floating-point, Integer, unsigned integer.
Select CAST (' 00033ad ' as signed)
Select CONVERT (44.334,signed)
Select Concat (CONVERT (44.334,char (10)), ' 3000 ')
Type Automatic derivation conversions
Select ' 0033aa ' +23
Select CONCAT (123, ' abc ', 123)

MySQL Database Basics (iii)--sql language