-
常规操作;
show databases; use 数据库名称; show tables; select * from 表名; select name,age,id from 表名
-
创建用户:
create user 'alex'@'192.168.1.1' identified by '123123'; create user 'alex'@'192.168.1.%' identified by '123123'; create user 'alex'@'%' identified by '123123';
-
授权:
grant select,insert,update on db1.t1 to 'alex'@'%'; grant all privileges on db1.t1 to 'alex'@'%'; revoke all privileges on db1.t1 from 'alex'@'%';
-
操作文件夹(创建数据库):
create database db2; create database db2 default charset utf8; 中文 drop database db2;
-
操作文件(创建表):
create table t1(id int,name char(10)) default charset=utf8; create table t1(id int,name char(10))engine=innodb default charset=utf8; create table t3(id int auto_increment,name char(10))engine=innodb defaultcharset=utf8;
innodb 支持事务,原子性操作 支持回滚
myisam myisam
auto_increment 表示:自增
primary key: 表示 约束(不能重复且不能为空); 加速查找
not null: 是否为空create table t1( id int signed not null auto_increment primary key, num decimal(10,5), name char(10) )engine=innodb default charset=utf8; 清空表: delete from t1; truncate table t1; 删除表: drop table t1;
-
操作文件中内容(表内容)
插入数据: insert into t1(id,name) values(1,'alex'); 删除: delete from t1 where id<6 修改: update t1 set age=18; update t1 set age=18 where age=17; 查看数据: select * from t1;
-
外键:
create table userinfo( uid bigint auto_increment primary key, name varchar(32), department_id int, xx_id int, ******* constraint fk_user_depar foreign key (department_id) references color(id) )engine=innodb default charset=utf8; create table department( id bigint auto_increment primary key, title char(15) )engine=innodb default charset=utf8;
-
补充:主键
一个表只能有一个主键 主键可以由多列组成
-
补充:外键 ?
CREATE TABLE t5 ( nid int(11) NOT NULL AUTO_INCREMENT, pid int(11) not NULL, num int(11), primary key(nid,pid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 想要外键关联两个,主键也要两个 create table t6( id int auto_increment primary key, name char(10), id1 int, id2 int, CONSTRAINT fk_t5_t6 foreign key (id1,id2) REFERENCES t1(nid,pid) )engine=innodb default charset=utf8;
-
对于自增补充:
desc t10; 看每个字段都是啥意思 show create table t10; 看表的创建过程 show create table t10 \G; 竖着看 alter table t10 AUTO_INCREMENT=20; 设置自增的起始id
-
MySQL: 自增步长(一次增加几个id)不能安表设置
基于会话级别:(窗口) show session variables like 'auto_inc%'; 查看全局变量 set session auto_increment_increment=2; 设置会话步长 基于全局级别: show global variables like 'auto_inc%'; 查看全局变量 set global auto_increment_increment=2; 设置会话步长
-
SqlServer:自增步长(它能按表设置步长)
基础表级别: CREATE TABLE `t5` ( `nid` int(11) NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `num` int(11) DEFAULT NULL, PRIMARY KEY (`nid`,`pid`) ) ENGINE=InnoDB AUTO_INCREMENT=4, 步长=2 DEFAULT CHARSET=utf8
-
唯一索引
create table t1( id int ...., num int, xx int, unique 唯一索引名称 (列名,列名), 联合唯一索引 ) 作用: 约束不能重复(可以为空)PS: 主键不能重复(不能为空) 加速查找
通配符:
select * from tb12 where name like "a%" 首字母有a的 %:任意多个字符
select * from tb12 where name like "a_" —:一个字符
分页:
select * from tb12 limit 10; 前10条
select * from tb12 limit 0,10; 从0开始,往后10条
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset 20; 从20开始,往后10条
# page = input('请输入要查看的页码')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1 把上条结果放在0的位置
# select * from tb12 limit 10,10;2
分页*
a. select * from userinfo3 limit 20,10;
b.
- 不让看
- 索引表中扫:
select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)
- 方案:
记录当前页最大或最小ID
1. 页面只有上一页,下一页
# max_id
# min_id
下一页:
select * from userinfo3 where id > max_id limit 10;
上一页:
select * from userinfo3 where id < min_id order by id desc limit 10;
2. 上一页 192 193 [196] 197 198 199 下一页
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
)
排序:
select * from tb12 order by id desc; 从大到小
select * from tb12 order by id asc; 从小到大
select * from tb12 order by age desc,id desc; 重复的age按照id排
取后10条数据
select * from tb12 order by id desc limit 10;
分组:
select count(id),max(id),part_id from userinfo5 group by part_id
#选出的表含有count(id),max(id),part_id #按照 part_id 分组 重复的会合并
聚合函数:count ,max,min,sum,avg
**** 如果对于聚合函数结果进行二次筛选时?必须使用having ****(不是where)
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
-
连表操作:
select * from userinfo5,department5 where userinfo5.part_id = department5.id #那两张表 + 表的关系 select * from userinfo5 left join department5 on userinfo5.part_id = department5.id #userinfo5左边全部显示(左表4行,右表5行,只显示4行) select * from userinfo5 right join department5 on userinfo5.part_id = department5.id # department5右边全部显示 select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id #将出现null时一行隐藏
左右连表: join
上下连表: union# 自动去重 select id,name from tb1 union select num,sname from tb2 # 不去重 select sid,sname from student UNION ALL select sid,sname from student