mysql创建表步骤dml_数据库ddl及dml的基本操作

mysql> create database zone;   创建zone数据库Query OK, 1 row affected (0.00 sec)

mysql> show databases;   查看数据库+--------------------+    | Database           |    +--------------------+    | information_schema |    | dingchao           |    | momo               |    | mysql              |    | test               |    | wpdb               |    | zone               |    +--------------------+

mysql> drop database zone;   删除数据库Query OK, 1 row affected (0.00 sec)

mysql> show databases;   查看数据库+--------------------+    | Database           |    +--------------------+    | information_schema |    | dingchao           |    | momo               |    | mysql              |    | test               |    | wpdb               |    +--------------------+    6 rows in set (0.00 sec)

mysql> use dingchao;   使用数据库Database changed

mysql> create table boss ( ID int unsigned not null unique key auto_increment,

Name char(10) not null, Age tinyint, Gender enum('N','M') not null, Gongzi char(15));   Query OK, 0 rows affected (0.08 sec)   创建表

mysql> show tables;   查看表+--------------------+    | Tables_in_dingchao |    +--------------------+    | Class              |    | boss               |    | student            |    +--------------------+    3 rows in set (0.01 sec)

mysql> desc boss;   查看表的结构+--------+------------------+------+-----+---------+----------------+    | Field  | Type             | Null | Key | Default | Extra          |    +--------+------------------+------+-----+---------+----------------+    | ID     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |    | Name   | char(10)         | NO   |     | NULL    |                |    | Age    | tinyint(4)       | YES  |     | NULL    |                |    | Gender | enum('N','M')    | NO   |     | NULL    |                |    | GongZI | char(15)         | YES  |     | NULL    |                |    +--------+------------------+------+-----+---------+----------------+    5 rows in set (0.00 sec)

mysql> insert into boss (Name,Age,Gender,GongZI) VALUE ('sloary','22','N','4400'),

('mara','35','M','3600'),('jifu','77','N','5800');   插入数据Query OK, 3 rows affected (0.00 sec)

mysql> select * from boss;   +----+--------+------+--------+--------+    | ID | Name   | Age  | Gender | GongZI |    +----+--------+------+--------+--------+    |  1 | sloary |   22 | N      | 4400   |    |  2 | mara   |   35 | M      | 3600   |    |  3 | jifu   |   77 | N      | 5800   |    |  4 | lili   |   22 | N      | 3800   |    |  5 | boy    |   35 | M      | 2600   |    |  6 | tom    |   77 | M      | 3000   |    |  7 | mary   |   18 | N      | 6000   |    |  8 | king   |   28 | M      | 4600   |    |  9 | hellen |   30 | M      | 3000   |    +----+--------+------+--------+--------+    9 rows in set (0.00 sec)

mysql> select * from boss where Age > 22 ;  选择行    查询年龄大于22岁的人+----+--------+------+--------+--------+    | ID | Name   | Age  | Gender | GongZI |    +----+--------+------+--------+--------+    |  2 | mara   |   35 | M      | 3600   |    |  3 | jifu   |   77 | N      | 5800   |    |  5 | boy    |   35 | M      | 2600   |    |  6 | tom    |   77 | M      | 3000   |    |  8 | king   |   28 | M      | 4600   |    |  9 | hellen |   30 | M      | 3000   |    +----+--------+------+--------+--------+    6 rows in set (0.00 sec)

mysql> select Name,Age,GongZI from boss where Age > 30;   查询年龄大于30岁人的工资

投影列 选择行 ，记住前面多个字段要加 “，”   +------+------+--------+    | Name | Age  | GongZI |    +------+------+--------+    | mara |   35 | 3600   |    | jifu |   77 | 5800   |    | boy  |   35 | 2600   |    | tom  |   77 | 3000   |    +------+------+--------+    4 rows in set (0.00 sec)

INSERT INTO box (Name,Age,Gender,GongZI) VALUE ('sloary','22','N','4400'),

('mara','35','M','3600'),('jifu','77','N','5800');   插入数据

