MYSQL資料庫

Yolololololo發表於2024-05-31
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], ...;

相關文章