Mysql 命令

启动：net start mysql

关闭：net stop mysql

登陆：

 

 

创建数据库
create database abc;

选择数据库、显示数据库

use 数据库名字

show databases;

查看表结构：

desc  表名字

显示当前数据库：

select database();

mysql> select database();
+----------------+
| database()     |
+----------------+
| partition_test |
+----------------+
1 row in set (0.04 sec)

删除表和数据

1、drop  table tb 

      drop将表格直接删除，没有办法找回

2、truncate (table) tb

      删除表中的所有数据，不能与where一起使用

3、delete from tb (where)

      删除表中的数据(可制定某一行)

 

更新数据库

在这里，需要注意：数据库的名字不可以修改。

数据库的修改仅限库选项，即字符集和校对集（校对集依赖字符集）。

基本语法：alter database + 数据库名称 + [库选项];

charset/character set[=] 字符集;
collate[=] 校对集;
示例：alter database TBL_ERROR_CODE charset gbk;表示修改此数据库的字符集为gbk.
 

删除数据库 

drop database

mysql> create database dd;
Query OK, 1 row affected (0.01 sec)

mysql> show databases like "d%";
+---------------+
| Database (d%) |
+---------------+
| dd            |
| dxt00         |
+---------------+
2 rows in set (0.00 sec)

mysql> drop database dd;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases like "d%";
+---------------+
| Database (d%) |
+---------------+
| dxt00         |
+---------------+
1 row in set (0.00 sec)

创建表

create table if not exists

mysql> create table if not  exists maomao( name varchar(10), age int )charset utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_dxt00 |
+-----------------+
| maomao          |
| test_load       |
+-----------------+
2 rows in set (0.01 sec)

更改表：https://blog.csdn.net/qq_35246620/article/details/70945890

1）重命名 rename table xx to yy;

mysql> rename table maomao to mao;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_dxt00 |
+-----------------+
| mao             |
| test_load       |
+-----------------+
2 rows in set (0.00 sec)

2)添加字段 alter table mao add price int after age;

mysql> desc mao
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table mao add price int after age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc mao;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| price | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

修改字段，基本语法：alter table + 表名 + modify + 字段名 + 数据类型 + [列属性][位置]; 
其中，位置表示此字段存储的位置，分为first（第一个位置）和after + 字段名（指定的字段后，默认为最后一个位置）.
示例：alter table student modify name char(10) after id;

重命名字段，基本语法：alter table + 表名 + change + 旧字段名 + 新字段名 + 数据类型 + [列属性][位置]; 
其中，位置表示此字段存储的位置，分为first（第一个位置）和after + 字段名（指定的字段后，默认为最后一个位置）.
示例：alter table student change grade class varchar(10);

删除字段，基本语法：alter table + 表名 + drop+ 字段名; 
示例：alter table student drop age;
注意：如果表中已经存在数据，那么删除该字段会清空该字段的所有数据，而且不可逆，慎用。
 

---

表数据操作：增删查改

insert into mao values("mi",11,1000);

update mao set age = 12 where name="mi";

select * from mao;

 delete from mao where name = "mi";

mysql> desc mao;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| price | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into mao values("mi",11,1000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from mao;
+------+------+-------+
| name | age  | price |
+------+------+-------+
| mi   |   11 |  1000 |
+------+------+-------+
1 row in set (0.00 sec)

mysql> update mao set age = 12 where name="mi";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mao;
+------+------+-------+
| name | age  | price |
+------+------+-------+
| mi   |   12 |  1000 |
+------+------+-------+
1 row in set (0.00 sec)

mysql> delete from mao where name = "mi";
Query OK, 1 row affected (0.00 sec)

---

 

数据类型约束：

列属性：实际上，真正约束字段的是数据类型，但是数据类型的约束比较单一，因此就需要额外的一些约束来保证数据的有效性，这就是列属性。

列属性有很多，例如：null、not null、default、primary key、unique key、auto_increment和comment等。
 

mysql> create table my_class(
    -> grade varchar(20) not null,
    -> room varchar(20) null
    -> )charset utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> desc my_class;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| grade | varchar(20) | NO   |     | NULL    |       |
| room  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into my_class values(12,2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_class values(12,NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into my_class values(NULL,NULL);
ERROR 1048 (23000): Column 'grade' cannot be null

 

默认值

默认值：default，某一数据会经常性出现某个具体的值，因此可以在开始的时候就指定好，而在需要真实数据的时候，用户可以选择性的使用默认值。

执行如下 SQL 语句，进行演示：

mysql> create table my_default( 
name varchar(20) not null,
 age tinyint unsigned default 0,
 gender enum('男','女')default '男'
)charset utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc my_default;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 0       |       |
| gender | enum('男','女')     | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

---

主键

添加主键：

1）建表的时候添加主键

 id int(11) primary key,

mysql> create table my_pri(
    -> id int(11) primary key,
    -> name varchar(20) not null
    -> )charset utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc my_pri;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

一张表只能有一个主键： 

mysql> alter table  my_pri add primary key(name);
ERROR 1068 (42000): Multiple primary key defined

2） alter table  my_default add primary key(name);

mysql> alter table  my_default add primary key(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc my_default;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   | PRI | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 0       |       |
| gender | enum('男','女')     | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

主键约束

主键约束，即主键对应的字符中的数据不允许重复，如果重复，则数据操作（主要是增和改）失败。

主键冲突： 

mysql> insert into  my_default values("dxt",1,default);
Query OK, 1 row affected (0.00 sec)

mysql> insert into  my_default values("dxt",2,default);
ERROR 1062 (23000): Duplicate entry 'dxt' for key 'PRIMARY'

删除主键：
 

mysql> alter table  my_default drop primary key;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc my_default;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 0       |       |
| gender | enum('男','女')     | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

自增长：

mysql> create table my_auto(
    -> id int primary key auto_increment,
    -> name varchar(20) not null
    -> )charset utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> desc my_auto;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

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

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

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

mysql> select * from my_auto;
+----+------+
| id | name |
+----+------+
|  1 | dxt  |
|  2 | qun  |
|  3 | dun  |
+----+------+
3 rows in set (0.00 sec)

mysql> 
mysql> insert into my_auto(id,name) values(5,"dun");
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from my_auto;
+----+------+
| id | name |
+----+------+
|  1 | dxt  |
|  2 | qun  |
|  3 | dun  |
|  5 | dun  |
|  6 | dunn |
+----+------+
5 rows in set (0.00 sec)