1.1.基本sql语句

1.创建数据库

create database 数据库名 character set gb2312

collate gb2312_chinese_ci;

2.修改数据库编码方式

alter database 数据库名 character set utf8 collate utf8_bin;

3.查看数据库服务器中存在的数据库

show databases;

4.查看指定数据库

show create database 数据库名；

5.删除数据库

drop database 数据库名；

6.查看MySQL服务器系统支持的存储引擎

show engines;

7.查看mysql服务器系统支持的默认存储引擎

show variables like 'stopage_engine';

8.数据结构：

整数类型：

TINYINT、SMALLINT、MEDIUMINT、INTEGER、BIGINT

浮点类型：

float，double

定点数类型是DECIMAL(精确值)

浮点与定点类型基本格式：

数据类型（M，D） M是数据的总长度，D指小数点后面的长度

时间与日期类型：

YEAR类型表示年份、DATE类型表示日期、TIME类型表示时间、DATETIME和TIMESTAMP表示日期时间

字符串类型：

CHAR、VARCHAR、BLOB、TEXT、ENUM、SET

CHAR类型占用的存储空间是固定，而VARCHAR类型存放可变长度的字符串。

CHAR(M)或VARCHAR(M)

ENUM类型称为枚举类型，又称为单选字符串类型：

属性名 ENUM（’值1’， ’值2’，...，’值n’）

TEXT类型：

TEXT类型用于存储大文本数据，不能有默认值

SET类型：

lSET类型又称为集合类型，它的值可以有零个或多个

属性名 SET（’值1’， ’值2’，...，’值n’）

二进制类型:

当数据库中需要存储图片、声音等多媒体数据时，使用二进制类型

BINARY(M) VARBINARY(M) BIT(M) TINYBLOB(M) BLOG(M) MEDIUMBLOB(M) LONGBLOG(M)

9.数据表

创建表语法

###### NULL（NOT NULL）：表示字段是否可以为空；

###### DEFUALT：指定字段的默认值；

###### AUTO_INCREMENT：设置字段为自增，只有整型类型的字段才能设置自增。自增默认从1开始，每个表只能有一个自增字段；

###### UNIQUE KEY：唯一性约束；

###### PRIMARY KEY：主键约束；

###### COMMENT：注释字段；

###### 外键定义：外键约束

查看数据表：

show create tables;

desc tables(describe tables);

查看表结构：

describe 表名;

修改表名：

alter table users rename user;

修改字段名:

alter table 表名 change 旧字段名 新字段名 新数据类型；

alter table user change uPwd uPswd varchar(20);

修改字段类型:

alter table 表名 MODIFY 字段名 新数据类型;

alter table user modify uPswd varbinary(20);

修改字段的排列位置:

alter table 表名 modify 字段名 1 数据类型 first|after 字段名2；

alter table user modify uPswd varibinary(20) after uSex;

添加字段：

after table 表名 add 字段名 数据类型

[完整性约束条件] [first| after 已存在的字段名];

alter table user add uRegTime timestamp;

删除字段：

ALTER TABLE 表名 DROP 字段名;

修改表的搜索引擎：

ALTER TABLE 表名 ENGINE=存储引擎名;

复制表结构及数据到新表：

CREATE TABLE 新表名 SELECT * FROM 源表名;

只复制表结构到新表:

CREATE TABLE 新表名 SELECT * FROM 源表名 WHERE FALSE ;

/CREATE TABLE新表名LIKE源表名;

复制表的部分字段和数据到新表：

CREATE TABLE 新表名 AS(SELECT 字段1,字段2,...... FROM 源表名);

CREATE TABLE newUser AS (SELECT uName,uPswd FROM user);

删除表：

DROP TABLE 表名;

定义复合主键:

PRIMARY KEY(字段名1，字段名2，…，字段名n)

##### CREATE TABLE SCarInfo 

##### ( gdID INT,uID INT,scNum INT,

#####   PRIMARY KEY(gdID,uID)     -- 定义复合主键

##### );

UNIQUE(唯一性)约束：

##### CREATE TABLE users (

#####  uID int(11) PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',

#####  uName varchar(30) NOT NULL UNIQUE,

#####  uPwd varchar(30) NOT NULL,

#####  uSex ENUM('男','女') DEFAULT '男'

##### );

foreign key(外键约束):

#### CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);

####     CREATE TABLE GoodsType

##### 主表:( tID INT PRIMARY KEY

#####   tName VARCHAR(30) NOT NULL

##### );        设置tid为外键约束 

##### 从表：CREATE TABLE Goods

