Mysql必知必会一(ubuntu)

1. 连接Mysql数据库

mysql -u root -p ××××

××××：代表密码，如果不输入的话会有提示输入密码:

Enter password: 

这时候输入密码是不可见的，比较安全。成功后就有以下输出：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2. 了解数据库和表

(1). 查询系统所有的数据库：

show databases;

这时候会显示系统中所有数据库的名字：

+--------------------+
| Database           |
+--------------------+
| information_schema |
| delete_database    |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

(2). 显示当前数据库的所有表名：

show tables;

如果当前没有选择表格，会显示：

ERROR 1046 (3D000): No database selected

选择了的话，就会显示里面所有的表格：

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

3. 选择数据库

use mysql;

mysql是系统中的数据库的表名。

4. 创建和删除数据库

create database Goods;

创建名字为Goods的数据库，如果数据库存在，就会显示错误：

ERROR 1007 (HY000): Can't create database 'Goods'; database exists

否则就会成功创建：

Query OK, 1 row affected (0.00 sec)

以下命令是删除数据库的：

drop database Goods;

存在该数据库的话就会删除该数据库，并输出：

Query OK, 0 rows affected (0.02 sec)

否则，则会输出：

ERROR 1008 (HY000): Can't drop database 'Goods'; database doesn't exist

5. 创建，删除和重命名数据库里面的表格

create table customes
(
    cust_id int not null auto_increment,
    cust_name char(50) not null,
    cust_address char(50) null,
    cust_city char(50) null,
    cust_state char(5) null,
    cust_zip char(10) null,
    cust_country char(50) null,
    cust_contact char(50) null,
    cust_email char(255) null,
    primary key (cust_id)
)engine=InnoDB;

创建表格的一个格式，create table ***();

解释一下：

1. auto_increment表示这个变量是会自增存储的，也就是如果你给它一个null他会根据当前的值来+1然后变成自己的ID并保存起来。

2. engine=InnoDB指定数据库引擎。

3. primary key (cust_id):指定cust_id为主码。

成功的会就会显示：

Query OK, 0 rows affected (0.31 sec)

失败的会会显示：

ERROR 1050 (42S01): Table 'customes' already exists

删除数据表和删除数据库的方法是一样的，以下是命令：

 drop table customes;

重命名数据表：

rename table customes to custome;

将名字为customes的数据表重命名为custome。

6. 插入元素

insert into customers values(
     NULL,
     'Pep E. LaPew',
     '100Main Street',
     'Los Angeles',
     'CA',
     '90046','USA',
     NULL,
     NULL
);

成功就会显示：

Query OK, 1 row affected (0.05 sec)

失败，可能违反主码约束什么的，就会显示：

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

也可以按照下面这种格式来写，这种比较安全，毕竟上面那个是按照次序来录入的，有时候忘记顺序是很麻烦的一件事，你可能会将市区变成街区，ID变成金钱(这种)

insert into customers(
     cust_name,
     cust_address,
     cust_city,
     cust_state,
     cust_zip,
     cust_country,
     cust_contact,
     cust_email)
values( 
     'Pep E. LaPew',
     '100Main Street',
     'Los Angeles',
     'CA',
     '90046',
     'USA',
     NULL,
     NULL
);

这种情况还有一个好处，你可以不用记住数据库是如何定义的，因为你插入的顺序和你排的顺序是一样的。

插入多行的情况：

