MySQL的语句

2022-04-08 00:26:50

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的语句

上一篇：MySQL Explain详解


下一篇：微信服务号认证流程