当前位置：首页 > article >正文

Mysql语句分类与如何编写

article 2024/12/5 10:41:07

1、编写SQL语句

1） SQL语句的分类

SQL: Structure Query Language（结构化查询语言），SQL最早是被美国国家标准局（ANSI）确定为关系型数据库语言的美国标准。后来被国际化标准组织（ISO）采纳为关系型数据库语言的国际标准。

国家的统一的语言：普通话各个地方都有方言。每一个数据库都有方言。方言就是独自。

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象（数据库、表、列）

创建一个数据库、创建表、添加字段等

DML（Data Manipulation Language）：数据操作语言，用于定义数据库记录（数据）

数据的新增、修改、删除

DQL（Data Query Language）：数据查询语言，用于查询记录（数据）

专门针对数据的查询的

TCL（Transaction Control Language）：事务控制语言，DCL（Data Control Language）：数据控制语言，用于定义访问权限和安全级别

控制事务、添加用户、赋予权限、修改密码等。

2、DDL语句

数据库的操作：

展示所有的数据库
show databases;
# 创建数据库
CREATE DATABASE mydb01;
# 创建数据库，并采用指定的字符集
CREATE DATABASE mydb02 CHARACTER SET UTF8;
utf8字符集是可以支持中文的，非常的方便。

查看数据库（了解）
# 查看创建数据库mydb1定义的信息
SHOW CREATE DATABASE mydb01;
mysql> show create database mydb01;

1 row in set (0.07 sec)

mysql> alter database mydb01 character set gbk;
Query OK, 1 row affected (0.09 sec)

mysql> show create database mydb01;

# 将数据库mydb1的字符集修改为GBK
ALTER DATABASE mydb1 CHARACTER SET GBK;

DROP DATABASE mydb1; （不重要）

# 切换当前使用的数据库
USE mydb1; 
# 查询当前使用的数据库
SELECT DATABASE();

查看mysql的版本：
select verion();

关于表的操作：

表的增删改查

展示该数据库中的所有表：
show tables;

创建一个表：
语法：
create table 表名( 字段一 数据类型(数据长度),.....);
实战：
create table stu (
   name varchar(255),
   phone varchar(11),
   age int(3)
 );

查看表结构：
desc stu;

# 查看创建一个表的信息
SHOW CREATE TABLE t_users; #不常见

在sql语句中 # 后面的语句不执行，代表注释
修改表的名字
alter table stu rename to student;
以下内容可以练习一遍即可：
# 给一张表添加一个字段
ALTER TABLE t_users ADD score DOUBLE(5,2);
# 修改一张表的字段类型
ALTER TABLE t_users MODIFY score INT;
# 修改一张表的字段名
ALTER TABLE t_users CHANGE name uname VARCHAR(50);
# 修改一张表的字符集
ALTER TABLE t_users CHARACTER SET GBK;

修改表添加一个身高字段：
int 整数   1 2 3 4... 不能写1.11
double 小数 （5，2)  带小数为总共有5个长度，2 表示小数点后能存2位，所以整数为长度是3.
 double(5,2)  最大能表示到 999.99
 alter table student add height double(5,2);
# 删除一张表中的字段
ALTER TABLE t_users DROP score;

表的删除：
drop table student ;

-- 创建一个跟这个表一模一样的表结构
create table stu2  like stu;
-- 创建一个表，表结构跟另一个表一模一样，并且数据也跟它一模一样，类似于copy 某个表
create table stu3 as select * from stu;

DDL语句：
数据库的新增修改删除查看。
表的新增修改删除查看
表字段的新增修改删除查看（已经涵盖在了表的修改操作中了）

假如编写sql语句回车代表换行，不代表执行，一个sql语句见 ; 表示写完了。

建表语句中，经常出现字段的类型：

比如 age 年龄数据类型数字表示 int

常见的数据类型：

数据类型	类型描述	示例
int	整型，整数类型
double	浮点型	double(5,2): 表示最多有5位，其中必须有两位数小数，即最大值是999.99
char	固定长度的字符串	char(5): 固定5位字符，即'aa'也占用5位字符
varchar	可变长度的字符串	varchar(5): 可以根据内容动态分配空间，'aa'只占用两位。括号里的5表示最大的位数。
text	字符串类型
blob	字节类型	很少用的，一般存在比如音乐，图片
date	日期类型	yyyy-MM-dd
time	时间类型	hh:mm:ss
timestamp	时间戳类型	yyyy-MM-dd hh:mm:ss，会自动赋值
datetime	时间类型	yyyy-MM-dd hh:mm:ss

3、DML

