資料庫操作回顧

sql database基本指令

#查看資料庫版本
select version();
#查看當前時間
select now()
#創建表
create database xxx charset utf8;
#使用表
use xxx
#刪除表
drop database xxx;
#創建表語句
show create database xxx;
#顯示所有表
show databases

sql table基本指令

#創建表
create table yyy(
id int primary key not null auto_increment,
name varchar(30)
);
#添加欄
alter table yyy add name varchar(30);
#修改欄位類型
alter table yyy modify name varchar(100);
#修改名
alter table yyy change name newname varchar(20);

#刪除欄位
alter table yyy drop name;

#刪除庫
drop database xxx
#刪除表
drop table xxx

sql增刪改

#增加
insert into table_name values (xxx),(xxx);
#指定欄插入
insert into table_name (colum1,colum2) values (xxx),(xxx)
#修改
update table_name set xxx=xxx where xxx
#刪除
delete from table_name where xxx

sql查詢

#全部查詢 
SELECT * from posts;

#取相應欄位
SELECT title as '標題',create_time as '創建時間' FROM posts; #別名

SELECT posts.title,posts.author_id from posts;

#消除重覆行
SELECT DISTINCT gender from front_user#只顯示一列

#條件查詢 >  <  <= >=  =  !=(或者<>) 不等於，等於，大於小於

#NOT
#查找性別不為男的用戶
SELECT username,gender FROM front_user where not(gender="男") 

#AND
#查找性別不為男並且用戶名不是'donghao'的用戶
SELECT username,gender FROM front_user where not(gender="男" AND username="donghao")

#OR
SELECT name from album where id=1 or id=3

#模糊查詢
#like  %替換一個或者多個  _替換一個
SELECT title from posts where content like "<p>用戶%"

SELECT title from posts where content like "%陽光，而是微笑%"

#__
#查詢至少有兩個字以上的標題
SELECT title from posts where title like "__%"

#RLIKE正則
SELECT title from posts where title RLIKE "^祝.*~$"

#in
SELECT name from album where id IN (1,2,3,4,5,6,7,8)

#BETWEEN AND
SELECT name from album where id BETWEEN 1 and 100
#NOT BETWEEN AND
SELECT name from album where id not BETWEEN 1 and 100

#is null
SELECT * from front_user where realname is null;
#is not null
SELECT * from front_user where realname is not null;

排序

#從大到小
SELECT * from posts ORDER BY read_count desc
#(預設，可省略) 從小到大
SELECT * from posts ORDER BY read_count asc 

聚合函數

count  avg  max  min
SELECT COUNT(*) from posts
SELECT max(read_count) from posts
SELECT avg(read_count) from posts # or select sum(read_count)/count(read_count) from posts;
SELECT min(read_count) from posts
SELECT round(avg(read_count),2) from posts
#四捨五入 round(count,num)#保留2位小數

分組

SELECT gender,count(gender) from front_user GROUP BY gender;

#分組裡麵包含數據
SELECT gender,count(gender),GROUP_CONCAT(username) from front_user GROUP BY gender;

輸出：

#having 過濾數據（對結果查詢出的結果過濾）
SELECT gender,GROUP_CONCAT(username),avg(charactors) FROM front_user GROUP BY gender HAVING(gender!='男')

分頁

select * from posts LIMIT 10
select * from posts LIMIT 1 10 #1：起始下標  10 下標

連接查詢

# inner join
#只顯示front_user中的nickname和posts表中的title
SELECT front_user.nickname,posts.title from front_user INNER JOIN posts ON front_user.id=posts.author_id where nickname='小姐姐'
別名：
SELECT f.nickname,p.title from front_user as f INNER JOIN posts as p ON f.id=p.author_id where nickname='小姐姐'

#left join
left join左邊的表為基準，找不到置id 為null

#rightjoin
與left join相反

自關聯

SELECT *FROM `comment` as c INNER JOIN `comment` as d on c.id=d.origin_comment_id where c.post_id="PuanQjvyHDzULWTj4cEick"

子查詢

#查詢寫的字數最多的人信息
SELECT * from front_user where charactors=(SELECT MAX(charactors) from front_user)