Mysql基本使用指南

阅读原文时间：2023年07月08日阅读：3

一. 记录操作

子查询
select * from students where (age, height) = (select max(age), max(height) from students);

添加
insert into students(name, height) values('刘备', 1.75),('曹操', 1.6); insert into students values(0, 'xx', default, default, '男');

删除
delete from students where id=5;

逻辑删除
alter table students add is_delete bit default 0; update students set is_delete = 1 where id = 8;

修改
update students set age = 18, gender = '女' where id = 6;

去重
select distinct name, gender from students;

模糊查询
like模糊查询字符；%多个；_任意一个（在哪里星号表示多个，？表示1个） delete from table where field like '%关键词1%' or like '%关键词2%' or like '关键词3';(同not like) regexp正则匹配；^开头，$结尾，.*多个 SELECT * FROM a_code_log WHERE account REGEXP '^1312.*7160$' select * from table where field regex 'world1|world2|world3';(同not regexp);

范围查询
between .. and.. in(…,…,)

排序
order by id asc|desc [,name asc|desc,…]

分页查询
limit 起始位置,记录数，limit 0,5 同limit 5。注意写在最后面。

聚合函数
count(col)、max(col)、min(col)、sum(col)、avg(col)

小数位数
round(avg(price),2)

范围
age<10 and age >8; between…and

判空
is null、is not null，不要写成了=null。

不等于
!=同<>，不要写16<age<18，会返回所有的。只有python可以这样写。

ifnull函数
select avg(ifnull(height,0)) from…若为空使用自己提供的值。

分组
select gender,avg(age) from students group by gender;

分组集合
select gender,group_concat(name) from students group by gender;

分组过滤
只能用于group by后面。

汇总
with rollup，会在记录后面新增统计行，最后写。

内连接
select * from students as s inner join classes as c on s.cls_id = c.id;

左(外)连接
left join以左表为主查询右表数据，右表数据不存在用null填充。

自连接
inner join，把一张表模拟成左右两张表，然后进行连表查询，表还是本身。

注意点：内连接是求交集；左连接返回左表若右表缺值null填充；用左连接还是右连接看你想返回哪个表；自连接用于乱七八糟的表，如省市表（aid，省市区街，pid（依存关系）），可以自连接很多次。

缓存查询
WITH t1 AS (SELECT file_id FROM UTT_DOWNLOAD_FILE), t2 AS (SELECT FILE_TYPE FROM UTT_DOWNLOAD_FILE), t3 AS (SELECT CUST_ID FROM UTT_DOWNLOAD_FILE)；SELECT * FROM t1,t2,t3；

二. 表操作

创建表
CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号', name VARCHAR(200) COMMENT '姓名', age int COMMENT '年龄') COMMENT='学生信息'

修改表注释
ALTER TABLE student COMMENT '学生表';

修改列注释
ALTER TABLE student MODIFY COLUMN name VARCHAR(100) COMMENT '姓名'; SELECT table_name,table_comment FROM information_schema.tables WHERE table_schema = '所在的db' AND table_name ='表名groups'

SHOW FULL COLUMNS FROM groups可以按条件的去搜索某名字或某数据类型的列的信息

SHOW FULL COLUMNS FROM tableName WHERE FIELD = 'add_time' OR TYPE LIKE '%date%'

查看tableName表中列名是add_time的或类型是date的列
SELECT column_name, column_comment FROM information_schema.columns WHERE table_schema ='db' AND table_name = 'groups'SHOW CREATE TABLE communication_group

ALTER table 表名 MODIFY (字段名字段类型(长度));

表中未存有数据
ALTER TABLE 表名 MODIFY 字段名字段类型（长度）;

某列不重复
alter table api_test modify url varchar(200) UNIQUE CREATE TABLE admin (id int(10) NOT NULL AUTO_INCREMENT ,username varchar(50) NOT NULL ,password varchar(50) NOT NULL ,table_name varchar(50) NOT NULL ,PRIMARY KEY (id),UNIQUE KEY username (username))

添加字段
alter table students add birthday datetime;

修改字段
alter table students modify jixiangwu mascot varchar(20); （modify修改字段类型或约束(表有内容了只能改相近的，不能char改int，只能改varchar，改别的先删掉内容再改。）

修改字段名
alter table students change birthday birth datetime not null;

删除字段
alter table students drop birthday;

删除表
DROP TABLE IF EXISTS sites;(数据和结构全删)

删数据
truncate table student;(保留表结构)

表其它
show create table students; drop table students;desc students(看表结构)。

清空数据id从1开始：
TRUNCATE TABLE tablename

设置id初始值：
ALTER TABLE tableName AUTO_INCREMENT=10000

三. 库操作

show database

CREATE DATABASE [IF NOT EXISTS] teark charset=utf8

DROP DATABASE [IF EXISTS] teark

ALTER DATABASE mydb1 CHARACTER SET utf8;

use teark

select database()

select version()

select now()

show create database teark

SHOW ENGINESSHOW VARIABLES LIKE 'storage_engine

'ctrl+a行首，ctrl+e行尾，ctrl+c结束。

net start/stop mysqlquit/exit

show global variables like 'port'

mysql -uroot -p < test.sql 导入

mysqldump -u root -p db1> db1.sql导出（若报字符集错，在mysqldump后加--default-character-

set=utf8 -h localhost）

四. 外键

　　作用？

　　　　对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证(到外键所在的那个表里面去找有没有插入的这个种类)，数据如果不合法则更新和插入会失败，保证数据的有效性和完整性。非要删用on_delete，可级联删可从表null可默认值。

　　外键约束？

　　　　创建时设置外键约束放在最后一行，对于已经存在的字段添加外键约束：alter table students add foreign key(cls_id) references classes(id);

　　删除外键约束？

　　　　alter table teacher drop foreign key 外键名; ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, …) REFERENCES tb_name (index_col_name,…) [ON DELETE reference_option] [ON UPDATE reference_option]