DML指的是对数据库中的数据进行增、删、改的操作。不要和DDL搞混了。

新增数据：

语法-- 必须遵循的法则

INSERT INTO 表名（列名1，列名2 ...）VALUES(列值1，列值2...);注意：列名与列值的类型、个数、顺序要一一对应。

mysql> use mydb03;
Database changed
mysql> show tables;
+------------------+
| Tables_in_mydb03 |
+------------------+
| stu              |
+------------------+
1 row in set (0.08 sec)

mysql> desc stu;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name   | varchar(255) | YES  |     | NULL    |       |
| age    | int(11)      | YES  |     | NULL    |       |
| height | double(5,2)  | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.12 sec)

mysql> insert into stu(name,age,height) values('zhangsan',22,190.9);
Query OK, 1 row affected (0.07 sec)

mysql> insert into stu(name,age,height) values('lisi',23,180);
Query OK, 1 row affected (0.11 sec)

mysql> select * from stu;  # 该语句是查询语句，表示查询stu中所有的数据
+----------+-----+--------+
| name     | age | height |
+----------+-----+--------+
| zhangsan |  22 | 190.90 |
| lisi     |  23 | 180.00 |
+----------+-----+--------+
2 rows in set (0.08 sec)

# insert into 表名  如果表名后面不添加任何的字段名，就是向所有字段插入值。
mysql> insert into stu values('wangwu',24,175);
Query OK, 1 row affected (0.06 sec)

mysql> select * from stu;
+----------+-----+--------+
| name     | age | height |
+----------+-----+--------+
| zhangsan |  22 | 190.90 |
| lisi     |  23 | 180.00 |
| wangwu   |  24 | 175.00 |
+----------+-----+--------+
3 rows in set (0.09 sec)
# insert into 表名  如果表名后面添加字段名，说明想向这个字段设置值，其他字段不管了。
mysql> insert into stu(name) values('zhaoliu');
Query OK, 1 row affected (0.09 sec)

mysql> select * from stu;
+----------+------+--------+
| name     | age  | height |
+----------+------+--------+
| zhangsan |   22 | 190.90 |
| lisi     |   23 | 180.00 |
| wangwu   |   24 | 175.00 |
| zhaoliu  | NULL | NULL   |
+----------+------+--------+

null 就是空的意思，没有任何值，不是 0 也不是 '' 

desc stu;
insert into stu(id,sname,age) values(1,'zhangsan',20);
select * from stu;
insert into stu values(2,'lisi',20);
insert into stu(id,sname) values(3,'王五');
insert into stu(id,sname) values (4,'sisi'),(5,'五仁'),(6,'六子');

创建一个新的表，跟stu表不一样

需求是：从stu中，查询数据导入到emp表中。

insert into emp(sname,sage,sheight)  select * from stu;

mysql> insert into emp(sname,sage,sheight)  select * from stu;
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----------+------+---------+--------+
| sname    | sage | sheight | gender |
+----------+------+---------+--------+
| zhangsan |   22 |     191 | NULL   |
| lisi     |   23 |     180 | NULL   |
| wangwu   |   24 |     175 | NULL   |
| zhaoliu  | NULL | NULL    | NULL   |
+----------+------+---------+--------+
4 rows in set (0.07 sec)

stu	emp
name varchar	sname varchar
age int	sage int
height double	sheight double

select 语句查询出来的数量以及字段类型和顺序都必须和 insert into 中表的字段数量、类型、顺序一样，跟表名字、表字段的名字没有半毛钱关系。

对比：
这个语句执行的时候必须有表
insert into 表名 select * from 表2

这个语句执行的时候可以没有表，sql语句帮创建表
create table 表名 as select * from 表2

删除数据：

# 删除数据
DELETE FROM 表名;
# 删除掉表中所有的数据
TRUNCATE TABLE 表名;

delete from stu where name='zhaoliu';
# where 后面可以添加条件，满足条件的删除掉

DELETE 和 TRUNCATE

1. delete删除表中的数据，表结构还在；删除的数据可以恢复。
2. truncate是直接将表DROP掉，然后再按照原来的结构重新创建一张表。数据不可恢复。
3. truncate删除效率比delete高。

where条件：

如何造数据：

字段	解释
name	姓名
groupid	小组
score	成绩

# 删除姓名叫"张三"的数据
delete from student where name='张三';

# 删除成绩不是100的数据
delete from student where score != 100;
delete from student where score <> 100;

# 删除不及格的数据
delete from student where score < 60;
# 删除成绩在[60, 80]范围的数据
delete from student where score >= 60 and score <= 80;

