Mysql基本操作
启动mysql
运行到安装目录
C:\Program Files\MySQL\MySQL Server 5.6\bin>
输入
mysqld.exe –install
得到结果
Service successfully installed
输入
net start mysql //启动mysql
net stop mysql //关闭mysql
用户成功登录
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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>
数据库操作
查看数据库的版本号和服务器当前时间
mysql> select version(),current_date;
输出:
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 5.6.10 | 2013-08-05 |
+-----------+--------------+
1 row in set (0.01 sec)
查看服务器的所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
创建数据库
mysql> create database leaf;
Query OK, 1 row affected (0.01 sec)
mysql> use leaf;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| leaf |
+------------+
1 row in set (0.00 sec)
创建表
mysql> create table leaf(leaf_id varchar(6)
-> ,leaf_name varchar(10)
-> ,leaf_age int
-> ,leaf_sal int
-> ,leaf_bir date
-> ,leaf_sex varchar(5)
-> );
Query OK, 0 rows affected (0.13 sec)
显示当前数据库的表
mysql> show tables;
+----------------+
| Tables_in_leaf |
+----------------+
| leaf |
+----------------+
1 row in set (0.00 sec)
查看数据表的结构
mysql> describe leaf;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| leaf_id | varchar(6) | YES | | NULL | |
| leaf_name | varchar(10) | YES | | NULL | |
| leaf_age | int(11) | YES | | NULL | |
| leaf_sal | int(11) | YES | | NULL | |
| leaf_bir | date | YES | | NULL | |
| leaf_sex | varchar(5) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
往表中插入数据
mysql> insert into leaf values
-> ('101','leaf','10','6000','2013-8-5','male'),
-> ('102','lea','20','5000','2013-8-4','male'),
-> ('103','le','30','4000','2013-8-3','female'),
-> ('104','l','35','4000','2013-8-2','female');
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 2
查看表中数据
mysql> select * from leaf;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 101 | leaf | 10 | 6000 | 2013-08-05 | male |
| 102 | lea | 20 | 5000 | 2013-08-04 | male |
| 103 | le | 30 | 4000 | 2013-08-03 | femal |
| 104 | l | 35 | 4000 | 2013-08-02 | femal |
+---------+-----------+----------+----------+------------+----------+
4 rows in set (0.00 sec)
修改数据
mysql> update leaf set leaf_id=100 where leaf_name='leaf';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from leaf;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 100 | leaf | 10 | 6000 | 2013-08-05 | male |
| 102 | lea | 20 | 5000 | 2013-08-04 | male |
| 103 | le | 30 | 4000 | 2013-08-03 | femal |
| 104 | l | 35 | 4000 | 2013-08-02 | femal |
+---------+-----------+----------+----------+------------+----------+
4 rows in set (0.00 sec)
对表中数据薪水项全部加2000
mysql> update leaf set leaf_sal=leaf_sal+2000;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from leaf;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 100 | leaf | 10 | 8000 | 2013-08-05 | male |
| 102 | lea | 20 | 7000 | 2013-08-04 | male |
| 103 | le | 30 | 6000 | 2013-08-03 | femal |
| 104 | l | 35 | 6000 | 2013-08-02 | femal |
+---------+-----------+----------+----------+------------+----------+
4 rows in set (0.00 sec)
查询
mysql> select * from leaf where leaf_name='leaf';
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 100 | leaf | 10 | 8000 | 2013-08-05 | male |
+---------+-----------+----------+----------+------------+----------+
1 row in set (0.00 sec)
查询表中，leaf_sal>6000的信息
mysql> select * from leaf where leaf_sal>6000;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 100 | leaf | 10 | 8000 | 2013-08-05 | male |
| 102 | lea | 20 | 7000 | 2013-08-04 | male |
+---------+-----------+----------+----------+------------+----------+
2 rows in set (0.00 sec)
查询所有人工资情况
mysql> select leaf_name,leaf_sal from leaf;
+-----------+----------+
| leaf_name | leaf_sal |
+-----------+----------+
| leaf | 8000 |
| lea | 7000 |
| le | 6000 |
| l | 6000 |
+-----------+----------+
4 rows in set (0.00 sec)
查询结果排序，按照工资低到高排序
mysql> select * from leaf order by leaf_sal;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 103 | le | 30 | 6000 | 2013-08-03 | femal |
| 104 | l | 35 | 6000 | 2013-08-02 | femal |
| 102 | lea | 20 | 7000 | 2013-08-04 | male |
| 100 | leaf | 10 | 8000 | 2013-08-05 | male |
+---------+-----------+----------+----------+------------+----------+
4 rows in set (0.00 sec)
按照工资由高到低排序
mysql> select * from leaf order by leaf_sal desc;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 100 | leaf | 10 | 8000 | 2013-08-05 | male |
| 102 | lea | 20 | 7000 | 2013-08-04 | male |
| 103 | le | 30 | 6000 | 2013-08-03 | femal |
| 104 | l | 35 | 6000 | 2013-08-02 | femal |
+---------+-----------+----------+----------+------------+----------+
4 rows in set (0.00 sec)
查看工资前两名
mysql> select * from leaf order by leaf_sal desc limit 2;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 100 | leaf | 10 | 8000 | 2013-08-05 | male |
| 102 | lea | 20 | 7000 | 2013-08-04 | male |
+---------+-----------+----------+----------+------------+----------+
2 rows in set (0.00 sec)
随机查看两人
mysql> select * from leaf order by rand() limit 2;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 104 | l | 35 | 6000 | 2013-08-02 | femal |
| 103 | le | 30 | 6000 | 2013-08-03 | femal |
+---------+-----------+----------+----------+------------+----------+
2 rows in set (0.00 sec)
查询8月份出生的人
mysql> select * from leaf where month(leaf_bir)=8;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 100 | leaf | 10 | 8000 | 2013-08-05 | male |
| 102 | lea | 20 | 7000 | 2013-08-04 | male |
| 103 | le | 30 | 6000 | 2013-08-03 | femal |
| 104 | l | 35 | 6000 | 2013-08-02 | femal |
+---------+-----------+----------+----------+------------+----------+
4 rows in set (0.00 sec)
数据统计
mysql> select count(*) from leaf;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
使用统计函数
mysql> select
-> min(leaf_sal) as min_salary,
-> max(leaf_sal) as max_salary,
-> sum(leaf_sal) as sum_salary,
-> avg(leaf_sal) as avg_salary,
-> count(*) as employee_num
-> from leaf;
+------------+------------+------------+------------+--------------+
| min_salary | max_salary | sum_salary | avg_salary | employee_num |
+------------+------------+------------+------------+--------------+
| 6000 | 8000 | 27000 | 6750.0000 | 4 |
+------------+------------+------------+------------+--------------+
1 row in set (0.00 sec)
表单数据删除
mysql> delete from leaf where leaf_name='l';
Query OK, 1 row affected (0.06 sec)
mysql> select * from leaf;
+---------+-----------+----------+----------+------------+----------+
| leaf_id | leaf_name | leaf_age | leaf_sal | leaf_bir | leaf_sex |
+---------+-----------+----------+----------+------------+----------+
| 100 | leaf | 10 | 8000 | 2013-08-05 | male |
| 102 | lea | 20 | 7000 | 2013-08-04 | male |
| 103 | le | 30 | 6000 | 2013-08-03 | femal |
+---------+-----------+----------+----------+------------+----------+
3 rows in set (0.00 sec)
数据表重命名
alter table 原始表名 rename as 新表名
mysql> alter table leaf rename as lea;
Query OK, 0 rows affected (0.09 sec)
删除数据表
mysql> drop database leaf;
官方示例数据库
C:\Users\Administrator\Downloads\employees_db-full-1.0.6\employees_db>mysql -t -u root -p < employees.sql
====================================================================
Author by Leaf, Email: leaf816@gmail.com
====================================================================
