Mysql数据库最基础的查询语句(DDL语句和DML语句)

最新推荐文章于 2023-04-12 08:57:49 发布

麦兜没有糖

最新推荐文章于 2023-04-12 08:57:49 发布

阅读量367

点赞数 1

分类专栏：数据库mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45487019/article/details/103911162

版权

数据库mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Mysql数据库最基础的查询语句(DDL语句和DML语句)

基础语句整理
代码展示：
C:\Users\Administrator>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.18 MySQL Community Server - GPL

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> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> use test1;
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table person(person_id int(10),person_age int(10),person_name varchar(20));
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> create table emp(emp_id int(10),emp_sal int(20));
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> alter table person modify person_name varchar(20) first;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table person modify person_id int(10) first;
Query OK, 0 rows affected, 1 warning (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> alter table emp add emp_level int(5) after emp_id;
Query OK, 0 rows affected, 1 warning (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> alter table emp drop column emp_level;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table emp rename emp1;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table emp1 rename emp;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table emp;
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp | CREATE TABLE emp (
emp_id int(10) DEFAULT NULL,
emp_sal int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into person values(‘1’,‘茄子’,‘15’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into person values(‘2’,‘土豆’,‘30’),(‘3’,‘辣椒’,‘20’);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> insert into person(person_id,person_name,person_age) values(‘4’,‘萝卜’,‘15’);
Query OK, 1 row affected (0.01 sec)

mysql> select * from person;
±----------±------------±-----------+
| person_id | person_name | person_age |
±----------±------------±-----------+
| 1 | 茄子 | 15 |
| 2 | 土豆 | 30 |
| 3 | 辣椒 | 20 |
| 4 | 萝卜 | 15 |
±----------±------------±-----------+
4 rows in set (0.00 sec)

mysql> insert into emp values(‘1’,‘4000’),(‘2’,‘9000’),(‘4’,‘5000’),(‘6’,‘9000’),(‘2’,‘5000’);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from emp;
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 1 | 4000 |
| 2 | 9000 |
| 4 | 5000 |
| 6 | 9000 |
| 2 | 5000 |
±-------±--------+
5 rows in set (0.00 sec)

mysql> select * from emp order by emp_id;
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 1 | 4000 |
| 2 | 9000 |
| 2 | 5000 |
| 4 | 5000 |
| 6 | 9000 |
±-------±--------+
5 rows in set (0.00 sec)

mysql> select * from emp order by emp_id desc;
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 6 | 9000 |
| 4 | 5000 |
| 2 | 9000 |
| 2 | 5000 |
| 1 | 4000 |
±-------±--------+
5 rows in set (0.00 sec)

mysql> select * from emp order by emp_id,emp_sal desc;
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 1 | 4000 |
| 2 | 9000 |
| 2 | 5000 |
| 4 | 5000 |
| 6 | 9000 |
±-------±--------+
5 rows in set (0.00 sec)

mysql> select * from emp order by emp_id,emp_sal desc limit 3;
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 1 | 4000 |
| 2 | 9000 |
| 2 | 5000 |
±-------±--------+
3 rows in set (0.00 sec)

mysql> select * from emp order by emp_id,emp_sal desc limit 1,2;
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 2 | 9000 |
| 2 | 5000 |
±-------±--------+
2 rows in set (0.00 sec)

mysql> select count(emp_id) from emp;
±--------------+
| count(emp_id) |
±--------------+
| 5 |
±--------------+
1 row in set (0.00 sec)

mysql> select sum(emp_sal),max(emp_sal),min(emp_sal) from emp;
±-------------±-------------±-------------+
| sum(emp_sal) | max(emp_sal) | min(emp_sal) |
±-------------±-------------±-------------+
| 32000 | 9000 | 4000 |
±-------------±-------------±-------------+
1 row in set (0.00 sec)

mysql> select person_id,person_name,emp_sal from person,emp where person.person_id = emp.emp_id;
±----------±------------±--------+
| person_id | person_name | emp_sal |
±----------±------------±--------+
| 1 | 茄子 | 4000 |
| 2 | 土豆 | 9000 |
| 4 | 萝卜 | 5000 |
| 2 | 土豆 | 5000 |
±----------±------------±--------+
4 rows in set (0.00 sec)

mysql> select * from emp where emp_id in (select person_id from person);
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 1 | 4000 |
| 2 | 9000 |
| 4 | 5000 |
| 2 | 5000 |
±-------±--------+
4 rows in set (0.00 sec)

mysql> select person_id,person_name from person;
±----------±------------+
| person_id | person_name |
±----------±------------+
| 1 | 茄子 |
| 2 | 土豆 |
| 3 | 辣椒 |
| 4 | 萝卜 |
±----------±------------+
4 rows in set (0.00 sec)

mysql> update emp set emp_sal=‘6000’ where emp_id=‘1’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from emp;
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 1 | 6000 |
| 2 | 9000 |
| 4 | 5000 |
| 6 | 9000 |
| 2 | 5000 |
±-------±--------+
5 rows in set (0.00 sec)

mysql> update person p,emp e set e.emp_sal=e.emp_sal*p.person_id where p.person_id=e.emp_id;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 4 Changed: 3 Warnings: 0

mysql> select * from emp;
±-------±--------+
| emp_id | emp_sal |
±-------±--------+
| 1 | 6000 |
| 2 | 18000 |
| 4 | 20000 |
| 6 | 9000 |
| 2 | 10000 |
±-------±--------+
5 rows in set (0.00 sec)

mysql> delete from person where person_id=‘4’;
Query OK, 1 row affected (0.01 sec)

mysql> select * from person;
±----------±------------±-----------+
| person_id | person_name | person_age |
±----------±------------±-----------+
| 1 | 茄子 | 15 |
| 2 | 土豆 | 30 |
| 3 | 辣椒 | 20 |
±----------±------------±-----------+
3 rows in set (0.00 sec)