工作中一直使用oracle, 开始尝试对mysql进行系统学习。
1: mysql 如何后台登录到命令操作行:
mysql -u(用户名) -h(主机IP) -p(密码)
eg: mysql -uroot -h172.18.102.76 -p
2: 数据库相关操作
查看数据库:
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| piwik |
| test |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
mysql>
创建数据库:
mysql> create databases test123;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databases test123' at line 1
mysql> create database test123;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| piwik |
| test |
| test123 |
| zabbix |
+--------------------+
6 rows in set (0.00 sec)
mysql>
删除数据库:
mysql> drop database test123;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| piwik |
| test |
| zabbix |
+--------------------+
5 rows in set (0.00 sec)
mysql>
进入数据库:
mysql> use test ;
Database changed
mysql>
3: 对表的操作
创建表:
mysql> create table test( name char(12) not null , age int not null , book varchar(40) not null , bir date );
Query OK, 0 rows affected (0.00 sec)
mysql> show tables ;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql> CREATE TABLE table_123( name VARCHAR(60) NOT NULL , score TINYINT UNSIGNED NOT NULL DEFAULT 0 )ENGINE=InnoDB DEFAULT charset=utf8;
Query OK, 0 rows affected (0.00 sec)
ENGINE=InnoDB //设置表的存储引擎,一般常用InnoDB和MyISAM;InnoDB可靠,支持事务;MyISAM高效不支持全文检索
DEFAULT charset=utf8; //设置默认的编码,防止数据库中文乱码
mysql> show tables ;
+----------------+
| Tables_in_test |
+----------------+
| table_123 |
| test |
+----------------+
2 rows in set (0.00 sec)
创建临时表:
CREATE TEMPORARY TABLE tb_name(这里和创建普通表一样);
复制表:
CREATE TABLE tb_name2 SELECT * FROM tb_name;
CREATE TABLE tb_name2 SELECT id,name FROM tb_name;
删除表:
DROP [ TEMPORARY ] TABLE [ IF EXISTS ] tb_name[ ,tb_name2.......];
实例:
>DROP TABLE IF EXISTS tb_name;
重命名表:
RENAME TABLE name_old TO name_new;
ALTER TABLE name_old RENAME name_new;
eg:
mysql> show tables ;
+----------------+
| Tables_in_test |
+----------------+
| table_123 |
| test |
+----------------+
2 rows in set (0.00 sec)
mysql>
mysql> rename table table_123 to new_table_123;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table test rename new_test;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables ;
+----------------+
| Tables_in_test |
+----------------+
| new_table_123 |
| new_test |
+----------------+
2 rows in set (0.00 sec)
查询表结构:
>DESCRIBE tb_name;
也可以使用:
>SHOW COLUMNS in tb_name; //from也可以
表结构修改:
>ALTER TABLE tb_name ADD[CHANGE,RENAME,DROP] ...要更改的内容...
实例:
>ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL;
>ALTER TABLE tb_name DROP address;
>ALTER TABLE tb_name CHANGE score score SMALLINT(4) NOT NULL;
eg:
mysql> describe new_test ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | char(12) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
| book | varchar(40) | NO | | NULL | |
| bir | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> alter table new_test add column test vachar(20) not null ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vachar(20) not null' at line 1
mysql> alter table new_test add column test varchar(20) not null ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table new_test drop book ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table new_test change name name varchar(20) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe new_test ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
| bir | date | YES | | NULL | |
| test | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
表总数据的插入:
1、插入数据:
>INSERT INTO tb_name(id,name,score)VALUES(NULL,'张三',140),(NULL,'张四',178),(NULL,'张五',134);
这里的插入多条数据直接在后边加上逗号,直接写入插入的数据即可;主键id是自增的列,可以不用写。
2、插入检索出来的数据:
>INSERT INTO tb_name(name,score) SELECT name,score FROM tb_name2;
表中指定更新数据:
>UPDATE tb_name SET score=189 WHERE id=2;
>UPDATE tablename SET columnName=NewValue [ WHERE condition ]
删除表中数据:
>DELETE FROM tb_name WHERE id=3;
条件控制:
WHERE 语句:
>SELECT * FROM tb_name WHERE id=3;
HAVING 语句:
>SELECT * FROM tb_name GROUP BY score HAVING count(*)>2
相关条件控制符:
=、>、<、<>、IN(1,2,3......)、BETWEEN a AND b、NOT 、AND 、OR 、Linke()用法中 % 为匹配任意、 _ 匹配一个字符(可以是汉字)
IS NULL 空值检测
Mysql支持REGEXP的正则表达式:
>SELECT * FROM tb_name WHERE name REGEXP '^[A-D]' //找出以A-D 为开头的name
特殊字符需要转义。