linux ---MySQL
- mysql 登录账号
>mysql -u name -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
- 显示数据库
mysql> show databases;
- 选中数据库
mysql> use database_name;
- 查看选中数据库里的文件
mysql> show tables;
- 查看某个文件的结构
mysql> desc file_name;
mysql> DESCRIBE file_name;
- 退出mysql
mysql> exit
- 关键字和函数名不区分大小写 为了直观 关键字大写 其他小写
mysql> CREATE DATABASE test_db; 创建一个名为 test_db 的数据库
mysql> CREATE DATABASE IF NOT EXISTS test_db;
mysql> CREATE DATABASE IF NOT EXISTS test_db_char 创建一个测试数据库，命名为 test_db_char
-> DEFAULT CHARACTER SET utf8 指定其默认字符集为 utf8
-> DEFAULT COLLATE utf8_chinese_ci; 默认校对规则为 utf8_chinese_ci（简体中文，不区分大小写）
mysql> SHOW CREATE DATABASE test_db; 查看 test_db 数据库的定义声明
mysql> ALTER DATABASE test_db
-> DEFAULT CHARACTER SET gb2312 指定字符集修改为 gb2312
-> DEFAULT COLLATE gb2312_chinese_ci; 默认校对规则修改为 gb2312_unicode_ci
mysql> DROP DATABASE test_db; 删除数据库 test_db
mysql> DROP DATABASE IF EXISTS test_db;
mysql> SHOW DATABASES LIKE 'test_db'; 查看与 test_db 完全匹配的数据库
mysql> SHOW DATABASES LIKE '%test%'; 查看名字中包含 test 的数据库
mysql> SHOW DATABASES LIKE 'db%'; 查看名字以 db 开头的数据库
mysql> SHOW DATABASES LIKE '%db'; 查看名字以 db 结尾的数据库
mysql> ALTER TABLE old_name RENAME TO new_name; 修改表名
mysql> SELECT name,height FROM tb_students_info ORDER BY height DESC,name ASC;
选择 排序 降序 升序
mysql> SELECT name,age,height FROM tb_students_info
-> WHERE age>21 AND height>=175;
mysql> SELECT name FROM tb_students_info
-> WHERE name LIKE '____y'; 查找所有以字母“y”结尾，且“y”前面只有 4 个字母的学生姓名
mysql> SELECT name,age FROM tb_students_info
-> WHERE age BETWEEN 20 AND 23; 查询年龄在 20 到 23 之间的学生姓名和年龄
mysql> SELECT `sex`, GROUP_CONCAT(name) FROM tb_students_info GROUP_CONCAT() 函数会把每个分组的字段值都显示出来
-> GROUP BY sex; 分组
mysql> SELECT sex,COUNT(sex) FROM tb_students_info COUNT() 函数计算每一组的记录数
-> GROUP BY sex;
mysql> SELECT sex,GROUP_CONCAT(name) FROM tb_students_info
->GROUP BY sex WITH ROLLUP; WITH ROLLUP 显示记录的总和
select
name as 姓名, # as 重命名列名
case sex when 1 then '男' else '女' end as 性别, # case ... when .. then .. else .. end 1是男否则是女结束
birth as 生日
from student
where birth between '1980-1-1' and '1989-12-31';
截取字符串 https://www.jb51.net/article/173358.htm
select substring_index(input,split,index)
该字段 = ‘1,2,3’ 获取中间的值
mysql> select substring_index(substring_index(该字段, ',', 2) , ',', -1) from 表名;
mysql> select left(string, 3); # 左边数截前3个
mysql> select right(string, 3); # 右边数截前3个
mysql> select substring(string, pos, length); # 从pos位置截length长度
年龄计算
floor(datediff(curdate(), stu_birth) / 365)
floor ：向下取整
datediff：计算时间差
curdate()：获取当前时间
连接查询
select cou_name, cou_credit, tea_name from tb_course t1
inner join tb_teacher t2 on t1.tea_id=t2.tea_id;
统计查到的结果数目
select count(*) from example_table where 条件
显示查到的结果前5行
select * from example_table where 条件 limit 5
python pymysql 调用数据库数据
import pymysql
db = pymysql.connect(host='localhost', user='', passwd='', database='')
cursor = db.cursor()
sql_string = 'select * from database where pos between 100 and 200'
cursor.execute(sql_string)
一行一行读取返回值
while True:
row = cursor.fetchone() 元组
if not row:
break
一次性读取返回值
results = cursor.fetchall() # [(), ()]
本地就写 host='localhost'，非本地就 host='地址'
