MySql基础1

--------------- 普通登录
mysql -uroot -p
--  退出
quit
exit
\q
--  带端口号的登录
mysql -uroot -p -P3306
--  带服务器名称的登录
mysql -uroot -p -hlocalhost
----------------------------------------------------------
--  查询数据库
SHOW DATABASES;
--  创建数据库 psd1607
CREATE DATABASE psd1607;
--  查询数据库
SHOW DATABASES;
-- 删除数据库 psd1607
DROP DATABASE psd1607;
--创建数据库 psd07  设置编码为 gbk
CREATE DATABASE psd07
DEFAULT  CHARACTER SET gbk;
--  查看数据库创建命令
SHOW CREATE DATABASE psd07;
-- 修改psd07数据库编码  为 utf8
ALTER DATABASE psd07
CHARACTER SET utf8;
-- 打开数据库
USE psd07;
-- 查看当前打开的数据库
SELECT DATABASE();
-----------------------------------------------------------------------
-- 打开数据库 psd07
USE psd07;
-- 查看数据表
SHOW TABLES;
--  创建 users 表结构
CREATE TABLE users(
    name VARCHAR(30),
    age INT
);
-- 查看users表结构
DESC users;
DESCRIBE users;
SHOW COLUMNS FROM users;
--   查看创建users表结构命令
SHOW CREATE TABLE users;