##### ( gdID INT PRIMARY KEY AUTO_INCREMENT,  -- 标识该字段为主键且自增

#####  tID INT NOT NULL,

#####  gdCode VARCHAR(30) NOT NULL UNIQUE,  

#####  gdName VARCHAR(30) NOT NULL,

#####  gdPrice DECIMAL(8,2),

#####  CONSTRAINT FK_tID FOREIGN KEY (tID) REFERENCES GoodsType (tID)

##### );

外键约束级联更新级联删除：

##### CONSTRAINT 外键名 FOREIGN KEY(外键字段名)

#####        REFERENCES 主表名(主键字段名)

##### [ON UPDATE { CASCADE | SET NULL | NO ACTION | RESTRICT }]

##### [ON DELETE { CASCADE | SET NULL | NO ACTION | RESTRICT }]

###### CASCADE：指定在更新和删除操作表中记录时，如果该值被其他表引用，则级联更新或删除从表中相应的记录。

###### SET NULL：更新和删除操作表记录时，从表中相关记录对应的值设置为NULL。

###### NO ACTION：不进行任何操作

###### RESTRICT：拒绝主表更新或修改外键的关联列。



###### CREATE TABLE Goods

###### ( gdID INT PRIMARY KEY AUTO_INCREMENT,  -- 标识该字段为主键且自增

######   tID INT NOT NULL,gdCode VARCHAR(30) NOT NULL UNIQUE,  

######   gdName VARCHAR(30) NOT NULL,gdPrice DECIMAL(8,2),

######  CONSTRAINT FK_tID FOREIGN KEY (tID) REFERENCES GoodsType (tID) 

######  ON UPDATE CASCADE ON DELETE CASCADE

###### );

插入语句:

insert into goodstype value(1,'学习用品');

替换语句:

replace into user(uID,uNAME,uPswd) values(3,'张三','011');

多行插入:

insert into user(uID,uNAME,uPswd) values('郑霞','女','fsdf'),(‘李海’,'女','123')

,('王丹','女','hjhk');

插入其他表的数据:

insert into users(uNAME,uSEX,uPswd) select uNAME,uSEX,uPswd from user where id>5;

修改数据update：

update useR set uPswd ='888' where uNAME='王丹';

delete删除数据:

delete from user where uID=5;

delete from user;//删除全部

truncate语句无条件删除语句:

truncate user1；

10.select语句：

一.选择列

1.查询所有列

select *from 表名；

2.查询指定列

select gdcode,gdname,gdprice,gdsaleqty from goods;

3.查询指定列(自定义顺序)

select gdcode,gdname,gdprice,gdsaleqty,gdhot,gdcity from goods;

4.计算列值

select gdname,gdsaleqty*gdprice from goods;
获取用户年龄：
select uname 用户名,year(now())-year(ubirth) 年龄 from users;

5.为查询表中的列指定标题(as可省略)

select gdname as 商品名称,gdprice as 价格, gdcity 城市 from goods;

二.选择行

select uname 用户名 from users where uid=8;
select uname,uphone,ubirth from users where ubirth>='2000-1-1';

逻辑运算符

and,or ,not
select uname,uphone,ubirth from users where ubirth>='2000-1-1' and usex='男';
select gdname,gdprice from goods where not(gdprice>50);
select gdname,gdprice,gdcity from goods where (gdcity='长沙' or gdcity='西安') and gdprice<=50;

between-and运算符

select gdname from goods where gdprice (not) between 100 and 500;

in运算符：(指定条件范围)

select gdname,gdcity from goods where gdcity （not）in('长沙','西安','上海');

Like运算符

select uname,usex,uphone from users where uname like '李%';
select uname,usex,uphone from users where uname like '_湘%';

默认转义字符：“\” ；escape自定义转义字符

select gdcode,gdname,gdprice from goods where gdname like '华为P9_%';
select gdcode,gdname,gdprice from goods where gdname like '华为P9|%' escape'|';
select gdcode,gdname,gdprice from goods where gdname like '华为P9/%' escape'/';
select gdcode,gdname,gdprice from goods where gdname like 一.选择列
1.查询所有列
select*from 表名；

