MYSQL命令总结—详细版

**

Mysql命令详解

**
1. 进入mysql目录
cd d:\Program Files (x86)\MySQL\MySQL Server 5.5\bin
Mysql –h 192.168.21.150 –u root –p123456
2.创建数据库
1）. 断开数据库 quit，exit
2）. 查看数据库 ：show databases;
3）. 创建数据库： create database test1 character set utf8(gb2312);
4）. 删除数据库 : drop database test1;
5）. 使用数据库： use test1; show database;
6）. 三大数据类型：数值，时间， 字符

3. 创建数据表
Create table 表名称（
列表名称01 数据类型 约束，
约束：enum（数据列表），set（数据列表）
Primary key，unique，not null
列 类型 键 auto_increament
）；
use test1;
Create table student(
Id int not null primary key,
Name varchar(50) not null,
Age int);
Show tables;

3.1查看表结构：
desc student；
3.2删除表：
drop table student；
4.修改数据表结构
4.1修改表名称: alter table 表名称 rename to 新表名称;
Alter table student rename to class;
4.2修改字段名: alter table 表名称 change 旧字段名 新字段名 数据类型；
Alter table student change id stu_id int;
4.3修改字段的数据类型： alter table 表名 modify 字段名 数据类型；
Alter table class modify sex char(1);
4.4添加字段：alter table 表名 add 新字段名 数据类型
5. 数据表的增删改查
5.1增
insert into 表名 values（值列表）
5.2 删
delete from 表名 where 表达式=值
5.3 改
update 表名 set 列1名=值 where 列表达式=值
查询时若显示乱码，可用set names gb2312设置编码格式，然后重新查询
5.4 查
5.4.1查询规律
Select 列名列表
From 表1，表2
Where 列表达式=值
Group by 分组表达式 having 分组筛选条件
Order by 排序表达式
Limit 限制行数
去重查询 select distinct 列名 from 表名
列名中若有特殊字符，用倒引号``转义
函数形式 说明
char_length(字符串数据或列名) 计算字符串中的字符总数。
substring(字符串数据或列名,m,n) 从字符串中第m个开始连续取n个字符，最后一个字符下标是-1。left，right，汉字是一个字符
trim(字符串数据或列名) 去掉字符串首尾的空格，ltrim，rtrim
concat(str1 , str2 , …) 连结字符串。若参数有NULL返回NULL

函数形式 说明
count(*) 计算所有列整体的总行数，支持count(常量)
count(列名) 计算指定列下非空数据的总行数，重复值也计数
sum(数值型列名) 计算数值型指定列下非空数据的总和
avg(数值型列名) 计算数值型指定列下非空数据的平均值，average
max(列名) 计算指定列下非空数据的最大值
min(列名) 计算指定列下非空数据的最小值

round(数值数据或列名，小数位数) 根据指定小数位数，对数值数据四舍五入
floor(X) 返回不大于X的最大整数值。
ceiling(X) 返回不小于X的最小整数值。
select count(*) rs from student
select left(id,4) bj,count(left(id,4)) rs from student group by left(id,4)
select sex,count(sex) rs from student group by sex
select id,sum(cj) zcj from score group by id
select id,avg(cj) zcj,min(cj) min_cj,max(cj) max_cj from score group by id
select id,char_length(ID_num) from student where id=‘CS0402’
select id,substring(ID_num,7,4) from student where id=‘CS0402’
select id,substring(ID_num,7,4) from student where id=‘CS0402’

函数形式 说明
now( )或sysdate( ) 年月日时分秒
current_date( )或curdate( ) 年月日
current_time( ) 或curtime( ) 时分秒
current_timestamp( ) 年月日时分秒
year(now( ))、month(now( ))、day(now( ))、hour(now( ))、minute(now( ))、second(now( ))
5.4.2多条件查询
select 列名列表 from 表名 where 条件表达式1 and/or 条件表达式2
select current_timestamp()
select * from student where sex=‘男’
select * from score where cj>=‘80’
select * from score where cj not between 70 and 80
select * from student where xm in (‘李庆’,‘柏雅’,‘李晓波’)
select * from student where xm not in (‘李庆’,‘柏雅’,‘李晓波’)
select * from student where ID_num is null
insert into student(id,xm,sex) values(‘CS0505’,‘李莉’,‘男’)
select * from student where substring(zcsj,1,10)!=‘2018-10-26’

