sql语句学习记录

//进入docker 容器  0ed6d6f28db1 是mysql容器id
docker exec -it 0ed6d6f28db1 /bin/bash

//进入mysql
mysql -u root -p

//显示数据库
show databases;

//选择数据库  sys 是数据库名称
use sys

//查看表结构  users 是表名
desc users;  

1、select 

//select  显示users表的username列
select username from users;

//显示所有列
select * from users;

//在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值  类似去重
select distinct username from users;

 2、where

//Where +条件（筛选行） 比较运算符 = > < >= ,<=, !=,<> 表示（不等于）
select * from users where id=1;

//逻辑运算  and与  or或  not非
select * from users where id=1 or id=3；

//特殊条件  空值判断： is null
select * from users where createtime is null;


//特殊条件 between and (在 之间的值)
select * from users where id between 2 and 3;

//In 查询 users 表 id 列中等于 2,3的值。
select * from users where id in (2,3);

//like 查询 users 表中 username 列中有 ti 的值，ti 为要查询内容中的模糊信息。
select * from users where username like 'ti%';

//混合用法
select * from users where id>2 and username like '%12%';

3、order by

//order by  选择username 字段进行排序 默认是ASC,ASC：表示按升序排序
mysql> select * from users order by username ;
+----+----------+----------+------------+
| id | username | password | createtime |
+----+----------+----------+------------+
|  2 | 2        | 123456   | 1695633707 |
|  1 | tizi1231 | 123456   | 1695612607 |
|  3 | tizi1233 | 123456   | 1695634039 |
|  4 | tizi1234 | 123456   | 1695634055 |
|  5 | tizi1235 |          |       NULL |
|  6 | tizi6    | NULL     |       NULL |
+----+----------+----------+------------+


mysql> select * from users order by username asc;
+----+----------+----------+------------+
| id | username | password | createtime |
+----+----------+----------+------------+
|  2 | 2        | 123456   | 1695633707 |
|  1 | tizi1231 | 123456   | 1695612607 |
|  3 | tizi1233 | 123456   | 1695634039 |
|  4 | tizi1234 | 123456   | 1695634055 |
|  5 | tizi1235 |          |       NULL |
|  6 | tizi6    | NULL     |       NULL |
+----+----------+----------+------------+

mysql> select * from users order by username desc;
+----+----------+----------+------------+
| id | username | password | createtime |
+----+----------+----------+------------+
|  6 | tizi6    | NULL     |       NULL |
|  5 | tizi1235 |          |       NULL |
|  4 | tizi1234 | 123456   | 1695634055 |
|  3 | tizi1233 | 123456   | 1695634039 |
|  1 | tizi1231 | 123456   | 1695612607 |
|  2 | 2        | 123456   | 1695633707 |
+----+----------+----------+------------+


//多个排序如下username，password  优先前面的username排序，当username是一样，再次对password排序
mysql> select * from users order by username,password;
+----+----------+----------+------------+
| id | username | password | createtime |
+----+----------+----------+------------+
|  2 | 2        | 123456   | 1695633707 |
|  1 | tizi1231 | 123456   | 1695612607 |
|  4 | tizi1233 | 123455   | 1695634055 |
|  3 | tizi1233 | 123456   | 1695634039 |
|  5 | tizi1235 |          |       NULL |
|  6 | tizi6    | NULL     |       NULL |
+----+----------+----------+------------+

4、insert info 

//INSERT INTO 语句用于向表中插入新记录。
//直接写入值，必须把所有列的值都列出
insert into users values (7,'ff',123,1695634035);

//指定列  和 值 没有指定的  自动填null   主键自增
insert into users (username,password) values ('aa',123);

+----+----------+----------+------------+
| id | username | password | createtime |
+----+----------+----------+------------+
|  1 | tizi1231 | 123456   | 1695612607 |
|  2 | 2        | 123456   | 1695633707 |
|  3 | tizi1233 | 123456   | 1695634039 |
|  4 | tizi1233 | 123455   | 1695634055 |
|  5 | tizi1235 |          |       NULL |
|  6 | tizi6    | NULL     |       NULL |
|  7 | ff       | 123      | 1695634035 |
|  9 | ff       | 123      | 1695634035 |
| 10 | aa       | 123      |       NULL |
+----+----------+----------+------------+
9 rows in set (0.00 sec)

5、update

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

table_name：要修改的表名称。
column1, column2, ...：要修改的字段名称，可以为多个字段。
value1, value2, ...：要修改的值，可以为多个值。
condition：修改条件，用于指定哪些数据要修改。

//一定要加后面的where  如果不加，users表的全部username='bb'
mysql> updata users set username='bb' where id=7;

6、delete

//语法格式
DELETE FROM table_name
WHERE condition;

delete from users where id=9;