mysql 整理

最新推荐文章于 2025-04-05 16:32:58 发布

binger819623

最新推荐文章于 2025-04-05 16:32:58 发布

阅读量1.1k

点赞数

分类专栏： MYSQL 文章标签： mysql character table database 数据库 collation

本文链接：https://blog.youkuaiyun.com/binger819623/article/details/6059524

版权

MYSQL 专栏收录该内容

22 篇文章

订阅专栏

MySQL 时区设置
#查看时区设置情况：
方法一：mysql> show variables like '%time_zone%';
方法二：mysql> SELECT @@global.time_zone, @@session.time_zone;
方法三：mysql > select CURTIME();
或
mysql > select now();
#设置在/etc/mysql/my.conf中[mysqld] 下添加时区设置：
方法一：
    #default-time-zone=timezone
    default-time-zone = '+8:00'
方法二：
    mysql > SET time_zone = '+8:00'; # 此为北京时，我们所在东8区
    mysql> flush privileges; # 立即生效

20、使用procedure analyse()
可以使用procedure analyse()显示最佳类型的建议，使用很简单，在select语句后面加上procedure analyse()就可以了;例如：
select * from students procedure analyse();
select * from students procedure analyse(16,256);
第二条语句要求procedure analyse()不要建议含有多于16个值，或者含有多于256字节的enum类型，如果没有限制，输出可能会很长;
如何查询mysql中执行效率低的sql语句
配置my.cnf/my.ini，增加 --log-slow-queries 配置，记录所有的slow query，然后挨个优化

1、启动MySQL服务器
两种方法: 一是用winmysqladmin，如果机器启动时已自动运行，则可直接进入下一步操作。二是在DOS方式下运行 d:/mysql/bin/mysqld ; 当然也可以 net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start”命令，注意启动者应具有管理员权限。
2、退出MySQL操作界面
在mysql>提示符下输入quit可以随时退出交互操作界面:
mysql> quit
3、采用中文字符集：
shell> mysql -uroot -p --default-character-set=gbk
mysql> SET NAMES 'charset_name '
mysql> SET CHARACTER SET charset_name
上面两句等价于：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x ;

set global innodb_flush_log_at_trx_commit=0;

linux下/etc/mysql/my.cnf(windows下my.ini)
[client] 下添加
default-character-set=utf8 默认字符集为utf8
[mysqld] 添加
default-character-set=utf8 默认字符集为utf8
#character-set-server=utf8
collation-server=utf8_general_ci
init_connect='set collation_connection=utf8_general_ci'
init_connect='set names utf8' (设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行;
注意该参数对于连接数据库的用户是超级用户组的用户将被忽略，这样是为了避免该参数导致数据库致命错误，而无法使用任何一个用户连接上修改该项配置)
修改好后，重新启动mysql 即可，查询一下show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/mysql/charsets/ |
----------------------------------------------------------------
查看数据库的字符集
show variables like 'character_set_%';
show variables like '%collation%';
show charset;
--设置数据库字符编码为utf8
set names 'utf8'
alter database databaseName character set utf8; //修改数据库databaseName的
create database mydb character set utf8;
set character_set_client=utf8; //直接修改全部的
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;

---------------------------------

mysqld –default-character-set=gb2312 ;设置默认字符集为gb2312

查看Mysql版本
1、mysql -V
2、mysql> status;
3、mysql --help | grep Distrib
4、mysql> select version();

1、删除student_course数据库中的students数据表：
rm -f student_course/students.*

MySQL修改密码
方法一：
# /etc/init.d/mysql stop
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
# /etc/init.d/mysql restart
# mysql -uroot -p
Enter password: <输入新设的密码newpassword>
mysql>
方法二：

1、mysqladmin -u root -p password mypasswd
输入这个命令后，需要输入root的原密码，然后root的密码将改为mypasswd。
把命令里的root改为你的用户名，你就可以改你自己的密码了
2、mysql> INSERT INTO mysql.user (Host ,User,Password) VALUES(%,jeffrey,PASSWORD('ccc'));
mysql> FLUSH PRIVILEGES
3、mysql> REPLACE INTO mysql.user (Host,User,Password)

-----------------
改变mysql管理员的密码
方法1: mysqladmin -u root password ‘new_password’ (一般安装时用此方法设置)
方法2:UPDATE user SET password=PASSWORD(’new_password’) WHERE user=’root’;
FLUSH PRIVILEGES;
方法3:SET PASSWORD FOR root=PASSWORD(’new_password’);

MySQL数据库丢失root密码后该如何补救？

1. KILL掉系统里的MySQL进程；killall -TERM mysqld

2. 用以下命令启动MySQL，以不检查权限的方式启动；safe_mysqld --skip-grant-tables &

3. 然后用空密码方式使用root用户登录 MySQL；mysql -u root

4. 修改root用户的密码；

 
 mysql> update mysql.user set password=PASSWORD('新密码') where User='root';  
 
mysql> flush privileges;  
 