mysql> show databases;   +--------------------+    | Database           |    +--------------------+    | information_schema |    | dingchao           |    | momo               |    | mysql              |    | test               |    | wpdb               |    +--------------------+

mysql> select Name,Age,GongZI from boss where Age > 30 order by Age,GongZI; 升序   +------+------+--------+    | Name | Age  | GongZI |    +------+------+--------+    | boy  |   35 | 2600   |    | mara |   35 | 3600   |    | tom  |   77 | 3000   |    | jifu |   77 | 5800   |    +------+------+--------+    4 rows in set (0.00 sec)

mysql> select Name,Age,GongZI from boss where Age > 30 order by Age,GongZI desc; 降序   +------+------+--------+    | Name | Age  | GongZI |    +------+------+--------+    | mara |   35 | 3600   |    | boy  |   35 | 2600   |    | jifu |   77 | 5800   |    | tom  |   77 | 3000   |    +------+------+--------+    4 rows in set (0.00 sec)

mysql> show tables;     查看表+--------------------+    | Tables_in_dingchao |    +--------------------+    | Class              |    | boess              |    | boss               |    | student            |    +--------------------+    4 rows in set (0.01 sec)

mysql> delete from boess;   这个命令慎用  一删除整张表都被删除啦   Query OK, 0 rows affected (0.00 sec)

mysql> delete from boss where Age =22 ;   Query OK, 2 rows affected (0.00 sec)  一般跟where 条件使用

mysql> select * from boss;   +----+--------+------+--------+--------+    | ID | Name   | Age  | Gender | GongZI |    +----+--------+------+--------+--------+    |  2 | mara   |   35 | M      | 3600   |    |  3 | jini   |   77 | N      | 5800   |    |  5 | boy    |   35 | M      | 2600   |    |  6 | tom    |   77 | M      | 3000   |    |  7 | mary   |   18 | N      | 6000   |    |  8 | king   |   28 | M      | 4600   |    |  9 | hellen |   30 | M      | 3000   |    +----+--------+------+--------+--------+    7 rows in set (0.00 sec)

mysql> update boss set Name= 'jini' where GongZI = '3000';   更新数据Query OK, 2 rows affected (0.00 sec)    Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from boss;   +----+------+------+--------+--------+    | ID | Name | Age  | Gender | GongZI |    +----+------+------+--------+--------+    |  2 | mara |   35 | M      | 3600   |    |  3 | jini |   77 | N      | 5800   |    |  5 | boy  |   35 | M      | 2600   |    |  6 | jini |   77 | M      | 3000   |    |  7 | mary |   18 | N      | 6000   |    |  8 | king |   28 | M      | 4600   |    |  9 | jini |   30 | M      | 3000   |    +----+------+------+--------+--------+    7 rows in set (0.00 sec)

mysql> select * from boss;   +----+------+------+--------+--------+    | ID | Name | Age  | Gender | GongZI |    +----+------+------+--------+--------+    |  2 | mara |   35 | M      | 3600   |    |  3 | jini |   77 | N      | 5800   |    |  5 | boy  |   35 | M      | 2600   |    |  6 | jini |   77 | M      | 3000   |    |  7 | mary |   18 | N      | 6000   |    |  8 | king |   28 | M      | 4600   |    |  9 | jini |   30 | M      | 3000   |    +----+------+------+--------+--------+    7 rows in set (0.00 sec)

mysql> update boss set Name= 'jimi' where GongZI = '5800';   更新数据Query OK, 1 row affected (0.00 sec)    Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from boss;   +----+------+------+--------+--------+    | ID | Name | Age  | Gender | GongZI |    +----+------+------+--------+--------+    |  2 | mara |   35 | M      | 3600   |    |  3 | jimi |   77 | N      | 5800   |    |  5 | boy  |   35 | M      | 2600   |    |  6 | jini |   77 | M      | 3000   |    |  7 | mary |   18 | N      | 6000   |    |  8 | king |   28 | M      | 4600   |    |  9 | jini |   30 | M      | 3000   |    +----+------+------+--------+--------+    7 rows in set (0.00 sec)

