mysql命令练习

---数据库操作

create database if not exists 数据库名; //创建数据库1

create database if not exists 数据库名 //再次创建数据库

show databases //显示所有数据库

use 数据库名;   //进入数据库

drop database if exists 数据库名

---表操作

//创建表

//1.primary key 主键 unique 唯一键(不能出现重复值) unsigned 未签名 comment 评论解释 primary 主要的 increment增量 

//2.创建索引的3种方法:1.创建了主键就会自动的创建主键索引2.创建唯一键就会创建唯一索引3.普通索引

create table data(

id int unsigned not null   auto_increment  primary key comment '主键',

name varchar(20)  not null unique comment '姓名',

`add` varchar(50) not null default '地址不详' comment '地址',

score int comment '成绩,可以为空'

)engine=MyISAM

//组合主键和唯一键后定义

//唯一键不支持2个字段,需要一个一个写

create table data1(

id int unsigned,

name varchar(20),

`add` varchar(50),

score int,

test int,

primary key(id,name),

unique (test),

unique (score)

)engine=MyISAM

describe 表名

//创建表时候创建索引

create table data2(

name varchar(20),

score int unsigned unique ,

index ix_name(name),

index  ix_score(score)

)engine=MyISAM

describe 表名

//创建表后创建索引,不设置引擎

create table data3(

score int

)

create index 索引名 on 表名 (字段名 [desc])

alter table 表名 add 字段名 字段属性

alter table 表名 add index 索引名(字段名)

describe 表名

show index from 表名 --查询索引名

drop index 索引名 on 表名 --删除索引

---查询

show databases //显示所有数据库

show tables;  //显示表

show create table 表名  //显示表的所有创建命令和引擎源码

show table status like '表名';  // 查看表信息(引擎,编码,创建时间等) status-状态,地位,身份,这个like后必须加引号

describe 表名;  //查看表的所有列和列类型

desc 表名 'n%';  //查看表列名字以n开头的列

show status  // 查看MySQL服务器状态,当前连接数,运行时间,语句执行次数等 status-状态,地位

show full processlist  //查看MySQL当前用户连接进程状态 full-完整 process list-进程列表

show variables   --查看系统变量及其值

--复制表

insert into 目标表（字段1，字段2，....） select 字段1，字段2，... from 源表; //复制表的部分数据,注意,前面有括号,后面没有

create table 新表 select 字段 from 旧表 //复制新表方法1 可复制结构和数据,但不能复制主键,唯一键,引擎

create table 新表 like 旧表  //只能复制表结构(包含主键)，不能复制表数据,和可视化手动复制一样

select * from 表名  //查看表里有没有数据

describe 表名; //查看表的所有列和列类型

show table status like '表名';  //查看表信息

---删除和清空表

drop table if exists 表名; //删除data3表

drop table if exists 表名1,表名2; //同时删除data3 data4表

show tables //查看数据库中所有表

---插入数据

insert into 表名 values(0,'rose','女',80);  // 插入数据到每个字段,字段名可省略

insert into 表名 values (0,'李白','男','四川'),(0,'杜甫','男','湖北'); //插入多个字段

insert into 表名 (name,`add`,score) values ('tom',default,50);  //插入指定字段,值要一一对应

insert into 表名 select * from 表名 where 条件;  //不用测试,插入某一个表的数据进来,如果有唯一值会报错

alter table 表名 auto_increment=10  //让自动增长列从100开始 increment-增量

//变量范围：offset和increment可以在全局以及session级别设置这2个变量

//数据库引擎上innodb的，它不支持设置和修改初始值,只有MyISAM

set  @@auto_increment_offset=20;   //让自动增长从100开始,全局,对整个数据库生效 offset-开端出发 

set  @@auto_increment_increment=5;   //让自动增长列每次id增加为10 全局,会对整个数据库生效

show variables   --查看系统变量及其值

---修改表(添加修改字段)

describe 表名;

alter table 表名 add 新字段 字段属性;   //给data表添加add1字段,默认在最后

alter table 表名 add 新字段 字段属性 after name;  -- 在name之后添加sex字段

alter table 表名 add 新字段 字段属性 first;  -- 添加age字段并放在最前面

alter table 表名  add unique(字段1名),add unique(字段名2);  //设置字段1字段2都为唯一键

alter table 表名 change 旧字段 新字段 字段属性 zerofill default '缺考'; //修改data表中的sex字段数据类型并设置默认值未知 zerofill 补充零

alter table 表名 modify 字段名 字段属性 default '性别不详'; //将字段的数据类型更改且默认值是‘地址不详’modify-修改

