mysql数据库的SQL语句

一、数据库操作命令

1、查询数据库

show database;

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| performance_schema |

| test               |

| mysql              |

+--------------------+

7 rows in set (0.17 sec)

mysql>

mysql数据库初始化后，数据库会有4个默认的数据库

information_schema：信息数据库。主要保存着关于MySQL服务器所维护的所有其他数据库的信息，如数据库名，数据库的表，表栏的数据类型与访问权 限等。通过show databases;查看到数据库信息，也是出 自该数据库中得SCHEMATA表。

mysql：mysql的核心数据库。主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控 制和管理信息。

performance_schema： 用于性能优化的数据库。

test:空数据库。

2、查看数据库的创建语句

show create database mysql;

mysql> show create database mysql;

+----------+----------------------------------------------------------------+

| Database | Create Database                                                |

+----------+----------------------------------------------------------------+

| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+----------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

3、查看字符集命令

show character set;

mysql> show character set;

+----------+-----------------------------+---------------------+--------+

| Charset  | Description                 | Default collation   | Maxlen |

+----------+-----------------------------+---------------------+--------+

| dec8     | DEC West European           | dec8_swedish_ci     |      1 |

| cp850    | DOS West European           | cp850_general_ci    |      1 |

| hp8      | HP West European            | hp8_english_ci      |      1 |

| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |

| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |

| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |

| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |

| ascii    | US ASCII                    | ascii_general_ci    |      1 |

| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |

| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |

| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |

| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |

| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |

可以查看mysql数据库支持的所有字符集

4、修改数据库的字符集

alter databases ywx default character set utf8;

查看原有数据库ywx的字符集

mysql> show create database ywx;

+----------+---------------------------------------------------------------+

| Database | Create Database                                               |

+----------+---------------------------------------------------------------+

| ywx      | CREATE DATABASE `ywx` /*!40100 DEFAULT CHARACTER SET koi8r */ |

+----------+---------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

现在的字符集为koi8r

把ywx数据库的字符集从koi8r改为utf8

alter database ywx default character set utf8;

mysql> alter database ywx default character set utf8;

Query OK, 1 row affected (0.00 sec)

mysql> show create database ywx;

+----------+--------------------------------------------------------------+

| Database | Create Database                                              |

+----------+--------------------------------------------------------------+

| ywx      | CREATE DATABASE `ywx` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+--------------------------------------------------------------+

1 row in set (0.00 sec)

mysql>

5、创建数据库

create database ywx default character set utf8;

mysql> create database ywx1 default character set utf8;

Query OK, 1 row affected (0.01 sec)

mysql> show create database ywx1;

+----------+---------------------------------------------------------------+

| Database | Create Database                                               |

+----------+---------------------------------------------------------------+

| ywx1     | CREATE DATABASE `ywx1` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+---------------------------------------------------------------+

1 row in set (0.00 sec)

如果创建的数据库存在，会提示报错

创建一个已有的数据库

mysql> create database ywx1 default character set utf8;
ERROR 1007 (HY000): Can't create database 'ywx1'; database exists
mysql>

可以使用以下命令来创建数据库，创建的数据库存在则不创建，不存在则创建，不会返回错误信息

create database if not exists ywx default character set utf8;

mysql> create database if not exists ywx default character set utf8;
Query OK, 1 row affected, 1 warning (0.00 sec)

数据库ywx存在，不会报错，会有一个警告信息，查看警告信息，显示该数据库已存在

mysql> show warnings;
+-------+------+----------------------------------------------+
| Level | Code | Message                                      |
+-------+------+----------------------------------------------+
| Note  | 1007 | Can't create database 'ywx'; database exists |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

6、删除数据库（慎用！！！）

drop database ywx;

drop删除数据库为物理删除，硬盘上的数据也同时删除。

查看ywx1数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jfedu              |
| king               |
| mysql              |
| performance_schema |
| test               |
| ywx                |
| ywx1               |
+--------------------+
8 rows in set (0.00 sec)

