sql在MySQL中的使用

由于sql对应的数据库有好多种，本次的sql语句的学习就是基于MySQL。

一 ：基本教程

1,select 语句

1，select查询

语法：

select column_name,column_namefrom table_name;

select * fromtable_name;

2,select distinct查询

在表中，一个列可能会包含多个重复的值，有时候，你可能希望仅仅列出不同(distinct)的值。distinct关键词用于返回唯一不同的值。

语法：

select distinct column_name,column_name fromtable_name;

其中如果selectdistinct 后面需要跟多个列名的话，则必需多个列名一致，才返回相同的。

2，where字句

where子句用来提取哪些满足指定标准的记录。

语法：

select column_name,column_name

from table_name

where column_name operator value;

sql中使用单引号来环绕文本值（大部分数据库也支持双引号）

where子句中的运算符：

运算符	描述
=	等于
<>	不等于。（在sql的一些版本中也支持!=）
>	大于
<	小于
>=	大于等于
<=	小于等于
between	在某个范围内。eg:select * from users where id between 1 and 5;搜索的是在1<=id<=5的值。
like	搜索某种模式。%：表示多个字值，也就是通配符。_:表示单个字符。 eg:select * from users where name like ‘j%’;搜索的结果是以j开头的所有的name；
in/not in	指定针对某个列的多个可能值（数值，文本或者日期）。eg:select * from users where id in(1,3);搜索的是id为1和3的值。
and & or	如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。or 则是只有一个条件成立即可。

3，order by

order by关键字用于对结果集按照一个列或者多个列进行排序。

order by 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。

语法：

select column_name,column_name

fromtable_name

order by column_name,column_nameASC(升序) | DESC(降序);

注意：当order by多列的时候，先按照第一个column_name排序，然后在第一个排列的基础上，再按照第二个column_name进行排序。

4，insert into

此语句用于向表中插入新记录。

语法：

insert into table_name

values(value1,value2….);

或：insert into table_name (column1,column2,column3….)values(value1,value2,value3…);

备注：

1，在使用第一种方式的时候，插入的记录必需包含所有的value，即使表的id为自动递增的也需要指定id的值，否则插入不成功。

2，在使用第二种方式的时候，可以指定固定的列名，如果不包含主键id，则id是自增的，其他的值为空。

5，update

update用于更新表中已存在的数据。

语法：

update table_name

set column1=value1,column2=value2

where some_column = some_column;

备注：如果在更新记录 的时候，要加上判断条件，否则整个表中的数据都会被更新。

6，delete

delete语句用于删除表中 的记录。

语法：

delete from users

where some_column = some_column;

备注：where子句规定删除哪条记录，如果省略，所有的记录都会被删除。但是表结构，属性，索引保持不变。

二 高级教程

1，别名

在使用sql时，可以为表名或者列名称指定别名。

列别名用法：

select column_name asalias_name

from table_name;

表别名用法：

select column_name(s)

from table_name asalias_name;

eg:

(1)select name as n,password as p fromusers;

(2)select id,concat(name,’ ’,password) as user_infofrom users;

(3)select * from users as u where id = 1;

2，join(连接)

join查询，用来把两个或者多个表结合起来进行查询。

（1）inner join

inner join 关键字在表中，至少存在一个匹配时返回行。

语法：

select column_name(s)

from table1

inner join table2

on table1.column_name =table2.column_name;

或：

select column_name(s)

from table1

join table2

on table1.column_name =table2.column_name;

备注：join和inner join等价的。

（2）left join

left join关键字是从左表（table1）返回所有的行，及时右表（table2）中没有匹配的，而且没有匹配的返回null。

语法：

select column_name(s)

from table1

left join table2

ontable1.column_name=table2.column_name;

或：

select column_name(s)

from table1

left outerjoin table2

ontable1.column_name=table2.column_name;

eg:

（3）right join

right join关键字是从右表（table2）返回所有的行，及时左表（table1）中没有匹配的，而且没有匹配的返回null。这和left join类似的。

语法：

select column_name(s)

from table1

left join table2

ontable1.column_name=table2.column_name;

或：

select column_name(s)

from table1

left outerjoin table2

ontable1.column_name=table2.column_name;

eg:还是上面的表

（4）full outer join

