mysql常用操作

操作环境为windows下mysql57

windows下mysql开启与关闭：

net start mysql57
net stop mysql57

卸载与安装服务：

卸载服务
	mysqld –remove
安装
	mysql –install

登录与退出：

mysql -uroot -p

exit; quit;  \q

显示数据库

show databases;
use database;
show tables;

查看表结构

desc table;

创建数据库：

create databases test;

查看建表语句：

show create table t_proj_type;

创建表：

create table teacher(
	id int(10) not null unique auto_increment,
	name varchar(20) not null,
	primary key (id)
)engine=innodb;


create table test(
	id int not null auto_increment,
	username varchar(20) not null,
	userpasswd varchar(32) not null,
	primary key (id)
)engine=innodb;

查看当前使用的是哪个数据库

select database();

查看某个数据库下的所有表

show tables from mysql;  /*mysql为数据库名*/

#创建表：通过其他表来创建
内容与原表一致，但是没有主键约束

create table if not exists stu2 as select * from stu;

#通过like方式创建后与原表结构一致，没有原表数据，有主键约束

create table stu3 like stu;

#查看警告信息

show warnings;

查看某个表建表语句

show create table stu3;

查看表结构

desc stu3;
show columns from stu3;

#指定默认值 default

查看帮助信息

help show columns;
help create table;

建表后添加列：

alter table stu3 add column address varchar(40) null default '' comment '地址';

删除列

alter table stu3 drop column address;

添加到最前面

alter tablestu3 add column a_id int first;

添加到class列后面

alter tablestu3 add column test int after class;

#修改列的属性

alter table stu3 modify address varchar(50) not null;

#修改列名

alter table stu3 change address addr  varchar(50) not null;

#修改表名

alter table stu3 rename  to stu4;

#删除表

drop table atu3;

插入数据

insert into stu(name,age) values('赵六',32);
insert into stu(name,age) values('赵六',32),('wangwu',32);

#查看最近成功插入的一条数据的id

select last_insert_id() as id;

#修改数据，如果没有条件，修改的就是全部数据

update stu set age='1',name='11' where id=1;

#删除数据

delete from stu where id=7;

#删除全部数据

delete from stu;
/*比delete更高效，truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。*/
/*不能有where子句，外键*/
truncate stu;  

#排序
desc逆序，asc顺序，默认是asc

select * from stu order by id desc;

#字符串排序是根据字典顺序排序

#组合排序，先按照name，name相同时再按照age

select * from stu order by name asc,age asc;

#查询

select count(*) as total from stu;
select max(age) from stu;

select id,count(*) from stu group by age;

select id,avg(age) from stu group by age;

select id,age from stu group by age having id<10;

#过滤 不等于：<>  >= <=

select * from stu where id <> 10;

#多个条件用and连接

select * from stu where age='10' and id='10';

select * from stu where test is null;  /*不能写成=null*/

select * from stu where test is not null; /*不为空*/

#in 在...范围之内的数据

select * from stu where id in (1,2,3);

#not in 不在...范围之内的数据
#通配符 like ,%匹配任意个字符
#参考：https://www.w3cschool.cn/mysql/mysql-jlvd2oyc.html

#正则匹配

select * from stu where name like '%张%';
select * from stu where name like '张_';

#"_"匹配单个字符,"\_" 匹配"_"

#连接符  concat,相当于字符串连接

select concat(name,age) from stu;
select concat('姓名:',name,'年龄:',age) as 学生信息 from stu;

#as表示别名

#运算符 + - * %

select id+age from stu;

#聚合函数

max,min,avg,count,sum

select min(age) from stu;  /*不会计算null，计算的是有值的最小值*/

#分页

select * from stu limit 0,5;  /*从5开始获取5条*/

#去重查询

select distinct name from stu;

#区间查询

select * from stu where id between 1 and 10;
select * from stu where id>1 and id<10;

#多条件查询 and  or

select * from stu where id >4 or age>20;
select * from stu where id >4 and age>20;
select * from stu where (age=21 or age=40) and id>4;