mysql> select * from boss;   +----+------+------+--------+--------+    | ID | Name | Age  | Gender | GongZI |    +----+------+------+--------+--------+    |  2 | mara |   35 | M      | 3600   |    |  3 | jimi |   77 | N      | 5800   |    |  5 | boy  |   35 | M      | 2600   |    |  6 | jini |   77 | M      | 3000   |    |  7 | mary |   18 | N      | 6000   |    |  8 | king |   28 | M      | 4600   |    |  9 | jini |   30 | M      | 3000   |    +----+------+------+--------+--------+    7 rows in set (0.00 sec)

mysql> select * from boss where GongZI between 2000 and 5000; 查询工资介于2000-5000的人+----+------+------+--------+--------+    | ID | Name | Age  | Gender | GongZI |    +----+------+------+--------+--------+    |  2 | mara |   35 | M      | 3600   |    |  5 | boy  |   35 | M      | 2600   |    |  6 | jini |   77 | M      | 3000   |    |  8 | king |   28 | M      | 4600   |    |  9 | jini |   30 | M      | 3000   |    +----+------+------+--------+--------+    5 rows in set (0.00 sec)

mysql> select * from boss where Name like '%j%';  like 比较消耗资源，尽量少使用   +----+------+------+--------+--------+    | ID | Name | Age  | Gender | GongZI |    +----+------+------+--------+--------+    |  3 | jimi |   77 | N      | 5800   |    |  6 | jini |   77 | M      | 3000   |    |  9 | jini |   30 | M      | 3000   |    +----+------+------+--------+--------+    3 rows in set (0.01 sec)

mysql> select * from boss where Name rlike '^j.*$';  模糊查找基于正则   +----+------+------+--------+--------+    | ID | Name | Age  | Gender | GongZI |    +----+------+------+--------+--------+    |  3 | jimi |   77 | N      | 5800   |    |  6 | jini |   77 | M      | 3000   |    |  9 | jini |   30 | M      | 3000   |    +----+------+------+--------+--------+    3 rows in set (0.00 sec)

mysql> show engines;    查看存储引擎   +------------+---------+------------------------------------------------------------+--------------+------+------------+    | Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |    +------------+---------+------------------------------------------------------------+--------------+------+------------+    | MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |    | CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |    | MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |    | InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |    | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |    +------------+---------+------------------------------------------------------------+--------------+------+------------+    5 rows in set (0.00 sec)

mysql> show table status\G   查看表的属性   *************************** 1. row ***************************               Name: Class             Engine: MyISAM            Version: 10         Row_format: Dynamic               Rows: 1    Avg_row_length: 32        Data_length: 32    Max_data_length: 281474976710655       Index_length: 2048          Data_free: 0    Auto_increment: 2        Create_time: 2014-08-11 04:07:12        Update_time: 2014-08-11 04:14:15         Check_time: NULL          Collation: latin1_swedish_ci           Checksum: NULL    Create_options:            Comment:

mysql> show character set;   查看字符集   +----------+-----------------------------+---------------------+--------+    | Charset  | Description                 | Default collation   | Maxlen |    +----------+-----------------------------+---------------------+--------+    | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |    | dec8     | DEC West European           | dec8_swedish_ci     |      1 |    | cp850    | DOS West European           | cp850_general_ci    |      1 |    | hp8      | HP West European            | hp8_english_ci      |      1 |    | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |

mysql> show collation;   查看排序规则   +--------------------------+----------+-----+---------+----------+---------+    | Collation                | Charset  | Id  | Default | Compiled | Sortlen |    +--------------------------+----------+-----+---------+----------+---------+    | big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |    | big5_bin                 | big5     |  84 |         | Yes      |       1 |    | dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |    | dec8_bin                 | dec8     |  69 |         | Yes      |       1 |

mysql> show global variables like '%new%';  查看变量   +---------------+-------+    | Variable_name | Value |    +---------------+-------+    | new           | OFF   |    +---------------+-------+    1 row in set (0.00 sec)

