1、连接mysql
-
连接本机
mysql -u root -p -
连接远程主机
mysql -h 192.168.1.% -u root -p -
退出
exit;
2、修改密码
格式：alter user 用户名@主机地址 identified by "新密码";
-
将root密码改为newroot
alter user root@local identified by "newroot"; -
查看用户主机地址方法
use mysql; select user,host from user;
3、管理用户
可以管理mysql数据库中的user表来管理用户。
对于用户方面的管理，最好对用户授予不同的权限来管理用户。
增加tom用户，密码为tom，可在任何主机登录：
create user "tom"@localhost identified by "tom";
create user "tom1"@"192.168.1.%" identified by "tom1";4、管理数据库
-
显示数据库
show databases;注：数据库乱码问题
-
修改/etc/my.cnf配置文件:character-set-server=utf8
-
Java连接mysql的配置文件中：
jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8;
-
-
创建数据库
create database db_name; -
删除数据库
drop database if exists db_name; -
使用数据库
mysql> use db_name; Database changed -
当前选择的数据库
select database();MySQL中select命令类似于其他编程语言里的print或者write，你可以用它来显示一个字符串、数字、数学表达式的结果等等。部分select命令如下：
select version(); // 显示mysql版本 select now(); // 显示当前时间 select current_date; // 显示年月日 select ((4 * 7) / 10 ) + 23; // 计算
5、管理表
-
显示所有表
show tables; -
查看表结构
（1）方式一：
mysql> desc orders; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | order_num | int(11) | NO | PRI | NULL | auto_increment | | order_date | datetime | NO | | NULL | | | cust_id | int(11) | NO | MUL | NULL | | +------------+----------+------+-----+---------+----------------+ 3 rows in set (0.24 sec)（2）方式二：
mysql> show columns from orders; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | order_num | int(11) | NO | PRI | NULL | auto_increment | | order_date | datetime | NO | | NULL | | | cust_id | int(11) | NO | MUL | NULL | | +------------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)（3）方式三：
mysql> show create table ordersG; *************************** 1. row *************************** Table: orders Create Table: CREATE TABLE `orders` ( `order_num` int(11) NOT NULL AUTO_INCREMENT, `order_date` datetime NOT NULL, `cust_id` int(11) NOT NULL, PRIMARY KEY (`order_num`), KEY `fk_orders_customers` (`cust_id`), CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`) ) ENGINE=InnoDB AUTO_INCREMENT=20011 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified（4）方式四：
mysql> show full fields from orders; +------------+----------+-----------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+----------+-----------+------+-----+---------+----------------+---------------------------------+---------+ | order_num | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | order_date | datetime | NULL | NO | | NULL | | select,insert,update,references | | | cust_id | int(11) | NULL | NO | MUL | NULL | | select,insert,update,references | | +------------+----------+-----------+------+-----+---------+----------------+---------------------------------+---------+ 3 rows in set (0.00 sec)（5）方式五：
mysql> show fields from orders; +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+----------+------+-----+---------+----------------+ | order_num | int(11) | NO | PRI | NULL | auto_increment | | order_date | datetime | NO | | NULL | | | cust_id | int(11) | NO | MUL | NULL | | +------------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)（6）方式六：查看表中某个字段
mysql> desc orders order_num; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | order_num | int(11) | NO | PRI | NULL | auto_increment | +-----------+---------+------+-----+---------+----------------+ 1 row in set (0.00 sec)（7）方式七：查看表中索引
mysql> show index from ordersG; *************************** 1. row *************************** Table: orders Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: order_num Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: orders Non_unique: 1 Key_name: fk_orders_customers Seq_in_index: 1 Column_name: cust_id Collation: A Cardinality: 4 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec) ERROR: No query specified -
创建表：建立一个名为test的表
mysql> create table test( -> id INT(11) NOT NULL AUTO_INCREMENT, -> name VARCHAR(255) NOT NULL, -> PRIMARY KEY (id) -> ); Query OK, 0 rows affected (0.44 sec) -
删除表
mysql> drop table if exists test; Query OK, 0 rows affected (0.20 sec) -
向表中插入数据：
格式：insert into 表名 ( 字段名1,···, 字段名n ) values ( 值1, ···, 值n );
mysql> insert into test (name) values ("zhangsan"); Query OK, 1 row affected (0.14 sec) mysql> select * from test; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | +----+----------+ 1 row in set (0.02 sec)插入多条记录：
mysql> insert into test (name) values ("lisi"),("xiaoming"); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | xiaoming | +----+----------+ 3 rows in set (0.00 sec) -
查询表数据
格式： select 字段1, ···, 字段n from 表名 where 表达式
（1）查询表所有：
mysql> select * from test; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | | 3 | xiaoming | +----+----------+ 3 rows in set (0.00 sec)（2）查询前两行：
mysql> select * from test limit 2; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 2 | lisi | +----+----------+ 2 rows in set (0.03 sec) -
删除表数据
格式：delete from 表名 where 表达式
mysql> delete from test where id = 2; Query OK, 1 row affected (0.13 sec) mysql> select * from test; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 3 | xiaoming | +----+----------+ 2 rows in set (0.00 sec) -
修改表数据
格式：UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
-
UPDATE语法可以用新值更新原有表行中的各列；
-
SET子句指示要修改哪些列和要给予哪些值；
-
WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行；
-
如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新；
-
LIMIT子句用于给定一个限值，限制可以被更新的行的数目。
mysql> select * from test; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 3 | xiaoming | | 4 | lisi | +----+----------+ 3 rows in set (0.00 sec) mysql> update test set name = "xiaohong" where id = 3; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | | 3 | xiaohong | | 4 | lisi | +----+----------+ 3 rows in set (0.00 sec) -
-
增加字段
格式：ALTER TABLE tb_name ADD col_name1 column_definition [FIRST | AFTER col_name]; [FIRST | AFTER col_name] 指定位置关系，FIRST表示在第一列，AFTER col_name表示在 col_name 列之后；
mysql> alter table test add column( -> phone INT(11) NULL, -> addr VARCHAR(50) -> ); Query OK, 0 rows affected (0.32 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | NO | | NULL | | | phone | int(11) | YES | | NULL | | | addr | varchar(50) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) -
删除列
格式：ALTER TABLE tb_name DROP
[COLUMN]col_name1[, DROP col_name2 ...];-
[COLUMN]关键字可有可无； -
删除多列时需使用
DROP关键字，不可直接用 , 分隔；
-
-
修改字段
（1）修改列
ALTER TABLE tb_name CHANGE [COLUMN] old_col_name new_col_name column_definition #注意一定要指定类型 [FIRST|AFTER col_name];（2）修改列类型
ALTER TABLE tb_name MODIFY col_name column_definition; -
添加约束
（1）添加主键约束
格式：ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] PRIMARY KEY index_type;
-
[CONSTRAINT [symbol]] constraint 关键字，symbol 表示约束别名，可有可无，mysql会自动创建；
-
[index_type] 索引类型 包含 {B+TREE | HASH}，存储引擎为InnoDB时只能使用B+TREE，默认值为B+TREE，但是InnoDB可以有自适应hash索引、即索引中的索引；
（2）添加唯一约束
格式：ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...);
-
[INDEX|KEY] 说明是 INDEX 还是 KEY，关于INDEX 和 KEY 的区别参考：Mysql中的key和index的区别
-
[index_name] 索引名称，好像 和[CONSTRAINT [symbol]] 没有区别；
-
[index_type] 索引类型， 包含 {BTREE | HASH}
（3）删除约束
格式：alter table tb_name drop key index_name;
-
-
添加索引
（1）加索引
格式：
#普通索引 ALTER TABLE tb_name ADD {INDEX|KEY} [index_name](key_part,...) [index_option] ... #全文索引 ALTER TABLE tbl_name ADD FULLTEXT [INDEX|KEY] [index_name](key_part,...) [index_option] ... #空间索引 ALTER TABLE tbl_name ADD SPATIAL [INDEX|KEY] [index_name](key_part,...) [index_option] ... key_part: col_name [(length)] [ASC|DESC] index_type: USING {BTREE|HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT "string"
