mysql 命令完全总结
Write By CS逍遥剑仙
我的主页: csxiaoyao.com
GitHub: github.com/csxiaoyaojianxian
Email: sunjianfeng@csxiaoyao.com
QQ: 1724338257
/*
精心整理关于 mysql 的命令
数据库环境配置见
mysql配置总结.md
常用SQL用法见文件
sql代码总结.md
*/
1. 连接mysql
格式: mysql -h主机地址 -u用户名 -p用户密码
1. 连接本机mysql
终端进入目录 mysql/bin
<code class="language-sql">mysql -u root -p</code>
注意:用户名前可有空格也可没有空格,密码前必须没有空格
2. 连接到远程主机mysql
<code class="language-sql">mysql -h 192.168.0.1 -u root -p19931128;</code>
3. 退出mysql
<code class="language-sql">exit</code>
2. 修改密码
格式:mysqladmin -u用户名 -p旧密码 password 新密码
<code class="language-sql">mysqladmin -u root password 931128 -- 初始化数据库root无密码 mysqladmin -u root -p931128 password 19931128</code>
3. 用户管理
3.1 新建用户
<code class="language-sql">mysql> insert into mysql.user(Host,User,Password) values('localhost','csxiaoyao',password('000000')); mysql> update mysql.user set password=password('19931128') where User="csxiaoyao" and Host="localhost";</code>
3.2 用户权限管理
命令:GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 用户名@登录主机 IDENTIFIED BY ‘密码’;
<code class="language-sql">mysql> grant all PRIVILEGES on *.* to root@'%' identified by '19931128'; mysql> grant all on mydb.* to csxiaoyao@localhost identified by '19931128'; mysql> grant select,update on mydb.* to csxiaoyao@localhost identified by '19931128'; mysql> flush privileges;</code>
注意:
-
select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, file共14个权限,可被all privileges或者all代替
-
数据库名称.表名称
代替为.
,表示赋予用户操作服务器上所有数据库所有表的权限 -
用户地址可以是
localhost
,也可以是ip地址、机器名、域名,'%'
表示从任何地址连接 -
‘连接口令’ 不能为空
3.3 删除用户
<code class="language-sql">mysql> DELETE FROM mysql.user WHERE User="csxiaoyao" and Host="localhost"; mysql> flush privileges;</code>
4. 数据库操作
4.1 连接数据库
命令: use <数据库名>;
使用USE语句为当前数据库做标记,不会影响访问其它数据库中的表
<code class="language-sql">mysql> USE db1; mysql> SELECT a_name,e_name FROM author,db2.editor WHERE author.editor_id = db2.editor.editor_id; </code>
4.2 显示数据库
命令:show databases;
<code class="language-sql">mysql> show databases;</code>
4.3 创建数据库
命令:create database <数据库名>;
<code class="language-sql">mysql> create database sunshine;</code>
4.4 SELECT操作
命令:select database();
mysql
中 SELECT
命令类似于其他编程语言的 print
或 write
,可用来显示字符串、数字、数学表达式的结果等
显示mysql的版本
<code class="language-sql">mysql> select version();</code>
显示当前时间
<code class="language-sql">mysql> select now();</code>
显示年月日
<code class="language-sql">mysql> SELECT YEAR(CURRENT_DATE); mysql> SELECT MONTH(CURRENT_DATE); mysql> SELECT DAYOFMONTH(CURRENT_DATE);</code>
显示字符串
<code class="language-sql">mysql> SELECT "sunshine";</code>
当计算器用
<code class="language-sql">mysql> select ((4 * 4) / 10 ) + 25; </code>
4.5 删除数据库
命令:drop database <数据库名>;
<code class="language-sql">mysql> drop database sunshine; mysql> drop database if exists sunshine;</code>
5. 表操作
5.1 创建数据表
字段名 | 数字类型 | 数据宽度 | 是否为空 | 是否主键 | 自动增加 | 默认值 |
---|---|---|---|---|---|---|
id | int | 4 | 否 | primary key | auto_increment | |
name | char | 20 | 否 | |||
sex | int | 4 | 否 | 0 | ||
address | varchar | 50 | 是 | 江苏 | ||
birthday | date | 是 | ||||
degree | double | 16, 2 | 是 |
<code class="language-sql">create table sunshine ( id int(4) auto_increment not null primary key, name char(20) not null, sex int(4) not null default 0, address varchar(50) default "江苏", birthday date, degree double(16,2) ); </code>
注:更多建表操作见附录
5.2 表字段操作
增加字段:
命令:alter table <表名> add <字段> <类型> <其他>;
ALTER TABLE table_name ADD field_name field_type;
<code class="language-sql">mysql> alter table sunshine add salary int(4) default 0</code>
修改原字段名称及类型:
命令:ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
删除字段:
<code class="language-sql">mysql> ALTER TABLE table_name DROP field_name;</code>
5.3 修改表名
命令:rename table <原表名> to <新表名>;
<code class="language-sql">mysql> rename table OldTable to NewTable;</code>
注意:不能有活动的事务或对锁定的表操作,须有对原表的 ALTER
和 DROP
权限,和对新表的 CREATE
和 INSERT
权限
5.4 删除数据表
命令:drop table <表名>;
<code class="language-sql">mysql> drop table sunshine; -- 普通删除 mysql> DROP TABLE IF EXISTS `sunshine`; -- 安全删除</code>
5.5 索引操作
加索引
命令:alter table <表名> add index <索引名 (字段名1[,字段名2 …])>;
<code class="language-sql">mysql> alter table sunshine add index name_index1(name);</code>
加主关键字索引
命令:alter table <表名> add primary key <(字段名)>;
<code class="language-sql">mysql> alter table sunshine add primary key(id);</code>
加唯一限制条件索引
命令:alter table <表名> add unique <索引名 (字段名)>;
<code class="language-sql">mysql> alter table sunshine add unique name_index2(cardnumber);</code>
删除索引
命令:alter table <表名> drop index <索引名>;
<code class="language-sql">mysql> alter table sunshine drop index name_index2;</code>
6. 表数据操作
6.1 表插入数据
命令:insert into <表名 [( <字段名1>[,..<字段名n > ])]> values <( 值1 )[, ( 值n )]>;
<code class="language-sql">mysql> insert into sunshine values(1,'Sun',99.99),(2,'Jian',98.99),(3,'Fent', 97.99);</code>
注意:insert into每次只能插入一条记录
6.2 查询表数据
查询所有行
命令:select <字段1,字段2,…> from < 表名 > where < 表达式 >;
<code class="language-sql">mysql> select * from sunshine;</code>
查询前n行数据 LIMIT
<code class="language-sql">mysql> select * from sunshine order by id limit 0,2;</code>
6.3 删除表数据
<code class="language-sql">mysql> DELETE FROM sunshine WHERE name='csxiaoyao';</code>
6.4 修改表数据
命令:update <表名> set <字段> = <新值,…> where <条件>
<code class="language-sql">mysql> update sunshine set name='csxiaoyao' where id=1;</code>
单表UPDATE
命令:UPDATE
[LOW_PRIORITY][IGNORE]
tbl_name SET col_name1=expr1[, col_name2=expr2 ...][WHERE where_definition] [ORDER BY …][LIMIT row_count]
多表UPDATE
命令:UPDATE
[LOW_PRIORITY][IGNORE]
table_references SET col_name1=expr1[, col_name2=expr2 ...][WHERE where_definition]
注意:如果指定ORDER BY子句,则按被指定顺序对行更新;LIMIT子句限制被更新行数
7. 导入导出数据库
7.1 导出整个数据库
格式:mysqldump -u用户名 -p密码 数据库名 > 导出文件名.sql
<code class="language-Sql">mysqldump -u root -p database_name > outfile_name.sql</code>
注意:命令行下执行,导出文件默认是存在 /usr/local/mysql/bin/
目录下
7.2 导出表
格式:mysqldump -u用户名 -p密码 数据库名 表名 > 导出文件名.sql
<code class="language-sql">mysqldump -u root -p database_name table_name > outfile_name.sql</code>
7.3 导出表结构
格式:mysqldump -u用户名 -p密码 -d 数据库名 > 导出文件名.sql
<code class="language-sql">mysqldump -u root -p -d –add-drop-table database_name > outfile_name.sql mysqldump -u root -p -d database_name > outfile_name.sql</code>
注意:-d
如果没有数据,–add-drop-table
在每个create语句前增加一个drop table
7.4 带语言参数导出
<code class="language-sql">mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt database_name > outfile_name.sql</code>
7.5 导入sql文件
方法1
格式:mysql -u用户名 -p密码 数据库名 < 导入文件名.sql
<code class="language-sql">mysql -uroot -p < /Users/sunshine/database.sql;</code>
方法2
<code class="language-sql">mysql> set names utf8; # 设置数据库编码 mysql> source /Users/sunshine/database.sql;</code>
8. 其他问题
8.1 符号 ` 的使用
使用 "`" 避免和 mysql 关键字冲突,通常用来指明内容为数据库名、表名、字段名
8.2 自增
mysql
使用 auto_increment
,sqlserver
使用 identity(1,1)
9. 常用SQL
9.1 串接字符串 CONCAT 与 AS
<code class="language-sql">mysql> select CONCAT(name, " ", sex) > AS inf > from user > where degree > 0; </code>
9.2 PRIMARY KEY
<code>DROP TABLE IF EXISTS `sun`; CREATE TABLE `sun` ( `sn` int(4) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `birthday` date DEFAULT NULL, `degree` double(16,2) DEFAULT NULL, PRIMARY KEY (`sn`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;</code>