菜单

MySQL的常用命令集锦,MySQL常用命令集锦

2018年12月19日 - MySQL
1、索引分类
   1、普通索引
   2、唯一索引
   3、主键索引
   4、外键索引
2、普通索引(index)
   1、使用规则
      1、一个表中可以有多个index字段
      2、字段的值可以有重复,也可以为NULL值
      3、经常把做查询条件的字段设置为index字段
      4、index字段的key标志为: MUL
   2、创建
      1、创建表时创建index
         create table t1(
         ... ...,
         ... ...,
         index(id),
         index(name));
      2、在已有表中添加索引字段
         1、语法格式
            create index 索引名 on 表名(字段名);
            # 索引名一般和字段名一样
   3、查看
      1、desc 表名;  ->查看KEY标志为 MUL
      2、show index from 表名\G;
   4、删除
      drop index 索引名 on 表名;
      注意:
         删除普通索引只能一个一个删除
3、唯一索引(unique)
   1、使用规则
      1、一个表中可以有多个 unique 字段
      2、unique字段的值不允许重复,可以为空值NULL
      3、unique的KEY标志是 UNI
   2、创建(基本等同index创建)
      1、创建表时创建
         unique(字段名),
         unique(字段名)
      2、已有表中创建
         create unique index 索引名 on 表名(字段名);
      3、查看、删除唯一索引
         desc 表名;
         show index from 表名;
         drop index 索引名 on 表名;
4、主键索引(primary key) && 自增长属性(auto_increment)
   1、使用规则
      1、一个表中只能有一个主键字段
      2、对应字段的值不允许重复 且 不能为空值NULL
      3、主键字段的KEY标志为 PRI
      4、把表中能够唯一标识一条记录的字段设置为主键,通常把表中记录编号的字段设置为主键
   2、创建主键(PRI)
      1、创建表时创建
         1、字段名 数据类型 primary key auto_increment,
         2、
            id int auto_increment,
            ... ...,            # 设置自增长起始值
            primary key(id))auto_increment=10000;
      2、删除主键
         1、先删除自增长属性(modify)
            alter table 表名 modify id int;
         2、删除主键
            alter table 表名 drop primary key;
      3、在已有表中添加主键
         alter table 表名 add primary key(字段名);
5、外键
   1、定义 
      让当前表字段的值在另一个表的范围内选择
   2、语法格式
      foreign key(参考字段名)
      references 被参考表名(被参考字段名)
      on delete 级联动作
      on update 级联动作
   3、案例
      表1、缴费信息表(财务)
         学号  姓名    班级   缴费金额
            1   唐伯虎  AID01   28000
            2   点秋香  AID01   20000

      表2、学生信息表(班主任)
         学号  姓名   缴费金额
           1   唐伯虎  28000
            2   点秋香  20000

      1、创建缴费信息表
         create table jftab(
         id int primary key,
         name char(15),
         class char(5),
         money int
         )default charset=utf8;

         insert into jftab values
         (1,"唐伯虎","AID01",28000),
         (2,"点秋香","AID01",20000),
         (3,"祝枝山","AID01",22000);
      2、创建学生信息表(从表)
         create table bjtab(
         stu_id int,
         name char(15),
         money int,
         foreign key(stu_id) references jftab(id)
         on delete cascade
         on update cascade
         );
   4、级联动作
      1、cascade :数据级联更新
         当主表删除记录 或者 更新被参考字段的值时,从表会级联更新
      2、restrict 默认
         1、当删除主表记录时,如果从表中有相关联记录则不允许主表删除
         2、更新同理
      3、set null
         1、当主表删除记录时,从表中相关联记录的参考字段值自动设置为NULL
         2、更新同理
      4、no action
         on delete no action on update no action
         同 restrict,都是立即检查外键限制        
   5、删除外键
      alter table 表名 drop foreign key 外键名;
      1、外键名的查看方式
         show create table 表名;
   6、已有表中添加外键
      ## 会受到表中原有数据的限制
      alter table 表名 add foreign key(参考字段名)
      references 被参考表名(被参考字段名)
      on delete 级联动作
      on update 级联动作;
   7、外键使用规则
      1、两张表被参考字段和参考字段数据类型要一致
      2、被参考字段必须是 key 的一种,通常是 primary key
6、数据导入
   1、作用:把文件系统的内容导入到数据库中
   2、语法
      load data infile "文件名"
      into table 表名
      fields terminated by "分隔符"
      lines terminated by "\n"
   3、示例
      把 /etc/passwd 文件中的内容导入到db2库下的userinfo表
      tarena :  x  :  1000 : 1000 :
      用户名  密码    UID号  GID号
      tarena,,, : /home/tarena : /bin/bash
      用户描述    用户主目录     登录权限
                                 /bin/false
                                              /usr/sbin/nologin
   4、操作步骤
      1、在数据库中创建对应的表
         create table userinfo(
         username char(20),
         password char(1),
         uid int,
         gid int,
         comment varchar(50),
         homedir varchar(50),
         shell varchar(50)
         );
      2、将要导入的文件拷贝到数据库的默认搜索路径中
         1、查看数据库的默认搜索路径
            show variables like "secure_file_priv";
            /var/lib/mysql-files
         2、Linux命令行输入:
            sudo cp /etc/passwd  /var/lib/mysql-files/
      3、执行数据导入语句
         load data infile "/var/lib/mysql-files/passwd"
         into table userinfo
         fields terminated by ":"
         lines terminated by "\n";
   5、练习:将AID1709.csv文件导入到数据库中
      # csv文件单元格之间以 , 分隔

      /var/lib/mysql-files/AID1709.csv
      ls -l AID1709.csv
      rw-------
      chmod 666 AID1709.csv

      1、在数据库中创建对应的表
         id  姓名  成绩  手机号  班级
         create table scoretab(
         id int,
         name varchar(20),
         score float(5,2),
         phone char(11),
         class char(7)
         )default charset=utf8;
      2、把导入的文件复制到数据库的默认搜索路径中
         cp   源文件   目标路径
         cp  /home/tarena/AID1709.csv  /var/lib/mysql-flies/
         ######## 用 TAB 键 补齐路径 #######
      3、执行数据导入语句
         load data infile "/var/lib/mysql-files/AID1709.csv"
         into table scoretab
         fields terminated by ","
         lines terminated by "\n";

         # 修改文件权限 chmod 666 AID1709.csv
7、数据导出
   1、作用
      将数据库表中的记录保存到系统文件里
   2、语法格式
      select ... from 表名
      into outfile "文件名"
      fields terminated by "分隔符"
      lines terminated by "\n";
   3、把userinfo表中的username、password和uid导出到文件user.txt
      select username,password,uid from userinfo
      into outfile "/var/lib/mysql-files/user.txt"
      fields terminated by ","
      lines terminated by "\n";

      1、sudo -i
      2、cd /var/lib/mysql-files/
      3、cat user.txt
   4、注意
      1、导出的内容由SQL查询语句决定
      2、执行导出命令时路径必须指定对应的数据库搜索路径
8、表的复制
   1、语法格式
      create table 表名 select 查询命令;
   2、示例
      1、复制userinfo表中的全部记录,userinfo2
         create table userinfo2 select * from userinfo;
      2、复制userinfo表中username,password,uid三个字段的第2-10条记录,userinfo3
         create table userinfo3 select username,password,uid from userinfo limit 1,9;
   3、复制表结构
      create table 表名 select 查询命令 where false;
   4、注意
      复制表的时候不会把原有表的 key 属性复制过来
9、嵌套查询(子查询)
   1、定义
      把内层的查询结果作为外层的查询条件
   2、示例
      1、把uid的值小于 uid 平均值的用户名和uid号显示出来
         select username,uid from userinfo
         where uid < (select avg(uid) from userinfo);
