人生似水岂无涯,浮云吹作雪,世味煮成茶...

MySQL数据库基本操作

2022年05月03日 19:03    0 人评论    837 人阅读

数据库操作

1、查看在服务器上当前存在什么数据库:

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| openfire           |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

2、创建一个数据库:

create database 数据库名;

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

3、使用你所创建的数据库

use 数据库名;

mysql> use test;
Database changed

4、删除数据库

drop database 数据库名;

mysql> drop database test;
Query OK, 1 row affected (0.02 sec)

数据表操作

1、查看数据库中存在什么表:

show tables;

mysql> show tables;
Empty set (0.00 sec)

2、创建一个数据库表

CREATE TABLE table_name (column_name column_type);

mysql> create table user_tbl( id int not null auto_increment, name varchar(10), age int, primary key(id) );
Query OK, 0 rows affected (0.02 sec)

3、显示表的结构:

mysql> describe user_tbl;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

4、删除表

mysql> drop table user_tbl;

数据操作

插入数据

1、向MySQL数据表插入数据通用的 INSERT INTO SQL语法:

INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

mysql> insert into user_tbl (name, age) values ("Python", 25);
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_tbl (name, age) values ("PHP", 21);
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_tbl (name, age) values ("Java", 21);
Query OK, 1 row affected (0.00 sec)

查询数据

1、普通查询

SELECT field1, field2,...fieldN from table_name;

mysql> select * from user_tbl;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | Python |   25 |
|  2 | PHP    |   21 |
|  3 | Java   |   21 |
+----+--------+------+
3 rows in set (0.00 sec)

2、带条件的查询

SELECT * from table_name where 条件;

mysql> select * from user_tbl where id=1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | Python |   25 |
+----+--------+------+
1 row in set (0.00 sec)

3、字段运算函数

计算表中有多少条数据:

SELECT 函数(字段) from table_name;

mysql> select count(*) from user_tbl;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

找出最大的值(年龄)

mysql> select max(age) from user_tbl;
+----------+
| max(age) |
+----------+
|       25 |
+----------+
1 row in set (0.00 sec)

4、排序函数

SELECT * from table_name ORDER BY field ASC(正序)/DESC(倒序);

mysql> select * from user_tbl order by age ASC;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | PHP    |   21 |
|  3 | Java   |   21 |
|  1 | Python |   25 |
+----+--------+------+
3 rows in set (0.00 sec)

mysql> select * from user_tbl order by age DESC;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | Python |   25 |
|  2 | PHP    |   21 |
|  3 | Java   |   21 |
+----+--------+------+
3 rows in set (0.00 sec)

5、模糊查询

SELECT * from table_name where field like ‘%somevalue%';

mysql> select * from user_tbl where name like '%P%';
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | Python |   25 |
|  2 | PHP    |   21 |
+----+--------+------+
2 rows in set (0.00 sec)

6、LIMIT(取值)

SELECT * from table_name limit 起点,条数;

mysql> select * from user_tbl limit 2,1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  3 | Java |   21 |
+----+------+------+
1 row in set (0.00 sec)

7、更新数据

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

mysql> update user_tbl set name="JavaScript" where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_tbl;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | Python     |   25 |
|  2 | PHP        |   21 |
|  3 | JavaScript |   21 |
+----+------------+------+
3 rows in set (0.00 sec)

8、删除数据

delete from table_name [WHERE Clause];

mysql> delete from user_tbl where id=2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from user_tbl;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | Python     |   25 |
|  3 | JavaScript |   21 |
+----+------------+------+
2 rows in set (0.00 sec)

如果文章对您有所帮助, 请随意打赏! 您的支持将鼓励我写出更好的文章!

发表评论 已发布 0