Mysql操作语句
MySQL中定义数据字段的类型对你数据库的优化是非常重要的。
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 字节 | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 字节 | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 字节 | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 字节 | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 字节 | (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 字节 | (-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) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 字节 | (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) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
| 类型 | 大小 (字节) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2037 年某时 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255字节 | 定长字符串 |
| VARCHAR | 0-65535 字节 | 变长字符串 |
| TINYBLOB | 0-255字节 | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255字节 | 短文本字符串 |
| BLOB | 0-65 535字节 | 二进制形式的长文本数据 |
| TEXT | 0-65 535字节 | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215字节 | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215字节 | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295字节 | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295字节 | 极大文本数据 |
CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。
Mysql常用操作命令
1.显示数据库
show database;
默认数据库：
mysql - 用户权限相关数据
test - 用于用户测试数据
information_schema - MySQL本身架构相关数据
2.创建数据库
create database test charset utf8 collate utf8_general_ci ; #标明utf8格式,utf8_general_ci 是不区分大小写
#这里
utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。
utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感。
3.使用数据库
use database;
4.用户管理
1 创建用户 2 create user '用户名'@'IP地址' identified by '密码'; 3 删除用户 4 drop user '用户名'@'IP地址'; 5 修改用户 6 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';; 7 修改密码 8 set password for '用户名'@'IP地址' = Password('新密码') 9 10 PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
5.授权管理
1 show grants for '用户'@'IP地址' -- 查看权限 2 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 3 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
all privileges 除grant外的所有权限
select 仅查权限
select,insert 查和插入权限
...
usage 无访问权限
alter 使用alter table
alter routine 使用alter procedure和drop procedure
create 使用create table
create routine 使用create procedure
create temporary tables 使用create temporary tables
create user 使用create user、drop user、rename user和revoke all privileges
create view 使用create view
delete 使用delete
drop 使用drop table
execute 使用call和存储过程
file 使用select into outfile 和 load data infile
grant option 使用grant 和 revoke
index 使用index
insert 使用insert
lock tables 使用lock table
process 使用show full processlist
select 使用select
show databases 使用show databases
show view 使用show view
update 使用update
reload 使用flush
shutdown 使用mysqladmin shutdown(关闭MySQL)
super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
replication client 服务器位置的访问
replication slave 由复制从属使用
在grant这里:
对于权限:
all privileges 除grant外的所有权限
select 仅查权限
select,insert 查和插入权限
对于数据库及内部操作语法有:
A. 数据库名.* 数据库中的所有
B. 数据库名.表 指定数据库中的某张表
C. 数据库名.存储过程 指定数据库中的存储过程
D. *.* 所有数据库
对于用户和IP:
A.用户名@IP地址 用户只能在改IP下才能访问
B.用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意)
C.用户名@% 用户可以再任意IP下访问(默认IP地址为%)
示例:
grant all privileges on db1.tb1 TO '用户名'@'IP'
grant select on db1.* TO '用户名'@'IP'
grant select,insert on *.* TO '用户名'@'IP'
revoke select on db1.tb1 from '用户名'@'IP'
创建表操作:
1.创建表
1 create table 表名(
2 列名 类型 是否可以为空，
3 列名 类型 是否可以为空
4 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table dream (st_id int not null auto_increment,name char(8) not null,age int not null,time date,primary key(st_id));
创建一个以st_id为主键的表
这里st_id设置了auto_increament 属性后,就必须设置为主键primary key(st_id)
如果你想插入一条记录,获得唯一的一个编号,那就要用到主键
auto_increament:可以理解为递增的意思，每增加一条记录，值会自动加1
primary:是主键的意思
下面做了详细的扩展:
A.是否为空 非字符串
not null -不可空
null -可空
B.可以设置默认值
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
)
示例: create table ceshi2( nid int not null default 5,num int not null);
C.自增
自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
create table tb1(
nid int not null auto_increment primary key,
num int null
)
或
create table tb1(
nid int not null auto_increment,
num int null,
index(nid)
)
注意：1、对于自增列，必须是索引（含主键）。
2、对于自增可以设置步长和起始值
show session variables like 'auto_inc%';
set session auto_increment_increment=2;
set session auto_increment_offset=10;
shwo global variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10;
示例:create table ceshi( nid int not null auto_increment,num int not null,index(nid));
D:主键
主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
create table tb1(
nid int not null auto_increment primary key,
num int null
)
或
create table tb1(
nid int not null,
num int not null,
primary key(nid,num)
)
示例:create table ceshi3( nid int not null auto_increment primary key,num int null);
E:外键
外键，一个特殊的索引，只能是指定内容
creat table color(
nid int not null primary key,
name char(16) not null
)
create table fruit(
nid int not null primary key,
smt char(32) null ,
color_id int not null,
constraint fk_cc foreign key (color_id) references color(nid)
)
示例:create table ceshi4( nid tinyint not null primary key,name char(8) not null);
D:常用操作
show create table dream; 查看建表语句
flush privileges; 将数据读取到内存中
drop table 表名; 删除表
delete from 表名; 清空表
desc 表名 查看表结构
select * from user \G; 格式化输出
show grants for zcq@'%'; 查看权限
grant all privileges on *.* to 'jack'@'%' 给已经存在的用户授权
grant all privileges on *.* to 'jack'@'%' identified by '123' ; 创建用户
E:mysql基本数据类型
bit[(M)]
二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-128 ～ 127.
无符号：
0 ～ 255
特别的： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295
特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
decimal[(m[,d])] [unsigned] [zerofill]
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
特别的：对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-3.402823466E+38 to -1.175494351E-38,
0
1.175494351E-38 to 3.402823466E+38
有符号：
0
1.175494351E-38 to 3.402823466E+38
**** 数值越大，越不准确 ****
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
无符号：
-1.7976931348623157E+308 to -2.2250738585072014E-308
0
2.2250738585072014E-308 to 1.7976931348623157E+308
有符号：
0
2.2250738585072014E-308 to 1.7976931348623157E+308
**** 数值越大，越不准确 ****
char (m)
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
PS: 即使数据小于m长度，也会占用m长度
varchar(m)
varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
set
集合类型
A SET column can have a maximum of 64 distinct members.
示例：
CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
YEAR
YYYY（1901/2155）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
F:修改表
添加列：alter table 表名 add 列名 类型
删除列：alter table 表名 drop 列名
修改列：
alter table 表名 modify column 列名 类型; -- 类型
alter table 表名 change 原列名 新列名 类型; -- 列名，类型
添加主键：
alter table 表名 add primary key(列名);
删除主键：
alter table 表名 drop primary key;
alter table 表名 modify 列名 int, drop primary key;
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：alter table 表名 drop foreign key 外键名称
修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
示例:alter table dream change st_id student_id int; 这种写法，直接就把列表名称也改变了. 以及属性
表内容操作
1.增('insert'and'alter')
insert into 表 (列名,列名...) values (值,值,值...)
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)
insert into 表 (列名,列名...) select (列名,列名...) from 表
insert into dream (name,age,time) values ('小明',23,'2016-12-27');
alter table dream add course char(8) not null; 新增一列
insert into dream (name,age,time,course) values ('小王',24,'2016-12-30','Hadoop'); 注意:新增这里如果指定了char，那必须就要加符号,
alter table dream add numbers varchar(8) not null default 100; 新增一列，并且指定了不能为空，填补默认值为100,
2.删
delete from 表
delete from 表 where id＝1 and name＝'alex'
3.改
update 表 set name ＝ 'alex' where id>1
修改表名:alter table dream rename to dreams alter方法修改表名
修改表数据:update 表名 set `字段名`='修改值' where 条件;
切记:如果更新单张表，单行列,必须指定列，不然会把整个表都更新了
这里set name 可以随意指定列表名
4.查
select * from 表
select * from 表 where id > 1
select nid,name,gender as gg from 表 where id > 1
查询语句这里又几种模式:
>查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件
>select命令可以读取一条或多条命令
>你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
>你可以使用 WHERE 语句来包含任何条件
>你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
>你可以使用 LIMIT 属性来设定返回的记录数
>使用主键查询来作为where字句的条件查询是非常快速的.
a.条件查询
select * from 表 where id > 1 and name != 'alex' and num = 12;
select * from 表 where id between 5 and 16;
select * from 表 where id in (11,22,33)
select * from 表 where id not in (11,22,33)
select * from 表 where id in (select nid from 表)
b.通配符
select * from 表 where name like 'zcq%' - zcq开头的所有（多个字符串）
select * from 表 where name like 'zc_' - zc开头的所有（一个字符）
C.限制
select * from 表 limit 5; - 前5行
select * from 表 limit 4,5; - 从第4行开始的5行
select * from 表 limit 5 offset 4 - 从第4行开始的
D.排序
select * from 表 order by 列 asc - 根据 “列” 从小到大排列
select * from 表 order by 列 desc - 根据 “列” 从大到小排列
select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
E.分组
select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表 where nid > 10 group by num,nid order nid desc
select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid
select num from 表 group by num having max(id) > 10
特别的：group by 必须在where之后，order by之前
示例:
select name,count(*) from dream group by name;
分析:---把所有查询到的name信息，以及个数都列入一个* 列表上. *可以更改
select name, sum(age) as singin_out from dream group by name with rollup;
分析:--sum(age) 是收集的信息名称
---查询到nam信息中的age信息, 这里的group by中的with rollup是一个属于汇总的用法(默认为null名称)，如果不加这个方法只会单列出查询到的信息,
还可以修改这个汇总后的名称:select coalesce(name,'翟超群'),sum(age) as age_all from dream group by name with rollup;
F.连表
无对应关系则不显示
select A.num, A.name, B.name
from A,B
Where A.nid = B.nid
无对应关系则不显示
select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid
A表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid
B表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A right join B
on A.nid = B.nid
G,组合
组合，自动处理重合
select nickname
from A
union
select name
from B
组合，不处理重合
select nickname
from A
union all
select name
from B
MySQL NULL 值处理
我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:
IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false 。
MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。
事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
- 事务用来管理insert,update,delete语句
一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
- 1、事务的原子性：一组事务，要么成功；要么撤回。
- 2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
- 3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
- 4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里
在Mysql控制台使用事务来操作
mysql> begin； ＃开始一个事务
mysql> insert into a (a) values(555);
mysql>rollback; 回滚 ， 这样数据是不会写入的
索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件
JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。
LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。
注意：mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.
###
内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。外左联结与外右联结的区别在于如果用A左联结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反，这样也就不会混淆
为什么会存在差异，这和on与where查询顺序有关。
我们知道标准查询关键字执行顺序为 from->where->group by->having->order by[ 记得不是很清楚呢]
left join 是在from范围类所以 先on条件筛选表，然后两表再做left join。
而对于where来说在left join结果再次筛选。
1.Inner join (内连接)
内连接,也叫等值连接,inner join 产生同时符合A和B的一组数据
mysql> select * from student inner join class on student.sid = class.cid;
+-----+--------+--------+----------+----------+-----+--------------+
| sid | sname | gender | class_id | teachers | cid | caption |
+-----+--------+--------+----------+----------+-----+--------------+
| 1 | 钢蛋 | 女 | 1 | 波多 | 1 | 三年二班 |
| 2 | 铁锤 | 女 | 1 | 饭岛 | 2 | 一年三班 |
| 3 | 山炮 | 男 | 2 | 0 | 3 | 三年一班 |
+-----+--------+--------+----------+----------+-----+--------------+
2.Left join
mysql> select student.sname,score.number from student left join score on student.sid = score.sid;
+--------+--------+
| sname | number |
+--------+--------+
| 钢蛋 | 60 |
| 铁锤 | 59 |
| 山炮 | 100 |
+--------+--------+
3 rows in set (0.00 sec)
注释:个人理解left join这里的用法: 就是 select来查询 你需要的值 这里写入的是(student.sname,score.number), 而后面from到左边的表(就是student.sname所在的表),接来下就是left join (以左边为主,来连接另外一个表即:left join score) ,on条件筛选，选择一个共同的值.
3.Right join
同left join方法一样
4.Cross join (笛卡尔交集)
cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积
笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况
mysql> select student.sname,class.caption from student cross join class on student.sid = class.cid;
+--------+--------------+
| sname | caption |
+--------+--------------+
| 钢蛋 | 三年二班 |
| 铁锤 | 一年三班 |
| 山炮 | 三年一班 |
+--------+--------------+
3 rows in set (0.00 sec)
可以联合使用
#实例
一、概念： 数据： data 数据库： DB 数据库管理系统：DBMS 数据库系统：DBS MySQL：数据库 mysql：客户端命令（用来连接服务或发送sql指令） SQL：结构化查询语言 ，其中MySQL支持这个。 SQL语言分为4个部分：DDL、DML、DQL、DCL 二、连接数据库： mysql -h 主机名 -u 用户名 -p密码 库名 C:\>mysql --采用匿名账号和密码登陆本机服务 C:\>mysql -h localhost -u root -proot --采用root账号和root密码登陆本机服务 C:\>mysql -u root -p --推荐方式默认登陆本机 Enter password: **** C:\>mysql -u root -p lnmp61 --直接进入lnmp61数据库的方式登陆 三、授权： 格式：grant 允许操作 on 库名.表名 to 账号@来源 identified by '密码'; --实例：创建zhangsan账号，密码123，授权lnmp库下所有表的增/删/改/查数据,来源地不限 mysql> grant select,insert,update,delete on lnmp61.* to zhangsan@'%' identified by '123'; Query OK, 0 rows affected (0.00 sec) 四、SQL的基本操作 mysql>show databases; --查看当前用户下的所有数据库 mysql>create database 数据库名; --创建数据库 mysql> use test; --选择进入test数据库 mysql> drop database 数据库名; --删除一个数据库 mysql> show tables; --查看当前库下的所有表格 mysql> select database(); --查看当前所在的数据库 mysql> desc tb1; --查看tb1的表结构。 mysql> create table demo( --创建demo表格 -> name varchar(16) not null, -> age int, -> sex enum('w','m') not null default 'm'); Query OK, 0 rows affected (0.05 sec) mysql> desc demo; --查看表结构 +-------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------+------+-----+---------+-------+ | name | varchar(16) | NO | | NULL | | | age | int(11) | YES | | NULL | | | sex | enum('w','m') | NO | | m | | +-------+---------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql>drop table if exists mytab; -- 尝试删除mytab表格 --添加一条数据 mysql> insert into demo(name,age,sex) values('zhangsan',20,'w'); Query OK, 1 row affected (0.00 sec) mysql> insert into demo values('lisi',22,'m'); --不指定字段名来添加数据 Query OK, 1 row affected (0.00 sec) mysql> insert into demo(name,age) values('wangwu',23); --指定部分字段名来添加数据 Query OK, 1 row affected (0.00 sec) --批量添加数据 mysql> insert into demo(name,age,sex) values('aaa',21,'w'),("bbb",22,'m'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from demo; --查询数据 mysql> update demo set age=24 where name='aaa'; --修改 Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> delete from demo where name='bbb'; --删除 Query OK, 1 row affected (0.00 sec) mysql>\h -- 快捷帮助 mysql>\c -- 取消命令输入 mysql>\s -- 查看当前数据库的状态 mysql>\q -- 退出mysql命令行 五、 MySQL数据库的数据类型： MySQL的数据类型分为四大类：数值类型、字串类型、日期类型、NULL。 5.1 数值类型： *tinyint(1字节) smallint(2字节) mediumint(3字节) *int(4字节) bigint(8字节) *float(4字节) float(6,2) *double(8字节) decimal(自定义)字串形数值 5.2 字串类型 普通字串 *char 定长字串 char(8) *varchar 可变字串 varchar(8) 二进制类型 tinyblob blob mediumblob longblob 文本类型 tinytext *text 常用于<textarea></textarea> mediumtext longtext *enum枚举 set集合 5.3 时间和日期类型： date 年月日 time 时分秒 datatime 年月日时分秒 timestamp 时间戳 year 年 5.4 NULL值 NULL意味着“没有值”或“未知值” 可以测试某个值是否为NULL 不能对NULL值进行算术计算 对NULL值进行算术运算，其结果还是NULL 0或NULL都意味着假，其余值都意味着真 MySQL的运算符： 算术运算符：+ - * / % 比较运算符：= > < >= <= <> != 数据库特有的比较：in，not in, is null,is not null,like, between and 逻辑运算符：and or not 六、 表的字段约束： unsigned 无符号(正数) zerofill 前导零填充 auto_increment 自增 default 默认值 not null 非空 PRIMARY KEY 主键 （非null并不重复） unique 唯一性 （可以为null但不重复） index 常规索引 七: 建表语句格式： create table 表名( 字段名 类型 [字段约束], 字段名 类型 [字段约束], 字段名 类型 [字段约束], ... ); mysql> create table stu( -> id int unsigned not null auto_increment primary key, -> name varchar(8) not null unique, -> age tinyint unsigned, -> sex enum('m','w') not null default 'm', -> classid char(6) -> ); Query OK, 0 rows affected (0.05 sec) mysql> desc stu; +---------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(8) | NO | UNI | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | sex | enum('m','w') | NO | | m | | | classid | char(6) | YES | | NULL | | +---------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> show create table stu\G --查看建表的语句 *************************** 1. row *************************** Table: stu Create Table: CREATE TABLE `stu` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(8) NOT NULL, `age` tinyint(3) unsigned default NULL, `sex` enum('m','w') NOT NULL default 'm', `classid` char(6) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> mysql> insert into stu(id,name,age,sex,classid) values(1,'zhangsan',20,'m','lnmp 61'); Query OK, 1 row affected (0.00 sec) mysql> insert into stu(name,age,sex,classid) values('lisi',22,'w','lnmp61'); Query OK, 1 row affected (0.00 sec) mysql> insert into stu(name,age,classid) values('wangwu',21,'lnmp61'); Query OK, 1 row affected (0.00 sec) mysql> insert into stu values(null,'qq',24,'w','lnmp62'); Query OK, 1 row affected (0.00 sec) mysql> insert into stu values(null,'aa',20,'m','lamp62'),(null,'bb',25,'m','lnmp 63'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from stu; +----+----------+------+-----+---------+ | id | name | age | sex | classid | +----+----------+------+-----+---------+ | 1 | zhangsan | 20 | m | lnmp61 | | 2 | lisi | 22 | w | lnmp61 | | 3 | wangwu | 21 | m | lnmp61 | | 4 | qq | 24 | w | lnmp62 | | 5 | aa | 20 | m | lnmp62 | | 6 | bb | 25 | m | lnmp63 | +----+----------+------+-----+---------+ 6 rows in set (0.00 sec)
-- 多表查询：
-- 1. 嵌套方式的多表查询
-- 2. where关联查询
-- 3. 左联和右联，内联的查询。
=================================================
-- 已知条件：
mysql> select * from stu;
+----+------------+-----+------+---------+
| id | name | sex | age | classid |
+----+------------+-----+------+---------+
| 1 | zhangsan | m | 20 | lnmp01 |
| 2 | lisi | w | 21 | lnmp02 |
| 3 | wangwu | m | 25 | lnmp01 |
| 4 | zhaoliu | w | 29 | lnmp02 |
| 5 | qq01 | w | 28 | lnmp01 |
| 6 | qq02 | m | 30 | lnmp02 |
| 7 | qq03 | w | 31 | lnmp03 |
| 8 | xiaowang | m | 18 | lnmp04 |
| 9 | xiaoli | w | 19 | lnmp03 |
| 10 | xiaobai | m | 22 | lnmp02 |
| 11 | xiaosun | w | 24 | lnmp01 |
| 12 | xiaozhang2 | w | 36 | lnmp05 |
+----+------------+-----+------+---------+
12 rows in set (0.05 sec)
mysql> select * from grade;
+----+-----+------+-------+
| id | sid | php | mysql |
+----+-----+------+-------+
| 1 | 4 | 80 | 92 |
| 2 | 2 | 78 | 80 |
| 3 | 1 | 99 | 95 |
| 4 | 6 | 58 | 62 |
| 5 | 7 | 89 | 98 |
| 6 | 3 | 68 | 54 |
+----+-----+------+-------+
6 rows in set (0.00 sec)
mysql>
-- 一、嵌套查询：一个查询结果是另外一个查询的条件。
-- 例如在学生表中获取年龄最大的信息
mysql> select max(age) from stu;
+----------+
| max(age) |
+----------+
| 36 |
+----------+
1 row in set (0.00 sec)
mysql> select * from stu where age=(select max(age) from stu);
+----+------------+-----+------+---------+
| id | name | sex | age | classid |
+----+------------+-----+------+---------+
| 12 | xiaozhang2 | w | 36 | lnmp05 |
+----+------------+-----+------+---------+
1 row in set (0.00 sec)
mysql>
-- 获取php考试成绩最好的是谁？
mysql> select max(php) from grade; --获取php最高成绩
+----------+
| max(php) |
+----------+
| 99 |
+----------+
1 row in set (0.11 sec)
mysql> select sid from grade where php=99; --获取php成绩在99分的sid学号信息
+-----+
| sid |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> select * from stu where id=1; --获取学号为1的学生信息
+----+----------+-----+------+---------+
| id | name | sex | age | classid |
+----+----------+-----+------+---------+
| 1 | zhangsan | m | 20 | lnmp01 |
+----+----------+-----+------+---------+
1 row in set (0.00 sec)
mysql> select * from stu where id=(select sid from grade where php=99);
mysql> select * from stu where id in(select sid from grade where php=99);
+----+----------+-----+------+---------+
| id | name | sex | age | classid |
+----+----------+-----+------+---------+
| 1 | zhangsan | m | 20 | lnmp01 |
+----+----------+-----+------+---------+
1 row in set (0.19 sec)
-- 三层嵌套查询，解决考试php成绩最好的信息
mysql> select * from stu where id in(select sid from grade where php=(select max(p
hp) from grade));
+----+----------+-----+------+---------+
| id | name | sex | age | classid |
+----+----------+-----+------+---------+
| 1 | zhangsan | m | 20 | lnmp01 |
+----+----------+-----+------+---------+
1 row in set (0.00 sec)
-- 二、 where关联查询
-- 查询stu表与grade表的关联查询
mysql> select * from stu,grade where stu.id=grade.sid;
+----+----------+-----+------+---------+----+-----+------+-------+
| id | name | sex | age | classid | id | sid | php | mysql |
+----+----------+-----+------+---------+----+-----+------+-------+
| 4 | zhaoliu | w | 29 | lnmp02 | 1 | 4 | 80 | 92 |
| 2 | lisi | w | 21 | lnmp02 | 2 | 2 | 78 | 80 |
| 1 | zhangsan | m | 20 | lnmp01 | 3 | 1 | 99 | 95 |
| 6 | qq02 | m | 30 | lnmp02 | 4 | 6 | 58 | 62 |
| 7 | qq03 | w | 31 | lnmp03 | 5 | 7 | 89 | 98 |
| 3 | wangwu | m | 25 | lnmp01 | 6 | 3 | 68 | 54 |
+----+----------+-----+------+---------+----+-----+------+-------+
6 rows in set (0.13 sec)
-- 查询stu表与grade表的关联查询，只显示部分字段
mysql> select s.id,s.name,g.php,g.mysql from stu s,grade g
-> where s.id=g.sid;
+----+----------+------+-------+
| id | name | php | mysql |
+----+----------+------+-------+
| 4 | zhaoliu | 80 | 92 |
| 2 | lisi | 78 | 80 |
| 1 | zhangsan | 99 | 95 |
| 6 | qq02 | 58 | 62 |
| 7 | qq03 | 89 | 98 |
| 3 | wangwu | 68 | 54 |
+----+----------+------+-------+
6 rows in set (0.03 sec)
-- 查询lnmp01班的学生考试信息，显示：学号、姓名、班级、php和MySQL字段。
mysql> select s.id,s.name,s.classid,g.php,g.mysql from stu s,grade g
-> where s.id=g.sid and s.classid='lamp01';
+----+----------+---------+------+-------+
| id | name | classid | php | mysql |
+----+----------+---------+------+-------+
| 1 | zhangsan | lnmp01 | 99 | 95 |
| 3 | wangwu | lnmp01 | 68 | 54 |
+----+----------+---------+------+-------+
2 rows in set (0.00 sec)
-- 统计每个班的考试人数和平均成绩：显示：班级，人数和平均成绩
mysql> select s.classid,count(*) num,avg(g.php) php ,avg(g.mysql) mysql
-> from stu s,grade g
-> where s.id=g.sid group by s.classid;
+---------+-----+---------+---------+
| classid | num | php | mysql |
+---------+-----+---------+---------+
| lnmp01 | 2 | 83.5000 | 74.5000 |
| lnmp02 | 3 | 72.0000 | 78.0000 |
| lnmp03 | 1 | 89.0000 | 98.0000 |
+---------+-----+---------+---------+
3 rows in set (0.00 sec)
-- 查询姓名中含有ang的考试信息
mysql> select s.id,s.name,g.php,g.mysql from stu s,grade g
-> where s.id=g.sid and s.name like '%ang%';
+----+----------+------+-------+
| id | name | php | mysql |
+----+----------+------+-------+
| 1 | zhangsan | 99 | 95 |
| 3 | wangwu | 68 | 54 |
+----+----------+------+-------+
2 rows in set (0.00 sec)
--三、 左联和右联、内联（等价于where） 查询
------------------------------------------------------
--查询所有学生的考试信息（包含没有考试的）
-- 采用左联查询（以左边为主，右边没有的对应数据补null）
mysql> select s.id,s.name,g.php,g.mysql from stu s left join grade g
-> on s.id=g.sid;
+----+------------+------+-------+
| id | name | php | mysql |
+----+------------+------+-------+
| 2 | lisi | 78 | 80 |
| 5 | qq01 | NULL | NULL |
| 6 | qq02 | 58 | 62 |
| 7 | qq03 | 89 | 98 |
| 3 | wangwu | 68 | 54 |
| 10 | xiaobai | NULL | NULL |
| 9 | xiaoli | NULL | NULL |
| 11 | xiaosun | NULL | NULL |
| 8 | xiaowang | NULL | NULL |
| 12 | xiaozhang2 | NULL | NULL |
| 1 | zhangsan | 99 | 95 |
| 4 | zhaoliu | 80 | 92 |
+----+------------+------+-------+
12 rows in set (0.00 sec)
-- 采用右联查询（以右边为主，左边没有的对应数据补null）
mysql> select s.id,s.name,g.php,g.mysql from grade g right join stu s
-> on s.id=g.sid;
+----+------------+------+-------+
| id | name | php | mysql |
+----+------------+------+-------+
| 2 | lisi | 78 | 80 |
| 5 | qq01 | NULL | NULL |
| 6 | qq02 | 58 | 62 |
| 7 | qq03 | 89 | 98 |
| 3 | wangwu | 68 | 54 |
| 10 | xiaobai | NULL | NULL |
| 9 | xiaoli | NULL | NULL |
| 11 | xiaosun | NULL | NULL |
| 8 | xiaowang | NULL | NULL |
| 12 | xiaozhang2 | NULL | NULL |
| 1 | zhangsan | 99 | 95 |
| 4 | zhaoliu | 80 | 92 |
+----+------------+------+-------+
12 rows in set (0.00 sec)
-- 内联查询，就是两边表都存在的。
mysql> select s.id,s.name,g.php,g.mysql from grade g inner join stu s
-> on s.id=g.sid;
+----+----------+------+-------+
| id | name | php | mysql |
+----+----------+------+-------+
| 4 | zhaoliu | 80 | 92 |
| 2 | lisi | 78 | 80 |
| 1 | zhangsan | 99 | 95 |
| 6 | qq02 | 58 | 62 |
| 7 | qq03 | 89 | 98 |
| 3 | wangwu | 68 | 54 |
+----+----------+------+-------+
6 rows in set (0.00 sec)
mysql>
-- 查询lnmp01班的学生考试信息
mysql> select s.id,s.name,s.classid,g.php,g.mysql
-> from stu s left join grade g
-> on s.id=g.sid
-> where s.classid='lnmp01';
+----+----------+---------+------+-------+
| id | name | classid | php | mysql |
+----+----------+---------+------+-------+
| 1 | zhangsan | lnmp01 | 99 | 95 |
| 3 | wangwu | lnmp01 | 68 | 54 |
| 5 | qq01 | lnmp01 | NULL | NULL |
| 11 | xiaosun | lnmp01 | NULL | NULL |
+----+----------+---------+------+-------+
4 rows in set (0.00 sec)
mysql>