insert into customers(
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
values(
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'USA'
    ),(
    'M. Martian',
    '42 Galaxy Way',
    'New York',
    'NY',
    '11213',
    'USA'
);

成功的提示都是一样的们这里就不把结果放出来。

7.  检索数据

为什么不介绍修改和删除数据呢！那是因为为了方便理解，需要先学会检索数据才能看出修改和删除的结果。

(1). 检索单个列：

select cust_id from customers;

结果显示为：

+---------+
| cust_id |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
+---------+
5 rows in set (0.00 sec)

(2). 检索多个列：

select cust_id,cust_name,cust_address from customers;

结果显示为：

+---------+--------------+-----------------+
| cust_id | cust_name    | cust_address    |
+---------+--------------+-----------------+
|       1 | Pep E. LaPew | 100 Main Street |
|       2 | Pep E. LaPew | 100Main Street  |
|       3 | Pep E. LaPew | 100Main Street  |
|       4 | Pep E. LaPew | 100 Main Street |
|       5 | M. Martian   | 42 Galaxy Way   |
+---------+--------------+-----------------+
5 rows in set (0.00 sec)

(3). 检索所有的列：

select * from customers;

显示的结果为：

+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name    | cust_address    | cust_city   | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
|       1 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       2 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       3 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       4 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       5 | M. Martian   | 42 Galaxy Way   | New York    | NY         | 11213    | USA          | NULL         | NULL       |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
5 rows in set (0.00 sec)

(4). 检索不同的行：

select distinct cust_name from customers;

输出不同的名字，相同的名字过滤掉，显示的结果为：

+--------------+
| cust_name    |
+--------------+
| Pep E. LaPew |
| M. Martian   |
+--------------+
2 rows in set (0.00 sec)

(5). 限制检索的结果数量：

select * from customers limit 3;

限制输出为三行，下面是结果：

+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name    | cust_address    | cust_city   | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
|       1 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       2 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       3 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
3 rows in set (0.00 sec)

(6). 使用完全限定表名：

 select * from delete_database.customers;

显示的结果和select * from delete_database.customers;一样。到此，检索的知识也就一段落了！

8. 过滤数据

(1).使用where子句：

select * from customers where cust_id = 1;

这句sql语句表示从customers里面选择cust_id = 1的列，并输出所有的元素。

查询结果为：

+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name    | cust_address    | cust_city   | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
|       1 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
1 row in set (0.03 sec)

(2).where子句的操作符：

    = ,<>,!=,<,>,<=,>=,between（等于，不等于，不等于，小于，大于，小于等于，大于等于，在什么之间）

(3).检查单个值

    例子和第一个一样，不再多说明。

(4). 不匹配检查

select * from customers where cust_id <> 1;
select * from customers where cust_id != 1;

上面两个是等价的。输出结果为：

+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name    | cust_address    | cust_city   | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
|       2 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       3 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       4 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       5 | M. Martian   | 42 Galaxy Way   | New York    | NY         | 11213    | USA          | NULL         | NULL       |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
4 rows in set (0.00 sec)

(5). 范围值检查

select * from customers where cust_id between 1 and 3;

上面的语句表示输出cust_id范围在1到3之间的列。结果显示为：

+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
| cust_id | cust_name    | cust_address    | cust_city   | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
|       1 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       2 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
|       3 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL       |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+------------+
3 rows in set (0.00 sec)

(6). 空值检查

9. 更新数据和删除数据

(1). 更新数据

update customers set cust_name = 'The Fudds',cust_email='elmer@fudd.com' where cust_id = 3;

成功的话会输出：

Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看数据表信息：

+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
| cust_id | cust_name    | cust_address    | cust_city   | cust_state | cust_zip | cust_country | cust_contact | cust_email     |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
|       1 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL           |
|       2 | Pep E. LaPew | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | NULL           |
|       3 | The Fudds    | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | elmer@fudd.com |
|       4 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL           |
|       5 | M. Martian   | 42 Galaxy Way   | New York    | NY         | 11213    | USA          | NULL         | NULL           |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
5 rows in set (0.00 sec)

此时数据已经修改。

(2). 删除数据

delete from customers where cust_id = 2;

成功就会输出：

Query OK, 1 row affected (0.04 sec)

查看数据表：

+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
| cust_id | cust_name    | cust_address    | cust_city   | cust_state | cust_zip | cust_country | cust_contact | cust_email     |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
|       1 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL           |
|       3 | The Fudds    | 100Main Street  | Los Angeles | CA         | 90046    | USA          | NULL         | elmer@fudd.com |
|       4 | Pep E. LaPew | 100 Main Street | Los Angeles | CA         | 90046    | USA          | NULL         | NULL           |
|       5 | M. Martian   | 42 Galaxy Way   | New York    | NY         | 11213    | USA          | NULL         | NULL           |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
4 rows in set (0.00 sec)