mysql> show global  status like 'create';   Empty set (0.00 sec)

创建用户和授权   MariaDB [(none)]> create user dingchao@'192.168.%.%' identified by '1234'；    Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> create user tom@'192.168.%.%' identified by '1234';   Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;   Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database dingchao;   Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all on dingchao.* to dingchao@'192.168.%.%' identified by '1234'；   Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on dingchao.* to tom@'192.168.%.%' identified by '1234';   Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;   Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;   +----+----------+------+--------+------------+    | ID | Name     | Age  | Gender | Class      |    +----+----------+------+--------+------------+    |  1 | dingchao |   40 | m      | 11wanglou1 |    |  2 | tom      |   40 | m      | 11wanglou2 |    |  3 | peter    |   30 | F      | wushu      |    |  4 | mary     |   40 | m      | 11wanglou2 |    |  5 | kim      |   22 | F      | wushu      |    |  6 | jeny     |   33 | m      | 11wanglou2 |    |  7 | lili     |   26 | F      | wushu      |    |  8 | jbod     |   28 | m      | 11wanglou2 |    |  9 | maki     |   55 | F      | wushu      |    +----+----------+------+--------+------------+

select * from student where Age >30;  选择行

mysql> select * from student where Age >30;   +----+----------+------+--------+------------+    | ID | Name     | Age  | Gender | Class      |    +----+----------+------+--------+------------+    |  1 | dingchao |   40 | m      | 11wanglou1 |    |  2 | tom      |   40 | m      | 11wanglou2 |    |  4 | mary     |   40 | m      | 11wanglou2 |    |  6 | jeny     |   33 | m      | 11wanglou2 |    |  9 | maki     |   55 | F      | wushu      |    +----+----------+------+--------+------------+

select Name,Age from student where Age >30; 投影列选择行

mysql> select Name,Age from student where Age >30;   +----------+------+    | Name     | Age  |    +----------+------+    | dingchao |   40 |    | tom      |   40 |    | mary     |   40 |    | jeny     |   33 |    | maki     |   55 |

+----------+------+

创建用户和授权

MariaDB [(none)]> create user dingchao@'192.168.%.%' identified by '1234'；

Query OK, 0 rows affected (0.01 sec)

创建tom这个用户@可以从192.168.0.0这个网段上以密码1234 登录到服务器

MariaDB [(none)]> create user tom@'192.168.%.%' identified by '1234';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;  刷新密码使用户生效，不然重启服务器

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create database dingchao;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all on dingchao.* to dingchao@'192.168.%.%' identified by '1234'；

Query OK, 0 rows affected (0.00 sec)

授权tom这个用户可以在dingchao这个数据库上创建所有的表、删除所有的表及数据

可以从192.168.0.0 这个网段以1234密码登录

MariaDB [(none)]> grant all on dingchao.* to tom@'192.168.%.%' identified by '1234';

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;  刷新使授权生效

Query OK, 0 rows affected (0.00 sec)

查询数据库中授权的用户情况

MariaDB [mysql]> select User,Password,Host from user;

+---------+-------------------------------------------+---------------+

| User    | Password                                  | Host          |

+---------+-------------------------------------------+---------------+

| root    | *A4B6157319038724E3560894F7F932C8886EBFCF | localhost     |

| root    | *A4B6157319038724E3560894F7F932C8886EBFCF | 127.0.0.1     |

| tom     | *A4B6157319038724E3560894F7F932C8886EBFCF | 192.168.%.%   |

| root    | *A4B6157319038724E3560894F7F932C8886EBFCF | 192.168.%.%   |

| zbxuser | *A4B6157319038724E3560894F7F932C8886EBFCF | 172.16.%.%    |

| zbxuser | *A4B6157319038724E3560894F7F932C8886EBFCF | node1.org.com |

| zbxuser | *24E65C3D3577DA6C2A596788CEAA02923A74B75D | localhost     |

+---------+-------------------------------------------+---------------+

update

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

建议使用GRANT语句进行授权，语句如下：

grant all privileges on *.* to root@'%' identified by "root";

---------------------------------------------------

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

ON maildb.*

TO 'mail'@'localhost'

IDENTIFIED by 'mailPASSWORD ';

创建snort数据库，创建snort用户，将snort库所有表的所有权限赋予用户snort。

mysql> create database snort;

Query OK, 1 row affected (0.06 sec)

创建数据库

mysql> use mysql;

进入mysql库

mysql> insert into user (Host,User,Password) values ("localhost","snort",PASSWORD("112233445566"));

创建用户，设置初始密码

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

使改动生效； 注：如果不执行该指令，则无法正常执行后续指令。

mysql> grant all on snort.* to 'snort'@'localhost' ;

Query OK, 0 rows affected (0.00 sec)

将snort库的所有权限赋予 snort用户

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

使改动生效

mysql> show grants for snort@localhost;

+-------------------------------------------------------------------------------------+

| Grants for snort@localhost                                                          |

+-------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'snort'@'localhost' IDENTIFIED BY PASSWORD '1e6b29186dd45e97' |

| GRANT ALL PRIVILEGES ON `snort`.* TO 'snort'@'localhost'                            |

+-------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

MySQL 赋予用户权限命令的简单格式可概括为：grant 权限 on 数据库对象 to 用户

一、grant 普通数据用户，查询、插入、更新、删除 数据库中所有表数据的权利。

grant select on testdb.* tocommon_user@'%'

grant insert on testdb.* tocommon_user@'%'

grant update on testdb.* tocommon_user@'%'

grant delete on testdb.* tocommon_user@'%'

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* tocommon_user@'%'

二、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。

grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on testdb.* todeveloper@'192.168.0.%' ;

grant alter  on testdb.* todeveloper@'192.168.0.%' ;

grant drop   on testdb.* todeveloper@'192.168.0.%' ;

grant 操作 MySQL 外键权限。

grant references on testdb.* todeveloper@'192.168.0.%' ;

grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* todeveloper@'192.168.0.%' ;

grant 操作 MySQL 索引权限。

grant index on  testdb.* todeveloper@'192.168.0.%' ;

grant 操作 MySQL 视图、查看视图源代码 权限。

grant create view on testdb.* todeveloper@'192.168.0.%' ;

grant show   view on testdb.* todeveloper@'192.168.0.%' ;

grant 操作 MySQL 存储过程、函数 权限。

grant create routine on testdb.* todeveloper@'192.168.0.%' ;  -- now, can show procedure status

grant alter  routine on testdb.* todeveloper@'192.168.0.%' ;  -- now, you can drop a procedure

grant execute        on testdb.* todeveloper@'192.168.0.%' ;三、grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb todba@'localhost'

其中，关键字 “privileges” 可以省略。

四、grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* todba@'localhost'

五、MySQL grant 权限，分别可以作用在多个层次上。

1. grant 作用在整个 MySQL 服务器上：

grant select on *.* todba@localhost ; -- dba 可以查询 MySQL 中所有数据库中的表。

grant all    on *.* todba@localhost ; -- dba 可以管理 MySQL 中的所有数据库

2. grant 作用在单个数据库上：

grant select on testdb.* todba@localhost ; -- dba 可以查询 testdb 中的表。

3. grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders todba@localhost ;

4. grant 作用在表中的列上：

grant select(id, se, rank) on testdb.apache_log todba@localhost ;

5. grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to'dba'@'localhost'

grant execute on function  testdb.fn_add to'dba'@'localhost'

六、查看 MySQL 用户权限

查看当前用户(自己)权限：

show grants;

查看其他 MySQL 用户权限：

show grants for dba@localhost;

七、撤销已经赋予给 MySQL 用户权限的权限。

revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

grant  all on *.* to   dba@localhost;

revoke all on *.* from dba@localhost;

八、MySQL grant、revoke 用户权限注意事项

1. grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

2. 如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“

grant select on testdb.* to dba@localhost with grant option;