插入数据
插入数据之前首先创建一张persons表
CREATE TABLE persons(
id INT NOT NULL AUTO_INCREMENT,
name CHAR(40) NOT NULL DEFAULT '无名',
age INT NOT NULL DEFAULT 0,
info CHAR(50) NULL,
PRIMARY KEY(id)
);
为表的所有字段插入数据
一次插入一条数据
INSERT INTO persons(id,name,age,info)VALUES(1,'张三',20,,'张三的介绍');
一次插入多条数据
INSERT INTO persons(id,name,age,info)
VALUES(1,'张三',20,,'张三的介绍'),
(2,'李四',30,'李四的介绍'),
(3,'王五',34,'王五的介绍');
如果插入的字段正好匹配的话也可以直接这样
INSERT INTO persons VALUES(1,'张三',20,,'张三的介绍');
为表的指定字段插入数据
在上面的表结构中,info字段可以为空,所以这里不指定添加
一次插入一条数据
INSERT INTO persons(id,name,age)VALUES(1,'张三',20);
一次插入多条数据
INSERT INTO persons(id,name,age)
VALUES(1,'张三',20),
(2,'李四',30),
(3,'王五',34);
将查询的结果插入到表中
在创建一个表persons_old
CREATE TABLE persons_old(
id INT NOT NULL AUTO_INCREMENT,
name CHAR(40) NOT NULL DEFAULT '无名',
age INT NOT NULL DEFAULT 0,
info CHAR(50) NULL,
PRIMARY KEY(id)
);
在persons_old中插入一条数据
INSERT INTO persons_old VALUES(10,'张大炮',50,'张大炮的信息');
现在分别查看两个表
现在查询persons_old的数据并插入的persons中
INSERT INTO persons(id,name,age,info)
SELECT id,name,age,info
FROM persons_old;
更新数据
UPDATE 表名
SET 字段名1=值,字段名2=值....,字段名n
WHERE 条件
把年龄大于30的Info字段该为高龄
UPDATE persons
SET info='高龄'
WHERE age>30;
删除数据
DELETE FROM 表名[WHERE 条件];
把年龄大于40岁的信息删除
DELETE FROM persons WHERE age>40;
把表中的记录全部删除
DELETE FROM persons;
MySQL索引
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可提高数据库中特定数据的查询速度。
例如:数据库中有2万条记录,现在要执行这样的一个查询SELECT * FROM table WHERE num=1000,如果没有索引,必须便利整个表,直到num等于10000的这一行被找到为止,如果要在num列上创建索引,MySQL不需要任何扫描,直接在索引类找10000,就可以得知这行的位置。
索引的优点:
1>通过创建唯一索引,可以保证数据库表中的每一行数据的唯一性。
2>可以大大加快数据的查询速度,这也是创建索引的最主要原因。
3>在实现数据的参考完整性方面,可以加速表和表之间的连接。
4>在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间。
索引的缺点:
1>创建索引和维护索引要耗费时间,并且随着数据的增加所耗费的时间也会增加。
2>索引需要占磁盘空间,除了数据表占用数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。
3>当对表中的数据进行增加、删除和修改的时候,索引页要动态的维护,这样就降低了维护速度。
索引的分类:
索引的设计原则:
索引的创建
创建表的时候创建索引
CREATE TABEL 表名 (
字段1,数据类型,
字段2,数据类型,....
字段n,数据类型
[UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][索引名](索引值[长度])[ASC|DESC]
);
创建普通索引
最基本的索引类型,没有唯一性之类的限制,其作用只是加快对数据的访问速度。
在book表中的name字段建立普通索引
CREATE TABLE book (
b_id INT NOT NULL,
b_name VARCHAR(20) NOT NULL,
b_price INT(10) NOT NULL,
INDEX demoIdx(b_name)
);
使用SHOW CREATE TABLE 查看表结构
使用EXPLAIN语句查看索引是否正在使用
创建唯一索引
唯一索引和普通索引最大的区别就是,索引列的值必须唯一,但允许有空值,如果是组合索引,则列索引的组合必须唯一。
CREATE TABLE book (
b_id INT NOT NULL,
b_name VARCHAR(20) NOT NULL,
b_price INT(10) NOT NULL,
UNIQUE INDEX UniqIdx(b_id)
);
创建单列索引
单列索引是在数据表中的某一个字段上创建的索引,一个表中可以创建多个单列索引,前面两个列子中创建的索引都是单列索引。
CREATE TABLE book (
b_id INT NOT NULL,
b_name VARCHAR(20) NOT NULL,
b_price INT(10) NOT NULL,
INDEX singleIdx(b_name(10))
);
上面代码中创建了一个名为singleIdx的单列索引,索引长度为10。
创建组合索引
CREATE TABLE book (
b_id INT NOT NULL,
b_name VARCHAR(20) NOT NULL,
b_price INT(10) NOT NULL,
INDEX singleIdx(b_id,b_name(10))
);
创建全文索引
FULLTEXT全文索引可以用于全文搜索,只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。
CREATE TABLE book (
b_id INT NOT NULL,
b_name VARCHAR(20) NOT NULL,
b_price INT(10) NOT NULL,
b_info VARCHAR(255),
FULLTEXT INDEX FullTxIdx(b_info)
);
在已经存在的表上创建索引
使用ALTER TABLE 语句创建索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]
[索引名](索引列(索引长度))[ASC|DESC];
使用SHOW INDEX FROM 表名查看创建的索引
使用CREATE INDEX创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名(索引列[索引长度])[ASC|DESC];
索引的删除
使用ALTER TABLE 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;
使用DROP INDEX 语句删除索引
DROP INDEX 索引名 ON 表名;
MySQL视图
视图的创建
在单表上创建视图
CREATE VIEW 视图名(字段1,字段2,字段3...字段n)
AS SELECT 字段1,字段2,字段3...字段n
FROM 表名;
WHERE 条件
在多表上创建视图
CREATE VIEW 视图名(字段1,字段2,字段3,...字段n)
AS SELECT 表1.字段1,表1.字段2,表2.字段1,表2.字段2
FROM 表1,表2
WHERE 条件
视图的查看
使用DESCRIBE关键字
DESCRIBE 视图名;
使用SHOW TABLE STATUS语句查看
SHOW TABLE STATUS LIKE '视图名';
使用SHOW CREATE VIEW语句查看
SHOW CREATE VIEW 视图名;
在views表查看视图详情信息
SELECT * FROM information_schema.views;
视图的修改
使用CREATE OR REPLACE VIEW语句修改视图
CREATE OR REPLACE VIEW 视图名 AS .....;
使用ALTER语句修改视图
ALTER VIEW 视图名 AS.....;
视图的更新
使用INSERT INTO更新视图
INSERT INTO 视图名(字段1,...字段n) VALUES(值,值....值);
使用UPDATE更新视图
UPDATE 视图名 SET 字段名=值 WHERE 条件;
使用DELETE更新视图
DELETE FROM 视图名 WHERE 条件;
视图的删除
使用DROP关键字删除视图
DROP VIEW [IF EXISTS] 视图名;
MySQL用户管理
新建普通用户
使用CREATE USER创建新用户
使用CREATE USER创建的用户只有登录权限,没有任何其他权限
#指定了IP地址,用户名和密码
CREATE USER '用户名'@'IP地址' IDENTIFIED BY '密码';
使用GRANT创建新用户
GRANT 权限1,权限2,...权限n ON 数据库.表名
TO '用户名'@'IP地址' IDENTIFIED BY '密码'
[WITH GRANT OPTION];
直接操作MySQL用户表
INSERT INTO mysql.user(HOST,USER,PASSWORD,[privilegelist])
VALUES('IP地址','用户名',PASSWORD('密码'),privilegelist);
直接操作MySQL用户表的话要使用
flush privileges;来刷新权限
删除普通用户
使用DROP USER语句删除用户
DROP USER 用户名;
使用DELETE语句删除用户
DELETE FROM MySQL.user WHERE host='IP地址' AND user='用户名';
ROOT用户修改自己的密码
使用mysqladmin命令行指定新密码
这个命令直接打开cmd工具执行
mysqladmin-u 用户名 -h IP地址 -p password 密码
修改mysql数据库的user表
UPDATE mysql.user SET authentication_string=PASSWORD('新密码')
WHERE User='root' AND Host='localhost';
修改SET语句修改ROOT用户的密码
SET PASSWORD=PASSWORD('456789');
ROOT修改普通用户密码
使用SET语句修改普通用户密码
SET PASSWORD FOR '用户名'@'IP地址' = PASSWORD('新密码');
使用UPDATE语句修改普通用户密码
UPDATE mysql.user SET authentication_string=PASSWORD('新密码')
WHERE User='用户名' AND Host='IP地址';
使用GRANT语句修改普通用户密码
GRANT USAGE ON *.* TO '用户名'@'IP地址' IDENTIFIED BY '新密码';
普通用户修改密码
普通用户登录MYSQL服务器后,通过SET语句设置自己的密码
SET PASSWORD=PASSWORD('新密码');
ROOT用户密码丢失的解决办法
1.先关闭MYSQL服务
2.找到文件my.ini文件,编辑模式打开,在最后加上代码skip-grant-tables
3.启动MYSQL服务,然后直接mysql进入
3.UPDATE mysql.user SET authentication_string=PASSWORD('新密码')WHERE User='root';
4.删除my.ini文件中刚才添加的代码,重启服务
查看授权给某用户的权限
SHOW GRANTS FOR '用户名'@'IP地址';
收回权限
REVOKE 权限1,权限2...权限n ON 数据库.表名 FROM '用户名'@'IP地址';