mysql密码短语_常用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、建表约束--主键

View Code

12、建表约束--自增

create table user3(

id int primary key auto_increment,

name varchar(20)

);

12、建表约束--唯一：约束修饰的字段的值不可以重复

View Code

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、多层嵌套查询

View Code

4、union与not in

View Code

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表示所有

select * from score where cno='34'

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

order by degree desc;

复制代码