mysql & sql备忘

mysql & sql备忘

• ubuntu16.04安装 mysql

• 安装:
  sudo apt-get install mysql-server
• 查看是否安装成功
  ps -ef | grep mysql’
• 配置:
  打开配置文件
  sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
  ubuntu默认只能本地登录 须注释掉以下行
  bind-address = 127.0.0.1
  默认无法写入中文数据,需要把server的编码设置为utf8,在配置文件最后添加如下指令
  character_set_server = utf8
  设置完成保存退出,重启服务以生效
  sudo service mysql restart
• 登录
  mysql -uroot -p
• 查看编码
  show variables like ‘%char%’;
• 创建数据库
  create database mydb;
• 创建新用户 (%表示任意IP, by后面的是密码)
  create user ‘lxyz’@’%’ identified by ‘123456’;
• 授权 (*表示数据库中所有的表, %表示任意IP)
  grant all on mydb.* to 'lxzy'@'%';
• 刷新权限
  flush privileges;
• 退出,切换用户登录
  mysql -ulxyz -p -h192.168.0.103

• 登录mysql服务器

  mysql -u用户名 -h 服务器IP -p密码
  例：mysql -u aaa -h 127.0.0.1 -p

• 创建/删除 数据库

  create database xxx;
  drop database xxx;

• 切换数据库

  use 数据库名

• 显示所有的表

  show tables;

• 查看表相关信息

  desc 表名;
  show columns from 表名;//与desc功能相同
  show create table 表名;

• 数据库备份

  mysqldump -h主机名 -P端口 -u用户名 -p密码 [–databases] 数据库名 > 文件名.sql

• 还原与导入

  1.mysql -hhostname -uusername -ppassword databasename < backupfile.sql
  2.用source命令，用use进入到某个数据库，mysql>source d:\test.sql

• 从远程服务器查询结果 导出 到本地文件

  mysql -h 服务器IP -u 用户名 -p -D数据库名 -e “Select语句” > D:/db_back.sql

• 创建表时，设置主外键

  create table 表名1(
  列名 类型 primary key auto_increment,
  …
  );
  create table 表名2(
  列名 类型 primary key auto_increment,
  …
  foreign key(表2.列名) references 表名1(列名)
  );

• 删除主键：

  alter table your_table_name drop primary key;//这个是删除主键的指令(这里的主键不能是别的表的外键,否则删除这个主键的时候，你得先把他的外键删掉);

• 删除外键:

  alter table your_table_name drop foreign key your_foreign_key_id;
  alter table class drop foreign key fk_class_employee;
  //删除外键后，把对应的索引删除
  drop index index_name on talbe_name;
  drop index fk_class_employee on class;

• 增加主键:

  alter table your_table_name add primary key (your_primary_key_name);//最后边的那个()一定要有;

• 增加外键:

  alter table 需加外键的表 add constraint [外键名] foreign key(需加外键表的字段名) referencnes 关联表名(关联字段名);
  注意：外键名不能重复

• 修改列类型：

  alter table 表名 modify column 字段名 类型;

• 把src表所查询的东西，全部插入dest表中(表列对应)

  1.如果要插入目标表(dest)不存在：
  select * into dest from src where …;
  2.如果要插入目标表(dest)已经存在：
  insert into dest select * from src where …;
  3.如果是跨数据库操作的话：
  select * into B.dest from A.src where …**

• 更新表中 A行某字段,新值 为 同表中B行同字段的值(列值复制).

  update 表名 set 列名1=(select 列名1 from 表名 as C where C.列名1=‘条件1’) where 列名1=‘条件2’;
  错误: You can’t specify target table ‘表名’ for update in FROM clause
  解决方案:
  把From子句的表名替换成临时的结果集
  update 表名 set 列名1=(select 列名1 from (select * from 表名) as C where C.列名1=‘条件1’) where 列名1=‘条件2’;