-- 给users添加数据
INSERT users(name,age)
VALUE
('tom',18),
('rose',20);
--  查看表数据
SELECT * FROM users;
--------------------------------------------------------------------------------
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>mysql -uroot -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.37 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> USE psd07;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_psd07 |
+-----------------+
| users           |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test1(
    -> num1 TINYINT,
    -> nnum2 SMALLINT,
    -> num3 MEDIUMINT,
    -> num4 INT
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> DESC test1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num1  | tinyint(4)   | YES  |     | NULL    |       |
| nnum2 | smallint(6)  | YES  |     | NULL    |       |
| num3  | mediumint(9) | YES  |     | NULL    |       |
| num4  | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> INSERT test1(num1)
    -> VALUE(128);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1
mysql> INSERT test1(num2)
    -> VALUE(32768);
ERROR 1054 (42S22): Unknown column 'num2' in 'field list'
mysql> INSERT test1(num1)
    -> VALUE(127);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test1;
+------+-------+------+------+
| num1 | nnum2 | num3 | num4 |
+------+-------+------+------+
|  127 |  NULL | NULL | NULL |
+------+-------+------+------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test2(
    -> num1 TINYINT UNSIGNED,
    -> num2 SMALLINT(3) ZEROFILL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC test2;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type                          | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| num1  | tinyint(3) unsigned           | YES  |     | NULL    |       |
| num2  | smallint(3) unsigned zerofill | YES  |     | NULL    |       |
+-------+-------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> INSERT test2(num1,num2)
    -> VALUE(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test2;
+------+------+
| num1 | num2 |
+------+------+
|    1 |  001 |
+------+------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test3(
    -> num1 FLOAT(7,2),
    -> num2 DOUBLE(7,2),
    -> num2 DECIMAL(7,2)
    -> );
ERROR 1060 (42S21): Duplicate column name 'num2'
mysql> CREATE TABLE test3(
    -> num1 FLOAT(7,2),
    -> num2 DOUBLE(7,2),
    -> num3 DECIMAL(7,2)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> DESC test3;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| num1  | float(7,2)   | YES  |     | NULL    |       |
| num2  | double(7,2)  | YES  |     | NULL    |       |
| num3  | decimal(7,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT test3(num1,num2,num3)
    -> VALUE(232.678,676.987,345.678);
Query OK, 1 row affected, 1 warning (0.08 sec)

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------+
| Level | Code | Message                                   |
+-------+------+-------------------------------------------+
| Note  | 1265 | Data truncated for column 'num3' at row 1 |
+-------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test3;
+--------+--------+--------+
| num1   | num2   | num3   |
+--------+--------+--------+
| 232.68 | 676.99 | 345.68 |
+--------+--------+--------+
1 row in set (0.00 sec)

mysql> INSERT test3(num1,num2,num3)
    -> VALUE(232.678,676.987,345.67);
Query OK, 1 row affected (0.03 sec)

mysql> CREATE TABLE test4(
    -> aa CHAR(5),
    -> bb VARCHAR(21844)
    -> );
ERROR 1118 (42000): Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to TEXT or B
LOBs
mysql> CREATE TABLE test4(
    -> aa CHAR(5),
    -> bb VARCHAR(21800)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE test5(
    -> sex1 TINYINT(1),
    -> sex2 BOOLEAN
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql>  DESC test5;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| sex1  | tinyint(1) | YES  |     | NULL    |       |
| sex2  | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE test6(
    -> aa DATE
    -> ,
    -> bb TIME,
    -> cc YEAR,
    -> dd DATETIME,
    -> ee TIMESTAMP
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC test6;
+-------+-----------+------+-----+-------------------+--------------------------
---+
| Field | Type      | Null | Key | Default           | Extra
   |
+-------+-----------+------+-----+-------------------+--------------------------
---+
| aa    | date      | YES  |     | NULL              |
   |
| bb    | time      | YES  |     | NULL              |
   |
| cc    | year(4)   | YES  |     | NULL              |
   |
| dd    | datetime  | YES  |     | NULL              |
   |
| ee    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTA
MP |
+-------+-----------+------+-----+-------------------+--------------------------
---+
5 rows in set (0.00 sec)

mysql> INSERT test6(aa,bb,cc,dd,ee)
    -> VALUE
    -> (now(),now(),'2016',now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM test6;
+------------+----------+------+---------------------+---------------------+
| aa         | bb       | cc   | dd                  | ee                  |
+------------+----------+------+---------------------+---------------------+
| 2016-08-22 | 11:25:41 | 2016 | 2016-08-22 11:25:41 | 2016-08-22 11:25:41 |
+------------+----------+------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE test7(
    -> sex ENUM('man','woman','secret')
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> DESC test7;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| sex   | enum('man','woman','secret') | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT test7(sex)
    -> VALUE('man');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test7;
+------+
| sex  |
+------+
| man  |
+------+
1 row in set (0.00 sec)

mysql> SHOW TABLES;
+-----------------+
| Tables_in_psd07 |
+-----------------+
| test1           |
| test2           |
| test3           |
| test4           |
| test5           |
| test6           |
| test7           |
| users           |
+-----------------+
8 rows in set (0.00 sec)

mysql> CREATE TABLE cms_user(
    ->    id  SMALLINT UNSIGNED  PRIMARY KEY  AUTO_INCREMENT,
    ->    username  VARCHAR(50) NOT NULL UNIQUE,
    ->    pwd  CHAR(32)  NOT NULL,
    ->    age  TINYINT UNSIGNED  NOT NULL DEFAULT 18,
    ->    sex  TINYINT(1)  NOT NULL DEFAULT 0,
    ->    email VARCHAR(50) NOT NULL UNIQUE
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> DESC cms_user;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(50)          | NO   | UNI | NULL    |                |
| pwd      | char(32)             | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | YES  |     | 18      |                |
| sex      | tinyint(1)           | YES  |     | 0       |                |
| email    | varchar(50)          | NO   | UNI | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> INSERT cms_user(username,pwd,email)
    -> VALUE
    -> ('TOM','tom123','11@163.com'),
    -> ('rose','rose123','22@163.com'),
    -> ('alice','alice123','33@163.com');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM cms_user;
+----+----------+----------+------+------+------------+
| id | username | pwd      | age  | sex  | email      |
+----+----------+----------+------+------+------------+
|  1 | TOM      | tom123   |   18 |    0 | 11@163.com |
|  2 | rose     | rose123  |   18 |    0 | 22@163.com |
|  3 | alice    | alice123 |   18 |    0 | 33@163.com |
+----+----------+----------+------+------+------------+
3 rows in set (0.00 sec)

mysql>

---------------------day2------------------------------------
USE psd07;
--  创建表结构 cms_user
CREATE TABLE cms_user(
   id  SMALLINT UNSIGNED  PRIMARY KEY  AUTO_INCREMENT,
   username  VARCHAR(50) NOT NULL UNIQUE,
   pwd  CHAR(32)  NOT NULL,
   age  TINYINT UNSIGNED  NOT NULL DEFAULT 18,
   sex  TINYINT(1)  NOT NULL DEFAULT 0,
   email VARCHAR(50) NOT NULL UNIQUE
);

--  给 cms_user 添加数据
INSERT cms_user(username,pwd,email)
VALUE
('TOM','tom123','11@163.com'),
('rose','rose123','22@163.com'),
('alice','alice123','33@163.com');


--    windows下 ：添加中文信息   必须在添加中文之前 
--    设置 客户端命令行窗口 为
--    SET NAMES gbk;

--  删除表结构
DROP TABLE cms_user;

--  给cms_user  添加字段 addr 类型VARCHAR(30)
--  并要求在 age字段后
ALTER TABLE cms_user
ADD addr VARCHAR(30) NOT NULL DEFAULT '北京'
AFTER age;

--  给cms_user  添加两个字段 aa bb
--  在最前面
ALTER TABLE cms_user
ADD aa VARCHAR(30) FIRST,
ADD bb VARCHAR(30) FIRST; 

--   cms_user 中删除 aa ,bb字段
ALTER TABLE cms_user
DROP aa,
DROP bb;
--  cms_user 中 修改 email  数据类型为  
--  VARCHAR(100) 并且放到 addr字段后
ALTER TABLE cms_user
MODIFY email VARCHAR(100) NOT NULL
AFTER addr;
--  将cms_user 中 addr 字段修改 address字段名称
ALTER TABLE cms_user
CHANGE addr  address VARCHAR(30) 
NOT NULL DEFAULT '天津'; 

-------------------------------------------------
-- (1)不写字段的添加(从第一个字段到最后都要赋值)
INSERT cms_user
VALUE(NULL,'jerry','jerry123',20,'上海','jerry@163.com',1);

--  (2) 写字段名称的添加
INSERT cms_user(username,pwd,email)
VALUE
('ben','dfdg23','ben@163'),
('jerry','dfd23','jerry@163'),
('happy','dfdg23','happy@163'),
('good','8ou23','good@163'),
('john','hk23','john@163');

-- (3) INSERT ...SET(只能添加一条记录)
INSERT cms_user SET username='ben',
pwd='ben123',email='ben@163.com';

--  删除 cms_user 中 编号是5 的记录
DELETE FROM cms_user WHERE id=5;
--  清空整个  cms_user表数据:不能重置 AUTO_INCREMENT
DELETE FROM cms_user;
--  清空整个  cms_user表数据:能重置 AUTO_INCREMENT
TRUNCATE  cms_user;

--  更新(UPDATE)


--  更新 cms_user   编号是2 中字段 address值为 北京
UPDATE cms_user SET  address='北京' WHERE id=2; 

--  如果没有WHERE条件，将更新字段中值的所有记录
--  更新 cms_user  age 字段 值加 3
UPDATE cms_user SET age =age+3;
----------------------------------------------------
--  查询 cms_user  中  编号,姓名,地址,邮箱字段的记录.
--  效率高
SELECT id,username,email,address FROM cms_user;

--  3. 给  username字段起别名为 user
SELECT id,username AS user,email,address FROM cms_user;
--   4. 给表cms_user 起别名 u
SELECT id,username,email,address 
FROM cms_user AS u;
--   5.  表名(别名).字段(字段来自哪个表)
--  给 cms_user 表起别名u， 并标注字段来自哪个表
SELECT u.id,u.username,u.email,u.address 
FROM cms_user AS u;
-- 6.  数据库名.表名 (表来自哪个数据库)
--  标注 表 cms_user 来自哪个 数据库 psd07
SELECT id,username,email,address 
FROM psd07.cms_user;

--  (1)  WHERE 条件: 条件过滤
--  查询 编号是 2 的记录
SELECT * FROM cms_user WHERE id=2;
-- 查询 编号不是 2 的记录
SELECT * FROM cms_user WHERE id<>2;
-- 查询 编号大于等于5 的记录
SELECT * FROM cms_user WHERE id>=5;
--   <=> 判断null值
--  查询  字段 address 是null值的记录
SELECT * FROM cms_user
WHERE  address<=>null;

--   b. IS [NOT ] NULL 判断null值
--  查询  字段 address 是null值的记录
SELECT * FROM cms_user
WHERE  address IS null;
--  查询  字段 address  不是null值的记录
SELECT * FROM cms_user
WHERE  address IS  NOT null;

--  cms_user 表查询 编号  2-6 记录
SELECT *  FROM cms_user
WHERE id>=2  AND id<=6;

SELECT *  FROM cms_user
WHERE id  BETWEEN 2 AND 6;
--  cms_user 表查询 编号不在 2-6 记录
SELECT *  FROM cms_user
WHERE id<2  OR id>6;

SELECT *  FROM cms_user
WHERE id  NOT BETWEEN 2 AND 6;

--   d. [NOT] IN(值,值)  (不连续的某几个值)
--  cms_user 表查询 编号 2 5 7 记录
SELECT *  FROM cms_user
WHERE id IN(2,5,7);

SELECT *  FROM cms_user
WHERE  id=2 OR id=5 OR id=7;
--  cms_user 表查询 编号不是 2 5 7 记录
SELECT *  FROM cms_user
WHERE id NOT IN(2,5,7);

SELECT *  FROM cms_user
WHERE id<>2 AND id<>5 AND id<>7;

-- 查询 username 是 tom ，pwd 是 tom123 的记录
SELECT *  FROM cms_user
WHERE username='tom'  AND pwd='tom123';