mysql>

[root@web03 ~]# ll -d /data/mysql/ywx1
drwx------ 2 mysql mysql 20 Dec 22 12:40 /data/mysql/ywx1
[root@web03 ~]#

在数据库和数据库的数目录中都存在ywx1数据库。

删除ywx1数据库

drop database ywx1;

mysql> drop database ywx1;
Query OK, 0 rows affected (0.43 sec)

查看ywx1数据库是否已删除

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jfedu              |
| king               |
| mysql              |
| performance_schema |
| test               |
| ywx                |
+--------------------+
7 rows in set (0.00 sec)

mysql>
#数据库ywx1已删除

在mysql的数据目录中查看

[root@web03 ~]# ll -d /data/mysql/ywx1
ls: cannot access /data/mysql/ywx1: No such file or directory
[root@web03 ~]#
发现ywx1的数据库目录已经不存在了。

删除一个不存在的数据库，数据库也会报错

mysql> drop database ywx1; ERROR 1008 (HY000): Can't drop database 'ywx1'; database doesn't exist mysql>

使用以下命令，在删除时，数据库存在则直接删除，不存在则不会报错，会有一个警告信息

drop database if exists ywx1;

mysql> drop database if exists ywx1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Note  | 1008 | Can't drop database 'ywx1'; database doesn't exist |
+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

7、查看当前所在那个数据库

select database();

mysql> use mysql;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

mysql>

二、表的操作命令

1、查看某个数据库的所有表

use mysql;

show tables;

或

show tabales from mysql;

mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |

mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |

2、查看某个数据库的所有表的详细信息

use mysql;

show table status\G;

或

show table status from mysql\G;

mysql> use mysql; Database changed mysql> show table status\G;

********* 1. row *********
           Name: columns_priv
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 227994731135631359
   Index_length: 4096
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-12-20 21:19:39
    Update_time: 2019-12-20 21:19:39
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Column privileges
********* 2. row *********

mysql>show table status from mysql\G;
********* 1. row *********
           Name: columns_priv
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 227994731135631359
   Index_length: 4096
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-12-20 21:19:39
    Update_time: 2019-12-20 21:19:39
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Column privileges
********* 2. row *********

3、查看某个数据库的某张表的信息

use mysql;

show table status like "user" \G;

或

show table status from mysql like "user"\G

mysql> use mysql;
Database changed
mysql> show table status like "user"\G;
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 52
    Data_length: 364
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 52
 Auto_increment: NULL
    Create_time: 2019-12-20 21:19:39
    Update_time: 2019-12-21 16:00:17
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Users and global privileges
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show table status from mysql like "user"\G;
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 52
    Data_length: 364
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 52
 Auto_increment: NULL
    Create_time: 2019-12-20 21:19:39
    Update_time: 2019-12-21 16:00:17
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options:
        Comment: Users and global privileges
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

4、查看表结构

desc mysql.user;

mysql数据库的user表

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |

5、查看创建表的sql语句

mysql> use mysql;
Database changed
mysql> show create table mysql.user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

6、创建表

create database ywx;

use ywx;

create table t1(id int, name char(20), age tinyint);

