1、連接mysql
-
連接本機
-
連接遠程主機
mysql -h 192.168.1.% -u root -p
-
退出
2、修改密碼
格式：alter user 用戶名@主機地址 identified by '新密碼';
-
將root密碼改為newroot
alter user root@local identified by 'newroot';
-
查看用戶主機地址方法
use mysql;
select user,host from user;
3、管理用戶
可以管理mysql數(shù)據(jù)庫中的user表來管理用戶。
對于用戶方面的管理，最好對用戶授予不同的權(quán)限來管理用戶。
增加tom用戶，密碼為tom，可在任何主機登錄：
create user 'tom'@localhost identified by 'tom';
create user 'tom1'@'192.168.1.%' identified by 'tom1';
4、管理數(shù)據(jù)庫
-
顯示數(shù)據(jù)庫
注：數(shù)據(jù)庫亂碼問題
-
修改/etc/my.cnf配置文件:character-set-server=utf8
-
Java連接mysql的配置文件中：
jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8;
-
創(chuàng)建數(shù)據(jù)庫
-
刪除數(shù)據(jù)庫
drop database if exists db_name;
-
使用數(shù)據(jù)庫
mysql> use db_name;
Database changed
-
當前選擇的數(shù)據(jù)庫
MySQL中select命令類似于其他編程語言里的print或者write，你可以用它來顯示一個字符串、數(shù)字、數(shù)學表達式的結(jié)果等等。部分select命令如下：
select version(); // 顯示mysql版本
select now(); // 顯示當前時間
select current_date; // 顯示年月日
select ((4 * 7) / 10 ) + 23; // 計算
5、管理表
-
顯示所有表
-
查看表結(jié)構(gòu)
（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 orders\G;
*************************** 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 orders\G;
*************************** 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
-
創(chuàng)建表：建立一個名為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)
-
向表中插入數(shù)據(jù)：
格式：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)
-
查詢表數(shù)據(jù)
格式： 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)
-
刪除表數(shù)據(jù)
格式：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)
-
修改表數(shù)據(jù)
格式：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子句，則按照被指定的順序?qū)π羞M行更新；
-
LIMIT子句用于給定一個限值，限制可以被更新的行的數(shù)目。
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] 指定位置關(guān)系，F(xiàn)IRST表示在第一列，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 ...];
-
修改字段
（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;
（2）添加唯一約束
格式：ALTER TABLE tb_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...);
-
[INDEX|KEY] 說明是 INDEX 還是 KEY，關(guān)于INDEX 和 KEY 的區(qū)別參考：Mysql中的key和index的區(qū)別
-
[index_name] 索引名稱，好像 和[CONSTRAINT [symbol]] 沒有區(qū)別；
-
[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'
|
