Mysql基础知识笔记

首页 > 代码库 > Mysql基础知识笔记

2024-09-06 02:38:11 215人阅读

修改客户端/服务器默认编码方式为utf8(\S查看) 在my.ini文件中修改服务器[mysqld] character-set-server=utf8 客户端 [mysql] default-character-set=utf8 然后在重启下mysql服务

查看数据库的编码方式show create database mysql;

MYSQL存储引擎 show engines \G; \G可以是每个字段单独打印到行默认是INNODB

Mysql数据类型数值型日期和时间型字符串型以及不常用的二进制类型注意：

-->CHAR 定长字符串占空间大访问速度快 VARCHAR 变成字符串占空间小访问速度慢

-->ENUM类型(枚举) SET类型(集合)

Mysql约束条件:

-->主键 PRIMARY KEY 可以简写为KEY 唯一标识对应的数据，有单字段主键和多字段主键默认情况下AUTO_INCREMENT不是主键，只是如果没有指定主键会将自动编号默认为主键

-->唯一性 UNIQUE KEY 可以简写为 UNIQUE 标识该属性的值是唯一的制定了UNIQUE的值必须是唯一的

-->非空 NOT NULL

-->默认值 DEFAULT

-->外键 FOREIGN KEY

Table的相关操作:

--创建数据表 网站访客注册 用户名 密码 不可为空 用户名需要唯一 id自增长
CREATE TABLE IF NOT EXISTS visitor(
id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
password VARCHAR(20) NOT NULL,
retime TIMESTAMP NOT NULL DEFAULT NOW()
)ENGINE=INNODB AUTO_INCREMENT=100;

-->创建数据表，先得选择一个数据库比如 USE test;选择test数据库，在test数据库下创建了visitor表

-->查看 DESC visitor; 或者SHOW CREATE TABLE visitor;

-->插入数据如下:

INSERT visitor(username,password) VALUES(‘tangxin‘,‘root‘);

INSERT visitor(username,password) VALUES(‘josh‘,‘josh‘);

-->查询插入的数据SELECT *FROM visitor;

-->删除表中一条记录 DELETE FROM visitor WHERE id=102;

-->重命名表r ename table visitor to test; 或者ALTER TABLE visitor RENAME test;

-->更改数据表某个内容 UPDATE visitor SET username=‘Jhon‘ WHERE id=104;

-->添加字段ALTER TABLE visitor ADD age TINYINT UNSIGNED NOT NULL;

-->删除字段 ALTER TABLE visitor DROP age; 或者ALTER TABLE visitor DROP column age；

-->修改字段ALTER TABLE visitor MODIFY username VARCHAR(10) NOT NULL UNIQUE;

-->添加默认值ALTER TABLE visitor ALTER age SET DEFAULT 18;

-->删除默认值ALTER TABLE visitor ALTER age DROP DEFAULT;

-->添加删除主键 ALTER TABLE visitor DROP PRIMARY KEY; ALTER TABLE visitor ADD PRIMARY KEY(id);

　　但是如果指定的主键是自增长必须先去掉自增长才能删除成功!!!

-->添加删除唯一ALTER TABLE visitor DROP INDEX|KEY username; DROP INDEX username ON visiotr;

　　　　　　 ALTER TABLE visitor ADD UNIQUE(username);或者CREATE UNIQUE INDEX ON visitor(useername);

-->查询记录 SELECT username,id,password,retime FROM visitor WHERE id=100;

-->模糊查询 SELECT * FROM visitor WHERE username [NOT] LIKE ‘%t‘; %匹配任意字符 _匹配单字符还有[ ] ，[^ ]

-->范围查询 SELECT *FROM visitor WHERE username IN(‘josh‘,‘tang‘,‘xxx‘,‘rrrr‘); SELECT *FROM visitor WHERE id BETWEEN 90 AND 102;

-->分组查询 SELECT *FROM visitor GROUP BY id;

--> 聚合函数比如SELECT COUNT(id) FROM visitor; 也可以求和求平均数求最大最小值还有sum() max() min()等等聚合函数

-->分组查询和聚合函数配合使用 SELECT COUNT(id) FROM visitor GROUP BY id;

--> 限定组的结果使用HAVING 因为where不能配合聚合函数使用

-->ORDER BY ASC;从小到大排序; DESC; 按sal从大到小排序

-->limit 对返回结果进行限定

测试内连接外链接外键

--测试内连接  外链接 外键
--创建部门table  proid departame
CREATE TABLE depart(
proid TINYINT UNSIGNED,
departame VARCHAR(20)
);

INSERT INTO depart VALUES(1,‘COREPART‘),(2,‘BSPPART‘),(3,‘OSPPART‘),(4,‘CSPPART‘);

--创建员工table 所属部门id 人员名字
CREATE TABLE employee(
id TINYINT UNSIGNED,
name VARCHAR(20),
age TINYINT UNSIGNED
);