#in 和or区别：所在列有索引或主键时，in和or没有区别，执行的事件几乎一样
#如果查询的列没有索引，随着查询的数据量越多，in的效率不会有明显下降，or会随着数据量增大效率受影响较大

#多表关联查询

create table dingdan(
	id int not null auto_increment,
    num int,
    s_id int,
    dingdan_desc varchar(50),
    primary key(id)
)engine=innodb;

show tables;
desc dingdan;



select name,num,dingdan_desc 
from dingdan d,stu s 
where d.s_id=s.id;

# 用查询结果集作为关联表

create table contect(
	id int(10) auto_increment,
    s_id int(10) default null,
    tel varchar(20) default null,
    address varchar(20) default null,
    primary key(id)

)engine=innodb auto_increment=3 default charset=utf8;


create table dingdan(
	id int(10)  auto_increment,
    num int(11) default null,
    s_id int(11) default null,
    `desc` varchar(50) default null,
    primary key(id)

)engine=innodb auto_increment=4 default charset=utf8;



SELECT b.name,c.tel,c.address,b.num,b.`desc`
FROM contect c,
(SELECT s.name,d.num,d.`desc`,s.id
FROM dingdan d, stu s 
WHERE d.s_id = s.id) b
WHERE c.`s_id` = b.id;

写建表语句时出现mysql的关键字报错，加上``
如create table `desc`

 

#日期函数

select now();
select date_format(now(),'%y年%m月%d日 %H时:%i分%s秒');

#自定义函数：简称UDF 对mysql扩展的一种途径
#参数：0个或多个
#返回值只能有一个

#不带参数  returns：返回值+返回值类型

create function ftime() returns varchar(30)
	return date_format(now(),'%y年%m月%d日 %H时:%i分%s秒');

select ftime();  /*调用*/

#删除函数

drop function if exists ftime;

#创建带参数函数

create function favg(a int unsigned,b int unsigned) returns float(10,2)
	return (a+b)/2;
    
    
select favg(2,3);  /*调用*/
select favg(2,-3);  /*报错*/
#unsigned代表无符号

#改变执行sql语句结尾符号
#将delimiter语句与创建函数的语句一起执行

delimiter ..  /*MYSQL的结束符设置为..*/
CREATE FUNCTION addStu (username VARCHAR(20))
RETURNS int UNSIGNED
BEGIN
insert stu(name) values(username);
return LAST_INSERT_ID();
END..
delimiter ;  /*使用delimiter之后再改为;*/

/*
“DELIMITER //”语句的作用是将MYSQL的结束符设置为//，因为MYSQL默认的语句结束符为分号;，
为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END //”结束存储过程。
*/
alter table 表名 auto_increment=1;  /*重置id自增*/

#存储过程
#mysql执行命令的流程：
#sql命令 mysql引擎 分析是否正确 语法正确-->可执行命令 执行，返回结果给客户端

#IN 表示该参数的值必须在调用存储过程时指定
#OUT表示该参数可以被存储过程改变，并且可以返回
#INOUT 表示该参数在调用时指定，并且可以被改变和返回

#创建不带参数的存储过程

create procedure get_version()
	select version();

    
call get_version;  /*调用存储过程使用call，call get_version()也可以*/
/*如果存储过程没有参数，可以不写括号*/

delimiter ..
create procedure delById(IN id int unsigned) 
begin
	delete from stu where id=id;  /*这样写有问题，id都被换成了传入的值，sql注入，如40=40*/
end..
delimiter ;

#修改

delimiter ..
create procedure delById(IN a_id int unsigned) 
begin
	delete from stu where id=a_id; /*或者写成stu.id=id*/
end..
delimiter ;


call delById(3);  /*调用*/

#有返回值

delimiter ..
create procedure deleteReturnNum(IN s_id int unsigned,OUT s_num int unsigned)
begin
	delete from stu where id=s_id;
    select count(id) from stu into s_num;   /*使用into赋值给返回值*/
end..
delimiter ;

call deleteReturnNum(4,@num);  /*@+变量名接收返回值*/
select @num;  /*显示返回值*/

扩展阅读：https://www.cnblogs.com/gavin110-lgy/p/5772577.html

select	row_count();  /*受影响的行数*/