mysql> quit

重新启动MySQL，就可以使用新密码登录了

修改表信息：

看你的mysql现在已提供什么存储引擎         :mysql> show engines;
看你的mysql当前默认的存储引擎            :mysql> show variables like '%storage_engine%';
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎)    :mysql> show create table 表名;

ALTER TABLE supe_companyitems ENGINE = InnoDB;

ALTER TABLE table_name AUTO_INCREMENT=66666 ＃修改表的自增字段的起始ID值
alter table table_name engine innodb|myisam|memory ;＃更改表类型
alter table user change id id int ,drop FOREIGN/primary key; ＃删除自增和PRIMARY KEY
alter table user modify column id int ,drop FOREIGN/primary key;＃删除自增和PRIMARY KEY

alter table table_name add primary key (picid) ; 向表中增加一个主键
alter table review convert to character set utf8; ＃修改表字符集
alter table table_name character set gb2312 改变表的编码
alter table table_name rename new_t; ＃MYSQL更改表名

ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a); // 在列d上增加一个索引，并且使列a为主键
ALTER TABLE t2 DROP COLUMN c; // 删出列c
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c); // 增加一个新的AUTO_INCREMENT整数列，命名为c。注意，我们索引了c，因为AUTO_INCREMENT柱必须被索引，并且另外我们声明c为 NOT NULL，因为索引了的列不能是NULL。当你增加一个AUTO_INCREMENT列时，自动地用顺序数字填入列值。

MYSQL添加列 : alter table table_name add column userid int after picid;
MYSQL删除列: alter table table_name drop column c_n;
MYSQL创建索引 :
alter table c_table add index (c_n1,c_n2);
alter table c_table add unique index_name(c_n);
alter table c_table add primary key(sid);
create index ind_id on table1 (id);
create unique index ind_id on table1 (id);//建立唯一性索引
MYSQL删除索引:
alter table c_table drop index c_n1;
drop index idx_id on table1;
MySQL查看索引： show index from c_table;
MYSQL更改列信息:
alter table t_table change c_1 c_1 varchar(200);
alter table t_table modify 1 c_1 varchar(200);

insert插入语句: insert into table_name (c_1,c_2) values ('x1',1);
update语句: update　table_name set c_1 =1 where c_2=3;
MYSQL删除数据库或者表:
drop table table_name;
drop database database_name;//使用mysql_drop_db()可以删除的.
insert into table1 (select * from table2 )
删除一个表的所有行:使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name

创建触发器：
delimiter //
CREATE TRIGGER insertUser BEFORE INSERT ON vrm_login
FOR EACH ROW BEGIN
INSERT INTO vrm_admin_users SET email = NEW.login_email;
END; //
CREATE TRIGGER updateUser AFTER UPDATE ON vrm_login
FOR EACH ROW BEGIN
update vrm_admin_users
SET password = NEW.login_passwd,last_ip = NEW.login_ip,add_time = NEW.login_time
where email=NEW.login_email;
#DELETE FROM test3 WHERE a3 = NEW.a1;
END; //
delimiter ;
删除触发器：DROP TRIGGER [schema_name.]trigger_name

show tables或show tables from database_name; // 显示当前数据库中所有表的名称
show databases; // 显示mysql中所有数据库的名称
show columns from table_name from database_name; 或show columns from database_name.table_name; // 显示表中列名称
show grants for user_name; // 显示一个用户的权限，显示结果类似于grant 命令
show index from table_name; // 列出table_name中所有的索引
show status; // 显示一些系统特定资源的信息，例如，正在运行的线程数量
show variables; // 显示系统变量的名称和值
show processlist; // 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看
他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。

show table status; // 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间
show table status where comment='view' ; 查看视图
show status like 's%';
show table status from db_name;;列出当前db_name中数据表的信息。
show privileges; // 显示服务器所支持的不同权限
show create database database_name; // 显示create database 语句是否能够创建指定的数据库
show create table table_name; // 显示create database 语句是否能够创建指定的数据库
show engies; // 显示安装以后可用的存储引擎和默认引擎。
show innodb status; // 显示innoDB存储引擎的状态
show logs; // 显示BDB存储引擎的日志
show warnings; // 显示最后一个执行的语句所产生的错误、警告和通知
show errors; // 只显示最后一个执行语句所产生的错误

show procedure status; 查看存储过程列表
show create table table_name

show create trigger trigger_name; 查看某个触发器定义
show triggers; 查看触发器

---------------------------

explain table_name / describe table_name /desc table_name /SHOW COLUMNS FROM table_name ; 列出table_name完整信息.
explain select * from zs_goods where goods_id>20000 order by goods_sn

--------------------------------
select user(); 显示当前用户。
select password(’root’); 显示当前用户密码
select now(); 显示当前日期

SELECT version();
select database();
------------------------------------