delete from student where score between 60 and 80;
# 删除小组是1，3，5的数据
delete from student where groupid = 1 or groupid = 3 or groupid =5;
delete from student where groupid in (1,3,5);
# 删除成绩是空的数据
delete from student where score is null;
# 删除成绩不及格的第三组的数据
delete from student where score < 60 and groupid = 3;

# 删除姓张的数据
delete from student where name  like '张%';

% 表示任意0个或者多个字符
# 删除名字中含有  伟 字的同学
delete from student where name  like '%伟%';

修改数据：

需求：只要这个同学姓张，就加10分
考虑语法：
UPDATE 表名 SET 列名1=列值1，列名2=列值2 。。。 WHERE 列名=值

根据语法编写sql语句：
score = 10 是将 score 的数据重置为 10
update student set score = 10 where name like '张%';
修改为：
update student set score = score + 10 where name like '张%';

4、DQL语句

DQL: Data Query Language，数据查询语言，指的是从数据库中查询自己需要的数据。是后续的课程中使用频率非常非常高的SQL语句。

语法

select ... from ...
select [distinct] ... from ... where ...group by ... having ...order by ... [limit ...]

1、数据准备

创建数据库：

切换数据库：双击

CREATE TABLE stu (
	sid	CHAR(6),
	sname		VARCHAR(50),
	age		INT,
	gender	VARCHAR(50)
);
INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

接着继续创建数据：

CREATE TABLE emp(
	empno	INT,
	ename	VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm	decimal(7,2),
	deptno	INT
);
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

创建部门数据：

CREATE TABLE dept(
	deptno		INT,
	dname		varchar(14),
	loc			varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

2、SQL练习

基础查询

查询所有列
select * from stu;
查询指定列
select sname,age from stu;

mysql> select * from stu;
+--------+----------+------+--------+
| sid    | sname    | age  | gender |
+--------+----------+------+--------+
| S_1001 | liuYi    |   35 | male   |
| S_1002 | chenEr   |   15 | female |
| S_1003 | zhangSan |   95 | male   |
| S_1004 | liSi     |   65 | female |
| S_1005 | wangWu   |   55 | male   |
| S_1006 | zhaoLiu  |   75 | female |
| S_1007 | sunQi    |   25 | male   |
| S_1008 | zhouBa   |   45 | female |
| S_1009 | wuJiu    |   85 | male   |
| S_1010 | zhengShi |    5 | female |
| S_1011 | xxx      | NULL | NULL   |
+--------+----------+------+--------+
11 rows in set (0.07 sec)

mysql> select sname,age from stu;
+----------+------+
| sname    | age  |
+----------+------+
| liuYi    |   35 |
| chenEr   |   15 |
| zhangSan |   95 |
| liSi     |   65 |
| wangWu   |   55 |
| zhaoLiu  |   75 |
| sunQi    |   25 |
| zhouBa   |   45 |
| wuJiu    |   85 |
| zhengShi |    5 |
| xxx      | NULL |
+----------+------+
11 rows in set (0.08 sec)

条件查询

查询性别为女，并且年龄小于50的记录
select * from stu where gender = 'female' and age <50;
+--------+----------+-----+--------+
| sid    | sname    | age | gender |
+--------+----------+-----+--------+
| S_1002 | chenEr   |  15 | female |
| S_1008 | zhouBa   |  45 | female |
| S_1010 | zhengShi |   5 | female |
+--------+----------+-----+--------+

查询学号为S_1001，或者姓名为liSi的记录
select * from stu where sid = 'S_1001' or sname = 'liSi';
+--------+-------+-----+--------+
| sid    | sname | age | gender |
+--------+-------+-----+--------+
| S_1001 | liuYi |  35 | male   |
| S_1004 | liSi  |  65 | female |
+--------+-------+-----+--------+

查询学号为S_1001，S_1002，S_1003的记录
select * from stu where sid in ('S_1001','S_1002','S_1003');
+--------+----------+-----+--------+
| sid    | sname    | age | gender |
+--------+----------+-----+--------+
| S_1001 | liuYi    |  35 | male   |
| S_1002 | chenEr   |  15 | female |
| S_1003 | zhangSan |  95 | male   |
+--------+----------+-----+--------+

按照模糊的条件进行查询，可以使用LIKE条件，或者REGEXP。

% : 代表0个或者多个字符
_ : 代表任意一个字符

# 查询所有的姓名以s开头的学生
select * from stu where sname like 's%';
# 查询所有的姓名以s开头的，且长度为5的学生
select * from stu where sname like 's____';

模糊查询可以使用两个关键字： like 和 regexp （略过）

字段控制

1）字段可以去重相同的就展示一次

需求：emp中工资都有哪些档位   1000 3000 5000 10000

mysql> select distinct  sal from emp;
+---------+
| sal     |
+---------+
| 800.00  |
| 1600.00 |
| 1250.00 |
| 2975.00 |
| 2850.00 |
| 2450.00 |
| 3000.00 |
| 5000.00 |
| 1500.00 |
| 1100.00 |
| 950.00  |
| 1300.00 |
+---------+

2）列之间的计算

需求：计算每一个员工的薪水 （工资 + 绩效）
select ename,sal + comm from emp;

mysql> select ename,sal + comm from emp;
+--------+------------+
| ename  | sal + comm |
+--------+------------+
| SMITH  | NULL       |
| ALLEN  | 1900.00    |
| WARD   | 1750.00    |
| JONES  | NULL       |
| MARTIN | 2650.00    |
| BLAKE  | NULL       |
| CLARK  | NULL       |
| SCOTT  | NULL       |
| KING   | NULL       |
| TURNER | 1500.00    |
| ADAMS  | NULL       |
| JAMES  | NULL       |
| FORD   | NULL       |
| MILLER | NULL       |
+--------+------------+

mysql> select 100+ null;
+-----------+
| 100+ null |
+-----------+
| NULL      |
+-----------+

如果一个人的奖金为null，应该当作 0 来计算。
mysql> select ifnull(null,100);
+------------------+
| ifnull(null,100) |
+------------------+
|              100 |
+------------------+
1 row in set (0.10 sec)

select ename,sal + ifnull(comm,0) from emp;
mysql> select ename,sal + ifnull(comm,0) from emp;
+--------+----------------------+
| ename  | sal + ifnull(comm,0) |
+--------+----------------------+
| SMITH  | 800.00               |
| ALLEN  | 1900.00              |
| WARD   | 1750.00              |
| JONES  | 2975.00              |
| MARTIN | 2650.00              |
| BLAKE  | 2850.00              |
| CLARK  | 2450.00              |
| SCOTT  | 3000.00              |
| KING   | 5000.00              |
| TURNER | 1500.00              |
| ADAMS  | 1100.00              |
| JAMES  | 950.00               |
| FORD   | 3000.00              |
| MILLER | 1300.00              |
+--------+----------------------+

ifnull 是一个函数，就是带括号的东西就是函数。函数就是工具，会用即可。
mysql> select database();
+------------+
| database() |
+------------+
| sql        |
+------------+
不同函数有不同函数的用法，见一个记住一个，多多益善！

3）给列名添加别名：

mysql> select ename  姓名, sal + ifnull(comm,0)  薪水  from emp;
+--------+---------+
| 姓名   | 薪水    |
+--------+---------+
| SMITH  | 800.00  |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  | 2975.00 |
| MARTIN | 2650.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  | 950.00  |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.06 sec)

