MySql常用指令总结

作者: Hughman | 来源:发表于2017-02-12 20:22 被阅读95次

总结:

1、数据库操作

创建库
  create database db_name;

查询库
  show databases; //显示所有的数据库
  show create databases db_name; //显示特定的数据库

删除库
  drop database db_name;

修改库
  alter database db_name [modify command];
  alter database db_name character set gbk;


2、表操作

创建表
  use db_name;
  create table tbl_name (column_structure) [tbl_option];

如:
  create table tbl_name(
  variable_name1 varchar(10)
  variable_name2 int
);

或:
  create table db_name.tbl_name(
  variable_name1 varchar(10)
  variable_name2 int
  );

查询表
查看有哪些表
  show tables;
或show tables like 'pattern_%';
查看表的创建信息
  show create table tbl_name;

  show create table tbl_name \G
查看表的结构
  describe tbl_name;

  desc tbl_name;]

删除表
  drop table [if exists] tbl_name;

修改表
修改表名
  单个:rename table old_tbl_name to new_tbl_name;
  多个:rename table old_tbl_name1 to new_tbl_name1, old_tbl_name2 to   new_tbl_name2;
  跨数据库:rename table old_tbl_name to db_name.new_tbl_name;
修改列定义
增加新列定义:add
  alter table tbl_name add new_column_name data_type;
如:
  alter table exam_student add height int;
修改列的定义(新的属性或者数据类型):modify
  alter table tbl_name modify column_name new_data_type;
删除一个列:drop
  alter table tbl_name drop column_name;
重命名一个列:change
  alter table tbl_name change old_column_name new_column_name new_data_type;
修改表选项:
  alter table tbl_name new_tbl_option;
  alter table tbl_name character set utf8;

3、数据操作

创建数据
  insert into tbl_name (fields_list) values (values_list);
如:
  insert into exam_student (stu_name, stu_no) values ('xiaoming', 'php030_01');
若插入所有字段值
  insert into exam_student values ('xiaoming', 'php030_01', 98);

查询数据
查看字段信息
  select * from tbl_name;
参看具体字段列表信息
  select fields_list from tbl_name where condition;
如:默认状态下
  select stu_name, stu_no from exam_student;
或:
  select stu_name, stu_no from exam_student where 1;
有条件查询
  select * from tbl_name where condition;
如:
  select * from exam_student where fenshu >= 60;

删除数据
  delete from tbl_name condition;
如:
  delete from exam_student where fenshu <= 50;

修改数据
  update tbl_name set field = new_value where condition;
如:
  update exam_student set fenshu =100 where fenshu >= 97;

4、主键:primary

如:法一:
  create table teacher(
  t_id int primary key [auto_increment],
  t_name varchar(5),
  class_name varchar(6),
  t_days tinyint unsigned
  );
法二:
  create table teacher(
  t_id int,
  t_name varchar(5),
  class_name varchar(6),
  t_day tinyint unsigned,
  primary key (t_id)
  );
  
  insert into teacher values (1, '王老师', '0225', 23);
在已创建表中添加主键操作:
  alter table tbl_name add primary key (field) ;
  alter table tbl_name modify colunm_name data_type not null primary key auto_increment;
自动增长:auto_increment
  alter table tbl_name auto_increment initial_value;
在已创建表中的主键id上添加auto_increment
  alter table tbl_name id_name id_name int auto_increment;

5、外键:foreign key

如:
主表:
  create table itcast_class(
  class_id int primary key auto_increment,
  class_name varchar(10) not null default 'itcast_php' comment '班级名称'
  ) character set utf8;
从表:
  create table itcast_student(
  stu_id int primary key auto_increment,
  stu_name varchar(10) not null default '',
  class_id int,
  foreign key
  (class_id) references
   itcast_class (class_id)
  ) character set utf8;
插入数据的顺序:先主表,后从表
  insert into itcast_class values (null, 'php0331');
  insert into itcast_student values (null, '张三', 1);
删除操作:
  alter table itcast_student drop foreign key itcast_student_ibfk_1;
  alter table itcast_student add foreign key (class_id) references itcast_class (class_id)
  on delete set null;/主表里删除某个字段,从表若对应外键则变成null/
或者: 
  on delete cascade;/主表删除一个字段,从表对应的外键则会删除相应的值/
又或者:
  on update restrict;/不允许主表进行更新操作/
  delete from itcast_class where class_id = 1;

6、最大值

select max(field) from tbl_name;

7、连接

内连接
  select tbl1_name.field, tbl2_name.field1, tbl2_name.field2... from tbl1_name inner join tbl2_name on tbl1_name.id = tbl2_name.id where condition;
外连接
  select tbl1_name.fields, tbl2_name.fields from tbl1_name left outer join tbl2_name on tbl1_name.id = tbl2_name.id;

8、更改数据库密码方法:

进入mysql中后,在命令行中mysql>
输入:set password for root@localhost = password('123456');

相关文章

  • MySql常用指令总结

    总结: 1、数据库操作 创建库:create database db_name; 查询库:show databas...

  • MySql 常用指令

    MySQL常用指令 MySQL 存储引擎 选择 MySQL 数据类型

  • MAC中MySQL常用指令

    MySQL常用指令 安装MySQL数据库:brew install mysql 删除MySQL数据库:brew r...

  • MySQL基础-常用指令

    MySQL常用基本指令 1 常用数据库操作指令 创建数据库 create database 删除数据库 drop...

  • MYSQL常用指令

    Mysql创建远程连接 grant all privileges on *.* to root@'%' ident...

  • MySql常用指令

    1.修改数据库用户名和密码

  • mysql常用指令

    数据库指令 新建数据库 CREATE DATABASE db_name; 查看数据库 SHOW DATABASES...

  • mysql常用指令

    增加外键 建表的时候增加 CREATE TABLE Members (Member_ID INT NOT NULL...

  • mySql常用指令

    原文地址:https://www.cnblogs.com/bluealine/p/7832219.html 一、连...

  • 7.21 MySQL学习总结

    MySQL常用指令:1.命令行启动mysql : (windows平台 需要将mysql加入Path环境变量中) ...

网友评论

    本文标题:MySql常用指令总结

    本文链接:https://www.haomeiwen.com/subject/hgnaittx.html