mysql查询语句使用基础

最新推荐文章于 2024-08-15 23:27:38 发布

wang161x

最新推荐文章于 2024-08-15 23:27:38 发布

阅读量336

点赞数

CC 4.0 BY-SA版权

分类专栏： linux 文章标签： MySQL查询语句基础

本文链接：https://blog.youkuaiyun.com/weixin_41363156/article/details/84491391

linux 专栏收录该内容

29 篇文章

订阅专栏

我们编译完mysql后，就可以进行的使用它了，那么如何进入mysql呢？在我们安装完mysql后会生成4个没有密码的root帐号，2个没有账号密码的mysql账户，注意这里是mysql的root账户并不是系统的账户，但是为什么是4 个注意这与本身的host有关，就是主机名为localhost、：：1、127.0.0.1、bogon他们四个的，主机不同可能不同，但是localhost、：：1、127.0.0.1他们3个是一定有的，在我们安装完mysql后可以把mysql的连接密码更改了，更改密码的指令在以后会有提及，所以不要着急。
本篇文章讲解mysql：DML、DDL
DML(SELECT,INSERT,UODATE,DELETE)
首先就是SELECT，很多人都使用过数据库，但是真的了解过吗？淘宝的购物车，银行的金额信息这些都是保存在数据库中，所以数据库是一个广泛用于大众生活的东西，但是我们用数据库的大部分时间都是在查询，比如我们在淘宝淘东西的时候，我们看到了种种商品的标题价格以及属性，访问的各个资源都是数据库的里面的表：TABLES，当我们看到的商品的标题价格以及乃加id就是在查询数据库中的资源，只不过我们没有用sql语句，也没有通过sql的管理工具进行查询，而是通过淘宝网给出的淘宝APP内置集成的API来将人们选中的资源进行sql语句的解析以及优化器进行优化，然后执行脚本自动在数据库中执行这些sql语句，数据库返回结果后呈现在人们眼前，但是为什么会这么快呢？虽然说mysql服务器有负载均衡，但是也抗不住用户查询次数过多，时间过于集中，这就是数据库的查询缓存，数据库将用户的查询结果缓存下来，如果有别的用户使用同一语句进行查询时，命令派发器先查询缓存后，命中后直接返回结果，如大家所知，缓存不是越大越好，也不是越小越好，在查询缓存时，密码各种只要在30%以上或者特别复杂的查询就是有效缓存，使用资源消耗做标准。
查看开销：MYSQL内置的机制可以判断开销大小
优化器：优化查询语句，减小开销。
私有数据、大数据不会缓存，用户可以自行定义，手动指定。

                                            MYSQL的查询操作
          单表查询：简单查询
          多表查询：连续查询
          联合查询

选择和投影
投影：挑选需显示的字段
选择：挑选符合条件的行
投影：SELECT 字段1,字段2,… FROM tb_name;
SELECT * FROM tb_name;
选择：SELECT 字段1,… FROM TB_NAME WHERE 子句;
子句：布尔条件式，为真显示，为假不显示。
布尔条件表达式操作符：
=：等值比较
< <=: 小于、小于等于
> >=：大于、大于等于
! ：非
IS NULL：表示结果为空
IS NOT NULL：表示结果不为空
LIKE:模糊搜索，匹配、支持的通配符
%:任意长度的任意字符
-:任意单个字符
RLIKE:支持使用正则表达式
REGXP:性能较低
SELECT values_to_display FROM table_name WHERE expression GROUP BY how_to_group HAVING expression ORDER BY how_to_sort LIMIT row_count;
其中ORDER BY how_to_sort是表示根据哪个字段进行排序。默认为升序，（ASC表示升序、DESC表示降序），LIMIT row_count只返回结果中的某一个字段 LIMIT 2表示显示结果的前两行，LIMIT 2,3 表示显示偏移2行后的3行。
IN：判断指定字段的值是否在给定列表中，
X BETWEEN 20 AND 40; 表示大于20小于40的X，
BETWEEN…AND…:位于指定的范围之间。

     聚合函数：
          SUM（ ），AVG（），MAX（），COUNT（）。
     SELECT语句的执行流程：