mysql> select ename  '姓名', sal + ifnull(comm,0)  '薪水'  from emp;
+--------+---------+
| 姓名   | 薪水    |
+--------+---------+
| SMITH  | 800.00  |
| ALLEN  | 1900.00 |
| WARD   | 1750.00 |
| JONES  | 2975.00 |
| MARTIN | 2650.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  | 950.00  |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
14 rows in set (0.09 sec)

4) 列可以排序

查询所有学生记录，按年龄升序排序
select * from stu order by age asc;
select * from stu order by age ;
两个查询结果一样，说明order by 默认按照升序排序，asc 可写可不写。

查询所有学生记录，按年龄降序排序
select * from stu order by age desc;

desc :   desc 表名  --> 查看表结构
         order by 字段名 desc  --> 按照该字段降序排序

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
select * from emp order by sal desc, empno asc;

order by 字段1 ,字段2
字段二有可能用不上。
order by 字段1 字段1是排序的主要字段，只有该字段相同的时候，才会用到第二个字段，假如没有第二个字段。

复杂一点的：
select ename as 姓名, sal+ifnull(comm,0) as 总薪水 from emp order by sal+ifnull(comm,0) asc;
-- order by 之后可以使用前面的别名
select ename as 姓名, sal+ifnull(comm,0) as 总薪水 from emp order by 总薪水 asc;


-- 查看总薪水是1300元的员工信息
select * from emp where sal+ifnull(comm,0) = 1300;
-- where 后面不能直接使用别名, 而order by 可以
-- 除了where 不能直接使用别名，其他都可以
select *,sal+ifnull(comm,0) as xinshui from emp where xinshui = 1300;

查看全文

http://www.kler.cn/a/421084.html