MySQL的语句
时间：2021-08-25 12:37:08
收藏：0
阅读：8
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。
原文：https://www.cnblogs.com/zheyw/p/15183461.html
评论（0）