MySQL基础语法_肖长青

建库建表语法，字段数据类型
建库
CREATE DATABASE IF NOT EXI username DEFAULT CHARSET utf8;

建表
CREATE TABLE student(字段);

CREATE DATABASE IF NOT EXISTS  username DEFAULT CHARSET utf8;
USE zuoye;
CREATE TABLE student(
	st_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	st_name VARCHAR(20) NOT NULL,
	st_age DATE NOT NULL,
	st_xuehao VARCHAR(50),
	st_banji INT
);

增删改查语法
增
insert into student(字段名)values(要增加的数据)

INSERT INTO student(st_name,st_age,st_xueli,st_banji)VALUES
("张一","2000-1-1","专科","1001"),
("张二","2000-1-1","专科","1001"),
("张三","2000-1-1","专科","1001"),

删
delete from 表名 where 要删除的数据信息 ；

 DELETE FROM student WHERE st_name="张三";

改
update 表名 set 要修改的字段数据 where 条件

update student set st_xueli="本科" where st_name="张三";

查
查询表数据：select * from student;

CREATE DATABASE IF NOT EXISTS web DEFAULT CHARSET utf8;
USE web;
CREATE TABLE IF NOT EXISTS websites(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(50) NOT NULL,
	url VARCHAR(50) NOT NULL,
	alexa INT NOT NULL COMMENT "网站排名",
	country VARCHAR(20) NOT NULL
);

INSERT INTO websites(NAME,url,alexa,country)VALUES
("googie","http://www.goole.com",1,"usa"),
("淘宝","http://www.taobao.com",13,"cn"),
("菜鸟","http://www.runoob.com",4689,"cn"),
("微博","http://www.weibo.com",20,"cn"),
("facebook","http://www.facebook.com",3,"usa"),
("stachkovflow","http://www.stachkovflow.com",0,"ind"),
("百度","http://www.baidu.com",4,"cn");
 
CREATE TABLE IF NOT EXISTS access_log(
	aid INT PRIMARY KEY AUTO_INCREMENT,
	site_id INT NOT NULL,
	COUNT INT DEFAULT 0,
	DATE DATE
);
INSERT INTO access_log(site_id,COUNT,DATE)VALUES
(1,45,"2016-05-10"),
(2,100,"2016-05-10"),
(5,415,"2016-05-10"),
(5,200,"2016-05-10"),
(6,7,"2016-05-10"),
(7,145,"2016-05-10"),
(8,45,"2016-05-10"),
(9,545,"2016-05-10"),
(10,201,"2016-05-10");
 
USE web;
#内连接,...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;

子查询
查询部门编号大于2， 且工资大于5000 的员工姓名、工资、部门编号、部门名称。

select e.emp_name, e.salary, e.dept_id, d.`dept_name` 
from (
	select `emp_name`, dept_id,salary from emp
	where dept_id >= 2
	and salary > 5000
) e
left join dept d 
on e.dept_id = `id`;