mysql-学习笔记01-Tutorial
基于5.6
Chapter 3 -Tutorial
修改提示符：
PROMPT \u@\h \d >
\D |
完整的日期 |
\d |
当前数据库 |
\h |
服务器名称 |
\u |
当前用户 |
3.1、连接或者断开SERVER
root 登录创建用户，赋予权限，连接方式两种均可, 连接本地可以不➕-h
$ mysql -uroot -proot
mysql>CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser';
mysql>GRANT ALL ON test.* TO 'myuser'@'localhost'
$ mysql -umyuser -pmyuser
$ mysql -u myuser -p
$ mysql -h localhost -u myuser -p
如果出现以下错误表示，MySQL server daemon (Unix) or service (Windows) 没有在运行
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'
退出连接
mysql> quit
mysql> \q
快捷键： Control+D.
3.2 Entering Queries 输入查询
这部分并不需要选择任何数据库。查询当前时间，版本，用户，当计算器使用
mysql> SELECT VERSION(), CURRENT_DATE; +------------+--------------+ | VERSION() | CURRENT_DATE | +------------+--------------+ | 5.6.40-log | 2018-07-19 | +------------+--------------+ 1 row in set (0.00 sec) mysql> SELECT VERSION(), CURRENT_DATE; +------------+--------------+ | VERSION() | CURRENT_DATE | +------------+--------------+ | 5.6.40-log | 2018-07-19 | +------------+--------------+ 1 row in set (0.00 sec) mysql> SELECT SIN(PI()/4), (4+1)*5; +--------------------+---------+ | SIN(PI()/4) | (4+1)*5 | +--------------------+---------+ | 0.7071067811865475 | 25 | +--------------------+---------+ 1 row in set (0.00 sec) mysql> SELECT VERSION(); SELECT NOW(); +------------+ | VERSION() | +------------+ | 5.6.40-log | +------------+ 1 row in set (0.00 sec) +---------------------+ | NOW() | +---------------------+ | 2018-07-19 15:41:16 | +---------------------+ 1 row in set (0.00 sec) musql> SELECT USER(),CURRENT_DATE; +------------------+--------------+ | USER() | CURRENT_DATE | +------------------+--------------+ | myuser@localhost | 2018-07-19 | +------------------+--------------+ 1 row in set (0.00 sec)
\c 切换回 mysql>
mysql> select -> user() -> \c mysql>
其他提示符：
| Prompt | Meaning |
|---|---|
mysql> |
Ready for new query |
-> |
Waiting for next line of multiple-line query 多行SQL |
'> |
Waiting for next line, waiting for completion of a string that began with a single quote (') |
"> |
Waiting for next line, waiting for completion of a string that began with a double quote (") |
`> |
Waiting for next line, waiting for completion of an identifier that began with a backtick (`) |
/*> |
Waiting for next line, waiting for completion of a comment that began with /* |
3.3 Creating and Using a Database 创建和使用SQL语句
- 只显示有权限的数据库（privilege）
USE, likeQUIT, 不需要分号作为结尾.只能写在一行 - 使用root添加权限：GRANT ALL ON test.* TO 'myuser'@'localhost'
myuser@localhost (none) >SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | test | +--------------------+ 2 rows in set (0.00 sec) myuser@localhost (none) > USE test Database changed myuser@localhost test >
3.3.1 Creating and Selecting a Database创建和查询数据库
root 创建数据库，并赋予权限
root@localhost (none) >CREATE DATABASE menagerie; Query OK, 1 row affected (0.00 sec) root@localhost (none) >GRANT ALL ON menagerie.* TO 'myuser'@'localhost' -> ; Query OK, 0 rows affected (0.00 sec)
myuser 登录并使用
myuser@localhost (none) >show databases -> ; +--------------------+ | Database | +--------------------+ | information_schema | | menagerie | | test | +--------------------+ 3 rows in set (0.00 sec) myuser@localhost (none) >use menagerie Database changed
3.3.2 Creating a Table
显示表
myuser@localhost (none) >show databases -> ; +--------------------+ | Database | +--------------------+ | information_schema | | menagerie | | test | +--------------------+ 3 rows in set (0.00 sec)
建立表
myuser@localhost menagerie > CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 57 Current database: menagerie Query OK, 0 rows affected (0.07 sec)
varchar 字符串类型适合类似：name, owner, 和 species 这些列，因为他们的长度是变化的。
varchar 长度在1 到65535 之间。 可以使用alter table 进行修改。
很多类型的值只有固定的选项，例如性别sex：'male' and'female'可以简化为'm' and 'f'
固定长处使用char类型。
用data 存储时间格式
显示表结构
myuser@localhost menagerie >SHOW TABLES; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 58 Current database: menagerie +---------------------+ | Tables_in_menagerie | +---------------------+ | pet | +---------------------+ 1 row in set (0.01 sec) myuser@localhost menagerie >DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec) myuser@localhost menagerie >
3.3.3 Loading Data into a Table
创建你的表之后，你必须填充它，可是哟使用load data和insert 语句。默认的日期格式 'YYYY-MM-DD'
After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.
Suppose that your pet records can be described as shown here. (Observe that MySQL expects dates in 'YYYY-MM-DD'format; this may be different from what you are used to.)
