搜索到
1
篇
标签为 MySQL 的文章
-
MySQL常用命令 MySQL是一个关系型数据库管理系统，属于 Oracle 旗下产品，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性，MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。官网： MySQL导出数据库，命令：mysqldump -u 用户名 -p 数据库名 > 导出的文件名mysqldump -u root -p db_name > db_name.sql导出所有数据库mysqldump -u root -p –all-databases > all.sql导出一个表，命令：mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名mysqldump -u root -p db_name table_name > table_name.sql导出一个数据库结构mysqldump -u root -p -d --add-drop-table db_name > db_name.sql导入数据库mysqldump -u user_name -p db_name < db_name.sql使用mysql命令mysql -u user_name -p -D db_name < db_name.sql连接MySQL数据库服务器,用户名user_name，密码pwdmysql -u user_name -p pwd添加用户insert into mysql.user(Host,User,Password) values("localhost","user_name",password("pwd")); flush privileges;删除用户delete from user where user = 'user_name' and host='localhost'; flush privileges;查看当前用户select user();数据库授权#授权用户拥有库的所有权限 grant all on db_name.* to user_name@localhost identified by 'pwd'; #授权用户拥有库的select,update权限 grant select,update on db_name.* to user_name@localhost;显示所有数据库show databases;选择要使用的数据库,数据库名use database_name;显示所有表show tables;显示表结构，表名称table_namedesc table_name;插入数据insert into table_name(column1, column2, column3, ...) values(value1, value2, value3, ...);查询数据select * from table_name;更新数据update table_name set column_name = new_value where column_name = column_value;删除数据delete from table_name where column_name = cloumn_value;查看当前使用的数据库select database();当前数据库包含的表信息show tables;查看数据库字符集show variables like '%char%';建表create table table_name(id varchar(20),name varchar(20));查看表结构desc table_name;锁表、解锁flush tables with read lock; unlock tables;删除表drop table table_name;插入数据insert into table_name values("1","value");查询表中的数据select * from table_name; select * from table_name where id = 1; select * from table_name where id like "1%"; select * from table_name order by id limit 0,2; select * from table_name limit 0,2;删除表中数据delete from table_name where id=1;更新表中数据update table_name set cloumn_name = 'value' where id=2;修改表中字段的长度alter table talbe_name modify column column_name varchar(30);在表中增加字段alter table table_name add cloumn_name int(11) default '0';更改表名：rename table old_table_name to new_table_name;对表进行排序select * from table_name order by column_name asc; select * from table_name order by column_name desc;模糊查询select * from table_name where column_name LIKE '%search_keyword%';常见的连表查询select * from table1 join table2 on table1.column_name = table2.column_name; select * from table1 left join table2 on table1.column_name = table2.column_name; select * from table1 right join table2 on table1.column_name = table2.column_name;聚合函数 select COUNT(*) from table_name; select SUM(column_name) from table_name; select AVG(column_name) from table_name; select MAX(column_name) from table_name; select MIN(column_name) from table_name;分组select column_name, COUNT(*) from table_name group by column_name;创建索引create index index_name on table_name(column_name);删除索引drop index index_name on table_name;使用limit 进行分页select * from table_name limit start, count;显示服务器状态信息show status;查看Mysql提供存储引擎show engines;查看mysql默认存储引擎show variables like '%storage_engine%';查看mysql系统版本select version();查看mysql库里所有表show tables from mysql;查看Mysql端口show variables like 'port';查看mysql库user表中user,host信息select user,host from mysql.user;修改密码mysqladmin -uroot -p old_pwd password new_pwd use mysql; update mysql.user set password = 'nwe_pwd' where user = 'user_name'; flush privileges; set password for user_name@localhost=password('pwd'); flush privileges;MYSQL忘记密码方法，先停止Mysql服务，以跳过权限方式启动service mysqld stop /usr/local/mysql/bin/mysqld_safe –user=mysql –skip-grant-tables & #在shell终端输入mysql并按Enter键，进入mysql命令行，由于MYSQL用户及密码认证信息存放在mysql库中的user表，需进入mysql库 mysql; use mysql; update user set password=password('pwd') where user='user_name'； flush privileges;