FROM clause —> WHERE clause —->GROUP BY —-> HAVING clause —->ORDER BY —->SELECT —-> LIMIT
SELECT语句
DISTINCT：指定的结果相同的显示一次
SQL_CACHE:缓存于查询缓存中。
SQL_NO_CACHE：不缓存查询结果

示例：下面我们将简单演示一下mysql的用法。
首先我们下载一个收据库文件：https://pan.baidu.com/s/1qZQ35HM
提取码：rhxb
下载完成后导入数据库：
首先我们先进入数据库

</span>
[root@bogon data]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.5.59-log Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql&gt;
<span style="color: #000000;">

在这里插入图片描述
首先我们可以查看一下mysql自带的数据库：mysql,test,其中test是一张空表，mysql中是关于mysql的设置信息。

</span>
mysql&gt; use test
Database changed
mysql&gt; show tables;
Empty set (0.00 sec)
mysql&gt; use mysql;
Database changed
mysql&gt; show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.03 sec)
mysql&gt;
<span style="color: #000000;">

在这里插入图片描述
接下来我们看一下mysql系统上的用户及一些系统配置

</span>
mysql&gt; select * from user\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
*************************** 2. row ***************************
未完。。。。
<span style="color: #000000;">

在这里插入图片描述
我们接下来导入我们的数据库文件：
我把下载的文件放在/mydata/data下，使用命令：

</span>
[root@bogon data]# mysql test &lt; /mydata/data/hellodb.sql
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Ctrl-C' at line 1
[root@bogon data]# vim /mydata/data/hellodb.sql
[root@bogon data]#
[root@bogon data]# mysql test &lt; /mydata/data/hellodb.sql [root@bogon data]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 32 Server version: 5.5.59-log Source distribution Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql&gt; use test;
Database changed
mysql&gt; show tables;
+----------------+
| Tables_in_test |
+----------------+
| hat_area |
| hat_city |
| hat_province |
+----------------+
3 rows in set (0.00 sec)
mysql&gt;
<span style="color: #000000;">

在这里插入图片描述
如图导入成功！下面我们就可以使用这几张表了。

</span>
mysql&gt; DESC hat_area;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| areaID | varchar(50) | YES | | NULL | |
| area | varchar(60) | YES | | NULL | |
| father | varchar(6) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

因为代码与图片没有差异差异，所以不贴图了，
mysql> DESC hat_area;描述hat_area这张表的结构，
其中第一行就是数据类型了：
数据类型的功用：
1,存储的值类型：由数据类型决定
2,占据的存储空间
3,定长，变长。char varchar
4,如何被索引及排序
5,是否支持索引
数据字典：系统编目（system catalog）
保存数据库服务器上的元数据
元数据：
关系的名字
每个关系的各字段的名字
各字段的数据类型和长度
约束
每个关系视图的名字及视图的定义
授权用户的名字
用户的授权和账户信息
统计类的数据：
每个关系属性的个数
每个关系中行数
每个关系中的存储方法
保存元数据的数据库
information_schema
performance_schema 兼容sql语句类似/proc
mysql
数据类型：
字符型：char varchar binary varbinary text blob
数值型：精确数值型，近似数值型
日期时间型：日期型时间型日期时间型时间戳
内置类型：ENUM（枚举）SET（集合）
精确数值型：整型，十进制数据：decimal
近似数值型：单精度浮点型双精度型浮点型布尔型
数值型：
TINYINT
SMALLINT
MEDIUMINT
INT
BIGINT
DECIMAL
FLOAT
DOUBLE
字符型常用的属性修饰符：CHAR VARCHAR和TEXT
NOT NULL ：非空约束
NULL：允许为空
DEFAULT ’string‘：默认值，不适于TEXT类型
CHARACTER SET ’字符集‘
COLLECTION’规则’：排序方式

</span>
mysql&gt; SHOW VARIABLES LIKE '%char%'
-&gt; ;
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.5.55-linux2.6-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.13 sec)

mysql&gt; show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.03 sec)

