[root@MySQL ~]# mysql -uroot -p123456Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.32 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.mysql>```bash### 单实例远程登录**语法格式：** mysql –u[user] –p[password] –h[hostname/ip]```bash[root@MySQL-Client ~]# mysql -uroot -p123456 -h10.10.10.100[root@MySQL-Client ~]# mysql -uroot -p123456 -hmysql --也可以指定 hostname 方式链接Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.5.32 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.mysql>
多实例登录
语法格式：
mysql –u[user] –p[passwod] –S [sock path]
1234567891011121314
[root@MySQL ~]# mysql -uroot -p3306 -S /data/3306/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5Server version: 5.5.32-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.mysql>
远程登录多实例
语法格式：
mysql –u[user] –p[passwod] –S [sock path] –P[port]
123456789101112131415
[root@MySQL ~]# mysql -uroot -p123456 -h10.10.10.10 -P3306[root@MySQL ~]# mysql -uroot -p123456 -hMySQL -P3306 --也可以指定 hostname 方式链接Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 10Server version: 5.5.32-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.mysql>
设置及更改 MySQ root 密码
没有密码设置密码
语法格式：
mysqladmin –u[user] password ‘[password]’
12345678910111213141516171819
[root@MySQL ~]# /etc/init.d/mysqld startStarting MySQL.. SUCCESS! [root@MySQL ~]# mysql -uroot -p123456ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@MySQL ~]# mysqladmin -uroot password '123456'[root@MySQL ~]# mysql -uroot -p123456Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.5.32 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.mysql>
[root@MySQL ~]# /data/3307/mysql startStarting MySQL...[root@MySQL ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)[root@MySQL ~]# mysqladmin -uroot password '123456' -S /data/3307/mysql.sock [root@MySQL ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.mysql>
create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; #创建 GBK 字符集数据库：create database oldboy_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci; #创建 UTF8 数据库
删除库及表
删除库
语法格式
drop databases [db_name]
12345678910111213
mysql> show databases like 'db';+---------------+| Database (db) |+---------------+| db |+---------------+1 row inset (0.00 sec)mysql> drop database db;Query OK, 0 rows affected (0.00 sec)mysql> show databases like 'db';Empty set (0.00 sec)
创建与删除用户
创建用户
语法格式
create user ‘[user]’@’[hostname]’ IDENTIFIED BY ‘[password]’;
123456789101112131415161718192021222324
mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | 127.0.0.1 || root | localhost |+------+-----------+2 rows inset (0.00 sec)mysql> create user 'db'@'localhost' identified by '123456';Query OK, 0 rows affected (0.03 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | 127.0.0.1 || db | localhost || root | localhost |+------+-----------+3 rows inset (0.00 sec)
删除用户
语法格式
drop user ‘[user]’@’[hostame]’
123456789101112131415161718192021
mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | 127.0.0.1 || db | localhost || root | localhost |+------+-----------+3 rows inset (0.00 sec)mysql> drop user 'db'@'localhost';Query OK, 0 rows affected (0.00 sec)mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | 127.0.0.1 || root | localhost |+------+-----------+2 rows inset (0.00 sec)
SQL 语句删除用户
如 drop 删除不了（一般是特殊字符或大写），可以用下面的方式删除
语法格式：
delete from mysql.usr where user = ‘[user]’ and host = ‘[hostname]’
mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | MySQL || root | localhost |+------+-----------+4 rows inset (0.00 sec)mysql> drop user 'root'@'MySQL';Query OK, 0 rows affected (0.00 sec)mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | MySQL || root | localhost |+------+-----------+4 rows inset (0.00 sec)mysql> delete from mysql.user where user = 'root' and host = 'MySQL';Query OK, 1 row affected (0.00 sec)mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | localhost |+------+-----------+3 rows inset (0.00 sec)
数据库授权
查看用户被赋予的权限
语法格式
show grats for ‘[user]’@’[hostname]’;
12345678
mysql> show grants for'db'@'localhost';+-----------------------------------------------------------------------------------------------------------+| Grants for db@localhost |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'db'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT ALL PRIVILEGES ON `db`.* TO 'db'@'localhost' WITH GRANT OPTION |+-----------------------------------------------------------------------------------------------------------+2 rows inset (0.00 sec)
以不同的方式显示
123456
mysql> show grants for'db'@'localhost'\G;*************************** 1. row ***************************Grants for db@localhost: GRANT USAGE ON *.* TO 'db'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'*************************** 2. row ***************************Grants for db@localhost: GRANT ALL PRIVILEGES ON `db`.* TO 'db'@'localhost' WITH GRANT OPTION2 rows inset (0.00 sec)
添加用户授权
grant
all
on dbname.*
to username@localhost
identified by ‘passwd’
授权命令
对应权限
目标：库和表
用户名和客户端主机
用户密码
语法格式
GRANT ALL ON db1.* TO ‘jeffrey’@’localhost’;
123456789101112131415161718
mysql> show grants for'db'@'localhost';+-----------------------------------------------------------------------------------------------------------+| Grants for db@localhost |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'db'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+-----------------------------------------------------------------------------------------------------------+1 row inset (0.00 sec)mysql> grant select on db.* to 'db'@'localhost';Query OK, 0 rows affected (0.00 sec)mysql> show grants for'db'@'localhost';+-----------------------------------------------------------------------------------------------------------+| Grants for db@localhost |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'db'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT SELECT ON `db`.* TO 'db'@'localhost' |+-----------------------------------------------------------------------------------------------------------+2 rows inset (0.00 sec)
移除用户权限
语法格式
revoke select on [db_name].[table_name] from ‘[user]’@’[hostname]’;
12345678910111213141516171819
mysql> show grants for'db'@'localhost';+-----------------------------------------------------------------------------------------------------------+| Grants for db@localhost |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'db'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' || GRANT SELECT ON `db`.* TO 'db'@'localhost' |+-----------------------------------------------------------------------------------------------------------+2 rows inset (0.00 sec)mysql> revoke select on db.* from 'db'@'localhost';Query OK, 0 rows affected (0.00 sec)mysql> show grants for'db'@'localhost';+-----------------------------------------------------------------------------------------------------------+| Grants for db@localhost |+-----------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'db'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+-----------------------------------------------------------------------------------------------------------+1 row inset (0.00 sec)
创建一个和 root 等价的管理员授权方法
12
grant all privileges on *.* to system@'localhost' identified by 'oldboy123' with grant option;flush privileges;
企业生产环境是如何授权用户权限？
博客，CMS 等产品如何授权？
对于 WEB 连接也难怪乎授权尽量采用最小化原则，很多开源软件都是 WEB 界面安装，在安装期间除了 select,insert,update,delete4 个权限外，还需要 create,drop 等比较危险的权限
1
mysql> grant select,insert,update,delete,create,drop on blog.* to 'blog'@'localhost' identified by '123456';
CREATE TABLE `test` ( `id` int(4) NOT NULL, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 #创建时指定字符集
索引类似文件系统的 inode,记录类似文件系统的 block,
索引要创建再 where 条件列上，而非要查询的内容列，才能加快查询速度
主键索引：类似身份证 唯一标示表内的一条记录
12345678
create table test(id int(4) not null AUTO_INCREMENT,name char(20) not null,age tinyint(2) NOT NULL default '0',dept varchar(16) default NULL,primary key(id), #主键索引，记录唯一KEY index_name (name) #普通索引，记录可能不唯一);
删除表
语法格式
drop tables [table_name]
12345678910111213
mysql> show tables from db;+--------------+| Tables_in_db |+--------------+| test |+--------------+1 row inset (0.00 sec)mysql> drop table test;Query OK, 0 rows affected (0.01 sec)mysql> show tables from db;Empty set (0.00 sec)
查看表的内容
命令语法：
select <字段 1，字段 2…> from <表名> where <表达式>
其中 select fom where 是不能随便改的
1
mysql> select * from oldboy.test;
条件查询
1
mysql> select id,name from test whee name="ansheng";
多个条件查询
1
mysql> select id,name from testwhere name='ansheng' or id=5;
mysql> select id,name from test order by id asc;+----+---------+| id | name |+----+---------+| 1 | oldboy || 2 | oldgirl || 3 | inca || 4 | zuma || 5 | kaka |+----+---------+5 rows inset (0.00 sec)mysql> select id,name from test order by id desc;+----+---------+| id | name |+----+---------+| 5 | kaka || 4 | zuma || 3 | inca || 2 | oldgirl || 1 | oldboy |+----+---------+5 rows inset (0.00 sec)mysql> select id,name from testwhere id >2 order by id desc; +----+------+| id | name |+----+------+| 5 | kaka || 4 | zuma || 3 | inca |+----+------+3 rows inset (0.00 sec)
多表查询
1
mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno order by student.Sname;
查看表建立结构
12
mysql> desc student;mysql> show columns from student;
查看建立的表语句（可以看索引及创建表的相关信息）
12345678910
mysql> show create table db.test\G*************************** 1. row *************************** Table: testCreate Table: CREATE TABLE `test` ( `id` int(4) NOT NULL, `name` char(20) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', `dept` varchar(16) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row inset (0.00 sec)
更改表名称
语法格式
rename table tb_name to new_tb_name
12345678910111213141516171819
mysql> use dbDatabase changedmysql> show tables;+--------------+| Tables_in_db |+--------------+| test |+--------------+1 row inset (0.00 sec)mysql> rename table test to linux;Query OK, 0 rows affected (0.08 sec)mysql> show tables;+--------------+| Tables_in_db |+--------------+| linux |+--------------+1 row inset (0.00 sec)
第二种改名方法
1
mysql> alter table kkk rename to test;
往表里插入数据
按规矩指定所有列名，并且每列都插入值
语法格式
insert into tb_name(col_name) values(‘character’);
12345678910
mysql> insert into test(id,name) values(1,'natasha');Query OK, 1 row affected (0.00 sec)mysql> select * from test;+----+---------+-----+------+| id | name | age | dept |+----+---------+-----+------+| 1 | natasha | 0 | NULL |+----+---------+-----+------+1 row inset (0.00 sec)
由于 ID 列为自增的，所以，可以只在 name 列插入值
1234567891011
mysql> insert into test(name) values('harry');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> select * from test;+----+---------+-----+------+| id | name | age | dept |+----+---------+-----+------+| 1 | natasha | 0 | NULL || 0 | harry | 0 | NULL |+----+---------+-----+------+2 rows inset (0.00 sec)
如果不指定列，就要按规矩为每列都插入恰当的值
1
insert into test values(3,’inca’)
批量插入数据方法
12
insert into test values(4,'zuma'),(5,'kaka');insert into `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');
删除表中的数据
清空表内容
12
mysql> truncate table test;mysql> delete from test;