alter table 表名 drop `add1`;   -- 删除add1字段

alter table 表名 engine=innodb;  //修改表data引擎为innodb

show table status //查看引擎和行数,和创建时间等

alter table 表名 engine=myisam;  //再把引擎改回来myisam

alter table 旧表名 rename to 新表名;  //修改表明为datanew

---更新数据

update 表名 set 字段1=值,字段2=值 where id=1;

update 表名 set 字段1=值 where 字段 is null; //替换字段为null的值

---删除数据

create table 新表 select 字段 from 旧表 //先复制一个表,含数据

delete from 表名 where id in (1,2);  //删除id为1的表

truncate table  表名  //清空表数据,删除data2表所有数据并创建同结构的data2表,用truncate删除后,AUTO_INCREMENT自动增长列从1开始

insert into 表名 values(0,'rose','女',80); //插入数据测试是否从0开始

---枚举(单选)

alter table 表名 add 字段名 enum('男','女','保密') after name;  //enum枚举单选

insert into 表名 (字段名) values (3); //添加一个枚举数据,记得unique必填

describe 表名;  //查看表的所有列和列类型

---集合(多选)

alter table 表名 add hobby set('爬山','读书','游泳','烫头') first;  //添加集合多选到首 hobby爱好

insert into 表名 (字段名) values (15);  //添加一个集合数据全部 1+2+4+8  2的N次方

describe 表名; 

---sql的函数

select rand(); //随机数0-1,默认9位

select rand() + 2 //随机2-3

select rand()*3; //随机数0-3

select floor(rand()*3) //向下取整随机0,1,2

select ceil(rand()*3) //向上取整随机1,2,3

select truncate(3.14159,3) //结果3.141 

select round(3.1415926,3) //结果3.142 四舍五入

select round(rand()*3+5,0) //取5-8直接的整数 结果:随机5678

select * from 表名 order by rand(); //随机展现

select * from 表名 limit 2,10; //从第三条开始取10条(limit第一行从0开始) limit必须放在结尾

select ucase('i name is tom') '转成大写',lcase('My Name IS TOM') '转成小写';

select left('abcdef',3) '从左边截取',abc

select right('abcdef',3) '从右边截取',def

select substring('abcdef',3,2) '从中间截取',cd

select concat(字段1,'是',字段2,'岁') as '谁多大了' from 表名   //字符串相连

select length('锄禾日当午') as '字节',char_length('锄禾日当午') 字符; //查看字符长度

select 字段名 from 表名 where length(name)>=7  //查询字段字符长度大于7的行

select unix_timestamp();  //时间戳

select from_unixtime(unix_timestamp()),now();  //格式化时间

select year(now()) 年,month(now()) 月,day(now()) 日,hour(now()) 小时,minute(now()) 分钟,second(now())秒;

select dayname(now()) 星期,dayofyear(now()) 本年第几天,datediff(now(),'2010-08-08') 相距天数;

select md5('aa'),sha('aa');; //md5加密

---查询select 聚合函数

select sum(字段名) as 别名 from order where id>10;  //先查询出id大于10的数据，再执行聚合语句sum求和(空值不计算)

select avg(字段名) from 表名;   //求出avg平均值(不包含空值)

select max(字段名) '语文最大值' from 表名; //查出语文成绩最高的人,min是最低

select count(*) from 表名;  //返回所有行,包含null

select count(字段名) from 表名;  //返回sex字段行的数量,不包含null

select count(distinct 字段名) from 表名;  //返回字段行唯一不同的值个数, 不包含null distinct-不同的

select distinct 字段名 from 表名   //返回字段行唯一不同的值, 包含null

--查询select 模糊查询like _ % [] [^]

select * from 表名 where 字段名 like '%三%'  and 字段名 like '%猫%'  //查询有三且有猫的记录,% 表示任意0个或多个字符。可匹配任意类型和长度的字符

select * from 表名 where 字段名 like '_三_'  //查询三个字中间带三的名字  , 表示任意单个字符。匹配单个任意字符

select * from 表名 where 字段名 regexp '[张李王]三'  //找出“张三”、“李三”、“王三”（而不是“张李王三”）；

select * from 表名 where 字段名 not regexp '[张李王]三'  //将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；不包含null

.

--查询select 联合语句 union去重和union all不去重

select 表1字段 from 表1名 union select 表2字段 from 表2名  //查询data表中的姓名和data1表中姓名 结果自动合并的重复的记录

--查询select多表查询    内连接、外连接、自然连接、交叉连接

