目录
mysql出现access denied for user 'root@localhost'(using password: YES)
3. MySQL删除数据Delete与Truncate语句使用比较
1. mac mysql安装
压缩包安装(社区版)：
http://blog.sina.com.cn/s/blog_9ea3a4b70101ihl3.html
注： 安装包的形式 或 brew 安装mysql后，一直登录报错（不知道什么原因）
注： 建表报错：MySQL：Error : Tablespace for table `database`.`temp` exists. Please DISCARD the tablespace before IMPORT.
解决办法
http://www.shaoqun.com/a/101373.aspx
mysql 环境变量配置：
vi .bash_profile
export MYSQL_HOME=../mysql-5.7.13-osx10.11-x86_64/bin
PATH=$PATH:$MYSQL_HOME
source ~/.bash_profile--让以上所做的配置生效
2. 命令
第一种方法:
在命令行下(未连接数据库),输入 mysql -h localhost -u root -p123456 < F:\hello world\niuzi.sql (注意路径不用加引号的!!) 回车即可.
第二种方法:
在命令行下(已连接数据库,此时的提示符为 mysql> ),输入 source F:\hello world\niuzi.sql (注意路径不用加引号的) 或者 \. F:\hello world\niuzi.sql (注意路径不用加引号的) 回车即可
| 命令 | 作用 |
| show databases; | 所有数据库 |
| use database; show tables; | 所有表 |
| DROP DATABASE IF EXISTS sampledb; CREATE DATABASE sampledb DEFAULT CHARACTER SET utf8; USE sampledb; ##创建用户表 CREATE TABLE t_user ( user_id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(30), credits INT, password VARCHAR(32), last_visit datetime, last_ip VARCHAR(23) )ENGINE=InnoDB; ##创建用户登录日志表 CREATE TABLE t_login_log ( login_log_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, ip VARCHAR(23), login_datetime datetime )ENGINE=InnoDB; ##插入初始化数据 INSERT INTO t_user (user_name,password) VALUES('admin','123456'); COMMIT; | 新建库 新建表 |
| describe t_login_log;
或者
desc log; | 表结构 |
| use 库名； create table 表名 (字段设定列表)； | |
| drop database 库名; drop table 表名； delete from 表名; | |
| mysql -h110.110.110.110 -u root -p 123; exit 或者： cd /usr/local/mysql/bin 启动mysql服务器:sudo ./mysqld_safe --defaults-file=/etc/my.cnf --user=root & sudo ./mysql -u root -p 输入密码：123
________________________ win7 mysql net start mysql 启动 net stop mysql 停止
win7 mysql登录出现： mysql出现access denied for user 'root@localhost'(using password: YES)
解决：
1.关闭正在运行的MySQL（net stop mysql）。
2.打开DOS窗口，转到mysql\bin目录。 3.输入mysqld --skip-grant-tables回车。如果没有出现提示信息，那就对了。 4.再开一个DOS窗口(因为刚才那个DOS窗口已经不能动了)，转到mysql\bin目录。 5.输入mysql回车
| 登录 退出 |
| 刚安装好MYSQL，超级用户root是没有密码的，故直接回车即可进入到MYSQL中了 再将root的密码改为123： sudo ./mysqladmin -u root -p password 123; | 修改密码 |
| select version(); select now(); //显示当前时间 select ((4 * 4) / 10 ) + 25;//当计算器 | mysql版本 |
| insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59); update MyClass set name='Mary' where id=1; alter table MyClass add passtest int(4) default '0'; | |
| select * from MyClass order by id limit 0,2; | 查看表 MyClass 中前2行数据 |
| rename table MyClass to YouClass; | 修改表名 |
| 1.导出整个数据库 导出文件默认是存在mysql\bin目录下 mysqldump -u 用户名 -p 数据库名 > 导出的文件名 mysqldump -u user_name -p123456 database_name > outfile_name.sql 2.导出一个表 mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 mysqldump -u user_name -p database_name table_name > outfile_name.sql 3.导出一个数据库结构 mysqldump -u user_name -p -d –add-drop-table database_name > outfile_name.sql -d 没有数据 –add-drop-table 在每个create语句之前增加一个drop table | 备份数据库 |
3. MySQL删除数据Delete与Truncate语句使用比较
空mysqll表内容常见的有两种方法：一种delete，一种是truncate 。 不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容。
1）效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。
2）delete的效果有点像将mysql表中所有记录一条一条删除到删完
3）truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。
4. 常用sql
小王 65
小张 80
小李 72
小赵 93
排名在第3名的成绩是72，如何用1条语句把它找出来？
`s1` char(30) NOT NULL,
`s2` decimal(6,2) default NULL,
PRIMARY KEY (`s1`),
KEY `s2` (`s2`)
)engine=innodb default charset=utf8 ;
insert into t2 values
('小王' ,65),
('小张' ,80),
('小李' ,72),
('小赵' ,93);
select s2 from t2 order by s2 desc limit 2,1;
select s2 from t2 order by s2 desc limit 1 offset 2;
limit后面跟的是1条数据，offset后面是从第3条开始读取注： limit m,n
select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
即取出第3条至第6条，4条记录从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
即取出第3条至第6条，4条记录select * from tablename <条件语句> limit 100,-1
从第100条后开始-最后一条的记录
mysql低版本不支持limit offset
limit offset 在mysql 4.0以上的版本中都可以正常运行，在旧版本的mysql 3.23中无效
limit m offset n 等价于 limit m,n
select s2 from t2 order by s2 desc limit 0,1;
1、offset比较小的时候。
select * from yanxue8_visit limit 10,10
多次运行，时间保持在0.0004-0.0005之间
Select * From yanxue8_visit Where vid ＞=(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10
多次运行，时间保持在0.0005-0.0006之间，主要是0.0006
结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。
2、offset大的时候。
select * from yanxue8_visit limit 10000,10
多次运行，时间保持在0.0187左右
Select * From yanxue8_visit Where vid ＞=(
Select vid From yanxue8_visit Order By vid limit 10000,1
) limit 10
多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。
当偏移1000以上使用子查询法可以有效的提高性能：数据库进阶
- 尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引；
- 尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL
- 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描(使用union all)
-
in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3-
下面的查询也将导致全表扫描：
select id from t where name like ‘%abc%’若要提高效率，可以考虑全文检索。
- 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
- 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
- .尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
- 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
- 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
-
拆分大的 DELETE 或INSERT 语句，批量提交SQL语句
如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
mysql> explain select * from commonfig where id=6;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | commonfig | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)
EXPLAIN列的解释：
select_type:将select查询分为简单(simple)和复杂两种类型。复杂类型又分为子查询(subquery)和from列表中包含子查询(drived)。
table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 indexhe和ALL。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢
- type的解释
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref
- Extra
mysql> explain select distinct config_key from commonfig order by id;
+----+-------------+-----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | commonfig | ALL | NULL | NULL | NULL | NULL | 11 | Using temporary; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)
Extra: Using temporary
使用慢查询分析
在my.ini中：
long_query_time=1
log-slow-queries=xxx\mysqlslow.log
把超过1秒的记录在慢查询日志中
可以用mysqlsla来分析之。也可以在mysqlreport中，有如
DMS分别分析了select ,update,insert,delete,replace等所占的百份比
- 查看索引
mysql> show
index
from
tblname;
mysql> show keys
from
tblname;
mysql> show keys from mock;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mock | 0 | PRIMARY | 1 | url_id | A | 13 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
mysql> show index from mock;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mock | 0 | PRIMARY | 1 | url_id | A | 13 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)- 删除索引
DROP
INDEX
index_name
ON
talbe_name
ALTER
TABLE
table_name
DROP
INDEX
index_name
ALTER
TABLE
table_name
DROP
PRIMARY
KEY
- 添加索引
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
|
1
2
3
|
ALTER
TABLE
table_name
ADD
INDEX
index_name (column_list)
ALTER
TABLE
table_name
ADD
UNIQUE
(column_list)
ALTER
TABLE
table_name
ADD
PRIMARY
KEY
(column_list)
|
CREATE INDEX可对表增加普通索引或UNIQUE索引。
|
1
2
|
CREATE
INDEX
index_name
ON
table_name (column_list)
CREATE
UNIQUE
INDEX
index_name
ON
table_name (column_list)
|
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。
与B-Tree相比，B+Tree有以下不同点：
- 每个结点的指针上限为2d而不是2d+1。
- 内结点不存储data，只存储key；叶子结点不存储指针。
一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。
3）MySQL索引实现
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎(MySQL数据库MyISAM和InnoDB存储引擎的比较)的索引实现方式。
MyISAM引擎使用B+Tree作为索引结构，叶结点的data域存放的是数据记录的地址；
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同：
- 第一个重大区别是InnoDB的数据文件本身就是索引文件
- 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址
索引使用场景
索引的类型
在MySQL中，索引分为两大类：聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同；聚簇索引能够提高多行检索的速度，而非聚簇索引则对单行的检索速度很快。
在这两大类的索引类型下，还可以将索引分成四个小类：
1，普通索引：最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。
2，唯一索引：与普通索引类型，不同的是唯一索引的列值必须唯一，但允许为空值。
3，全文索引：全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。
4，组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。
索引设计原则ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
为了提高搜索效率，我们需要考虑运用多列索引,由于索引文件以B－Tree格式保存，所以我们不用扫描任何记录，即可得到最终结果。
注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。
3.最左前缀：顾名思义，就是最左优先，上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。
注：在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边
扫一扫
1389
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
