如何查看mysql my.ini_MySQL学习笔记（一）

MySQL  学习笔记

一.安装

Typical:经典安装

Custom:自定义安装

Complete: 完全安装

二.修改编码方式

[mysqld]

myini:character-set-server=utf8

[client] port=3306 default-character-set=utf8

三.启动停止mysql服务

cmd net stop mysql

cmd net start mysql

四.MySQL登录

mysql -V 查看版本，并退出

mysql -uroot -p -P3306 -h127.0.0.1 登录本地mysql

mysql>exit; 退出

mysql>quit; 退出

mysql>\q; 退出

五.修改MySQL提示符

shell>mysql -uroot -proot --prompt 新提示符 \h localhost

mysql>prompt 新提示符

(\D 完整日期  \d 当前数据库 \h 服务器名称  \u当前用户)

六.常用命令&语句规范

select version() 显示服务器版本

select now() 显示当前日期时间

select user() 显示当前用户

关键字与函数名全部大写

数据库名称，表名称，字段名称全部小写

SQL语句以分号结尾

七.操作数据库

创建数据库

create {database|schema}  [if not exist] db_name [default] character set [=] charset_name

create database t1;

create database if not exists t2 character set utf8;

查看数据

show {databases|schemas} [like 'pattern' |where expr]

show databases;

查看编码方式

show create database t1;

alter {database|schema} [da_name] [default] character set [=] charset_name

删除数据库

drop {database|schema} [if exists] da_name

八、MySQL 数据类型

        数据类型是指列、存储过程参数、表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

        整型

    tinyint 1字节  smallint2字节 mediumint3字节 int4字节 bigint8字节

        浮点型

    float[(M,D)] M是数字总位数，D是小数点后面的位数，如果M和D被省略，根据硬件允许的限制来保存值。大约精确7位小数

double 双精度浮点

        日期时间型

    year 1

    time 3

    date 3

    datetime 8

    timestamp 4

        字符型

    char(M)  M个字节，0-255

    varchar(M) L+1个字节，L<=M<=65535

    tinytext L+1字节 L<28

    text L+1字节 L<216

    mediumtext() 字节 L+1 L<224

    longtext  L+1字节 L<232

    enum('vlaue1','value2',...) 1 or 2个字节，取决于枚举值个数，最多65535

    set('value','value2',...) 1，2，3，4，8个字节，取决于set成员数目，最多64个 集合

九、数据表的操作

数据表是数据库的最重要组成部分，是对象的基础

打开数据库 use db_name

创建数据表

create table [if not exists] table_name(

column_name data_type,

column_name data_type,

)

root@localhost t2>create table if not exists haha(-> id int primary key,-> name varchar(10),-> age tinyint unsigned,-> salary float(8,2) unsigned-> );

Query OK, 0 rows affected (0.87 sec)

查看数据表

show tables [from db_name] [like 'pattern'|where expr]

show tables

root@localhost t2>show tables;

+--------------+| Tables_in_t2 |+--------------+| haha         |+--------------+1 row in set (0.05 sec)

        查看数据表结构

show columns from table_name

root@localhost t2>show columns from haha;

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

| Field  | Type                | Null | Key | Default | Extra |

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

| id     | int(11)             | NO   | PRI | NULL    |       |

| name   | varchar(10)         | YES  |     | NULL    |       |

| age    | tinyint(3) unsigned | YES  |     | NULL    |       |

| salary | float(8,2) unsigned | YES  |     | NULL    |      

|+--------+---------------------+------+-----+---------+-------+

4 rows in set (1.92 sec)

        插入记录

insert [into] table_name [(col_name,...)] values(val,....)

root@localhost t2>insert haha values(1,'zhangsan',18,10000.00);

Query OK, 1 row affected (0.49 sec)

root@localhost t2>insert haha(id,name,salary) values(2,'lisi',2002.35);Query OK, 1 row affected (0.08 sec)

查找记录

select expr,... from table_name

root@localhost t2>select * from t2;

ERROR 1146 (42S02): Table 't2.t2' doesn't existroot@localhost t2>select * from haha;

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

| id | name     | age  | salary   |

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

|  1 | zhangsan |   18 | 10000.00 |

|  2 | lisi     | NULL |  2002.35 |

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