full outer join 关键字只有左表（table1）和右表(table2)其中一个表存在匹配，则返回行。full outer join是left join 和 right join 的结果。

语法：

select column_name(s)

from table1

full outerjoin table2

ontable1.column_name=table2.column_name;

备注：mysql不支持full outer join所以在此就没有例子。

3，union操作符

union操作符合并两个或者多个select语句的结果。

语法：

select column_name(s)   from  table1

union

select column_name(s)   from  table2;

注释：默认的union操作符选取不同的值，如果要显示重复的值，请使用union all.

语法：

select column_name(s)   from  table1

union all

select column_name(s)   from  table2;

4,select into 语句

select into 语句从一个表复制数据，然后把数据插入到另一个新表中。注意：mysql数据库不支持select into …into语句，但是支持：insert into ….select。拷贝表结构：create table 新表 select * from 旧表。

语法：

复制所有的列插入到新表中：

select * into newtable [in externaldb]fromtable1;

或者只复制希望的列插入到新表中

select  column_name(s)  into newtable [in externaldb]from table1;

5，insert into select 语句

insert into select 语句，从一个表复制数据，然后把数据插入到一个已经存在的表中，但是目标表中的已经存在的行不会受到影响。

语法：

从一个表中复制所有的列到另一个已经存在的表：

insert into table2

select * from table1;

复制固定的列到已存在的表中：

insert into table2

select column_name(s) fromtable1;

6,create DB & table

create database dbname被用来创建数据库。

create table 用来创建数据库中的表，表有行和列，而且每个表都必须有表名。

语法：

create table table_name (

      column_name1data_type(size),

      coumn_name2data_type(size),

      …….

);

data_type:参数规定列的数据类型。

size:参数规定表中列最大的长度。

7，MySQL数据类型

