您的位置:澳门新葡8455最新网站 > 数据库管理 > MySQL常用命令

MySQL常用命令

发布时间:2019-11-04 10:57编辑:数据库管理浏览(168)

    (1)库的基础操作

    1.user表
            如果需要从其他机器连接 mysql 服务器
    报这个错“ERROR 1130: Host 'root' is not allowed to connect to this MySQL server”
    我们需要为连接用户设置权限

    • 查看已有库: show databases;
    • 创建库(制定默认字符集): ccreate database 库名 default charset=utf8;
    • 查看创建库的语句: show create database 库名;
    • 查看当前所在库: select database();
    • 切换库: use 库名;
    • 查看库中已有表: show tables;
    • 删除库: drop database 库名;

    在mysql库 user表中

    (2)表的管理

    图片 1

    • 创建表(制定字符集)

     【1】Host——对应的允许访问域(%表示全域,192.168.%.%表示对应账号对该段均可访问,
               127.0.0.1表示对应账号仅这台机器可访问)
    【2】User——用户名
    【3】Password——密码加密串
    【29】xxx_priv——各种权限 

        create table tablename(

    2.mysql默认库
    一个刚建好的mysql数据库 一般有一下几个库
    # show database;
    'information_schema'
    'mysql'
    'performance_schema'
    'test'

        字段名 数据类型,

    information_schema 库:
    information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名,数据库的表,表栏的数据类型与访问权限等。再简单点,这台MySQL服务器上,到底有哪些数据库、各个数据库有哪些表,每张表的字段类型是什么,各个数据库要什么权限才能访问,等等信息都保存在information_schema表里面。

        字段名 数据类型,

    mysql 库:
    mysql数据库是mysql的参数数据库,这个是mysql的核心数据库,类似于sql server中的master表,主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除

        ... ...

    performance_schema 库:
    5.5 版本起 新增了一个性能优化的引擎: PERFORMANCE_SCHEMA。主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表,这个功能默认是关闭的:
    需要设置参数: performance_schema  才可以启动该功能,这个参数是静态参数,只能写在my.cnf 中
    不能动态修改。
    Performance的开启很简单,在my.cnf中[mysqld]加入performanc_schema,检查性能数据库是否启动的命令:
    SHOW VARIABLES LIKE ‘performance_schema’;
    若是返回的 值为ON,则说明性能数据库正常开启状态。

        );

     

    • 查看创建表的语句(字符集)

    test 库:
    做测试使用的。

        show create table tablename;

     

    • 查看表结构

    3..显示查看命令
    查看mysql的当前登陆用户 
    select user();

        desc tablename;

    查看当前版本
    select  @@version ;

    • 删除表

    1、显示当前数据库服务器中的数据库列表:

        drop table tablename;

    mysql> SHOW DATABASES;

    (3)表记录管理

    注意:mysql库里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。

    • 在表中插入记录

    2、显示数据库中的数据表:

        insert into tablename values(值1),(值2),...,(值N);

    mysql> USE 库名;
    mysql> SHOW TABLES;

    • 查询表记录

    3、显示数据表的结构:

        select * from tablename;

    mysql> DESCRIBE 表名;

        select 字段名1,字段名2,...,字段名N from tablename;

    4、显示当前时间
    mysql>select now();

    (4)表字段的操作

    5、显示年月日
    SELECT DAYOFMONTH(CURRENT_DATE);
    +--------------------------+
    | dayofmonth(current_date) |
    +--------------------------+
    |                       24 |
    +--------------------------+
    1 row in set (0.02 sec)

        语法:

     SELECT MONTH(CURRENT_DATE);
    +---------------------+
    | MONTH(CURRENT_DATE) |
    +---------------------+
    | 9 |
    +---------------------+
    1 row in set (0.00 sec)

          alter table tablename 执行动作;

    SELECT YEAR(CURRENT_DATE); +--------------------+
    | YEAR(CURRENT_DATE) |
    +--------------------+
    | 2009 |
    +--------------------+
    1 row in set (0.00 sec)

    • 添加字段
      •   alter table tablename add 字段名 数据类型 first | after 字段名;
    • 删除字段
      •   alter table tablename drop 字段名;
    • 修改字段数据类型
      •   alter table tablename modify 字段名 新数据类型;

     

          注: 修改数据类型时,会受到表中原有数据的限制!

    1. 当计算器用
      select ((4 * 4) / 10 ) + 25;
      +----------------------+
      | ((4 * 4) / 10 ) + 25 |
      +----------------------+
      | 26.60 |
      +----------------------+
      1 row in set (0.00 sec)
    • 修改字段名
      •   alter table tablename change 旧名 新名 数据类型;
    • 修改表明
      •   alter table tablename rename 新表明;

     

    (5)表记录的管理

     显示字符串
    mysql> SELECT "welecome to my blog!";
    +----------------------+
    | welecome to my blog! |
    +----------------------+
    | welecome to my blog! |
    +----------------------+
    1 row in set (0.00 sec)

    1. 删除表记录
      1.   delete from tablename where 条件;
    2. 更新表记录
      1.   update tablename set 字段名1=值1, 字段名2=值2,... where 条件;

    串接字符串
    select CONCAT(f_name, " ", l_name) AS Name
    from employee_data
    where title = 'Marketing Executive';
    +---------------+
    | Name |
    +---------------+
    | Monica Sehgal |
    | Hal Simlai |
    | Joseph Irvine |
    +---------------+
    3 rows in set (0.00 sec)
    注意:这里用到CONCAT()函数,用来把字符串串接起来。另外,我们还用到以前学到的AS给结果列'CONCAT(f_name, " ", l_name)'起了个假名。

    (6)一些特殊的匹配方式

     

    1. 模糊匹配
      1.   where 字段名 like 表达式;
      2.   表达式
        1.   _   :  匹配单个字符
        2.   %  : 匹配 0 到多个字符
    2. 正则匹配 regexp
      1.   where 字段名 regexp "正则表达式";
      2.   正则表达式符号
        1.   ^ : 以...开头
        2.   $: 以...结尾
        3.   . : 匹配任意一个字符
        4.   []: 包含...内容
          1.         [0-9]:匹配带数字的
          2.         [a-z]:匹配带小写字母的
          3.         [A-Z]
        5.   * : 星号前面的字符出现 0 次或者多次

     

     

     

    可以把 SQL 分为两个部分:
    ※ 数据定义语言(DDL),例如:CREATE、DROP、ALTER等语句。
    ※ 数据操作语言(DML),例如:INSERT(插入)、UPDATE(修改)、DELETE(删除)语句。
    ※ 数据查询语言(DQL),例如:SELECT语句。(一般不会单独归于一类,因为只有一个语句)。
    ※ 数据控制语言(DCL),例如:GRANT、REVOKE等语句。
    ※ 事务控制语句(TCL),例如:COMMIT、ROLLBACK等语句

    查询和更新指令构成了 SQL 的 DML 部分:
    SELECT - 从数据库表中获取数据
    UPDATE - 更新数据库表中的数据
    DELETE - 从数据库表中删除数据
    INSERT INTO - 向数据库表中插入数据

    SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引(键),规定表之间的链接,以及施加表间的约束。
    SQL 中最重要的 DDL 语句:
    CREATE DATABASE - 创建新数据库
    ALTER DATABASE - 修改数据库
    DROP DATABASE - 删除数据库

    CREATE TABLE 
    - 创建新表
    ALTER TABLE - 变更(改变)数据库表
    DROP TABLE - 删除表

    CREATE INDEX 
    - 创建索引(搜索键)
    DROP INDEX - 删除索引
    ALTER INDEX -修改索引

     

    4.库操作

    4.1 创建数据库
    注意:创建数据库之前要先连接Mysql服务器
    命令:
    create database <数据库名>
    例1:建立一个名为xhkdb的数据库
       mysql> create database xhkdb;

    例2:创建数据库并分配用户
    ①CREATE DATABASE 数据库名;
    ②GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '
    密码';
    ③SET PASSWORD FOR '数据库名'@'localhost' = OLD_PASSWORD('密码');

    依次执行3个命令完成数据库创建。注意:中文 “密码”和“数据库”是户自己需要设置的。

    4.2 显示数据库
    命令:show databases   (注意:最后有个s)
    mysql> show databases;
    注意:为了不再显示的时候乱码,要修改数据库默认编码。以下以GBK编码页面为例进行说明:
    1、修改MYSQL的配置文件:my.ini里面修改default-character-set=gbk
    2、代码运行时修改:
       ①Java代码:jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk
       ②PHP代码:header("Content-Type:text/html;charset=gb2312");
       ③C语言代码:int mysql_set_character_set( MYSQL * mysql, char * csname);
    该函数用于为当前连接设置默认的字符集。字符串csname指定了1个有效的字符集名称。连接校对成为字符集的默认校对。该函数的工作方式与SET NAMES语句类似,但它还能设置mysql- > charset的值,从而影响了由mysql_real_escape_string() 设置的字符集。

    4.3 删除数据库
    命令:drop database <数据库名> 例如:删除名为 xhkdb的数据库
    mysql> drop database xhkdb;
    例子1:删除一个已经确定存在的数据库
       mysql> drop database drop_database;
       Query OK, 0 rows affected (0.00 sec)
    例子2:删除一个不确定存在的数据库
       mysql> drop database drop_database;
       ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist
          //发生错误,不能删除'drop_database'数据库,该数据库不存在。
       mysql> drop database if exists drop_database;
       Query OK, 0 rows affected, 1 warning (0.00 sec)//产生一个警告说明此数据库不存在
       mysql> create database drop_database;
       Query OK, 1 row affected (0.00 sec)
       mysql> drop database if exists drop_database;//if exists 判断数据库是否存在,不存在也不产生错误
       Query OK, 0 rows affected (0.00 sec)

    4.4 连接数据库
    命令: use  <数据库名> 例如:如果xhkdb数据库存在,尝试存取它:
       mysql> use xhkdb;
    屏幕提示:Database changed
    use 语句可以通告MySQL把db_name数据库作为默认(当前)数据库使用,用于后续语句。该数据库保持为默认数据库,直到语段的结尾,或者直到发布一个不同的USE语句:
       mysql> USE db1;
       mysql> SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
       mysql> USE db2;
       mysql> SELECT COUNT(*) FROM mytable;   # selects from db2.mytable
    使用USE语句为一个特定的当前的数据库做标记,不会阻碍您访问其它数据库中的表。下面的例子可以从db1数据库访问作者表,并从db2数据库访问编辑表:
       mysql> USE db1;
       mysql> SELECT author_name,editor_name FROM author,db2.editor
           ->        WHERE author.editor_id = db2.editor.editor_id;
    USE语句被设立出来,用于与Sybase相兼容。
    有些网友问到,连接以后怎么退出。其实,不用退出来,use 数据库后,使用show databases就能查询所有数据库,如果想跳到其他数据库,用
       use 其他数据库名字
    就可以了。

    4.5 当前选择的数据库
    命令:mysql> select database();
    MySQL中SELECT命令类似于其他编程语言里的print或者write,你可以用它来显示一个字符串、数字、数学表达式的结果等等。如何使用MySQL中SELECT命令的特殊功能?
    1.显示MYSQL的版本
    mysql> select version(); 
    +-----------------------+ 
    | version()             | 
    +-----------------------+ 
    | 6.0.4-alpha-community | 
    +-----------------------+ 
    1 row in set (0.02 sec) 

    4.5 修改数据库

    alter database  数据库名 用于更改数据库的全局特性。这些特性储存在数据库目录中的db.opt文件中

    create database <数据库名>  增
    drop database <数据库名>     删
    alter <数据库名>                      改
    show databases                      查

    select database();
    use 数据库名

     

     

     

    5.表结构操作

    1.创建表
    **
    create table
    **mysql> create table yuangong(
        -> id int unsigned not null auto_increment,
        -> firstname varchar(25) not null,
        -> lastname varchar(25) not null,
        -> email varchar(45) not null,
        -> phone varchar(10) not null,
        -> primary key(id));
    Query OK, 0 rows affected (0.03 sec)

     

    create table(
    字段1名 数据类型  约束条件,
    字段2名 数据类型   约束条件,
    字段3名 数据类型   约束条件,
    [index(字段名)]**

    **);

     

    无论当前是否在使用目标数据库,都可以创建表,只要在表名前面加上目标数据库即可。例如:
    mysql>create table 数据库名. 表名(
    )

    在sql语句中注意“约束的概念":

    1.实体完整性约束(主键``--唯一且非空)`` primary key``()

    ``违约处理:``No action``(拒绝执行)

     

    2.参照完整性约束(外键约束)foregin ``key``() ``references tableName(filedName) [``on delete``|``update casecade | ``no action``]

    ``违约处理:级联更新或拒绝执行

     

    3.用户自定义完整性约束 非空、唯一、核对(``not null``,``unique``,``check``短语)

    ``违约处理:拒绝执行

     

    4.默认值   default "默认值"

     

    CREATE TABLE Person
    (
     EmployeeEducationTypeID int NOT NULL PRIMARY KEY,
     EmployeeID int NOT NULL,
     EducationTypeID int NULL,
     GPA numeric(4,3) NOT NULL CHECK (GPA>2.5 AND GPA<=4.0)
    )
    -----------在列上直接定义CHECK约束

     

    2.复制表 复制一个表命名为yuangong2
    mysql> *create 新表  select from   库 . 表;

    **

    3.查看表
    3.1表
    mysql>show tables;
    3.2表结构
    mysql> describe 表名;
    也可以
    mysql> show columns in 表名;

    mysql>show create table 表名;
     mysql>show full columns from 表名;
     

    4.删除表
    drop
    [temporary] table [if exists] 表名 [, tbl_name, ...]

     

    5.改表结构

    修改表名: mysql> alter table 原表名 rename 新表名
    增加一个字段:
    mysql>alter table  表名 add  字段名 数据类型 约束 [first|after 列名];

    删除一个字段:
    mysql>**alter table  表名 drop 字段名;

    修改字段的名称及类型:(不论是否改变字段类型,一定要重新指定该字段的类型)
    mysql>alter table 表名 change  原字段名  字段新名 新数据类型 新约束条件;**

    修改字段类型: mysql>alter table 表名  modify  原字段名 新数据类型 新约束条件;

    修改字段排序: mysql>alter table 表名  modify   原字段名 新数据类型 first | after  字段名2;

    6.创建临时表      有的时候,当工作在非常大的表上时,可能偶尔需要运行很多查询获得一个大量数据的小的子集,不是对整个表运行这些查询,而是让MySQL每次找出所需的少数记录,将记录保存到一个临时表可能更快一些,然后对这些临时表进行查询操作。可以通过使用temporary关键字和create table语句来实现。

     mysql> create temporary table 临时表名 select firstname, lastname from 表名;

    临时表的创建与其它表一样,只是它们存储在操作系统指定的临时目录中。临时表将在你连接MySQL期间存在,当你断开时,MySQL将自动删除表并释放所有的内存空间;当然了,你也可以手动的使用drop table命令删除临时表。

     
    增:create  
    删:drop
    改:alter
    查:select     show     describe

     

     

    6.表内容操作
    1.增加数据(插入)

    **insert 语句一次插入整条数据:
       insert 表名 values('字段1值' , '字段2值' , '字段3值' , '字段n值');

    insert 语句插入几个字段数据:
       insert 表名('字段2' , '字段3' )  values('字段2值' , '字段3值' );**

    insert 语句一次插入多条数据:
       insert 表名 values('字段1值' , '字段2值' ),('字段1值' , '字段2值' );

    使用insert…select语句插入从其他表选择的行
       insert 表名(col1,col2) select col3,col4 from tbl_name2;

    2.删除数据
         删除整个表记录
           delete from 表名    (慎用!慎用!一定看清楚)

         删除一条数据
           delete from 表名 where 字段名=值;    (
    一次最少删除一行记录,不能只删除一行的一个字段记录)

     

    3.更新数据
         更改一个字段的所有值
           update 表名 set  字段=值                  (一定!一定!看清楚!更改该字段的所有记录值)

         更改单条记录的一个字段值
           update 表名  set  字段=值 where  字段=值 

         更改单条记录的多个字段值
           update 表名 set 字段1=值1, 字段2=值2  where 字段=值

     

    4.查询数据
           =  !=  >=   <=   in()     between 值and值    or      and           like  _   %

         输出表全部记录
           select * from 表名

         输出某几个字段值
           select   字段1,字段2,字段3  from 表名 

         查询不重复的记录
           select  distinct 字段1,字段2,字段3 from 表名

     

    条件查询        select * from 表名 where  字段名 (条件运算符)  值          条件运算符:=   !=   >=   <=     in()在列表内的值        between  值and值  范围内的值
            select * from TableName where id=2;
            select * from TableName where id>2;
            select * from TableName where id<2;     
            select * from TableName where id in(2,5,8);   
            select * from TableName where id between  2 and 8;
               以上运算符条件查询只能用在 数值匹配上

     

    逻辑运算符
           or    and
            select * from TableName where id<2  or  gz=100;        (符合一个条件即可)
            select * from TableName where id<2  and  gz=100;     (要符合两个条件)

     

    模糊查询
           通配符    like     _  匹配单个字符       %匹配n个字符
           select  * from yuangong  where 字段名  like '_b_';
           select * from yuangong   where 字段名 like 'b__';
           select * from yuangong   where 字段名 like '%b';

    正则表达式
       regexp    ^   $  
    .

            含有某个字符组(其中含有hello即可)
                          select * from yuangong where 字段名 regexp 'hello';
           ^  以什么开头
                          select * from yuangong where 字段名 regexp '^b';

           **$  以什么结尾
                          *select from yuangong where 字段名  regexp 'b$';        .   匹配任意个字符                       select * from yuangong where 字段名 regexp 'b.';  查询以b开头的
           [0~9]  匹配所有数字                       select * from yuangong where 字段名 regexp '1[1-2]';  查询第一个数字为1  第二个数字为1到2
          [a~z] 匹配所有字幕
    **
                          select * from yuangong where 字段名 regexp '1[a-e]';**查第一个为1 第二个数字为a到e的字符串

     

    四则运算
              +   -    *    /

                    select  name,字段1 * 字段2   from 表名;
                        select  字段1*12   from  表名  where  name="mike";
                        select 字段1*字段2  as  别名 from 表名 where name="mike";

     

    内置聚合函数 算数函数
                 min()      max()    avg()     sum()     count()

               select  min(money)  from  people;  字段最小值
               select  max(money) from people;   字段最大值
               select  avg(money)  from people;   字段平均值
               select  sum(money) from people;    获得字段的和
                select count(*) from people;              总行数
                select count(字段名) from people;      字段行数

     

    排序操作 order by            order by    默认从低到高,desc从高到低
              select *from 表名 order by 字段1,字段2 [desc];

    limit显示设置         limit        select *from 表名 order by 字段1 limit 4;     显示前四行
           **select *from 表名 order by 字段1 limit 2,4;    ** 显示3到5行(从0开始计数)

     

     

    分组查询 group by
             group by
                  select子句中的列名必须为分组列或列函数
                      select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by后面
             select 部门,avg(工资) from 员工表 group by 部门; 每个部门平均工资
               SELECT DEPT, MAX(SALARY) AS MAXIMUM FROM STAFF GROUP BY DEPT; 每个部门最高工资
               select 部门,count(*) from 员工表 group by 部门; 每个部门人数

     

    having结果筛选         having              从已经查询出的结果缓存中,再次查询
                        把 HAVING 加入 SQL 的原因是,WHERE 无法应用于合计函数,而如果没有 HAVING,
                             就无法测试结果条件。
              select name,sum(工资)  from table_name GROUP BY name  HAVING sum(工资)>1000 ;
             select  地区, SUM(人口), SUM(面积) FROM 表 GROUP BY 地区 having  sum(面积)>1000;

      

    增:insert   
    删:delete
    改:update 查:select

     

     

    7.索引操作

             MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。
             唯一性索引,这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。
             主键是一种唯一非空性索引,但它必须指定为“PRIMARY KEY”。主键一般在创建表的时候指定

     

    1.创建索引
          **
       create index 索引名 on 表名(字段名(数据类型));                   普通索引          create unique index 索引名 on 表名(字段名(数据类型));    ** 唯一索引

    2.在表结构中添加索引
             alter table 表名 add INDEX [索引名] (字段名);
             alter table 表名 add UNIQUE [索引名] (字段名);
            altertable 表名 add  primary key(字段名);  

    3.创建表时添加索引
           **
    create table 表名(
                  id int,
                  name varchar(),
                  age int,
                 index (id),
                  );
           **create table 表名(
                id int,
                name varchar(),
                age int,
                UNIQUE [索引名] (字段名)
                 );     
    create table 表名(
                id int,
               name varchar(),
               age int,
               primary key  (字段名)
               );**

    4.查询表中索引
            show  index  from 表名;

    5.删除索引
            drop index 索引名 on 表名;

     

     

     

     

     

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:MySQL常用命令

    关键词: