mysql基础语法_杨小明

大数据_杨小明

已于 2022-05-08 20:37:36 修改

阅读量1.2k

点赞数

文章标签： mysql 数据库 sql

于 2022-03-13 19:44:13 首次发布

本文链接：https://blog.csdn.net/dh18674633979/article/details/123459853

版权

本文介绍了MySQL的基础语法，包括建库建表、增删改查、表关联查询、索引类型（普通、唯一、组合索引）、视图创建与更改、事务处理和触发器案例。同时探讨了MySQL查询优化，如避免在索引字段上进行运算，合理使用EXISTS子句等。

摘要由CSDN通过智能技术生成

建库建表语法，字段数据类型

建库

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;