mysql>create database ywx;
mysql>use ywx;
mysql> create table t1(
    -> id int,
    -> name char(20),
    -> age tinyint
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_ywx |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> show tables from ywx;
+---------------+
| Tables_in_ywx |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

7、修改表字段名，需要将字段属性写全

alter table t1 change id age int(5);

id为原字段,age为新字段

把t1表的id字段该为age字段。

mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql> alter table t1 change id num int(5);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#id字段已经被改为num字段了

8、添加表字段

给ywx数据库的t1表添加job字段

alter table t1 add job varchar(20);

查看现有的t1表没有job字段
mysql> show create table t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t1 add job varchar(20);
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

默认新添加的字段是放在表的最后面，如果想添加到第一列，或某些字段的后面，如下配置

添加到第一列

alter table t1 add xueli varchar(20) first;

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                            |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

添加xueli字段到第一列

mysql> alter table t1 add xueli varchar(20) first;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `xueli` varchar(20) DEFAULT NULL,
  `num` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

添加到某一个字段的后面

添加新的字段dianhua到name的后面

alter table t1 add dianhua tinyint after name;

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `xueli` varchar(20) DEFAULT NULL,
  `num` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

添加新的字段dianhua到name的后面

alter table t1 add dianhua tinyint after name;

mysql> alter table t1 add dianhua tinyint after name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `xueli` varchar(20) DEFAULT NULL,
  `num` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `dianhua` tinyint(4) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

9、修改表字段的属性或位置

修改字段的顺序，把第二列的num放在第一列

alter table t1 modify num int(5) first;

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `xueli` varchar(20) DEFAULT NULL,
  `num` int(5) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `dianhua` tinyint(4) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改第二列的num到第一列

mysql> alter table t1 modify num int(5) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `xueli` varchar(20) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `dianhua` tinyint(4) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改job的位子到name的下面

alter table t1 modify job varchar(20) after name;

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `xueli` varchar(20) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `dianhua` tinyint(4) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改job的位子到name的下面

mysql> alter table t1 modify job varchar(20) after name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `xueli` varchar(20) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `dianhua` tinyint(4) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

10、删除表字段

删除ywx数据库t1的表的dianhua字段

alter table t1 drop dianhua;

mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `xueli` varchar(20) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `dianhua` tinyint(4) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除dianhua字段

mysql> alter table t1 drop dianhua;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `num` int(5) DEFAULT NULL,
  `xueli` varchar(20) DEFAULT NULL,
  `name` char(20) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

三、表数据的擦操作命令

先创建好表：

create database ywx default character set utf8;
use ywx;
create table t2(id int(10) auto_increment primary key, name varchar(20), job varchar(10));

mysql> create database ywx default character set utf8;
mysql> use ywx
Database changed
mysql> create table t2(
    -> id int(10) auto_increment primary key,
    -> name varchar(20),
    -> job varchar(10)
    -> ) default character set utf8;
Query OK, 0 rows affected (0.00 sec)

1、增加字段数据内容

1.1字段增加数据

insert into t2 values(1,“xiaoming”,"it");

或者

insert into t2(name) values("xiaohua");

或者

insert into t2 set

name="xiaoming",

job="teacher";

mysql> insert into t2 values(1,"xiaoming","it");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+----------+------+
| id   | name     | job  |
+------+----------+------+
|    1 | xiaoming | it   |
+------+----------+------+
1 row in set (0.00 sec)

mysql>

mysql> insert into t2(name) values("xiaohua");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+----------+------+
| id | name     | job  |
+----+----------+------+
|  1 | xiaoming | it   |
|  2 | xiaohua  | NULL |
+----+----------+------+
2 rows in set (0.00 sec)

mysql>

mysql> insert into t2 set
    -> name="xiaoming",
    -> job="teacher";
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+----+----------+---------+
| id | name     | job     |
+----+----------+---------+
|  1 | xiaoming | it      |
|  2 | xiaohua  | NULL    |
|  3 | xiaoming | teacher |
+----+----------+---------+
3 rows in set (0.00 sec)

mysql>

1.2增加指定字段的数据

insert into t2(name) values("xiaoqiang"),("xiaowang");

mysql> insert into t2(name) values("xiaoqiang"),("xiaowang");
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+-----------+---------+
| id | name      | job     |
+----+-----------+---------+
|  1 | xiaoming  | it      |
|  2 | xiaohua   | NULL    |
|  3 | xiaoming  | teacher |
|  4 | xiaoqiang | NULL    |
|  5 | xiaowang  | NULL    |
+----+-----------+---------+
5 rows in set (0.00 sec)

2、删除字段数据

2.1、物理删除，数据就真没有了（无法找回）

delete from t2 where id=2;

删除t2表中id=2的数据

删除前的表数据
mysql> select * from t2;
+----+-----------+---------+
| id | name      | job     |
+----+-----------+---------+
|  1 | xiaoming  | it      |
|  2 | xiaohua   | NULL    |
|  3 | xiaoming  | teacher |
|  4 | xiaoqiang | NULL    |
|  5 | xiaowang  | NULL    |
+----+-----------+---------+
5 rows in set (0.00 sec)

删除t2表中id=2的数据
mysql> delete from t2 where id=2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t2;
+----+-----------+---------+
| id | name      | job     |
+----+-----------+---------+
|  1 | xiaoming  | it      |
|  3 | xiaoming  | teacher |
|  4 | xiaoqiang | NULL    |
|  5 | xiaowang  | NULL    |
+----+-----------+---------+
4 rows in set (0.00 sec)

2.2逻辑删除，数据可以恢复（建议使用）

逻辑删除，需要添加一个isdelete字段，默认设置为0，设置为1时，表示该数据被删除

增加一个isdelete字段

alter table t2 add isdelete bit default 0;

mysql> alter table t2 add isdelete bit default 0;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+-----------+---------+----------+
| id | name      | job     | isdelete |
+----+-----------+---------+----------+
|  1 | xiaoming  | it      |          |
|  3 | xiaoming  | teacher |          |
|  4 | xiaoqiang | NULL    |          |
|  5 | xiaowang  | NULL    |          |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

2.2.1逻辑删除t2表中id=3的数据

update t2 set isdelete=1 where id=3;

mysql> update t2 set isdelete=1 where id=3;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+----+-----------+---------+----------+
| id | name      | job     | isdelete |
+----+-----------+---------+----------+
|  1 | xiaoming  | it      |          |
|  3 | xiaoming  | teacher |         |
|  4 | xiaoqiang | NULL    |          |
|  5 | xiaowang  | NULL    |          |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

mysql> select * from t2 where isdelete=0;
+----+-----------+------+----------+
| id | name      | job  | isdelete |
+----+-----------+------+----------+
|  1 | xiaoming  | it   |          |
|  4 | xiaoqiang | NULL |          |
|  5 | xiaowang  | NULL |          |
+----+-----------+------+----------+
3 rows in set (0.00 sec)

2.2.2恢复逻辑删除的数据t2表中的id=3的数据

update set isdelete=0 where id=3;

mysql> update t2 set isdelete=0 where id=3;
Query OK, 1 row affected (0.30 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+----+-----------+---------+----------+
| id | name      | job     | isdelete |
+----+-----------+---------+----------+
|  1 | xiaoming  | it      |          |
|  3 | xiaoming  | teacher |          |
|  4 | xiaoqiang | NULL    |          |
|  5 | xiaowang  | NULL    |          |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

mysql> select * from t2 where isdelete=0;
+----+-----------+---------+----------+
| id | name      | job     | isdelete |
+----+-----------+---------+----------+
|  1 | xiaoming  | it      |          |
|  3 | xiaoming  | teacher |          |
|  4 | xiaoqiang | NULL    |          |
|  5 | xiaowang  | NULL    |          |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

3、更该字段中的数据内容

修改表中的数据，不增加行，insert into会增加行。

把t2表中id=3的行的name字段修改为“xiaoxiao”

update t2 set name="xiaoxiao" where id=3;

t2是表名

name是字段名

xiaoxiao是修改后的name字段数据

where后面为条件语句，如果没有where语句，就是对整个表的name字段的数据全部更改为xiaoxiao,很危险，请慎用。

mysql> select * from t2;
+----+-----------+---------+----------+
| id | name      | job     | isdelete |
+----+-----------+---------+----------+
|  1 | xiaoming  | it      |          |
|  3 | xiaoming  | teacher |          |
|  4 | xiaoqiang | NULL    |          |
|  5 | xiaowang  | NULL    |          |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

mysql> update t2 set name="xiaoxiao" where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2 where isdelete=0;
+----+-----------+---------+----------+
| id | name      | job     | isdelete |
+----+-----------+---------+----------+
|  1 | xiaoming  | it      |          |
|  3 | xiaoxiao  | teacher |          |
|  4 | xiaoqiang | NULL    |          |
|  5 | xiaowang  | NULL    |          |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

同时修改多个字段的数据内容，用逗号分隔

同时修改t2表，id=3的name字段和job字段

update t2 set name="xiaoming", job="king" where id=3;

mysql> select * from t2 ;
+----+-----------+---------+----------+
| id | name      | job     | isdelete |
+----+-----------+---------+----------+
|  1 | xiaoming  | it      |          |
|  3 | xiaoxiao  | teacher |          |
|  4 | xiaoqiang | NULL    |          |
|  5 | xiaowang  | NULL    |          |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

mysql> update t2 set name="xiaoxiao",job="king" where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2 ;
+----+-----------+---------+----------+
| id | name      | job     | isdelete |
+----+-----------+---------+----------+
|  1 | xiaoming  | it      |          |
|  3 | xiaoming  | king    |          |
|  4 | xiaoqiang | NULL    |          |
|  5 | xiaowang  | NULL    |          |
+----+-----------+---------+----------+
4 rows in set (0.00 sec)

4、查看字段数据内容

4.1全字段查找，不建议使用会，数据量很大会崩溃数据库服务器

查看t2表中的所有数据内容

select * from t2;

mysql> select * from t2;
+----+-----------+------+----------+
| id | name      | job  | isdelete |
+----+-----------+------+----------+
|  1 | xiaoming  | it   |          |
|  3 | xiaoming  | king |          |
|  4 | xiaoqiang | NULL |          |
|  5 | xiaowang  | NULL |          |
+----+-----------+------+----------+
4 rows in set (0.00 sec)

4.2查看指定字段的数据内容

查看t2表中的name和job字段的数据内容

select name,job from t2;

mysql> select name,job from t2;
+-----------+------+
| name      | job  |
+-----------+------+
| xiaoming  | it   |
| xiaoming  | king |
| xiaoqiang | NULL |
| xiaowang  | NULL |
+-----------+------+
4 rows in set (0.00 sec)

查看t2表中id=3的name和job字段的数据内容

select name,job from t2 where id=3;

mysql> select name,job from t2 where id=3;
+----------+------+
| name     | job  |
+----------+------+
| xiaoming | king |
+----------+------+
1 row in set (0.00 sec)

5、更具运算符查找

5.1运算符查找

>   大于
>=  大于等于
=   等于
<   小于
<=  小于等于
!=  不等于

查找t2表中id大于等于3的用户id，用户名及job

select id,name,job from t2 where id >=3;

mysql> select id,name,job from t2 where id >= 3;
+----+-----------+------+
| id | name      | job  |
+----+-----------+------+
|  3 | xiaoming  | king |
|  4 | xiaoqiang | NULL |
|  5 | xiaowang  | NULL |
+----+-----------+------+
3 rows in set (0.00 sec)

5.2同时满足多个条件或者几个条件，满足其一即可的查找

and 同时满足多个条件
or  几个条件，满足其一即可

查找t2表中的id<=3,并且没有标记删除的数据

为了更好的模拟数据，把id=1的标记为isdelete=1

update t2 set isdelete=1 where id=1;

mysql> update t2 set isdelete=1 where id=1;
Query OK, 1 row affected (0.31 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t2;
+----+-----------+------+----------+
| id | name      | job  | isdelete |
+----+-----------+------+----------+
|  1 | xiaoming  | it   |         |
|  3 | xiaoming  | king |          |
|  4 | xiaoqiang | NULL |          |
|  5 | xiaowang  | NULL |          |
+----+-----------+------+----------+
4 rows in set (0.00 sec)

mysql> select * from t2 where isdelete=1;
+----+----------+------+----------+
| id | name     | job  | isdelete |
+----+----------+------+----------+
|  1 | xiaoming | it   |         |
+----+----------+------+----------+
1 row in set (0.00 sec)

查找t2表中的id<=3,并且没有标记删除的数据

select id,name,job,isdelete from t2 where isdelete=1;

mysql> select id,name,job,isdelete from t2 where id<=3 and isdelete=1;
+----+----------+------+----------+
| id | name     | job  | isdelete |
+----+----------+------+----------+
|  1 | xiaoming | it   |         |
+----+----------+------+----------+
1 row in set (0.00 sec)

6、模糊查找like

like 模糊查找
% 匹配任意多个字符
— 匹配单个字符

6.1使用%来匹配多个字符

查看t2表的数据内容

select * from t2;

mysql> select * from t2;
+----+-----------+------+----------+
| id | name      | job  | isdelete |
+----+-----------+------+----------+
|  1 | xiaoming  | it   |         |
|  3 | xiaoming  | king |          |
|  4 | xiaoqiang | NULL |          |
|  5 | xiaowang  | NULL |          |
+----+-----------+------+----------+
4 rows in set (0.00 sec)

我们知道%在mysql数据库中是匹配多个字符的意思

匹配name是以ming结尾的行的信息

select id,name,job from t2 where name like "%ming";

mysql> select id,name,job from t2 where name like "%ming";
+----+----------+------+
| id | name     | job  |
+----+----------+------+
|  1 | xiaoming | it   |
|  3 | xiaoming | king |
+----+----------+------+
2 rows in set (0.00 sec)

匹配到t2表中name字段以“ming”结尾的行的数据

匹配以t2表中name字段以“xi”开头的行的数据

select id,name,job where name like "xi%"

6.2使用“_”来匹配单一字符

先在t2表中添加一行数据name="xiaom" job="seal"

insert int t2(name) values("xiaom" );

mysql> insert into t2(name) values("xiaom");
Query OK, 1 row affected (0.13 sec)

mysql> select * from t2;
+----+-----------+------+----------+
| id | name      | job  | isdelete |
+----+-----------+------+----------+
|  1 | xiaoming  | it   |         |
|  3 | xiaoming  | king |          |
|  4 | xiaoqiang | NULL |          |
|  5 | xiaowang  | NULL |          |
|  6 | xiaom     | NULL |          |
+----+-----------+------+----------+
5 rows in set (0.00 sec)

查找xiao后面只有一个字符的行

select id,name,job form t2 where name like "xiao_";

mysql> select id,name,job from t2 where name like "xiao_";
+----+-------+------+
| id | name  | job  |
+----+-------+------+
|  6 | xiaom | NULL |
+----+-------+------+
1 row in set (0.00 sec)

7、范围查询

in                  表示非连续的范围 如：in（3,5）第3行和第5行
between A and B     表示一个连续的范围内 如：between3 and 5 第3到第5行
not  in               不在某个条件内 如 not in (3,5)不包含第3行和第5行的行的内容

select id,name,job from t2;

mysql> select id,name,job from t2;
+----+-----------+------+
| id | name      | job  |
+----+-----------+------+
|  1 | xiaoming  | it   |
|  3 | xiaoming  | king |
|  4 | xiaoqiang | NULL |
|  5 | xiaowang  | NULL |
|  6 | xiaom     | NULL |
+----+-----------+------+
5 rows in set (0.00 sec)

7.1非连续范围“in”

查找id=3和id=5的行的内容

select id,name,job from t2 where id in (3,5);

mysql> select id,name,job from t2 where id in (3,5);
+----+----------+------+
| id | name     | job  |
+----+----------+------+
|  3 | xiaoming | king |
|  5 | xiaowang | NULL |
+----+----------+------+
2 rows in set (0.00 sec)

7.2连续的范围“between”

查找id=3到id=5的行的内容

select id,name,job from t2 where id between 3 and 5;

mysql> select id,name,job from t2 where id between 3 and 5;
+----+-----------+------+
| id | name      | job  |
+----+-----------+------+
|  3 | xiaoming  | king |
|  4 | xiaoqiang | NULL |
|  5 | xiaowang  | NULL |
+----+-----------+------+
3 rows in set (0.00 sec)

7.3不包含的行not in

查找除了id=3和id=5的所有行的内容

select id,name,job from t2 where id not in (3,5);

mysql> select id,name,job from t2 where id not in (3,5);
+----+-----------+------+
| id | name      | job  |
+----+-----------+------+
|  1 | xiaoming  | it   |
|  4 | xiaoqiang | NULL |
|  6 | xiaom     | NULL |
+----+-----------+------+
3 rows in set (0.00 sec)

7.4查找空值（NULL）

查找有某个字段是null的行 is null

查找t2表中job字段为null的行

select id,name,job from t2 where job is null;

8、聚合

# 查找年龄最大的
select max(age) from t2;
select min(age) from t2;
select count(*) from t2 where isdelete=0;

9、排序

order by  字段  asc       根据“列”从小到大排序
order by  字段  desc      根据“列”从大到小排序
根据id，从大到小进行排序：
select * from t2 order by id desc;

10、分组

group by 字段
#根据名字分组，统计同名的个数： select count(*),name  from t2 group by name;

11、限制

limit n;   显示前n行
#显示前三行 select * from t2 limit 3;
#显示从第3行开始后的3行； select * from t2 limit 3,3;
#显示从第3行开始后的4行 select * from t2 limit 4 offset 3;

四、视图操作

视图并不是真实存在的表，主要是将常用的到字段或者数据整合成一个“表”。

1、创建视图

# 先创建表：
create table t1 ( id int(10) not null auto_increment primary key, name varchar(20), job varchar(10) );
# 插入数据：
insert t1 set name="xiaoming", job="it"; insert t1 set name="xiaowang", job="it"; insert t1 set name="xiaohong", job="it";
# 创建索引：
create view v1 as select name,job from t1; create view v2 as select * from t1 where id >= 2;

2、查看视图

# 查看所有视图：
select * from information_schema.views where table_schema="test"\G
# 查看某个视图结构：
desc v2;
# 查看视图内容：
select * from v2;

3、删除视图

drop   view v2;
或者
drop view  if exists v2;

4、修改视图

alter view v1 as select id,job from t1;

set

二、mysql数据库的SQL语句

mysql数据库的SQL语句

一、数据库操作命令

1、查询数据库

2、查看数据库的创建语句

3、查看字符集命令

4、修改数据库的字符集

5、创建数据库

6、删除数据库（慎用！！！）

7、查看当前所在那个数据库

二、表的操作命令

1、查看某个数据库的所有表

2、查看某个数据库的所有表的详细信息

3、查看某个数据库的某张表的信息

4、查看表结构

5、查看创建表的sql语句

6、创建表

7、修改表字段名，需要将字段属性写全

8、添加表字段

添加到第一列

添加到某一个字段的后面

9、修改表字段的属性或位置

10、删除表字段

三、表数据的擦操作命令

1、增加字段数据内容

1.1字段增加数据

1.2增加指定字段的数据

2、删除字段数据

2.1、物理删除，数据就真没有了（无法找回）

2.2逻辑删除，数据可以恢复（建议使用）

2.2.1逻辑删除t2表中id=3的数据

2.2.2恢复逻辑删除的数据t2表中的id=3的数据

3、更该字段中的数据内容

4、查看字段数据内容

4.1全字段查找，不建议使用会，数据量很大会崩溃数据库服务器

4.2查看指定字段的数据内容

5、更具运算符查找

5.1运算符查找

5.2同时满足多个条件或者几个条件，满足其一即可的查找

6、模糊查找like

6.1使用%来匹配多个字符

6.2使用“_”来匹配单一字符

7、范围查询

7.1非连续范围“in”

7.2连续的范围“between”

7.3不包含的行not in

7.4查找空值（NULL）

8、聚合

9、排序

10、分组

11、 限制

四、视图操作

1、创建视图

2、查看视图

3、删除视图

4、修改视图

11、限制