Mysql 基础操作 DDL DML DCL

mysql 基础操作  包括DDL DML DCL示例

c:\Program Files\MySQL\MySQL Server 5.1>cd bin 
c:\Program Files\MySQL\MySQL Server 5.1\bin> mysql -u root -pmysql  --登陆
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.62-community MySQL Community Server (GPL)


Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> show databases;  --显示所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| yws                |
+--------------------+
4 rows in set (0.07 sec)


mysql> use yws
Database changed
mysql> show tables \g
+---------------+
| Tables_in_yws |
+---------------+
| userinfo      |
| zzm           |
+---------------+
2 rows in set (0.04 sec)


mysql> use mysql  --选择某个数据库
Database changed
mysql> show tables \g 查看本数据库下所有表
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.29 sec)


mysql>
mysql> use yws \g
Query OK, 0 rows affected (0.00 sec)



mysql> create table emp(ename varchar(10) ,hiredate date,sal decimal(10,2),deptn o int(2)); --创建表
Query OK, 0 rows affected (0.16 sec)


mysql> desc emp; --描述表结构
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(10)   | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.02 sec)



mysql> show create table emp \G;  --查看创建表SQL 利用\G选项更加美观
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


ERROR:
No query specified


mysql> alter table emp modify ename varchar(20); --修改字段类型
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table emp add column age int(3); --添加新列
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table emp drop column age; --删除列
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0



mysql> alter table emp add column age int(4);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table emp change age age1 int(4); --修改列名称和列类型
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

note:change和modify都可以修改，不同的是 change需要写两次列名称，但是可以修改列的名称，modify只可以修改类型。

mysql> alter table emp add birth date after ename; --修改列字段顺序
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0
note：mysql独特的功能，可以调整表字段顺序。
mysql>


mysql> alter table emp modify age1 int(3) first; --修改列字段顺序
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| age1     | int(3)        | YES  |     | NULL    |       |
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)


mysql>
mysql> alter table emp rename y_emp; --修改表名称
Query OK, 0 rows affected (0.06 sec)




mysql> alter table y_emp modify age1 int(3) after deptno;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc y_emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age1     | int(3)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)


mysql> alter table y_emp change age1 age int(4);
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> desc y_emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename    | varchar(20)   | YES  |     | NULL    |       |
| birth    | date          | YES  |     | NULL    |       |
| hiredate | date          | YES  |     | NULL    |       |
| sal      | decimal(10,2) | YES  |     | NULL    |       |
| deptno   | int(2)        | YES  |     | NULL    |       |
| age      | int(4)        | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)


mysql> alter table y_emp drop column birth;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table y_emp drop column age;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql>


mysql> insert into y_emp values ('yws','2015-03-16',5000,2);  --插入数据
Query OK, 1 row affected (0.07 sec)


mysql> insert into y_emp values ('yws','2015-03-16',5000,2),('xy','2015-03-02',3 000,2); --可以插入多列数据
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select count(*) from y_emp;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.03 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

limit的简单用法
limit （start,start_offerset）
mysql>
mysql> select * from y_emp limit 1; 
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| yws   | 2015-03-16 | 5000.00 |      2 |
+-------+------------+---------+--------+
1 row in set (0.00 sec)


mysql> select * from y_emp limit 1,2; 
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| yws   | 2015-03-16 | 5000.00 |      2 |
| xy    | 2015-03-02 | 3000.00 |      2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)


mysql> select * from y_emp limit 3;;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| yws   | 2015-03-16 | 5000.00 |      2 |
| yws   | 2015-03-16 | 5000.00 |      2 |
| xy    | 2015-03-02 | 3000.00 |      2 |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)


ERROR:
No query specified


mysql>


mysql> select * from y_emp order by sal limit 2; --配合order by 实现排序和分页
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| xy    | 2015-03-02 | 3000.00 |      2 |
| yws   | 2015-03-16 | 5000.00 |      2 |
+-------+------------+---------+--------+
2 rows in set (0.03 sec)


mysql> select * from y_emp order by sal desc limit 2;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| yws   | 2015-03-16 | 5000.00 |      2 |
| yws   | 2015-03-16 | 5000.00 |      2 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)


mysql>


mysql> create table dept (deptno int,ename varchar(20));
Query OK, 0 rows affected (0.10 sec)



mysql> insert into dept select deptno,ename from y_emp; --复制记录
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

union all和 union 写法
mysql> select deptno from y_emp
    -> union all
    -> select deptno from dept;
+--------+
| deptno |
+--------+
|      2 |
|      2 |
|      2 |
|      2 |
|      2 |
|      2 |
+--------+
6 rows in set (0.02 sec)


mysql> select deptno from y_emp
    -> union
    -> select deptno from dept;
+--------+
| deptno |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

DCL授权
mysql>
mysql> grant select,insert on yws.* to 'scott'@'localhost' identified by '123';  --创建用户并授权
Query OK, 0 rows affected (0.04 sec)


mysql帮助文档可通过 help ?command方式 
?show
mysql> ? data types;
You asked for help about help category: "Data Types"
For more information, type 'help ', where is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29477587/viewspace-1457876/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29477587/viewspace-1457876/