5.4.3模糊查询
select 列名列表 from 表名 where 列表达式 like ‘模式符’
%：匹配零个或多个字符。
_：匹配单个字符。
select 列名列表 from 表名 where 列名 rlike ‘正则表达式’
或：列名 regexp ‘匹配方式’
[^]：（不）匹配其中的任意一个，不能仅使用[ ]
( )：一个整体
|：或者（从中选一个）
select * from student where ID_num like ‘%88%’
select * from student where xm like ‘李%’
select * from student where xm rlike ‘^[李]’
select * from student where xm rlike ‘^(李|张|孙)’

5.4.4分组查询
select 结果列表达式 from 数据源
where 列表达式条件式
group by 分组列表达式1，分组列表达式2 having 分组条件
引入分组后，select语句各部分的执行顺序
1）先执行from打开表；
2）然后执行where从表中筛选数据（where可以根据实际情况省略）
3）之后执行group by对数据进行分组；
4）再执行having对分组结果进行筛选；
5）最后执行select显示数据
select sex,count() rs from student where sex=‘男’
select count() rs from student where ID_num is null
select count() rs from score where cj>=60
select left(id,4) bj,sum(cj) zcj from score group by left(id,4)
select left(id,4) bj,sex,count() rs from student group by left(id,4),sex
统计总成绩不足100分的每个学生的总成绩和平均成绩:select id, sum(cj),avg(cj) from score natural join score group by id having sum(cj)<100
统计D102班总成绩不足100分的每个学生的总成绩：select id,sum(cj) from score group by id having sum(cj)<100 and left(id,4)=‘CS04’
统计男女生各有多少人，要求显示总人数：select left(id,4) bj,ifnull(sex,‘总计’)sex,count(*) rs from student group by sex with rollup

5.4.5并（union、union all）
要求多个select后的列名列表必须列数相同、类型兼容、语义相同。
select ifnull(sex,‘total’)sex,count(*) from student group by sex with rollup
select id,xm,sex from student union select id,name,sex from xuesheng
select id,xm,sex from student union all select id,name,sex from xuesheng
select student.id,xm,sex,cj from student,score where student.id=score.id

5.4.6多表连接
用于指定多个数据源之间的组合关系，将多张表在内存中组合为一张表。
1)笛卡尔积
2)等值连接
select student.id,xm,sex,cj from student,score where student.id=score.id
3)自然连接
select * from student natural join score
4)内连接
select 列名列表 from 表1 inner join 表2 on 表1.列表达式1=表2.列表达式2
on表示连接条件，不能替换为where。
列表达式同名时不可省略表名。
select * from student inner join score on student.id=score.id
5)自连接
表自己与自己的连接称为自连接。
只能通过表别名的方式来实现。

命令形式
select 列名列表 from 表名 表别名1 join 表名 表别名2 on 表别名1.表达式1=表别名2.表达式2
6)外连接
外连接是以一个表为基础，将另外一个表与之进行条件匹配，即使条件不匹配，也有一方表的数据总是出现在结果集中。
左侧表中的数据全部出现，右侧表只有符合条件的才出现，不匹配的数据置空，即显示为null。
select student.id,xm,sex,cj from student inner join score on student.id=score.id 右边为准
select student.id,xm,sex,cj from student left outer join score on student.id=score.id 左边为准
5.4.7子查询
内部的查询称为内查询、子查询，外部的查询称为外查询、父查询。
执行时，先执行内查询，然后执行外查询。
多行子查询select * from student where id in (select id from score) in、not in和谓词exists
单行子查询select * from score where kh=(select kh from course where kc=‘数学’) 可以用：=、!=、>、>=、<、<=、in、not in和谓词exists。
子查询与连接的效率对比：
查询数据时，如果既能用多表连接实现，也能用子查询实现，应该优先选择多表连接，因为连接效率高于子查询。
如果子查询可以使用in，也可以使用exists实现，那么可以优先选择exists，exists的效率要要高于in
建表数据源 create table stu as select id,xm,sex from student
插入数据源：insert into stu select id,xm,sex from student where id not in (select id from stu)