本文展示了如何在MySQL中创建数据库、选择并使用数据库、创建表、查看表结构、插入数据、更新数据以及删除表。通过示例操作，包括全量和部分插入数据，以及条件查询，如单条件、多条件、区间查询等。
总结：
创数据库[create database +数据库名；]
查数据库[show databases；]，
删除数据库【drop database +数据库名】
创建库[create database +数据库名；]，
使用数据库{user +数据库名}，
创建表{创建 表格 表名t1 (列名id 类型int ); //int整数类型，默认12位数 }，
查看所有表{show tables；}，
查看表结构（和创表一样或是查询所有列）{desc 表名}，
插入数据，
完整插入语法：insert into 表名 values（值1，值2...值n）；
部分插入语法：insert into 表名（列名，列名） values（值1，值2）;
查看表数据内容{select * from 表名;}
更新表数据，{语法：update 表名 set 列名=值 where condition；}
删除表 {drop table +表名}
插入数据语法格式
insert into 表名（字段名1，字段名2，字段名3，...） value(值1，值2，值3，...);
注意：字段名和值要一一对应，数量要对应，数据类型也要对应。
实战实操如下：
mysql> show databases;查询数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| CW |
| haha |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
7 rows in set (0.00 sec)
mysql> use haha进入数据库，haha数据库提前创建好，这里不说了，直接使用
Database changed
mysql> create table employee5(创建表
-> id int primary key AUTO_INCREMENT not null,
-> name varchar(30) not null,
-> sex enum('male','female') default 'male' not null,
-> hire_date date not null,
-> post varchar(50) not null,
-> job_description varchar(100),
-> salary double(15,2) not null,
-> office int,
-> dep_id int
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc employee5;查表结构
+-----------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job_description | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+-----------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
插入数据库
mysql> insert into employee5(name, sex,hire_date,post,job_description,salary, office,dep_id) values ('jack','male','20180202','instructor','teach', 5000,501,100), ('tom','male','20180203','instructor','teach', 5500,501,100), ('robin','male','20180202','instructor','teach', 8000,501,100), ('alice','female','20180202','instructor','teach', 7200,501,100), ('aofa','male','20180202','hr','hrcc',600,502, 101), ('harry','male','20180202','hr',NULL,6000,502, 101), ('emma','female','20180206','sale','salecc', 20000,503,102), ('christine','female','20180205','sale','salecc', 2200,503,102), ('zhuzhu','male','20180205','sale',NULL,2200, 503,102), ('gougou','male','20180205','sale','',2200,503, 102);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from employee5;查数据内容
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)
mysql> mysql> select * from employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)
增加数据内容
mysql> insert into employee5(name,sex,hire_date,post,job_description,salary,office,dep_id)增加 values ('jack','male','20180202','instructor','teach',5000,501,100), ('tom','male','20180203','instructor','teach',5500,501,100), ('robin','male','20180202','instructor','teach',8000,501,100), ('alice','female','20180202','instructor','teach',7200,501,100), ('aofa','male','20180202','hr','hrcc',600,502,101), ('harry','male','20180202','hr',NULL,6000,502,101), ('emma','female','20180206','sale','salecc',20000,503,102), ('christine','female','20180205','sale','salecc',2200,503,102), ('zhuzhu','male','20180205','sale',NULL,2200,503,102), ('gouugou','male','20180205','sale','',2200,503,102);
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
增加数据库内容
mysql> insert into employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000,501,100), ('tom','male','20180203','instructor','teach',5500,501,100), ('robin','male','20180202','instructor','teach',8000,501,100), ('alice','female','20180202','instructor','teach',7200,501,100), ('aofa','male','20180202','hr','hrcc',600,502,101), ('harry','male','20180202','hr',NULL,6000,502,101), ('emma','female','20180206','sale','salecc',20000,503,102), ('christine','female','20180205','sale','salecc',2200,503,102), ('zhuzhu','male','20180205','sale',NULL,2200,503,102), ('gougou','male','20180205','sale','',2200,503,102),
-> ('jane','male','20191214','sale','NULL','3000','503','102');
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from employee5; 查询数据库内容
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| 1 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
| 11 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 12 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 13 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 14 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 15 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 16 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 17 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 18 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 19 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 20 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
| 21 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 22 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 23 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 24 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 25 | aofa | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 26 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 27 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 28 | christine | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 29 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 30 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
| 31 | jane | male | 2019-12-14 | sale | NULL | 3000.00 | 503 | 102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
31 rows in set (0.00 sec)
下面是条件查询
一、简单查询 简单查询 查看所有列 前提是需要进入数据库。 不进入数据库，就请输入库名。 SELECT * FROM 表名;
查部分列 SELECT 列1，列2，列3 FROM 表名;
通过四则运算查询 看一看年薪 SELECT name, salary, salary*14 FROM employee5;
单条件查询where
查询hr部门的员工姓名 SELECT name,post FROM employee5 WHERE post='hr';
多条件查询AND/OR
查询hr部门的员工，并且工资大于1000 SELECT name,salary FROM employee5 WHERE post='hr' AND salary>1000;
查询所有部门的员工，并且工资是6000或者 8000的员工 SELECT name, salary FROM employee5 WHERE salary=6000 OR salary=8000 OR AND 关键字BETWEEN AND 在什么之间
需求：查一查薪资在5000到15000 SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
需求：不在5000~15000呢？请使用NOT SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
mysql> select id,name,sex from employee5;
+----+-----------+--------+
| id | name | sex |
+----+-----------+--------+
| 1 | jack | male |
| 2 | tom | male |
| 3 | robin | male |
| 4 | alice | female |
| 5 | aofa | male |
| 6 | harry | male |
| 7 | emma | female |
| 8 | christine | female |
| 9 | zhuzhu | male |
| 10 | gougou | male |
| 11 | jack | male |
| 12 | tom | male |
| 13 | robin | male |
| 14 | alice | female |
| 15 | aofa | male |
| 16 | harry | male |
| 17 | emma | female |
| 18 | christine | female |
| 19 | zhuzhu | male |
| 20 | gougou | male |
| 21 | jack | male |
| 22 | tom | male |
| 23 | robin | male |
| 24 | alice | female |
| 25 | aofa | male |
| 26 | harry | male |
| 27 | emma | female |
| 28 | christine | female |
| 29 | zhuzhu | male |
| 30 | gougou | male |
| 31 | jane | male |
+----+-----------+--------+
31 rows in set (0.00 sec)
mysql> select name,salary *14 from employee5;
+-----------+------------+
| name | salary *14 |
+-----------+------------+
| jack | 70000.00 |
| tom | 77000.00 |
| robin | 112000.00 |
| alice | 100800.00 |
| aofa | 8400.00 |
| harry | 84000.00 |
| emma | 280000.00 |
| christine | 30800.00 |
| zhuzhu | 30800.00 |
| gougou | 30800.00 |
| jack | 70000.00 |
| tom | 77000.00 |
| robin | 112000.00 |
| alice | 100800.00 |
| aofa | 8400.00 |
| harry | 84000.00 |
| emma | 280000.00 |
| christine | 30800.00 |
| zhuzhu | 30800.00 |
| gougou | 30800.00 |
| jack | 70000.00 |
| tom | 77000.00 |
| robin | 112000.00 |
| alice | 100800.00 |
| aofa | 8400.00 |
| harry | 84000.00 |
| emma | 280000.00 |
| christine | 30800.00 |
| zhuzhu | 30800.00 |
| gougou | 30800.00 |
| jane | 42000.00 |
+-----------+------------+
31 rows in set (0.00 sec)
mysql> select name,post from employee5 where post='hr'
-> ;
+-------+------+
| name | post |
+-------+------+
| aofa | hr |
| harry | hr |
| aofa | hr |
| harry | hr |
| aofa | hr |
| harry | hr |
+-------+------+
6 rows in set (0.00 sec)
mysql> select name,post from employee5 where post='hr' and salary>1000;
+-------+------+
| name | post |
+-------+------+
| harry | hr |
| harry | hr |
| harry | hr |
+-------+------+
3 rows in set (0.00 sec)
mysql> select name,post from employee5 where post='hr' and salary=6000 or salary=8000;
+-------+------------+
| name | post |
+-------+------------+
| robin | instructor |
| harry | hr |
| robin | instructor |
| harry | hr |
| robin | instructor |
| harry | hr |
+-------+------------+
6 rows in set (0.00 sec)
mysql> select name,post from employee5 where ='hr' and salary=6000 salary=8000;^C
mysql> select name,post from employee5 where post='hr' salary between 6000 and 15000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'salary between 6000 and 15000' at line 1
mysql> select name,post from employee5 where salary between 6000 and 15000;
+-------+------------+
| name | post |
+-------+------------+
| robin | instructor |
| alice | instructor |
| harry | hr |
| robin | instructor |
| alice | instructor |
| harry | hr |
| robin | instructor |
| alice | instructor |
| harry | hr |
+-------+------------+
9 rows in set (0.00 sec)
mysql> select name,post from employee5 where salary not between 6000 and 15000;
+-----------+------------+
| name | post |
+-----------+------------+
| jack | instructor |
| tom | instructor |
| aofa | hr |
| emma | sale |
| christine | sale |
| zhuzhu | sale |
| gougou | sale |
| jack | instructor |
| tom | instructor |
| aofa | hr |
| emma | sale |
| christine | sale |
| zhuzhu | sale |
| gougou | sale |
| jack | instructor |
| tom | instructor |
| aofa | hr |
| emma | sale |
| christine | sale |
| zhuzhu | sale |
| gougou | sale |
| jane | sale |
+-----------+------------+
22 rows in set (0.00 sec)
mysql>
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
