-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathmysql-basic-2.html
More file actions
46 lines (46 loc) · 18.5 KB
/
mysql-basic-2.html
File metadata and controls
46 lines (46 loc) · 18.5 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<!doctype html><html lang=en><head><meta charset=utf-8><meta http-equiv=X-UA-Compatible content="IE=edge,chrome=1"><title>Mysql Basic 2 - Frank Wang's Coding World</title><meta name=renderer content="webkit"><meta name=viewport content="width=device-width,initial-scale=1,maximum-scale=1"><meta http-equiv=Cache-Control content="no-transform"><meta http-equiv=Cache-Control content="no-siteapp"><meta name=theme-color content="#f8f5ec"><meta name=msapplication-navbutton-color content="#f8f5ec"><meta name=apple-mobile-web-app-capable content="yes"><meta name=apple-mobile-web-app-status-bar-style content="#f8f5ec"><meta name=author content><meta name=description content="The basic data types of MySQL database include numeric types, character types and time and date types. Basic operations on MySQL database tables. Basic operations of MySQL database records"><meta name=keywords content="Frank Wang,Front End Developer,JavaScript"><meta name=generator content="Hugo 0.157.0 with theme even"><link rel=canonical href=http://frankwang0909.github.io/mysql-basic-2.html><link rel=apple-touch-icon sizes=180x180 href=/apple-touch-icon.png><link rel=icon type=image/png sizes=32x32 href=/favicon-32x32.png><link rel=icon type=image/png sizes=16x16 href=/favicon-16x16.png><link rel=manifest href=/manifest.json><link rel=mask-icon href=/safari-pinned-tab.svg color=#5bbad5><link href=/sass/main.min.af7fd1da18d66c2b017df5b4cae508ef44cfcac3fb4c7c7a327fe4f4f9e28b08.css rel=stylesheet><link href=/lib/fancybox/jquery.fancybox-3.1.20.min.css rel=stylesheet><meta property="og:url" content="http://frankwang0909.github.io/mysql-basic-2.html"><meta property="og:site_name" content="Frank Wang's Coding World"><meta property="og:title" content="Mysql Basic 2"><meta property="og:description" content="The basic data types of MySQL database include numeric types, character types and time and date types. Basic operations on MySQL database tables. Basic operations of MySQL database records"><meta property="og:locale" content="en_US"><meta property="og:type" content="article"><meta property="article:section" content="post"><meta property="article:published_time" content="2017-06-08T07:32:57+08:00"><meta property="article:modified_time" content="2017-06-08T07:32:57+08:00"><meta property="article:tag" content="MySQL"><meta itemprop=name content="Mysql Basic 2"><meta itemprop=description content="The basic data types of MySQL database include numeric types, character types and time and date types. Basic operations on MySQL database tables. Basic operations of MySQL database records"><meta itemprop=datePublished content="2017-06-08T07:32:57+08:00"><meta itemprop=dateModified content="2017-06-08T07:32:57+08:00"><meta itemprop=wordCount content="1004"><meta itemprop=keywords content="MySQL"><meta name=twitter:card content="summary"><meta name=twitter:title content="Mysql Basic 2"><meta name=twitter:description content="The basic data types of MySQL database include numeric types, character types and time and date types. Basic operations on MySQL database tables. Basic operations of MySQL database records"><!--[if lte IE 9]><script src=https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js></script><![endif]--><!--[if lt IE 9]><script src=https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js></script><script src=https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js></script><![endif]--></head><body><div id=mobile-navbar class=mobile-navbar><div class=mobile-header-logo><a href=/en/ class=logo>Frank Wang’s Coding World</a></div><div class=mobile-navbar-icon><span></span>
<span></span>
<span></span></div></div><nav id=mobile-menu class="mobile-menu slideout-menu"><ul class=mobile-menu-list><a href=/en/><li class=mobile-menu-item>Home</li></a><a href=/en/post/><li class=mobile-menu-item>Archives</li></a><a href=/en/tags/><li class=mobile-menu-item>Tags</li></a><a href=/en/categories/><li class=mobile-menu-item>Categories</li></a><a href=/><li class=mobile-menu-item>中文</li></a></ul></nav><div class=container id=mobile-panel><header id=header class=header><div class=logo-wrapper><a href=/en/ class=logo>Frank Wang’s Coding World</a></div><nav class=site-navbar><ul id=menu class=menu><li class=menu-item><a class=menu-item-link href=/en/>Home</a></li><li class=menu-item><a class=menu-item-link href=/en/post/>Archives</a></li><li class=menu-item><a class=menu-item-link href=/en/tags/>Tags</a></li><li class=menu-item><a class=menu-item-link href=/en/categories/>Categories</a></li><li class=menu-item><a class=menu-item-link href=/>中文</a></li></ul></nav></header><main id=main class=main><div class=content-wrapper><div id=content class=content><article class=post><header class=post-header><h1 class=post-title>Mysql Basic 2</h1><div class=post-meta><span class=post-time>2017-06-08</span><div class=post-category><a href=/en/categories/database/>Database</a></div><span class=more-meta>1004 words </span><span class=more-meta>5 mins read</span></div></header><div class=post-toc id=post-toc><h2 class=post-toc-title>Contents</h2><div class="post-toc-content always-active"><nav id=TableOfContents><ul><li><a href=#1-basic-data-types-of-mysql>1. Basic data types of MySQL:</a><ul><li><a href=#1-numeric-type>1. Numeric type:</a></li><li><a href=#2-character-type>2. Character type:</a></li><li><a href=#3-date-and-time-type>3. Date and time type:</a></li></ul></li><li><a href=#2-data-table-operations>2. Data table operations:</a><ul><li><a href=#1-view-the-current-database>1. View the current database</a></li><li><a href=#2-create-data-table>2. Create data table</a></li><li><a href=#3-view-data-table>3. View data table</a></li><li><a href=#4-view-the-data-table-structure>4. View the data table structure</a></li></ul></li><li><a href=#3-recording-operations>3. Recording operations:</a><ul><li><a href=#1-insert-records>1. Insert records</a></li><li><a href=#2-find-records>2. Find records</a></li><li><a href=#3-null-value>3. Null value</a></li><li><a href=#4-automatic-numbering-auto_increment>4. Automatic numbering: AUTO_INCREMENT</a></li><li><a href=#5-primary-key-primary-key>5. Primary key: PRIMARY KEY</a></li><li><a href=#5unique-constraint-unique-key>5.Unique constraint: UNIQUE KEY</a></li><li><a href=#6default-value-default>6.Default value: DEFAULT</a></li></ul></li></ul></nav></div></div><div class=post-content><h2 id=1-basic-data-types-of-mysql>1. Basic data types of MySQL:</h2><h3 id=1-numeric-type>1. Numeric type:</h3><p>Includes strict numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).</p><table><thead><tr><th>type</th><th style=text-align:center>size</th><th style=text-align:center>range (signed)</th><th style=text-align:center>range (unsigned)</th><th style=text-align:right>purpose</th></tr></thead><tbody><tr><td>TINYINT</td><td style=text-align:center>1 byte</td><td style=text-align:center>(-128, 127)</td><td style=text-align:center>(0, 255)</td><td style=text-align:right>small integer value</td></tr><tr><td>SMALLINT</td><td style=text-align:center>2 bytes</td><td style=text-align:center>(-2^15, 2^15 -1)</td><td style=text-align:center>(0, 2^16 -1)</td><td style=text-align:right>Large integer value</td></tr><tr><td>MEDIUMINT</td><td style=text-align:center>3 bytes</td><td style=text-align:center>(-2^23, 2^23 -1)</td><td style=text-align:center>(0, 2^24 -1)</td><td style=text-align:right>Large integer value</td></tr><tr><td>INT or INTEGER</td><td style=text-align:center>4 bytes</td><td style=text-align:center>(-2^31, 2^31 -1)</td><td style=text-align:center>(0, 2^32 -1)</td><td style=text-align:right>Large integer value</td></tr><tr><td>BIGINT</td><td style=text-align:center>8 bytes</td><td style=text-align:center>(-2^63, 2^63 -1)</td><td style=text-align:center>(0, 2^64 -1)</td><td style=text-align:right>Very large integer value</td></tr><tr><td>FLOAT</td><td style=text-align:center>4 bytes</td><td style=text-align:center>(-3.402 823 466 E+38, -1.175 494 351 E-38), 0, (1.175 494 351 E-38, 3.402 823 466 351 E+38)</td><td style=text-align:center>0, (1.175 494 351 E-38, 3.402 823 466 E+38)</td><td style=text-align:right>Single precision floating point value</td></tr><tr><td>DOUBLE</td><td style=text-align:center>8 bytes</td><td style=text-align:center>(-1.797 693 134 862 315 7 E+308, -2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)</td><td style=text-align:center>0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)</td><td style=text-align:right>Double precision floating point value</td></tr><tr><td>DECIMAL</td><td style=text-align:center>DECIMAL(M,D). If M>D, M+2 otherwise D+2</td><td style=text-align:center>Values attached to M and D</td><td style=text-align:center>Values attached to M and D</td><td style=text-align:right>Decimal value</td></tr></tbody></table><p>DECIMAL(M,D)：</p><p>M specifies the maximum number of decimal digits that can be stored to the left and right of the specified decimal point, with a maximum precision of 38.</p><p>D specifies the maximum number of decimal digits that can be stored to the right of the decimal point. The number of decimal places must be a value from 0 to a. The default number of decimal places is 0.</p><h3 id=2-character-type>2. Character type:</h3><p>String types mainly include CHAR, VARCHAR, TEXT, MEDIUMTEXT, LONGTEXT, EMU, etc.</p><table><thead><tr><th>Type</th><th style=text-align:center>Size</th><th style=text-align:right>Purpose</th></tr></thead><tbody><tr><td>CHAR</td><td style=text-align:center>0<del>2^8-1 (0</del>255) bytes</td><td style=text-align:right>fixed-length string</td></tr><tr><td>VARCHAR</td><td style=text-align:center>0<del>2^16-1 (0</del>65536) bytes</td><td style=text-align:right>Variable length string</td></tr><tr><td>TINYTEXT</td><td style=text-align:center>0<del>2^8-1 (0</del>255) bytes</td><td style=text-align:right>short text string</td></tr><tr><td>TEXT</td><td style=text-align:center>0<del>2^16-1 (0</del>65536) bytes</td><td style=text-align:right>Long text data</td></tr><tr><td>MEDIUMTEXT</td><td style=text-align:center>0<del>2^24-1 (0</del>16777215) bytes</td><td style=text-align:right>Medium length text data</td></tr><tr><td>LONGTEXT</td><td style=text-align:center>0<del>2^32-1 (0</del>4294967296)</td><td style=text-align:right>Very large text data</td></tr></tbody></table><p>Fixed-length string: If it does not reach the specified length, trailing spaces will be padded.</p><h3 id=3-date-and-time-type>3. Date and time type:</h3><p>The date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.</p><p>Each time type has a range of valid values and a “zero” value, which is used when specifying an illegal value that MySQL cannot represent.</p><p>Type Size
(Bytes) Range Format Purpose
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD date value
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS time value or duration
YEAR 1 1901/2155 YYYY year value
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS Mixed date and time values
TIMESTAMP 4 1970-01-01 00:00:00/Sometime in 2037 YYYYMMDD HHMMSS Mixed date and time value, timestamp</p><table><thead><tr><th>type</th><th style=text-align:center>size</th><th style=text-align:center>range</th><th style=text-align:center>format</th><th style=text-align:right>purpose</th></tr></thead><tbody><tr><td>YEAR</td><td style=text-align:center>1 byte</td><td style=text-align:center>1901~2155</td><td style=text-align:center>YYYY</td><td style=text-align:right>Year value</td></tr><tr><td>DATE</td><td style=text-align:center>3 bytes</td><td style=text-align:center>1000-01-01 ~ 9999-12-31</td><td style=text-align:center>YYYY-MM-DD</td><td style=text-align:right>Date value</td></tr><tr><td>TIME</td><td style=text-align:center>3 bytes</td><td style=text-align:center>-838:59:59 ~ 838:59:59</td><td style=text-align:center>HH:MM:SS</td><td style=text-align:right>Time value or duration</td></tr><tr><td>DATETIME</td><td style=text-align:center>8 bytes</td><td style=text-align:center>1000-01-01 00:00:00 ~ 9999-12-31 23:59:59</td><td style=text-align:center>YYYY-MM-DD HH:MM:SS</td><td style=text-align:right>Mixed date and time values</td></tr><tr><td>TIMESTAMP</td><td style=text-align:center>4 bytes</td><td style=text-align:center>1970-01-01 00:00:00 ~ 2037</td><td style=text-align:center>YYYYMMDD HHMMSS</td><td style=text-align:right>Timestamp</td></tr></tbody></table><h2 id=2-data-table-operations>2. Data table operations:</h2><h3 id=1-view-the-current-database>1. View the current database</h3><p>SELECT DATABASE();</p><h3 id=2-create-data-table>2. Create data table</h3><p>CREATE TABLE [IF NOT EXISTS] table_name ( column_name data_type,…);</p><p>CREATE TABLE IF NOT EXISTS user(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
);</p><h3 id=3-view-data-table>3. View data table</h3><p>SHOW TABLES;</p><p>View data tables from other databases</p><p>SHOW TABLES FROM mysql;</p><h3 id=4-view-the-data-table-structure>4. View the data table structure</h3><p>SHOW COLUMNS FROM tbl_name;</p><p>SHOW COLUMNS FROM user;</p><h2 id=3-recording-operations>3. Recording operations:</h2><p>The rows in the data table are called database records</p><h3 id=1-insert-records>1. Insert records</h3><p>INSERT [INFO] tbl_name [(col_name,..)] VALUES(val,..);</p><p>INSERT user VALUES(‘Frank’, 22, 3500.18);</p><p>If the field name (i.e. column name) is omitted, the value must be consistent with the number of fields, otherwise an error will be reported.</p><p>Column count doesn’t match value count at row 1</p><p>If you assign a value to some fields, you need to specify the field name.</p><p>INSERT user(username,salary) VALUES(‘Jack’, 4500.18);</p><h3 id=2-find-records>2. Find records</h3><p>SELECT expr,… FROM tbl_name;</p><p>View all records</p><p>SELECT * FROM tbl_name;</p><p>SELECT * FROM user;</p><h3 id=3-null-value>3. Null value</h3><p>NULL, the field value can be empty;
NOT NULL, field value cannot be empty.</p><p>CREATE TABLE tb2(
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);</p><h3 id=4-automatic-numbering-auto_increment>4. Automatic numbering: AUTO_INCREMENT</h3><p>Must be used in combination with primary key. By default, the actual value is 1, and the increment is 1 each time.</p><p>CREATE TABLE tb3(
id SMALLINT UNSIGNED AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);</p><p>Error reported:</p><p>ERROR 1075(42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.</p><h3 id=5-primary-key-primary-key>5. Primary key: PRIMARY KEY</h3><ol><li><p>Each data table can only have one primary key;</p></li><li><p>The primary key ensures the uniqueness of the record;</p></li><li><p>The primary key is automatically NOT NULL.</p></li></ol><p>Create data table tb4:</p><p>CREATE TABLE tb4(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED NULL
);</p><p>Insert record:</p><p>INSERT tb3(username) VALUES(‘Ross’);
INSERT tb3(username) VALUES(‘Richard’);
INSERT tb3(username) VALUES(‘Monica’);</p><h3 id=5unique-constraint-unique-key>5.Unique constraint: UNIQUE KEY</h3><ol><li><p>UNIQUE KEY can ensure the uniqueness of records;</p></li><li><p>UNIQUE KEY can be null;</p></li><li><p>Multiple unique constraints can exist in each table.</p></li></ol><p>Create data table tb5:</p><p>CREATE TABLE tb5(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
age TINYINT UNSIGNED NULL
);</p><p>View the tb5 data structure of the data table just created:</p><p>SHOW COLUMNS FROM tb5;</p><p>Insert a record:</p><p>INSERT tb5(username, age) VALUES(‘Ross’, 30);</p><p>The insertion is successful, and there is now a record with <code>username</code> as ‘Ross’. Insert another record:</p><p>INSERT tb5(username, age) VALUES(‘Ross’, 28);</p><p>Error reported:</p><p>ERROR 1062 (23000): Duplicate entry ‘Ross’ for key ‘username’</p><h3 id=6default-value-default>6.Default value: DEFAULT</h3><p>When a record is inserted, if a field is not explicitly assigned a value, a default value is automatically assigned.</p><p>CREATE TABLE tb6(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM(‘1’, ‘2’, ‘3’) DEFAULT ‘3’
);</p><p>View the data structure of the data table tb6 just created:</p><p>SHOW COLUMNS FROM tb6;</p><p>Insert a record:</p><p>INSERT tb6(username) VALUES(‘Frank’);</p><p>View records:</p><p>SELECT * FROM tb6;</p></div><footer class=post-footer><div class=post-tags><a href=/en/tags/mysql/>MySQL</a></div><nav class=post-nav><a class=prev href=/python_mysqldb.html><i class="iconfont icon-left"></i>
<span class="prev-text nav-default">Python Mysql</span>
<span class="prev-text nav-mobile">Prev</span>
</a><a class=next href=/mysql-basic.html><span class="next-text nav-default">Mysql Common Commands</span>
<span class="next-text nav-mobile">Next</span>
<i class="iconfont icon-right"></i></a></nav></footer></article></div></div></main><footer id=footer class=footer><div class=social-links><a href=mailto:frankwang0909@gmail.com class="iconfont icon-email" title=email></a><a href=https://www.twitter.com/frankwang0909 class="iconfont icon-twitter" title=twitter></a><a href=https://www.github.com/frankwang0909 class="iconfont icon-github" title=github></a><a href=http://frankwang0909.github.io/en/index.xml type=application/rss+xml class="iconfont icon-rss" title=rss></a></div><div class=copyright><span class=power-by>Powered by <a class=hexo-link href=https://gohugo.io>Hugo</a>
</span><span class=division>|</span>
<span class=theme-info>Theme -
<a class=theme-link href=https://github.com/olOwOlo/hugo-theme-even>Even</a>
</span><span class=copyright-year>©
2016 -
2026<span class=heart><i class="iconfont icon-heart"></i></span><span></span></span></div></footer><div class=back-to-top id=back-to-top><i class="iconfont icon-up"></i></div></div><script type=text/javascript src=/lib/jquery/jquery-3.2.1.min.js></script><script type=text/javascript src=/lib/slideout/slideout-1.0.1.min.js></script><script type=text/javascript src=/lib/fancybox/jquery.fancybox-3.1.20.min.js></script><script type=text/javascript src=/js/main.min.4ae89da218555efa0e7093a20b92017d2e1202b66fff9fc2edf4cb8d44b44c6e.js></script><script type=text/javascript>window.MathJax={tex:{}}</script><script async src=https://cdn.jsdelivr.net/npm/mathjax@3.0.5/es5/tex-mml-chtml.js integrity="sha256-HGLuEfFcsUJGhvB8cQ8nr0gai9EucOOaIxFw7qxmd+w=" crossorigin=anonymous></script></body></html>