MYSQL数据库

创建数据库

create database myDB;

删除数据库

drop database firstDB;

选择数据库

use myDB;

创建表

mysql> create table PEOPLE (
    -> ID int AUTO_INCREMENT PRIMARY KEY,
    -> NAME varchar(20) not null,
    -> PASSWORD tinyint(50) not null,
    -> AGE int not null,
    -> BIRTHDAY datetime);                                                     
Query OK, 0 rows affected (0.01 sec)

alter 修改表（列）字段

alter table PEOPLE MODIFY star int(2);

删除表

drop table users;

插入数据

insert into users (column1,column2,column3,..) values (value1, value2, value3, ...);

查询数据

//查询所有数据
select * from users;

//特定的列
select username,email from users;

//where条件查询
select * from users where is_active = true;

//升序
select * from users order by birthdate;

//降序
select * from users order by birthdate desc;

//limit限制返回的行数
select * from users limit 10;

where子句

//运算符and和通配符
select * from users where username like 'j%' and is_active = true;

//or
select * from users where is_active =true or birthdata < ‘2005-1-1’;

//IN
select * from users where birthdate in ('2005-1-1','2000-9-9','2000-8-8');

更新数据

update table_name set column1 = value1, column2 = value2,... where condition;

eg:
UPDATE orders
SET status = 'Shipped', ship_date = '2023-03-01'
WHERE order_id = 1001;

删除数据

DELETE FROM table_name WHERE condition;

like 模糊匹配

select column1, column2, ... from table_name where column_name LIKE pattern;

union 连接两个以上的select语句的结果，将其组合到一个结果集合，出去重复的行

SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2
[ORDER BY column1, column2, ...];

asc升序 desc降序

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;