一、mysql安装
百度经验:http://jingyan.baidu.com/article/642c9d34aa809a644a46f717.html
二、mysql登录与退出,提示符
2.1 登录
使用命令:mysql -uroot -proot -P3306 -h127.0.0.1
如果是本机,且默认端口为3306 ,可以直接使用: mysql -uroot -proot
-u后面的root为用户,
-p后面的root为密码
C:\Users\baojulin>mysql -uroot -proot -P3306 -h127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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>
关于更多登录的参数:
2.2 退出
可以使用: exit; quit; 或者 \q;
mysql> exit;
Bye
C:\Users\baojulin>
2.3 提示符
提示符变成了127.0.0.1
C:\Users\baojulin>mysql -uroot -proot -P3306 -h127.0.0.1 --prompt \h
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.32 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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.
127.0.0.1
三、mysql的语句规范
- 关键字与函数名称全部大写
- 数据库名称,表名称,字段名称全部小写
- SQL语句必须以分号结尾
如: 测试使用系统的几个函数:
函数 | 作用 |
---|---|
VERSION(); | 显示版本信息 |
NOW(); | 显示当前时间 |
USER(); | 显示当前登录的用户 |
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.32 |
+-----------+
1 row in set (0.04 sec)
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2016-12-19 09:40:58 |
+---------------------+
1 row in set (0.03 sec)
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql>
四、创建数据库
语法: {}必选 []可选
创建数据集:
CREATE {DATABASE | SCHEMA} [IF NOT EXTSTS] db_name
[DEFAULT] CHARCTER SET [=] charset_name
查看当前数据库:
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]
修改数据库:
ALTER{DATABASE | SCHEMA} [db_name][DEFAULT] CHARACTER SET [=] charset_name
删除数据库:
DROP {DATABASE | SCHEMA} [IF NOT EXISTS] db_name;
例子:
创建数据库 t1
mysql> CREATE DATABASE IF NOT EXISTS t1;
Query OK, 1 row affected (0.00 sec)
继续创建数据库 t1 ,因为数据库存在,所以提示警告
mysql> CREATE DATABASE IF NOT EXISTS t1;
Query OK, 1 row affected, 1 warning (0.00 sec)
显示数据库警告
mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1007 | Can't create database 't1'; database exists |
+-------+------+---------------------------------------------+
1 row in set (0.00 sec)
查看数据库创建信息
mysql> SHOW CREATE DATABASE t1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| t1 | CREATE DATABASE `t1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
创建数据库 t2 时,设置编码
mysql> CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;
Query OK, 1 row affected (0.03 sec)
mysql> SHOW CREATE DATABASE t2;
+----------+------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------+
| t2 | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)
修改数据库,以及编码
mysql> ALTER DATABASE t2 CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE t2;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| t2 | CREATE DATABASE `t2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
删除数据库
mysql> DROP DATABASE t2;
Query OK, 0 rows affected (0.10 sec)
mysql>
五、数据类型
5.1 整型
5.2 浮点型
5.3 日期类型
MySQL数据类型之日期时间型【实际用的不多,因为时差原因,采用数字代替(时间戳)】
1、YEAR:1970至20691个字节
2、TIME:-838:59:59至838:59:59—3个字节
3、DATE:1000-1-1至9999-12-31—-3个字节
4、DATETIME: 1000-1-1 00:00:00至9999-12-31 23:59:59—8个字节
5、TIMESTAMP存储范围:1970-1-1 00:00:00到2037-12-31 23:59:59—-4个字节
5.4 字符类型
六、创建表
6.2 创建表的语法,插入数据的语法
创建表
CREATE TABLE [IF NOT EXISTS] table_name(column_name date_type,
.........); //table_name表名 column_name列名 date_type数据类型
查看表
SHOW TABLES[FROM db_name] [LIKE 'pattern' | WHERE expr];
SHOW TABLES FROM mysql;//查看所有数据库的数据表列表
查看数据表结构
SHOW COLUMNS FROM tbl_name //tb_name数据表名
DESC tbl_name
插入数据:
INSERE [INTO] tb1_name [(col_name,....)] VALUES(val,....) //插入记录
insert into 表名 values('','','');
insert into 表名(字段名,字段名,字段名)values('','','');
查看表数据
select * from 表名;(注:*指的是字段)
6.2 创建表的时候,给表字段增加约束
语法 | 说明 |
---|---|
NULL | 字段值可以为空 |
NOT NULL | 字段禁止为空 |
AUTO_INCREMENT | 自动编号(自增长),必须配合主键(PRIMARY KEY)使用 |
PRIMARY KEY | 主键:每个表只有一个主键定义,主键是非空且唯一的 |
UNIQUE KEY | 唯一约束: 唯一约束的字段可以为空值(null) 每张表可以存在多个唯一约束 |
DEFAULT | 默认值 |
补充:mysql 约束以及修改列定义(补充)
http://blog.youkuaiyun.com/hp5321/article/details/53786715
6.3 例子:
使用t1数据库
mysql> use t1;
Database changed
查看当前数据库
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| t1 |
+------------+
1 row in set (0.00 sec)
创建表,id 自动编号;username 不为空,且唯一; sex使用枚举,默认值是3
mysql> CREATE TABLE t_user(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> username VARCHAR(20) NOT NULL UNIQUE KEY,
-> sex ENUM('1','2','3') DEFAULT '3'
-> );
Query OK, 0 rows affected (0.16 sec)
显示表
mysql> SHOW TABLES;
+--------------+
| Tables_in_t1 |
+--------------+
| t_user |
+--------------+
1 row in set (0.00 sec)
mysql> SHOW TABLES FROM t1;
+--------------+
| Tables_in_t1 |
+--------------+
| t_user |
+--------------+
1 row in set (0.00 sec)
显示表的结构
mysql> SHOW COLUMNS FROM t_user;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | enum('1','2','3') | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> DESC t_user;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | enum('1','2','3') | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
插入数据
mysql> INSERT INTO t_user(username,sex) VALUES('lhp','1');
Query OK, 1 row affected (0.08 sec)
查看数据,id字段已经自动编号
mysql> SELECT * FROM t_user;
+----+----------+------+
| id | username | sex |
+----+----------+------+
| 1 | lhp | 1 |
+----+----------+------+
1 row in set (0.00 sec)
插入数据,性别使用默认值
mysql> INSERT INTO t_user(username) VALUES('lhp2');
Query OK, 1 row affected (0.05 sec)
查看数据。性别默认值是3
mysql> SELECT * FROM t_user;
+----+----------+------+
| id | username | sex |
+----+----------+------+
| 1 | lhp | 1 |
| 2 | lhp2 | 3 |
+----+----------+------+
2 rows in set (0.00 sec)
mysql>
个人学习笔记(慕课网)