常用MYSQL短語

主要總結mysql一些常用知識點

[常用指令]

1、檢視資料庫

show database;

2、建立資料庫

create database database_name;

3、切換資料庫

use database_name;

4、檢視某資料庫中所有的資料表

show table;

5、建立資料表

View Code

6、檢視資料表結構

describe table_name; --縮寫: desc

7、檢視資料表中的記錄

select * from table_name;

-- 去重複

select distinct name from table_name

8、往資料表中添加資料記錄

INSERT INTO table_name

VALUES('puffball','Diane','hanst','f','1999-03-23',NULL);

-- 指定屬性

insert into user3 (name) value('asfjl');

9、删除資料

delete from table_name where name='puffball';

10、修改資料

update table_name set name='wang' where owner='haha'

11、建表限制--主鍵

12、建表限制--自增

create table user3(

id int primary key auto_increment,
name varchar(20)           

);

12、建表限制--唯一：限制修飾的字段的值不可以重複

13、非空限制：修飾的字段不能為NULL

複制代碼

create table user6(

id int,
name varchar(20) not null            

-－ 反null? 異常

insert into user6 (name) value('jfsl');

14、預設限制

create table user7(

id int,
name varchar(20),
age int default 10           

insert into user7 (id,name) value(1,'slfj');

insert into user7 (id,name,age) values(1,'slsfj',5);

15、外鍵限制

create table classes(

id int primary key,
name varchar(20)           

create table students(

id int primary key,
class_id int,
foreign key(class_id) references classes(id)           

[查詢]

1、多表查詢

-- 兩表查詢

select sname,cno, degree from student,score

where student.sno = score.sno;

-- 三表查詢

select sname, cname,degree from student,course,course,score

where student.sno = score.sno

and course.cno = score.cno;

2、分組查詢

-- 子查詢加分組求評均

select cno, avg(degree) from score

where sno in (select sno from student where class='1233')

group by cno;

-- year函數與帶in關鍵字的子查詢

select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,117));

3、多層嵌套查詢

4、union與not in

5、any與all

-- any表示至少一個

select * from score where cno='34'

and degree>any(select degree from score where cno='334')

order by degree desc;

-- all表示所有

and degree>all(select degree from score where cno='334')

原文位址

https://www.cnblogs.com/lisen10/p/sql.html