2 rows in set (0.00 sec)

        空值与非空

        null,not null

root@localhost t2>create table tb2(    -> name varchar(20) not null,    -> age tinyint unsigned null    -> );

Query OK, 0 rows affected (0.27 sec)

root@localhost t2>show columns from tb2;

+-------+---------------------+------+-----+---------+-------+|

Field | Type                | Null | Key | Default | Extra |

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

| name  | varchar(20)         | NO   |     | NULL    |       |

| age   | tinyint(3) unsigned | YES  |     | NULL    |       |

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

2 rows in set (0.05 sec)

root@localhost t2>insert tb2 vlaues('tom',null);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'vlaues('tom',null)' at line 1root@localhost t2>insert tb2 values('tom',null);Query OK, 1 row affected (0.04 sec)

root@localhost t2>insert tb2 values(null,12);ERROR 1048 (23000): Column 'name' cannot be null

root@localhost t2>select * from tb2;

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

| name | age  |

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

| tom  | NULL |

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

1 row in set (0.00 sec)

        自动编号

    auto_increment

        主键 (primary key)

    每张数据表只能存在一个主键

    主键保证记录的唯一性(键值唯一)

    主键自动为not null

root@localhost t2>create table tb3(-> id int unsigned auto_increment primary key,-> username varchar(10) not null-> );

Query OK, 0 rows affected (0.04 sec)

root@localhost t2>show columns from tb3;

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

| Field    | Type             | Null | Key | Default | Extra          |

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

| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment || username | varchar(10)      | NO   |     | NULL    |                |

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

2 rows in set (0.06 sec)

root@localhost t2>insert tb3(username) values('zhangsan');Query OK, 1 row affected (0.03 sec)

root@localhost t2>insert tb3(username) values('lisi');Query OK, 1 row affected (0.00 sec)

root@localhost t2>insert tb3(username) values('wangwu');Query OK, 1 row affected (0.00 sec)

root@localhost t2>insert tb3(username) values('zhaoliu');Query OK, 1 row affected (0.00 sec)

root@localhost t2>select * from tb3;

+----+----------+|

id | username |

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

|  1 | zhangsan |

|  2 | lisi     |

|  3 | wangwu   |

|  4 | zhaoliu  |

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

4 rows in set (0.00 sec)

        唯一约束(unique key)

    唯一约束保证记录的唯一性

    唯一约束的字段可以为空值(null)

    每张数据表可以存在多个唯一约束(键值唯一)

root@localhost t2>create table tb5(-> id int unsigned auto_increment primary key,-> username varchar(20) not null unique key,-> age tinyint unsigned-> );

Query OK, 0 rows affected (0.07 sec)

root@localhost t2>show columns from tb5;

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

| Field    | Type                | Null | Key | Default | Extra          |

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

| id       | int(10) unsigned    | NO   | PRI | NULL    | auto_increment || username | varchar(20)         | NO   | UNI | NULL    |                |

| age      | tinyint(3) unsigned | YES  |     | NULL    |                |

+----------+---------------------+------+-----+---------+----------------+3 rows in set (0.06 sec)

root@localhost t2>insert tb5(username,age) values('zhangsan',15);Query OK, 1 row affected (0.00 sec)

root@localhost t2>insert tb5(username,age) values('zhangsan',16);ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'

默认约束(default)

默认值，当插入记录时，如果没有明确为字段赋值，则自动赋予默认值

root@localhost t2>create table tb6(-> id int unsigned auto_increment primary key,-> name varchar(10) not null unique key,-> sex enum('1','2','3') default '3'-> );

Query OK, 0 rows affected (0.01 sec)

root@localhost t2>show columns from tb6;

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

| Field | Type              | Null | Key | Default | Extra          |

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

| id    | int(10) unsigned  | NO   | PRI | NULL    | auto_increment |

| name  | varchar(10)       | NO   | UNI | NULL    |                |

| sex   | enum('1','2','3') | YES  |     | 3       |                |

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

3 rows in set (0.06 sec)

root@localhost t2>insert tb6(name) values('zhangsan');Query OK, 1 row affected (0.00 sec)

root@localhost t2>select * from tb6;

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

| id | name     | sex  |

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

|  1 | zhangsan | 3    |

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

1 row in set (0.00 sec)