資料庫基礎語法
1. 登錄和登出資料庫
1. 連接資料庫
mysql -uroot -pmysql
2. 退出資料庫
quit, exit, ctrl+D
2. 資料庫操作的sql陳述句
1. 顯示時間
select now();
2. 查看所有資料庫
show databases;
3. 創建資料庫(例如: python1)
create datsbase python1 charse=utf8;
4. 使用資料庫
use python1
5. 查看當前使用的資料庫
select database();
6. 洗掉資料庫
drop database python1;
3. 表結構操作的sql陳述句
1. 查看資料庫中的所有表
show tables;
2. 創建表(例如: students表(id(無符號, 主鍵, 非空),姓名(字串, 非空), 年齡(短整型, 非空, 默認為0)))
create table students(id int unsigned primary key auto_increment not null, name varchar(30) notnull, age tinyint not null default 0);
3. 查看表結構
desc students;
4. 修改表--添加欄位
alter table students add birthday datetime default 0;
5. 修改表--修改欄位, 不重命名
alter table students modify birthday date not null;
6. 修改表--修改欄位, 重命名
alter table students change birthday birth date not null;
7. 修改表--洗掉欄位
alter table students drop birthday;
8. 查看表創建陳述句
select create table students;
9. 查看庫創建陳述句
select create database python1;
10. 洗掉表
drop table students;
11. 洗掉庫
drop database pytohn1;
4. 表資料操作的sql陳述句
1. 增加--全列插入(向students表中增加列)
insert into students values(0, '張三', 18);
2. 增加--部分插入(向students表中增加name)
insert into students(name) values('張三')
3. 修改--全列修改
update students set name = '李四';
4. 修改--部分修改
update students set name = '李四' where id = 1;
5. 洗掉--物理洗掉
delect from students where id = 2;
6. 邏輯洗掉
alter table students add id_delect bit default 0;
update studets det students.id_delect = 1 where id = 2;
select * from students where id_delect = 1;
7. 查詢--查詢所有列
select * from students;
8. 查詢指定列
select id, birth from students;
9. 欄位順序(按年齡從大到小排序)
select * from students order by age desc;
10. 使用關鍵字as起別名
(給欄位起別名) select id as i, birth as b from students;
(給表起別名) select s.id, s.birth from student as s;
11. 消除重復行
select distinct id from students;
12. where 條件查詢(比較, 邏輯, 模糊, 范圍, 空)
(比較) select * from students where age > 7;
(邏輯) select * from students where age > 5 and age < 7;
(模糊) select * from students where name like '黃%_' ----(%替換任意個, _替換一個)
(范圍) select * from students where age in (1, 2, 3, 5) ----(不連續范圍)
(空) select * from students where age is not null;
13. between... and...連續范圍內
select * from students where age between 2 and 7;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/228954.html
標籤：MySQL