Text类型
char(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符
Varchar(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
tinytext	保存最大长度为 255 个字符的字符串。
text	保存最大长度为 65,535 个字符的字符串。
blob	用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。
mediumtext	存放最大长度为 16,777,215 个字符的字符串
mediumblob	用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据
longtext	存放最大长度为 4,294,967,295 个字符的字符串。
longblob	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据
enum(x,y,z,etc…)	允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照您输入的顺序排序的。可以按照此格式输入可能的值： ENUM('X','Y','Z')
set	与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。
Number类型
tinyint(size)	带符号-128到127 ，无符号0到255
smallint(size)	带符号范围-32768到32767，无符号0到65535, size 默认为 6
mediumint(size)	带符号范围-8388608到8388607，无符号的范围是0到16777215。 size 默认为9
int(size)	带符号范围-2147483648到2147483647，无符号的范围是0到4294967295。 size 默认为 11
bigint(size)	带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。size 默认为 20
float(size,d)	带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数
double(size,d)	带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数
decimal(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数
备注：以上的 size 代表的并不是存储在数据库中的具体的长度，如 int(4) 并不是只能存储4个长度的数字。实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。 例如：1、int的值为10 （指定zerofill） int(9)  显示结果：000000010 int(3)  显示结果为010 就是显示的长度不一样而已，都是占用四个字节的空间。
Data类型
date()	日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
datatime	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
timestamp()	*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
time()	时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
year()	2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069
备注：*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

8，sql约束

sql约束用来规定表中的数据规则。如果存在违反约束的数据行为，行为则会被终止。约束可以在创建表的时候规定（通过create table 语句），或者在表创建之后规定(通过alter table 语句)

create table + constraint语法：

create table table_name (

column_name1data_type(size) constraint_name,

      coumn_name2data_type(size) constraint_name,

      …….

);

sql中的约束：

（1）not null

指示某列不能存储 NULL 值。如果不向该字段添加值，就无法插入新纪录或者更新记录。

（2）unique

 保证某列的每行必须有唯一的值。一个表中可以有多个unique修饰的字段。

语法：

创建表的时候：

create table users (

      idint notnull,

      namevarchar(255),

      unique(id)

);

命名unique约束并定义多个列约束。

create table users (

      idint notnull,

      namevarchar(255),

      constraintconstraint_nameunique(id,name)

);

 

表已经创建：

alter table users

add unique (id);

或者命名unique约束，并定义多个列的unique约束，

alter table users

add constraint constraint_nameunique(id,name);

撤销unique约束：

alter table users

drop index constraint_name;

（3）primary key

是not null 和 unique的组合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。而且主键：包含唯一值，不能为null，每个表都应该有一个主键，并且每个表只能有一个主键。

创建表时的语法：

create table users (

      idint notnull,

      namevarchar(255),

      primary key(id)

);

命名primary key 约束，并定义多个列：

create table users (

      idint not null,

      namevarchar(255),

      constraintconstraint_name_pkprimary key (id,name)

);

 

表已经创建时的约束(如果使用以下方式修改表约束，添加的主键必需在创建表的时候，声明为not null的约束)：

alter table users

add primarykey (id);

或者命名primary key约束，并定义多个列的primary key约束，

alter table users

add constraint constraint_name_pkprimary key(id);

撤销primary key约束：

alter table users

drop primarykey;

备注：在创建主键后，可以设置auto-increment字段，这个字段的作用的在每次插入到新纪录的时候，自动的创建主键字段的值。eg:

create table users (

      idint notnullatuo_increment,

      namevarchar(255),

      primary key (id)

);

 

（4）foreign key

 保证一个表中的数据匹配另一个表中的值的参照完整性。表的外键在表操作的时候，非常不方便，所以在此不再详细介绍，因为我们平时关联表的是，都是使用字段进行关联。如果感兴趣自己可以百度了解。

（5）check

 约束用于限制列中值的范围，如果对单个列定义check约束，那么该列只会允许特定的值。如果对于一个表定义的check约束，那么此约束会基于行中其他列的值在特别列中的值进行限制。

创建表时的语法：

create table users (

      idint notnull,

      namevarchar(255),

      check(id > 0)

);

命名check 约束，并定义多个列：

create table users (

      idint notnull,

      namevarchar(255),

      constraintconstraint_name_ckcheck (id > 0 and name=’jym’)

);

 

表已经创建时的约束：

alter table users

add check (id > 0);

或者命名check约束，并定义多个列的primary key约束，

alter table users

add constraint constraint_name_ckcheck(id > 0 and name=’jym’);

撤销check约束：

alter table users

drop constraint_name_ck;

 

（6）default

 default约束用于向列中插入默认值，如果没有规定其他值，那么会将默认值添加到所有的新记录。

创建表时的语法：

create table users (

      idint notnull,

      namevarchar(255)，

      sexvarchar(10) default‘jym’

);

通过使用类似getdate()这样的函数，default约束也可以用于插入系统值。

create table users (

      idint notnull,

      namevarchar(255),

      OrderDatadata defaultgetdate()

);

表已经创建时的约束：

alter table users

add nameset default ‘jym’;

撤销default约束：

alter table users

alter column_name dropdefault;

8，create index创建索引

create index语句用来在表中创建索引，在不读取整个表的情况下，索引使数据库应用程序可以更快的查找数据。而且用户无法看到索引，他们只是用来被快速搜索和查询。

备注：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

create index语法：在表上创建一个简单的索引。允许使用重复的值：

create index index_name

on table_name (column_name)

create unique index语法：在表上创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值

create uniqueindex index_name

on table_name (column_name)

注释：用于创建索引的语法在不同的数据库中不一样。因此，检查您的数据库中创建索引的语法。

eg:

create index UserIdIndex

on users (id)

多个列的索引：

create index UserIdIndex

on users (id，name)

9，drop撤销索引，表和数据库

可以使用drop撤销索引，表和数据库；

删除索引语法：

alter table  table_namedrop index index_name

删除表：

drop table table_namme

删除数据库：

drop database database_name;

仅仅删除表中的数据，但不删除表的本身：

truncate table table_name;

10，alter table 语句

alter table 语句用于在已有的表中添加，删除或修改列。

(1)在表中添加列：

alter table table_name

add column_name datatype

(2)删除表中的列

alter table table_name

drop column column_name;

(3)修改列的数据类型

alter table table_name

modify column_name datatype

11，视图

视图是基于sql语句的结果集的可视化的表。视图包含行和列，就像一个真实的表，视图中的字段就是来自一个或者多个数据库中真实的表中字段。您可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

create view 语法：

create view  view_name AS

select column_name(s)

from table_name

where condition

注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。