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 —— 在CentOS9下安装MySQL

MySQL 中开启二进制日志（Binlog）

mysql 中的all,5分钟了解MySQL5.7中union all用法的黑科技

查看>>

MySQL 中的外键检查设置：SET FOREIGN_KEY_CHECKS = 1

MySQL 中随机抽样：order by rand limit 的替代方案

查看>>

MySQL 为什么需要两阶段提交？

查看>>

mysql 为某个字段的值加前缀、去掉前缀

查看>>

mysql 主从

查看>>

mysql 主从 lock_mysql 主从同步权限mysql 行锁的实现

查看>>

mysql 主从互备份_mysql互为主从实战设置详解及自动化备份(Centos7.2)

查看>>