五. 索引

　　广义来说PRIMARY KEY, FOREIGN KEY, KEY/INDEX, UNIQUE都叫索引，狭义说就是KEY/INDEX。

　　优点是提高了查询的速度，缺点是降低了增删改的速度。

　　一个字段经常出现在where里面适合建立索引。多样性（）越多越适合建立索引。

　　show index from …; alter table t_name add index i_name)(可选) (字段名…) drop index name on table;

六. 高级语句

插入查询结果
insert into good_cates(name) select cate_name from goods group by cate_name;

用连接更新字段名
update goods g inner join good_cates gc on g.cate_name=gc.name set g.cate_name=gc.id;

创建表并给某个字段添加数据
create table good_brands (id int unsigned primary key auto_increment,name varchar(40) not null) select brand_name as name from goods group by brand_name;

修改表结构
alter table goods change cate_name cate_id int not null, change brand_name brand_id int not null;

视图
create view v_name as select …; show tables; drop view;

事务四大特性(简称ACID)
使用事务？
start transaction;/begin;
select balance from checking where customer_id = 10233276;
update checking set balance = balance - 200.00 where customer_id = 10233276;
update savings set balance = balance + 200.00 where customer_id = 10233276;
commit;

七. profile

　　执行sql语句可以看到时间？

　　set profiling=1

　　查看上面的语句花费多少时间？

　　show profiles

　　关闭时间检测，不然执行的sql都会显示记录时间！

　　set profiling=0

八. ORM（建库方式）

1. 全部纯sql语句方式

　　一个sql语句七八百行！2.借助orm。orm作用：省去自己拼写SQL，保证SQL语法的正确性。一次编写可以适配多个数据库。防止注入攻击。在数据库表名或字段名发生变化时，只需修改模型类的映射，无需修改数据库操作的代码。

2. 模型类+映射

　　先创建模型类，再迁移到数据库中即创建模型类，迁移通过模型类进行数据操作。简单快捷但不能控制所有细节，表结构发生变化的时候，也会难免发生迁移错误。SQL创建数据库表，再编写模型类作映射。避免了迁移错误。如SQLAlchemy映射：不亚于Django的ORM框架。见Flask-SQLAlchemy篇

九. 后记

两种方法去重？

　　distinct和…

关于group by

　　最好和聚合函数一起用，不对称的数据用group_concat。后面再进行筛选不能用where用having，再想删选就在group by之前筛选。

批量执行sql

　　source home/…/a.sql(可以直接拖进来自动形成路径) 视图只能查。

create as

　　注意视图的as不同于别的as。

设计者

　　数据库的设计得是有经验的，参与设计框架的。数据库开发的程序员掌握power designer；db designer。 n-n必须转换成1-n，来个关系表，只有两个字段即两个主键。

Mysql数据库用户授权

　　使用Grant命令。导入sql文件，source 文件名。

数据库引擎

InnoDB

　　具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储引擎，支持外键。

MyISAM

　　AUTO_INCREMENT列当插入修改时会被更新，因此比InnoDB类型的AUTO_INCREMENT更快；不支持事务。创建数据库将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

MEMORY

　　数据存储到内存，为查询和引用其他表数据提供快速访问，类似redis。适合只是临时存放数据，数据量不大，并且不需要较高的数据安全性。MySQL中使用该引擎作为临时表，存放查询的中间结果。

关于引擎

　　决定数据的保存方式和sql的执行方式。扫描每条记录占多少字节，写入到文件。一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求* *

专业术语

　　DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

　　DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

　　DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

常见MySQL数据类型

CAHR(Length) Length字节定长字段，长度为0~255个字符
VARCHAR(Length) String长度+1字节或String长度+2字节变长字符，长度为0~65 535个字符
TINYTEXT String长度+1字节字符串，最大长度为255个字符
TEXT String长度+2字节字符串，最大长度为65 535个字符
MEDIUMINT String长度+3字节字符串，最大长度为16 777 215个字符
LONGTEXT String长度+4字节字符串，最大长度为4 294 967 295个字符
TINYINT(Length) 1字节范围：-128~127，或者0~255（无符号）
SMALLINT(Length) 2字节范围：-32 768~32 767，或者0~65 535（无符号）
MEDIUMINT(Length) 3字节范围：-8 388 608~8 388 607，或者0~16 777 215（无符号）
INT(Length) 4字节范围：-2 147 483 648~2 147 483 647，或者0~4 294 967 295（无符号）
BIGINT(Length) 8字节范围：-9 2　　23 372 036 854 775 808~9 223 372 036 854 775 807，或者0~18 446 744 073 709 551 615（无符号）
FLOAT(Length, Decimals) 4字节具有浮动小数点的较小的数
DOUBLE(Length, Decimals) 8字节具有浮动小数点的较大的数
DECIMAL(Length, Decimals)Length+1字节或Length+2字节存储为字符串的DOUBLE，允许固定的小数点DATE 3字节采用YYYY-MM-DD格式DATETIME 8字节采用YYYY-MM-DD HH:MM:SS格式TIMESTAMP 4字节采用YYYYMMDDHHMMSS格式；可接受的范围终止于2037年TIME 3字节采用HH:MM:SS格式
ENUM 1或2字节 Enumeration(枚举)的简写，这意味着每一列都可以具有多个可能的值之一
SET 1、2、3、4或8字节与ENUM一样，只不过每一列都可以具有多个可能的值注意：int(20)里面代表显示位数，而不是只能存储这么多位。