MySQL总结01

window删除MySQL服务

• cmd下执行
  sc delete MySQL

登陆登出

• 登陆：
  mysql -uroot -ppasswd -h host

• 退出登陆
  mysqladmin -uroot -ppasswd shutdown
  note: 在mysql外面执行

linux修改管理员root密码

• 外部命令修改
  mysqladmin -uroot -poldpasswd password newpasswd

• MySQL内部修改
  update mysql.user set password=password(newpasswd) where user='root' and host='localhost';

windows修改root用户密码

• 方法1： 用SET PASSWORD命令
  首先登录MySQL。
  格式：mysql> set password for 用户名@localhost = password('新密码');
  例子：mysql> set password for root@localhost = password('123');

• 方法2：用mysqladmin
  格式：mysqladmin -u用户名 -p旧密码 password 新密码
  例子：mysqladmin -uroot -p123456 password 123

• 方法3：用UPDATE直接编辑user表
  首先登录MySQL。
  mysql> use mysql;
  mysql> update user set password=password('123') where user='root' and host='localhost';
  mysql> flush privileges;

• 方法4：在忘记root密码的时候，可以这样
  以windows为例：

1. 关闭正在运行的MySQL服务。
2. 打开DOS窗口，转到mysqlin目录。
3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysqlin目录。
5. 输入mysql回车，如果成功，将出现MySQL提示符 >。
6. 连接权限数据库： use mysql; 。
7. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。
8. 刷新权限（必须步骤）：flush privileges;　。
9. 退出 quit。
10. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

库操作

• 创建普通库，此库的字符集和MySQL的字符集一致
  create database dbname

• 创建特定字符集的字符串，以utf8为例
  create database dbname character set utf8 collate utf8_general_ci

• 查看数据库支持的字符集
  show variables like '%set%';

• 查看所有数据库
  show databases;

• 使用库
  use databasename;

• 删除库
  drop database dbname;

• 查看当前所在库
  select database();

• 查看创库语句（里面包含库的字符集和引擎信息）
  show create database dbname;

用户操作并授权

• 创建用户
  create user username@host identified by passwd;

• 删除用户
  drop user username@host;

• 查看当前所在的用户
  select user();

• 授权
  grant priviledges on dbname.* to username@host;
  note: 权限包括select,insert,update,create,drop,peferences,idex, alter,idex,create temporary tables,lock tables,
  execute,create view, show view,show view,create routine,alter routine, event, trigger

• 查看用户权限
  show grants for username@host;

• 回收用户权限
  revoke privileges on dbname.* from username@host;

• 刷新权限
  flush privileges

授权局域网内主机远程连接数据库

• 百分号法
  grant all on privileges *.* to dbname@10.0.0.% identified by passwd;

• 子网掩码配置法
  grant all on *.* to dbname@255.255.255.0 identified by passwd;

索引

查询数据库，按照主键索引查询最快，每个表只能有一个主键列，但是可以有多个普通索引列。
主键列要求列的所有内容必须唯一，而索引列不要求内容必须唯一

• 给表字段增加索引
  • 建表时添加

create table tablename(
 id int(4) not null auto_increment,
 name char(20) not null,
 primary key(id),  --主键索引
 key index_name(name) --普通索引`
);

• 见表之后增加索引
  alter table tablename add index index_name(name);

• 利用alter命令修改id列为自增主键索引列
  alter table tablename change id id int primary key auto_increment;

• 建立普通索对字段前n个字符创建普通索引
  create index index_name on tablename(name);
create index index_name on tablename(name(3));
  note:对前n个字符创建索引的好处时可以节省内存空间，前提时前n个字符可以确定字段唯一

• 创建联合索引
  create index index_name on tablename(name, age);
  note:联合索引上的字段可以有自己的索引且条件查询数据时，联合索引采用前缀生效特征，
  即：联合索引有a,b,c，仅a,ab,abc三个查询条件列可以走索引，ac就不可以

• 创建唯一索引，作用时保证数据的唯一性
  create unique index uni_index_name on tablename(name);

• 查看索引
  show index from tableG
  note:G让展示内容垂直分布

• 删除索引
  alter table tablename drop index index_name;

• 索引创建及生效条件

索引相当于文章的目录，好处是加快查询速度，但是占用系统空间，更新数据库是还要维护索引数据。
因此数十行到几百行的小表无需建立索引；写频繁，读少的业务要少建索引

• 建索引的位置

索引一定要创建在条件列，而不是select后的选择数据的列。另外要尽量选择在唯一值多的大表上建立索引

表

• 表操作

  • 查看所有表
    show tables;

  • 查看创表语句
    show create table tablename;

  • 查看表结构
    desc tablename;

  • 修改表名
    ALTER TABLE tablename RENAME [TO] new_tablename
    或者
    rename table oldname to newname;

• 创建外键
  在创表时添加外键关联
  constraint fk_authorities_users foreign key(username) references users(username)
  当创建好数据表时添加外键约束
  alter table user add foreign key(pid) references province(pId);

• 表DDL

  • 修改表类型
    ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
    eg：alter table person modify name varchar(30) after age;

  • 增加表字段
    ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
    eg: alter table person add classes varchar(6) after name;

  • 删除表字段
    ALTER TABLE tablename DROP [COLUMN] col_name
    eg: alter table person drop column name;

  • 字段改名
    ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
    eg: alter table tablename chage column age age1 int(4);
    note: change可以修改列名称，modify 则不能

• 表DML

  • 增
    INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn);
INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn), (value1,value2,……valuesn)...;

  • 删
    delete from tablename where 条件;
drop table tablename;
truncate tablename;

二者区别：truncate删除表的速度更快，因为truncate时通过物理磁盘删除的；而drop是通过逻辑一行一行删除的。

• 改
  update tablename set field1=value1, field2=value2,... [where condition];

• 查
  slect field1, field2,... from tablename [where condition];
  检查查有没有走索引的方法
  explain sql语句