10、连接查询
   1、内连接
      1、定义
         从表中删除与其他被连接的表中没有匹配到的行
      2、语法格式
         select 字段名列表 from 表1 
         inner join 表2 on 条件 inner join 表3 on 条件;
      3、示例
         1、显示省市的详细信息
            select sheng.s_name,city.c_name from sheng
            inner join city on sheng.s_id=city.cfather_id;
         2、显示省市县详细信息
            select sheng.s_name,city.c_name,xian.x_name from sheng
            inner join city on sheng.s_id=city.cfather_id
            inner join xian on city.c_id=xian.xfather_id;     
   2、外连接
      1、左连接
         1、定义
            以左表为主显示查询结果
         2、语法格式
            select 字段名列表 from 表1
            left join 表2 on 条件;
         3、示例
            1、以省表为主显示省市详细信息
               select sheng.s_name,city.c_name from sheng
               left join city on sheng.s_id=city.cfather_id;
            2、显示省市区详细信息,要求县全部显示
               select sheng.s_name,city.c_name,xian.x_name from sheng left join city 
               on sheng.s_id=city.cfather_id
               right join xian on city.c_id=xian.xfather_id;
            3、显示省市区详细信息,要求 市 全部显示
               select sheng.s_name,city.c_name,xian.x_name from sheng
               right join city on sheng.s_id=city.cfather_id
               left join xian on city.c_id=xian.xfather_id;
            #### 结果集 ####
      2、右连接
         用法同左连接,以右表为主显示查询结果
11、多表查询
   1、select 字段名列表 from 表名列表;  # 笛卡尔积
   2、select 字段名列表 from 表名列表 where 条件;
      等同于 内连接 inner join

MySQL的常用命令集锦,MySQL常用命令集锦

上面是大家常会合因而到且分外实用之MySQL命令。下边你看#意味着在Unix命令行下执行命令,看到mysql>表示即曾经报到MySQL服务器,是于mysql客户端执行mysql命令。
签到MySQL,倘使老是远程数据库,需要用-h指定hostname。

签到MySQL,倘若连续远程数据库,需要用-h指定hostname。

# [mysql dir]/bin/mysql -h hostname -u root -p

创设一个数据库。

mysql> create database [databasename];

排有有数据库。

mysql> show databases;

切换至一个数据库。

mysql> use [db name];

来得一个数据库的所有表。

mysql> show tables;

查数据表的字段格式。

mysql> describe [table name];

删去一个数据库。

mysql> drop database [database name];

剔除一个数据表。

mysql> drop table [table name];

展现一个数据表的保有数据。

mysql> SELECT * FROM [table name];

返指定数据表的各列信息。

mysql> show columns from [table name];

使用值“whatever”过滤突显选定的一些行。

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

彰显有包含name为”鲍勃”和phone number为“3444444”的笔录。

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

显所有非含有name为”鲍伯(Bob)”和phone
number为“3444444”的笔录,并盖phone_number字段排序。

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

体现有的name以字母“bob”开始和phone number为“3444444”的笔录。

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

显示name以字母“bob”开头和phone number为“3444444”的第1至第5条记录。

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

动用正则表达式查找记录。使用“正则表明式二进制”强制区分轻重缓急写。此命令查找以a最先的旁记录。

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

回去唯一不同的记录。

mysql> SELECT DISTINCT [column name] FROM [table name];

盖升序或降序显示选定的记录。

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

回到行数。

mysql> SELECT COUNT(*) FROM [table name];

总括指定列值的总数。

mysql> SELECT SUM(*) FROM [table name];

联结表。

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

新建一个用户。以root登录。切换至mysql数据库,创制用户,刷新权限。

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

自打unix命令行更改用户密码。

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

从mysql命令行更改用户密码。以root登录,设置密码,更新权限。

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root

mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit

# /etc/init.d/mysql stop
# /etc/init.d/mysql start

