MySQL数据库基本操作

MySQL数据库基本操作

1.数据库基本操作

• 创建数据库

mysql> create database db_testproject;
Query OK, 1 row affected

• 删除数据库

mysql> drop database db_zwq;
Query OK, 1 row affected

• 数据库查询

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| album_manager      |
| blog               |
| db_testproject     |
| mybatis            |
| mysql              |
| noval_read         |
| performance_schema |
| qingxun            |
| sakila             |
| softwaretest       |
| sys                |
| test               |
| world              |
+--------------------+
14 rows in set

• 数据库字符集设置

1. 创建时设置字符集

mysql> create database db_zwq default character set='utf8';
Query OK, 1 row affected

2. 修改数据库字符集

mysql> alter database db_testproject character set utf8;
Query OK, 1 row affected

2. 数据库对表的基本操作

• 显示数据库中的表

显示当前数据库中有哪些表
mysql> show tables;
+--------------------------+
| Tables_in_db_testproject |
+--------------------------+
| t_employee               |
+--------------------------+
1 row in set


显示具体表的结构
mysql> desc t_employee;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(50) | YES  |     | NULL    |                |
| address | varchar(50) | YES  |     | NULL    |                |
| salary  | double      | YES  |     | NULL    |                |
| sex     | char(1)     | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set

• 创建表

mysql> use db_testproject;
Database changed
mysql> create table t_employee(
    -> id int primary key auto_increment,     primary key设置主键，auto_increment自动增长
    -> name varchar(50),
    -> address varchar(50),
    -> salary double,
    -> sex char(1)
    -> );
Query OK, 0 rows affected

• 删除表

mysql> drop table t_employee;
Query OK, 0 rows affected

3.对表中数据操作

• 表中数据的插入

insert into t_employee(name,address,salary,sex)values("张三","湖南",5654,'m');

• 表中数据的修改

update t_employee set address="上海",salary=8000 where id=1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

• 表中数据的删除

mysql> delete from t_employee where id=8;
Query OK, 1 row affected

• 表的查询

1. 查询所有数据

mysql> select * from t_employee;
+----+------+---------+--------+-----+
| id | name | address | salary | sex |
+----+------+---------+--------+-----+
|  1 | 张三 | 湖南    |   5654 | m   |
+----+------+---------+--------+-----+
1 row in set

2. 查询部分列数据

mysql> select name,address from t_employee;
+------+---------+
| name | address |
+------+---------+
| 张三 | 湖南    |
| 小王 | 广东    |
| 小兰 | 湖北    |
+------+---------+
3 rows in set

3. 按条件查询

a.查询结果排序

desc表示降序，asc表示升序，不写默认为升序
mysql> select * from t_employee order by salary desc;
+----+------+---------+--------+-----+
| id | name | address | salary | sex |
+----+------+---------+--------+-----+
|  6 | 小王 | 广东    |   9854 | m   |
|  7 | 小兰 | 湖北    |   7832 | f   |
|  5 | 张三 | 湖南    |   5654 | m   |
+----+------+---------+--------+-----+
3 rows in set

b.数据库中常用组函数

sum()求和

mysql> select sum(salary) as '总工资' from t_employee;
+--------+
| 总工资 |
+--------+
|  23340 |
+--------+
1 row in set

age()求平均值

mysql> select address,avg(salary) from t_employee group by address;
+---------+-------------+
| address | avg(salary) |
+---------+-------------+
| 广东    |        8972 |
| 湖北    |        7832 |
| 湖南    |        5654 |
+---------+-------------+
3 rows in set

max()求最大值，min()求最小值

mysql> select max(salary) as '最高工资',min(salary) as '最低工资' from t_employee;
+----------+----------+
| 最高工资 | 最低工资 |
+----------+----------+
|     9854 |     5654 |
+----------+----------+
1 row in set

c.数据库中常用行函数

count（*）统计所有行数，count(列名)统计此列，但不包括值为null的行数

mysql> select count(*) from t_employee;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set

mysql> select count(salary) from t_employee;
+---------------+
| count(salary) |
+---------------+
|             4 |
+---------------+
1 row in set