MySQL的语句

时间：2021-08-25 12:37:08 阅读：10 评论：0 收藏：0 [点我收藏+]

01.创建表格

创建 mytable 表格， id ( int 类型、主键、自动增长) 、username ( varchar(30) 类型）、password ( varchar(30) 类型)

create table mytable(
    id int auto_increment,
    username varchar(30),
    password varchar(30),
    primary key(id));

desc mytable;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(30) | YES  |     | NULL    |                |
| password | varchar(30) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

关键字：

create table mytable（创建表格叫 mytable）

create：创建
int：数字
varchar(30)：字符串
auto_increment：自动增加
primary key(id)：设置主键

02.给表中插入内容

向 username 和 password 中插入值

insert into mytable(username,password) values(‘siki‘,‘siki‘);
insert into mytable(username,password)values(‘siki‘,‘siki‘);
insert into mytable(username,password) values(‘siki2‘,‘siki2‘);

select * from mytable;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | siki     | siki     |
|  2 | siki     | siki     |
|  3 | siki2    | siki2    |
+----+----------+----------+

关键字：

insert into mytable(username,password) values(‘siki‘,‘siki‘);

插入到 mytable 表中(username 和 password 列中) values（值为‘siki‘,‘siki‘）

select * from mytable;

从 mytable 表中选择所有数据

insert：插入
into：到...里
values：插入的值
select：选择
from：从
*：代表所有数据

03.修改表格内容

无条件修改

修改所有行数据

update mytable set username=‘123‘;

select * from mytable;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 123      | siki     |
|  2 | 123      | siki     |
|  3 | 123      | siki2    |
+----+----------+----------+

有条件修改

修改 id 为 1 的 username 行数据

update mytable set username=‘456‘ where id=1;

select * from mytable;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 456      | siki     |
|  2 | 123      | siki     |
|  3 | 123      | siki2    |
+----+----------+----------+

有条件修改（多个条件）

update mytable set username=‘456‘,password=‘12356‘ where id=1;

mysql> select * from mytable;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | 456      | 12356    |
|  2 | 123      | siki     |
|  3 | 123      | siki2    |
+----+----------+----------+

关键字：

update mytable set username=‘456‘,password=‘12356‘ where id=1;

更新 mytable 表格放置在 username，password 列中，在 id 为 1 的位置

update：更新
set：放置
where：位置

04.删除表格内容

无条件删除

删除所有行的数据

delete from mytable;
Query OK, 3 rows affected (0.01 sec)

select * from mytable;
Empty set (0.00 sec)

有条件删除

删除 id 为 7 的行数据

select * from mytable;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  4 | siki2    | siki2    |
|  5 | siki2    | siki2    |
|  6 | siki2    | siki2    |
|  7 | siki2    | siki2    |
|  8 | siki2    | siki2    |
|  9 | siki2    | siki2    |
| 10 | siki2    | siki2    |
| 11 | siki2    | siki2    |
+----+----------+----------+

delete from mytable where id=7;

select * from mytable;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  4 | siki2    | siki2    |
|  5 | siki2    | siki2    |
|  6 | siki2    | siki2    |
|  8 | siki2    | siki2    |
|  9 | siki2    | siki2    |
| 10 | siki2    | siki2    |
| 11 | siki2    | siki2    |
+----+----------+----------+

关键字：

delete from mytable where id=7;

从 mytable 表中删除 id 为7 的行数据。

delete：删除

05.查询表格内容

查询所有内容

select * from mytable;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  4 | siki2    | siki2    |
|  5 | siki2    | siki2    |
|  6 | siki2    | siki2    |
|  8 | siki2    | siki2    |
|  9 | siki2    | siki2    |
| 10 | siki2    | siki2    |
| 11 | siki2    | siki2    |
+----+----------+----------+

查询前 n 条数据

查询前四条数据

select * from mytable limit 4;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  4 | siki2    | siki2    |
|  5 | siki2    | siki2    |
|  6 | siki2    | siki2    |
|  8 | siki2    | siki2    |
+----+----------+----------+

查询除前 n 条数据的后 m 条数据

查询前两条数据后的三条数据

select * from mytable limit 2,3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  6 | siki2    | siki2    |
|  8 | siki2    | siki2    |
|  9 | siki2    | siki2    |
+----+----------+----------+

按照列查询列数据

查询 username 的数据

select username from mytable;
+----------+
| username |
+----------+
| siki2    |
| siki2    |
| siki2    |
| siki2    |
| siki2    |
| siki2    |
| siki2    |
+----------+

按照列有条件查询数据

查询 id 大于8 的 username 和 password 列的数据

select username,password from mytable where id>8;
+----------+----------+
| username | password |
+----------+----------+
| siki2    | siki2    |
| siki2    | siki2    |
| siki2    | siki2    |
+----------+----------+

按照某列顺序排列

select * from mytable order by id;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  4 | siki2    | siki2    |
|  5 | siki2    | siki2    |
|  6 | siki2    | siki2    |
|  8 | siki2    | siki2    |
|  9 | siki2    | siki2    |
| 10 | siki2    | siki2    |
| 11 | siki2    | siki2    |
+----+----------+----------+

按照某列排序（倒序）

select * from mytable order by id desc;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 11 | siki2    | siki2    |
| 10 | siki2    | siki2    |
|  9 | siki2    | siki2    |
|  8 | siki2    | siki2    |
|  6 | siki2    | siki2    |
|  5 | siki2    | siki2    |
|  4 | siki2    | siki2    |
+----+----------+----------+

按照第一列排序，排序相同的情况下再按照第二列排序
```
select * from actor order by first_name,last_name;
```
按照第一列排序（倒序），排序相同的情况下再按照第二列排序（顺序）
```
select * from actor order by first_name desc,last_name;
```

按照某列排序查询第一条

select * from mytable order by id desc limit 1;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 11 | siki2    | siki2    |
+----+----------+----------+

修改查询表的表头

查询表的表头 last_name 修改为 LastName ，first_name 修改为 FirstName（加不加 as 都可以）

select last_name as LastName,first_name FirstName from actor;

+--------------+-------------+
| LastName     | FirstName   |
+--------------+-------------+
| GUINESS      | PENELOPE    |
| WAHLBERG     | NICK        |
| CHASE        | ED          |
| DAVIS        | JENNIFER    |
| LOLLOBRIGIDA | JOHNNY      |
| NICHOLSON    | BETTE       |
| MOSTEL       | GRACE       |
+--------------+-------------+

关键字：

limit：限制
order：规则
desc：降序

06.查询静态值

select ‘some string‘;
+-------------+
| some string |
+-------------+
| some string |
+-------------+

加法

select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+

现在年月日时间

select now();
+---------------------+
| now()               |
+---------------------+
| 2021-08-19 12:27:41 |
+---------------------+

现在年月日

select curdate();
+------------+
| curdate()  |
+------------+
| 2021-08-19 |
+------------+

现在的时间

select curtime();
+-----------+
| curtime() |
+-----------+
| 12:28:00  |
+-----------+

PI 值

select pi();
+----------+
| pi()     |
+----------+
| 3.141593 |
+----------+

求余

select mod(45,7);
+-----------+
| mod(45,7) |
+-----------+
|         3 |
+-----------+

开根号

select sqrt(25);
+----------+
| sqrt(25) |
+----------+
|        5 |
+----------+

四舍五入

select round(4.5);
+------------+
| round(4.5) |
+------------+
|          5 |
+------------+

四舍五入保留小数

select round(4.65665,2);
+------------------+
| round(4.65665,2) |
+------------------+
|             4.66 |
+------------------+

只舍去

select floor(4.9)
+------------+
| floor(4.9) |
+------------+
|          4 |
+------------+

只入

select ceiling(4.1)
+--------------+
| ceiling(4.1) |
+--------------+
|            5 |
+--------------+

综合

select customer_id,round(amount) from payment limit 20;
+-------------+---------------+
| customer_id | round(amount) |
+-------------+---------------+
|           1 |             3 |
|           1 |             1 |
|           1 |             6 |
|           1 |             1 |
|           1 |            10 |
|           1 |             5 |
|           1 |             5 |
|           1 |             1 |
|           1 |             4 |
|           1 |             6 |
|           1 |             6 |
|           1 |             5 |
|           1 |             5 |
|           1 |             8 |
|           1 |             3 |
|           1 |             5 |
|           1 |             5 |
|           1 |             1 |
|           1 |             1 |
|           1 |             3 |
+-------------+---------------+

07.字符串操作

Concat（将两个列的字符串连接在一起）

select concat(first_name, last_name) Name from actor;
+---------------------+
| Name                |
+---------------------+
| PENELOPEGUINESS     |
| NICKWAHLBERG        |
| EDCHASE             |
| JENNIFERDAVIS       |
| JOHNNYLOLLOBRIGIDA  |
| BETTENICHOLSON      |
| GRACEMOSTEL         |
| MATTHEWJOHANSSON    |
| JOESWANK            |
| CHRISTIANGABLE      |
+---------------------+

取得字符串前几个数字

要是有 from 就是表示从哪个表中获得数据

select left(last_name,3) from actor;
+-------------------+
| left(last_name,3) |
+-------------------+
| AKR               |
| AKR               |
| AKR               |
| ALL               |
+---------------------+

不加 from 则代表字符串直接获得

select left(‘ last_name‘,3);
+----------------------+
| left(‘ last_name‘,3) |
+----------------------+
|  la                  |
+----------------------+

翻转字符串

select reverse(first_name) from actor limit 20;
+---------------------+
| reverse(first_name) |
+---------------------+
| EPOLENEP            |
| KCIN                |
| DE                  |
| REFINNEJ            |
| DERF                |
| NELEH               |
| NAD                 |
| BOB                 |
| ELLICUL             |
+---------------------+

获得字符串长度

select length(first_name) from actor limit 20;
+--------------------+
| length(first_name) |
+--------------------+
|                  8 |
|                  4 |
|                  2 |
|                  8 |
|                  4 |
|                  5 |
|                  3 |
|                  3 |
|                  7 |
+--------------------+
20 rows in set (0.00 sec)

替换字符串

将字符串中的 da 换成 11；

select replace(‘sadasdasdsadsa‘,‘da‘,‘11‘);
+-------------------------------------+
| replace(‘sadasdasdsadsa‘,‘da‘,‘11‘) |
+-------------------------------------+
| sa11s11sdsadsa                      |
+-------------------------------------+

替换列中的字符串

select replace(first_name,‘D‘,‘11‘) from actor limit 20;
+------------------------------+
| replace(first_name,‘D‘,‘11‘) |
+------------------------------+
| PENELOPE                     |
| NICK                         |
| E11                          |
| FRE11                        |
| HELEN                        |
| 11AN                         |
| LUCILLE                      |
+------------------------------+

格式化字符串（24课）（或者 MySQL 里查看）

获得季度

select quarter(‘2020-07-05‘);
+-----------------------+
| quarter(‘2020-07-05‘) |
+-----------------------+
|                     3 |
+-----------------------+

08.去除重复字段

select distinct first_name from actor order by first_name;

只需要在需要查询的字符串或列前加上 distinct 关键字。

09.Where条件判断

	1.数字 > < = >= <= <>

? 2.字符串 = ‘‘ > < = >= <= <> !=
?

	逻辑操作
	is 仅用is null或is not null
	and or not
	and 优先级> or 
	
	范围判断
	in (not in)
	between (not between)     示例：select * from category where category_id between 1 and 9;
	like (not like) % _（这个是模糊查询）
	    示例1：select * from category where name like ‘A%‘;（A后面有0或多个字符）（A是开头）
	    示例1：select * from category where name like ‘%A%‘（只要有A字符就行）
	    示例1：select * from category where name like ‘A_‘;（这个表示必须是A后面只有一个字符）
 	关于NULL的条件
	is NULL
	is not NULL

多次查询，用 and 来进行并列，用 or 来表示满足一个条件。
有 or 或 and，先进行 and ，再处理 or
在条件前面添加 not 则表示不满足这个条件的条件。
where id in（1，2，3）表示 id 满足 1 或 2 或 3。

MySQL的语句

原文：https://www.cnblogs.com/zheyw/p/15183461.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)