初步认识mysql

一：安装mysql-5.6.22-winx64（简单介绍）

        windows下mysql免安装配置
      1、 下载mysql免安装压缩包
                下载mysql-5.6.22-winx64.zip 解压到本地D:\mysql-5.6.22-winx64
       2、 修改配置文件
               复制my-default.ini为my.ini 文件
       3、 修改my.ini
               在[mysqld] 组：
              basedir = D:\\mysql-5.6.22-winx64
              datadir = D:\\mysql-5.6.22-winx64\\data
              port = 3306
       4、 命令行安装mysql
              4.1、注册服务(注意以管理员身份操作：在我的电脑中打开“C:\Windows\System32”，找到文件“cmd.exe”，右键点击，选择“以管理员身份运行(A),进到目录“D:                

                       \mysql-  5.5.24-winx64”，然后运行以下代码)
                        bin\mysqld --install mysql-5.6.22-winx64 --defaults-file=d:\mysql-5.6.22-winx64\my.ini
                4.2、 启动服务(以管理员身份运行)
                          net start mysql-5.6.22-winx64
                           --也可右键点击“我的电脑”|“管理”|“服务和应用程序”|“服务”|找到“mysql-5.5.24-winx64”，右键点开，选择启动
               4.3、命令行登录（把bin目录设到path环境变量中，则可省略“bin\”，后面同理）
                       bin\mysql -u root -p
                      --密码为空回车
                        为root设密码(原密码为空时)：mysqladmin -u root password "1234"
                          修改root密码：mysqladmin -u root password oldpass "newpass"
               4.4、停止服务(以管理员身份运行)
                       net stop mysql-5.6.22-winx64
               4.5 删除服务(以管理员身份运行)
                       bin\mysqld --remove mysql-5.6.22-winx64
                        启动服务不通过注册
                        bin\mysqld --console
                      停止服务不通过注册服务
                      bin\mysqladmin -u root shutdown

5、常用命令
1.列出MYSQL支持的所有字符集：
SHOW CHARACTER SET;

2.当前MYSQL服务器字符集设置
SHOW VARIABLES LIKE 'character_set_%';

3.当前MYSQL服务器字符集校验设置
SHOW VARIABLES LIKE 'collation_%';

4.显示某数据库字符集设置
show create database 数据库名;

5.显示某数据表字符集设置
show create table 表名;

6.修改数据库字符集
alter database 数据库名 default character set 'utf8';

7.修改数据表字符集
alter table 表名 default character set 'utf8';

8.建库时指定字符集
create database 数据库名 character set gbk collate gbk_chinese_ci;

SQL基础：

DDL的创建简单操作：

※※※DDL – 数据定义语言Data Definition Language
建库、建表、设置约束等：
create\drop\alter

create database aa;

create table sstud(
  sno varchar(15) not null primary key,
  sname varchar(15) not null,
  age int,
  saddress varchar(30)
);

alter table sstud add column aa int;//修改表结构--添加一列
alter table sstud drop column aa;//修改表结构--删除一列
drop table sstud;
drop database aa;

DML的简单操作：

※※※  DML－数据操纵语言Data Manipulation Language。
主要指数据的增删查改
Select\delete\update\insert\call

select * from student;   //显示student表中的所有记录
select * from student where saddress='湖南长沙'; //显示(查询）saddress='湖南长沙'的所有记录

select sno,sname,saddress from student;   //显示student表中的指定字段

insert into student values(8000,'Jack','1995-1-1','中国北京'); //向student表插入一条记录

delete from student where sname='heihei'; //删除指定行
update student set sname='Rose' where sno='1009';

insert into student (sno,sname) values ('8001','aaaa');//只插入指定的列

下面我们来了解一下DDL的语法结构：

CREATE语法:

CREATE DATABASE语法

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]
 
create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

CREATE DATABASE用于创建数据库，并进行命名。如果要使用CREATE DATABASE，您需要获得数据库CREATE权限。create_specification选项用于指定数据库的特性。数据库特性储存在数据库目录中的db.opt文件中。CHARACTER SET子句用于指定默认的数据库字符集。COLLATE子句用于指定默认的数据库整序。

CREATE TABLE语法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    [(create_definition,...)]

    [table_options] [select_statement]

或：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

    [(] LIKE old_tbl_name [)];

create_definition:

    column_definition

  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)

  | KEY [index_name] [index_type] (index_col_name,...)

  | INDEX [index_name] [index_type] (index_col_name,...)

  | [CONSTRAINT [symbol]] UNIQUE [INDEX]

        [index_name] [index_type] (index_col_name,...)

  | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)

  | [CONSTRAINT [symbol]] FOREIGN KEY

        [index_name] (index_col_name,...) [reference_definition]

  | CHECK (expr)

column_definition:

    col_name type [NOT NULL | NULL] [DEFAULT default_value]

        [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]

        [COMMENT 'string'] [reference_definition]

