MySql基础1

来源：互联网发布：snapgene mac 编辑：程序博客网时间：2024/05/20 21:22
--------------- 普通登录mysql -uroot -p--  退出quitexit\q--  带端口号的登录mysql -uroot -p -P3306--  带服务器名称的登录mysql -uroot -p -hlocalhost------------------------------------------------------------  查询数据库SHOW DATABASES;--  创建数据库 psd1607CREATE DATABASE psd1607;--  查询数据库SHOW DATABASES;-- 删除数据库 psd1607DROP DATABASE psd1607;--创建数据库 psd07  设置编码为 gbkCREATE DATABASE psd07DEFAULT  CHARACTER SET gbk;--  查看数据库创建命令SHOW CREATE DATABASE psd07;-- 修改psd07数据库编码  为 utf8ALTER DATABASE psd07CHARACTER SET utf8;-- 打开数据库USE psd07;-- 查看当前打开的数据库SELECT DATABASE();------------------------------------------------------------------------- 打开数据库 psd07USE 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 -pEnter password: ****Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 5Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> USE psd07;Database changedmysql> 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 1mysql> 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 tabletype, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBsmysql> 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_TIMESTAMP |+-------+-----------+------+-----+-------------------+-----------------------------+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: 0mysql> 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_userCREATE 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_userADD addr VARCHAR(30) NOT NULL DEFAULT '北京'AFTER age;--  给cms_user  添加两个字段 aa bb--  在最前面ALTER TABLE cms_userADD aa VARCHAR(30) FIRST,ADD bb VARCHAR(30) FIRST; --   cms_user 中删除 aa ,bb字段ALTER TABLE cms_userDROP aa,DROP bb;--  cms_user 中 修改 email  数据类型为  --  VARCHAR(100) 并且放到 addr字段后ALTER TABLE cms_userMODIFY email VARCHAR(100) NOT NULLAFTER addr;--  将cms_user 中 addr 字段修改 address字段名称ALTER TABLE cms_userCHANGE addr  address VARCHAR(30) NOT NULL DEFAULT '天津'; --------------------------------------------------- (1)不写字段的添加(从第一个字段到最后都要赋值)INSERT cms_userVALUE(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_INCREMENTDELETE FROM cms_user;--  清空整个  cms_user表数据:能重置 AUTO_INCREMENTTRUNCATE  cms_user;--  更新(UPDATE)--  更新 cms_user   编号是2 中字段 address值为 北京UPDATE cms_user SET  address='北京' WHERE id=2; --  如果没有WHERE条件，将更新字段中值的所有记录--  更新 cms_user  age 字段 值加 3UPDATE cms_user SET age =age+3;------------------------------------------------------  查询 cms_user  中  编号,姓名,地址,邮箱字段的记录.--  效率高SELECT id,username,email,address FROM cms_user;--  3. 给  username字段起别名为 userSELECT id,username AS user,email,address FROM cms_user;--   4. 给表cms_user 起别名 uSELECT 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 来自哪个 数据库 psd07SELECT 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_userWHERE  address<=>null;--   b. IS [NOT ] NULL 判断null值--  查询  字段 address 是null值的记录SELECT * FROM cms_userWHERE  address IS null;--  查询  字段 address  不是null值的记录SELECT * FROM cms_userWHERE  address IS  NOT null;--  cms_user 表查询 编号  2-6 记录SELECT *  FROM cms_userWHERE id>=2  AND id<=6;SELECT *  FROM cms_userWHERE id  BETWEEN 2 AND 6;--  cms_user 表查询 编号不在 2-6 记录SELECT *  FROM cms_userWHERE id<2  OR id>6;SELECT *  FROM cms_userWHERE id  NOT BETWEEN 2 AND 6;--   d. [NOT] IN(值,值)  (不连续的某几个值)--  cms_user 表查询 编号 2 5 7 记录SELECT *  FROM cms_userWHERE id IN(2,5,7);SELECT *  FROM cms_userWHERE  id=2 OR id=5 OR id=7;--  cms_user 表查询 编号不是 2 5 7 记录SELECT *  FROM cms_userWHERE id NOT IN(2,5,7);SELECT *  FROM cms_userWHERE id<>2 AND id<>5 AND id<>7;-- 查询 username 是 tom ，pwd 是 tom123 的记录SELECT *  FROM cms_userWHERE username='tom'  AND pwd='tom123';
0 0