MySQL常用语句汇总

-- 创建普通索引
create index index_customers
on custs(cust_name(3) ASC);
-- 显示索引
show index in custs;
-- 创建组合索引
create index index_cust
on custs(cust_name,cust_id);
-- 边创建边设置索引
create table seller
(
seller_id int not null auto_increment,
seller_name char(50) not null,
seller_address char(50) null,
seller_contact char(50) null,
product_type int null,
sales int null,
primary key (seller_id,product_type),
index index_seller(sales)
);
-- 添加索引
alter table seller
add index index_seller_name(seller_name);
-- 删除索引
drop index index_seller_name on seller;
alter table seller
drop index index_seller;
-- 删除主键
-- 查询表中所有数据信息
select * from custs;
-- 插入一条数据
-- 没有列名
insert into custs
values('aa',0,default,default,'beijing');
-- 列举所有列名
insert into custs(cust_name,cust_id,cust_sex,cust_city,cust_address)
values('bb',0,1,default,'wuhan');
-- 没有自动增长列名
insert into custs
(cust_name,cust_sex,cust_city,cust_address)
values('cc',default,'haerbin','wuhan');
-- 部分列赋值
insert into custs
set cust_name='dd',cust_sex=default;
-- 插入多条语句
insert into custs
select '1',0,'1','1','1' union
select '1',0,'1','1','2';
-- 中文乱码
set character_set_results=gb2312;
-- 显示男女
select cust_name,
case
when cust_sex =1 then '男'
else '女'
end as 性别
from customers;
-- 编号加 100
select cust_id+100,cust_name from customers;
-- 聚合函数
select count(*) from customers;
select max(cust_id) from customers;
select min(cust_id) from customers;
select sum(cust_id) from customers;
select avg(cust_id) from customers;
-- 交叉连接
select * from tb1 cross join tb2;
--内连接
select * from stu_info
inner join stu_score
on stu_info.sno=stu_score.sno;
-- 左外连接
select stu_info.sno,stu_info.sname,stu_score.sscore
from stu_info
left join stu_score
on stu_info.sno=stu_score.sno;
-- 右外连接
select stu_info.sno,stu_info.sname,stu_score.sscore
from stu_score
right join stu_info
on stu_score.sno=stu_info.sno;
-- 比较运算符
select * from customers where cust_id!=2;
select * from customers where cust_id<>2;
-- 逻辑运算符
and 与
select * from customers where cust_id>2 and cust_sex=1;
or 或
select * from customers where cust_id>2 or cust_sex=1;
-- 两者之间 范围
select * from customers where cust_id between 2 and 4;
select * from customers where cust_id>=2 and cust_id<=4;
-- in
select * from customers where cust_id in(2,4);
select * from customers where cust_id=2 or cust_id=4;
-- 子查询
select * from stu_info where sno in(
select sno from stu_score);
-- exists
-- 分组查询
select ssex,count(*)
from stu_info
group by ssex;
select saddress,ssex,count(*)
from stu_info
group by saddress,ssex;
select saddress,ssex,count(*)
from stu_info
group by saddress,ssex
with rollup;
-- having 筛选
select saddress,ssex ,count(*)
from stu_info
group by saddress,ssex
having count(*)>1;
-- order by
select * from stu_info
order by sname desc;
-- limit
select * from stu_info
order by sname desc
limit 3;
select * from stu_info
order by sname desc
limit 4,3;
-- 创建视图
create or replace view customers_view
as
select * from customers where cust_sex=1
with check option;
create or replace view stu_view
as
select stu_info.sno,stu_score.score
from stu_info
inner join stu_score
on stu_info.sno=stu_score.sno
with check option;
-- 查询视图
select * from customers_view;
-- 删除视图
drop view stu_view;
-- 显示视图结构
show create view customers_view;
-- 添加
insert into customers_view
values(0,'ww',1,'ww','ww');
-- 更新
update customers_view
set cust_name='qq'
where cust_id=5;
-- 删除
delete from customers_view where cust_id=5;
-- 存储函数
delimiter $$
create function fn_search(cid int)
returns char(50)
deterministic
begin
declare sex char(2);
select cust_sex into sex from customers where cust_id=cid;
if sex is null then
return (select 'no');
else if sex=0 then
return (select 'gril');
else return (select'boy');
end if;
end if;
end $$
-- 调用
select fn_search(1);
-- 删除
drop function if exists fn_search;
-- 创建时设置外键
create table orders
 (
order_id int not null auto_increment,
order_product char(50) not null,
order_product_type char(50) not null,
cust_id int not null,
order_date datetime not null,
order_price double not null,
order_amount int not null,
primary key(order_id),
foreign key(cust_id)
references customers(cust_id)
on delete restrict
on update restrict
);
create table orders
(
order_id int not null auto_increment,
order_product char(50) not null,
order_product_type char(50) not null,
cust_id int not null,
order_date datetime not null,
order_price double not null,
order_amount int not null,
primary key(order_id)
);
alter table orders add foreign key
orders_fk(cust_id) references customers(cust_id);
-- 检查约束
create table team
(
teamno int not null,
division set('north','south','east','west')
);
-- 查看 mysql 数据库的使用者账号
select user from mysql.user;
-- 密码加密
select password(456);
+-------------------------------------------+
| password(456) |
+-------------------------------------------+
| *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
+-------------------------------------------+
-- 创建用户
create user 'zhangsan'@'localhost' identified by '123',
'lisi'@'localhost' identified by password '*531E182E2F72080AB0740FE2F2D68
9DBE0146E04';
-- 测试以张三用户登录
C:\Users\Administrator>mysql -u zhangsan -p
Enter password: ***
-- 删除用户账号
drop user lisi@localhost;
-- 重命名
rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost';
-- 修改密码
set password for 'wangwu'@'localhost'
='*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119';
-- 设置权限
grant select
n test1.customers
o 'wangwu'@'localhost';
-- 创建两个用户
grant select,update
on test1.customers
to 'liming'@'localhost' identified by '123',
'huang'@'localhost' identified by '789';
--执行所有数据库操作的权限
grant all
on test1.*
to 'wangwu'@'localhost';
-- 添加用户的权限
grant create user
on *.*
to 'wangwu'@'localhost';
-- 权限转移
grant select,update
on test1.customers
to 'zhou'@'localhost' identified by '123'
with grant option;
-- 权限撤回
revoke select
on test1.customers
from 'zhou'@'localhost';
xibsongbin@gmail.com
niejiefeng668@outlook.com
csx1102@163.com
zhao_haisheng1122@163.com