SQL基础语法

目录

 

1.创建库、表，使用库

2.增、删、改、查

3.表关联

4.子查询

---

 

1.创建库、表，使用库

CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8;

USE 数据库名；

CREATE TABLE temp

（

        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(50) NOT NULL,
        birthday DATE NOT NULL

）；

2.增、删、改、查

增：insert into cs_user(username,password,gender,age)values("一","123456","男",17),("二","123456","男",19);

删：delete from cs_user where username="一";删除某一条数据 drop table cs_user;删除表

改：update cs_user set password="111" where username="一";

查：select * from cs_user;查询所有数据 select * from cs_user where username="一";查询某条数据

3.表关联

#内连接,...inner join..on
SELECT * FROM websites w INNER JOIN access_log a ON w.id=a.site_id;
SELECT * FROM websites w,access_log a WHERE w.id=a.site_id;
#左连接 left join...on
SELECT * FROM websites w LEFT JOIN access_log a ON w.id=a.site_id;
#右连接 right join...on
SELECT * FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id;
#全连接=左连接 union 右连接
SELECT * FROM websites w LEFT JOIN access_log a ON w.id=a.site_id
UNION
SELECT * FROM websites w RIGHT JOIN access_log a ON w.id=a.site_id;

#union（例）
SELECT id FROM websites
UNION ALL
SELECT aid FROM access_log;

#三表关联查询，第一个表关联中间表的结果，然后关联第二个表
SELECT st.*,sc.*,su.* FROM stu st 
INNER JOIN score sc ON st.no=sc.no
LEFT JOIN sub su ON sc.subno=su.subno
WHERE st.no > 1；

4.子查询

#查询所有与夏老师同一年出生的教师信息

select * from teacherinfo where year(birthday) in (select  year(birthday) from teacherinfo where name='夏老师');