【MySQL】mysql：sql 语句汇总

最新推荐文章于 2022-11-05 21:27:38 发布

顾三殇

最新推荐文章于 2022-11-05 21:27:38 发布

阅读量485

点赞数 1

分类专栏：【软件测试】数据库测试文章标签： sql 语句汇总 mysql

本文为博主原创文章，未经允许不允转载，转载需先私信本人获得同意，且须注明出处：By @CSDN 顾三殇

本文链接：https://blog.csdn.net/qq_39720249/article/details/102558701

版权

【软件测试】数据库测试专栏收录该内容

16 篇文章 11 订阅

订阅专栏

1、创建普通索引

create index index_customers

on custs(cust_name(3) ASC);

2、显示索引

show index in custs;

3、创建组合索引

create index index_cust

on custs(cust_name,cust_id);

4、边创建边设置索引

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)

);

5、添加索引

alter table seller

add index index_seller_name(seller_name);

6、删除索引

drop index index_seller_name on seller;

alter table seller

drop index index_seller;

7、删除主键

8、查询表中所有数据信息

select * from custs;

9、插入一条数据

10、没有列名

insert into custs

values('aa',0,default,default,'beijing');

11、列举所有列名

insert into custs(cust_name,cust_id,cust_sex,cust_city,cust_address)

values('bb',0,1,default,'wuhan');

12、没有自动增长列名

insert into custs

(cust_name,cust_sex,cust_city,cust_address)

values('cc',default,'haerbin','wuhan');

13、部分列赋值

insert into custs

set cust_name='dd',cust_sex=default;

14、插入多条语句

insert into custs

select '1',0,'1','1','1' union

select '1',0,'1','1','2';

15、中文乱码

set character_set_results=gb2312;

16、显示男女

select cust_name,

case

when cust_sex =1 then '男'

else '女'

end as 性别

from customers;

17、编号加 100

select cust_id+100,cust_name from customers;

18、聚合函数

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;

19、交叉连接

select * from tb1 cross join tb2;

20、内连接

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;

21、右外连接

select stu_info.sno,stu_info.sname,stu_score.sscore

from stu_score

right join stu_info

on stu_score.sno=stu_info.sno;

22、比较运算符

select * from customers where cust_id!=2;

select * from customers where cust_id<>2;

23、逻辑运算符

① and 与

select * from customers where cust_id>2 and cust_sex=1;

② or 或

select * from customers where cust_id>2 or cust_sex=1;

24、两者之间范围

select * from customers where cust_id between 2 and 4;

select * from customers where cust_id>=2 and cust_id<=4;

25、in

select * from customers where cust_id in(2,4);

select * from customers where cust_id=2 or cust_id=4;

26、子查询

select * from stu_info where sno in(

select sno from stu_score);

27、exists

28、分组查询

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;

29、having 筛选

select saddress,ssex ,count(*)

from stu_info

group by saddress,ssex

having count(*)>1;

30、order by

select * from stu_info

order by sname desc;

31、limit

select * from stu_info

order by sname desc

limit 3;

select * from stu_info

order by sname desc

limit 4,3;

32、创建视图

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.snowith check option;

33、查询视图

select * from customers_view;

34、删除视图

drop view stu_view;

35、显示视图结构

show create view customers_view;

36、添加

insert into customers_view

values(0,'ww',1,'ww','ww');

37、更新

update customers_view

set cust_name='qq'

where cust_id=5;

38、删除

delete from customers_view where cust_id=5;

39、存储函数

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 $$

40、调用

select fn_search(1);

41、删除

drop function if exists fn_search;

42、创建时设置外键

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);

43、检查约束

create table team

(

teamno int not null,

division set('north','south','east','west')

);

44、查看 mysql 数据库的使用者账号

select user from mysql.user;

45、密码加密

select password(456);+-------------------------------------------+

| password(456) |

+-------------------------------------------+

| *531E182E2F72080AB0740FE2F2D689DBE0146E04 |

+-------------------------------------------+

46、创建用户

create user 'zhangsan'@'localhost' identified by '123',

'lisi'@'localhost' identified by password '*531E182E2F72080AB0740FE2F2D68

9DBE0146E04';

47、测试以张三用户登录

C:\Users\Administrator>mysql -u zhangsan -p

Enter password: ***

48、删除用户账号

drop user lisi@localhost;

49、重命名

rename user 'zhangsan'@'localhost' to 'wangwu'@'localhost';

50、修改密码

set password for 'wangwu'@'localhost'

='*6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119';

51、设置权限

grant select

n test1.customers

o 'wangwu'@'localhost';

52、创建两个用户

grant select,update

on test1.customers

to 'liming'@'localhost' identified by '123',

'huang'@'localhost' identified by '789';

53、执行所有数据库操作的权限

grant all

on test1.*

to 'wangwu'@'localhost';

54、添加用户的权限

grant create user

on *.*to 'wangwu'@'localhost';

55、权限转移

grant select,update

on test1.customers

to 'zhou'@'localhost' identified by '123'

with grant option;

56、权限撤回

revoke select

on test1.customers

from 'zhou'@'localhost';

xibsongbin@gmail.com

niejiefeng668@outlook.com

csx1102@163.com

zhao_haisheng1122@163.com