mysql 常用的查询语句
时间：2021-07-01 10:21:17
帮助过：2人阅读
1.连接数据库代码:
2 >mysql
-h 主机的ip地址
-u 登入数据库用户名
-p 登入密码
3
4 2.显示数据库
5 >show databases;
6
7 3.连接数据库
8 >use dbname;
9
10 4.显示当前连接的数据库
11 >select database();
12
13 5.显示表结构
14 >desc tablename
15
16
17 操作表:
18 1.修改表名
19 >alter table table_old rename table_new;
20
21 2.删除表
22 >drop table tabelname;
23
24 3.导入数据库
25 >source 文件全路径
26
27 4.修改字段长度
28 >alter table table_name modify column_name
varchar(
50) ;
29 4.1.修改字段名称
30 >alter table keyword_info change enabled cate_num
varchar(
50);
31 4.2.新增表字段名称
32 >alter table keyword_info
add enabled
varchar(
50)
33
34 5.修改字段类型
35 >alter table talbe_name modify column_name
int(
12);
36
37 6.查询建表语句
38 >show
create table table_name;
39
40 7.查询字段去重和统计不重复字段
41 >select distinct(column_name)
from table_name;
42 >select count(
DISTINCT column_name)
from table_name
43
44 8.删除表字段内容
45 >delete from tablename
where column_name
in (
‘xx‘,
‘xx‘,
‘xx‘);
46 >delete from keyword_info
where keyword
in(
‘设计‘,
‘新品‘,
‘潮牌‘,
‘高贵‘);
47
48 9.查询重复字段
49 >select column_name,
count(
1)
as count from user_table
group by column_name
having count>1;
50
51 10.删除完全重复字段(如果有10000条重复数据我不是要手动执行10000次,通过函数实现循环)
52 >DELETE from keyword_info
WHERE keyword
in (
select keyword
from (
select keyword,
count(
1)
as count from keyword_info
GROUP BY keyword
HAVING count>1)
as b) LIMIT
1
53
54 11.更新表字段内容
55 >update table_name
SET column_name
=新值
WHERE column_name
=老值
56
57 12.查询表插入新表
58 >INSERT INTO talbe1 (cloumn1)
SELECT cloum1_like
FROM table2 LIMIT
1;
59 >INSERT INTO keywords_search_info (nick)
SELECT shop_name
FROM search LIMIT
1;
60
61 13.给已有表增加主键
62 1.字段必须不为空
63 >alter table mytable_1
alter column name
char(
1)
not null
64 2.删除原来主键
65 >alter table mytable_1
drop constraint PK_mytable_1（主键名字）
66 3.添加新主键
67 >alter table mytable_1
add constraint PK_mytable_1
primary key(id,name)
68 4.例子
69 >alter table keyword_info
add constraint keyword
primary key(keyword);
70
71 14.多表关联(
on 后面紧跟主表字段)
72 >select distinct i.shopid
from (item i
left join task_info t
on i.shopid
=t.shop_id)
left join shop s
on i.shopid
=s.shop_id
where t.type
=‘1‘ limit
10,
5;
73
74 高级函数:
75 1.取整
76 >round(x,d)
77
78 2.求平均
79 >avg(x)
80
81 一个简单的存储过程:
82
83 //创建一个存储过程
84 CREATE PROCEDURE p14()
85
86 BEGIN
87 DECLARE v
INT;
88 SET v
= 0;
89 WHILE v
< 30 DO
90 DELETE from keyword_info
WHERE keyword
in (
select keyword
from (
select keyword,
count(
1)
as count from keyword_info
GROUP BY keyword
HAVING count>1)
as b) LIMIT
1;
91 SET v
= v
+ 1;
92 END WHILE;
93 END;
94
95
96 CALL p14();
//执行这个存储过程
97
98 drop procedure if exists p4
//删除存储过程
mysql 常用的查询语句
标签：
