mysql查询表变量_MySQL 之SQL语言、表库操作、查询及变量精讲

SQL语言

1、 SQL语言组成部分

DDL：定义语言 (create、drop、alter)

DML：操作语言 (insert、delete、update、select)

DCL: 控制语言 (grant、revoke)

2、 数据类型

字符型

#char 字符固定的空间 255 字符

#varchar 变化的空间，不区分大小写 65535 字符

#tinytext 255 字符

#text 作为存储对象，不会直接存储在表中，而是存放了指向其他表的指针 65535

#mediumtext 16,777,215 字符

#longtext 4,294,967,295 字符

#binary 二进制数据固定的空间 0-255bytes

#varbinary 变化的空间，区分大小写 0-65532bytes

#tinyblob 最大255bytes

#blob 二进制大对象 最大64Kb

#mediumblob 最大16Mb

#longblob 最大4Gb

数值型

①精确数值型：整型、十进制数据

#tinyint

#smallint

#mediumint

#int

#bigint

#decimal (十进制数据)

②近似数值型

#float 单精度浮点数

#double 双精度浮点数

③日期时间型

#date

#time

#datetime

#timestamp

#year

④布尔型

#0和1

##mysql 实际上没有布尔型

⑤内建类型

#enum 枚举

#set 集合

3、数据常用属性修饰符

字符型常用的属性修饰符

#not_null 非空约束

#null 允许为空

#defaul'string' 默认值，不使用text类型

#character set ‘字符集’

mysql> show character set;

#collation'规则' 排序规则

mysql> show collation;

整数型的常用属性修饰符

#auto_increment 自动增长.(前提：非空，且唯一，支持索引，非负值)

#unsigned 无符号

#null 允许为空

#not null 不为空

#default 默认

浮点型常用修饰符

#not null

#null

#default

#unsigned

日期时间型的修饰符

#not null

#null

#default

内建类型(enum枚举和set集合)的修饰符

#not null

#null

#default 'string'

4、MySQL中字符大小写要求

#1、SQL关键字及函数名不区分字符大小写；

#2、数据库、表及视图名称的大小区分与否取决于低层OS、FS

#3、存储过程、存储函数及时间调度器的名字不区分大小写，但触发器区分大小写

#4、表别名区分大小写；

#5、对字段中的数据，如果字段类型为Binary类型，则区分大小写，非binary不区分大小写；

变量

1、设定服务器变量的值(仅用于支持动态的变量)

支持修改的服务器变量：

动态变量：可以在Mysql运行时修改

静态变量：在配置文件中修改其值，并重启后方能生效；

服务器变量从其生效范围来讲，有两类：

全局变量(global)：服务器级别，修改之后仅对新建立的会话有效；

会话变量(session)：会话级别，仅对当前回话有效；

## 会话建立时，从全局继承各变量；

2、查看服务器变量

#mysql> show {global|session} variables like/where子句;

#mysql> select @@{global|session}.variables_name;

#mysql> select * from information_schema.global_variables WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';

#mysql> select * from information_schema.session_variables WHERE VARIABLE_NAME='SOME_VARIABLE_NAME';

##常用为前两个

举例

3、修改变量

#前提：默认仅管理员有权限修改全局变量

#格式：mysql> SET {GLOBAL|SESSION} VARIABLE_NAME='VALUE';

举例

mysql > set global tx_isolation='read-committed'; 设置服务器的事务隔离级别为读提交

### 注意：无论是全局还是会话级别的动态变量修改，在重启mysqld后都会失效；想永久有效，需定义在配置文件中的相应段中如：[mysqld]服务器段

数据库操作(database)

创建数据库

#格式 CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

删除数据库

#格式 DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

修改数据库

#格式 ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

mysql> create database exam; 创建数据库exam

mysql> drop database exam; 删除数据库exam

表操作(table)

mysql表创建

创建表

格式：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_options]

[partition_options]

# (create_definition,...)：

字段的定义：字段名、类型和类型修饰符

键、约束或索引：

PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK

{INDEX|KEY}

# [table_options]

ENGINE [=] engine_name AUTO_INCREMENT [=] value

[DEFAULT] CHARACTER SET [=] charset_name

[DEFAULT] COLLATE ;'[=] collation_name

COMMENT [=] 'string'

DELAY_KEY_WRITE [=] {0 | 1}

ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]

创建一个表 (create)

mysql> create table t1 (Name varchar(50) not null,Age tinyint unsigned not null,primary key (Name,Age));

查看该表

Mysql表分类

Mysql表分为两种MyISAM表、InnoDB表

MyISAM表：每表有三个文件，都位于数据库目录中；

