mysql service+database

mysql

运行----->cmd----->输入以下命令: ---net start mysql：[net start 服务名] ---表示开始名字为mysql 的服务 ======================================================================== ---net stop mysql :[net stop 服务名] ---表示停止名字为mysql 的服务 --------------------------------------------------------------------------------------------------------------------- ------------------------------使用mysql数据库-前-必须会的命令---------------------------------------- @登陆mysql mysql -u用户名 -p命名 [-h主机名 -P端口号]【Enter,回车确认】 eg: C:\Users\Administrator>mysql -uroot -p -hlocalhost -P3306 Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.40 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> ---------------------------------------------------------------------------------------------------------- @exit:退出mysql eg: mysql> exit Bye C:\Users\Administrator> ------------------------------------------mysql的提示符修改------------------------------------------------- @提示符修改：修改命令的开头 方法1： C:\Users\Administrator>mysql -uroot -p --prompt \h[\D\d\u] | 修改后所用的提示符 方法2： C:\Users\Administrator>mysql -uroot -p Enter password: **** mysql>prompt \h ..................................................................................................................................................... eg: C:\Users\Administrator>mysql -uroot -p --prompt \h Enter password: **** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.40 MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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. localhost----------[修改后的提示] ..................................................................................................................................................... localhostprompt mysql>-------------------[修改的第二种方法：登录后再修改提示符] PROMPT set to 'mysql>' mysql> ..............................................................................................

mysql

........................................................ mysql>prompt \u@\h >-------------------------[多种组合修改提示符] PROMPT set to '\u@\h >' root@localhost > *************************************************************************************** prompt 命令后面接的参数意义： { \h ---------表示当前主机名 \u-----------表示当前登录用户名 \D-----------表示当前完整的日期 \d------------表示当前所用的数据库名 } ------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------mysql 常识----------------------------------------------------------- @@@@@@@@注意：mysql非增删改查命令用回车键[Enter]确认，[sql语句]增删改查命令每条结束用“；”结束 @@@@@@@@语句规范: .关键字与函数名称全部大写 .数据库名称、表名称、字段名称全部小写 .SQL语句必须以分号结尾 [当然这只是规范而已，如果你就是要写小写或大写，在书写无误的情况下也照样执行] ------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------mysql 常用函数----------------------------------------------- .显示当前服务器版本 SELECT VERSION( ); .显示当前的日期时间 SELECT NOW( ); .显示当前用户 SELECT USER( ); .显示当前数据库 SELECT DATABASE( ); ......................................................................................................................................... eg: mysql>SELECT USER();-------[按规定书写] +----------------+ | USER() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> eg: mysql>select version();--------------[偷懒不按规定书写] +-----------+ | version() | +-----------+ | 5.5.40 | +-----------+ 1 row in set (0.03 sec) .................................................................................................................................................................. 以下SQL语句中“{}”大括号中必须写，“|”前后必须选写一个，“[]”中的可写可不写 --------------------------------------------基本 sql 语句------------------------------------------------------------ ................................................................database.............................................................. :::::::::::::::::::::::::::::::::::: ::: 创建数据库 :::: :::::::::::::::::::::::::::::::::::: 创建 CREATTE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name ； 删除： DROP {DATABASE | SCHEMA} [IF EXISTS] d

mysql

b_name; 修改： ALTER {DATABASE |SCHEMA}[db_name] [DEFAULT] CHARACTER SET [=]charset_name; 显示： SHOW{ DATABASES| SCHEMAS} [LIKE 'pattern'|WHERE expr]; 选择： USE database_name ； 注解如下： [最好用大写] @ create{ database | schema}[if not exists] db_name | | 如果不存在 数据库名 [default] character set [=]charset_name ; | | 设置字符串编码 相应编码（gbk,gb312,uft8......） eg： mysql> create database if not exists my_db character set utf8; Query OK, 1 row affected (0.00 sec) .............................................[查看当前服务下的数据表列表]........................................................... @ show{databases |schemas} [like ‘pattern’| where expr] | | 正则表达 后缀 @show create database db_name;[展示数据在创建时的一些信息] @mysql> show warnings;[展示错误信息] eg： mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_db | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) ..................................................[展示数据在创建时的一些信息]......................................................... eg: mysql> show create database my_db; +--------------+--------------------------------------------------------------------------------------------------------+ | Database | Create Database | +--------------+--------------------------------------------------------------------------------------------------------+ | my_db | CREATE DATABASE `my_db` /*!40100 DEFAULT CHARACTER SET utf8 */ | +--------------+--------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ...........................................[修改数据库信息 ]---[字符编码]................................................................ eg: mysql> alter database my_db character set gbk; Query OK, 1 row affected (0.00 sec) mysql> show create database my_db; +--------------+------------------------------------------------------------------------------------------------------+ | Database | Create Database | +--------------+---------------------------------------------

mysql

---------------------------------------------------------+ | my_db |CREATE DATABASE `my_db` /*!40100 DEFAULT CHARACTER SET gbk */ | +--------------+-------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ...........................................[展示数据库]----[所有数库]....................................................................... eg:[未删除my_db之前的数据库列表] mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | my_db | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) .............................................[删除数据库]-----[指定数据库]............................................................... mysql> drop database my_db; Query OK, 0 rows affected (0.16 sec) mysql> show databases;[删除my_db之后的数据库列表] +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) ..................[删除数据库]-----[指定删除已删除/或不知道是否存在的数库]+[无判断句]............ mysql> drop database my_db; ERROR 1008 (HY000): Can't drop database 'my_db'; database doesn't exist ..............[删除数据库]--+[判断句]+--[指定删除已删除/或不知道是否存在的数库]................... mysql> drop database if exists my_db; Query OK, 0 rows affected, 1 warning (0.00 sec) 理解: if exists----> !!! [起了屏蔽错误语句作用] ....................[展现屏蔽的warnings]...................................................................................................... mysql> show warnings; +-------+------+-----------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------+ | Note | 1008 | Can't drop database 'my_db'; database doesn't exist | +-------+------+-----------------------------------------------------+ 1 row in set (0.00 sec) ============================[创建数据库用户]================================ grant all on 数据库名.* to 用户名@localhost identified by '密码'; eg: grant all on newDB.* to newuser@localhost identified by 'password'; 添加一个远程用户，名为username密码为password GRANT ALL PRIVILEGES ON *.* TO username@"%" IDENTIFIED BY 'password' 注释： （1）grant all 赋予所有的权限 （2）newDB.* 数据库newDB 中所有的表 （3）newuser 用户名 （4）@localhost 在本地电脑上的 mysql server 服务器 （5）identfified by 'password' 设置用户密

mysql

码为"password" ==============================[查看数据库用户]=============================== SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM http://wendang.chazidian.comer; eg: mysql> select distinct concat('User: ''',user,'''@''',host,''';') as query from http://wendang.chazidian.comer; +-----------------------------+ | query | +-----------------------------+ | User: 'root'@'127.0.0.1'; | | User: 'hewei'@'localhost'; | | User: 'root'@'localhost'; | | User: '管理员'@'localhost'; | +-----------------------------+ 4 rows in set (0.00 sec) ==============================================================================