SQL基础语法——符超

建库

create database if not exists databasename default charset utf8;

建表

create table if not exists ‘student’（

student’int unsigned auto——increment，

‘student’varchar（100）not null，

‘student’varchar（40）not null，

）

插入数据，字段

insert into student(stuname) values("张三");

insert into student(stuname) values("李四");

insert into student(stuname) values("王五");

insert into student(stuname) values("老六");

alter table student add stusex varchar(10) default '男';

表关联

左关联:

SELECT   别名1.*，别名2.*  FROM  表名1 别名1 LEFT  JOIN 表名2 别名2 ON 别名1.sch_id=别名2.sch_id;

SELECT a.*,b.* FROM school a LEFT JOIN student b ON a.sch_id=b.sch_id;

右关联:

SELECT 别名1.*，别名2.* FROM 表名1 别名1 RIGHT JOIN 表名2 别名2 ON 别名1.sch_id=别名2.sch_id;

SELECT a.*,b.* FROM school a RIGHT JOIN student b ON a.sch_id=b.sch_id;

内连接:

select  别名1.*，别名2.*  from 表名1 别名1 inner join 表名2 别名2 on 别名1.sch_id=别名2.sch_id;

SELECT a.*,b.* FROM school a INNER JOIN student b ON a.sch_id=b.sch_id;

联合查询:

SELECT * FROM 表名1;UNION ALL SELECT * FROM 表名2;

SELECT * FROM root UNIOA ALL SELECT * FROM student

#子查询

语句:

SELECT * FROM 表名1 WHERE 字段2>=(SELECT  字段2 FROM WHERE 字段1=条件值);

#查询大于或等于张三人的年龄
SELECT * FROM user WHERE age>= (
        SELECT age FROM WHERE name
);