root密码也空时,设置root密码。

# mysqladmin -u root password newpassword

更新root密码。

# mysqladmin -u root -p oldpassword newpassword

同意用户“bob”从localhost以密码“passwd”连接服务器。以root登录,切换mysql数据库。设置权限,更新权限。

# mysql -u root -p

mysql> use mysql;
mysql> grant usage on *.* to [email protected] identified by 'passwd';
mysql> flush privileges;

一旦不牵记手工输入密码 请使用–password 参数

 mysqldump -h database_ip -u Username --password=123456 --opt databasename > backup-file.sql
 mysqldump -h database_ip -d -u Username --password=123456 databasename >database_structure.sql

为数据库db设置权限。以root登录,切换来mysql数据库,授予权限,更新权限。

# mysql -u root -p

mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

或者

mysql> grant all privileges on databasename.* to [email protected];
mysql> flush privileges;

更新就存在表的数目。

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

去除表中[field name] = ‘whatever’的行。

mysql> DELETE from [table name] where [field name] = 'whatever';

更新数据库的权位/特权。

mysql> flush privileges;

删除列。

mysql> alter table [table name] drop column [column name];

新增列到db。

mysql> alter table [table name] add column [new column name] varchar (20);

改变列名。

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

多唯一的排。

mysql> alter table [table name] add unique ([column name]);

安列值大点。

mysql> alter table [table name] modify [column name] VARCHAR(3);

抹唯一排。

mysql> alter table [table name] drop index [colmn name];

导入一个CSV文件及表明。

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

导出所有数据库暨sql文件。

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

导出一个数据库。

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

起一个数据库导出一个注脚。

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

自从sql文件还原数据库(数据表)。

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

创数量表例1。

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

始建数量表例2。

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

以查询结果保存及文件

 select title from book into outfile '/tmp/outfile.txt';

查找表中多余的重复记录,重复记录是按照某字段(peopleId)来判断

 select * from people where peopleId in (select peopleId from people group by 
 peopleId having count(peopleId) > 1);

查询表中未重复记录(排除重复记录)

 select * from phome_ecms_wma where title in (select distinct title from phome_ecms_wma);

除去表中重复记录,重复记录是依照某字段(title)来判定

 select *,count(distinct title) INTO OUTFILE '/tmp/table.bak' from phome_ecms_wma group by title;
 delete from phome_ecms_wma;
 LOAD DATA INFILE '/tmp/table.bak' REPLACE INTO TABLE phome_ecms_wma character set utf8;

擅自选用记录

 SELECT *FROM url ORDER BY RAND() LIMIT 5;

查询数据库当前编码

 mysql> show variables like "character_set%";

改表字段类型

 mysql> alter table table_name change last_action last_action datetime NOT NULL default '0000-00-00 00:00:00';

给表添加一个新字段

 mysql> ALTER TABLE host ADD ks_mac VARCHAR(100);

自打表中删除一个字段

 mysql> ALTER TABLE table_name DROP field_name; 

重命名表

 mysql>alter table t1 rename t2;

被字段加索引

 mysql> alter table tablename add index 索引名 (字段名1[,字段名2 …]);
 mysql> alter table tablename add index emp_name (name);

加主关键字之目

 mysql> alter table tablename add primary key(id);

加以唯一限制法的目

 mysql> alter table tablename add unique emp_name2(cardnumber);

抹某个索引

 mysql>alter table tablename drop index emp_name;

长距离访问mysql 设置

 mysql> GRANT ALL PRIVILEGES ON database_test.* to [email protected] IDENTIFIED BY '123456';
 mysql> FLUSH PRIVILEGES;

http://www.bkjia.com/Mysql/1071218.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1071218.htmlTechArticleMySQL的常用命令集锦,MySQL常用命令集锦
上边是咱平时会就此到且万分管用的MySQL命令。下边你盼#代表于Unix命令行下执行命令,看到mys…

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图