type:

    TINYINT[(length)] [UNSIGNED] [ZEROFILL]

  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]

  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]

  | INT[(length)] [UNSIGNED] [ZEROFILL]

  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]

  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]

  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]

  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]

  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]

  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]

  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]

  | DATE

  | TIME

  | TIMESTAMP

  | DATETIME

  | CHAR(length) [BINARY | ASCII | UNICODE]

  | VARCHAR(length) [BINARY]

  | TINYBLOB

  | BLOB

  | MEDIUMBLOB

  | LONGBLOB

  | TINYTEXT [BINARY]

  | TEXT [BINARY]

  | MEDIUMTEXT [BINARY]

  | LONGTEXT [BINARY]

  | ENUM(value1,value2,value3,...)

  | SET(value1,value2,value3,...)

  | spatial_type

index_col_name:

    col_name [(length)] [ASC | DESC]

reference_definition:

    REFERENCES tbl_name [(index_col_name,...)]

               [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]

               [ON DELETE reference_option]

               [ON UPDATE reference_option]

reference_option:

    RESTRICT | CASCADE | SET NULL | NO ACTION

table_options: table_option [table_option] ...

table_option:

    {ENGINE|TYPE} = engine_name

  | AUTO_INCREMENT = value

  | AVG_ROW_LENGTH = value

  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

  | CHECKSUM = {0 | 1}

  | COMMENT = 'string'

  | CONNECTION = 'connect_string'

  | MAX_ROWS = value

  | MIN_ROWS = value

  | PACK_KEYS = {0 | 1 | DEFAULT}

  | PASSWORD = 'string'

  | DELAY_KEY_WRITE = {0 | 1}

  | ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

  | UNION = (tbl_name[,tbl_name]...)

  | INSERT_METHOD = { NO | FIRST | LAST }

  | DATA DIRECTORY = 'absolute path to directory'

  | INDEX DIRECTORY = 'absolute path to directory'

partition_options:

    PARTITION BY

           [LINEAR] HASH(expr)

        |  [LINEAR] KEY(column_list)

        |  RANGE(expr)

        |  LIST(column_list)

    [PARTITIONS num]

    [  SUBPARTITION BY

           [LINEAR] HASH(expr)

         | [LINEAR] KEY(column_list)

      [SUBPARTITIONS(num)]  

    ]

    [(partition_definition), [(partition_definition)], ...]

partition_definition:

    PARTITION partition_name

        [VALUES { 

                  LESS THAN (expr) | MAXVALUE 

                | IN (value_list) }]

        [[STORAGE] ENGINE [=] engine-name]

        [COMMENT [=] 'comment_text' ]

        [DATA DIRECTORY [=] 'data_dir']

        [INDEX DIRECTORY [=] 'index_dir']

        [MAX_ROWS [=] max_number_of_rows]

        [MIN_ROWS [=] min_number_of_rows]

        [TABLESPACE [=] (tablespace_name)]

        [NODEGROUP [=] node_group_id]

        [(subpartition_definition), [(subpartition_definition)], ...]

subpartition_definition:

    SUBPARTITION logical_name

        [[STORAGE] ENGINE [=] engine-name]

        [COMMENT [=] 'comment_text' ]

        [DATA DIRECTORY [=] 'data_dir']

        [INDEX DIRECTORY [=] 'index_dir']

        [MAX_ROWS [=] max_number_of_rows]

        [MIN_ROWS [=] min_number_of_rows]

        [TABLESPACE [=] (tablespace_name)]

        [NODEGROUP [=] node_group_id]

select_statement:

    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

ALTER语法：

ALTER DATABASE语法

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification [, alter_specification] ...

alter_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

ALTER TABLE语法

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options
  | partition_options
  | ADD PARTITION partition_definition
  | DROP PARTITION partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names

DROP语法：

DROP DATABASE语法

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP TABLE语法

DROP [TEMPORARY] TABLE [IF EXISTS]

    tbl_name [, tbl_name] ...

    [RESTRICT | CASCADE]

DML语言：

INSERT语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    VALUES ({expr | DEFAULT},...),(...),...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    SET col_name={expr | DEFAULT}, ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

DELETE语法

单表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

    [WHERE where_definition]

    [ORDER BY ...]

    [LIMIT row_count]

多表语法：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    tbl_name[.*] [, tbl_name[.*] ...]

    FROM table_references

    [WHERE where_definition]

或：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]

    FROM tbl_name[.*] [, tbl_name[.*] ...]

    USING table_references

    [WHERE where_definition]

UPDATE语法

Single-table语法：

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name

    SET col_name1=expr1 [, col_name2=expr2 ...]

    [WHERE where_definition]

    [ORDER BY ...]

    [LIMIT row_count]

Multiple-table语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references

    SET col_name1=expr1 [, col_name2=expr2 ...]

    [WHERE where_definition]

SELECT语法

SELECT

    [ALL | DISTINCT | DISTINCTROW ]

      [HIGH_PRIORITY]

      [STRAIGHT_JOIN]

      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr, ...

    [INTO OUTFILE 'file_name' export_options

      | INTO DUMPFILE 'file_name']

    [FROM table_references

    [WHERE where_definition]

    [GROUP BY {col_name | expr | position}

      [ASC | DESC], ... [WITH ROLLUP]]

    [HAVING where_definition]

    [ORDER BY {col_name | expr | position}

      [ASC | DESC] , ...]

    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [PROCEDURE procedure_name(argument_list)]

    [FOR UPDATE | LOCK IN SHARE MODE]]

以上只是向大家展示常用语法结构，并非详解，具体详细解释，建议下载手册查看，以上语法结构摘自手册。