INSERT employee VALUES(1,‘Green‘,28),(1,‘Justin‘,28),(2,‘josh‘,27),(2,‘Jothan‘,29),(3,‘Brooks‘,28),(4,‘Rick‘,32),(1,‘Kervin‘,29);

--测试内连接
SELECT d.proid,d.departame,e.name,e.age FROM

depart AS d

JOIN employee AS e

ON d.proid=e.id

ORDER BY proid ASC;

--测试外连接
--左连接 部门BMCPART在员工表中没有对应数据 但是查询是依旧会显示出来 显示为NULL 右连接 全连接同理
INSERT INTO depart VALUES(5,‘BMCPART‘);

SELECT d.departame,e.name FROM

depart AS d

LEFT JOIN employee AS e

ON d.proid=e.id

ORDER BY proid ASC;


--测试外键
--先删除部门5 BMCPART
DELETE FROM depart WHERE proid=5;

--向部门5插入了一个员工 可以成功插入 但是部门5压根不存在 这种情况下我们需要外键约束
INSERT employee VALUES(5,‘Leo‘,26);

--创建外键table
CREATE TABLE employee(
id TINYINT UNSIGNED,
name VARCHAR(20),
age TINYINT UNSIGNED,
CONSTRAINT em_fk_dp FOREIGN KEY(id) REFERENCES depart(proid)
);

--这个时候往部门5插入员工就会报错
INSERT employee VALUES(5,‘Leo‘,26);

--在主表数据发生改变时，与之关联的从表数据应该如何处理 on update | on delete
--先删除外键
ALTER TABLE employee DROP FOREIGN KEY em_fk_dp;

--cascade关联操作，如果主表被更新或删除，从表也会执行相应的操作
--set null,表示从表数据不指向主表任何记录
--restrict：拒绝主表的相关操作
--添加外键
ALTER TABLE employee ADD CONSTRAINT em_fk_dp FOREIGN KEY(id) REFERENCES depart(proid) ON DELETE CASCADE;

--如果主表删除部门1 部门1下的员工也应该删除
DELETE FROM depart WHERE proid=1;

SELECT *FROM employee;

--联合查询 UNION 加上ALL 不删除重复行  查询的column必须一致
SELECT proid,departame FROM depart

UNION ALL

SELECT id,name FROM employee;


--子查询
--where型子查询 把内层查询结果当作外层查询的比较条件
SELECT * FROM depart WHERE depart.proid=ANY(SELECT id FROM employee);

--from子查询 把内层的查询结果供外层再次查询 后面的AS t不能少 每个派生出来的表都需要一个别名
SELECT departame FROM (SELECT *FROM depart WHERE proid=2) AS t;

--exits子查询 把外层查询结果拿到内层，看内层的查询是否成立
SELECT proid,departame FROM depart WHERE EXISTS(SELECT *FROM employee WHERE depart.proid=employee.id);

--正则表达式查询 REGEXP 比如 具体详细看看正则表达式
SELECT *FROM employee WHERE id REGEXP ‘.‘;

--MYSQL函数库 包括 数学函数 字符串函数 条件判断函数 系统信息函数 日期和时间函数等
--http://www.cnblogs.com/kissdodog/p/4168721.html

索引:

对于千万级别以上的数据，索引可以加快查询速度一般有B-TREE索引以及 HASH索引 MYIASM INNODB存储引擎支持B-TREE索引 MEMORY存储引擎支持HASH索引

索引类型：PRIMARY , INDEX , UNIQUE , FULLTEXT , SPAIAL

--创建唯一性索引
--一个表可以有多个唯一性索引 但是只能有一个主键索引
CREATE TABLE test_index2(
id TINYINT UNSIGNED NOT NULL KEY,
username VARCHAR(20) UNIQUE,
unicard CHAR(18),
UNIQUE KEY in_unicard(unicard)
);

--全文索引在查询很长的一篇文章是很有用，内容不多的话普通索引足矣
--SPAIAL 控件索引使用较少 暂不考虑

--创建单列索引 形式和普通索引一样
CREATE TABLE test_index3(
id TINYINT UNSIGNED AUTO_INCREMENT NOT NULL KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX in_test1(test1)
);

--创建多列索引
CREATE TABLE test_index4(
id TINYINT UNSIGNED AUTO_INCREMENT NOT NULL KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
UNIQUE INDEX mul_test1_test2_test3_test4(test1,test2,test3,test4)
);

--删除索引
DROP INDEX in_id ON test_index;
ALTER TABLE test_index DROP INDEX in_username;

--命令行创建索引
CREATE INDEX in_id ON test_index(id);
ALTER TABLE test_index ADD UNIQUE INDEX in_username(username);

PS：

图形化界面管理数据库比如PHPadmin方式的web管理或者下载sqlyog软件来进行管理

Mysql基础知识笔记

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > Mysql基础知识笔记

Mysql基础知识笔记

看完仍有疑问？有类似问题直接问程序猿