MySQL基础操作
MySQL 数据类型
字符类型
CHAR, 1～255个字符的定长串。它的长度必须在创建时指定，否则MySQL假定为CHAR(1)
ENUM, 接受最多64 K个串组成的一个预定义集合的某个串
VARCHAR, 长度可变，最多不超过21845
TEXT, 最大长度为64 K的变长文本
TINYTEXT, 最大长度为255字节
MEDIUMTEXT, 最大长度为16K
LONGTEXT, 最大长度为4GB
数值类型
二进制
BIT, 位字段，1～64位。
BOOLEAN, 布尔标志，或者为0或者为1
整数
TINYINT, 整数值，-128～127
SMALLINT, 整数值，-32768～32767
INT, 整数值，-2147483648～2147483647
BIGINT, 整数值，-9223372036854775808～9223372036854775807
浮点数
DECIMAL, 精度可变的浮点值，精确的。
FLOAT, 单精度浮点值
DOUBLE, 双精度浮点值
时间类型
DATE, 格式为YYYY-MM-DD，(1000-01-01/9999-12-31)
TIME, 格式为HH:MM:SS
YEAR, 用2位数字表示，范围是70（1970年）～69（2069年），用4位数字表示，范围是1901年～2155年
DATETIME, YYYY-MM-DD HH:MM:SS，(1000-01-01 00:00:00/9999-12-31 23:59:59)
TIMESTAMP, 功能和DATETIME相同（但范围较小）(1970-01-01 00:00:00/2037-12-31 23:59:59)
二进制类型
BLOB, 最大长度为64KB
MEDIUMBLOB, 最大长度为16MB
LONGBLOB, 最大长度为4GB
TINYBLOB, 最大长度为255字节
用户管理
mysql数据库有一个名为user的表，它包含所有用户账号信息。IP地址默认为%
查询用户
select user,host from mysql.user;
创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
重新命名
rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
修改密码
set password for '用户名'@'IP地址' = Password('新密码')
权限管理
查看权限
show grants for '用户'@'IP地址'; show grants for sadmin;
用户授权
grant 权限 on 数据库.表 to '用户'@'IP地址'; --grant允许用户admin在django的所有表中有select的权限。 grant select on django.* to admin; --all处了grant以外的所有权限 grant all on django.* to admin;
用户撤权
revoke 权限 on 数据库.表 from '用户'@'IP地址'; revoke select on django.* from admin;
库操作
查看数据库
show databases;
创建数据库
create database 数据库名;
使用数据库
use 数据库名;
删除数据库
drop database 数据库名;
表操作
创建表
CREATE TABLE customers ( cust_id int NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL, cust_age int NOT NULL DEFAULT 25, cust_address char(50) NULL, cust_city char(50) NOT NULL DEFAULT 'local', cust_country char(50) NULL, PRIMARY KEY (cust_id) --设置主键cust_id ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 主键值必须唯一，即表中的每个行必须具有唯一的主键值。
- 每个表只允许一个AUTO_INCREMENT列，而且必须被索引，如成为主键。
- MySQL只支持常量作为默认值。
- InnoDB是一个可靠的事务处理引擎，不支持全文本搜索。
- MyISAM是一个性能极高的引擎，支持全文本搜索，但不支持事务处理。
- 引擎可以混用，但是外键不能跨引擎，即使用一个引擎的表不能引用不同引擎的外键。
重新命名
rename table 表名 to 新表名;
删除表
drop table 表名;
清除表内容，表还保存
delete from 表名; truncate table 表名;
查看表
show tables;
查看表结构
describe 表名;
修改表结构
-- 添加字段 alter table 表名 add 字段名 字段类型; -- 删除字段 alter table 表名 drop 字段名; -- 修改字段 alter table 表名 change column 字段名 新字段名 类型 NOT NULL DEFAULT 值; alter table 表名 modify column 字段名 新类型 NOT NULL; -- 修改默认值 alter table 表名 alter 字段名 set default 值; -- 删除默认值 alter table 表名 alter 字段名 drop default;
主键
-- 添加主键 alter table 表名 add primary key(字段名); -- 删除主键 alter table 表名 modify 字段名 int; --先取消AUTO_INCREMENT alter table 表名 drop primary key; --删除主键
外键
引用一个表中的一列或多列数据，被引用的列应该具有主键约束或者唯一性约束。外键用来建立和加强两个表数据之间的连接。
-- 添加外键 alter table 表名 add constraint 外键名fk_从_主 foreign key (字段) references 外表名 (外表字段); -- 删除外键 alter table 表名 drop foreign key 外键名称
引擎
-- 修改表引擎 ALTER TABLE customers ENGINE = MYISAM;
表内容操作
增
MySQL用单条INSERT语句处理多个插入，比用多条INSERT语句快，提高数据库处理的性能。
insert into 表 (列名,列名...) values (值,值...); -- 插入单行数据 insert into 表 (列名,列名...) values (值,值...),(值,值...); -- 插入多行数据 insert into 表1 (列名,列名...) select (列名,列名...) from 表2; -- 插入SELECT返回的行
删
delete from 表; -- 删除表中所有行 delete from 表 where id＝1; -- 删除条件匹配的行
改
如果用UPDATE更新多行，并且更新时出现一个错误，则整个UPDATE操作会被取消，所有行恢复到原来的值；使用IGNORE可以忽略这些错误，update ignore 表名...
update 表名 set 列名 = 值 where id = 1; -- 更新单列 update 表名 set 列名1 = 值, 列名2 = 值 where id = 1; -- 更新多列
查
select * form 表名; select name, age from 表名;
限制limit
select * from 表名 limit 2; -- 前2行 select * from 表名 limit 2,3; -- 从第3行开始的3行 select * from 表名 limit 5 offset 3; -- 从第4行开始的5行
条件判断where
WHERE匹配条件，=，!=，<>，<，<=，>，>=，BETWEEN AND，IS NULL判断值是否为空。AND和OR的用法，多个操作符组合括号用法，IN (start,end)范围操作符，NOT非操作符，可以与IN、BETWEEN、EXISTS组合。
select * from 表名 where name = 'Alice'; -- name为Alice的行 select * from 表名 where age > 25 and name = 'Alice'; -- age大于25并且name为Alice的行 select * from 表名 where name is null; -- name为空的行 select * from 表名 where age between 25 and 35; -- age在25和35之间的行 select * from 表名 where age not in (25,35); -- age不在25-35之间的行
通配符like
LIKE是WHERE子句，%任意个任意字符，_一个任意字符，区分大小写，NULL不会被匹配。通配符影响性能，尽量少使用，避免开头使用通配符。
select * from 表名 where name like 'Ali%'; -- name为Ali开头的行 select * from 表名 where name like 'Ali_'; -- name为Ali后一个字符的行
排序order by
可以根据多个列排序，asc正向排序，desc反向排序
select * from 表名 order by age asc; -- age正向排序 select * from 表名 order by age desc; -- age反向排序 select * from 表名 order by age asc, num desc; -- 先对age正向，age中有重复的行，再根据num反向排序
分组group by
having过滤分组
select age from 表名 group by age; -- 根据age进行分组 select age from 表名 group by age having count(1) > 2; -- 对age分组后每组中成员数大于2的age列
聚合函数
avg(column)平均值，只用于单个列，列名必须作为函数参数给出，忽略列值为NULL的行。
count(*) 对表中行计数，包含null，count(列) 对列中有值行计数，不包含NULL。
max(),min()最大最小值，也会返回文本列中的。
sum()指定列值的和，忽略列值为NULL的行。
distinct去重函数，后面必须使用列名，不能用于计算或表达式。
select avg(age), max(age), min(age) from 表名; -- 查询表中age的平均值，最大值，最小值 select count(age) from 表名 where age > 20; -- 查询age大于20的行数 select distinct age from 表名; -- 查询去重后的age
SELECT子句顺序
- SELECT 要返回的列或表达式
- FROM 从中检索数据的表
- WHERE 行级过滤
- GROUP BY 分组说明
- HAVING 组级过滤
- ORDER BY 输出排序顺序
- LIMIT 要检索的行
-- user_id大于5，对class_id分组，再提取组成员大于1，然后对class_id进行反向排序，最后限制1-3行 select class_id from user_info where user_id > 5 group by class_id having count(1) > 1 order by class_id desc limit 2 offset 0;
连表
LEFT JOIN（左连接）：获取左表所有记录，右表没有对应匹配显示NULL。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
INNER JOIN 内连接查询: 显示两个表共有的数据。
-- LEFT JOIN select * from 表1 left join 表2 on 表1.字段 = 表2.字段; -- 以表1的条件字段为主进行连表，只查询表1中有的，如果表2中没有则为NULL select * from 表1 left join 表2 on 表1.字段 = 表2.字段 left join 表3 on 表1.字段 = 表3.字段; -- 以表1为主，表2表3连接进去 -- INNER JOIN select * from 表1 inner join 表2 on 表1.字段 = 表2.字段 inner join 表3 on 表1.字段 = 表3.字段; -- 只显示表1表2表3中，条件字段存在且不为NULL的行