mysql&gt; SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | | Yes | 1 |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 | 72 | | Yes | 1 |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
| koi8r_bin | koi8r | 74 | | Yes | 1 |
| latin1_german1_ci | latin1 | 5 | | Yes | 1 |
| latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 |
| latin1_danish_ci | latin1 | 15 | | Yes | 1 |
| latin1_german2_ci | latin1 | 31 | | Yes | 2 |
| latin1_bin | latin1 | 47 | | Yes | 1 |
| latin1_general_ci | latin1 | 48 | | Yes | 1 |
| latin1_general_cs | latin1 | 49 | | Yes | 1 |
| latin1_spanish_ci | latin1 | 94 | | Yes | 1 |
| latin2_czech_cs | latin2 | 2 | | Yes | 4 |
| latin2_general_ci | latin2 | 9 | Yes | Yes | 1 |
| latin2_hungarian_ci | latin2 | 21 | | Yes | 1 |
| latin2_croatian_ci | latin2 | 27 | | Yes | 1 |
| latin2_bin | latin2 | 77 | | Yes | 1 |
| swe7_swedish_ci | swe7 | 10 | Yes | Yes | 1 |
| swe7_bin | swe7 | 82 | | Yes | 1 |
| ascii_general_ci | ascii | 11 | Yes | Yes | 1 |
| ascii_bin | ascii | 65 | | Yes | 1 |
| ujis_japanese_ci | ujis | 12 | Yes | Yes | 1 |
| ujis_bin | ujis | 91 | | Yes | 1 |
| sjis_japanese_ci | sjis | 13 | Yes | Yes | 1 |
| sjis_bin | sjis | 88 | | Yes | 1 |
| hebrew_general_ci | hebrew | 16 | Yes | Yes | 1 |
| hebrew_bin | hebrew | 71 | | Yes | 1 |
| tis620_thai_ci | tis620 | 18 | Yes | Yes | 4 |
| tis620_bin | tis620 | 89 | | Yes | 1 |
| euckr_korean_ci | euckr | 19 | Yes | Yes | 1 |
| euckr_bin | euckr | 85 | | Yes | 1 |
| koi8u_general_ci | koi8u | 22 | Yes | Yes | 1 |
| koi8u_bin | koi8u | 75 | | Yes | 1 |
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86 | | Yes | 1 |
| greek_general_ci | greek | 25 | Yes | Yes | 1 |
| greek_bin | greek | 70 | | Yes | 1 |
| cp1250_general_ci | cp1250 | 26 | Yes | Yes | 1 |
| cp1250_czech_cs | cp1250 | 34 | | Yes | 2 |
| cp1250_croatian_ci | cp1250 | 44 | | Yes | 1 |
| cp1250_bin | cp1250 | 66 | | Yes | 1 |
| cp1250_polish_ci | cp1250 | 99 | | Yes | 1 |
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
| latin5_turkish_ci | latin5 | 30 | Yes | Yes | 1 |
| latin5_bin | latin5 | 78 | | Yes | 1 |
| armscii8_general_ci | armscii8 | 32 | Yes | Yes | 1 |
| armscii8_bin | armscii8 | 64 | | Yes | 1 |
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| ucs2_general_ci | ucs2 | 35 | Yes | Yes | 1 |
| ucs2_bin | ucs2 | 90 | | Yes | 1 |
| ucs2_unicode_ci | ucs2 | 128 | | Yes | 8 |
| ucs2_icelandic_ci | ucs2 | 129 | | Yes | 8 |
| ucs2_latvian_ci | ucs2 | 130 | | Yes | 8 |
| ucs2_romanian_ci | ucs2 | 131 | | Yes | 8 |
| ucs2_slovenian_ci | ucs2 | 132 | | Yes | 8 |
| ucs2_polish_ci | ucs2 | 133 | | Yes | 8 |
| ucs2_estonian_ci | ucs2 | 134 | | Yes | 8 |
| ucs2_spanish_ci | ucs2 | 135 | | Yes | 8 |
| ucs2_swedish_ci | ucs2 | 136 | | Yes | 8 |
| ucs2_turkish_ci | ucs2 | 137 | | Yes | 8 |
| ucs2_czech_ci | ucs2 | 138 | | Yes | 8 |
| ucs2_danish_ci | ucs2 | 139 | | Yes | 8 |
| ucs2_lithuanian_ci | ucs2 | 140 | | Yes | 8 |
| ucs2_slovak_ci | ucs2 | 141 | | Yes | 8 |
| ucs2_spanish2_ci | ucs2 | 142 | | Yes | 8 |
| ucs2_roman_ci | ucs2 | 143 | | Yes | 8 |
| ucs2_persian_ci | ucs2 | 144 | | Yes | 8 |
| ucs2_esperanto_ci | ucs2 | 145 | | Yes | 8 |
| ucs2_hungarian_ci | ucs2 | 146 | | Yes | 8 |
| ucs2_sinhala_ci | ucs2 | 147 | | Yes | 8 |
| ucs2_general_mysql500_ci | ucs2 | 159 | | Yes | 1 |
| cp866_general_ci | cp866 | 36 | Yes | Yes | 1 |
| cp866_bin | cp866 | 68 | | Yes | 1 |
| keybcs2_general_ci | keybcs2 | 37 | Yes | Yes | 1 |
| keybcs2_bin | keybcs2 | 73 | | Yes | 1 |
| macce_general_ci | macce | 38 | Yes | Yes | 1 |
| macce_bin | macce | 43 | | Yes | 1 |
| macroman_general_ci | macroman | 39 | Yes | Yes | 1 |
| macroman_bin | macroman | 53 | | Yes | 1 |
| cp852_general_ci | cp852 | 40 | Yes | Yes | 1 |
| cp852_bin | cp852 | 81 | | Yes | 1 |
| latin7_estonian_cs | latin7 | 20 | | Yes | 1 |
| latin7_general_ci | latin7 | 41 | Yes | Yes | 1 |
| latin7_general_cs | latin7 | 42 | | Yes | 1 |
| latin7_bin | latin7 | 79 | | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| cp1251_bulgarian_ci | cp1251 | 14 | | Yes | 1 |
| cp1251_ukrainian_ci | cp1251 | 23 | | Yes | 1 |
| cp1251_bin | cp1251 | 50 | | Yes | 1 |
| cp1251_general_ci | cp1251 | 51 | Yes | Yes | 1 |
| cp1251_general_cs | cp1251 | 52 | | Yes | 1 |
| utf16_general_ci | utf16 | 54 | Yes | Yes | 1 |
| utf16_bin | utf16 | 55 | | Yes | 1 |
| utf16_unicode_ci | utf16 | 101 | | Yes | 8 |
| utf16_icelandic_ci | utf16 | 102 | | Yes | 8 |
| utf16_latvian_ci | utf16 | 103 | | Yes | 8 |
| utf16_romanian_ci | utf16 | 104 | | Yes | 8 |
| utf16_slovenian_ci | utf16 | 105 | | Yes | 8 |
| utf16_polish_ci | utf16 | 106 | | Yes | 8 |
| utf16_estonian_ci | utf16 | 107 | | Yes | 8 |
| utf16_spanish_ci | utf16 | 108 | | Yes | 8 |
| utf16_swedish_ci | utf16 | 109 | | Yes | 8 |
| utf16_turkish_ci | utf16 | 110 | | Yes | 8 |
| utf16_czech_ci | utf16 | 111 | | Yes | 8 |
| utf16_danish_ci | utf16 | 112 | | Yes | 8 |
| utf16_lithuanian_ci | utf16 | 113 | | Yes | 8 |
| utf16_slovak_ci | utf16 | 114 | | Yes | 8 |
| utf16_spanish2_ci | utf16 | 115 | | Yes | 8 |
| utf16_roman_ci | utf16 | 116 | | Yes | 8 |
| utf16_persian_ci | utf16 | 117 | | Yes | 8 |
| utf16_esperanto_ci | utf16 | 118 | | Yes | 8 |
| utf16_hungarian_ci | utf16 | 119 | | Yes | 8 |
| utf16_sinhala_ci | utf16 | 120 | | Yes | 8 |
| cp1256_general_ci | cp1256 | 57 | Yes | Yes | 1 |
| cp1256_bin | cp1256 | 67 | | Yes | 1 |
| cp1257_lithuanian_ci | cp1257 | 29 | | Yes | 1 |
| cp1257_bin | cp1257 | 58 | | Yes | 1 |
| cp1257_general_ci | cp1257 | 59 | Yes | Yes | 1 |
| utf32_general_ci | utf32 | 60 | Yes | Yes | 1 |
| utf32_bin | utf32 | 61 | | Yes | 1 |
| utf32_unicode_ci | utf32 | 160 | | Yes | 8 |
| utf32_icelandic_ci | utf32 | 161 | | Yes | 8 |
| utf32_latvian_ci | utf32 | 162 | | Yes | 8 |
| utf32_romanian_ci | utf32 | 163 | | Yes | 8 |
| utf32_slovenian_ci | utf32 | 164 | | Yes | 8 |
| utf32_polish_ci | utf32 | 165 | | Yes | 8 |
| utf32_estonian_ci | utf32 | 166 | | Yes | 8 |
| utf32_spanish_ci | utf32 | 167 | | Yes | 8 |
| utf32_swedish_ci | utf32 | 168 | | Yes | 8 |
| utf32_turkish_ci | utf32 | 169 | | Yes | 8 |
| utf32_czech_ci | utf32 | 170 | | Yes | 8 |
| utf32_danish_ci | utf32 | 171 | | Yes | 8 |
| utf32_lithuanian_ci | utf32 | 172 | | Yes | 8 |
| utf32_slovak_ci | utf32 | 173 | | Yes | 8 |
| utf32_spanish2_ci | utf32 | 174 | | Yes | 8 |
| utf32_roman_ci | utf32 | 175 | | Yes | 8 |
| utf32_persian_ci | utf32 | 176 | | Yes | 8 |
| utf32_esperanto_ci | utf32 | 177 | | Yes | 8 |
| utf32_hungarian_ci | utf32 | 178 | | Yes | 8 |
| utf32_sinhala_ci | utf32 | 179 | | Yes | 8 |
| binary | binary | 63 | Yes | Yes | 1 |
| geostd8_general_ci | geostd8 | 92 | Yes | Yes | 1 |
| geostd8_bin | geostd8 | 93 | | Yes | 1 |
| cp932_japanese_ci | cp932 | 95 | Yes | Yes | 1 |
| cp932_bin | cp932 | 96 | | Yes | 1 |
| eucjpms_japanese_ci | eucjpms | 97 | Yes | Yes | 1 |
| eucjpms_bin | eucjpms | 98 | | Yes | 1 |
+--------------------------+----------+-----+---------+----------+---------+
197 rows in set (0.00 sec)
mysql&gt;
<span style="color: #000000;">