2.查询指定列
select gdcode,gdname,gdprice,gdsaleqty from goods;
3.查询指定列(自定义顺序)
select gdcode,gdname,gdprice,gdsaleqty,gdhot,gdcity from goods;
4.计算列值
select gdname,gdsaleqty*gdprice from goods;
获取用户年龄：
select uname 用户名,year(now())-year(ubirth) 年龄 from users;
5.为查询表中的列指定标题(as可省略)
select gdname as 商品名称,gdprice as 价格, gdcity 城市 from goods;
二.选择行
select uname 用户名 from users where uid=8;
select uname,uphone,ubirth from users where ubirth>='2000-1-1';
逻辑运算符
and,or ,not
select uname,uphone,ubirth from users where ubirth>='2000-1-1' and usex='男';
select gdname,gdprice from goods where not(gdprice>50);
select gdname,gdprice,gdcity from goods where (gdcity='长沙' or gdcity='西安') and gdprice<=50;
between-and运算符
select gdname from goods where gdprice (not) between 100 and 500;
in运算符：(指定条件范围)
select gdname,gdcity from goods where gdcity （not）in('长沙','西安','上海');
Like运算符
select uname,usex,uphone from users where uname like '李%';
select uname,usex,uphone from users where uname like '湘%';
默认转义字符：“\” ；escape自定义转义字符
select gdcode,gdname,gdprice from goods where gdname like '华为P9_%';
select gdcode,gdname,gdprice from goods where gdname like '华为P9|%' escape'|';
select gdcode,gdname,gdprice from goods where gdname like '华为P9/%' escape'/';
select gdcode,gdname,gdprice from goods where gdname like '华为P9?%' escape'?';

正则表达式：

以5结尾

select uname,usex,uphone from users where uphone regexp '5$';
select uname,usex,uphone from users where uphone regexp '^1[678]';
is null 运算符(判断是否为空)
select uname,usex from users where uimage is (not) null;

distinct消除重复结果集

select gdcity,gdname from goods where gdprice<200;
select distinct gdcity from goods where gdprice<200;

排序：（默认是按表中的数据）order by(desc 降序，asc 升序)

select gdcode,gdname,gdprice from goods where tid=1 order by gdprice asc;
多个排序规则
select gdcode,gdname,gdprice,gdsaleqty from goods where tid=1 order by gdsaleqty desc,gdprice;

limit：

select gdcode,gdname,gdprice,gdsaleqty from goods limit 2;

数据分组统计：分组：group by

聚合函数

sum 返回组中所有值的和 avg 返回组中各值的平均值

max 返回组中的最大值 min 返回组中的最小值

count 返回组中的项数

group_concat 返回一个字符串结果，该结果由分组中的值连接组合而成

1.sum/avg/max/min

sum/avg/max/min ([all | distinct] 列名|常量|表达式)

select max(gdprice) from goods;

select sum(gdsaleqty) from goods;

2.count函数(返回组中的项数)

count ({【【all | distinct】列名|常量|表达式】| *})

distinct指定count返回唯一非空值的数量(消除重复集)；* 指定应该计算所有行并返回表中行的总数

统计用户总人数

select count(*) from users;

查询orders表，显示购买过商品的用户人数：

select count()

3.group by

group by 【ALL】 列名1，列名2 【,......n】 【with rollup】 【having 条件表达式】

注：

all显示所有组，为默认值。

列名为分组依据列，不能使用在select列表中定义的别名来指定组合列

使用withrollup关键字指定的结果集不仅包含由group by提供的行，同时还包含汇总行

having用来指定分组后的数据集进行过滤

//根据城市分组

select uid,uname,usex,ucity from users group by ucity;

//统计各城市的用户人数

select ucity,count(*) from users group by ucity;

4.group_contact( 返回一个字符串结果，该结果由分组中的值连接组合而成)

group by和group_concat一起使用：
select ucity,group_concat(uname) as unames from users group by ucity;
select ucity,group_concat(uname) as unames from users group by ucity;
select ucity,count() from users group by ucity having count()>=3;
select ucity,count() from users group by ucity having ucity='上海';
select ucity,count() from users where ucity='上海' group by ucity;

5.连接查询：

内连接：join

select tname,gdcode,gdname,gdprice from goodstype g join goods a on g.tid=a.tid where tname='服饰';
select uname,sum(ototal) from users u join orders o on u.uid=o.uid where uname='段湘林';

三表查询：

select gdname,gdprice,scnum from user u join scar s on u.uid=s.uid join goods g on g.gdid=s.gdid where uname='段湘林';

自连接：

select s.uname,s.uphone,s.ucity from users s join users t on s.ucity=t.ucity where t.uname='蔡准';
（子查询）select uname,uphone from users where ucity=(select ucity from users where uname='蔡准');

外连接：

左连接：

select s.uid,uname,ototal from users s left join orders t on s.uid=t.uid;

右连接：

select s.uid,uname,count(t.uid) from orders t right join users s on s.uid=t.uid group by s.uid;

交叉连接：

