MySQL 数据库 (; 表示结束语句) 关系型数据库
注释用 //
db_name 表示库名; b_name 表示表名
1. 进入, 退出
root 用户只能在本机连接, 不可外部链接
进入: MySQL -uroot -pqwe123 (u 代表 use,p 代表 password)
或 MySQL -uroot -p
qwe123 (密码)
退出 :exit / quit
2. 库, 表级操作 (官方命令的字母要大写)
2.1 库操作:
查看所有数据库:
SHOW DATABASES;
创建:
CREATE DATABASE db_name;
使用:
USE db_name;
删除:
DROP DATABASE db_name;
2.2 表操作:
创建:(必须指定类型) 类型:(int/smallint 整数, char/varchar 字符串, bit 位), 约束:(NOT NULL 不能为空, UNIQUE KEY 不能重复, PRIMARY KEY 非空且唯一, UNSIGNED 无符号 (非负))
如: CREATE TABLE b_name(id INT UNSIGNED(表示非负数) NOT NULL AUTO_INCREMENT,
- name varchar(30)(30 表示最多字符长度) NOT NULL,
- 'number' char(9)(9 表示固定字符长度) NOT NULL,
- gender bit(1) DEFAULT b'0',
- phone char(11) NOT NULL,
- tzaccount varchar(20) NOT NULL,
- qq varchar(12),
- wechat varchar(30),
- 'power' smallint(6)(括号内最大为 65535) NOT NULL,
- actrecv smallint(6) NOT NULL,
- PRIMARY KEY (id), (非空且唯一)(一张表只能有一个主键)
- UNIQUE KEY ('number'),
- UNIQUE KEY (phone),
- UNIQUE KEY (tzaccount),
- UNIQUE KEY (qq),
- UNIQUE KEY (wechat)(最后没有逗号)
- );
查看所有表:
SHOW TABLES;
查看创建表:
SHOW CREATE TABLE b_name;(; 或用 \ G 结尾)
删除表:
DROP TABLE b_name;
3. 记录的 CRUD 操作:
3.1 插入数据:
- INSERT INTO b_name VALUES (1, 'Tyf', ...(参数必须全写完))
- INSERT INTO b_name(name, `number`, gender, phone) VALUES ('ll', '20181105', b'1', '13266666666') (选择性插入)
3.2 查询:
- SELECT * FROM b_name; (查询所有)
- SELECT * FROM b_name WHERE gender=b'1'; (按条件查询, 如查询 gender=b'1'的 对象)
- SELECT name,phone FROM b_name;(查询一部分, 查询 name,phone 列)
3.2.1 筛选条件:
比较运算: (NULL 不能作比较, 可以用 IS NULL 或 IS NOT NULL)
等于 =
大于 >
大于等于 >=
小于 < 小于等于 <=
不等于 <> 或 != (如: WHERE gender <> 0;)
逻辑运算:
and
or
not
3.2.2 子查询:(要求: 1. 必须嵌套在查询内部, 2. 必须在圆括号内)
如: SELECT * FROM b_name WHERE `power`>(SELECT `power` FROM b_name WHERE name='Tyf');
3.2.3 聚合:
统计个数:
count(*) (如: SELECT count(*) FROM b_name;)
最大值:
max(column)
最小值:
min(column)
求和:
sum(column)
平均值:
avg(column)
3.2.4 分组聚合:(查询只能包含分组列和聚合列, 且单纯的分组意义不大, 但可以这么做)
如: SELECT gender, max(`power`)(这里还可以添加内容) FROM b_name GROUP BY gender;
3.2.5 聚合筛选:
如: SELECT gender, max(`power`) FROM b_name GROUP BY gender HAVING max(`power')>9000;(分组聚合以后才能用 HAVING 语句)
SELECT gender, max(`power`) FROM b_name WHERE `number`>'1233' GROUP BY gender
3.2.6 限制查询个数:
- SELECT * FROM b_name LIMIT 3;(3 代表查询个数)
- SELECT * FROM b_name LIMIT 3,4;(跳过三个查询四个)
3.2.7 排序:
- SELECT * FROM b_name ORDER BY `number`;(默认升序)
- SELECT * FROM b_name ORDER BY `number` DESC;(降序)
3.2.8 分页算法:(分页用 SQL 座效率高, 最好不要用 python 做)
- SELECT * FROM b_name ORDER BY `number` LIMIT 3;(第一页)
- SELECT * FROM b_name ORDER BY `number` LIMIT 3,3;(第二页)
- SELECT * FROM b_name ORDER BY `number` LIMIT 2*3,3;(第三页)
...... 等等以此类推
3.3 删除:
- DELETE FROM b_name WHERE 1; (全删)
- DELETE FROM b_name WHERE name='Tyf'; (安条件删除)
3.4 修改:
UPDATE b_name SET gender=b'1' WHERE name='Tyf';(不加 WHERE name='Tyf'则表示修改所有, SET 后面改可以改多个)
注意: 表中加 is_delete bit(1), 则数据永远删不掉, 表示假删除
4. 注意事项:
大小写不敏感: 建议 MySQL 语法建议大写
语句结束符: 分号; \G 表示不同格式
强制数据类型: 数据定下来以后最好别再改动, 可能导致数据丢失
反引号: 如果使用 MySQL 保留字来做名字, 则使用 ` ` 括起来
5. 表关系:
略 (视频 )
6. 联表:(优先联表, 默认内连接)
SELECT * FROM (b_name1 JOIN b_name2); #类似于直角坐标系交叉点 (不懂补视频)
6.1 内连接:
- SELECT * FROM b_name1 JOIN b_name2 ON b_name1=b_name2; (筛选型联表)
- SELECT teachers.name AS t
- SELECT * FROM departmentname, departments.name AS dname FROM teachers INNER(INNER 可加可不加) JOIN departments;
6.2 左连接:
s LEFT JOIN teachers ON departments.id=teachers.department_id;(保留左边内容)
6.3 右连接:
SELECT * FROM departments RIGHT JOIN teachers ON departments.id=teachers.department_id;(保留右边内容)
7. 外键约束:(作用: 避免出现脏数据)
7.1 表 a 约束表 b,a 中有的 b 中才能插入, 没有的则不能插入:
- CREATE TABLE a(ca INT, PRIMARY KEY(ca));
- CREATE TABLE b(cb INT, FOREIGN KEY(cb) REFERENCES a(ca));
7.2 通过外键保证三大关系 (一对多, 多对多, 一对一):
一对多: 让多的一端加上外键;(外键一定要关联到唯一或主键上)
多对多: 需要构建一个中间表, 并为中间表分别加上两个外键 (分别指向两张表);(具体补视频)
一对一: 随便一端使用加上外键, 再使用唯一键限制.
8. 增加列, 删除列:
- ALTER TABLE tb_name ADD COLUMN co_name co_definition [FIRST|AFTER];
- ALTER TABLE tb_name DROP COLUMN co_name;
如:
ALTER TABLE b_name ADD COLUMN age int(11) DEFAULT 1;
9. 重命名:
ALTER TABLE tb_name RENAME COLUMN old_name TO new_name; (*)
10. 修改 (只能修改列选项) :
ALTER TABLE tb_name MODIFY co_name co_definition;
11. 改变 (必须要同时修改名字和选项):
ALTER TABLE tb_name CHANGE old_name new_name co_definition;
12. 改表名:
RENAME TABLE old_name TO new_name;
13. 修改库名 (没有的!) 折中的办法: 将当前库里的所有表移动到另一个库里, 然后再删除当前库:
RENAME TABLE old_db.tb_name TO new_db.tb_name;
(ALTER TABLE old_tbname RENAME new_tbname;) 不推荐使用, 不能使用这个命令移动库, 没有 RENAME 强大
14. 交换表名:
RENAME TABLE one_name TO m_name, tow_name TO one_name, m_name TO tow_name;(借用中间变量修改交换表名)
15. 增加主键, 删除主键:
- ALTER TABLE tb_name ADD PRIMARY KEY (co);
- ALTER TABLE tb_name DROP PRIMARY KEY;
16. 在 MySQL 里面, 唯一键其实被叫做有唯一约束的索引, 添加索引和删除索引
ALTER TABLE tb_name ADD INDEX (co);-- 普通键
ALTER TABLE tb_name DROP INDEX idx_name; -- 也可以用来删除唯一键
ALTER TABLE tb_name ADD UNIQUE KEY (co);-- 唯一键
17. 添加和删除外键
- ALTER TABLE teachers ADD FOREIGN KEY (department_id) REFERENCES departments(id);(简单版, 默认自动添加新的外键名)
- ALTER TABLE teachers ADD CONSTANT cs_name FOREIGN KEY (department_id) REFERENCES departments(id);(复杂版, 可以自定义外建键名)
- ALTER TABLE teachers DROP FOREIGN KEY cs_name;
- 18.pymysql(第三方库):
导入:
- from pymysql import connect
- db_config = {
'host':'127.0.0.1', -- 默认是 127.0.0.1
- 'user':'admin',
- 'password':'Root110qwe',
- 'db':'myku',
'charset':'utf8' -- 不能使用 utf-8
}
连接:
conn = connect(**db_config)
操作:
- try:
- with conn.cursor() as cursor:
- sql = "INSERT INTO myku(id,name,sex,phone) VALUES (%s, %s, %s)"
- cursor.execute(sql,(3,'tyf','boy','12354')) /(cursor.executemany(sql,[(,),(,),....])
- res = cursor.fetchall()(只是临时的)/conn.commit()(提交, 可以修改表内的数据)
- print(res)
提示 -------------------------------
如果是查询 就 cursor.fetchall()
如果是修改 就 conn.commit()
来源: http://www.bubuko.com/infodetail-2966999.html