BINARY，VARBINARY和BLOB几种字符型常用的
NOT NULL
NULL
BEFAULT ：不适用于BLOB
整型的常用属性修饰符：
AUTO_INCREMENT:自增长
前提：非空且惟一，支持索引，非负值
UNSIGNED：无符号
NULL
NOT NULL
DEFAULT
浮点型常用修饰符：
NOT NULL
NULL
DEFAULT
UNSIGNED
日期时间型的修饰符
NOT NULL
NULL
DEFAULT
ENUM和SET的修饰符
NOT NULL
NULL
DEFAULT
MySQL SQL_MODE：SQL模式
TRADITIONAL， STRICT_TRANS_TABLES or STRICT_ALL_TABLES
设定服务器变量的值：（仅用于支持动态的变量）
支持修改的服务器变量
动态变量：可以在MySQL运行是修改
静态变量：于配置文件中修改其值，重启方能生效
服务器从其生效的范围来说可以分两类：
全局变量：服务器级别，修改之后仅对新建立的会话有效
会话变量：会话级别仅对当前会话有效，新的会话建立后，从全局继承各变量
查看服务器变量：

</span>
mysql&gt; SHOW {GLOBAL|SESSION} VARIABLES [LIKE'']
mysql&gt; SELECT @@{GLOBAL|SESSION} VARIABLES NAME
<span style="color: #000000;">

修改变量
前提：尽管理员有权限修改全局变量

</span>
mysql&gt;SET {GLOBAL|SESSION} VARIABLE_NAM='VALUE';

注意：无论是全局变量还是会话级别的动态变量修改，在重启mysql后都会失效，想永久有效，可定义在配置文件中的相应段中[mysqld];
MySQL字符大小写：
1,SQL关键字及函数不区分字符大小写
2,数据库，表及视图名称的大小写区分与否取决于底层OS及FS
3在存储过程，存储函数及事件调度器的名称不区分大小写但触发器区分
4,表别名不区分大小写
5,对字段中的数据，如果字段类型为BINARY类型，则区分大小写，非BINARY不区分大小写。