MySQL 使用
安装完成后运行MySQL command line client.
// 输入安装时设置的密码
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 6 to server version: 5.0.27-community-nt
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
mysql>
此时说明成功登录mysql(注意,现在还没有连接到数据库,所以不能创建表、对表操作等等) * 现在能干什么呢?
1.察看MySQL的数据库信息
mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
4 rows in set (0.02 sec)
显示了MySQL中现有的数据库(information_schema/mysql是系统自身配置信息的数据库,test是一个初始为空的测试数据库)。
2.看看怎样连接数据库
mysql> connect mysql
Connection id: 12
Current database: mysql
这样就连接到了mysql数据库中,在这里如果对数据库中的表感兴趣,但是又不知道有哪些表,怎么办?
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.02 sec)
知道了数据库中有哪些表,要知道每个表有什么:
mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
20 rows in set (0.01 sec)
这样我们在想看看数据库中有什么东西就不难了。
好了,如果想要连接到其他数据库怎么办呢?采用同样的连接数据库的方法:
mysql> connect test;
Connection id: 15
Current database: test
mysql> show tables;
Empty set (0.01 sec)
嗯,不错。又到了另一个数据库了,可是当在数据库中转来转去后,自己也忘了在哪了,怎么办?
别急:
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
OK,搞定。
3.现在来看看怎么创建数据库
mysql> create database test1;
Query OK, 1 row affected (0.02 sec)
在连接到一个数据后也可以创建新的数据库,注意不要用已存在的数据库名字。
注意,创建后一定要连接到创建的数据库才能对新数据库操作。
mysql> connect test1;
Connection id: 18
Current database: test1
好了,现在可以创建我们所需要的表了,下面以一个简单的用户表,包括用户名和密码作为例子:
mysql> CREATE TABLE user(name VARCHAR(20) NOT NULL, password VARCHAR(20));
Query OK, 0 rows affected (0.06 sec)
下面介绍几种语句:
创建索引:
CREATE INDEX index_name ON table_name (col_name [(length)], ... )
例:mysql>CREATE INDEX index_name ON user (name);
执行查询:
SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY][DISTINCT | DISTINCTROW | ALL]
select_expression,... [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ]
例:SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name;
SELECT college, region, seed FROM tournament ORDER BY region, seed;
SELECT col_name FROM tbl_name WHERE col_name > 0;
改变表结构:
ALTER TABLE table_name ALTER_SPEC [, ALTER_SPEC ... ]
例:ALTER TABLE user ADD COLUMN userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT;
插入数据:
INSERT [INTO] table_name [(column(s))] VALUES(expression(s))
例:INSERT INTO user(naem, password) VALUES('111', '1-1-1');
更新数据:
例:UPDATE user SET password='2-2-2' WHERE name='111';
UPDATE user SET name='222', password='3-3-3' WHERE name='111';
删除数据:
例:DELETE FROM user WHERE name='111';
数据库授权:
GRANT SELECT, INSERT, DELETE, DROP ON *.*(或test.*/user.*/..) TO 用户名@localhost IDENTIFIED BY '密码';
OK,就先讲这么多了。
退出MySQL
mysql>exit
或
mysql>quit