select uid,uname,gdid,gdname from users cross join goods;

多表连接：

select uid,uname,usex from user where uid=1 union select uid,uname,usex from user where uid=2;
select tid,gdname,gdprice from goods where tid=1 union select tid,gdname,gdprice from goods where tid=2 order by gdprice desc limit 3;

6.子查询

子查询：

select gdid,gdname,gdprice,gdsaleqty from goods where tid=(select tid from goodstype where tname='服饰');

子查询用作表达式：

1.使用比较运算符的子查询：

select devalution,odtime from orderdetail where gdid=(select gdid from goods where gdname='LED小台灯');
select uname,uphone from users where ucity=(select ucity from users where uname='蔡准');
select gdcode,gdname,gdprice,gdsaleqty from goods where gdsaleqty<(select gdsaleqty from goods where gdname='LED小台灯');

in

查询已购物的会员信息，包括用户名，性别，年龄和注册时间

select uname,usex,ubirth,uregtime from users where uid in (selec uid from orders);

any/some

查询比服饰类某一商品价格高的商品信息，包括商品编号，商品名称和商品价格

select gdcode,gdname,gdprice from goods where gdprice>any (select gdprice from goods where (tid=(select tid from goodstype where tname='服饰')));

all

查询价格比服饰类商品都高的商品信息，包括商品编号，商品名称和商品价格

select gdcode,gdname,gdprice from goods where gdprice>all (select gdprice from goods where (tid=(select tid from goodstype where tname='服饰')));

子查询用作派生表(子查询的嘉国际作为查询的数据源)

查询年龄了在20-30之间的用户名，性别和年龄

select*from (select uname,usex,year(now())-year(ubirth) from users) as temptb where uage between 20 and 30;

相关子查询

1.exists

查询已购物的会员信息，用户名，性别，出生年月和注册时间

select uname,usex,ubirth,uregtime from users where exists(select*from orders where users.uid=orders.uid);

2.计算相关子查询

相关子查询还尅嵌套在select子句的目标列中，通过子查询计算出关联数据的目标列

插询2016年11月17日评价了商户的用户，列出用户名，被评价的商品名和评价时间

select (select uname from users where uid=(select uid from orders where oeders.oid=od.oid)) as '用户名',(select gdname from goods where goods.gdid=odid) as '商品名',
odtime as '评价时间' from orderdetail as od where date_format(odtime,'%Y-%m-%d')='2016-11-07';

子查询用于更新数据

1.查询结果集作为插入的数据源

创建商品历史表，并将库存量小于10且上架时间超过一年的商品下架处理，并将这些商品添加到goodshistory

----创家居goodshistory，复制goods表结构--------
create table goodshistory like goods;
-----将满足条件的商品插入到goodshistory表中------
insert into goodshistory select*from goods where gdquantity<10 and year(now())-year(gdaddtime)>=1;

2.子查询用于修给数据

统计订单详情表中的评价书，将商品销售量为200以上，且评价大于1的商品设置为热销商品(gdhot属性值改为1)

update goods set gdhot =1 where gdsaleqty>=200;

3.子查询用于删除数据

将已下架的商品从商品表中删除，其中下架的商品是指已将存放在历史表中的商品

select from goods where gdcode in (select gdcode from goodshistory);

11.索引.视图

1.索引

3.查看索引语句

1.show create table newgoods;
2.show index from newgoods;
show keys from newgoods;

2.删除索引：

1.alter table newgoods drop index ix_gdcode;
2.drop index ix_fullixord on orderdetail;(表名)

2.视图：

1.创建视图:

创建名为view_ugd的视图，用来显示用户购买的商品信息，包括用户名，商品名称，购买数量以及商品价格

create view view_gud(用户名,商品名称,购买数量,商品价格) as select a.uname,gdname,b.scnum,c.gdprice
from users a join scar b on a.uid=b.uid join goods c on c.gdid=b.gdid;
create view view_users as select uname,upwd,usex,ubirth,uphone from users;

管理和维护视图

查看视图：

视图属性：
show table status like 'view_users';
视图结构：
desc view_users;
视图语句：
show create view view_gud;

修改视图：

create or replace view view_users(姓名,电话) as select uname,uphone from users;
alter view view_users(姓名,电话) as select uname,uphone from users;

删除视图：

drop view if exists 视图名 【resteict|cascade】//restrict:表示不能级联删除 cascade：表示级联删除

更新视图：

update view_users set 电话='154622488200' where 姓名='蔡准';

插入数据：

insert into viewusers values('周鹏','123','男','123','1999-09-1');
delete from viewusers where uname='周鹏';