select * from 表1 inner join 表2 on 表1.公共字段=表2.公共字段  //内连接 返回的两个表的公共记录

select * from 表1 inner join 表2 using(ID); //使用using用来指定连接字段 返回的两个表的公共记录

//自然连接无存在价值

select * from 表1 natural join 表2;  //自然内连接  显示2个表不重复的所有列 natural:自然天然

select * from 表1 natural left join 表2; //自然左外连接 已左边表为准,右边多出的显示null

select * from 表1 natural right join 表2; //自然右外连接 反之

select * from 表1 left join 表2 on 表1.公共字段=表2.公共字段   //左外连接 以左边的表为准，右边如果没有对应的记录用null显示

select * from 表1 right join 表2 on 表1.公共字段=表2.公共字段 //右外连接 以右边的表为准，左边如果没有对应的记录用null显示

select * from 表1 full join 表2 on 表1.公共字段=表2.公共字段  //完全外连接  ，如右表没有匹配，则结果全部为NULL，如左表没有匹配，则全部为NULL。

select * from 表1 cross join 表2   //交叉连接 返回笛卡尔积

select * from 表1 cross join 表2 where 表1.ID=表2.ID //交叉连接如果有连接条件和内连接是一样的。

select 表名1,coalesce(writtenexam,'缺考'),coalesce(labexam,'缺考') from stuinfo natural left join stumarks; //返回函数第一个非空表达式

---order by 排序

select * from 表名 order by 字段 desc; //按字段降序 默认是升序asc

select * from 表名 order by 字段1 asc,字段2 desc; //多列排序

select * from 表名 order by rand(); //随机排序

---limit限制

//limit在update和delete中也可以用

select * from 表名 limit 2,3;  //从第2个位置开始取，取3条记录

select * from 表名 limit 3;  //起始位置可以省略,默认从0开始

---group by分组

select 分组字段,avg(字段2) as '平均分' from 表名 group by 分组字段;  //按性别展现平均分,前面放分组字段和聚合函数

select group_concat(字段),分组字段,avg(字段2) as '平均分' from 表名 group by 分组字段;  //通过group_concat把所有值连接起来

select 分组字段1,分组字段2,avg(字段2) from stu group by 分组字段1,分组字段2;

---having条件筛选  

//where一般是对原始数据进行筛选，having是对结果集进行筛选。,where不能使用聚合函数,但having能,

//因为优先级from>where>group（含聚合）>having>order>select。

select 分组字段,count(*) from 表名 group by 分组字段 having count(*) > 4;

--查询select子查询

1.标量子查询 返回值是1个 查找笔试成绩是80的学生

select * from 表1名 where stuno=(select stuno from 表2名 where 表二字段=80); 

2.列子查询,返回值是多个或1列, 如果子查询的结果返回多条记录，不能使用等于=，用in或not in-- 查找及格的同学

select * from stuinfo where stuno in (select stuno from stumarks where writtenexam>=60);

3.行子查询,子查询返回的结果是多个字段组成查找语文成绩最高的男生和女生

select * from stu where(stusex,ch) in (select stusex,max(ch) from stu group by stusex);

4.表子查询 将子查询的结果作为表   查找语文成绩最高的男生和女生

select * from (select * from stu order by ch desc) t group by stusex;

5.exists子查询  可提高查询效率

如果笔试成绩有人超过80人，就显示所有学生信息

select * from stuinfo where exists (select * from stumarks where writtenexam>=80);

--视图,可以使得降低SQL语句的复杂度

1.创建视图并指定算法为temptable

create or replace algorithm=temptable view view1 as select * from stu where ch>=60 and math>=60;

2.使用视图

select * from view1;

3.修改视图

alter view 视图名 as select 语句

4.删除视图

drop view [if exists] 视图1，视图,...

5.查看视图3种方法

show tables //显示所有的表和视图

show table status\G;       -- 查询所有表和视图的详细状态信息

show table status where comment='view'\G   -- 只查找视图信息

6.查看视图结构

desc view1;

7.查询创建视图的语法

show create view view1\G

--事务

1.开启事务

start transaction  或 begin [work]

2.提交事务

 update bank set money=money-100 where card='1001';

 commit

4.设置事务回滚点

update bank set money=money-100 where card='1001';

 savepoint a1

 3.回滚事务

rollback

rollback  to a1

---

--拓展:

1.显示地区及每个地区参加笔试的人数并将结果降序排列

select stuaddress,count(writtenexam) c from stuinfo left join stumarks using(stuno) group by stuaddress order by c desc;

2.显示男女的总人数

select stusex,count(*) from stuinfo group by stusex;