mysql学习（linux系统）
2018-12-15 本文已影响0人
伤了谁疼111
一、安装
[root@xue ~]# yum -y install mariadb-server mariadb
二、启动
[root@xue ~]# systemctl start mariadb
[root@xue ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
三、设置初始密码
[root@xue ~]# mysql_secure_installation
[root@xue ~]# ps -ef | grep mysqld
mysql 31338 1 0 03:04 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 31495 31338 0 03:04 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 31597 28727 0 03:06 pts/0 00:00:00 grep --color=auto mysqld
[root@xue ~]# mysqladmin -uroot -p create RUNOOB
Enter password:
[root@xue ~]# mysqladmin -uroot -p drop RUNOOB
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'RUNOOB' database [y/N] Y
Database "RUNOOB" dropped
四、进入数据库
[root@xue ~]# mysql -uroot -p
五、创建数据库
MariaDB [(none)]> create database pap;
Query OK, 1 row affected (0.00 sec)
六、创建数据表
MariaDB [pap]> create table class (stu int,name varchar(20),agr int,area varchar(20));
Query OK, 0 rows affected (0.20 sec)
MariaDB [pap]> create table score (stu int,name varchar(20),ke varchar(10),fen int);
Query OK, 0 rows affected (0.10 sec)
MariaDB [php]> create table msg(id int,title varchar(60),name varchar(10),content varchar(1000))engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.10 sec)
七、插入数据
MariaDB [pap]> insert into msg(id,title,name,content) values (1,'初来乍到','张三','分解到家');
Query OK, 1 row affected (0.09 sec)
MariaDB [pap]> insert into msg(id,title,name,content) values (2,'又来了','李四','见覅off');
Query OK, 1 row affected (0.03 sec)
MariaDB [pap]> insert into msg
-> values
-> (3,'冯家湾','几覅哦','几覅欧文'),
-> (4,'范围房','房价跟','飞机我啊'),
-> (5,'房价而','佛教房','飞机瓦尔');
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
八、修改数据
MariaDB [pap]> update msg set id=2,content='房间爱我' where name='李四';
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [pap]> insert into msg values ();
Query OK, 1 row affected, 3 warnings (0.70 sec)
九、删除某行数据
MariaDB [pap]> delete from msg where id=2;
Query OK, 1 row affected (0.12 sec)
十、查找数据
MariaDB [pap]> select id,title from msg;
+------+--------------+
| id | title |
+------+--------------+
| 1 | 初来乍到 |
| 3 | 冯家湾 |
| 4 | 范围房 |
| 5 | 房价而 |
| NULL | |
+------+--------------+
5 rows in set (0.00 sec)
MariaDB [pap]> select name,content from msg where id>2;
+-----------+--------------+
| name | content |
+-----------+--------------+
| 几覅哦 | 几覅欧文 |
| 房价跟 | 飞机我啊 |
| 佛教房 | 飞机瓦尔 |
+-----------+--------------+
3 rows in set (0.00 sec)
十一、删除表
MariaDB [pap]> drop table class;
Query OK, 0 rows affected (0.11 sec)
MariaDB [pap]> create table class( id int primary key auto_increment,name varchar(10),age tinyint)charset utf8;
Query OK, 0 rows affected (0.14 sec)
MariaDB [pap]> insert into class (name,age) values ('zhangsan',25);
Query OK, 1 row affected (0.03 sec)
MariaDB [pap]> insert into class (name,age) values ('zhangsan',127);
Query OK, 1 row affected (0.10 sec)
MariaDB [pap]> insert into class (name,age) values ('zhangsan',-128);
Query OK, 1 row affected (0.11 sec)
十二、插入行
MariaDB [pap]> alter table class add age2 tinyint unsigned;
Query OK, 3 rows affected (0.12 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [pap]> insert into class(name,age,age2) values ('lisi',25,0);
Query OK, 1 row affected (0.04 sec)
MariaDB [pap]> alter table class add age3 tinyint(1);
Query OK, 4 rows affected (0.21 sec)
Records: 4 Duplicates: 0 Warnings: 0
浮点型
MariaDB [pap]> create table goods(name varchar(10) not null default '',price float(6,2) not null default 0.00) charset=utf8;
Query OK, 0 rows affected (0.14 sec)
MariaDB [pap]> insert into goods(name,price) values ('跑步机',688.896);
Query OK, 1 row affected (0.10 sec)
MariaDB [pap]> alter table goods add bigprice float(9,2) not null default 0.0;
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [pap]> alter table goods add deciprice decimal(9,2) not null default 0.0;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [pap]> insert into goods(name,bigprice,deciprice) values ('',1234567.23,1234567.23);
Query OK, 1 row affected (0.26 sec)
MariaDB [pap]> select * from goods;
+-----------+--------+------------+------------+
| name | price | bigprice | deciprice |
+-----------+--------+------------+------------+
| 跑步机 | 688.90 | 0.00 | 0.00 |
| | 0.00 | 1234567.25 | 1234567.23 |
+-----------+--------+------------+------------+
2 rows in set (0.00 sec)
MariaDB [pap]> create table stu(name char(8) not null default '',waihao varchar(10) not null default '')charset utf8;
Query OK, 0 rows affected (0.11 sec)
year类型
MariaDB [pap]> create table y(ya year(4));
Query OK, 0 rows affected (0.12 sec)
MariaDB [pap]> insert into y values ('1901');
Query OK, 1 row affected (0.33 sec)
MariaDB [pap]> insert into y values ('2200');
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [pap]> insert into y values ('97');
Query OK, 1 row affected (0.09 sec)
MariaDB [pap]> insert into y values ('12');
Query OK, 1 row affected (0.44 sec)
date类型
MariaDB [pap]> create table d(title varchar(30),dt date)charset utf8;
Query OK, 0 rows affected (0.11 sec)
MariaDB [pap]> insert into d values ('开国大典','1994-10-01');
Query OK, 1 row affected (0.07 sec)
MariaDB [pap]> insert into d values ('世界末日','2012-02-30');
Query OK, 1 row affected, 1 warning (0.26 sec)
time类型
MariaDB [pap]> create table t(tm time);
Query OK, 0 rows affected (0.43 sec)
MariaDB [pap]> insert into t values ('13:34:56');
Query OK, 1 row affected (0.36 sec)
MariaDB [pap]> insert into t values ('13:60:00');
Query OK, 1 row affected, 1 warning (0.36 sec)
日期时间类型
MariaDB [pap]> create table user(name varchar(20) not null default '',regtime datetime not null default '1000-01-01 00:00:00')charset utf8;
Query OK, 0 rows affected (0.21 sec)
MariaDB [pap]> insert into user(name) values ('张三');
Query OK, 1 row affected (0.04 sec)
MariaDB [pap]> insert into user values ('李四','2012-02-22 14:28:36');
Query OK, 1 row affected (0.33 sec)
MariaDB [pap]> create table teacher(name varchar(20),gender tinyint)engine=innodb default charset utf8;
Query OK, 0 rows affected (0.36 sec)
MariaDB [pap]> insert into teacher values ('张',1);
Query OK, 1 row affected (0.10 sec)
MariaDB [pap]> create table t2(gender enum('男','女'))charset utf8;
Query OK, 0 rows affected (0.36 sec)
MariaDB [pap]> insert into t2 values ('男');
Query OK, 1 row affected (0.34 sec)
MariaDB [pap]> insert into t2 values ('女');
Query OK, 1 row affected (0.10 sec)
MariaDB [pap]> insert into t2 values ('春哥');
Query OK, 1 row affected, 1 warning (0.16 sec)
综合练习
创建数据表
MariaDB [pap]>
MariaDB [pap]> create table wl163(
-> id int primary key auto_increment,
-> name char(3) not null default '',
-> age tinyint unsigned not null default 0,
-> email varchar(30) not null default '',
-> tel char(11) not null default '',
-> salary decimal(7,2) not null default '1900.79',
-> riqi date not null default '2018-12-15'
-> )charset=utf8;
Query OK, 0 rows affected (0.13 sec)
插入数据
MariaDB [pap]> insert into wl163
-> (name,age,email,tel,riqi)
-> values
-> ('陈心宇',21,'chenxinyu@yz.com','18912499740','2016-09-10');
Query OK, 1 row affected (0.10 sec)
MariaDB [pap]> insert into wl163
-> values
-> (3,'段宗?',21,'duanzonghe@ah.com','18951312252',1234.56,'2016-09-10');
Query OK, 1 row affected (0.10 sec)
MariaDB [pap]> insert into wl163
-> (name,age,email)
-> values
-> ('薛怀',21,'xuehuai@ha.com'),
-> ('杨其龙',21,'yangqilong@sh.com'),
-> ('张迁',21,'zhangqian@sy.com'),
-> ('王欣',21,'wangxin@ah.com');
Query OK, 4 rows affected (0.11 sec)
Records: 4 Duplicates: 0 Warnings: 0
修改数据
MariaDB [pap]> update wl163
-> set
-> tel='15161737044',
-> salary=3999.99
-> where id=4;
