Mysql基本操作-白红宇

Mysql基本操作

阅读量：789 次

发布时间：2023-02-12

本文共 10617 字，大约阅读时间需要 35 分钟。

启动mysql

运行到安装目录C:\Program Files\MySQL\MySQL Server 5.6\bin>输入mysqld.exe –install得到结果Service successfully installed输入net start mysql  //启动mysqlnet stop mysql  //关闭mysql

用户成功登录

Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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: 0mysql> 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: 0mysql> 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

====================================================================

转载于:https://www.cnblogs.com/lea-fu/p/3238079.html

你可能感兴趣的文章

mysql 查看锁_阿里/美团/字节面试官必问的Mysql锁机制，你真的明白吗

查看>>

MySql 查询以逗号分隔的字符串的方法（正则）

查看>>

MySQL 查询优化：提速查询效率的13大秘籍（避免使用SELECT 、分页查询的优化、合理使用连接、子查询的优化）（上）

mysql 查询，正数降序排序，负数升序排序

查看>>

MySQL 树形结构根据指定节点获取其下属的所有子节点（包含路径上的枝干节点和叶子节点）...

查看>>

mysql 死锁 Deadlock found when trying to get lock； try restarting transaction

查看>>

mysql 死锁(先delete 后insert)日志分析

查看>>

MySQL 死锁了，怎么办？

查看>>

MySQL 深度分页性能急剧下降，该如何优化？

查看>>

MySQL 深度分页性能急剧下降，该如何优化？

MySQL 用 limit 为什么会影响性能？

查看>>

MySQL 用 limit 为什么会影响性能？有什么优化方案？

查看>>

MySQL 用户权限管理：授权、撤销、密码更新和用户删除（图文解析）

查看>>

mysql 用户管理和权限设置

查看>>