MySQL
目录
安装
下载
https://dev.mysql.com/downloads/mysql/
初始化
- exe格式安装不需要
- 初始化完成后默认用户名：root 默认密码：空
mysqld --initialize-insecure
启动服务端
- exe格式安装不需要
mysqld
启动客户端
mysql -u root -p
Enter password:输入密码
SQL
数据库操作
创建数据库
create database 数据库名;
create database db1;
删除数据库
drop database 数据库名;
drop database db1;
切换数据库
use 数据库名;
use db1;
表操作
创建表
create table 表名(
字段名 类型,
字段名 类型 [是否可以为空 null | not null],
字段名 类型 [默认值 default 默认值],
字段名 类型 [[自增 auto_increment] 主键 primary key], // auto_increment 和 primary key 必须同时使用，且每个表只能有一个auto_increment
[constraint 外键名] foreign key 从表(字段名称) references 主表(字段名称),
[primary key (字段1，字段2,....)]//设置主键，可以设置多个字段
[unique 索引名称 (字段名,...)]//设置唯一索引
.......);
示例：
create table t1(id int, name char(10));
create table t1(id int null, name char(10));
create table t3(id int auto_increment primary key, name char(10)) engine=innodb default charset=utf8;
create table t1(id int, name char(10)) engine=innodb default charset=utf8;//引擎为innodb 默认字符编码为utf8
//innodb 支持事务（原子性操作）
constraint fk_score_student foreign key score(student_id) references student(sid)
查询数据
select 列 from 表名;
select * from t1;
select 列名 as 别名 from 表名;
select tname as "姓名" from teacher;
select 列名 from 表名 where 条件;
select * from t1 where name="张三";
select * from t1 where name like "张%";//查询姓名以张开头的人的所有信息
select * from t1 where name like "张_";//查询姓名以张开头的并且名只有一个字的人(例如张三，张四)的所有信息
select 列名 from 表名 limit 数量;//只显示查询的到前多少条
select * from t1 limit 10;// 只显示前十条
select 列名 from 表名 limit 起始值,数量;//只显示查询的从起始值开始的多少条信息
select * from t1 limit 5,10;// 只显示从第五条开始的十条信息
select count(*) as 数量 from 表名 group by 字段名;//分组
select gender,count(*) as "数量" from student group by gender;//按性别分组统计人数
select * from 表1,表2 where 关联条件; //多表查询
select * from course,teacher where course.teacher_id = teacher.tid;
select * from 表1 left join 表2 on 关联条件;// 多表查询
select * from course left join teacher on course.teacher_id = teacher.tid;
插入数据
insert into 表名(字段名，字段名,.....)values(数据,数据),(数据，数据) //数据个数与字段名个数对应
insert into t1(id,name)values(1,"a");
insert into t1(字段1，字段2) select 字段1,字段2 from t2;
insert into t1(id,name) select id,name from t2;;
更新数据
update 表名 set 更新内容 where 条件;
update t1 set id=3 where id=1;
alter table 表名 AUTO_INCREMENT=数值; //修改表的自增值
alter table t1 AUTO_INCREMENT=30;
删除数据
delete from t1 where 条件;
delete from t1 where name='张三';
清空表
- 清空表里的内容，并不删除表
delete from 表名;//不会清空自增值
delete from t1;
truncate table 表名;//会清空自增值，速度更快
truncate table t1;
删除表
drop table 表名;
drop table t1;
查看
查看数据库
show databases;
查看表
show tables;
查看表结构
desc 表名;
查看表的创建方法
show create table 表名;
查看用户
use mysql;
select user from user;
用户管理
创建用户
create user '用户名'@'可登陆的ip地址' identified by '密码';
示例：
create user 'a'@"localhost" identified by '123'; //创建一个名字叫a的账户，只允许本地登陆，密码123
create user 'b'@'192.168.1.1' identified by '123'; //创建一个名字叫b的账户，只允许ip为192.168.1.1登陆，密码123
create user 'c'@'192.168.%' identified by '123'; //创建一个名字叫c的账户，只允许ip为192.168.*.*的地址登陆，密码123
create user 'd'@'%' identified by '123'; //创建一个名字叫a的账户，允许所有ip登陆，密码123
权限管理
授权
grant 权限 on 数据库.表 to 用户@可登陆地址;
- 权限
| 权限名 | 作用 |
|---|---|
| select | 查找权限 |
| update | 更新权限 |
其他操作
视图
create view v1 as select * from teacher;//创建
alter view v1 as select * from student;//修改
drop view v1;//删除
索引
create index 索引名 on 表名(字段名);
create index ix_name on user(username);//普通索引
create unique ix_name on user(username);//唯一索引
drop index ix_name on user;
慢日志
show variables like "%query%"; // 查看有关查询的变量
设置
set global 参数;
set global slow_query_log = on;// 开启慢日志记录
set global long_query_time = 1;// 设置慢日志记录阈值
set global slow_query_log_file = "f:\\slow_query.conf"; //设置慢日志存储文件位置
set global log_queries_not_using_indexes = on; //设置没有命中索引记录
分页
- 直接使用limit来分页时，当页数过大时，查询会很慢
- limit 200000,10 会扫描表的前200000项，然后取10条，速度慢
- 可以通过程序记录上一次分页的id
- 使用 where id > 上一次分页的最后一个id 来实现不扫描前面的数据
上一页，下一页实现
select * from t1 where id > 200000 limit 10;// 下一页
SELECT * FROM student WHERE sid < 200000 ORDER BY id DESC LIMIT 10;// 上一页
跳转到指定页数
SELECT * FROM student WHERE sid IN(SELECT sid FROM (SELECT e.sid FROM (SELECT sid FROM student WHERE sid > 上一次记录的页数 LIMIT (目标页数-记录页数)*每页个数 ) AS e LIMIT 每页个数) as t);
SELECT * FROM student WHERE sid IN(SELECT sid FROM (SELECT e.sid FROM (SELECT sid FROM student WHERE sid > 5000 LIMIT 30) AS e LIMIT 10) as t);
