mysql 使用

发表于 | 更新于 | 分类于

数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# 连接数据库
mysql -h 地址 -P 端口 -u 用户名 -p 密码

# 显示全部数据仓库
show databases;

# 创建数据仓库
create database test_name;

# 进入数据仓库
use test_name;

# 显示全部数据表
show tables;
SHOW TABLES FROM 表名

# 创建数据表
create table table_name (column_name column_type,
						column_name column_type,
						column_name column_type);
                        
# 复制表结构
CREATE TABLE 表名 LIKE 要复制的表名

# 表中插值
insert into table_name  values("学习 mysql", "教程", "今天");
insert into table_name (titie,word) values("学习 mysql", "教程",); #指定字段

# 显示数据表
select * from table_name;

# 查找数据
select col_name,col_name2 from table_name;
select col_name,col_name2 from table_name where col_name3 > 10; # where 增加选择条件
select col_name from table_name where col_name3 REGEXP '^st'; #正则表达式 col_name3 以st开头
select * from mytable  where birthaddr='河北' order by sex; # 排序
# =, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
# in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not
select * from mytable where sex=1 and birthaddr in ('河北','河南')
select sum(col_name) from table_name group by col_name2; #分组统计 count sum max min avg
select birthaddr,count(sex) as cc from mytable group by birthaddr having cc >= 2; # group by having

# 联表
select a.id, a.author, b.count from runoob_tbl a inner join tcount_tbl b ON a.author = b.author; # 内接
select a.id, a.author, b.count from runoob_tbl a left join tcount_tbl b ON a.author = b.author; # 左接
select a.id, a.author, b.count from runoob_tbl a right join tcount_tbl b ON a.author = b.author; # 右接

# 修改数据
update table_name set addr="河北" where col_name="小王"; # 修改值
alter table table_name rename to new_table_name; # 修改表名
alter table table_name drop col_name; # 删除col_name字段
alter table table_name add col_name_new VARCHAR(40); #最后一列添加字段
alter table table_name add col_name_new VARCHAR(40) first; #第一列添加字段
alter table table_name add col_name_new VARCHAR(40) after col_name; #在某一列后面添加字段

#删除
delete from table_name where runoob_id=3; #删除行数据
drop table table_name; # 删除表
truncate table_name; # 清空表
drop database; #删除库

having 子句与 where 功能、用法相同，执行时机不同。where 在开始时执行检测数据，对原数据进行过滤。having 对筛选出的结果再次进行过滤。having 字段必须是查询出来的，where 字段必须是数据表存在的。

python 操作mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
>pip install mysqlclient

import MySQLdb
conn= MySQLdb.connect(host='localhost',user='root',passwd='xxxxxxx', db ='test_db') # 连接数据库
cur = conn.cursor()
cur.execute("select * from mytable") # 所有的mysql在 cur.execute 中编写

data = [
  ('Jane', date(2005, 2, 12)),
  ('Joe', date(2006, 5, 23)),
  ('John', date(2010, 10, 3)),
]
stmt = "INSERT INTO employees (first_name, hire_date) VALUES (%s, %s)"
cursor.executemany(stmt, data) #插入多条

result =  cur.fetchall() # 获得返回值，返回值是一个嵌套元祖
conn.close() # 关闭连接