#tb_name.frm 表结构定义

#tb_name.MYD 数据文件

#tb_name.MYI 索引文件

InnoDB表

InnoDB表有两种存储方式

#1、默认：每表有一个独立文件和一个共享的文件

innodb_file_per_table=OFF(全局变量默认值)

tb_name.frm 表结构的定义，位于数据库目录中；

ibdata# 共享的表空间文件，默认位于数据目录(datadir指向的目录)

#2、独立的表空间文件

innodb_file_per_table=ON(配置文件中定义)

tb_name.frm 每表有一个表结构文件

tb_name.ibd 一个独有的表空间文件表创建

表删除 (drop)

格式

#drop table tb_name;

表中的数据删除 (delete)

格式：mysql> delete from table_name where 字段名='values'

#例如：mysql> delete from Persons where LastName='Griffin'删除Person表中的LastName字段中的Griffiin

表修改 (alter)--主要用于修改表中字段

当前系统中已有的mydb数据库

修改表中的字段演练，以t6表为例

#添加新字段(add)

①在t6表中添加一个Age字段

mysql> alter table t6 add Age tinyint unsigned not null;

②在t6表中添加一个Gender字段，默认为M，并且Gender字段要放在Name字段后

mysql> alter table t6 add Gender enum('M','F') not null default 'M' after Name;

#删除字段(add)

①在t6表中删除Age字段

mysql> alter table t6 drop Age;

#修改字段名称(change)

①将t6表中的Name字段改名为StuName

mysql> alter table t6 change Name StuName char(30) not null;

#修改字段类型及属性(modify)

mysql> alter table t6 modify Gender enum('M','F') not null after ID;

#字段上添加索引 (add index)

①在Name字段添加索引

mysql> alter table t6 add index(Name);

#查看字段上的索引

mysql> show indexes from t6;

#删除表中特定字段的索引 (drop index)

msyql> alter table t6 drop index Name

修改表名 (rename to)

格式：

mysql> alter table old_name rename to new_name

例如

mysql> alter table t1 rename to t7;

转换字符集及排序规则

格式

mysql> alter table t6 'character set'=

mysql> alter table t6 collation=

MySQL 的查询操作

1、查询分类

单表查询：简单查询

多表查询：连续查询

联合查询：多个查询一起使用(union)

2、选择和投影

投影：挑选要显示的字段

选择：挑选符合条件的行

投影： select 字段1，字段2，... from tb_name

select * from tb_name;

选择： select 字段，...from tb_name where 子句；

where子句：布尔条件表达式-->符合的显示，不符合的不显示；

3、布尔表达式操作符，

= 等于

<=> abc = bde ,abc =NULL

<> 不等于

< 小于

<= 小于等于

> 大于

>= 大于等于

IS NULL 为空

IS NOT NULL 不为空

RELIKE 支持正则表达式

LIKE 支持字符通配(模糊查找) %任意长度的任意字符 _任意单个字符

between and 两者之间

IS NULL

like字符通配

rlike 正则表达式

between and 两者之间

4、组合条件查询

(1) and   &&

or    ||

not    !

and

#查找年龄大于25，同时性别为女。

mysql> select Name,Age,Gender from students where age > 25 and Gender='M';

(2)  ORDER BY  字段名 [ASC|DESC]

(

ASC 升序显示

)

(DESC 降序显示)

#ASC

mysql> select Name,Age,Gender from students where age > 25 and Gender='M' order by Age asc;

#DESC

mysql> select Name,Age,Gender from students where age > 25 and Gender='M' order by Age desc;

(3)

聚合函数  sum()   avg()   max()    min( )      count()     avg()

总和           平均值     最大值       最小值      统计         平均值

(4) group by   分组

(5)      having      聚合

例子1

#查询students表以性别分组，查询各个性别的年龄和

mysql> select Gender,sum(Age) from students group by Gender;

例子2

#显示班级大于等于两个人的班级

mysql> select ClassID，count(Name) from students group by ClassID having count(Name) >= 2;

例子3

#显示总年龄小于等于50的班级

mysql> select ClassID from students group by ClassID having sum(Age) <= 50;

(6)limit 显示行

#查找students表的所有信息，偏移前两行，然后再取三行显示

mysql> select * from students limit 2,3;

#select语句的执行流程(先后顺序)

from clause --->where clause ---> group by --->having clause ---> order by 。。。---> select --->limit

SELECT语句选项

distinct:指定结果相同的只显示一次

sql_cache：缓存查询结果于缓存中；

sql_no_cache:不缓存查询结果

ps：水平有限，总结也许不够全面，请补充。后续推出多表查询、子查询及视图详解