flush privileges 在不重启的情况下刷新用户权限
-------------------------------------------------------------------
1.复制表结构及数据到新表：CREATE TABLE 新表 LIKE 旧表
CREATE TABLE cc SELECT * FROM tmp where 1<>1 即:让WHERE条件不成立.
2.复制旧表的数据到新表(假设两个表结构一样) ： INSERT INTO 新表 SELECT * FROM 旧表
3.复制旧表的数据到新表(假设两个表结构不一样) ： INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表
4.将查询结果存入文件： select ... into outfile 'D://result.txt'

备份表。

1. SELECT * INTO OUTFILE ‘D:/S.SQL’ FIELDS TERMINATED BY ',' LINES TERMINATED BY '/n' FROM B WHERE …

2. BACKUP TABLE B TO ‘D:/S.SQL’

3. MYSQLDUMP

a) Mysqldump -h -u -p --no-date dbname>e.dbname.sql-------只备份表结构

b) 在A机器：192.168.4.20上的数据库TTT可以通过mysqldump远程备份到B机器：192.168.4.50上。前提是在B机器上创建了数据库TTT。

mysqldump -hA -u -p dbname | mysql -hB -u -p dbname.

还原表。

1. LOAD DATA INFILE ‘D:/S.SQL’ INTO TABLE B.

2. RESTORE TABLE B FROM ‘D:/S.SQL’

3. MYSQL

Mysql –h –u –p <e:/dbname.sql

条件查询

SELECT CASE WHEN count(*)=0 THEN -1（这里给出默认值）ELSE id END into @v_id FROM t_aaaa WHERE name=p_name;

纵变横

select group_concat(id) from t where id<5;--------------1，2，3，4.

SQL删除某些字段重复的记录(只保留一条)
delete t1 from test t1,test t2 where t1.id>t2.id and t1.title=t2.title;
有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复，比较容易解决，使用
select distinct * from tableName 就可以得到无重复记录的结果集。
如果该表需要删除重复的记录(重复记录保留1条)，可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
2、第二类部分重复
①、select goods_id,count(*) as countgoods,min(rec_id) from tmp_order_goods group by goods_id having countgoods>1 order by countgoods desc ;
删除重复：
create table tmp0 as select min(rec_id) rec_id from tmp_order_goods group by goods_id having count(*)>1;
delete from tmp_order_goods where rec_id not in(select rec_id from tmp0);
drop table tmp0;
如果不加临时表，会出现错误“You can't specify target table 'table name' for update in FROM clause”
②、查找表中多余的重复记录(多个字段)
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
删除表中多余的重复记录(多个字段)，只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
③、查找表中多余的重复记录(多个字段)，不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

3、select uid,username,itemid ,count(distinct uid) from supe_spaceitems group by uid ;

select uid,username,itemid ,group_concat(distinct uid) from supe_spaceitems group by uid ;

触发器和存储过程的优缺点汇总
缺点：
1、可移植性是存储过程和触发器最大的缺点。
2、占用服务器端太多的资源，对服务器造成很大的压力
3、不能做DDL。
4、触发器排错困难，而且数据容易造成不一致，后期维护不方便。
优点：
1、预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率。
2、存储过程可以重复使用，减少开发人员的工作量。
3、业务逻辑封装性好，修改方便。
4、安全。不会有SQL语句注入问题存在。
---------------------------------------------------------------------------------------
grant all on mydb.* to NewUserName@HostName identified by “password” ;
grant usage on *.* to NewUserName@HostName identified by “password”;
grant select,insert,update on mydb.* to NewUserName@HostName identified by “password”;
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;
grant all privileges on *.* to root@localhost
grant select,insert,delete,update,alter,create,drop on lybbs.* to NewUserName@”%” identified by “lybbs”;
若要给此用户赋予他在相应对象上的权限的管理能力，可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户，Password字段应用PASSWORD 函数进行更新加密，以防不轨之人窃看密码。对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，回收权限可以通过更新User表相应字段，也可以使用REVOKE操作。
全局管理权限：
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限：
Alter: 修改已存在的数据表(例如增加/删除列)和索引。
Create: 建立新的数据库或数据表。
Delete: 删除表的记录。
Drop: 删除数据表或数据库。
INDEX: 建立或删除索引。
Insert: 增加表的记录。
Select: 显示/搜索表的记录。
Update: 修改表中已存在的记录。
特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录–其它什么也不允许做。

SQL2000系统表的应用
--1：获取当前数据库中的所有用户表： select Name from sysobjects where xtype='u' and status>=0
--2：获取某一个表的所有字段： select name from syscolumns where id=object_id('表名')
--3：查看与某一个表相关的视图、存储过程、函数：select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
--4：查看当前数据库中所有存储过程：select name as 存储过程名称 from sysobjects where xtype='P'
--5：查询用户创建的所有数据库：select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者 select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
--6：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns where table_name = '表名'
--7:取得表字段的描述
select name,
(select value from sysproperties where id = syscolumns.id and smallid=syscolumns.colid) as 描述 from syscolumns where id=object_id('表名')