数据库入门

新建完 mini文件之后，管理员打开cmd进入bin的目录

#初始化数据库
mysqld --initialize --console

记住密码，然后重新设置密码

命令符下登陆数据库

mysql -h 主机名 -u 用户名 -p密码
#参数说明：

-h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
-u : 登录的用户名;
-p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。

#cmd登陆数据库,展示所有数据库命令
show databases;

#创建新数据库
create database；

#root登陆数据库，删除数据库
mysql> show databases

#查看表内容：desc 表名
mysql> desc person
    -> ;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| fname  | varchar(255)  | NO   |     | NULL    |       |
| iname  | varchar(255)  | NO   |     | NULL    |       |
| gender | enum('m','f') | NO   |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#查询多个列，用，隔开
mysql> select prod_desc , prod_name from products
    -> ;
+----------------------------------------------------------------+----------------+
| prod_desc                                                      | prod_name      |
+----------------------------------------------------------------+----------------+
| .5 ton anvil, black, complete with handy hook                  | .5 ton anvil   |
| 1 ton anvil, black, complete with handy hook and carrying case | 1 ton anvil    |
| 2 ton anvil, black, complete with handy hook and carrying case | 2 ton anvil    |
| Detonator (plunger powered), fuses not included                | Detonator      |
| Large bag (suitable for road runners)                          | Bird seed      |
| Carrots (rabbit hunting season only)                           | Carrots        |
| 1 dozen, extra long                                            | Fuses          |
| JetPack 1000, intended for single use                          | JetPack 1000   |
| JetPack 2000, multi-use                                        | JetPack 2000   |
| Oil can, red                                                   | Oil can        |
| Safe with combination lock                                     | Safe           |
| Sling, one size fits all                                       | Sling          |
| TNT, red, single stick                                         | TNT (1 stick)  |
| TNT, red, pack of 10 sticks                                    | TNT (5 sticks) |
+----------------------------------------------------------------+----------------+
14 rows in set (0.00 sec)

mysql> select vend_id from products
    -> ;
+---------+
| vend_id |
+---------+
|    1001 |
|    1001 |
|    1001 |
|    1002 |
|    1002 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1005 |
|    1005 |
+---------+
14 rows in set (0.00 sec)
#distinct去重关键字,**用在列名前**
mysql> select distinct vend_id from products ;
+---------+
| vend_id |
+---------+
|    1001 |
|    1002 |
|    1003 |
|    1005 |
+---------+
4 rows in set (0.03 sec)

在不选择数据库和表名的时候可以给搜索项添加限制直接查询

mysql> select products.vend_id
    -> from crashcourse.products
    -> ;
+---------+
| vend_id |
+---------+
|    1001 |
|    1001 |
|    1001 |
|    1002 |
|    1002 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1003 |
|    1005 |
|    1005 |
+---------+
14 rows in set (0.00 sec)

#查询表前5行
mysql> select prod_name
    -> from products
    -> limit 5
    -> ;
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
| Detonator    |
| Bird seed    |
+--------------+
5 rows in set (0.00 sec)

#查询表从第五行开始的五行
mysql>  select prod_name
    -> from products
    -> limit 5,5
    -> ;
+--------------+
| prod_name    |
+--------------+
| Carrots      |
| Fuses        |
| JetPack 1000 |
| JetPack 2000 |
| Oil can      |
+--------------+
5 rows in set (0.00 sec)

排序和限制一起使用：注意from后面先排序后做限制 ---先Oder by 在 limit
mysql> select prod_price from products
    -> order by prod_price desc
    -> limit 5,5
    -> ;
+------------+
| prod_price |
+------------+
|      10.00 |
|      10.00 |
|       9.99 |
|       8.99 |
|       5.99 |
+------------+
5 rows in set (0.00 sec)