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 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
-
創建表：建立一個名為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'
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/76852.html
標籤：MySQL
上一篇：MYSQL陳述句中的增刪改查
下一篇：MySQL 的基本架構示意圖